Headers Used in SQL Activity
A header is printed at the top of every SQL Activity trace, report, and index page.
The following topics provide additional information:
The report headers contain the standard OMEGAMON for Db2®
PE header information
and the following additional data:
- SORTED BY
- The event by which the report or trace is sorted, which can be the default or as specified in the SORTBY option.
- WITH detail WORKLOAD
- The workload details included in the report or trace as specified in the WORKLOAD option.
- OMEGAMON for Db2 PE identifiers
- The identifiers define the order of the SQL Activity data printed. If you specify no OMEGAMON for Db2 PE identifiers with ORDER, the default order of PRIMAUTH-PLANNAME is used.
- TRACE #
- Each trace occurrence and thread within the TRACE subcommand is
numbered sequentially in the format x.yyyy, where:
- x can be 1 through 5, representing the five TRACE subcommands
- yyyyy can be 1 through 99 999, representing each thread being traced.
- Db2 LUWID
- The identifier of the logical unit of work. The following parts
of this identifier are printed:
- The network ID
- The name of the logical unit, which is the name by which VTAM® recognizes the Db2 subsystem
- The instance number
- ACE ADDRESS
- The agent control element absolute address in hexadecimal.
- START TIME
- The timestamp showing when the startup of the thread ended or, if the REQUESTED FROM time is after the thread begin, the REQUESTED FROM timestamp.
- START ELAPSED
- The thread start elapsed time, if calculable.
- START REASON
- The event that started the thread:
- CREATE THREAD
- CREATE DBAT
- NEW USER
- RESIGNON
- IN PROGRESS, no thread start IFCID present.
- STOP TIME
- The timestamp showing when the thread stopped or, if the REQUESTED TO time is after the thread end, the REQUESTED TO timestamp.
- STOP ELAPSED
- The thread stop elapsed time, if calculable.
- STOP REASON
- The event that stopped the thread:
- TERMINATE THREAD
- DEALLOCATE DBAT
- NEW USER
- RESIGNON
- ACCOUNTING FOUND-the thread terminated with the accounting record
- LOCATION CHANGED-the thread was terminated due to a location change
- END OF FILE-the thread was terminated because there were no records left to process.
- START AET
- The average thread start elapsed time.
- STOP AET
- The average thread stop elapsed time.
- AUTOBIND AET
- The average autobind elapsed time, if present.
- ARCHIVE LOG AET
- The average archive log (quiesce) elapsed time, if present.
Explanation of short and long fields
To improve the evaluation of SQL activities, Db2 supports both, short and long fields. If the field value exceeds the available field length (such as long values in the header information), the string is truncated, depending on the space available. Truncated values are then listed at the end of each logical report unit, together with their full values.
A "greater than" sign (>) indicates whether
a value is truncated. When a value is truncated, the "greater than"
sign (>) is printed instead of a colon (:) following the label
name. The full value starts with a "greater than" sign followed by
the label. For example:
Tname > This value is truncated
...
...
>Tname : This value was truncated - now you see its full length
...If truncated values are listed, the "greater than" sign (>) is shown at the end of each value, because there is no colon (:) as a delimiter between the label and the value. In lists the label is used as a column heading.
Note: The
mapping between truncated and full values remains the same for multiple
reports from the same input data. This mapping is not supported for
multiple reports from different input data. The printing of abbreviations
and full text can cause inaccurate results in Batch SQL Activity output.
Here are examples of SQL Activity layouts with truncated
values:
- SQL Activity - Minibind:
ACCESS_CREATOR ACCESS_NAME MATCHCOLS INDEXONLY PREFETCH_INDEX OPERATION MIXOPSEQ TDK_LONG> IX_OMPE_FIRST_LONG> 0 YES SEQUENTIAL SCAN 1 ... >ACCESS_CREATOR : TDK_LONG_NAMED_COLLECTION_FOR_LONG_NAMED_OBJECTS >ACCESS_NAME : IX_OMPE_FIRST_LONG_NAMED_TABLE_FOR_UNCOMMITED_READ - SQL Activity trace, where WSNAME and TRANSACT, and the OMEGAMON for Db2
PE identifiers,
PRIMAUTH and ORIGAUTH, are truncated.
LOCATION: OMPDBZ4 IBM OMEGAMON FOR DB2 PERFORMANCE EXPERT (V5R5M0) PAGE: 1-1 GROUP: DBZ4 SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED MEMBER: SZ42 TO: NOT SPECIFIED SUBSYSTEM: SZ42 ACTUAL FROM: 12/17/15 08:54:37.74 DB2 VERSION: V10 SUMMARIZED BY OCCURRENCE, WITH ALL WORKLOAD PRIMAUTH> cccccccc CONNECT : BATCH CORRNAME: YCLO6287 CONNTYPE: TSO ORIGAUTH> cccccccc PLANNAME: DSNTIA10 CORRNMBR: 'BLANK' THRDTYPE: ALLIED ENDUSER > cccccccc WSNAME > dddddddd TRANSACT> eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee >PRIMAUTH: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc ccccccc >ORIGAUTH: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc ccccccc >ENDUSER : ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc ccccccc >WSNAME : ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd >TRANSACT: eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee