DSXchange

We have created a job which was loading about 5,000 records per second. But suddenly its loading only 10-12 records per sec. When we looked into the log for the job we found the following error. Can any body help us in this.

Part_Dimension_Loading_Final_3..DB2_UDB_API_1: This update action 'update or insert' is not compatible with parameter array binding. Array Size property will be set to 1.

Hi,

You might have change the value of Parameter array size of transaction Handling tab in target stage. It Specify the number of rows written at a time. The default value is 1.

Thanks,
Anupam

For update existing or insert new rows , you need the array size to be set to 1.

There is a relationship between rows-per-transaction and parameter array size that must be maintained.

I could be wrong, but 5000/s is pretty darned fast (excuse my dockyards language) for an Update Else Insert job. Is it possible that it used to be Insert Only or Update Only and has recently been changed?

My experience is with Oracle, not DB2, but I have found Array Size / Transaction Size ineffective when using "Update Else Insert" or "Insert Else Update" because the logic to perform the second command if the first fails forces it to run one transaction at a time.

ray.wurlod wrote:There is a relationship between rows-per-transaction and parameter array size that must be maintained. ...

Hi,
Would you Please tell us the relationship between rows-per-transaction and parameter array size?

Perhaps the answers on the other active thread on this issue would be a good place to look.