DB2 UPSERT , Records silently rejected
Posted: Fri Jul 31, 2009 1:09 pm
Hi All,
We have job that loads data into a table on UPSERT mode. The Update SQL has been maked with a "1=0" condition in its where clause. The destination table has a single column as key and is the LD_SEQ_I.
LD_SEQ_I is set as auto increment at a database level. Basically every record coming through the input link will need to be inserted and the database will automatically increment the LD_SEQ_I and insert it in there with the record.
What has been happening is that occation only 20-30% of the records get loaded and the remaining simply get rejected. There is no reason why the records could get rejected at all. As a temporary fix I just clean up the table and rerun the job, the records go in easily.
Success and Rejects logs are as following.
Project:XXXXXXXX
Job name:XXXXXXX
Event #:92227
Timestamp:7/31/2009 3:36:37 AM
Event type:Info
User:xxxxxxx
Message:
db2API_REF_INITEM_E,92: Records Inserted: 163
Records Used for Update: 0
Records Rejected: 0
Total Records: 163.
Project:XXXXXXXX
Job name:XXXXXXX
Event #:92231
Timestamp:7/31/2009 3:45:58 AM
Event type:Info
User:xxxxxxx
Message:
db2API_REF_INITEM_E,102: Records Inserted: 0
Records Used for Update: 0
Records Rejected: 152
Total Records: 152.
This is a daily job and it only fails like this intermittently. It occurred 10 times this month.
Any thoughts or suggestions ???
We have job that loads data into a table on UPSERT mode. The Update SQL has been maked with a "1=0" condition in its where clause. The destination table has a single column as key and is the LD_SEQ_I.
LD_SEQ_I is set as auto increment at a database level. Basically every record coming through the input link will need to be inserted and the database will automatically increment the LD_SEQ_I and insert it in there with the record.
What has been happening is that occation only 20-30% of the records get loaded and the remaining simply get rejected. There is no reason why the records could get rejected at all. As a temporary fix I just clean up the table and rerun the job, the records go in easily.
Success and Rejects logs are as following.
Project:XXXXXXXX
Job name:XXXXXXX
Event #:92227
Timestamp:7/31/2009 3:36:37 AM
Event type:Info
User:xxxxxxx
Message:
db2API_REF_INITEM_E,92: Records Inserted: 163
Records Used for Update: 0
Records Rejected: 0
Total Records: 163.
Project:XXXXXXXX
Job name:XXXXXXX
Event #:92231
Timestamp:7/31/2009 3:45:58 AM
Event type:Info
User:xxxxxxx
Message:
db2API_REF_INITEM_E,102: Records Inserted: 0
Records Used for Update: 0
Records Rejected: 152
Total Records: 152.
This is a daily job and it only fails like this intermittently. It occurred 10 times this month.
Any thoughts or suggestions ???