error in doing UPSERT mode in DB2UDB

kalpanam · Post by **kalpanam** » Mon Sep 20, 2010 4:04 am

Hi All,

I have the job design as :

dataset----->DB2

dataset with two columns ORDER_NBR(decimal 9,0) and order_key(integer 10)

DB2 stage is having other columns as sesion_key,update_time,ORDER_NBR(decimal 9,0),order_key(integer 10).

I'm using write method:upsert and upsert method is Auto-generatedupdate only.(update sql :UPDATE #$prsnt_SchemaName#CS_ORDER_FACT SET ORDER_KEY = ORCHESTRATE.ORDER_KEY WHERE (ORDER_NBR = ORCHESTRATE.ORDER_NBR)

but the job is getting abort giving the error as "session_key in the input dataset.
Missing field: session_key in the input dataset".

Please let me know where I'm doing wrong?

Thanks,
Kalpana.

ArndW · Post by **ArndW** » Mon Sep 20, 2010 4:07 am

Is "session_key" declared in the dataset and passed into the stage? Did you declare "session_key" in the DB2 stage columns and then not use it in your insert or update statements?

kalpanam · Post by **kalpanam** » Mon Sep 20, 2010 4:14 am

ArndW wrote:Is "session_key" declared in the dataset and passed into the stage? Did you declare "session_key" in the DB2 stage columns and then not use it in your insert or update statements?

no session_key is not present in the dataset,infact dataset is having only two columns that are order_nbr and order_key and i need to update the table in db2.my auto-generated update statament is like :

UPDATE #$prsnt_SchemaName#CS_ORDER_FACT SET ORDER_KEY = ORCHESTRATE.ORDER_KEY WHERE (INTERNET_ORDER_NBR = ORCHESTRATE.INTERNET_ORDER_NBR)

rohithmuthyala · Post by **rohithmuthyala** » Mon Sep 20, 2010 5:52 am

The error basically is saying that the DB2 stage has an extra column session_key, but the source dataset doesn't have it, so if you remove the extra columns in the DB2 stage and keep only the columns present in Dataset.