UDB API stage deadlock or timeout Reason code SQLSTATE=57033

[mary]

Hi All....

I have one multi instance parallel job which uses DB2 UDB API stage to insert in to one table. This job is having a before sql to delete the records based on the where clause.

This job is run with parameter values so that for one run it will delete a specified set of records and insert the new records for that category.

this job is ran with two set of parameters for mulually exclussive categories of records. If we run the job with each of this parameters , sequentially the job goes fine(means one job at a time).But we are getting deadlock or timeout issue if we run this job for both of the cateories of records at the same time.

We tried with the execution mode for the API stage as parallel and sequential . Both the time we got the same issue. This issue we can solve by scheduling so that one set of job should run only after the other. But is there any other way from Datastage to solve this issue?

Thanks in Advance.

[bart12872]

When you delete in the table, there is an exclusive lock on the table, and you can't execute another delete statement on the same table, even if the set of records are differents.

If you partitionned the table you should execute your delete statement in parallel.

You could also create a specific delete job before the jobs of insert. With one unique delete stament.

[Sainath.Srinivasan]

Surprising that the delete will go for table level lock rather than row level. Maybe it is escalating because of low memory.

Try to monitor with help of your DBA.

[mary]

Is this lock is having any relation to the array size and tarnsaction size?

In our job we are using array size=50 and tarnsaction size=100.

[Sainath.Srinivasan]

Log the ids to a sequential file and do a diff to confirm the exclusiveness.

Check the memory usage and locking with your DBA.