Netezza userdefined update query issue

[njgirl]

Hi,
Could you please help me for the below issue.
I've a parallel job to update a netezza table .

Purpose:

Code: Select all

update xtable set c1=inputc1, c2=inputc2
where k1=inputk1 and k2=inputk2 and c3 is null

I'm using Netezza connector stage as a target stage. When I choose write mode and define k1 and k2 and update columns as c1 and c2 then its working fine. But I want to add condition as c3 is null. so I defined a userdefined query and giving as:

Code: Select all

update xtable set c1=orchestrate.c1,c2=orchestrate.c2
where k1=orchestrate.k1 and k2=orchestrate.k2 and c3 is null

then its throwing error as "Reason: [SQLCODE=42S02][Native=29] ERROR: Relation 'ORCHESTRATE' does not exist".
If I use ODBC stage and gave same query, then working fine, but running very slow. So, I want to use netezza stage and need the required output. Please advice.

Thanks,

[vmcburney]

The Netezza connector is kind of new and not many of us have used it yet. It looks like this stage doesn't like the orchestrate keyword in the user-defined SQL. I would try changing the stage back to Generated SQL and running it and seeing what the SQL statement looks like in the log messages. It might have different syntax. I would also try building it through the SQL Builder so it generates the user-defined SQL for you - if there is a SQL building in a Netezza output stage.

[vamsi.4a6]

I would try changing the stage back to Generated SQL and running it and seeing what the SQL statement looks like in the log messages. It might have different syntax. .

Do I need to set or enable any enviornment variable to see the SQL Statement in the log messages?

[chulett]

I? Do you post under two different userids?

[akshyat]

Netezza Database uses the concept Temporary work tables for loading the data unless we specify the load types as direct loads. Therefore your regular update expressions would not work in case of a user defined query.

Netezza Connector stage provides place holders for the main table and the temporary work table.

so a regular update statement in datastage:
update table1 set col1=orchestrate.col1 where col2=orchestrate.col2

would change to:
UPDATE [[table]]
SET [[table]].col1 = [[twt]].col1
WHERE
[[table]].col2= [[twt]].col2

Note the placeholders [[table]] signifies the main table to be loaded.
[[twt]] signifies the temporary work table. During run time the netezza connector stage automatically assigns values for these two tables.

[collabxchange]

[[twt]] signifies the temporary work table. During run time the netezza connector stage automatically assigns values for these two tables.

If TWT is assigned at runtime then how would you give its exact name in the sql?

[aymancherif]

hello collabxchange ,

Have you known how to get the TWT name so that I could be able to write the user defined SQL in the Netezza Connector stage.

Thanks,