Deadlock message - identifying the holder from DSNT376I

Ram Ramaiyan

Deadlock message - identifying the holder from DSNT376I

Hello Experts, 

Could you please share how we can get the information of the holder incase of timeout or a deadlock when the thread information has '*'(asterisk)

Message for DSNT376I says 

An asterisk (*) in any segment indicates that the information is not available.

Kindly refer https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/msgs/src/tpc/dsnt376i.html

Sample alert shared below

DSNT376I -DXX2 PLAN=DSNJCCUP WITH
CORRELATION-ID=BBOXXX1S
CONNECTION-ID=RRSAF
LUW-ID=SEERIN01.DB2CDXX2.D46D9D2E6D28=133559

THREAD-INFO=RRRST:RRSAF:RRRST:db2jcc_application:STATIC:611791:*:
*
IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNJCCUP
WITH
CORRELATION-ID=BBOXXX1S 
CONNECTION-ID=RRSAF
LUW-ID=SEERIN01.DB2CDXX1.D46D9CF8A8DB=132452
THREAD-INFO=RRRST:RRSAF:RRRST:db2jcc_application:*:*
:*:*
ON MEMBER DXX1 
DSNT501I -DXX2 DSNILMCL RESOURCE UNAVAILABLE
CORRELATION-ID=BBOXXX1S
CONNECTION-ID=RRSAF
LUW-ID=SEERIN01.DB2CDXX2.D46D9D2E6D28=133559
REASON 00C9008E
TYPE 00000302
NAME DBNAME.TSNAME.X'0068185F'

Would appreciate your time and leads. Thank you!

-Ram R.

 

Venkat Srinivasan

RE: Deadlock message - identifying the holder from DSNT376I
(in response to Ram Ramaiyan)

c9008e is a timeout so IFCID 196 will trace this. If you have Omegamon, Locking report level lockout will report the detail.

Work from two Websphere(BBO...) address spaces connecting to two different members was involved in a timeout.  

Venkat

Walter Janißen

AW: Deadlock message - identifying the holder from DSNT376I
(in response to Venkat Srinivasan)
Hi

But I think, it doesn’t show more than in the message already appeared. I think, if asterisks occur, Db2 is for some reason not able to provide more.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Venkat Srinivasan <[login to unmask email]>
Gesendet: Dienstag, 5. Juni 2018 16:39
An: [login to unmask email]
Betreff: [DB2-L] - RE: Deadlock message - identifying the holder from DSNT376I


c9008e is a timeout so IFCID 196 will trace this. If you have Omegamon, Locking report level lockout will report the detail.

Work from two Websphere(BBO...) address spaces connecting to two different members was involved in a timeout.

Venkat

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Ram Ramaiyan

RE: Deadlock message - identifying the holder from DSNT376I
(in response to Venkat Srinivasan)

Hi Venkat, Thanks for your response.Yes, we have several occurrences of Timeouts and Deadlocks. There must be some solution or a ptf which should help the holder information(batch,static,dynamic etc.,) tracked in the mstr logs? We do not have Omegamon in our shop, In parallel I will check with CA if Insight or Detector can help here.

Regards,

Ram R.

Chris Tee

Deadlock message - identifying the holder from DSNT376I
(in response to Ram Ramaiyan)
Ram


The command D LOCKCONT in Insight will show you the timeout and deadlock details.


regards


Chris


________________________________
From: Ram Ramaiyan <[login to unmask email]>
Sent: 05 June 2018 15:47
To: [login to unmask email]
Subject: [DB2-L] - RE: Deadlock message - identifying the holder from DSNT376I


Hi Venkat, Thanks for your response.Yes, we have several occurrences of Timeouts and Deadlocks. There must be some solution or a ptf which should help the holder information(batch,static,dynamic etc.,) tracked in the mstr logs? We do not have Omegamon in our shop, In parallel I will check with CA if Insight or Detector can help here.

Regards,

Ram R.

-----End Original Message-----

Venkat Srinivasan

RE: Deadlock message - identifying the holder from DSNT376I
(in response to Ram Ramaiyan)

The information you miss from that of a typical jcc timeout is the client tokens. It was not posted as part of the thread. Do you know if you are using zos application connectivity. is there a statement in syspackstmt for 611791?

ca insight does have batch capability.

The trace record will have the correlation header and thats all that you would get in addition to the locking timeout data.

The timeout data will have the stmt id and a flag indicating dynamic or static for the waiter and holder. if it is dynamic the stmt id is the stmt id in sqlcache. It is somewhat difficult unless you probe the statement soon after the event before the cached stmt is discarded. if it is static it is somewhere in packstmt but you should know where to find by looking at the packages involved.

Venkat

Ram Ramaiyan

RE: Deadlock message - identifying the holder from DSNT376I
(in response to Chris Tee)

Thanks Chris. SQL Statement Retrieved From DB2 Catalog is blank. Can you please tell me if you are able to view the SQL of holder/victims.

 

Regards,

Ram R.



Use F6 to perform a dynamic EXPLAIN.

Plan . : Statement Number: 000000000 Degree . :
Status : N/A OptHints : NO
Program:
Coll ID:
Version:
Path . :

Ram Ramaiyan

RE: Deadlock message - identifying the holder from DSNT376I
(in response to Venkat Srinivasan)

Thanks Venkat. Victim information(with SQL) is available.We are concerned about tracking the holder to prevent this from re-occurring. 

Regards,

Ram R. 

Venkat Srinivasan

RE: Deadlock message - identifying the holder from DSNT376I
(in response to Ram Ramaiyan)

Since you know the pageset, can you not go to detector and drill down the sqls on the member dxx1. If you know the holders lock state, it is easier to weed thru the statements. 

Venkat