DSXchange

We have a situation where a parallel job (inserting data from data set to DB2) is set as a multiple instance job writing to the same table. Up to 4 instance might be running at the same time. Each data set contains around 100,000 records

the job structure is:

Data set --------- Key generator -------- DB2


The instances sometimes abort due to deadlocks, which we suspect is because of another instance locking the table which doing its insert.

We wanted to use array size and row commit level to control how often the job to commit the data. But the problem is, as we are setting the write method to be "Write" as this is an insert only job, there is no option to set the array size. We have tried to set the row commit level only but the job still encounter deadlock sometimes when multiple instances are running.

Right now, we are setting the row commit level to 1 and we have yet to encounter deadlock. But the performance is too slow.

Is there any method to set the array size as well?

Thanks

any help is appreciated

Set array size to 1 is the safest when multiple instances are running, and set rows/transaction to 1 also. That way you will not experience lock (or deadlock) problems.

ray.wurlod wrote:Set array size to 1 is the safest when multiple instances are running, and set rows/transaction to 1 also. That way you will not experience lock (or deadlock) problems. ...

But the problem is, there is no option for me to set array size. Only Row commit level.

something like this:

When Write Method property is set to Write, you are streaming rows into the database to be written directly into the table. Array size is not relevant.