MVSFORUMS.com

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

Hi,
I need some help in understanding SQLCODE -913. I know its a deadlock/timeout, but I would like to know when it is issued by DB2. I am having this problem with a MQ-DB2 application running under RRS (in Batch). When does DB2 decide to issue a -913 instead of -911 ? In case of CICS it uses information from the ROLLB paramater, in this case I am finding it difficult to understand the technicals. Have any of you encountered a similar situation. Thanks

kolusu · Posted: Mon Sep 22, 2003 12:06 pm Post subject:

Suresh,

DB2 decides by the type of application to issue a -911 or 913

For TSO, Batch, and CAFWhen a deadlock or timeout occurs in these environments, DB2 attempts to roll back the SQL for one of the application processes.If the ROLLBACK is successful, that application receives SQLCODE -911. If the ROLLBACK fails, and the application does not abend, the application receives SQLCODE -913.

If you are using CICS and a deadlock occurs, the CICS attachment facility decides whether or not to roll back one of the application processes, based on the value of the ROLBE or ROLBI parameter. If your application process is chosen for rollback, it receives one of two
SQLCODEs in the SQLCA:

-911:A SYNCPOINT command with the ROLLBACK option was issued on behalf of your application process. All updates (CICS commands and DL/I calls, as well as SQL statements) that occurred during the current unit of work have been undone.(SQLSTATE '40001')

-913:A SYNCPOINT command with the ROLLBACK option was not issued.DB2 rolls back only the incomplete SQL statement that encountered the deadlock or timed out. CICS does not roll back any resources. Your application process should either issue a SYNCPOINT command with the ROLLBACK option itself or terminate. (SQLSTATE '57033')

There is a subroutine DSNTIAC to check the SQLCODE and display the SQLCA. Your application must take appropriate actions before resuming.

For IMS, the following actions take place:

1.In a DL/I batch application, the application process abnormally terminates with a completion code of 04E and a reason code of 00D44033 or 00D44050.

In any IMS environment except DL/I batch:

2.DB2 performs a rollback operation on behalf of your application process to undo all DB2 updates that occurred during the current unit of work.

3.For a non-message driven BMP, IMS issues a rollback operation on behalf of your application. If this operation is successful, IMS returns control to your application, and the application receives SQLCODE -911. If the operation is unsuccessful, IMS issues user abend code 0777, and the application does not receive an SQLCODE.

4.For an MPP, IFP, or message driven BMP, IMS issues user abend code 0777, rolls back all uncommitted changes, and reschedules the transaction. The application does not receive an SQLCODE.

Hope this helps...

cheers

kolusu

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

Thank you, the information was very helpful

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Hi Kolusu,

"In a DL/I batch application, the application process abnormally terminates with a completion code of 04E and a reason code of 00D44033 or 00D44050"

If the process terminates abnormally with a completion code of 04E and reason code 00D44033, then what would be the procedure to detect where the source of the abend is? Would you be having an idea on this?
_________________
Thanks & Regards,
Manoj.

kolusu · Posted: Wed Jan 28, 2004 11:27 am Post subject:

Manoj,

When the job terminates abnormally with a completion code of 04E and reason code 00D44033, you can find the reason from the log(s:log). Search for the message numbers DSNT375I or DSNT376I.

It would be something like this

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Hi Kolusu,

I had tried looking for messages DSNT375I or DSNT376I as also given in the DB2 Messages and Codes book. However, These messages were not available in the output dump/listing.

Thus I am not able to determine who is holding the resource. Any other thing that may help? Thanks!
_________________
Thanks & Regards,
Manoj.

Bithead · Posted: Wed Jan 28, 2004 12:06 pm Post subject:

The messages appear in the DB2 master address space.

This should give you some information:

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Bithead,

Should I run the above job?
_________________
Thanks & Regards,
Manoj.

Bithead · Posted: Wed Jan 28, 2004 12:10 pm Post subject:

You can try. It is just a report program. Check the name of the IMS Reslib (STEPLIB).

kolusu · Posted: Wed Jan 28, 2004 12:57 pm Post subject:

Manoj,

You can access the DB2 master log from your SDSF. choose option S;DA. Type in pre DB2* and hit Enter. You will find a job DB2PMSTR running. You just can browse the job .But let me inform you that this might be shop specific. But most shops keep the same naming standard.

If you are unable to see the DB2 master job, then talk your DBA or IMS system programmer and ask him about the DB2 master log.

Hope this helps...

Cheers

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

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Hi Kolusu/Bithead,

I could not see any DB2PMSTR jobs running or for that matter any DB2 jobs running. I guess I dont have the authority. Awaiting responses from my DBA's...

The output log is huge and could get it to the TEST system only as 15 different files. Now to run the above job, can I do something like this below or do I have to concatenate it first.

Bithead · Posted: Wed Jan 28, 2004 4:40 pm Post subject:

Try running it against the last log file and work backwards. If the job had performed no commits but had many updates, then this file will be full of backout records, otherwise this is where the deadlock will be.

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Hi,

Just got back form my DBA and found out who was in contention with the job. It is the DBA that gave me the info for DSNT376I. Made everything clear.

How I hope I could have somehow managed to see the DSNT376I message myself. Rolling Eyes

Thanks bithead and Kolusu for your help!! I shall still try running the job and have a look at what I get. Thanks!
_________________
Thanks & Regards,
Manoj.

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Hi,

I tried running the jobs against each of the files above. For each of the files I got exactly the same SYSPRINT message as below:

Bithead · Posted: Thu Jan 29, 2004 5:28 pm Post subject:

It is saying that it can't find the abend record. I have given you the format to pull off the type 67 subtype FF record which is the Pseudoabend (0777) / dependant region abend. The other card is looking for the string "DEADLOCK". Both of these must be found to get a hit. I can only assume that one of these is not present.

Try with the following card to get all 67FF log records

OPTION PRINT O=5,T=X,V=67FF,L=2

If this fails, try with the following card to get all 67 trace records

OPTION PRINT O=5,T=X,V=67,L=1

You should be able to concatenate a number of files together, but I would not use all of them because the process will run for a log time.