Joined: 25 May 2007 Posts: 61 Location: Coimbatore
In my project nearly 250 jobs are running daily night cycle. In that two jobs like J0B1 and JOB2 are running the same time.
Most of the time one of the job got abended due to contention with another job (SQL return code as -911)
I think it get abending due to Dead Lock, (REASON Code: 00C90088).
The both jobs using the same program with same plan.
In that program Update and Delete Query is there, so one of the job may hold the table with X (Exclusive Lock), that's what it getting abended.
What are the possible way to solve this issue.
I have some solutions,
1. Making dependency with one another (But it may delay my batch
2. Changing the bind parameter (Lock Parameters)
they already using the following parameters,
Plan : PLAN1
Owner : USER007
Valdat : bind
Isolat : CS
Valid : Alt
Operat : Yes
Acq : use
Rel : dea
Bound : 2007-07-19
So i guess it is an perfect isolation parameters (CS). I don't think this will help to solve. i thought to change the Release parameter as commit but it will make more I/O operations.
3. Retry logic, suppose if it gave the return code as -911 i just release the locks and wait for some time and then call the same paragraph again in the program.
4. Otherwise if it gave the return code -911 then set the RC as 2049 and stop the program. In the JCL if the RC = 2049 then i try to execute the previous step again.
This are ways i am thinking to solve. But if any thing else is there to solve this issue please let mt know. because this is more high priority work in my project.
Please post your comments on my solutions and your ideas as soon as possible...