SQL/RID Pool/Parallelism/Stored Procedure Information
This panel shows information about the current SQL, RID pool, Parallelism, and Stored Procedure activities.
________________ ZSQLC VTM O2 V540./I SE11 07/16/13 15:21:01 2
> Help PF1 Back PF3 Up PF7 Down PF8
> R.J
>
> H-HISTORICAL
===============================================================================
> SQL/RID POOL/PARALLELISM/STORED PROCEDURE INFORMATION
SQLC
+ Collection Interval: REALTIME Start: 07/16 15:14:09
+ Report Interval: 2 min End: 07/16 15:21:01
+
++ TOTAL INTERVAL /SECOND /THREAD /COMMIT % OF
+ SQL Manipulative (DML) QUANTITY QUANTITY ( 124) ( 6) ( 20) DML
+ ---------------------- -------- -------- ------- ------- ------- ------
+ SELECT 243 0 .00 .00 .00 .00
+ INSERT 2 0 .00 .00 .00 .00
+ UPDATE 0 0 .00 .00 .00 .00
+ MERGE 0 0 .00 .00 .00 .00
+ DELETE 1 0 .00 .00 .00 .00
+ DESCRIBE 10040 24 .19 4.00 1.20 2.16
+ DESCRIBE TABLE 0 0 .00 .00 .00 .00
+ PREPARE 10148 37 .29 6.16 1.85 3.33
+ OPEN CURSOR 10202 13 .10 2.16 .65 1.17
+ FETCH 532574 1017 8.20 169.50 50.85 91.78
+ CLOSE CURSOR 178 13 .10 2.16 .65 1.17
+ REFRESH TABLE 4 4 .03 .66 .20 .36 *DB2 12
+ Total DML 563392 1108 8.93 184.66 55.40 100.00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT % OF
+ SQL Control (DCL) QUANTITY QUANTITY ( 124) ( 6) ( 20) DCL
+ ---------------------- -------- -------- ------- ------- ------- ------
+ CALL 5 0 .00 .00 .00 .00
+ CONNECT (TYPE 1) 0 0 .00 .00 .00 .00
+ CONNECT (TYPE 2) 10 0 .00 .00 .00 .00
+ GRANT 0 0 .00 .00 .00 .00
+ LOCK TABLE 0 0 .00 .00 .00 .00
+ RELEASE 0 0 .00 .00 .00 .00
+ REVOKE 0 0 .00 .00 .00 .00
+ SET CONNECTION 0 0 .00 .00 .00 .00
+ SET CURRENT DEGREE 0 0 .00 .00 .00 .00
+ SET CURRENT RULES 0 0 .00 .00 .00 .00
+ SET CURRENT SQLID 8 2 .01 .33 .10 18.18
+ SET HOST VARIABLE 47 2 .01 .33 .10 18.18
+ SET CURRENT PATH 0 0 .00 .00 .00 .00
+ SET CURRENT PRECISION 0 0 .00 .00 .00 .00
+ ASSOCIATE LOCATOR 0 0 .00 .00 .00 .00
+ ALLOCATE CURSOR 0 0 .00 .00 .00 .00
+ HOLD LOCATOR 0 0 .00 .00 .00 .00
+ FREE LOCATOR 0 0 .00 .00 .00 .00
+ TRANSFER OWNERSHIP 7 7 .05 1.16 .35 63.63 *DB2 12
+ Total DCL 77 11 .08 1.83 .55 100.00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT % OF
+ SQL Definitional (DDL) QUANTITY QUANTITY ( 142) ( 2) ( 0) DDL
+ ---------------------- -------- -------- ------- ------- ------- ------
+ CREATE TABLE 0 0 .00 .00 .00 .00
+ CREATE GBL TEMP TABLE 0 0 .00 .00 .00 .00
+ DCL GBL TEMP TABLE 0 0 .00 .00 .00 .00
+ CREATE AUX TABLE 0 0 .00 .00 .00 .00
+ CREATE INDEX 0 0 .00 .00 .00 .00
+ CREATE TABLESPACE 0 0 .00 .00 .00 .00
+ CREATE DATABASE 0 0 .00 .00 .00 .00
+ CREATE STOGROUP 0 0 .00 .00 .00 .00
+ CREATE SYNONYM 0 0 .00 .00 .00 .00
+ CREATE VIEW 0 0 .00 .00 .00 .00
+ CREATE ALIAS 0 0 .00 .00 .00 .00
+ CREATE TRIGGER 0 0 .00 .00 .00 .00
+ CREATE DISTINCT TYPE 0 0 .00 .00 .00 .00
+ CREATE FUNCTION 0 0 .00 .00 .00 .00
+ CREATE PROCEDURE 0 0 .00 .00 .00 .00
+ CREATE SEQUENCE 0 0 .00 .00 .00 .00
+ CREATE ROLE 0 0 .00 .00 .00 .00
+ CREATE TRUSTED CTX 0 0 .00 .00 .00 .00
+ CREATE MASK/PERM 0 0 .00 .00 .00 .00
+ CREATE VARIABLE 0 0 .00 .00 .00 .00
+ DROP TABLE 0 0 .00 .00 .00 .00
+ DROP INDEX 0 0 .00 .00 .00 .00
+ DROP TABLESPACE 0 0 .00 .00 .00 .00
+ DROP DATABASE 0 0 .00 .00 .00 .00
+ DROP STOGROUP 0 0 .00 .00 .00 .00
+ DROP SYNONYM 0 0 .00 .00 .00 .00
+ DROP VIEW 0 0 .00 .00 .00 .00
+ DROP ALIAS 0 0 .00 .00 .00 .00
+ DROP PACKAGE 0 0 .00 .00 .00 .00
+ DROP TRIGGER 0 0 .00 .00 .00 .00
+ DROP DISTINCT TYPE 0 0 .00 .00 .00 .00
+ DROP PROCEDURE 0 0 .00 .00 .00 .00
+ DROP FUNCTION 0 0 .00 .00 .00 .00
+ DROP SEQUENCE 0 0 .00 .00 .00 .00
+ DROP TRUSTED CTX 0 0 .00 .00 .00 .00
+ DROP ROLE 0 0 .00 .00 .00 .00
+ DROP MASK/PERM 0 0 .00 .00 .00 .00
+ DROP VARIABLE 0 0 .00 .00 .00 .00
+ ALTER TABLE 0 0 .00 .00 .00 .00
+ ALTER INDEX 0 0 .00 .00 .00 .00
+ ALTER TABLESPACE 0 0 .00 .00 .00 .00
+ ALTER DATABASE 0 0 .00 .00 .00 .00
+ ALTER STOGROUP 0 0 .00 .00 .00 .00
+ ALTER VIEW 0 0 .00 .00 .00 .00
+ ALTER FUNCTION 0 0 .00 .00 .00 .00
+ ALTER PROCEDURE 0 0 .00 .00 .00 .00
+ ALTER SEQUENCE 0 0 .00 .00 .00 .00
+ ALTER JAR 0 0 .00 .00 .00 .00
+ ALTER TRUSTED CTX 0 0 .00 .00 .00 .00
+ ALTER MASK/PERM 0 0 .00 .00 .00 .00
+ RENAME TABLE 0 0 .00 .00 .00 .00
+ RENAME INDEX 0 0 .00 .00 .00 .00
+ TRUNCATED TABLE 0 0 .00 .00 .00 .00
+ COMMENT ON 0 0 .00 .00 .00 .00
+ LABEL ON 0 0 .00 .00 .00 .00
+ Total DDL 0 0 .00 .00 .00 .00
+ + TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ RID List Processing QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Successful 0 0 .00 .00 .00
+ Not Used (No Storage) 0 0 .00 .00 .00
+ Not Used (Max Limit) 0 0 .00 .00 .00
+ To WF (No Storage) 0 0 .00 .00 .00
+ To WF (Max Limit) 0 0 .00 .00 .00
+ Interrupted (No Stor) 0 0 .00 .00 .00
+ Interrupted (Max Lmt) 0 0 .00 .00 .00
+ Skipped (Adaptive IX) 9 0 .00 .00 .00 *DB2 12
+ Skipped (Index Known) 0 0 .00 .00 .00
+ Term (No Storage) 0 0 .00 .00 .00
+ Term (> RDS Limit) 0 0 .00 .00 .00
+ Term (> DM Limit) 0 0 .00 .00 .00
+ Term (> PROC Limit) 0 0 .00 .00 .00
+ HWM RID Blks Inuse 0 N/A N/A N/A N/A
+ Curr RID Blks Inuse 0 0 .00 .00 .00
+ HWM RID Blks in WF 0 N/A N/A N/A N/A
+ Curr RID Blks in WF 0 0 .00 .00 .00 +
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Query Parallelism QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ HWM degree Parallism 8 N/A N/A N/A N/A
+ Max Degree (Planned) 8 N/A N/A N/A N/A
+ Max Degree (Estimated) 8 N/A N/A N/A N/A
+ Max Degree (Executed) 1 0 .00 .00 .00
+ Ran Planned 1 0 .00 .00 .00
+ Ran Reduced (Storage) 0 0 .00 .00 .00
+ Ran Reduced (Negotia) 0 0 .00 .00 .00
+ Ran Seq (Cursor) 0 0 .00 .00 .00
+ Ran Seq (No Buffer) 0 0 .00 .00 .00
+ Ran Seq (No ESA Sort) 0 0 .00 .00 .00
+ Ran Seq (Autonomous) 0 0 .00 .00 .00
+ Ran Seq (Negotiate) 0 0 .00 .00 .00
+ One DB2 (Coord=No) 0 0 .00 .00 .00
+ One DB2 (Isolation) 0 0 .00 .00 .00
+ One DB2 (DCL GTT) 0 0 .00 .00 .00
+ Groups Intended 0 0 .00 .00 .00
+ Groups Skipped 0 0 .00 .00 .00
+ Reform (Config) 0 0 .00 .00 .00
+ Reform (No Buffer) 0 0 .00 .00 .00 + TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Miscellaneous QUANTITY QUANTITY ( 1) ( 10) ( 36)
+ ---------------------- -------- -------- ------- ------- -------
+ Incremental Bind 0 0 .00 .00 .00
+ Max SQL Levels 0 0 .00 .00 .00
+ Max LOB Storage (MB) 0 0 .00 .00 .00
+ Max XML Storage 0 0 .00 .00 .00
+ Array Expansions 0 0 .00 .00 .00
+ Sparse IX (Disabled) 0 0 .00 .00 .00
+ Sparse IX (Built WF) 0 0 .00 .00 .00
+ Fetched 1 Block Only 0 0 .00 .00 .00
+ Pipes Allocated 0 0 .00 .00 .00
+ Pipes Disabled 0 0 .00 .00 .00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Stored Procedures QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Abended 0 0 .00 .00 .00
+ Timed Out 0 0 .00 .00 .00
+ Rejected 0 0 .00 .00 .00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ User Defined Functions QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Executed 0 0 .00 .00 .00
+ Abended 0 0 .00 .00 .00
+ Timed Out 0 0 .00 .00 .00
+ Rejected 0 0 .00 .00 .00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Triggers QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Stmt Triggers Executed 0 0 .00 .00 .00
+ Row Triggers Executed 0 0 .00 .00 .00
+ SQL Error in Trigger 0 0 .00 .00 .00 +
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Dynamic SQL QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Prepares (Copy Found) 38 0 .00 .00 .00
+ Prepares (Catalog Fnd) 2 0 .00 .00 .00 *DB2 12
+ Prepares (Copy NFound) 46 0 .00 .00 .00
+ Prepares (Restrict IX) 0 0 .00 .00 .00
+ KeepDyn (Implicit) 0 0 .00 .00 .00
+ KeepDyn (Avoided) 0 0 .00 .00 .00
+ KeepDyn (Exceed Limit) 0 0 .00 .00 .00
+ KeepDyn (Invalidated) 0 0 .00 .00 .00
+ Literals (Parsed) 0 0 .00 .00 .00
+ Literals (Replaced) 0 0 .00 .00 .00
+ Literals (Matched) 0 0 .00 .00 .00
+ Literals (Duplicated) 0 0 .00 .00 .00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ ROW ID QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Successful 0 0 .00 .00 .00
+ Revert to Index 0 0 .00 .00 .00
+ Revert to TS Scan 0 0 .00 .00 .00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Row Processing QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Rows Fetched 503342 0 .00 .00 .00
+ Rows Inserted 0 0 .00 .00 .00
+ Rows Updated 0 0 .00 .00 .00
+ Rows Deleted 0 0 .00 .00 .00
+
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Currently Committed QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Insert Rows Skipped 0 0 .00 .00 .00
+ Delete Rows Accessed 0 0 .00 .00 .00
+ Update Rows Accessed 0 0 .00 .00 .00 +
+ TOTAL INTERVAL /SECOND /THREAD /COMMIT
+ Workfile Database QUANTITY QUANTITY ( 142) ( 2) ( 0)
+ ---------------------- -------- -------- ------- ------- -------
+ Curr WFDB For TS (KB) 134258K 0 .00 .00 .00
+ DGTT WF configed (KB) 0 0 .00 .00 .00
+ WF Stor Configed (KB) 0 0 .00 .00 .00
+ System WKDB threshold 90 0 .00 .00 .00
+ Max Total Storage 0 N/A N/A N/A N/A
+ HWM DGTT WF Stor (KB) 0 N/A N/A N/A N/A
+ HWM WF Storage (KB) 0 N/A N/A N/A N/A
+ Curr DGTT WF Stor (KB) 0 0 .00 .00 .00
+ Curr WF Storage (KB) 0 0 .00 .00 .00
+ Storage in 4K TS 0 0 .00 .00 .00
+ Storage in 32K TS 0 0 .00 .00 .00
+ 32K used instead of 4K 0 0 .00 .00 .00
+ 4K used instead of 32K 0 0 .00 .00 .00
+ HWM DM WF Count 0 N/A N/A N/A N/A
+ HWM NSort WF Count 0 N/A N/A N/A N/A
+ Curr DM WF Count 0 0 .00 .00 .00
+ Curr NSort WF Count 0 0 .00 .00 .00
+ HWM DM WF size (KB) 0 N/A N/A N/A N/A
+ Curr DM WF size (KB) 0 0 .00 .00 .00
+ HWM Sort WF Count 0 N/A N/A N/A N/A
+ Curr Sort WF Count 0 0 .00 .00 .00
+ HWM Sort WF size (KB) 0 N/A N/A N/A N/A
+ Curr Sort WF size (KB) 0 0 .00 .00 .00
+ WF Overflow to TS 0 0 .00 .00 .00
+ WF Not Created(NoStor) 0 0 .00 .00 .00
+ Agent Max Storage 0 0 .00 .00 .00
+ Number of Max Exceeded 0 0 .00 .00 .00
+ Agent WFDB threshold 0 0 .00 .00 .00
+ HWM WFDB by Thread(KB) 0 N/A N/A N/A N/A
===============================================================================Navigation
For additional information about
- Near-term history activity, select option H-HISTORICAL at the top of the panel.
- other topics, use the PF keys.
Fields
- Collection Interval
- This field displays REALTIME to indicate that you are looking at the realtime version of this panel and not at the corresponding near-term history panel. The collection interval and the report interval are the same in this panel.
- Start
- The start time of the report interval currently displayed.
- Report Interval
- The time in the last cycle (for example, between two presses of the Enter key).
- End
- The end time of the report interval currently displayed.
- Maximum Degree of Parallelism Executed
- The maximum degree of parallel I/O processing for all parallel groups. This is a high-water mark.
For each field described below the following statistics
are provided:
- The amount of activities that occur during the interval.
- Rate per minute during the last cycle. The number under /MINUTE is the number of minutes in the interval.
- Rate per thread during the last cycle. The number under /THREAD is the number of Create Threads during the interval.
- Rate per Commit during the last cycle. The number under /COMMIT is the number of commit requests (including abort requests) during the interval.
- Percentage of DML, DCL, or DDL during the last cycle.
SQL Manipulative (DML):
- SELECT
- SELECT statements executed to retrieve rows from a DB2® table.
- INSERT
- INSERT statements executed to add rows to a DB2 table.
- UPDATE
- UPDATE statements executed to alter existing rows in a DB2 table.
- MERGE
- The number of times a MERGE statement is executed.
- DELETE
- DELETE statements executed to remove rows from a DB2 table.
- DESCRIBE
- DESCRIBE statements executed to obtain information about prepared SQL statements.
- DESCRIBE TABLE
- DESCRIBE TABLE statements executed to obtain information about a table or view.
- PREPARE
- Occasions when SQL statements were dynamically prepared for execution.
- OPEN CURSOR
- OPEN statements executed to prepare cursors for subsequent Fetch operations.
- FETCH
- FETCH statements executed to retrieve rows from DB2 tables.
- CLOSE CURSOR
- CLOSE statements executed to close previously opened cursors.
- REFRESH TABLE
- Number of refresh table statements. DB2 12 and later. (Field name: QXTREFTBL)
- Total DML
- All data manipulative language statements.
SQL Control (DCL):
- CALL
- CALL statements executed to invoke a stored procedure.
- CONNECT (Type 1)
- CONNECT (Type 1) statements executed to connect an application process to a designated server.
- CONNECT (Type 2)
- CONNECT (Type 2) statements executed to connect an application process to a designated server.
- GRANT
- GRANT statements issued to extend DB2 privileges to users.
- LOCK TABLE
- LOCK TABLE statements issued to lock a tablespace or table in a segmented tablespace.
- RELEASE
- RELEASE statements executed to place one or more connections in the released state.
- REVOKE
- REVOKE statements issued to revoke users' DB2 privileges.
- SET CONNECTION
- SET CONNECTION statements executed to establish the application server of the process.
- SET CURRENT DEGREE
- SET CURRENT DEGREE statements executed to assign a value to the CURRENT DEGREE special register.
- SET CURRENT RULES
- SET CURRENT RULE statements executed to assign a value to the current rules special register.
- SET CURRENT SQLID
- SET CURRENT SQLID statements issued to change your current authorization ID.
- SET HOST VARIABLE
- SET host-variable statements issued.
- SET CURRENT PATH
- SET CURRENT PATH statements issued to assign a value to the CURRENT PATH special register.
- SET CURRENT PRECISION
- The number of Set Current® Precision statements.
- ASSOCIATE LOCATOR
- The number of ASSOCIATE LOCATOR statements issued.
- ALLOCATE CURSOR
- The number of ALLOCATE CURSOR statements issued.
- HOLD LOCATOR
- The number of Hold Locator statements.
- FREE LOCATOR
- The number of Free Locator statements.
- TRANSFER OWNERSHIP
- Number of transfer ownership statements. DB2 12 and later. (Field name: QXTRNOWN)
- Total DCL
- All data control language statements.
SQL Definitional (DDL):
- CREATE TABLE
- CREATE TABLE statements issued to define a DB2 table.
- CREATE GBL TEMP TABLE
- The number of Create Global Temporary Table statements issued to create a description of a temporary table at the current server.
- DCL GBL TEMP TABLE TH
- The number of SQL Declare Global Temporary Table statements.
- CREATE AUX TABLE
- The number of Create Auxiliary Table statements.
- CREATE INDEX
- CREATE INDEX statements issued to establish indexes on DB2 tables.
- CREATE TABLESPACE
- CREATE TABLESPACE statements issued to establish DB2 tablespaces.
- CREATE DATABASE
- CREATE DATABASE statements issued to establish DB2 databases.
- CREATE STOGROUP
- CREATE STOGROUP statements issued to establish DB2 storage groups.
- CREATE SYNONYM
- CREATE SYNONYM statements issued to create alternate names for DB2 tables and views.
- CREATE VIEW
- CREATE VIEW statements issued to establish views of DB2 tables.
- CREATE ALIAS
- CREATE ALIAS statements issued to achieve "location transparency" of DB2 tables. This field is used primarily to refer to tables and views from remote DB2 subsystems in a distributed environment.
- CREATE TRIGGER
- CREATE TRIGGER statements issued to define a trigger in a schema and build a trigger package at the current server.
- CREATE DISTINCT TYPE
- CREATE DISTINCT TYPE statements issued to define a distinct type, which is a data type that a user defines. A distinct type must be sourced on one of the built-in data types.
- CREATE FUNCTION
- CREATE FUNCTION statements issued to register a user-defined function with an application server. You can register the following types of functions with this statement: external scalar, external table, and sourced.
- CREATE PROCEDURE
- CREATE PROCEDURE statements issued to define a stored procedure.
- CREATE SEQUENCE
- The number of Create Sequence statements.
- CREATE ROLE
- The number of Create Role statements.
- CREATE TRUSTED CTX
- The number of Create Trusted CTX statements.
- CREATE MASK/PERM
- The number of Create Mask or Create Permission statements.
- CREATE VARIABLE
- The number of Create Variable statements.DB2 11 and later.
- DROP TABLE
- DROP TABLE statements issued to remove tables from DB2 databases.
- DROP INDEX
- DROP INDEX statements issued to remove indexes from DB2 tables.
- DROP TABLESPACE
- DROP TABLESPACE statements issued to delete tablespaces.
- DROP DATABASE
- DROP DATABASE statements issued to delete databases.
- DROP STOGROUP
- DROP STOGROUP statements issued to delete storage group definitions.
- DROP SYNONYM
- DROP SYNONYM statements issued to delete alternative table names and view names.
- DROP VIEW
- DROP VIEW statements issued to delete table views.
- DROP ALIAS
- DROP ALIAS statements issued to delete view and table aliases from the DB2 catalog.
- DROP PACKAGE
- DROP PACKAGE statements issued to delete packages.
- DROP TRIGGER
- DROP TRIGGER statements issued to delete triggers.
- DROP FUNCTION
- DROP FUNCTION statements issued to delete user-defined functions.
- DROP DISTINCT TYPE
- DROP DISTINCT TYPE statements issued to delete user-defined data types.
- DROP PROCEDURE
- DROP PROCEDURE statements issued to delete stored procedures.
- DROP SEQUENCE
- The number of Drop Sequence statements.
- DROP TRUSTED CTX
- The number of Drop Trusted CTX statements.
- DROP ROLE
- The number of Drop Role statements.
- DROP MASK/PERM
- The number of Drop Mask or Drop Permission statements.
- DROP VARIABLE
- The number of Drop Variable statements. DB2 11 and later.
- ALTER TABLE
- ALTER TABLE statements issued to change table attributes.
- ALTER INDEX
- ALTER INDEX statements issued to change index attributes.
- ALTER TABLESPACE
- Alter Tablespace statements issued to change tablespace attributes.
- ALTER STOGROUP
- ALTER STOGROUP statements issued to add devices to and delete devices from storage groups.
- ALTER VIEW
- Number of Alter View statements.
- ALTER DATABASE
- ALTER DATABASE statements issued to change database attributes.
- ALTER FUNCTION
- ALTER FUNCTION statements issued to change the description of an external scalar or external table function at the current server.
- ALTER PROCEDURE
- ALTER PROCEDURE statements issued to change the description of a stored procedure at the current server.
- ALTER SEQUENCE
- The number of Alter Sequence statements.
- ALTER TRUSTED CTX
- The number of Alter Trusted CTX statements.
- ALTER JAR
- The number of Alter Jar statements.
- ALTER MASK/PERM
- The number of Alter Mask or Alter Permission statements.
- RENAME TABLE
- RENAME TABLE statements issued to rename an existing table.
- RENAME INDEX
- The number of Rename Index statements.
- TRUNCATED TABLE
- The number of Truncated Table statements issued to rename an existing table.
- COMMENT ON
- COMMENT ON statements issued to add or replace comments for user-defined objects (tables, views, columns, and sets of columns) in the DB2 catalog.
- LABEL ON
- LABEL ON statements issued to add or replace labels in DB2 catalog descriptions of tables, views, columns, and sets of columns.
- Total DDL
- All Data Definition Language statements.
RID List Processing: For each field described
below the following statistics are provided:
- The amount of activities that occur during the interval.
- Rate per minute.
- Rate per thread.
- Rate per Commit.
- Successful
- Number of times RID list processing was used when accessing a DB2 table.
- This field is incremented once for a given table access for Index Access with list prefetch and for Multiple Index Access.
- Not Used (No Storage)
- Number of times RID list processing was terminated because of insufficient storage to hold the list of RIDs.
- Not Used (Max Limit)
- Number of times RID list processing was terminated because the number of RIDs would exceed a RID limit or threshold.
- To WF (No Storage)
- The number of times a RID list overflows to a work file because RIDPOOL storage is not available to hold the list of RIDs.
- To WF (Max Limit)
- The number of times a RID list overflows to a work file because the number of RIDs exceeds internal limits.
- Interrupted (No Stor)
- The number of times a RID list append for a Hybrid Join is interrupted because RIDPOOL storage is not available to hold the list of RIDs. This is the number of times DB2 interrupted the RID phase and switched to the Data phase.
- Interrupted (Max Lmt)
- The number of times a RID list append for a Hybrid Join is interrupted because the number of RIDs exceeds internal limits. This is the number of times DB2 interrupted the RID phase and switched to the Data phase.
- 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)
- 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.
- Term (No Storage)
- The number of times RID list processing exhausted virtual storage.
- Term (> RDS Limit)
- The number of times RID list processing terminated because the number of RID entries was greater than the RDS limit. The RDS limit is the maximum (25% of table size, number of RIDs that can fit into the guaranteed number of RID blocks).
- Term (> DM Limit)
- The number of times RID list processing terminated because the number of RID entries was greater than the DM limit. The DM limit is approximately 26 million RIDs.
- Term (> PROC Limit)
- The number of times the maximum RID pool storage was exceeded. The default maximum RID pool size is the minimum (install value pool size, 10GB).
- HWM RID Blks Inuse
- The number of RID blocks currently in use.
- Curr RID Blks Inuse
- The highest number of RID blocks in use at any one time since DB2 startup time.
- HWM RID Blks in WF
- The highest number of RID blocks overflown (stored) to a work file at any time since DB2 startup.
- Curr RID Blks in WF
- The number of RID blocks currently residing in work file storage.
Query Parallelism: For each field described
below the following statistics are provided:
- Total quantity, which reflects the amount of activities since DB2 was started.
- Interval quantity, which reflect activity during the last cycle.
- Rate per minute during the last cycle.
- Rate per thread during the last cycle.
- Rate per Commit during the last cycle.
- HWM degree Parallism
- Maximum degree of parallelism among the parallel groups to indicate the extent to which query parallelism applies.
- Max Degree (Planned)
- The planned maximum degree of parallelism for a parallel group. This value is the optimal degree of parallelism 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 later.
- 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 later.
- Max Degree (Executed)
- Total number of parallel groups executed.
- Ran Planned
- 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.
- Ran Reduced (Storage)
- 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.
- Ran Reduced (Negotia)
- Number of parallel group degree to be reduced due to system negotiation result of system stress level. DB2 11 and later.
- Ran Seq (Cursor)
- Total number of parallel groups which fell back to sequential mode because the cursor might be used in UPDATE/DELETE.
- Ran Seq (No Buffer)
- 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.
- Ran Seq (No ESA Sort)
- Total number of parallel groups which fell back to sequential mode due to lack of ESA sort support.
- Ran Seq (Autonomous)
- Total number of parallel groups which fell back to sequential mode due to executing under an autonomous procedure. This applies only to DB2 11 or higher.
- Ran Seq (Negotiate)
- Number of parallel groups that is degenerated to sequential mode due to system negotiation result of system stress level.
- This applies only to DB2 11 or higher.
- One DB2 (Coord=No)
- Total number of parallel groups that are executed on a single DB2 because the COORDINATOR subsystem parameter is set to NO. When the statement is bound, the COORDINATOR subsystem parameter is set to YES. This situation might also occur when a plan or package is bound on a DB2 where the COORDINATOR subsystem parameter is set to YES, but is run on a DB2 where the subsystem parameter COORDINATOR is set to NO.
- One DB2 (Isolation)
- Total number of parallel groups that are executed on a single DB2 because of repeatable-read or read-stability isolation.
- One DB2 (DCL GTT)
- Total number of parallel groups that are part of a query block. The query block is using an UDF. It is executed on a single DB2 because a Declared Temporary Table exists in the application process. Neither the query block nor the parallel group is referencing a Declared Temporary Table. A parallel group might use or might not use an UDF.
- Groups Intended
- Total number of parallel groups that are intended to run across the data sharing group. This count is only incremented on the parallelism coordinator at run time.
- Groups Skipped
- The number of times that the parallelism coordinator must bypass a DB2 when distributing tasks because there is not enough buffer pool storage on one or more DB2 members.
- This field is incremented only on the parallelism coordinator. It is only incremented once per parallel group even though it is possible that more than one DB2 systems have 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 is set to 0 on an assistant, DB2 does not send parallel work to the assistant, but this count is not incremented.
- Reform (Config)
- Total number of parallel groups for which DB2 reformulated the parallel portion of the access path because the sysplex configuration is different from the sysplex configuration at bind time. This counter is only incremented by the parallelism coordinator at run time.
- Reform (No Buffer)
- Total number of parallel groups for which DB2 reformulated the paralllel 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.
Miscellaneous
- Incremental Bind
- Occurrences of incremental bind, which take place upon execution of a DB2 plan that is bound as VALIDATE(RUN).
- Max SQL Levels
- The maximum level of nested SQL cascading. This includes cascading because of triggers, UDFs, and stored procedures.
- Max LOB Storage (MB)
- Maximum storage that is used for LOB values.
- Max XML Storage
- Maximum storage that is used for XML values.
- Array Expansions
- The number of times an array variable is expanded beyond 32K.
- This applies to DB2 11 or higher.
- Sparse IX (Disabled)
- The number of times that sparse index is disabled because of insufficient storage.
- This applies to DB2 11 or higher.
- Sparse IX (Built WF)
- The number of times that sparse index built a physical work file for probing.
- This applies to DB2 11 or higher.
- Fetched 1 Block Only (QXR1BOAD)
- The number of times 1 block is fetched and there were no further fetches. DB2 12 and later.
- Pipes Allocated (QISTINPA)
- The number of data manager fast insert pipes that were allocated since DB2 restart. DB2 12 and later.
- Pipes Disabled (QISTINPD)
- The number of data manager fast insert pipes that were disabled since DB2 restart. DB2 12 and later.
Stored procedures: For each field described
below the following statistics are provided:
- Total quantity, which reflects the amount of activities since DB2 was started.
- Interval quantity, which reflects activity during the last cycle.
- Rate per minute during the last cycle.
- Rate per thread during the last cycle.
- Rate per Commit during the last cycle.
- Abended
- CALL statements executed to invoke a stored procedure that terminated abnormally.
- Timed Out
- CALL statements executed to invoke a stored procedure that timed out while waiting to be scheduled.
- Rejected
- CALL statements executed to invoke a stored procedure that was in the STOP ACTION(REJECT) state.
User Defined Functions: For
each field described below the following statistics are provided:
- Total quantity, which reflects the amount of activities since DB2 was started.
- Interval quantity, which reflects activity during the last cycle.
- Rate per minute during the last cycle.
- Rate per thread during the last cycle.
- Rate per Commit during the last cycle.
- 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.
Triggers: For each field described
below the following statistics are provided:
- Total quantity, which reflects the amount of activities since DB2 was started.
- Interval quantity, which reflects activity during the last cycle.
- Rate per minute during the last cycle.
- Rate per thread during the last cycle.
- Rate per Commit during the last cycle.
- Stmt Triggers Executed
- The number of times a statement trigger was activated.
- Row Triggers Activated
- The number of times a row trigger was activated.
- SQL Error in Trigger
- The number of times an SQL error occurred during execution of a triggered action.
Dynamic SQL
- Prepares (Copy Found)
- The number of times a PREPARE request is satisfied by making a copy from the prepared statement cache.
- 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)
- Prepares (Copy NFound)
- The number of times a PREPARE request is received but a matching statement is not found in the prepared statement cache. Cache search is only done for DML SQL and only if cache option is active.
- Prepares (Restrict IX)
- The number of PREPARE requests for which the use of index/indexes are restricted because the index is in a pending state.
- KeepDyn (Implicit)
- The number of times the following actions occurred:
- An implicit PREPARE is performed because the KEEPDYNAMIC(YES) option is used
- An OPEN, EXECUTE, or DESCRIBE of a dynamic statement occurred after a COMMIT, however, DB2 no longer had a valid copy of the executable version of the prepared statement.
- KeepDyn (Avoided)
- The number of times that a PREPARE is avoided because KEEPDYNAMIC(YES) is used together with prepared statement caching, and DB2 still had the copy of the executable version of the prepared statement.
- KeepDyn (Exceed Limit)
- The number of times that an executable copy of a prepared statement is discarded because the MAXKEEPD system limit is exceeded.
- KeepDyn (Invalidated)
- The number of times that a prepared statement is purged from the cache because a DROP, ALTER, or REVOKE statement is issued on a dependent object.
- Literals (Parsed)
- The number of times DB2 parsed dynamic statements because CONCENTRATE STATEMENTS WITH LITERALS behavior is in effect for the prepare of the statement for the dynamic statement cache.
- Literals (Replaced)
- The number of times DB2 replaced at least one literal in a dynamic statement because CONCENTRATE STATEMENTS WITH LITERALS is in effect for the prepare of the statement for dynamic statement cache.
- Literals (Matched)
- The number of times DB2 found a matching reusable copy of a dynamic statement in statement cache during preparation of a statement that had literals replaced because of CONCENTRATE.
- Literals (Duplicated)
- The number of times DB2 created a duplicate statement instance in the statement cache for a dynamic statement that had literals replaced by CONCENTRATE STATEMENTS WITH LITERALS behavior and the duplicate statement instance was needed because a cache match failed because of literal reusability criteria.
Row ID
- Successful
- The number of times that direct row access was successful.
- Revert to Index
- The number of times an attempt to use direct row access reverted to using an index to locate a record.
- Revert to TS Scan
- The number of times an attempt to use direct row access reverted to using a table space scan to locate a record.
Rows Processing
- Rows Fetched
- The number of fetched rows.
- Rows Inserted
- The number of inserted rows.
- Rows Updated
- The number of updated rows.
- Rows Deleted
- The number of deleted rows.
Currently Committed
- Insert Rows Skipped
- The number of rows that are skipped by read transactions because uncommitted inserts are performed when current committed read is in effect for fetch operations.
- Delete Rows Accessed
- The number of rows that are skipped by read transactions because uncommitted deletes are performed when current committed read is in effect for fetch operations.
- Update Rows Accessed
- The number of rows that are accessed by read transactions while uncommitted updates existed because currently committed read behavior is in effect for fetch operation.
Workfile Database (WFDB)
- Curr WFDB For TS (KB)
- Current total storage (KB) that is configured for all table spaces in the WFDB.
- This applies to DB2 11 or higher.
- DGTT WF configed (KB)
- Total preferred STORAGE (KB) that is configured for DGTTs in the WFDB.
- This applies to DB2 11 or higher.
- WF Stor Configed (KB)
- Total preferred STORAGE (KB) that is configured for work files in the WFDB.
- This applies to DB2 11 or higher.
- System WKDB threshold
- The value in percent (%) for the threshold of the system-level WFDB space usage alert.
- Valid values: 0 - 100
- This applies to DB2 11 or higher.
- Max Total Storage
- The maximum total storage (KBs) that is used in the WFDB at system level for DGTTs and work files since DB2 is started. After restart, this value starts again with 0.
- HWM DGTT WF Stor (KB)
- The highest total storage (KB) that is used for DGTTs by all agents on the system since DB2 is started.
- This applies to DB2 11 or higher.
- HWM WF Storage (KB)
- The highest total storage (KB) that is used for work files by all agents on the system since DB2 is started.
- This applies to DB2 11 or higher.
- Curr DGTT WF Stor (KB)
- Current total storage (KB) that is used for DGTTs by all agents on the system since DB2 is started.
- This applies to DB2 11 or higher.
- Curr WF Storage (KB)
- The current total storage (KB) that is used for work files by all agents on the system since DB2 started.
- This applies to DB2 11 or higher.
- Storage in 4K TS
- Current 4KB table space storage that is used (KB).
- Storage in 32K TS
- Current 32KB table space storage that is used (KB).
- 32K used instead of 4K
- The number of times a 32KB page TS is used when a 4KB page TS is to be preferred but not available.
- 4K used instead of 32K
- The number of times a 4KB page TS is used when a 32KB page TS is to be preferred but not available.
- HWM DM WF Count
- High watermark count (maximum number) of SORT-related DM in-memory work files that are active at any point in time since DB2 is started.
- HWM NSort WF Count
- High watermark count (maximum number) of non-SORT-related DM in-memory work files that are active at any point in time since DB2 is started.
- This applies to DB2 11 or higher.
- Curr DM WF Count
- The number of times SORT-related DM in-memory work files are currently active.
- Curr NSort WF Count
- The number of times non-SORT related DM in-memory work files are currently active.
- This applies to DB2 11 or higher.
- HWM DM WF size (KB)
- High watermark count of maximum space in KB that is used for the active DM In-Memory work files at any point in time since DB2 is started.
- Curr DM WF size (KB)
- Total space in KB that is used for the currently active DM In-Memory work files.
- HWM Sort WF Count
- High watermark count (maximum number) of Sort-Inmem work files that are active at any point in time since DB2 is started.
- Curr Sort WF Count
- The number of times Sort-Inmem work files are currently active.
- HWM Sort WF size (KB)
- High watermark count of maximum space that is used for the active Sort-Inmem work files at any point in time since DB2 is started.
- Curr Sort WF size (KB)
- Total Space that is used for the currently active Sort-Inmem work files.
- WF Overflow to TS
- The number of times the type-2 in-memory work files overflow into a physical tablespace since DB2 is started.
- This applies to DB2 11 or higher.
- WF Not Created(NoStor)
- The number of times a work file is not created as any type of DM in-memory work file because of critical storage conditions since DB2 is started.
- This applies to DB2 11 or higher.
- Agent Max Storage
- The limit of the maximum storage per agent (KB).
- Number of Max Exceeded
- The number of times the maximum storage limit is exceeded.
- Agent WFDB threshold
- The value for the threshold of the agent-level WFDB space usage alert.
- Valid values: 0 - 100.
- This applies to DB2 11 or higher.
- HWM WFDB by Thread(KB)
- The highest amount of WFDB storage (KB) that is used by any thread on the system since DB2 is started.
- This applies to DB2 11 or higher.