SQL Counts attributes

Use the 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 Db2 subsystem. They also show information for stored procedures, user-defined functions, triggers, direct row access, parallelism, RID pool access, and prepare statements. They provide information about multi-row processing and concentrate statements.

4K Used Instead 32K The number of times that space in a 4 KB page table space was used because space in a 32 KB page table space was preferred but not available (Field name: QISTWFP2).

32K Used Instead 4K The number of times that space in a 32 KB page table space was used because space in a 4 KB page table space was preferred but not available (Field name: QISTWFP1).

Agent Max Storage The maximum amount of storage that can be used for each agent (Field name: QISTWMXA).

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

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

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

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

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

Current Total Storage The total whole kilobytes of storage that are currently used in the workfile database (field name: QISTWCTO).

DB2 ID The Db2 subsystem ID.

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

Delete Rows Accessed The number of rows accessed by READ transactions because of uncommitted DELETE operations (using currently committed semantic for FETCH) - (Field name: QISTRCCD).

Delta The changes since the last sample period.

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

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

Free Locator The number of FREE LOCATOR statements (Field name: QXFREEL).

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

Hold Locator The number of HOLD LOCATOR statements (Field name: QXHOLDL).

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

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

Insert Rows Skipped The number of rows skipped by READ transactions because of uncommitted INSERT operations (using currently committed semantic for FETCH) - (Field name: QISTRCCI).

Interval Time The number of seconds since last sample.

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

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 Total Storage The maximum total amount of storage that is used in the workfile database (Field name QISTWMXU).

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

MVS ID The MVS system identifier.

Number of Max Exceeded The number of times the maximum amount of storage that an agent can use was exceeded (field name: QISTWFNE).

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.

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

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

Rate The number of requests per second over the last sampling interval.

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 Lock Timeout(P) The number of times SET CURRENT LOCK TIMEOUT statement was executed from the DSN_PROFILE_TABLE (Field name: QXSTTIMEOUTFromProf).

Set Current Lock Timeout(SQL) The number of times SET CURRENT LOCK TIMEOUT statement was executed (Field name: QXSTTIMEOUTFromAppl).

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

Set Current Precision The number of SET CURRENT PRECISION statements executed (Field name: QXSETCPR).

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

Storage in 4K TS The total whole kilobytes of storage that were used for 4 KB table spaces (Field name: QISTW4K).

Storage in 32K TS The total whole kilobytes of storage that were used for 32 KB table spaces (Field name: QISTW32K).

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

Total The total count executed by the thread.

Transfer Ownership The number of TRANSFER OWNERSHIP statements (Field name: QXTRNOWN).

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

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

Update Rows Accessed The number of rows accessed by READ transactions because of uncommitted UPDATE operations (using currently committed semantic for FETCH) - (Field name: QISTRCCU).