DB2 SQLcode -911

dsbhati · New User Joined: 13 Jun 2005 Posts: 10 Location: india

I am regularly getting SQL Code -911 in production , my suspect is that same time batch and online is using the tables. what could be permanent solution ?
could any body give me same suggestion?.

Thanks and regards
Darshan

kirankumar.k · New User Joined: 21 Jun 2005 Posts: 1

Please check this: ibmmainframes.com/references/a2.html#-911

MGIndaco · Posted: Tue Jun 21, 2005 1:55 pm

In which kind of operation you have -911?

sudheer648 · New User Joined: 23 May 2005 Posts: 97 Location: Chennai

In DB2, an SQLCODE of -911 indicates that a deadlock or timeout has occurred. This error typically arises when two or more processes are waiting for resources that the other processes have locked, leading to a situation where none of the processes can proceed.

Explanation of SQLCODE -911

Description: Deadlock or timeout.
Reason: The transaction has been rolled back due to a deadlock or a timeout condition.
SQLSTATE: 40001 (Deadlock), 57033 (Timeout)

Common Causes

Deadlock:
This occurs when two or more transactions are waiting for each other to release locks, creating a cycle of dependencies with no possibility of resolution without intervention.

Timeout:
This occurs when a transaction waits too long for a resource that is locked by another transaction.

Resolution Steps

Analyze the Application Logic:
Review the application code to identify and resolve potential deadlock scenarios.
Ensure that transactions acquire locks in a consistent order to minimize deadlocks.

Retry Logic:
Implement retry logic in the application. If a transaction fails due to a -911 error, the application can retry the transaction after a short delay.

Increase Lock Timeout:
Adjust the lock timeout settings to allow more time for transactions to acquire necessary locks before timing out. However, this is a temporary fix and does not address the root cause.

Improve Transaction Design:
Shorten the duration of transactions to hold locks for a shorter period.
Break large transactions into smaller ones to reduce lock contention.

Use Appropriate Isolation Levels:
Choose the appropriate isolation level for your transactions. Lower isolation levels (e.g., Read Committed) can reduce locking but may lead to other issues like dirty reads.

Monitor and Tune the System:
Use DB2 monitoring tools to identify and resolve locking issues.
Analyze the database logs and monitor for frequent deadlocks or timeouts.

kanak · Moderator Joined: 12 Mar 2005 Posts: 252 Location: India

Manual Says

-911 ==> THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name

What Sudheer has said is correct.

chiranjeev · New User Joined: 20 Jun 2005 Posts: 2 Location: MYSORE

permanent sollution can be , if u have autority u can
reschedule the time of batch jobs in CNTRL+M.
this abend is due to resource contention as pointed out by kANAK AND SUDHEER.

dsbhati · New User Joined: 13 Jun 2005 Posts: 10 Location: india

Hi all,

thanks for ur help i know the reason is resource contention. and also running of batch and online program at same time of using same table. But changing batch job timing is not a solution coz again we don't know when will online transcation can happen.

Could anybody help me out regarding this??
Can we check -911 in program and use delay(time) cics command??
or in batch can commit help in this case after updation???

Thanks & Regards,
Darshan

sree_2503 · Posted: Thu Jun 23, 2005 9:58 am

Hi,

There might be two cases from this lead -

1) Either ur online program is calling a subroutine where the cursor is fetching the rows from the same table as being used by the Batch program.

2) The Online program directly retrieves rows from the table.

For both the cases, u can use

EXEC CICS
DELAY INTERVAL(NN)
END-EXEC

say, NN=02,03...

before the fetch operation. But in case no 1, u can make use the call type based on Synchronous/Asynchronous and then can use the delay command.

Hope this helps.

dsbhati · New User Joined: 13 Jun 2005 Posts: 10 Location: india

THANKS SREE

COULD U TELL ME ABOUT IST CASE ,MEANS WHAT SYNCHRONOUS/ASYNCHRONOUS CALL??

Thanks
Darshan

basic · New User Joined: 15 Feb 2006 Posts: 2

Can any one show example how to overcome Please

basic · New User Joined: 15 Feb 2006 Posts: 2

Sorry again iam using tELON COBOL IMS DB2 / DC programs , could you help in overcome this

robertolima · New User Joined: 15 Feb 2006 Posts: 1

Try COMMIT for each 100 rows (Insert or Update).

ragshere · New User Joined: 20 Dec 2004 Posts: 70

Hi,
Hope this will work

1. change the lock size to 'ROW' for all the tablespaces( tables) used in batch job program. here LOCKSIZE is a tablespace property.

2. do a RUNSTATS on all tabalespaces(tables) used by the batch program and mainly tables on which contention occured.

3. bind the batch program package/plan with isolation level CS and
deallocate bind parameter as COMMIT.

You need to do all these 3 steps. then you won't hit with -911.

Thanks
Rags