MVSFORUMS.com

Hema_Manyam · Beginner Joined: 15 Dec 2005 Posts: 20 Topics: 9

Hi,

Is there a delay command in DB2 ?

Suppose if I have batch DB2 pgm, and if I encounter deadlock then I want to delay for 3 min and then again perform the same statement to avoid deadlock. How is it possible to delay in db2 ?

Thanks
Hema

kolusu · Posted: Thu Dec 29, 2005 5:45 am Post subject:

Hema_Manyam,

There is no concept of delay in DB2. However you have a delay funtion in cobol. check this link

http://mvsforums.com/helpboards/viewtopic.php?t=2008&highlight=delay

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Manas Biswal · Posted: Thu Dec 29, 2005 10:11 am Post subject:

Hema_Manyam,

There are a lot of different scenarios that you need to consider when handling deadlocks in DB2. Most of the time, you really have no control over deadlocks, and DB2 will automatically rollback one of the transactions involved in a deadlock.
DB2 deadlocks should be avoided and you can do various things both at an application level and at the database configuration level to minimise deadlocks. But as far as "handling" deadlocks is concerned, DB2 does most of the work and the application has limited control over it.

When DB2 detects a deadlock, two scenarios can potentially happen to your application -
1. DB2 attempts to rollback the SQL and the UOW of the current application. If the rollback is successful, then the application abends with a SQLCODE of -911. You have to redo the transaction or the job again. You cannot retry the SQL within the same transaction or job.
2. If the attempt to rollback fails, then DB2 throws the -913 SQL exception, which you can capture from within your application and retry the transaction (after the delay that you mentioned).

Again, the above two scenrios depend on the interfacing application. The behavior id different for TSO BATCH, DLI Batch, IMS and CICS applications accessing DB2.
Sometimes, your application can control and resume/retry after a deadlock and sometimes it can't. I would suggest that you go through the DB2 for Z/OS manual before coding for deadlocks.
You can use the below as a starting point -
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.apsg/bjnqmstr283.htm

HTH....Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

a_seshu · Posted: Fri Dec 30, 2005 12:40 pm Post subject:

Hema_Manyam

Think this way.....

Your program encountered a deadlock at a particular point... You end up getting an sql code of -911 or -913. All the work done in your program in that LUW (Logical Unit of Work) would have been rolled back by then. Now do you think there is any advantage doing a delay and reattempting the same operation???

Take this example

You have a sequence of three updates to perform a process for an update.

Hema_Manyam · Beginner Joined: 15 Dec 2005 Posts: 20 Topics: 9

Thanks Manas & a_seshu. I realise, I am out of my mind when I am posting this question. Here is the reason why it happened.

Currently I am also working in IDMS, here when deadlock happens we close all files, rebind the areas and then use delay command to try again. Hence I was thinking in that mode and have raised this query. Later I realised that it is not possible to do db2 bind inside the program similar to IDMS.

Thanks guys for your detailed explanation.

Regards,
Hema.