Thread SQL Counts attributes

Use the Thread SQL Counts attributes to create situations to monitor the SQL DCL (Data Control Language) declarations, SQL DDL (Data Definition Language) statements, and SQL DML (Data Manipulation Language) statements, associated with a thread. These attributes also list information for stored procedures, user-defined functions, triggers, direct row access, parallelism, RID pool access, and prepare statements. They also show information for currently committed rows, multi-row processing, workfile storage, and concentrate statements.

Abort The number of times the thread has rolled back uncommitted data (Field name QWACABRT).

AGNT ASID The address space ID (ASID) of the agent.

Allocate Cursor The number of SQL ALLOCATE CURSOR statements executed (Field name: QXALOCC).

Alter Database The number of ALTER DATABASE statements executed (Field name: QXALDAB).

Alter Function The number of ALTER FUNCTION statements executed (Field name: QXALUDF).

Alter Index The number of ALTER INDEX statements executed (Field name: QXALTIX).

Alter Procedure The number of ALTER PROCEDURE statements executed (Field name: QXALPRO).

Alter Stogroup The number of ALTER STOGROUP statements executed (Field name: QXALTST).

Alter Table The number of ALTER TABLE statements executed (Field name: QXALTTA).

Alter Tablespace The number of ALTER TABLESPACE statements executed (Field name: QXALTTS).

Alter Trusted Context The number of alter trusted context statements executed (Field name QXALTCTX) - not supported prior to Db2 9.

Associate Locator The number of SQL ASSOCIATE LOCATORS statements executed (Field name: QXALOCL).

Authorization ID The primary authorization ID from connection or sign-on that identifies the execution privileges an application has to a Db2 object or service. It is an alphanumeric text string with a maximum of 8 characters.

Authorization ID (Unicode) The primary authorization ID from connection or sign-on that identifies the execution privileges an application has to a Db2 object or service. It is an alphanumeric text string with a maximum of 128 characters.

Close Cursor The number of CLOSE statements executed. This number at the server location might not match the user application because of DDF's internal processing (Db2 field name: QXCLOSE).

Collection ID (Unicode) The collection ID. This field is blank for DBRM. It is an alphanumeric text string, with a maximum of 128 characters.

Comment On The number of COMMENT ON statements executed (Field name: QXCMTON).

Commit The number of times the thread successfully concluded commit phase 2 processing (Field name QWACCOMM).

Connect (Type 1) The number of CONNECT type 1 statements executed (Field name: QXCON1).

Connect (Type 2) The number of CONNECT type 2 statements executed (Field name: QXCON2).

Thread SQL Counts Attributes Connection ID. Identifies the connection of an application to a Db2 system.

It is an alphanumeric text string, with a maximum length of eight characters.

Thread SQL Counts Attributes The type of connection associated with the thread.

Correlation ID The correlation between an application and the connection to a Db2 system. It is an alphanumeric text string, with a maximum of 12 characters; for example DLKEX212.

Create Alias The number of CREATE ALIAS statements executed (Field name: QXCRALS).

Create Database The number of CREATE DATABASE statements executed (Field name: QXCRDAB).

Create Distinct Type The number of CREATE DISTINCT TYPE statements executed (Field name: QXCDIST).

Create Function The number of CREATE FUNCTION statements executed (field name: QXCRUDF).

Create Global Temp Table The number of CREATE GLOBAL TEMPORARY TABLE statements executed (field name: QXCRGTT).

Create Index The number of CREATE INDEX statements executed (Field name: QXCRINX).

Create Procedure The number of CREATE PROCEDURE statements executed (Field name: QXCRPRO).

Create Role The number of CREATE ROLE statements executed (Field name QXCRROL) - not supported prior to Db2 9.

Create Stogroup The number of CREATE STOGROUP statements executed (field name: QXCRSTG).

Create Synonym The number of CREATE SYNONYM statements executed (Field name: QXCRSYN).

Create Table The number of CREATE TABLE statements executed (Field name: QXCRTAB).

Create Tablespace The number of CREATE TABLESPACE statements executed (Field name: QXCTABS).

Create Trigger The number of CREATE TRIGGER statements executed (Field name: QXCTRIG).

Create Trusted Context The number of create trusted context statements executed (Field name QXCRCTX) - not supported prior to Db2 9.

Create View The number of CREATE VIEW statements executed (Field name: QXDEFVU).

DB2 ID The Db2 subsystem ID.

Delete The number of DELETE statements executed (Db2 field name: QXDELET).

Describe The number of DESCRIBE, DESCRIBE CURSOR, DESCRIBE INPUT, and DESCRIBE PROCEDURE statements executed. This number at the server location might not match the user application because of DDF's internal processing (Field name: QXDESC).

Describe Table The number of DESCRIBE TABLE statements executed (Field name: QXDSCRTB).

Description The description of the monitored activity.

Direct Row Revert to Index The number of times that direct row access failed and an index was used to find a record (Field name: QXROIIDX).

Direct Row Revert to TS Scan The number of times that an attempt to use direct row access reverted to using a table-space scan because Db2 was not able to use a matching index scan (Field name: QXROITS).

Direct Row Successful The number of times that direct row access was successful (Field name: QXROIMAT).

Drop Alias The number of SQL DROP ALIAS statements executed (Field name: QXDRPAL).

Drop Database The number of DROP DATABASE statements executed (field name: QXDRPDB).

Drop Distinct Type The number of DROP DISTINCT TYPE statements executed (Field name: QXDDIST).

Drop Function The number of DROP FUNCTION statements executed (Field name: QXDRPFN).

Drop Index The number of DROP INDEX statements executed (Field name: QXDRPIX).

Drop Package The number of SQL DROP PACKAGE statements executed (Field name: QXDRPPKG).

Drop Procedure The number of DROP PROCEDURE statements executed (Field name: QXDRPPR).

Drop Role The number of DROP ROLE statements executed (Field name QXDRPROL) - not supported prior to Db2 9.

Drop Stogroup The number of DROP STOGROUP statements executed (Field name: QXDRPST).

Drop Synonym The number of DROP SYNONYM statements executed (Field name: QXDRPSY).

Drop Table The number of DROP TABLE statements executed (Field name: QXDRPTA).

Drop Tablespace The number of DROP TABLESPACE statements executed (Field name: QXDRPTS).

Drop Trigger The number of DROP TRIGGER statements executed (Field name: QXDRPTR).

Drop Trusted Context The number of drop trusted context statements executed (Field name QXDRPCTX) - not supported prior to Db2 9.

Drop View The number of DROP VIEW statements executed (Field name: QXDRPVU).

End User ID The user ID of the workstation end user. This user ID can be different from the authorization ID used to connect to Db2. This field contains blanks if the client does not supply this information (Field name: QWHCEUID).

Fetch The number of FETCH statements executed. This number at the server location might not match the user application because of DDF's internal processing (Field name: QXFETCH).

Grant The number of GRANT statements executed (Field name: QXGRANT).

Incremental Bind The number of incremental binds (excluding prepare) (Field name: QXINCRB).

Incremental Bind Due To Invl Stmt The number of incremental binds due to invalidated statements (Db2 field name: QXINCRB_INVSTMT).

Insert The number of INSERT statements executed (Field name: QXINSRT).

Interval Start The date and time, as set on the monitored system, indicating the instance when the agent collects information.

Job Name The job name that is associated with a thread.

Label On The number of LABEL ON statements executed (Field name: QXLABON).

Literals Dups Created The number of times Db2 created a duplicate STMT instance in the statement cache for a dynamic statement that had literals replaced by CONCENTRATE STATEMENTS WITH LITERALS behavior (Field name: QXSTCWLD). The duplicate STMT instance was needed because a cache match failed because the literal reusability criteria was not met.

Literals Matches Found The number of times Db2 found a matching reusable copy of a dynamic statement in cache because of CONCENTRATE STATEMENTS WITH LITERALS behavior (Field name: QXSTCWLM).

Literals Parsed The number of times Db2 parsed dynamic statements because of CONCENTRATE STATEMENTS WITH LITERALS behavior (Field name: QXSTCWLP).

Literals Replaced The number of times Db2 replaced at least one literal in a dynamic statement because of CONCENTRATE STATEMENTS WITH LITERALS behavior (Field name: QXSTCWLR).

Lock Table The number of LOCK TABLE statements executed (Field name: QXLOCK).

LUWID The logical unit of work ID (LUWID) for a thread.

luw-id=token

USCACO01.O2D22A.A1FE8E04B9D4=8

Max Nested SQL Trigger The maximum level of indirect SQL cascading (Field name: QXCASCDP). This includes cascading because of triggers, UDFs, or stored procedures.

Max Parallel Degree The maximum degree of parallel query processing executed among all parallel groups to indicate the extent to which queries were processed in parallel (Field name: QXMAXDEG).

Merge The number of MERGE statements executed (Db2 field name QXMERGE) - not supported prior to Db2 9.

MVS ID The MVS system identifier.

Open Cursor The number of OPEN statements executed (Field name: QXOPEN).

Originating System ID The managed system name of the agent. It is an alphanumeric text string, with a maximum of 32 characters; for example, DB91:SYS1:DB2.

Package DBRM (Unicode) The package name of the database request module (DBRM) with which a thread is associated. It is an alphanumeric text string with a maximum of 128 characters.

Parallel Bypass DB2 (Buffers) The number of times the parallelism coordinator had to bypass a Db2 when distributing tasks because one or more Db2 members did not have enough buffer pool storage (Field name: QXXCSKIP). The number in this field is only incremented at the parallelism coordinator once per parallel group, even though more than one Db2 might have lacked buffer pool storage for that parallel group. It is also only incremented when the buffer pool is defined to allow for parallelism. For example, if VPXPSEQT=0 on an assistant, Db2 does not send parallel work there and the number in this field is not incremented.

Parallel Degree Executed The total number of parallel groups that executed in the planned parallel degree (Field name: QXNORGRP). This field is incremented by one for each parallel group that executed in the planned degree of parallelism (as determined by Db2).

Parallel Degree Reduced (no buffer) The total number of parallel groups that did not reach the planned parallel degree because of a lack of storage space or contention on the buffer pool (Field name: QXREDGRP).

Parallel Failed (Cursor) The total number of parallel groups that fell back to sequential mode because of a cursor that can be used by UPDATE or DELETE (Field name: QXDEGCUR).

Parallel Failed (No Buffer) The total number of parallel groups that fell back to sequential mode because of a storage shortage or contention on the buffer pool (Field name: QXDEGBUF).

Parallel Failed (No ESA Sort) The total number of parallel groups that fell back to sequential mode because of a lack of ESA sort support (Field name: QXDEGESA).

Parallel Failed (No Enclaves) The total number of parallel groups that executed in sequential mode because of the unavailability of MVS/ESA enclave services (Field name: QXDEGENC).

Parallel Grp Executed The total number of parallel groups that were executed (Field name: QXTOTGRP).

Parallel Maximum LOB Storage The maximum storage used for LOB values (Db2 field name: QXSTLOBV).

Parallel Single DB2 (Coord=NO) The total number of parallel groups executed on a single Db2 subsystem due to the COORDINATOR subsystem value being set to NO (Field name: QXCOORNO). When the statement was bound, the COORDINATOR subsystem value was set to YES. This situation can also occur when a package or plan is bound on a Db2 subsystem with COORDINATOR=YES, but is run on a Db2 subsystem with COORDINATOR=NO.

Parallel Single DB2 (Cursor) The total number of parallel groups executed on a single Db2 subsystem because of a repeatable-read or read-stability isolation (Field name: QXISORR).

Parallel Sysplex Intent The total number of parallel groups that Db2 intended to run across the data sharing group (Field name: QXXCBPNX). This number is only incremented at the parallelism coordinator at run time.

Parallelism Disabled YES indicates that query parallelism is disabled by the Resource Limit Facility for at least one dynamic SQL SELECT statement.

Plan Name The name of an application plan that Db2 produces during the bind process and uses for processing SQL statements during execution. It is an alphanumeric text string, with a maximum of 8 characters. For example, PLANLCK2 is the name of an application plan. *SYSTEM* indicates Db2 subsystem-generated threads that own or are waiting on a lock.

Prepare The number of PREPARE statements executed. This number at the server location might not match the user application because of DDF's internal processing (Field name: QXPREP).

Prepare Avoided KEEPDYN(YES) The number of times where no SQL PREPARE or EXECUTE IMMEDIATE was issued by the application and a copy of a prepared SQL statement was found in local dynamic SQL cache (Field name: QXSTNPRP). When an application plan or package is bound with KEEPDYNAMIC YES, a copy of each prepared SQL statement for the application thread is held in the local dynamic SQL cache and kept across a commit boundary. An application thread can save the total cost of a prepare by using a copy of the prepared statement in the local dynamic SQL cache from an earlier prepare by the same thread. To do this, the application must be modified to avoid issuing repetitive SQL PREPAREs for the same SQL statement.

Prepare Copied from Cache The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache (Field name: QXSTFND).

Prepare Discarded - MAXKEEPD The number of times statements are invalidated in the local dynamic SQL cache because the MAXKEEPD limit has been reached and prepared SQL statements in the local dynamic SQL cache have to be reclaimed (Field name: QXSTDEXP).

Prepare Implicit KEEPDYN(YES) An implicit prepare occurs when the user copy of the prepared SQL statement no longer exists in the local dynamic SQL cache and the application plan or package is bound with KEEPDYNAMIC YES (Field name: QXSTIPRP). If the skeleton copy of the prepared SQL statement exists in the global dynamic SQL cache in the EDM pool, a short prepare is executed, otherwise a full prepare is executed.

Prepare No Match The number of times that Db2 searched the prepared statement cache but could not find a suitable prepared statement (Field name: QXSTNFND).

Prepare Purged - DROP/ALT/REV The number of times statements are invalidated in the local dynamic SQL cache because of SQL DDL or updated RUNSTATS information and prepared SQL statements in the local dynamic SQL cache have to be reclaimed (Field name: QXSTDINV).

Redrived Incrm Bind (APREUSE) The number of redrived incremental binds with APREUSE (Db2 field name: QXINCRB_APR_REDRIVE).

Release The number of RELEASE statements executed (Db2 field name: QXREL).

Rename Index The number of RENAME INDEX statements executed (Field name QXRNIX) - not supported prior to Db2 9.

Rename Table The number of RENAME TABLE statements executed (Field name: QXRNTAB).

Revoke The number of REVOKE statements executed (Field name: QXREVOK).

RID HWM The highest number of RID blocks in use at any time since Db2 startup (Field name: QISTRHIG). This is a high-water mark.

RID In Use The number of RID blocks currently in use (snapshot value) (Field name: QISTRCUR).

RID Exceeded Pool The number of times the maximum RID pool storage was exceeded (Field name: QISTRMAX). The size is determined by the installation parameter RID POOL SIZE (Db2 install panel DSNTIPC). It can be 0, or between 128 KB and 10 GB. The general formula for calculating the RID pool size is: Number of concurrent RID processing activities x average number of RIDs x 2 x 5 bytes per RID.

RID Exhausted Virtual The number of times RID pool processing was not used because DBM1 storage was exhausted (Field name: QISTRSTG).

RID Not Used (no storage) The number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID pool process involving one index (single index access with list prefetch) or multiple indexes (multiple index access) (Db2 field name: QXNSMIAP).

RID Not Used (Max Limit) The number of times Db2 detected that a RID list exceeded one or more internal limits during a given RID list (or RID pool) process involving one index (single index access with list prefetch) or multiple indexes (multiple index access) (Db2 field name: QXMRMIAP). The internal limits include the physical limitation of the number of RIDs a RID list can hold and threshold values for the retrieval, ORing, and ANDing of RIDs.

RID Pool Used The number of times the RID (RECORD ID) pool has been processed (Field name: QXMIAP). During RID (RECORD ID) pool (also called RID list) processing, Db2 uses an index to produce a list of candidate RIDs, which is called a RID list. The RID list can be sorted and intersected (ANDed) or unioned (ORed) with other RID lists before actually accessing the data pages. RID list processing is used for a single index (index access with list prefetch) or for multiple indexes (multiple index access), which is when the RID lists are ANDed and ORed.

RID Terminated (> DM) The number of times a RID pool processing operation terminated because the number of RID entries was greater than the DM limit (Field name: QISTRPLM).

RID Terminated (> RDS) The number of times RID pool processing terminated because the number of RIDs that can fit into the guaranteed number of RID blocks was greater than the maximum limit (25% of table size). Beginning Db2 Version 12, the number of times processing was terminated is incremented by the DM as well as RDS. (Field name: QISTRLLM).

Row Triggers Executed The number of times a row trigger was activated (Field name: QXROWTRG).

Rows Deleted The number of rows that have been deleted because of SQL DELETE statements (Field name: QXRWSDELETD).

Rows Fetched The number of rows that have been fetched because of SQL FETCH statements (Field name: QXRWSFETCHD).

Rows Inserted The number of rows that have been inserted because of SQL INSERT statements (Field name: QXRWSINSRTD).

Rows Updated The number of rows that have been updated because of SQL UPDATE statements (Field name: QXRWSUPDTD).

Select The number of SQL SELECT statements executed (Field name: QXSELECT).

Set Connection The number of SET CONNECTION statements executed (Field name: QXSETCON).

Set Current Degree The number of SET CURRENT DEGREE statements executed (Field name: QXSETCDG).

Set Current Path The number of SET CURRENT PATH statements executed (Field name: QXSETPTH).

Set Current Rules The number of SET CURRENT RULES statements executed (Field name: QXSETCRL).

Set Current SQLID The number of SET CURRENT SQLID statements executed (Field name: QXSETSQL).

Set Host Variable The number of SET HOST VARIABLE statements executed. The special register that was retrieved is not tracked (Field name: QXSETHV).

SP Abended The number of times a stored procedure terminated abnormally (Field name: QXCALLAB).

SP Call Statements The number of times the SQL CALL statements executed to invoke a stored procedure (SP).

SP Rejected The number of times an SQL CALL statement was rejected because of the procedure that is in the STOP ACTION(REJECT) state (Field name: QXCALLRJ).

SP Timed-Out The number of times an SQL CALL statement timed out when waiting to be scheduled (Field name: QXCALLTO).

SQL Error in Trigger The number of times an SQL error occurred during the execution of a triggered action (Field name: QXTRGERR). This includes errors that occur in user-defined functions or stored procedures that are called from triggers and that pass back a negative SQLCODE.

Stmt Triggers Executed The number of times a statement trigger was activated (Field name: QXSTTRG).

Total The total count executed by the thread.

UDFs Abended The number of times a user-defined function (UDF) abended (Field name: QXCAUDAB).

UDFs Executed The number of user-defined functions (UDFs) executed (Field name: QXCAUD).

UDFs Rejected The number of times a user-defined function (UDF) was rejected (Field name: QXCAUDRJ).

UDFs Timed Out The number of times a user-defined function (UDF) timed out while waiting to be scheduled (Field name: QXCAUDTO).

Uniqueness Value The instance number: When concatenated with the fully qualified network name, it uniquely identifies a distributed thread (Field name: QWHSLUUV).

Uniqueness Value 2 The Db2 thread uniqueness value. For internal use.

Update The number of UPDATE statements executed (Field name: QXUPDTE).