Help

 

Db2 SQL Activity Summary

The Db2 SQL Summary report is a summary of all of the SQL activity executed by the application being measured.  The reported data is obtained from a Db2 Accounting Trace. Db2 trace data is accumulated by identity.  The identity data items differ by type of application.

  • Batch summarizes by correlation ID.

  • CICS summarizes by transaction ID.

  • RRSAF summarizes by authorization ID.

  • DDF summarizes by end user's USERID, end user's transaction, and end user's workstation.

The activity summary report contains an entry for each identity and a totals entry containing the accumulation of each identity entry if more than one identity exists.  Multiple entries are shown in the report collapsed and must be expanded.

WLM CLass Name

WLM Class Name is the name of the WLM class under which the workload ran if it was assigned. This displays only if the WLM Class Name is available.

Enduser Identity

The Enduser unique identity can be established by one or more of the following elements:

  • Requester Location

  • Authorization ID

  • Correlation ID

  • End User ID

  • End User Workstation

  • End User Transaction

Resource usages

CPU usage can be monitored to note application SQL statement change improvements or migration from one type of processor to another.

  • SQL Central processor CPU usage (sec) shows  the CP CPU consumed on a central processor.

  • In Db2 shows the accumulated CP CPU time that is spent in Db2. This CPU time does not include the:

    • CP CPU time that is consumed on an IBM specialty engine

    • CPU time that is consumed while processing SQL statements in a stored procedure, trigger, or (UDF) user defined function.

  • Stored Procedure shows the CP CPU time accumulated in Db2 for processing SQL statements in stored procedures.
    This time does not include CPU time consumed on an IBM specialty engine.

  • Trigger shows the CP CPU time consumed executing trigger SQL statements on a nested task.
    This time does not include CPU consumed on an IBM specialty engine.

  • UDF shows the CP CPU time consumed executing user defined functions (UDFs) (for version 10 only).

  • Total shows the amount of CP CPU time used for all environments.
    This CPU time does not include CPU consumed on an IBM specialty engine.

  • SQL Specialty processor CPU usage (sec) shows the CPU consumed on specialty processors (zIIP).

  • In Db2 shows the SP CPU time consumed that is spent in Db2.
    This SP CPU time does not include the SP CPU time that is consumed while processing SQL statements in a stored procedure, trigger, or user defined function (UDF).

  • Stored Procedure shows the SP CPU time accumulated in Db2 for processing SQL statements in stored procedures.

  • Trigger shows the SP CPU time consumed executing triggers SQL statements on a nested task.

  • UDF shows the SP CPU time consumed executing user-defined functions (UDFs) on an IBM specialy engine (for version 10 only).

  • Total shows the Class 1 SP CPU time consumed while executing on an IBM specialty engine in all environments.

  • SQL Elapsed time (sec) can be monitored to note application SQL statement change improvements.

  • In Db2 shows the elapsed time for non-nested activity accumulated in Db2 for the allied agent work request originating in another address space connected to Db2.

  • Stored Procedure shows the elapsed time that the allied agent spent executing Stored Procedure SQL statements.

  • Trigger shows the elapsed time expended executing trigger SQL statements.

  • UDF shows the elapsed time that the allied agent spent executing UDF SQL statements - V10 only.

  • SQL Accelerator usage (sec) Accelerator CPU and elapsed times are reported for all application Db2 workloads running on accelerators.

  • Elapsed Time shows accelerator services elapsed time.

  • CPU Time shows accelerator services CPU utilization.

  • TCP/IP Elapsed shows accelerator services TCP/IP elapsed time.

  • TCP/IP CPU Time shows accelerator services TCP/IP CPU utilization.

Buffer pool statistics

  • BP Name identifies the name of the buffer pool.

    The buffer pool naming conventions are as follows:

  • 4 KB page buffer pools are named BP0 through BP49

  • 8 KB page buffer pools are named BP8K0 through BP8K9

  • 16 KB page buffer pools are named BP16K0 through BP16K9

  • 32 KB page buffer pools are named BP32K through BP32K9.

  • Buffer Pool ID shows the buffer pool ID.

  • Size shows the number opf pages in the buffer pool.

  • Hit Ratio is a measure of how often a page access (a getpage) is satisfied without requiring an I/O operation.

  • Thresholds

  • VP Seq. shows the percentage of the buffer pool that might be occupied by sequentially accessed pages.

  • Parallel Seq. shows the portion of the buffer pool that might be used to support parallel operations. It is expressed as a percentage of VP Seq.

  • Deferred Write shows the percentage of the buffer pool that might be occupied by unavailable pages, including both updated pages and in-use pages.

  • Vert Deferred Write is similar to the deferred write threshold, but it applies to the number of updated pages for a single page set in the buffer pool.

  • Async. Read shows the number of pages that were found in a hiperpool and moved to a virtual buffer pool because of a prefetch that was triggered by the agent.

  • Dynamic Prefetch shows the number of (dynamic) PREFETCH requests. This is triggered by sequential detection and includes prefetches for segmented table spaces. Dynamic prefetch is typically used for a SELECT or UPDATE that is run repeatedly, accessing the index for each access. If sequential prefetch, list prefetch, and dynamic prefetch reads have large values, check to see if the access path can be improved.

  • Immediate Write shows the number of immediate (synchronous) write I/O operations. Although an immediate write is rare, a small nonzero value is acceptable. A large value indicates that the system needs tuning.   

  • Get Pages shows the number of GETPAGE requests. This counter is incremented by successful GETPAGE requests for queries processed in parallel for each thread and for all successful and unsuccessful GETPAGE requests for queries that are not processed in parallel. Reducing the number of GETPAGE requests can improve Db2 performance by reducing the number of synchronous page reads. With fewer GETPAGE requests, the requested page is more likely to be returned from the buffer pool. CPU usage is also reduced. Check the ratio of GETPAGE requests to SQL DML statements, trying to keep this ratio below six.

  • Get Pages failed shows the number of times that a page requested for a query processed in parallel was unavailable because an I/O was in progress or the page was not found in the buffer pool. The agent does not wait, but control returns to the agent. This counter is used only when queries are processed in parallel. If this value is close to zero, most pages are already in the buffer pool, and wait time for synchronous I/O is low. This counter can be high when, for example, there is a cluster index scan and the data is not truly clustered by the index key. In this instance, data pages are not accessed in their true order and the cluster ratio is not valid. Use the Runstats utility to update it. The value of this field is also used to determine how many sequential prefetches of one page were scheduled.

  • List Prefetch shows the number of List Prefetch requests. List Prefetch allows Db2 to access data pages efficiently even if the needed data pages are not contiguous. It can be used with single index access and always with multiple index access. List prefetch is always used to access data from the inner table during a hybrid join. Data pages are read in quantities equal to the sequential prefetch quantity, which depends on the buffer pool size and is usually 32 pages. During bind time , Db2 does not use list prefetch if the estimated number of RIDs to be processed would take more than 50% of the RID pool. During execution time, list prefetch processing terminates if Db2 detects that more than 25% of the rows in the table need to be accessed. If list prefetch is terminated, it is indicated in IFCID 125.

  • Page Updates shows the number of times a buffer update occurs. This is incremented every time a page is updated and is ready to be written to DASD. If the same page is updated twice, for example, the number is incremented by 2. This number is kept for all pages, including data pages and work file pages. A nonzero value indicates any of the following activities:

  • SQL INSERT, UPDATE, or DELETE

  • Merge scan join

  • Internal sort activity on the work files. Check the access path to determine if sort activity can be either minimized or avoided.

  • Seq Prefetch shows the number of SEQUENTIAL PREFETCH requests. This is incremented for each PREFETCH request. Each request can result in an I/O read. If it does, up to 32 pages can be read for SQL and up to 64 pages for utilities. For SQL, depending on the buffer pool size, a request does not result in an I/O if all the requested pages are already in the buffer pool. Db2 can use sequential prefetch if the data is accessed in sequential order even though sequential prefetch was not requested at bind time. This is known as sequential detection and is not included in the sequential prefetch count. Sequential detection is included in the Dyn. Prefetch requests field.

    Table space scans and non-matching index scans generally use sequential prefetch.

  • Sync. Read shows the number of synchronous read I/O operations. Db2 increments this counter for each media manager synchronous physical read. Asynchronous I/O requests are not counted.

RDS SQL Activity statics - Miscellaneous and exceptions

  • DRA (direct row access) attempts OK shows the number of times that direct row access was successful.

  • DRA attempts reverted to INDEX shows the number of times an index was used to find a record.

  • DRA attempts reverted to table space scan shows the number of times that an attempt to use direct row access reverted to using a table-space scan because Db2 was unable to use a matching index scan.

    Ideally, this value should be 0. Table-space scans can happen, for example, when a REORG is performed between the read of the ROWID column and the use of the host variable in the WHERE clause of the SQL statement. This causes the RID value in the host variable to be incorrect. Db2 first tries a matching-index scan before using a table-space scan. To avoid table space scans, you can force the access path of an unsuccessful direct row access to use a matching index scan on the primary-index key by adding PKCOL to the WHERE clause in the SQL statement. .... WHERE ROWIDCOL=:HVROWID AND PKCOL=:HVPK.

  • Implicit Prepare (KEEPD=Y not in cache) shows the count of implicit prepares which occur 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. 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.

  • Max degree of PG execution shows the maximum degree of parallel query processing executed among all parallel groups to indicate the extent to which queries were processed in parallel.

  • No Prepare (KEEPD=Y not in cache) shows 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. 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.

  •  Parallel Groups executed shows the total number of parallel groups executed.

  • Parallel Groups degree executed as planned shows the total number of parallel groups that executed in the planned parallel degree. This field is incremented by one for each parallel group that executed in the planned degree of parallelism (as determined by Db2).

  • Parallel Groups degree reduced (BUF-shortage/contention) shows 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.

    If this field is not 0, increase the size of the current buffer pool using the ALTER BUFFERPOOL statement or use the ALTER TABLESPACE statement to assign table spaces accessed by this query to a different buffer pool.

  • Parallel Groups fell to SEQ (lack of EAS sort) shows the total number of parallel groups that fell back to sequential mode due to a lack of ESA sort support.

  • Parallel Groups fell to SEQ (BUF-shortage/contention) shows the total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.

  • Parallel Groups fell to SEQ w/Cursor DEL or UPD shows the total number of parallel groups that fell back to sequential mode due to a cursor that can be used by UPDATE or DELETE.

  • Parallel Groups intended across data sharing GP shows the total number of parallel groups that Db2 intended to run across the data sharing group. This number is only incremented at the parallelism coordinator at run time.

  • Parallel Groups member bypassed (buffer short) shows 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. 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 Groups on single (coordinator SubS = NO) shows the total number of parallel groups executed on a single Db2 subsystem due to the COORDINATOR subsystem value being set to NO. 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 Groups on single due to isolation level shows the total number of parallel groups executed on a single Db2 subsystem due to repeatable-read or read-stability isolation.

  • Parallel Groups reformulated (BPOOL shortage) shows the total number of parallel groups in which Db2 reformulated the parallel portion of the access path because there were insufficient buffer-pool resources. This counter is incremented only at the parallelism coordinator at run time.

  • Parallel Groups reformulated (configuration changed) shows the total number of parallel groups where Db2 reformulated the parallel portion of the access path because of a change in the number of active members, or because of a change of processor models on which they run, from bind time to run time. This counter is incremented only on the parallelism coordinator at run time.

  • Prepares STMT discard (MAXKEEPD reached) shows 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.

  • Prepares STMT drop (DROP/ALTER/REVOKE) shows 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.

  • Prepares not satisfied from cache shows the number of times that Db2 searched the prepared statement cache but could not find a suitable prepared statement.

  • Prepares satisfied from cache shows the number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.

  • RID list Processing not used (exceeded limits) shows 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). 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. For index access with list prefetch (single index), this field can only be incremented during RID list retrieval. For multiple index access, this field can be incremented during RID list retrieval, ANDing, and ORing. This counter reflects the number of times internal limits or threshold values were exceeded for the RID lists obtained directly from an index as well as for RID lists derived during the ANDing and ORing process.
    Before you increase the RID list storage size, investigate the cause of the failure using the statistics record or the performance trace. You can specify the desired size for the RID list (within the range of 16 KB to 1000 MB) on the Db2 installation panel DSNTIPC.

  • RID list Processing not used (lack of storage) shows the number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID list process involving one index (single index access with list prefetch) or multiple indexes (multiple index access). This field can be incremented during retrieval, sorting, ANDing, and ORing of RID lists for index access with list prefetch (single index). For single index access, this field can only be incremented once per access. For multiple index access, it can be incremented for every index involved in the ANDing and ORing of RID lists.  

  • RID list processing used shows the number of times RID list (also called RID pool) processing is used. During RID (RECORD ID) 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. This field is incremented once for a given table access when RID list processing is used for index access with list prefetch, for multiple index access, or for both. For multiple index access, if a final RID list is obtained through ANDing and ORing of RID lists, the counter is incremented once, even if not all indexes were used by the RIDs in the multiple index access.

    A nonzero value in this field indicates that Db2 has used list prefetch. If this is the case, check the access path selection.

  • Rows Deleted shows the number of database rows deleted.

  • Rows Fetched shows the number of database rows fetched.

  • Rows Inserted shows the number of database rows inserted.

  • Rows Updated shows the number of database rows updated.

  • Times re-optimization occurred shows the total number of times reoptimization occurred because the value of the host variable or parameter marker changed.

RDS SQL Activity statistics - Statement Counts

  • Statement Counts shows the number of times  a SQL statement executed. The counts are shown by statement type. They include all execution of the type across all packages executing on the application threads.