We have a contention issues b/w 2 jobs job1 and job2.

Job1 :- Has a program pgm1 which updates 1 row of database at a time and commits it.

Job2 :- It has a program pgm2 which opens a cursor with fetch only option and fetches a max of 4000 rows into an array and then closes the cursor.

These 2 jobs use the same database. And we are pretty sure that they don't refer to same row (ie 4000 row selected by fetch in pgm2 are not the rows used by pgm1 for update). But contention happens closing the cursor in pgm2 and job2 abends.

What could be the cause of this abend ? This abend happens very rarely only.

We are using DB2 ver 7.

Can this be prevented by : when ever contention happens we will call a delay of few seconds and then try opening the cursor again and populate the array again from the start. This can be allowed to happen for max of 10 times and even then if the fetch is not successful we will abend the job.

or is there any other better method for solving the abend.


Thanks a lot
Praveen

what is the error message?

Hi,

The cursor gets closed due to -913 and subsequent fect returns -501 and job abends.


000090 DSNT375I -DP21 PLAN=PGM2 WITH 515
000090 CORRELATION-ID=JOB2
000090 CONNECTION-ID=BATCH
000090 LUW-ID=NETPRG.PDB2DIST.BA0D47D1B8C4=152759
000090 THREAD-INFO=CA7:*:*:*
000090 IS DEADLOCKED WITH PLAN=PGM1 WITH
000090 CORRELATION-ID=JOB1
000090 CONNECTION-ID=BATCH
000090 LUW-ID=NETPRG.DP23DIST.BA0D47CEB8E8=86254
000090 THREAD-INFO=CA7:*:*:*
000090 ON MEMBER DP23
000090
000090 DSNT501I -DP21 DSNILMCL RESOURCE UNAVAILABLE 516
000090 CORRELATION-ID=JOB2
000090 CONNECTION-ID=BATCH
000090 LUW-ID=*
000090 REASON 00C90088
000090 TYPE 00000302
000090 NAME DPPFF.MBCG.X'00000D'


SQL Error Timestamp. 2003-09-20-11.07.16.635712
Subsys.............. DP21 User............ CA7
Plan................ PGM1 Correlation ID.. JOB1
Program............. PGM1 Connect ID...... BATCH
Program Type........ PKGE Location........ PDB2DDF
Collection ID....... PROD_COL1 Contoken........ 173337CA1B345A39
Version...

Statement Type...... STATIC Statement Number.... 781
Call Type........... FETCH

DSNT408I SQLCODE = -913, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY
DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE
00000302, AND RESOURCE NAME DPPFF.MBCG.X'00000D'
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR

It appears the "for fetch only" clause on the cursor either isn't there or the package is bound with RR or some other restrictive scope. Second, the resource with the problem is page 13 of DPPFF.MBCG