Introduction to the SQL Activity Report Set

Edit online

The SQL Activity report set consists of reports and traces and provides information on the SQL activity taking place during the processing of a Db2® application. The reports show the processing of an SQL statement and all the related Db2 activity, known as workload, related to that statement.

Also included in these reports are trigger and nesting level information. When the appropriate IFCID 003 is present, Accounting Trace is also reported.

In most situations, an SQL Activity trace gives details on either a Db2 thread or part of a reused thread between two signons. In CP query and sysplex query parallelism, an originating thread and multiple parallel threads are created to execute an SQL statement. Data from parallel threads can also be included in the originating thread if you specify an INPUTDD containing the relevant data in your JCL. The term thread is used to include the originating and the parallel threads. The trace is a collection of threads presented in logical unit of work ID (LUWID) sequence with an index to help you find a particular thread.

An SQL Activity report is an aggregation of threads ordered by the combination of OMEGAMON AI for Db2 identifiers you specify. If you specify no OMEGAMON AI for Db2 identifiers with ORDER, the default order of PRIMAUTH and PLANNAME is used. The report is a summary of all the work belonging to, and ordered by, those identifiers.

The following information is collected for each thread provided that the appropriate IFCIDs are available:

Thread identification (OMEGAMON AI for Db2 identifiers, Db2 logical unit of work ID (LUWID), CICS® logical unit of work ID (LUWID), ACE, thread start and stop time, thread type, and location)
Programs (DBRMs and packages), stored procedures, cursors used, UDF, and triggers.
SQL statements executed within the thread with their workload detail
Events and time spent in DDF processing
Time spent in signon processing
Time spent creating and terminating threads
Time spent in autobind processing
Accounting information

You can control the level of summarization, the sorting of events, and the workload detail within a unit of reporting.

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 AI for Db2 identifiers, PRIMAUTH and ORIGAUTH, are truncated.

LOCATION: OMPDBZ4                          IBM Z OMEGAMON AI FOR DB2 (V6R1M0)             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: V13
                                            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