Accounting highlights report

The accounting highlights report shows general accounting information.

Figure 1. Accounting highlights report 
HIGHLIGHTS
--------------------------
#OCCURRENCES    :       13
#ALLIEDS        :       13
#ALLIEDS DISTRIB:        0
#DBATS          :        0
#DBATS DISTRIB. :        0
#NO PROGRAM DATA:       13
#NORMAL TERMINAT:       13
#ROLLUP TRAN    :       13
#DDFRRSAF ROLLUP:        0
#ABNORMAL TERMIN:        0
#CP/X PARALLEL. :        0
#UTIL PARALLEL. :        0    
#IO PARALLELISM :        0
#PCA RUP COUNT  :        0    
#RUP AUTONOM. PR:        0    
#AUTONOMOUS PR  :        0    
#INCREMENT. BIND:        0
#INCBND INV STMT:        5
#INCBND APREUSE :        1
#COMMITS        :      215
#ROLLBACKS      :        0
#SVPT REQUESTS  :        0
#SVPT RELEASE   :        0
#SVPT ROLLBACK  :        0
MAX SQL CASC LVL:        0
UPDATE/COMMIT   :     0.00
SYNCH I/O AVG.  : 0.002932 
MAX WFILE BLKS  :        0   
#ZHL READ I/O   :   325.00
#OCCURRENCES

The number of logical accounting records. A logical accounting record can contain more than one physical record.

This is the case, for example, in query CP and sysplex query parallelism, where several accounting records (IFCID 003 and, optionally, 239) are generated, namely one for the entire thread and one for each parallel task within the thread.

In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in a record.

This number is used for calculating averages (as a divisor) for class 1, 2, 3, and 5 times and events.

Field Name: ASOCCURS

#ALLIEDS

The number of allied threads. In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in this record for the corresponding end user.

Field Name: ASALLIED

#ALLIEDS DISTRIB

The number of allied-distributed threads. In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in this record for the corresponding end user.

Field Name: ASALLDST

#DBATS

The number of database access threads. In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in this record for the corresponding end user.

Field Name: ASDBATS

#DBATS DISTRIB.

The number of DBAT-distributed threads. In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in this record for the corresponding end user.

Field Name: ASDBATD

#NO PROGRAM DATA

The number of Accounting records without package data. In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it cannot be determined. In this case it is 0.

Field Name: ADNOPACK

#NORMAL TERMINAT

The number of normal terminations. Here is a list of reasons for termination and the corresponding field names:

Reason
Field Name
New user
ASNTNEWU
Deallocation
ASNTDEAL
Application program end
ASNTAPEN
Resignon
ASNTRESI
DBAT inactive
ASNTDBAT
RRS commit
ASRRSCOM
Note: Termination reasons in case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads are not counted when the accounting record is a roll-up accounting record.

Field Name: ASNORMTM

This is an exception field.

#ABNORMAL TERMIN

The number of abnormal terminations. Here is a list of reasons for termination and the corresponding field names:

Reason
Field Name
Application program abend
ASATAPAB
End of memory
ASATENDM
Resolve indoubt
ASATRIND
Cancel force
ASATCANF
Rollup termination
ASROLL

Field Name: ASABNOTM

This is an exception field.

#DDFRRSAF ROLLUP

The number of DDF/RRSAF rollup records with accumulated counter data for an end user.

A rollup record is written when the number of occurrences of the end user on the thread reaches the ZPARM value for ACCUMACC and due to one of the following reasons:
  • The number of times the threshold was reached for the number of end-user occurrences when data was accumulated by end user for DDF or RRSAF.
  • The number of times the Db2 storage threshold for Accounting blocks was reached for data accumulated by end user for DDF or RRSAF.
  • The number of times the threshold for the staleness was exceeded when data was accumulated by end user for DDF or RRSAF.
Note: End user is defined as the concatenation of the following values:
  • End-user user ID (QWHEUID, 16 bytes)
  • End-user transaction name (QWHCEUTX, 32 bytes)
  • End-user workstation name (QWHCEUWN, 18 bytes)

Field Name: ASCUTS

#ROLLUP TRAN

The number of rollup records.

It is calculated as:

SUM(QWACPCNT)

where

QWACRINV IN ('END USER ', 'BLOCK STOR', 'STALENESS ')

Example: If
  • ACCUMACC=10
  • all records have QWACRINV='END USER'
and
  • there are 20 accounting records
then

#ROLLUP TRAN will be 200 (10 * 20)

If one of the records has

QWACRINV='BLOCK STOR',

and

QWACPCNT=7,

then

#ROLLUP TRAN will be 197 (10*19 + 1*7)

Field Name: ASROLL

#CP/X PARALLEL.

The number of originating accounting records where query CP and sysplex query parallelism was used for at least one SQL statement. I/O parallelism might have been used by other SQL statements.

Field Name: ASPARCPU

#UTIL PARALLEL.

The number of Accounting records that indicated that UTILITY parallelism was used by at least one SQL statement and query CP and sysplex query parallelism was not used by any SQL statement.

Field Name: ASPARUT

#IO PARALLELISM

The number of accounting records that indicated that I/O parallelism was used by at least one SQL statement and query CP and sysplex query parallelism was not used by any SQL statement.

Field Name: ASPARIO

#PCA RUP COUNT

The number of parallel child agents rolled into this record. The value depends on the record type:

  1. For all non-rollup records, this value is 0.
  2. For a parallel query rollup record, this value is the number of parallel child agents rolled into this record.
  3. For a DDF/RRSAF rollup record, this value is the number of parallel query child agents rolled into this record. These agents are NOT counted in QWACPCNT.
  4. For an autonomous procedure rollup record, this value is 0.

Field Name: APTCOUNT

#RUP AUTONOM. PR

The number of accounting records that indicated a roll-up autonomous thread.

Field Name: ADRUPATX

#AUTONOMOUS PR

The number of autonomous procedures that were executed:

  1. For non-rollup records, this value is the number of autonomous procedures that were executed.
  2. For a parallel query rollup record, this value is 0.
  3. For a DDF or RRSAF rollup record, this value is the number of autonomous procedures that were executed. These procedures are NOT counted in QWACPCNT.
  4. For autonomous procedures rollup records, this value is 0.

Field Name: AATCOUNT

#INCREMENT. BIND
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.

#INCBND INV STMT

The number of incremental binds due to invalidated statements.

Field Name: QXIBINVS

#INCBND APREUSE

The number of incremental bind statements with APREUSE that Db2 retried.

Field Name: QXIBAPRE

#COMMITS

The number of successful two-phase (units of recovery) or single-phase (syncs) commit requests. It indicates the number of units of recovery that are completed successfully, and for which the associated commit duration locks were released. It represents the total number of commit requests processed by the Db2 subsystem, whether the request was an explicit or implicit external request from an IMS or a CICS connection, or an implicit internal request within Db2 when Db2 was the commit coordinator or conducted read-only commit processing as a commit participant on phase-1 calls from an IMS or CICS connection.

For parallel queries, only the commits from the initiating (parent) thread are recorded by this counter.

Field Name: QWACCOMM

This is an exception field.

#ROLLBACKS

The number of rollback requests. This is the number of units that were backed out, including rollbacks from attaches.

Special Considerations: This field contains the number of:
  • Application program abends
  • Application rollback requests
  • Application deadlocks on database records
  • Applications canceled by operator
  • Thread abends due to resource shortage

Field Name: QWACABRT

This is an exception field.

#SVPT REQUESTS

The number of named SAVEPOINTs set within a transaction.

Field Name: QWACSVPT

#SVPT RELEASE

The number of RELEASE SAVEPOINT statements executed.

Background and Tuning Information

Release savepoints as soon as possible. Outstanding savepoints block SQL operations that resolve remote locations. Db2 always releases outstanding savepoints when a transaction ends.

Field Name: QWACRLSV

This is an exception field.

#SVPT ROLLBACK

The number of ROLLBACK TO SAVEPOINT statements executed.

Field Name: QWACRBSV

MAX SQL CASC LVL

The maximum level of indirect SQL cascading. This includes cascading because of triggers, UDFs, or stored procedures.

Field Name: QXCASCDP

This is an exception field.

UPDATE/COMMIT

The sum of SQL INSERT, SQL UPDATE, and SQL DELETE statements executed.

Field Name: ASIUD

This is an exception field.

SYNCH I/O AVG.

The synchronous I/O suspension time per event.

Field Name: AAIOTMCN

This is an exception field.

MAX WFILE BLKS

The maximum number of work-file blocks that are used by this agent at any given point in time (traditional work-file blocks, declared global temporary tables (DGTT) and DGTT indexes) (Db2 field QWAC_WORKFILE_MAX).

Field Name: AWWFMAX

#ZHL READ I/O
The number of successful read I/Os done with zHyperLink.

Field Name: #ROLLUP TRANQBACSYI