Please share the SQL query which you are executing.
Effective with 10.4 there is a native connector for PostGreSQL.
Please note the following:
- Generally speaking ODBC is not certified/supported.
- ODBC can be used if Pushdown Optimization is enabled.
In addition to this, it is only supports for Source or Full pushdown and the ODBC subtype as PostgreSQL must be selected as per documentation.
Documentation Reference:Effective in version 10.2 HotFix 2, when the connection type is ODBC, you can select the ODBC subtype as PostgreSQL to push transformation logic to PostgreSQL. You can configure source-side or full pushdown optimization to push the transformation logic to PostgreSQL.For more information about the supported functions and transformations that you can push to the PostgreSQL database, see the Informatica PowerCenter 10.2 HotFix 2 Advanced Workflow Guide.
- This means that the session would need to be able to SUCCESSULLY pushdown the mapping logic either to the source only or to both the source and target.
- Pushdown to target only is not supported.
Thank you for the response, Other jobs are running fine using ODBC connection. The issue I'm getting is because I'm using create temp table as (select * from table) in source qualifier sqlquery which is causing this issue. My question is is create stmts can't be used in sqlquery from informatica
Generally speaking a CREATE TABLE statement is not a SELECT statement, even though it may *contain* one (as in your case). And in a Source Qualifier you can only use SELECT statements, nothing else.
Only Select statements should be used within Source Qualifier SQL query. SQL queries/functions such as drop, create, insert, and if are not supported in Source Qualifier SQL query. You can use Select statement with the following clauses: From, Where, Join, Union, Order By, or subqueries.
By design, Source Qualifier is used to represent the rows that the Integration Service reads when it runs a session. SQL query is a custom query that replaces the default query the Integration Service uses to read data from sources represented in this Source Qualifier transformation. A custom query overrides entries for a custom join or a source filter.
Please refer the below document for more details on the same:
Please let us know if this helped.
As stated by others, Create query cannot be used in the Source Qualifier.
You may use a Pre-SQL option at session to create a table prior to the session execution or use the Post-SQL to drop the temp table created.
Hope this helps.
1 of 1 people found this helpful
As stated others, Create statement cannot be used in SQL query override. You can create a store procedure and try calling its has a source on it.
Thank you for the responses. I tried using presql, looks like presql doesn't allow joins. Temp table is created based on select stmt joins. Stored procedure reads record by record, we have huge volume, unless lot of design change in involved. Please let me know your suggestions on this
In order to give better avice, we need to understand this part of the whole process better.
What exactly are you trying to do here?
Why do you want to create a temporary table while reading data to be processed?
Can't these two process steps be separated? If not, why not?
Which DBMS do we talk about? Temporary tables have different properties in different DBMS, that's why I'm asking.
We migrated to new DB, as part of that testing, initially due to huge volume we were getting spill issue from DB.
Per DBA suggestion we used create temp tables in source qualifier sql. By implementing sql worked fine in dbeaver. Per our previous discussion we know that Other than Select we can't use anyother DDL/DML's in sql query. I tired in presql, presql also we can't use becuase temp on the basis of mutiple joins etc and presql won't take mutiple sources.
$$SQ_query is the actual sql with create temp which is in param file. Using unix script I wrote the o/p to a txt file.
since this SQ_query is multiple places in session like below, not sure how to use this in below.
Or is there a better way to implement this
select id,sys_name,count(*),sum(x),sum(y) from (SQ_query ) a group by id, sys_name.
I've never worked with PostGres myself, so I don't know how exactly a temporary table is set up there. Does this temporary table exist only during that particular DB session, or is this something like a "materialised view" in Oracle (which can be used in multiple processes)?
Honestly I still don't understand what this temp table is good for. If it's "stable" beyond the process during which it was created, for example a "staging" table should work fine as a replacement. Then you would simply need to extract ID and SYS_NAME from this "staging table" via a standard relational SQ and aggregate these data in an Aggregator transformation.
Either way, a SQ must contain only SELECT statements, nothing else. So you have to revise the whole process such that the creation of this "table" is separated from the actual aggregation process.
I have not tried this in Oracle/Post but this does work in MS SQL. The below block works just fine in a SQL Override to create the global temp in the scope of the Select statement and like temp tables removes it when the session is done.
select '1' as 'ID'
SELECT CUSTOMER.C_CUSTKEY, CUSTOMER.C_NAME, CUSTOMER.C_ADDRESS, CUSTOMER.C_NATIONKEY, CUSTOMER.C_PHONE, CUSTOMER.C_ACCTBAL, CUSTOMER.C_MKTSEGMENT, CUSTOMER.C_COMMENT , #temp.ID
inner join #temp on 1=1;