Current® SQL Counts
This panel provides counts of the various SQL activities, RID pool activities, and Parallel activities of an individual thread.
With this information, you can determine the activities that are using resources excessively. For example, a single SELECT command might result in many physical FETCH activities.
This information includes data definition statements and data manipulation statements. DB2® control counts are also supplied.
The panel also displays counts related to RID pool usage and parallel I/O activity.
________________ ZTSCNT VTM O2 V540./C DA31 10/30/14 13:28:16 2
> Help PF1 Back PF3
> THREAD INFORMATION: Enter a selection letter on the top line.
> A-THREAD DETAIL B-LOCK COUNTS C-LOCK WAITS D-LOCKS OWNED E-GLOBAL LOCKS
> F-CURRENT SQL *-SQL COUNTS H-DISTRIBUTED I-BUFFER POOL J-GROUP BP
> K-PACKAGES L-RES LIMIT M-PARALLEL TASKS N-UTILITY O-OBJECTS
> P-CANCEL THREAD Q-DB2 CONSOLE R-DSN ACTIVITY S-APPL TRACE T-ENCLAVE
> U-LONG NAMES W-ACCEL ACTIVITY
===============================================================================
> CURRENT SQL COUNTS
PLAN
+ Thread: Plan=KO2PLAN Connid=RRSAF Corrid= Authid=OMPEUSER
+ Attach: RRSAF JOB Name=DA31DM1S JOB Asid= 169
+ Luwid=DEIBMIPS.IPSASA31.CDFB0F7D865B=12
sqls
+ Commit = 7 Abort = 0 Select = 5
+ Open Cursor = 3 Close Cursor = 3 Fetch = 9443
+ Insert = 0 Delete = 0 Update = 1
+ Describe = 0 Lock Table = 0 Prepare = 1
+ Grant = 0 Revoke = 0 Set Rules = 0
+ Increm Bind = 0 Label/Comm On = 0 Set SQLID = 0
+ Set Host Var = 0 Set Connection = 0 Set Degree = 0
+ Conn Type 1 = 0 Conn Type 2 = 0 Set Path = 0
+ Rename Table = 0 Hold Locator = 0 Free Locator = 0
+ Release = 0 Assoc Locator = 0 Alloc Cursor = 0
+ Merge = 0 Rename Index = 0 Truncate Table = 0
+ Refresh Table = 1 Transfer Owner = 2
+
+ Creates: Drops: Alters:
+ Table = 0 Table = 0 Table = 0
+ Index = 0 Index = 0 Index = 0
+ Table Space = 0 Table Space = 0 Table Space = 0
+ Data Base = 0 Data Base = 0 Data Base = 0
+ Storage Group = 0 Storage Group = 0 Storage Group = 0
+ Synonym = 0 Synonym = 0
+ View = 0 View = 0 View = 0
+ Alias = 0 Alias = 0
+ Function = 0 Function = 0 Function = 0
+ Procedure = 0 Procedure = 0 Procedure = 0
+ Sequence = 0 Sequence = 0 Sequence = 0
+ Role = 0 Role = 0
+ Trusted CTX = 0 Trusted CTX = 0 Trusted CTX = 0
+ Mask/Perm = 0 Mask/Perm = 0 Mask/Perm = 0
+ Variable = N/A Variable = N/A
+ Trigger = 0 Trigger = 0
+ Dist Type = 0 Dist Type = 0
+ Aux Table = 0 Package = 0 Jar = 0
+ Glob Temp Tab = 0 Declare GTT = 0
+ 
+ RID List
+ ---------------------
+ Successful = 0 To Workfile (No Storage) = 0
+ Not Used (No Storage) = 0 To Workfile (Max Limit) = 0
+ Not Used (Max Limit) = 0 Interrupted (No Storage) = 0
+ Skipped (Index Known) = 0 Interrupted (Max Limit) = 0
+ Skipped (Adaptive IX) = 0
+
+ Query Parallelism
+ ---------------------
+ Max Degree (Planned) = N/A Parallel Group Degenerated = N/A
+ Max Degree (Estimated) = N/A Reform Parallelism (Config Chg) = 0
+ Max Degree (Executed) = 0 Reform Parallelism (No Buffer) = 0
+ Ran Planned = 0 Total Parallel Groups = 0
+ Ran Reduced (Storage) = 0 Groups Intended = 0
+ Ran Reduced (Negot) = N/A Members Skipped = 0
+ Ran Seq (Cursor) = 0 One DB2 (Coord=NO) = 0
+ Ran Seq (No Buffer) = 0 One DB2 (Isolation) = 0
+ Ran Seq (No ESA Sort) = 0 One DB2 (DCL GTT) = 0
+ Ran Seq (Autonomous) = N/A
+ Ran Seq (Negotiate) = N/A
+ + Miscellaneous Triggers
+ --------------------- ---------------------
+ Max LOB Storage (KB) = 0 Statement Trigger = 0
+ Maximum XML Storage = 0 Row Trigger = 0
+ Maximum Nested SQL = 0 SQL Errors = 0
+ Array Expansions = N/A
+ Sparse IX (Disabled) = N/A
+ Sparse IX (Built WF) = N/A
+ Fetch 1 Block Only = 0
+
+ Stored Procedures User Defined Functions
+ --------------------- ---------------------
+ Executed = 0 Executed = 0
+ Abended = 0 Abended = 0
+ Timed Out = 0 Timed Out = 0
+ Rejected = 0 Rejected = 0
+ + Prepare Statistics
+ ---------------------
+ Prepares (Copy Found) = 0 Prepares (Copy Not Found) = 1
+ Prepares (restrict IX) = 0 Prepares (Catalog Found) = 0
+ Literals (Parsed) = 0 KeepDyn (Prepared) = 0
+ Literals (Replaced) = 0 KeepDyn (Avoided) = 0
+ Literals (Matched) = 0 KeepDyn (Exceed Limit) = 0
+ Literals (Duplicated) = 0 KeepDyn (Invalidated) = 0
+
+ ROWID Row Processing
+ --------------------- ---------------------
+ Direct Access = 0 Rows Fetched = 9443
+ Index Used = 0 Rows Updated = 0
+ TS Scan Used = 0 Rows Inserted = 1
+ Rows Deleted = 0
=============================================================================== Highlighting
The following table shows
the fields that might be highlighted in the panel above to indicate
that an exception that is related to this field exceeded its threshold
value:
| Field | Exception | Reason |
|---|---|---|
| Plan | ARCM | Backout requires an archive tape log mount. |
| GETP | The Getpage to Read I/O ratio indicates poor read efficiency. | |
| INDB | The thread is indoubt and terminated. | |
| PREF | The Sequential Prefetch rate is high. | |
| RIO | The synchronous Read I/O rate is high. | |
| ETIM | This thread has reached the Elapsed Time threshold value. | |
| PGUP | The rate for system page updates is high. | |
| WTRE | The wait resource time is high. | |
| Commit | COMT | The ratio of Commits to Updates indicates a low Commit frequency. |
| Abort | COMT | Backout processing caused by aborts might have reduced the Commit rate. |
Navigation
For additional
information about
- an individual thread, for example, the locking activity or SQL activity of a thread, or the resource limit activity, type the appropriate option letter on the top line and press Enter.
- related topics, select one of the options on the top of the panel.
- other topics, use the PF keys.
Fields
Thread identifier: This information
identifies the thread to which the information in this panel applies.
- Plan
- The DB2 plan name of the active thread.
- Connid
- The DB2 connection identifier of the active thread.
- Corrid
- The DB2 correlation identifier of the active thread. If the correlation is not set, N/A is displayed.
- Authid
- The DB2 authorization identifier of the active thread.
- Attach
- Depending on the type of connection, the appropriate information is displayed.
- Attach information is displayed only if the thread is a distributed allied thread (not for distributed database access threads).
Package
identifier: This information identifies the package to which the
information in this panel applies.
Distributed
thread identifier: The following fields are displayed when the
thread has a distributed relationship with a remote DB2 subsystem.
- Type
- The distributed thread type.
- Distributed Allied
- A requesting thread; one that has issued an SQL call to a remote DB2 location.
- Database Access
- A responding thread; one that is serving a remote DB2 location by responding to an SQL call.
- Luwid
- This value consists of two parts: the logical unit
of work ID (luw-id) and a token. The token can be used in place of
the luw-id in any DB2 command
that accepts luw-id as input. Format:
luw-id=tokenThe luw-id consists of the network name, the originating VTAM® LUNAME, and a unique identifier (separated by periods). Thus, the Luwid field displays data such as the following:
USCACO01.O2D22A.A1FE8E04B9D4=8
SQL counts fields:
- Commit
- The number of times the thread successfully concluded Commit phase 2 processing.
- Abort
- A count of the number of times the thread has rolled back uncommitted data.
- Select
- The number of SELECT requests.
- Open Cursor
- The number of OPEN cursor statements issued by a thread.
- Close Cursor
- The number of CLOSE cursor statements issued by a thread.
- Fetch
- The number of FETCH requests.
- Insert
- The number of INSERT statements executed by the thread.
- Delete
- The number of DELETE statements issued by the thread.
- Update
- The number of UPDATE statements executed by the thread.
- Describe
- The number of DESCRIBE statements issued by the thread.
- Lock Table
- A count of the number of LOCK TABLE statements issued by the application (not the total number of tables locked by the thread).
- Prepare
- The number of PREPARE statements issued by a thread.
- Grant
- The total number of times SQL GRANT requests were issued from within the program.
- Revoke
- The total number of times SQL REVOKE requests were issued from within the program.
- Set Rules
- The number of SET CURRENT RULES statements executed by the thread.
- Increm Bind
- A count of the number of times the plan active in the thread was rebound. PREPARES are not included. This value should be zero in a production environment. The plan can be rebound with VALIDATE(BIND) to prevent incremental binds.
- Label/Comm On
- The number of LABEL ON and COMMENT ON statements issued by the thread.
- Set SQLID
- The number of SET SQLID requests issued by the thread.
- Set Host Var
- The number of SET host variable requests executed by the thread.
- Set Connection
- The number of SET CONNECTION statements executed by the thread.
- Set Degree
- The number of SET CURRENT DEGREE statements executed by the thread.
- Connect Type 1
- The number of CONNECT type 1 statements executed by the thread.
- Connect Type 2
- The number of CONNECT type 2 statements executed by the thread.
- Set Path
- The number of SET CURRENT PATH statements executed by the thread.
- Rename table
- The number of RENAME TABLE statements executed by the thread.
- Hold Locator
- The number of HOLD LOCATOR statements executed by the thread.
- Free Locator
- The number of FREE LOCATOR statements executed by the thread.
- Release
- The number of RELEASE statements executed by the thread.
- Assoc Locator
- The number of ASSOCIATE LOCATOR statements executed by the thread.
- Alloc Cursor
- The number of ALLOCATE CURSOR statements executed by the thread.
- Merge
- The number of MERGE statements executed by the thread.
- Rename Index
- The number of RENAME INDEX statements executed by the thread.
- Truncate Table
- The number of TRUNCATE TABLE statements executed by the thread.
- Refresh Table
- Number of refresh table statements. DB2 12 and later. (Field name: QXTREFTBL)
- Transfer Owner
- Number of transfer ownership statements. DB2 12 and later. (Field name: QXTRNOWN)
- Creates, Drops, Alters:
- Table
- The number of CREATE, DROP, or ALTER TABLE statements executed by the thread.
- Index
- The number of CREATE, DROP, or ALTER INDEX statements executed by the thread.
- Table Space
- The number of CREATE, DROP, or ALTER TABLESPACE statements executed by the thread.
- Data Base
- The number of CREATE, DROP, or ALTER DATABASE statements executed by the thread.
- Storage Group
- The number of CREATE, DROP, or ALTER STOGROUP statements executed by the thread.
- Synonym
- The number of CREATE or DROP SYNONYM statements executed by the thread.
- View
- The number of CREATE or DROP VIEW statements executed by the thread.
- Alias
- The number of CREATE or DROP ALIAS statements executed by the thread.
- Function
- The number of CREATE, DROP, or ALTER FUNCTION statements executed by the thread.
- Procedure
- The number of CREATE, DROP, or ALTER PROCEDURE statements executed by the thread.
- Sequence
- The number of CREATE, DROP, or ALTER PROCEDURE statements executed by the thread.
- Role
- The number of CREATE, DROP, or ROLE statements executed by the thread.
- Trusted CTX
- The number of CREATE, DROP, or ALTER TRUSTED CONTENT statements executed by the thread.
- Mask/Perm
- The number of CREATE, DROP, or ALTER MASK/PERM statements executed by the thread.
- Variable
- The number of CREATE, or DROP Variable statements executed by the thread. DB2 11 and above.
- Trigger
- The number of CREATE or DROP DISTINCT TRIGGER statements executed by the thread.
- Dist Type
- The number of CREATE or DROP DISTINCT TYPE statements executed by the thread.
- Aux Table
- The number of CREATE AUXILIARY TABLE statements executed by the thread.
- Package
- The number of the DROP PACKAGE statements executed by the thread.
- JAR
- The number of ALTER JAR statements executed by the thread.
- Glob Temp Tab
- The number of CREATE GLOBAL TEMPORARY TABLE statements executed by the thread.
- Declare GTT
- The number of DECLARE GLOBAL TEMPORARY TABLE statements executed by the thread.
RID List:
- Successful
- The number of times RID list processing used. This field is incremented once for a given table access for Index Access with list prefetch and for Multiple Index Access.
- To Workfile (No Storage)
- The number of times a RID list was overflown to a work file because no RIDPOOL storage was available to hold the list of RIDs. DB2 10 and above.
- Not Used (No Storage)
- The number of times a RID list was not used for a given RID list process involving one index (Index Access with list prefetch) or involving multiple indexes (Multiple Index Access) because no storage was available to hold the list of RIDs.
- To Workfile (Max Limit)
- The number of times a RID list was overflown to a work file because the number of RIDs exceeded one or more internal limits. DB2 10 and above.
- Not Used (Max Limit)
- The number of times one or more RID lists were not used for a given RID list process involving one index (Index Access with list prefetch) or involving multiple indexes (Multiple Index Access) because the number of RIDs exceeded one or more internal limits.
- Interrupted (No Storage)
- The number of times a RID list append for a Hybrid Join was interrupted because no RIDPOOL storage was available to hold the list of RIDs (that is, number of times DB2 interrupted the RID phase and switched to the Data phase). DB2 10 and above.
- Skipped (Index Known)
- The number of times a RID list retrieval for multiple index access was skipped because it was not necessary due to DB2 being able to predetermine the outcome of index ANDing or ORing. DB2 10 and above.
- Interrupted (Max Limit)
- The number of times a RID list append for a Hybrid Join was interrupted because the number of RIDs exceeded one or more internal limits (that is, number of times DB2 interrupted the RID phase and switched to the Data phase). DB2 10 and above.
- Skipped (Adaptive IX)
- The number of times a DM is not called for RID list retrieval for multiple index access or LPF
because it was not necessary due to Runtime Adaptive Index processing being able to predetermine the
outcome. DB2 12 and later. (Field name: QXRSDMAD)
Query Parallelism
fields:
- Max Degree (Planned)
- The planned maximum degree of parallelism for a parall group. This value is the optimal degree of parallelis that can be obtained at execution time, after host variables or parameter markers are resolved, and before buffer pool negotiation and system negotiation are performed. DB2 11 and above.
- Parallel Group Degenerated
- Total number of parallel groups that changed to sequential mode during optimization, for reasons such as, the result of evaluation of a parallel group is that zero rows are returned, or a parallel group is partitioned on a single record. DB2 11 and above.
- Max Degree (Estimated)
- The estimated maximum degree of parallelism for a parallel group. This value is estimated at bind time, based on the cost formula. If a parallel group contains a host variable or parameter marker, the estimate is based on assumed values. DB2 11 and above.
- Reform Parallelism (Config Chg)
- The total number of parallel groups for which DB2 reformulated the parallel portion of the access path because the sysplex configuration was different from the sysplex configuration at bind time. This counter is only incremented by the parallelism coordinator at run time.
- Max Degree (Executed)
- Maximum degree of parallelism among the parallel groups to indicate the extent to which query parallelism applies.
- Reform Parallelism (No Buf)
- The total number of parallel groups for which DB2 reformulated the parallel portion of the access path because there was not enough buffer pool resource. This counter is only incremented by the parallelism coordinator at run time.
- Ran Planned
- The total number of parallel groups that have a planned degree greater than one at run time, and were executed to the same degree because of sufficient storage on the buffer pool.
- Total Parallel Groups
- The total number of parallel groups executed.
- Ran Reduced (Storage)
- The total number of parallel groups that have a planned degree greater than one at run time, but were processed to a parallel degree less than planned because of a storage shortage or contention on the buffer pool.
- Groups Intended
- The total number of parallel groups that were intended to run across the data sharing group. This count is only incremented on the parallelism coordinator at run time.
- Ran Reduced (Negot)
- The total number of parallel groups for which the degree of parallelism was reduced as a result of parallel system negotiation because system resources were constrained. DB2 11 and above.
- Members Skipped
- The number of times that the parallelism coordinator had to bypass a DB2 when distributing tasks because there was not enough buffer pool storage on one or more DB2 members. This field is incremented only on the parallelism coordinator, and it is only incremented once per parallel group, even though it is possible that more than one DB2 had a buffer pool shortage for that parallel group. The purpose of this count is to indicate when there are not enough buffers on a member. Therefore, this count is incremented only when the buffer pool is defined to allow parallelism. For example, if VPXPSEQT = 0 on an assistant, DB2 does not send parallel work there, but this count is not incremented.
- Ran Seq (Cursor)
- The total number of parallel groups which fell back to sequential mode because the cursor may be used in UPDATE/DELETE.
- One DB2 (Coord=NO)
- The total number of parallel groups executed on a single DB2 due to the COORDINATOR subsystem parameter being set to NO. When the statement was bound, the COORDINATOR subsystem parameter was set to YES. This situation can also occur when a plan or package is bound on one DB2 where COORDINATOR=YES but is run on a DB2 where COORDINATOR=NO.
- Ran Seq (No Buffer)
- The total number of parallel groups that have a planned degree greater than one at run time, but fell back to sequential mode because of storage shortage or contention on the buffer pool.
- One DB2 (Isolation)
- The total number of parallel groups executed on a single DB2 due to repeatable-read or read-stability isolation.
- Ran Seq (No ESA Sort)
- The total number of parallel groups that fell back to sequential operation because of a lack of ESA sort support.
- One DB2 (DCL GTT)
- The total number of parallel groups that are part of a query block using a UDF and executed on a single DB2 due to the existence of a Declared Temporary Table in the application process. Neither the query block nor the parallel group references a Declared Temporary Table, and a parallel group may or may not use a UDF.
- Ran Seq (Autonomous)
- The total number of parallel groups that changed to sequential mode because they were executing under an autonomous procedure. DB2 11 and above.
- Ran Seq (Negotiate)
- The total number of parallel groups that changed to sequential mode as a result of parallel system negotiation because system resources were constrained. DB2 11 and above.
Miscellaneous
fields:
- Maximimum LOB Storage
- The maximum storage used for LOB values, in Megabytes.
- Maximum XML Storage
- The maximum storage used for XML values.
- Maximum Nested SQL
- Maximum level of nested SQL cascading because of Triggers, User-Defined Functions, or Stored Procedures.
- Array Expansions
- The number of times an array variable was expanded to be larger than 32KB. DB2 11 and above.
- Sparse IX (Disabled)
- The number of times that sparse index was disabled because of insufficient storage. DB2 11 and above.
- Sparse IX (Built WF)
- The number of times that sparse index built a physical work file for probing. DB2 11 and above.
- Fetched 1 Block Only (QXR1BOAD)
- The number of times 1 block is fetched and there were no further fetches. DB2 12 and later.
Trigger
fields:
- Statement Trigger
- Number of times a Statement Trigger is activated.
- Row Trigger
- Number of times a Row Trigger is activated.
- SQL Errors
- Number of times an SQL error occurred during execution of a triggered action.
Stored procedures:
- Executed
- The number of CALL statements executed by the thread.
- Timed Out
- The number of times an SQL CALL timed out waiting to be scheduled. No TCB was available in the stored procedures address space or the procedure was in the STOP ACTION(QUEUE) state.
- Rejected
- The number of times an SQL CALL was rejected because the procedure was in the STOP ACTION(REJECT) state.
- Abended
- The number of times a stored procedure terminated abnormally.
User-defined
functions (UDF) fields:
- Executed
- The number of user-defined functions (UDFs) executed.
- Abended
- The number of times a UDF abended.
- Timed Out
- The number of times a UDF timed out when waiting to be scheduled.
- Rejected
- The number of times a UDF was rejected.
Prepare
Statistics:
- Prepares (Copy Found)
- The number of times that DB2 satisfied a prepare request by making a copy of a statement in the Prepared Statement Cache.
- Prepare (Copy Not Found)
- The number of times that DB2 searched the Prepared Statement Cache but could not find a suitable prepared statement.
- Prepares (Catalog Found)
- The number of times a PREPARE request is satisfied by making a copy from the stabilized
statement in SYSIBM.SYSDYNQRY catalog table. The stabilized statement search is done only when no
matching statement is found in the prepared statement cache. DB2 12 and later. (Field name:
QXSTSFND)
- Prepare (Restrict IX)
- The number of prepare statements for which the use of indexes was restricted because the indexes were in a pending state.
- Literals (Parsed)
- Number of times DB2 parsed dynamic statements because of CONCENTRATE STATEMENTS WITH LITERALS behaviour.
- Literals (Replaced)
- Number of times DB2 replaced at least one literal in a dynamic statement because of CONCENTRATE STATEMENTS WITH LITERALS behaviour.
- Literals (Matched)
- Number of times DB2 found a matching reusable copy of a dynamic statement in cache because of CONCENTRATE STATEMENTS WITH LITERALS behaviour.
- Literals (Duplicated)
- Number of times DB2 created a duplicate stmt instance in the statement cache because of CONCENTRATE STATEMENTS WITH LITERALS behaviour.
- KeepDyn (Prepared)
- The number of times that DB2 did an implicit prepare for a statement bound with KEEPDYNAMIC(YES), because the Prepared Statement Cache did not contain a valid copy of the prepared statement.
- KeepDyn (Avoided)
- The number of times that DB2 did not prepare a statement bound with KEEPDYNAMIC(YES), because the Prepared Statement Cache contained a valid copy of the prepared statement.
- KeepDyn (Exceed Limit)
- The number of times that DB2 discarded a prepared statement from the Prepared Statement Cache, because the number of prepared statements in the cache exceeded the value of subsystem parameter MAXKEEPD.
- KeepDyn (Invalidated)
- The number of times that DB2 discarded a prepared statement from the Prepared Statement Cache, because a program executed a DROP, ALTER, or REVOKE statement against a dependent object.
Direct
Row Access fields:
- Direct Access
- The number of times that DB2 used Direct Row Access to locate a record.
- Index Used
- The number of times that DB2 attempted to use Direct Row Access but reverted to using an Index to locate a record.
- TS Scan Used
- The number of times that DB2 attempted to use Direct row Access but reverted to using a Table Space scan to locate a record.
Row Processing:
- Rows Fetched
- The number of rows fetched.
- Rows Inserted
- The number of rows inserted.
- Rows Updated
- The number of rows updated.
- Rows Deleted
- The number of rows deleted.