Accounting highlights trace

The accounting highlights trace shows general accounting information.

Figure 1. Accounting highlights trace

HIGHLIGHTS
--------------------------
THREAD TYPE   : ALLIED    
TERM.CONDITION: NORMAL    
INVOKE REASON : PROGRM END
PARALLELISM   : CP        
PCA RUP COUNT :          0
RUP AUTONOM.PR:          0
AUTONOMOUS PR :          0
QUANTITY      :        304
COMMITS       :         23
ROLLBACK      :          0
SVPT REQUESTS :          0
SVPT RELEASE  :          0
SVPT ROLLBACK :          0
INCREM.BINDS  :          0
UPDATE/COMMIT :       5.91
SYNCH I/O AVG.:   0.000375
PROGRAMS      :          1
MAX CASCADE   :          0
MAX WFILE BLKS:      30720
ZHL READ I/O  :        325

THREAD TYPE

The type of thread. This field can contain one of the following values:

ALLIED: The thread is not involved in any distributed activity.
ALLDDIST: The thread is initiated by a DB2 attach and requests data from one or more server locations.
DBAT: The thread is initiated, created, and performing work on behalf of a remote (requester) location. The value DBAT also includes DBAT DISTRIBUTED threads that are initiated by a requester location and executed by the server location that in turn requests data from another server location.

Background and Tuning Information

If the thread is involved in distributed activity, some monitored values can produce different results. For example, the class 1 elapsed time for a distributed thread is higher because the network time is also included.

Field Name: ADTHRTYP

TERM.CONDITION

Termination condition. Signon in a CICS environment is controlled by an additional RCT option, TXIDSO. If YES, resignon occurs if the only identifier changed is the transaction ID. If NO, resignon does not occur.

Field Name: ADTERMCO

INVOKE REASON

The status of the thread. The values are:

Status: Description
CAN FORCE: CANCEL FORCE. The Stop Force command terminated, abnormal program termination.
DBAT INACT: DDF thread is becoming inactive.
DEALLOC: Deallocation, normal program termination.
TYP2 INACT: DDF TYPE 2 thread is becoming inactive.
MEMORY END: End of memory, abnormal termination.
MON READS: IFI reads request for IFCID 147.
NEW USER: New user, the authorization Id changed.
PROG ABEND: End of task. Application program abended.
PROGRM END: End of task. Application program terminated normally.
RESIGNON: Same user resign-on with on with same authorization ID.
RES INDBT: Resolve indoubt, abnormal program termination.
RRS COMMIT: Termination due to a commit of an application attached to the Recoverable Resource Manager Services Facility (RRSAF).
STALENESS: Accumulating data by end user for DDF or RRSAF and accumulated data has exceeded the staleness threshold.
BLOCK STOR: Accumulating data by end user for DDF or RRSAF and internal DB2 storage threshold has been reached.
TASK END: End of task - application program terminated normally.
END USER: Accumulating data by end user for DDF or RRSAF and threshold reached for number end user occurrences.

Field Name: ADINVRSN

PARALLELISM

An indicator to show which type of parallel processing is used when SQL statements are executed:

SQL statement: Parallel processing
I/O: For threads exploiting query I/O parallelism but no query CP or sysplex query parallelism
CP: For threads exploiting query CP parallelism
SYSPLEX: For threads exploiting sysplex query parallelism
UTILITY: For utility threads with subtasks
NO: For threads without subtasks

Field Name: ADPARLEV

PCA RUP COUNT

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

For all non-rollup records, this value is 0.
For a parallel query rollup record, this value is the number of parallel child agents rolled into this record.
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.
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:

For non-rollup records, this value is the number of autonomous procedures that were executed.
For a parallel query rollup record, this value is 0.
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.
For autonomous procedures rollup records, this value is 0.

Field Name: AATCOUNT

QUANTITY

The number of parallel child agents, or Accounting intervals rolled up, or autonomous procedures rolled up. The value depends on the record type:

For a non-rollup parent record, this value is the number of parallel child agents that were created.
For a non-rollup child agent record, this value is 0.
For a parallel query rollup record, this value is the number of parallel child agents rolled into the record.
For a DDF/RRSAF rollup record, this value is the number of Accounting intervals that were rolled into the record for the corresponding end user.
For an autonomous procedure rollup record, this value is the number of autonomous procedures rolled into the record.

Field Name: QWACPCNT

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.

ROLLBACK

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

INCREM.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.

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.

PROGRAMS

The number of packages or DBRMs for which accounting data was collected.

Field Name: QWACPKGN

This is an exception field.

MAX CASCADE

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.

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: QBACSYI