Start of change

-START DYNQUERYCAPTURE command (Db2)

The -START DYNQUERYCAPTURE command stabilizes access paths for qualified cached dynamic queries. This command can also optionally start monitoring of cached dynamic queries that qualify for a scope but have not met the specified execution threshold for stabilization.

All statements in the dynamic statement cache are qualified for capture, with the following exceptions:
  • Queries that were prepared with the REOPT(AUTO) bind option
  • Queries that were prepared with the CONCENTRATE STATEMENT WITH LITERALS bind option
  • Queries that were transformed because they reference system temporal, application temporal, or archived transparency tables and one or more of the following settings uses a non-default value:
    • CURRENT SYSTEM TEMPORAL TIME special register
    • CURRENT BUSINESS TEMPORAL TIME special register
    • GET_ARCHIVE global variable

Abbreviation: -STA DYNQUERY

Environment for -START DYNQUERYCAPTURE

This command can be issued from the z/OS® console, through a batch job, or the instrumentation facility interface (IFI).

Data sharing scope: Member

Authorization for -START DYNQUERYCAPTURE

To execute this command, you must use a privilege set of the process that includes one of the following authorities:

  • SQLADM authority
  • System DBADM authority
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority

Syntax for -START DYNQUERYCAPTURE

Read syntax diagramSkip visual syntax diagram START DYNQUERYCAPTURE STBLGRP( stabilization-group) THRESHOLD(2integer-constant)cache-snap-specSTMTID(integer-constant)STMTTKN(string-constant)
cache-snap-spec
Read syntax diagramSkip visual syntax diagram CURSQLID(*SQLID) MONITOR(NOYES) SCOPE(LOCALGROUP)

Option descriptions for -START DYNQUERYCAPTURE

STBLGRP
A user provided stabilization group name. You can use the stabilization group to logically associate a set of queries. The stabilization group name can be used as input to a FREE command to free all the queries for a stabilization group.
THRESHOLD
The threshold for stabilizing dynamic SQL statements. When the number of executions for a qualified statement is equal to or greater than the integer-constant value, the statement is scheduled for stabilization.

Start of changeStatement executions are counted only when IFCID 316 and IFCID 318 are both activated. Otherwise the execution count remains 0.End of change

The default value is 2.

STMTID
Stabilize an individual statement that has the specified STMTID value in the dynamic statement cache.
STMTTKN
Stabilize an individual statement that has the specified STMTTKN value.
CURSQLID
Specifies the scope of statements captured:
*
Capture all dynamic SQL statements. This is the default value.
SQLID
The scope of captured dynamic SQL statements us limited to statements that have the CURRENT SQLID value.
MONITOR
Specifies whether to process statements in the statement cache and stop, or to process the statements in the statement cache and enable monitoring of queries that qualify for the scope, but have not met the THRESHOLD value.
NO
Schedule stabilization for qualified statements within the cache that exceed the execution threshold.
YES
Schedule stabilization for qualified statements that exceed the execution threshold, and monitoring for qualified statements that have not met the execution threshold.

If a statement is eligible for capture under more than one monitor, the monitor with the lower threshold applies. If multiple eligible monitors have the same threshold, the monitor that was started first applies.

SCOPE
Specifies the scope of the command.
LOCAL
Starts the capture on the local Db2 system only.
GROUP
Starts the capture on all members of the data sharing group.

Usage notes for -START DYNQUERYCAPTURE

Stopping and starting Db2
If Db2 is stopped and started after you have started a DYNQUERY CAPTURE MONITOR, the monitor is not restarted automatically.
Specifying SCOPE (GROUP)
If a monitor is started with SCOPE(GROUP), and a new member joins the data sharing group after the monitor is started, the monitor is not started automatically at the new member.

Examples for -START DYNQUERYCAPTURE

Stabilize queries in the dynamic cache with CURRENT SQLID of ADMF001 and have been executed at least 50 times
You issue the following command:
-STA DYNQUERYCAPTURE STBLGRP(ABC) THRESHLD(50) CURSQLID(ADMF001)

The output is similar to the following example:

DSNX221I -DB2A DSNXESTC DYNAMIC QUERY CAPTURE FOR
COMMAND NUMBER 3 STARTED SUCCESSFULLY.
DSNX222I -DB2A DSNXESC1 DYNAMIC QUERY CAPTURE
COMPLETED FOR COMMAND NUMBER 3 WITH 20 STATEMENTS SCHEDULED,
20 STATEMENTS STABILIZED, AND 0 STATEMENTS ALREADY STABILIZED.
Stabilize all queries in the dynamic cache of each member in the data sharing group that have been executed at least 200 times
You issue the following command:
-STA DYNQUERYCAPTURE STBLGRP(DEF) THRESHLD(200) SCOPE(GROUP)

The output is similar to the following example. The numbers in the output are accumulated from all members in the data sharing group:

DSNX221I -DB2A DSNXESTC DYNAMIC QUERY CAPTURE FOR
COMMAND NUMBER 2 STARTED SUCCESSFULLY.
DSNX222I -DB2A DSNXESC1 DYNAMIC QUERY CAPTURE
COMPLETED FOR COMMAND NUMBER 2 WITH 50 STATEMENTS SCHEDULED,
50 STATEMENTS STABILIZED, AND 3 STATEMENTS ALREADY STABILIZED.

Output for -START DYNQUERYCAPTURE

Message DSNX221I is issued when the START DYNQUERYCAPTURE command contains no syntax errors.

Message DSNX222I is issued at the completion of processing for the START DYNQUERYCAPTURE command.

Message DSNX223I is issued if a previous START DYNQUERYCAPTURE command specified the same SQLID.

End of change