ODBC Enterprise Upsert mode(Delete Then Insert) for DB2

[girish312]

Hi,
I am trying to figure out the proper way to configure the ODBC EE stage in parallel job to Upsert records into DB2 table. I noticed that when I select "Delete Then Insert option" in upsert mode, when the table is not empty, it is not inserting anything, but deleting all records. I am wondering somehow it is doing the insert operation first, but not realling inserting anything as it already has same number of records. Then it is trying Delete operation and deleting everything it had from previous run. At the end, the table has no records. I also noticed, if the table is empty, it is inserting the records, but not deleting anything. This way it is atleast inserting records when table is empty. My thought is, it should first delete all records based on keys and then insert into the table. But it seems to working incorrectly.

Anythouhts based on how guys used it. My first preference is to achived the Delete and Insert operation within in one stage in the same job. We have no proper DB2 EE setup and DB2 API seems to be not capturing any errors when used in parrallel jobs.

Any thoughts would be highly appreciated. Last but not least, this site has been highly helpful on finding answers for some of DS issues where there is no documentation.


Thanks,
Girish