Db2 System States attributes

Use the Db2 System States attributes to create situations to monitor system-level performance and exception alerts.

Active Stored Procedures The number of stored procedures currently executing.

Active Triggers The number of triggers currently executing.

Active User Functions The number of user defined functions currently executing.

Allowed locks per TS The default (SYSTEM) for the LOCKMAX clause of the SQL statements CREATE TABLESPACE and ALTER TABLESPACE. Install parameter LOCKS PER TABLE(SPACE) on panel DSNTIPJ, or ZPARM NUMLKTS in DSN6SPRM.

Archive Device The CCUU address of the device that is to be used for mounting the tape. It is an alphanumeric text string, with a maximum length of four characters.

Archive DSN The data set name of the archive file being waited on. It is an alphanumeric text string, with a maximum length of 44 characters.

Archive Volser The volser for tape volume being waited on. It is an alphanumeric text string, with a maximum length of six characters.

ASIDs Stored Procedures The number of unique active threads executing stored procedures.

ASIDs User Functions The number of ASIDs executing user functions.

Bytes written to log The log rate for the active log data sets in MB per second. This figure is valid for dual logging. If single logging is used, multiply the value shown by 2. This rate is MB/sec at which data is written to the active log data set. It was calculated by multiplying QJSTCIWR (label LOG CI WRITTEN (LOG1&2)) by 4096, then dividing the result by (1024 * 1024 * statistics-interval-seconds * 2). When the value exceeds 10MB/sec per log copy, you should examine I/O tuning of log data sets; for example, using faster log devices and/or I/O striping, using variable-length or compressed log record layouts to reduce log data size.

CF global contention The total number of suspends because of contention divided by the total number of synchronous requests that went to XES, and the lock requests that were converted from synchronous to asynchronous locks, and the locks because of child lock propagation. If multiple members from the same data sharing group run on the same LPAR, the global contention rate should be ignored for a member where the QTGSFCON flag is zero. The QTGSFCON flag indicates whether the false contention is reported at the subsystem (=1) or LPAR level (=0).

Checkpoint freq Checkpoint frequency. This shows either the number of minutes (1 through 60) or the number of Db2 log records between the start of successive checkpoints. Db2 starts a new checkpoint when this value is reached. You can use the SET LOG command to change the number of log records between checkpoints dynamically. Valid values are 1-60 when specifying a time value and 200-16000000 when specifying a number of records. Install parameter CHECKPOINT FREQ on panel DSNTIPL, ZPARM CHKFREQ in DSN6SYSP.

Class castout thresh reached The number of times group buffer pool castout was initiated because the group buffer pool class castout threshold was detected. The class castout threshold is one of two group buffer pool thresholds. In most cases the default value for the class threshold (10 percent) is a good choice. Depending on your workload, altering this value can reduce DASD contention during castout.

Current Open Data Set The current number of open data sets.

Current Thread Count The current number of active threads.

Db2 ID The name of a Db2 subsystem.

Db2 version The Db2 version.

DB Wait Percent The percentage of threads that are waiting for database services. Valid entry ranges from 0.0 to 100.0.

DeadLocks The number of times deadlocks were detected. This number should be low, ideally 0. Deadlocks occur when two or more application processes each hold locks on resources that the others need, without which they cannot proceed. Ensure that all applications accessing the same tables access them in the same order. Deadlocks can also occur through index page splits if there is high insert activity. In this case, the recommendation is to set SUBPAGES to 1 for the index. This field is incremented once for each deadlock encountered. There is no correlation between this field and the deadlock events reported in the Locking report set or the number of IFCID 172 records written. This field reports all deadlocks, regardless of how they were resolved. The locking report and record trace IFCID 172 show only those deadlocks that were resolved by Db2.

Dist DB Inactive The state will be true if this Db2 system is not enabled for the Distributed Data Facility (DDF). It is an alphanumeric text string, with a maximum length of one character.

Dist Receive Rate The DDF Receive bytes per second in units of 1,000.

Dist Send Rate The DDF Send bytes per second in units of 1,000.

DM critical thresh reached The number of times the deferred write threshold (DWTH) was reached. This threshold is a percentage of the virtual buffer pool that might be occupied by unavailable pages, including both updated pages and pages in use. Db2 checks this threshold when an update to a page is completed. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, write operations are scheduled for enough data sets (up to 128 pages per data set) to reduce the number of unavailable buffers to 10% below the threshold.

DSC active Indicates whether prepared dynamic SQL statements are saved for later use by eligible application processes in the EDM pool. Install parameter CACHE DYNAMIC SQL on panel DSNTIP8, or ZPARM CACHEDYN in DSN6SPRM.

DSC size The size of the statement cache that can be used by the Environmental Descriptor Manager (EDM). This value is used at Db2 startup time as the minimum value. You can increase and subsequently decrease this value with the SET SYSPARM command. This value cannot be decreased below the value that is specified at Db2 startup. The CLIST calculates a statement cache size. This storage pool is located above the 2 GB bar. The value used at Db2 startup time is either calculated by the CLIST based on input from other installation information or an override value. For record trace, this value is shown in bytes. Install parameter EDM STATEMENT CACHE on panel DSNTIPC, or ZPARM EDMSTMTC in DSN6SPRM.

DSMAX The maximum number of open data sets allowed to be specified in DSNZPARM.

DSMAX Utilization The number of data sets opened is approaching the maximum number of open data sets defined by the DSMAX parameter in DSNZPARM.

DWQT reached The number of times the deferred write threshold (DWTH) was reached. This threshold is a percentage of the virtual buffer pool that might be occupied by unavailable pages, including both updated pages and pages in use. Db2 checks this threshold when an update to a page is completed. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, write operations are scheduled for enough data sets (up to 128 pages per data set) to reduce the number of unavailable buffers to 10% below the threshold.

EDM Current Pages The current number of EDM pages (Db2 9 or below) or the current number of DBD pages (Db2 10 or later) that are used.

EDM Pool full The total number of failures because the EDM pool or EDM pool was full.

EDM Total Pages The total number of pages allocated for the EDM pool (Db2 9 or below) or the total number of pages allocated for the DBD pool (Db2 10 or later).

EDM Utilization The current EDM pages divided by total EDM pages (Db2 9 or below) or the current DBD pages divided by total DBD pages (Db2 10 or later).

GBP castout thresh reached The number of times a group buffer pool castout was initiated because the group buffer pool castout threshold was detected. The GBP castout threshold, GBP class castout threshold, and the length of the GBP checkpoint interval determine the castout characteristics of the group buffer pool. You can consider this threshold a safety margin to protect the group buffer pool from being accidentally flooded by overactive applications. In most situations, the default value for the group buffer pool castout threshold of 50 percent is a good choice. Use the ALTER GROUPBUFFERPOOL command to tune the group buffer pool thresholds. A value near to 100 indicates that in most cases Db2 found skeleton copies of prepared statements in global dynamic cache and could perform short prepares. A value near to 0 indicates that in most cases skeleton copies of prepared statements were not found in global dynamic cache and full prepares were performed.

Global cache hit ratio The ratio of successful search requests for prepared statements from the global dynamic SQL cache. This indicates the effectiveness of the global dynamic SQL cache in the EDM pool.

Global Trace Active The state will be true if this Db2 system currently has the global tracing active. It is an alphanumeric text string, with a maximum length of one character.

Group or Subsystem Name The DB2ID or data sharing group name. It is an alphanumeric text string with a maximum of 8 characters; for example, TDDB241G.

Group Object Analysis Status The status of the Event Manager for Object Analysis.

Group or Subsystem Type Indicates whether a group or subsystem is a Db2 or a data sharing group.

Incomp retained locks The number of global lock or change requests denied or suspended due to an incompatible retained lock.

In Doubt Threads The number of threads that are in an INDOUBT status.

Indoubt-URs The number of indoubt units of recovery. A unit of recovery is indoubt when a failure occurs after a successful prepare but before a successful commit. The failure can occur in the address space of the application, the transaction manager, Db2, or all of these. IMS and CICS applications use the prepare and commit sequence to commit work. Ideally, this value should be 0.

Interval Time The number of seconds since last sample.

Lock Conflict Count The total number of owners and waiters that are in lock conflict. Valid value is an integer in the range 0 - 99999999.

Lock Escalations Number of all types of lock escalations.

Lock Escalation Exclusive The number of times that the allowable number of locks per table space was exceeded resulting in page (IX) lock to escalate to table or table space lock in exclusive mode.

Lock Escalation Rate The number of lock escalations (exclusive and shared) per second in this interval.

Lock Escalation Shared The number of times that the allowable number of locks per table space was exceeded, resulting in page (IS) lock to escalate to table or table space lock in shared mode.

Max active DBATs The maximum number of database access threads (DBATs) that can be active concurrently. The maximum number of database access threads (DBATs) that can be active concurrently. When this limit has been reached, Db2 uses the value of DDF THREADS on panel DSNTIPR to decide how to handle a new allocation request. When DDF THREADS is ACTIVE and MAX REMOTE CONNECTED has not been reached, the allocation request is allowed but any further processing for the connection is queued waiting for an active database access thread to terminate. When DDF THREADS is INACTIVE and MAX REMOTE CONNECTED has not been reached, the allocation request is allowed and is processed when Db2 can assign an unused database access thread slot to the connection. The total number of threads accessing data concurrently is the sum of MAX USERS and MAX REMOTE ACTIVE. The maximum allowable value for this sum is 2000. Install parameter MAX REMOTE ACTIVE on panel DSNTIPE, or ZPARM MAXDBAT in DSN6SYSP.

Max degree Indicates the upper limit on the degree of parallelism for a parallel group. This field has a value of 0. This means PARAMDEG is not set and Db2 can set a default maximum degree of parallelism based on the system configuration. Install parameter MAX DEGREE on panel DSNTIP8, or ZPARM PARAMDEG in DSN6SPRM.

Max opened DS The maximum number of data sets that can be open at one time. The practical limit can be less than the MVS limit of 32727, depending on available storage below the line. Install parameter DSMAX on panel DSNTIPC, or ZPARM DSMAX in DSN6SPRM.

Max kept dyn stmt Shows the total number of prepared dynamic SQL statements that are saved past a commit point. 0 means that prepared dynamic SQL statements are not saved past commit points. Install parameter MAX KEPT DYN STMTS on panel DSNTIPE, or ZPARM MAXKEEPD in DSN6SPRM.

Max size of EDM Pool The size (in kilobytes) of the environmental descriptor manager (EDM) pool. This can be the value calculated by the CLIST, based on input from previous panels, or the value entered in the Override column at installation time. Install parameter EDMPOOL STORAGE SIZE on panel DSNTIPC, or ZPARM EDMPOOL in DSN6SPRM.

Merge error BP shortage The total number of work files that were rejected during all merge passes because of insufficient buffer resources. This field and the degraded low buffers field determine the average number of work files that cannot be honored at each merge pass because of insufficient buffer pool space. Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are too many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting. Consider increasing the size of the buffer pool using the ALTER BUFFERPOOL command. Note that, when there are many concurrent sorts or large sorts, it is a good idea to dedicate a separate buffer pool for sort work files. This will greatly facilitate work-file performance tuning.

Migrated DS timed out The number of recall timeouts.

MVS System An ID for the MVS System Management Facility (SMF). It is an alphanumeric text string with a maximum of 4 characters; for example SP11.

No QP BP shortage The total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.

No QP no MVS enclave serv The total number of parallel groups that executed in sequential mode due to the unavailability of MVS/ESA enclave services.

Nonstealable pages Percentage of non-stealable pages in use.

Number of Active DBATs The current number of active and disconnected (pooled) DBATs.

Number of batch users The number of connections to a single instance from batch or TSO background tasks.

Number of Db2 systems The total number of monitored Db2 systems. Valid value is an integer in the range 1 - 64.

Number of GBP Connections The total number of Group Buffer Pool (GBP) Connections.

Number of TSO users The number of connections to a single instance from TSO foreground tasks.

Object Analysis DB Count The total number of monitored databases in a specific data sharing group that are participating in object analysis. Valid value is an integer in the range 0 - 4999.

Open DS thresh reached The number of data sets that were closed because the total number of open data sets reached the deferred close threshold value. The deferred close value is based on the value of DSMAX or the MVS DD limit (whichever is smaller).

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.

Output buffer full The number of waits caused by an unavailable output log buffer. When Db2 wants to write a log record and the log buffer is not available, Db2 and the application must wait for an available log buffer.

Output buffer size The output log buffer size in kilobytes. There is only one output log buffer per Db2 subsystem. Increasing this parameter reduces BSDS I/O updates when there is a buffer wraparound. Frequent wraparounds are likely in LOAD or REORG with logging, and mass insert operations. Increasing this parameter also helps avoid log write waits for an available buffer during heavy update workload. When the specified size is not a 4KB multiple, it is rounded up to the next 4 KB multiple. Install parameter OUTPUT BUFFER on DSNTIPL, or ZPARM OUTBUFF in DSN6LOGP.

Pages castout The number of times a group buffer pool castout was initiated because the group buffer pool castout threshold was detected. The GBP castout threshold, GBP class castout threshold, and the length of the GBP checkpoint interval determine the castout characteristics of the group buffer pool. You can consider this threshold a safety margin to protect the group buffer pool from being accidentally flooded by overactive applications. In most situations, the default value for the group buffer pool castout threshold of 50 percent is a good choice. Use the ALTER GROUPBUFFERPOOL command to tune the group buffer pool thresholds.

Pages read from BPs The number of Getpage requests including conditional and unconditional requests.

Pages read from DASD The number of synchronous read I/O operations performed by Db2 for applications and utilities. This number includes both Synchronous Reads Sequential Access Only (QBSTSIO) and synchronous read operations for non-sequential access. You can use this value and the value of Synchronous Reads Sequential Access Only to calculate the number of Non-Sequential Synchronous Reads. Check the buffer pool hit ratio if the number of non-sequential synchronous reads is larger than expected.

Resource Timeout The number of seconds before a timeout is detected. This is an integer multiple of DEADLOCK TIME on panel DSNTIPJ. Timeout means that a lock request has waited for a resource (or for claims on a resource for a particular claim class to be released) longer than this time. For data sharing, the actual timeout period is longer than the timeout value. Install parameter RESOURCE TIMEOUT on panel DSNTIPI, or ZPARM IRLMRWT in DSN6SPRM.

Resource unavailable The number of read accesses delayed due to unavailable resources. Generally, this can be due to insufficient tape units allocated. If this is so, reissue the SET ARCHIVE command and use a higher value for the count parameter. Another (although unlikely) cause is insufficient archive log read service task availability.

Resync attempted The number of resynchronization connections attempted with all remote locations (two-phase commit operations only). A large value can indicate network or system problems.

RID Pool size The size of the RID pool in bytes.

RID Pool size too small The number of times the maximum RID pool storage was exceeded. 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 (5bytes per RID).

RIDPool Fail No Storage Due to insufficient storage for the candidate RID lists, Db2 was unable to use more than one index when accessing a Db2 table.

RIDPool Fail No Storage Rate The number of RIDPOOL fallbacks per second in this interval.

SMF overruns The total number of SMF buffer overruns. Ideally, this field should be 0 or very small.

Sort degraded BP too small The number of times that a merge pass was not efficiently performed due to a shortage of space in the buffer pool. The number in this field is incremented for each merge pass where the maximum number of work files allowed is less than the number of work-files requested. The maximum number of work files allowed is calculated as follows: Buffers consumed=2*(work files already allocated); Buffers available = (sequential steal threshold * buffer pool size - buffers consumed); Maximum work files allowed = buffers available / (2 * 8); Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are too many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting. Consider increasing the buffer pool size using the ALTER BUFFERPOOL command.

Sort error BP shortage The number of times a work file could not be created due to insufficient buffer resources. It indicates that a sort is in progress and limited in regard to the number of work files it can use. Ideally, this should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are many concurrent work files. For example, there could be a number of open cursors that require sorting. Generally, sorts are performed more efficiently with additional work files, but there are internal Db2 limits on the number of work files a transaction can have. It is possible that at run time a transaction cannot use as many work files as it had planned. You can control this by increasing the buffer pool size (ALTER BUFFERPOOL), or changing the transaction so it requires fewer concurrent work files.

SP abends The number of times a stored procedure terminated abnormally.

SP start failed/rejected The number of times an SQL CALL statement was rejected due to the procedure being in the STOP ACTION(REJECT) state.

SP timed out The number of times an SQL call timed out waiting to be scheduled.

Status Indicates whether a Db2 subsystem or data sharing group for a monitored entity is operational. Valid values are OFFLINE or ONLINE.

Storage Contractions The number of 31-bit storage contractions.

Tape volume contention The number of read accesses that were delayed because of a tape volume contention when only one reader per tape is possible.

Threads Wait Limit The number of inactive connections waiting because the system thread limit (MAXDBAT) has been reached: If CONQUED is less than or equal to DSCDBAT and CONQUED is less than or equal to (MDBAT-ADBAT), inactive connections waiting equals to 0, otherwise inactive connections waiting equals to CONQUED - (MDBAT-ADBAT+DSCDBAT); refer to the Db2 DISPLAY DDF DETAIL command for the field names.

Threads Wait Lock The number of threads in a suspend state because they are waiting to obtain a lock.

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

Timeouts The number of times a unit of work was suspended for a time exceeding the timeout value. This number should be low, ideally 0.

Transactions per second The number of successful requests for commit phase 1 in a two-phase commit environment such as CICS or IMS. It includes successfully prepared agents associated with threads that use the Recoverable Resource Manager Services Attach Facility (RRSAF). It does not include successful single-phase commits or distributed two-phase commits. IMS and CICS applications use the PREPARE and COMMIT sequence to commit work.

Trigger Depth The depth of the largest trigger stack.

UDF abended The number of times a user-defined function abended.

UDF start failed/rejected The number of times a user-defined function was rejected.

UDF timed out The number of times a user-defined function timed out while waiting to be scheduled.

Users Waiting Threads The count of users waiting for threads.

Wait Tape Mount The state will be true if the Db2 system is waiting on a tape mount to recover from an archive log. It is an alphanumeric text string, with a maximum length of one character.

Write failed no storage The number of coupling facility write requests that could not complete due to a lack of coupling facility storage resources. It is an alphanumeric text string, with a maximum length of one character.