Handling deadlock situation in COBOL-DB2 program

anbu1 · New User Joined: 28 Sep 2006 Posts: 1

I am trying to introduce retry logic in a cobol-db2 program to handle contention abends. The cobol program currently does not have a committ restart logic. If I introduce a retry every 10seconds when a deadlock occurs, do I need to do an ' Explicit committ' in the program? What will happen to the cursors that are already open?

If contention occurs while updating a table, will the same data be used after the retry?

raghunathns · Posted: Fri Sep 29, 2006 7:01 pm

use commit with hold option to keep the cursor to keep in open mode.

zOS_Uber_Geek · Posted: Sat Sep 30, 2006 8:20 pm

Hi,

I recently did some work like this on a program. It is true that when you issue a COMMIT, the cursors are closed. My program was fairly straightforward so I just re-opened the cursor after the COMMIT was issued and began processing again. What we do where I work is use a DB2 table to store information about the last record we updated. For instance, we make an entry in the table after a COMMIT that goes something like this:

MOVE (account number that was last updated) TO WS-BATCH-COMMIT-RECORD(1:10)
MOVE (company number of the last account) TO WS-BATCH-COMMIT-RECORD(11:5)

SELECT *
FROM XY.BATCH_COMMITS_TABLE
WHERE BATCH_COMMIT_ID = (program name)
WITH UR

IF SQLCODE = DB2EOT
INSERT INTO XY.BATCH_COMMITS_TABLE
(BATCH_COMMIT_ID, BATCH_COMMIT_RECORD)
VALUES ('program name', :WS-BATCH-COMMIT-RECORD)
ELSE
UPDATE XY.BATCH_COMMITS_TABLE
SET BATCH_COMMIT_RECORD = :WS-BATCH-COMMIT-RECORD
WHERE BATCH_COMMIT_ID = 'program name'
END-IF

At program re-entry:

SELECT *
FROM XY.BATCH_COMMITS_TABLE
WHERE BATCH_COMMIT_ID = 'program name'
WITH UR

IF SQLCODE = DB2ZERO
MOVE WS-BATCH-COMMIT-RECORD(1:10) TO WS-ACCOUNT-NUMBER
MOVE WS-BATCH-COMMIT-RECORD(11:15) TO WS-COMPANY-NUMBER
OPEN CUSTOMER_FORWARD_CURSOR
ELSE
* start at the beginning of the table
MOVE 0 TO WS-ACCOUNT-NUMBER
MOVE 0 TO WS-COMPANY-NUMBER
OPEN CUSTOMER_FORWARD_CURSOR
END-IF

Keeping track of where we last committed data, helps speed up the processing and also allows us to restart jobs if they go down due to deadlocks or other SQL errors without having to worry about where we left off. When the program is restarted, it looks for an entry in the BATCH COMMITS TABLE and starts processing from that point on so DB2 does not have to look at all the data up to that point. Does this make sense?

We also keep a counter in our retry logic and attempt to retry the DB2 operation three times before giving up and letting the program abend on its own. This is done to save processing cycles so that we don't have a program that becomes hung up out in the system.

I hope this helps you out.