Support for monitoring SET CURRENT LOCK TIMEOUT related Db2 changes
Several instrumentation changes were introduced with the Db2 13 feature SET CURRENT LOCK TIMEOUT that are covered by OMEGAMON for Db2® PE.
You can find the changes in data section QXST (IFCID 2 and 3 and 148) as well as in the Lock timeout and Deadlock IFCIDs (172/196).
In addition, a brand new IFCID 437 is written after a SET CURRENT LOCK TIMEOUT statement is issued from an application or a profile.
BATCH reporting support
The BATCH ACCOUNTING and STATISTICS REPORT and TRACE report sets, as well as the RECTRACE report sets are enhanced to show the new metrics. PDB and CSV support for ACCOUNTING and STATISTICS is also available.
SQL DML AVERAGE TOTAL SQL DCL TOTAL
-------- -------- -------- -------------- --------
SELECT 0.00 0 LOCK TABLE 4
INSERT 0.00 0 GRANT 0
ROWS 0.00 0 REVOKE 0
IAG1 0.00 0 SET CURR.SQLID 0
IAG2 0.00 0 SET HOST VAR. 0
UPDATE 0.00 0 SET CUR.DEGREE 0
ROWS 0.00 0 SET RULES 0
MERGE 0.00 0 SET CURR.PATH 0
DELETE 0.00 0 SET CURR.PREC. 0
ROWS 0.00 0 SET TIMEOUT 3
FROM APPL. 1
DESCRIBE 0.75 9 FROM PROF. 2
DESC.TBL 0.00 0 CONNECT TYPE 1 0
PREPARE 0.75 9 CONNECT TYPE 2 0
OPEN 0.00 0 SET CONNECTION 0
FETCH 0.00 0 RELEASE 0
ROWS 0.00 0 CALL 0
CLOSE 0.00 0 ASSOC LOCATORS 0
ALLOC CURSOR 0
DML-ALL 1.50 18 HOLD LOCATOR 0
FREE LOCATOR 0
DCL-ALL 7
SQL DCL QUANTITY /SECOND /THREAD /COMMI
--------------------------- -------- ------- ------- ------
LOCK TABLE 12.00 0.05 0.03 0.0
GRANT 0.00 0.00 0.00 0.0
REVOKE 0.00 0.00 0.00 0.0
SET HOST VARIABLE 75.00 0.31 0.18 0.0
SET CURRENT SQLID 68.00 0.28 0.16 0.0
SET CURRENT DEGREE 0.00 0.00 0.00 0.0
SET CURRENT RULES 0.00 0.00 0.00 0.0
SET CURRENT PATH 0.00 0.00 0.00 0.00
SET CURRENT PRECISION 0.00 0.00 0.00 0.00
SET CURRENT LOCK TIMEOUT 8.00 0.03 0.02 0.01
FROM APPLICATION 5.00 0.02 0.01 0.00
FROM PROFILE 3.00 0.01 0.01 0.00
CONNECT TYPE 1 0.00 0.00 0.00 0.00
CONNECT TYPE 2 0.00 0.00 0.00 0.00
RELEASE 0.00 0.00 0.00 0.00
SET CONNECTION 0.00 0.00 0.00 0.00
ASSOCIATE LOCATORS 0.00 0.00 0.00 0.00
ALLOCATE CURSOR 0.00 0.00 0.00 0.00
HOLD LOCATOR 0.00 0.00 0.00 0.00
FREE LOCATOR 0.00 0.00 0.00 0.00
TOTAL 163.00 0.67 0.38 0.13
The new IFCID 437 format is shown in the following figure.
DB2 VERSION: V13 PAGE DATE: 10/07/21
PRIMAUTH CONNECT INSTANCE END_USER WS_NAME TRANSACT
ORIGAUTH CORRNAME CONNTYPE RECORD TIME DESTNO ACE IFC DESCRIPTION DATA
PLANNAME CORRNMBR TCB CPU TIME ID
-------- -------- ----------- ----------------- ------ --- --- -------------- ------------------------------------------------------
TS3473A TSO DA6D158FDAEC TS3473A TSO TS3473A
TS3473A TS3473A TSO 10:52:48.65548900 78 1 437 TIMEOUT NETWORKID: ROCKNET1 LUNAME: DD1ADB2 LUWSEQ: 1
DSNESPCS 'BLANK' 0.02568870 DEADLOCK CTRL
|-------------------------------------------------------------------------------------------------------------------------
| SET CURRENT LOCK TIMEOUT
|
|SET DONE BY...............: PROFILE TABLE STATUS.......................: SUCCESSFUL
|OLD TIMEOUT VALUE.........: N/P NULL INDICATOR FOR OLD VALUE.: YES
|NEW TIMEOUT VALUE.........: -1 NULL INDICATOR FOR NEW VALUE.: NO
|-------------------------------------------------------------------------------------------------------------------------
TS3473A TSO DA6D159CE8D2 TS3473A TSO TS3473A
TS3473A TS3473A TSO 10:53:02.33921100 79 1 437 TIMEOUT NETWORKID: ROCKNET1 LUNAME: DD1ADB2 LUWSEQ: 1
DSNESPCS 'BLANK' 0.03507180 DEADLOCK CTRL
|-------------------------------------------------------------------------------------------------------------------------
| SET CURRENT LOCK TIMEOUT
|
|SET DONE BY...............: APPLICATION STATUS.......................: SUCCESSFUL
|OLD TIMEOUT VALUE.........: N/P NULL INDICATOR FOR OLD VALUE.: YES
|NEW TIMEOUT VALUE.........: 0 NULL INDICATOR FOR NEW VALUE.: NO
|PACKAGE CONSISTENCY TOKEN.: X'1A0D8BD811DAADD8'
|
|COLLECTION ID.........: COLLID1
|PACKAGE ID............: DSNESM68
|
|-------------------------------------------------------------------------------------------------------------------------
The RECTRACE changes for the respective instrumentation changes are not shown here.
Real-time monitoring support – SQL statement data
The QXST changes (SQL STATEMENT DATA) display in the Performance Expert Client and enhanced 3270 user interface, for the System Statistics side as well as for the thread detail workspaces. The next figures shows the E3270UI and PE Client isupport in detail.
- E3270UI workspace changes for System Statistics
- These changes are available from both the single Db2 subsystem as well as the Db2Plex view. To view, navigate to the System statistics main workspace and then select the SQL Counts tab (SQLC for Plex view, SQL for single Db2 view). On the SQL Counts workspace, select the DCL tab to view the new metrics.
- From Plex view (see highlighted rows).
Figure 4. Data Control Language (DCL) (KDPPSQL2) 
- From Single Db2 view (see highlighted rows).
Figure 5. Data Control Language (DCL) (KDPPSQL2) 
- E3270UI screen space for Thread Detail
- To see the QXST changes in a Thread Detail view, from the single Db2 system view, navigate to
the thread summary workspace and then drill down into a specific thread. Select the SQL tab
and then the “DCL” tab on the SQL Counts workspace to view the details screen. You can access
the same details screen also by navigating from the Db2Plex thread summary view and zooming
into the thread details, shown next.
Figure 6. Data Control Language (DCL) (KDPPSQL2) 
Real-time monitoring support – Timeout and Deadlock Event details
The changes for Lock timeout and deadlock events can be monitored with OMPE using the Performance Expert Client Event Exception detail screens as well as the enhanced 3270UI Events detail displays.
To access and collect Db2 Events:
- Db2 Event trace collection must be turned on
- Timeout and deadlock data must be collected
On the main single Db2 screen, navigate to the Events option (option E) and select a timeframe and event filter.
Click OK. The Deadlock/Timeout events are shown in the Events summary workspace.
Zoom into the list of events for deadlock and timeout, which display the following additional metrics in support of the enhanced timeout management functionality introduced by Db2 13.
- Timeout Details
- The “Interval Source” shows based on what setting the timeout condition (IRLM, Special register
or ZPARM) was triggered.
Figure 9. Events Timeout Detail 
Zoom into the Blocker Details to view the Holder Timeout Interval and the Interval Source for the Holder.
Figure 10. Events Timeout Blocker Detail 
- Deadlock Details
- Select the details of a Deadlock Event to display the Events Deadlock Detail workspace.
Figure 11. Events Deadlock Detail 
Drill down into the Resource details. You'll find the 'Waiter Source of the Worth' field which can be set to 'Global Variable' or 'Other'. For 'Global Variable', Db2 uses the DEADLOCK_RESOLUTION_PRIORITY global variable to specify a deadlock resolution priority value that is used in resolving deadlocks with other threads.
Figure 12. Events Resource Detail 