Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    India
    Posts
    2

    Unanswered: db2 contention issue

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Bangalore, India
    Posts
    28
    what is the error message?

  3. #3
    Join Date
    Dec 2003
    Location
    India
    Posts
    2
    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

  4. #4
    Join Date
    Oct 2003
    Posts
    87
    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
    Oracle - DB2 - MS Access -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •