User-defined update statement in DB2 UDB API Stage

[vkhandel]

I have a requirement wherein I have to update the DB2 (on AS400) tables from the input dataset by passing the update statement as parameter.

The input dataset schema is like -
COL1, COL2, COL3, KEYCOL1, KEYCOL2, KEYCOL3

And say the target table schema is like -
COLA, COLB, COLC, KEYCOLA, KEYCOLB, KEYCOL3

Could someone suggest how should I built and pass the UPDATE statement as a parameter to DB2 API Stage in this case?

The whole job is in RCP mode.

[kris007]

I have tried passing SQL statements as parameters to read data from tables but so far never to update/insert. But I am assuming it should still be similar for user defined update/inserts. Auto generated Update statements within the DB2 stage usually look like this

Code: Select all

UPDATE TABLE SET TABLE.COLUMN = ORCHESTRATE.INPUT_VALUE WHERE TABLE.KEY = ORCHESTRATE.INPUT_KEY

Once you have the auto generated sql you have to copy the "non orchestrate" part and pass it as a parameter to your job and use those parameters in your user defined SQL. In this case you might need two parameters. Let us know if it works.

[vkhandel]

Thanks for the suggestion "Kris". That didn't worked though .. (
As a workaround, I am updating table by generating UPDATE statements at run-time for each target table and directly executing them from UNIX.

[kris007]

Thanks for the suggestion "Kris".

Not sure what "Kris" implies