Handling -911 and -913 in CICS pgms

madhavan

Handling -911 and -913 in CICS pgms
Hi,

In CICS programs after executing a sql statement, we have the following
requirement if we get a sql code of -911 or -913:

Try executing the sql 3 times to see if the deadlock continues. If yes, give
the control back to the user in the same screen with a message. User is
allowed to try the same operation again. Basically we are not terminating
the application.
I guess we should cursor fetch sqls update sqls differently in these cases.

Please let us know the implications of this.
Did any of you have to code like this in CICS applications?


regards
Madhavan.



Jim Knisley

Re: Handling -911 and -913 in CICS pgms
(in response to madhavan)
We code batch update programs with retry logic, however we do not code online
CICS transactions with retry logic. To ensure that we meet service level
agreements, we have set the IRLM wait parameter in ZPARM and the deadlock
parameters in the IRLM started task so that the timeout occurs within a few
seconds instead of the IBM default of 60 seconds.
Our philosophy is that if a deadlock or timeout is going to occur in an online
environment, lets get it over with as soon as possible to free the resources.
If a long running transaction or a batch program has hold of the resources that
you need, you are not likely to have a successful retry anyway. Plus, if your
transaction is holding other resources that another transaction coming in needs,
you will deadlock or timeout that transaction. If you retry that second one
while you are still retrying the first one and the second one is holding
resources that a third transaction needs ......... As you can see, it can have
a cascading effect and you can quickly tie up a lot of resources. By giving the
map back to the user quickly with a message that the transaction failed due a
timeout and please press enter to retry, it gives them the illusion of better
response time, instead of retrying the query and then having the transaction
fail anyway after they have waited with a system clock.
As long as you are running pseudoconversational, putting the map back out to the
user and letting them press enter to retry the transaction has no impact on
resources because the DB2 resources will have been released.
We have the deadlock cycle at 5 seconds because we do have a couple of
search/browse type transactions that run a couple of seconds or I would set the
timeout and deadlock values even lower. Most everything else online is
subsecond.
*** As with everything else, this is what our installation has choosen and works
best for us in our busy CICS region that also has batch work that runs against
the same DB2 tables during the day. It may not be the best approach for your
shop if you have different requirements.
Hope this helps,
Jim




madhavan <[login to unmask email]> on 12/15/99 11:08:01 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Jim Knisley/TFG/HII)
Subject: Handling -911 and -913 in CICS pgms



Hi,

In CICS programs after executing a sql statement, we have the following
requirement if we get a sql code of -911 or -913:

Try executing the sql 3 times to see if the deadlock continues. If yes, give
the control back to the user in the same screen with a message. User is
allowed to try the same operation again. Basically we are not terminating
the application.
I guess we should cursor fetch sqls update sqls differently in these cases.

Please let us know the implications of this.
Did any of you have to code like this in CICS applications?


regards
Madhavan.








Roger Miller

Re: Handling -911 and -913 in CICS pgms
(in response to Jim Knisley)
If you received a -911, then there is only one place to retry - from the
beginning of the application.
You are not holding any locks and are likely to cause data loss. In any
case, I would push for
this option whenever possible.

For the -913 case, there are two options. If this was just a timeout, you
can try to wait longer than
the lock parameters, longer than the other unit of work. If this is a
deadlock, then you are hoping
that the other unit of recovery does not retry. For one of the most common
cases - deadlocking
with the same program or one that is coded the same, what the retry
accomplishes is to take
a small problem and make it bigger. The worst example I have seen made a
little mistake in
the retry logic and looped until the transaction was cancelled when the
application got a
different SQLCODE from the one expected.

I'd also like to recommend two excellent articles by Bonnie Baker in the
DB2 Magazine.

http://www.DB2mag.com/98fprog.html
http://www.DB2mag.com/98w_prog.shtml

There is also a chapter in Application Programming and SQL Guide titled
Planning for
Concurrency. I'd say that spending more time avoiding locking problems is
more valuable
in general than time on retry.

Roger Miller


madhavan <[login to unmask email]>@RYCI.COM> on 12/15/99 08:08:01 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Handling -911 and -913 in CICS pgms



Hi,

In CICS programs after executing a sql statement, we have the following
requirement if we get a sql code of -911 or -913:

Try executing the sql 3 times to see if the deadlock continues. If yes,
give
the control back to the user in the same screen with a message. User is
allowed to try the same operation again. Basically we are not terminating
the application.
I guess we should cursor fetch sqls update sqls differently in these cases.

Please let us know the implications of this.
Did any of you have to code like this in CICS applications?


regards
Madhavan.








Jim (C) Chie

Re: Handling -911 and -913 in CICS pgms
(in response to Roger Miller)
Remember, if you get a -911 the application has ALREADY rolled back to the
last sync point, in which case any other updates will be lost. I can't
remember if changes to CICS or other resources (such as MQ) are also backed
out, but you should check before implementing retry logic.
A -913 on the other hand is designed to return control to you BEFORE
rollback. It does, however close your cursor, possibly making retry hard.

-----Original Message-----
From: madhavan [mailto:[login to unmask email]
Sent: Wednesday, December 15, 1999 8:08 AM
To: [login to unmask email]
Subject: Handling -911 and -913 in CICS pgms


Hi,

In CICS programs after executing a sql statement, we have the following
requirement if we get a sql code of -911 or -913:

Try executing the sql 3 times to see if the deadlock continues. If yes, give
the control back to the user in the same screen with a message. User is
allowed to try the same operation again. Basically we are not terminating
the application.
I guess we should cursor fetch sqls update sqls differently in these cases.

Please let us know the implications of this.
Did any of you have to code like this in CICS applications?


regards
Madhavan.