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
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.
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.