Dynamic query capture monitors are used by Db2 to monitor dynamic SQL statements for
stabilization. These monitors are part of the dynamic SQL plan stability feature of Db2.
Before you begin
Before you can view dynamic query capture monitors, all of the following
conditions must be true:
- The subsystem must be running on Db2 12 function
level 500 or higher.
- The CACHEDYN subsystem parameter must be set to YES, and the CACHEDYN_STABILIZATION subsystem
parameter must be set to either BOTH or CAPTURE. (To change these parameter values, follow the
instructions in Managing Db2 subsystem parameters.)
Procedure
To view dynamic query capture monitors:
-
On the DB2 Administration Menu (ADB2)
panel, specify option
E, and press Enter.
-
On the EXPLAIN (ADB2E) panel, specify option
DPS, and press Enter.
If the DPS option is not displayed, the prerequisites (in Before you begin) are not all true.
- Optional:
On the Display/Manage Dynamic Plan Stability
(ADB2ED) panel, specify any selection criteria
to filter the monitors that you want displayed.
-
Specify option 2, and press Enter:
ADB2ED in ---------- DC1A Display/Manage Dynamic Plan Stability --------- 14:35
Option ===> 2
OPTIONS:
1 - START DYNQUERYCAPTURE
2 - DISPLAY/STOP DYNQUERYCAPTURE
3 - Display/manage SYSDYNQRY
Enter standard selection criteria for Option 3.
Settings: LIKE operator; Criteria not saved.
Stabilization Group . . >
Copy ID . . . . . . . . (0 or 4)
SQL ID . . . . . . . .
Valid . . . . . . . . . (A,H,N or Y)
SQL Statement Type . .
Applcompat . . . . . . (eg. V12R1M501)
Last Used . . . . . . .
Altered Within . . . .
The Display/Manage DYNQUERYCAPTURE
(ADB2ED2) panel displays
information about all of the currently active dynamic query capture monitors. Each row contains
information about a stabilization group that is being monitored. If you started a dynamic query
capture monitor with a smaller scope than an existing active query capture monitor, the monitor with
the smaller scope is not considered active and is not displayed on this panel.
ADB2ED2 n ----------------- DC1A Display/Manage DYNQUERYCAPTURE Row 1 to 6 of 6
Commands: STOPGROUP STOPLOCAL
Line commands:
STOL - Stop local monitor STA - Start monitor
STOG - Stop Group monitor
Command
SEL number Stabilization group SQL ID Threshold Stabilized
* * * * *
---- ----------- -----------------------------> -------- --------- -----------
170 T5 ERNSCH01 1 0
166 T12345678901234567890123456789 TS6509 1 37004
148 T4 TS5794 1 2387
147 T3 TS5791 1 1
146 T2 TS5770 1 470
145 EREN1 * 2 3820
******************************* END OF DB2 DATA *******************************
From this panel, you can stop monitors by using the STOPGROUP and STOPLOCAL commands or the STOL
and STOG line commands. You can also modify a monitor by using the STA line command or start a new
monitor by using the STA command.