I have a simple job like below
Code: Select all
Data set --> Transformer --> DB2 connector (Update)
We are processing 60 million records and updating on 5 part natural keys.
We are using DB2 connector (Partitioning is set to DB2 connector). Transaction record count = 6000 and Array size = 2000. No sorting done on any keys.
If I have to come to table, table is partitioned by one of the date fields (this field is not in any index). Also we have index that was created on the 5 part natural key that we are updating (Also we had 32 index on this table). So here are the couple of things we have tried but not succeed yet. This is in production.
1. Initially when we tried to update, it was updating 700 rows/sec. So we have dropped couple of index and tried but no use.
2. We have taken out the Foreign key relation and tried but no use.
3. We have removed the partition on the table and tried but no use (Interesting thing is in development at least in this scenario when table is not partitioned we were getting 5000 rows/sec. After partition it was doing 2500 rows/Sec).
4. We did the explain plan on the query and then find that index did not had any over head on the update.
I have tried reading through the forum and found couple of threads with this case but wasn't able to find the answer. Any suggestions I would really appreciate.