Highlights

This topic shows detailed information about Statistics - Highlights.

The sample shows the Statistics Highlights block for the long report. The description also shows additional fields printed with the Statistics short report.

Statistics - Highlights

The field labels shown in the following sample layout of Statistics - Highlights are described in the following section.


---- HIGHLIGHTS ----------------------------------------------------------------------------------------------------
INTERVAL START  : 07/26/10 19:32:45.57    SAMPLING START: 07/26/10 19:32:45.57    TOTAL THREADS      :     0.00
INTERVAL END    : 07/26/10 19:35:47.34    SAMPLING END  : 07/26/10 19:35:47.34    TOTAL COMMITS      :    33.00
INTERVAL ELAPSED:          3:01.768843    OUTAGE ELAPSED:             0.000000    DATA SHARING MEMBER:      N/A

INTERVAL START

The start time of the period represented by this report or trace entry.

For a trace, it is the timestamp of the Db2 Statistics records pair which marks the beginning of the delta record represented by the trace entry.

For the group page of group-scope reports it is the beginning of the earliest interval across reported members.

For SAVE data, it is the timestamp of the first Db2 Statistics report pair used to derive a row in the statistics SAVE and FILE tables.

For FILE, it is the timestamp of the Db2 Statistics records pair which marks the beginning of the delta record represented by a row in the statistics SAVE and FILE tables.

Field Name: SDBEGREC

INTERVAL END

The end time of the period represented by this report or trace entry.

For a trace, it is the timestamp of the Db2 statistics records pair which marks the end of the delta record represented by the trace entry.

For the group page of group-scope reports it is the ending of the latest interval across reported members.

For SAVE data, it is the timestamp of the last Db2 statistics report pair used to derive a row in the statistics SAVE and FILE tables.

For FILE, it is the timestamp of the Db2 statistics records pair which marks the end of the delta record represented by a row in the statistics SAVE and FILE tables.

Field Name: SDENDREC

INTERVAL ELAPSED

The elapsed time of the period represented by this report or trace entry.

For a trace, it is the time elapsed between two consecutive Db2 statistics records pairs which mark the beginning and the end of the delta record represented by the trace entry. For a report, it is the elapsed time for the period within the interval record for which the Db2 statistics data is available.

For the group page of group-scope reports it is the average elapsed time of all the reported members.

Field Name: SDELTIME

SAMPLING START

The timestamp of the first Db2 statistics records pair used to derive a report entry (an interval record). For example, when INTERVAL(0) is specified, the sampling start coincides with the interval record start time in member-scope reports.

Field Name: SDSAMPST

SAMPLING END

The timestamp of the last Db2 statistics records pair used to derive a report entry (an interval record). For example, when INTERVAL(0) is specified, the sampling start coincides with the interval record end time in member-scope reports.

Field Name: SDSAMPEN

OUTAGE ELAPSED

The time for which OMEGAMON for Db2® PE detected discontinuity in the available Db2 statistics data. The most common reason for this is a stop or start of the reported Db2 system within the reported interval. For the group page of group-scope reports it is the average outage time of all reported members.

Field Name: SDOUTEL

TOTAL THREADS

The number of successful create thread requests. It does not include DBATs.

A thread is required before an application can use SQL. When established, a thread can have one or more secondary authorization IDs.

A thread is needed to perform any Db2 activity. For example, a thread is needed to run a Db2 utility to perform an IFI request such as READS, or to process a Db2 command such as -DISPLAY THREAD. However, a thread is not created if the command failed because of a syntax error.

Background and Tuning Information

Thread reuse can help improve performance.

The term thread reuse only applies to IMS and CICS attachments. In the case of the TSO attachment facility and the call attachment facility (CAF), threads cannot be reused, because the threads are allocated to the user address space.

Thread reuse should be considered in the following cases:

If transaction volume is high:
High volume transactions should achieve a high percentage of thread reuse. If threads are reused on low volume transactions, the number of threads needed increases because these threads are not automatically terminated by IMS when not being used. This may result in too many idle threads for the level of the Db2 workload. Under CICS, protected threads are terminated after about 45 seconds if no transaction eligible to reuse the thread has been received.
If thread creation cost is significant:
As a rule of thumb, more than 5% of the total CPU cost of transaction processing is considered significant.

The ACQUIRE and RELEASE parameters of BIND should be specified to minimize the thread creation cost, while providing the needed concurrency:

If most of the application plan's SQL statements are executed, then ACQUIRE(ALLOCATE) is cheaper than ACQUIRE(USE).
If only a small number of the SQL statements are executed, ACQUIRE(USE) becomes cheaper and improves concurrency, because the required resources are only acquired (locked) when the plan actually references (uses) them. An example would be a generalized plan used by many different transactions. It would contain multiple logic paths referencing different tables.
Note that, when packages are involved, ACQUIRE(USE) is always implicitly used.
Concurrency in thread reuse is based on page locking provided by the IS and IX intent locks, whose duration is governed by ACQUIRE and RELEASE of BIND.
RELEASE(DEALLOCATE) is strongly recommended for thread-reuse transactions to reduce transaction CPU time.

When thread reuse is implemented, monitor the EDM pool. It should be sufficient in size to accommodate expanding plans where the next transaction requires additional plan sections over those that are already part of the plan.

Field Name: Q3STCTHD

This is an exception field.

TOTAL COMMITS

The total number of commits during the interval covered by the report or trace. This includes commit, read-only commit, sync, and rollback events. DBATs executed on this location are not included in reports generated with the SCOUPE(GROUP) subcommand option.

Field Name: SDCOMMIT

This is an exception field.

DATA SHARING MEMBER

In group-scope reports, this field shows the name of the member for which statistics is presented, and, on the group total page, the number of Db2 subsystems in the reported data sharing group. In member-scope reports, this field shows N/A.

Field Name: QWHAMEMN

This is an exception field.

INCREMENTAL BINDS

The number of incremental binds (excluding prepare). It is incremented by:

SQL statements with BIND VALIDATE(RUN) that fail at bind time and are bound again at execution time
Static DDL statements (such as CREATE TABLE, DROP TABLE, LOCK TABLE) that use Db2 private protocol

Background and Tuning Information

If a plan is bound with VALIDATE(RUN), Db2 performs validity checks at bind time and rechecks any failures at run time. This can result in catalog contention and degraded application performance, depending on the number of statements flagged and how many times they are executed. Avoid VALIDATE(RUN) if possible. Ensure that all objects are created and all privileges are granted before bind, and select the VALIDATE(BIND) option.

Field Name: QXINCRB

This is an exception field.

DBAT QUEUED

The number of times a DBAT or connection was queued because it reached the ZPARM maximum for active remote threads (MAXDBAT).

Field Name: QDSTQDBT

This is an exception field.

AUTH SUCC.W/OUT CATALOG

The number of successful authorization checks that do not use the Db2 catalog (including plan cache checks and public checks).

Background and Tuning Information

For transaction level security, ENABLE and DISABLE on BIND PACKAGE should be used to ensure adequate security. Granting execute authority on the plan to public should be adequate.

Field Name: QTAUCCH

DB2 Command

The total number of Db2 commands that were issued.

Field Name: SDSTTOTL

BUFF.UPDT/PAGES WRITTEN

The number of buffer updates per page written from the buffer pool to DASD.

The ratio of BUFFER UPDATES (QBSTSWS) to PAGES WRITTEN (QBSTPWS) suggests a high level of efficiency as the ratio increases, because more updates are being externalized per physical write. For example, if there are 10 updates on the same page before it is externalized, then the ratio is 10:1 or 10. If all 10 updates are on 10 distinct pages, then the ratio is 10:10 or 1.

Background and Tuning Information

Buffer updates per pages written depends strongly on the type of application. For example, a batch program that processes a table in skip sequential mode with a high row update frequency in a dedicated environment can achieve very good update efficiency. In contrast, update efficiency tends to be lower for transaction processing applications, because transaction processing tends to be random.

The following factors can influence the number of updates per page:

Number of rows per page: A small PCTFREE value will gather more rows on the same page. However, at the same time this can have impact on concurrency.
Buffer pool size and deferred write thresholds: Increase DWQT and VDWQT or the size of the buffer pool. This would tell Db2 to let page updates accumulate in the buffer pool. This means, the probability that more updates per page get captured increases. This effect is less significant if the buffer pool is concurrently used by multiple transactions, it depends on the type of transaction.

Field Name: SBRBUPW

TOTAL API

The total number of calls made to IFI.

Field Name: SDIFITOT

PAGES WRITTEN/WRITE I/O

The number of pages written from the buffer pool to DASD per synchronous or asynchronous write I/O. This count does not include preformatting I/O, such as I/O needed to prepare a data set for use.

Background and Tuning Information

The following factors impact the ratio of pages written per write I/O:

Checkpoint frequency: At checkpoint time, I/Os are scheduled to write all updated pages on the deferred write queue to DASD. If this occurs too frequently, the deferred write queue does not grow large enough to achieve a high ratio of pages written per write I/O.
The checkpoint frequency depends on the number of logs written between two consecutive checkpoints. This number is set at installation time; see the field CHECKPOINT FREQ of installation panel DSNTIPN.
Frequency of active log switch: Db2 takes a system checkpoint each time the active log is switched. High frequency of active log switches causes the problem described under checkpoint frequency.
Buffer pool size and deferred write thresholds: The deferred write thresholds (VDWQT and DWQT) are a function of buffer pool size. If the buffer pool size is decreased, these thresholds are reached more frequently, causing I/Os to be scheduled more often to write some of the pages on the deferred write queue to DASD. This prevents the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O.
Number of data sets, and the spread of updated pages across them: The efficiency of write I/O also depends on the number of data sets associated with the buffer pool and spread of updated pages across them. Because of the nature of batch processing, the ratio of pages written to write I/Os can be expected to be higher than that expected for transaction type workloads.

To determine update efficiency check also the ratio Buffer Updates / Pages Written (SBRBUPW).

Field Name: SBRPWWIO

MEMBER

Field Name: QWHAMEMN