Hi,
In one of our job we have two DB2 stages as target. Both these DB2 stages have SQL statement on the same table. On certain occasion when we run the job, the job gets aborted giving a deadlock error. And on most occasions when we re-run it, it completes successfully.
The error message that we get is,
Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C9008E". SQLSTATE=57033
Is there a way to avoid this deadlock? Can we make it so that the first output link from the transformer completes fully and then the processing takes place for second output link?
Thanks.
Deadlock because of 2 DB2 stages
Moderators: chulett, rschirm, roy
If you are trying to do 2 actions on the SAME table in 1 job obviously the 2 statement will try to blow out each other.
(1) Why not to break the job into 1 separate job and then then one after
another?
(2) After Sub Routine and Before Subroutine if the SQL doesn't use
any input may also solve the problem.
I always prefer 1 action for 1 table in any job.
(1) Why not to break the job into 1 separate job and then then one after
another?
(2) After Sub Routine and Before Subroutine if the SQL doesn't use
any input may also solve the problem.
I always prefer 1 action for 1 table in any job.
You may want to check the generated osh to see if the two DB2 stages are being combined. If they both run on the same process you may get this issue.
If you want to try, put the following environment variable into your job, and set to True.
APT_DISABLE_COMBINATION=True
This will force all stages to operate in there own processes, and should help to stop 'stage blow-out'
If you want to try, put the following environment variable into your job, and set to True.
APT_DISABLE_COMBINATION=True
This will force all stages to operate in there own processes, and should help to stop 'stage blow-out'
Thanks shamshad and OddJob for your suggestions.
We tried setting APT_DISABLE_COMBINATION=True. But still we got the same error.
Actually, one out of the two SQL is just a one time delete SQL. So, we removed this DB2 stage having the delete SQL and tried giving this delete SQL in the Before SQL tab of the other DB2 stage. But we still get the same deadlock error.
So, is there a chance to get a deadlock error when a SQL is given in the Before SQL tab. Because, we were assuming that only after executing the SQL in the Before tab the actual SQL of the DB2 stage will be executed. We were hoping that these 2 SQLs will not be executed at the same moment. If such a thing will not occur, then we have to look into to see if something else is causing the deadlock error.
Thanks.
We tried setting APT_DISABLE_COMBINATION=True. But still we got the same error.
Actually, one out of the two SQL is just a one time delete SQL. So, we removed this DB2 stage having the delete SQL and tried giving this delete SQL in the Before SQL tab of the other DB2 stage. But we still get the same deadlock error.
So, is there a chance to get a deadlock error when a SQL is given in the Before SQL tab. Because, we were assuming that only after executing the SQL in the Before tab the actual SQL of the DB2 stage will be executed. We were hoping that these 2 SQLs will not be executed at the same moment. If such a thing will not occur, then we have to look into to see if something else is causing the deadlock error.
Thanks.