SYSVIEW for DB2 BTLKCONT Report - Lock Timeouts and Deadlocks / Stmt ID field conversion

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

In the BTLKCONT Deadlock Report the Stmt ID field is displayed as follows: 

 

Lock Resource  00000270.00000108                                        Type TABLE   
Holding task Lock mode   IS         Duration   COMMIT                              
ictim *   Auth        CA7JK      Member     SSID       Stmt ID  000000000026D2CB
          Plan        DSNPLN01   Connection BATCH      Corr ID  USER01        
          Network     CA01       LU name    XXXXSSID   Instance DE57C6CAE6CE    
          Program     PROGR01    Collection COLL01     Location                 
          EndUser     USER01                                                     
          Workstation BATCH                                                     
          Transaction USER01                                                  

If the Stmt ID value X'26D2CB' is converted to decimal it has no meaning to check the Db2 Catalog table SYSIBM.SYSPACKSTMNT and display the SQL statement that was the cause of the deadlock. 

Cause

 

 

Resolution

STATEMENT ID is something of a token. While the statement exists in the dynamic or static statement cache, the statement id can be used to look up the 
statement in the cache, if dynamic, or in the Db2 Catalog if static.     

STATEMENT ID is different from STATEMENT NUMBER, which is the actual number recorded in SYSIBM.SYSPACKSTMT table. IBM tends to record the statement id rather than the statement number in trace records. Some of the older ones, such as IFCID 58 (SQL execution) still report the number.

To see the difference online go to option 10.3 and select a statement.

Notice that the STATEMENT ID is 132474A and the STATEMENT NUMBER number is 100.

To collect the STATEMENT ID the IFCID 400 must be started to have DB2 collect statistics for static SQL. To enable IFCID 400, you can either set 
IQL-INCLUDE-IFCID400=YES in the data collector SYSPARMS, or include IFCID 401 in the HIST-STATS-RECS SYSPARM parameter, which will also cause the data collector to save static SQL statistics in the online history files. 
  
As for the STATEMENT ID, it looks to be a column in SYSIBM.SYSPACKSTMT table (name is STMT_ID) at least at newer releases of Db2. So, you should be able to look up a statement using it.