ACCOUNTING command with REPORT subcommand

This section describes the ACCOUNTING command with the REPORT subcommand.

Usage

Use the REPORT subcommand to generate reports from reduced records.

Usage notes

Up to 35 REPORT subcommands can be specified within each ACCOUNTING command.

Syntax of the REPORT subcommand


>>-REPORT------------------------------------------------------->

>--+--------------------------------------------------------------+-><
   '-+-| FROM/TO block |----------------------------------------+-'   
     |           .-MEMBER-.                                     |     
     +-SCOPE--(--+-GROUP--+--)----------------------------------+     
     |            .-SHORT-.                                     |     
     +-LAYOUT--(--+-LONG--+--)----------------------------------+     
     |            '-name--'                                     |     
     | .-NOEXCEPTION-.                                          |     
     +-+-EXCEPTION---+------------------------------------------+     
     |         .-10-----.                       .-INAPPLET-.    |     
     +-TOP--(--+-number-+--+------+--+-------+--+-keyword--+--)-+     
     |                     '-ONLY-'  '-TOTAL-'                  |     
     |            .-ACRPTDD-.                                   |     
     +-DDNAME--(--+-ddname--+--)--------------------------------+     
     +-| ORDER block |------------------------------------------+     
     '-| INCLUDE/EXCLUDE block |--------------------------------'

Subcommand options

The syntax diagram shows the options that are available with this subcommand. See OMEGAMON XE for DB2 PE subcommand options for comprehensive descriptions of these options. The following list gives additional or specific descriptions of selected options, where appropriate.

FROM/TO

Limits the range of records included in the reporting process by date and time.

For details, see FROM/TO subcommand options.

You need to specify a REDUCE INTERVAL() for the FROM/TO subcommand under ACCOUNTING REPORT. Otherwise, you will see the following message:

NO DATA TO REPORT - NO ADEQUATE INPUT DATA OR TOO RESTRICTIVE FILTERS.

The reason for this message is that all accounting input data will be reduced by default to only one reporting interval. If you request several ACCOUNTING REPORTs with different FROM/TO subcommands, the INTERVAL() value that you chose should be the smallest of all FROM/TO intervals specified, as in the following example:

 ACCOUNTING                                               
          REDUCE   INTERVAL(15)              
          REPORT   DDNAME(ACCLONG1)  /*ACCOUNTING REPORT*/
                   FROM(,10:00:00)                           
                   TO(,10:59:59)                          
...
          REPORT   DDNAME(ACCLONG2)  /*ACCOUNTING REPORT*/
                   FROM(,11:00:00)                           
                   TO(,11:29:59)                          
...
          REPORT   DDNAME(ACCLONG3)  /*ACCOUNTING REPORT*/
                   FROM(,13:00:00)                           
                   TO(,13:14:59)

In order to ensure that no extra reporting interval is taken into account, specify either FROM or TO in a way that it does not overlap an adjacent interval boundary. In the following example, the report will show only one reporting interval every day FROM(,13:00:00) TO(,13:14:59):

ACCOUNTING                                               
          REDUCE   INTERVAL(15)
          REPORT   DDNAME(ACCLONG3)  /*ACCOUNTING REPORT*/
                   FROM(,13:00:00)                           
                   TO(,13:14:59)

As a comparison, the following report will report two adjacent reporting intervals every day - one from 13:00 to 13:15 and one from 13:15 to 13:30:

 ACCOUNTING                                               
          REDUCE   INTERVAL(15)
          REPORT   DDNAME(ACCLONG3)  /*ACCOUNTING REPORT*/
                   FROM(,13:00)                           
                   TO(,13:15)

For more details, see BOUNDARY subcommand option and INTERVAL subcommand option.

SCOPE

Specifies the scope of the report in a data sharing environment.

MEMBER: In member-scope reports, a data sharing group's instrumentation data is presented member by member. The events are reported in the specified ORDER sequence within the DB2® subsystem (member) where they occurred. Member-scope reports are used for DB2 subsystems that are not involved in data sharing.
GROUP: In group-scope reports, instrumentation data belonging to individual members is merged and presented for the entire group. The events are reported in the specified ORDER sequence within the DB2 data sharing group, regardless of which member of the group actually generated the events.

LAYOUT

Specifies the name of a report layout. You can specify one of the supplied layouts or one that you have previously tailored:

SHORT

This is the default.

LONG

This option provides detailed thread-related data. You can also use other functions such as Record Trace and SQL Activity to find detailed DB2 trace data.

Historical Reporter migration layouts

Use the Historical Reporter migration layouts to help you identify OMEGAMON® XE for DB2 PE data that was previously shown in the reports of the OMEGAMON Historical Reporter. The migration layouts include:

User-defined layouts

You can customize your own report layouts by specifying which blocks of data and which fields within the blocks are included, and their relative order. To adapt the reports according to your requirements, you use user-tailored reporting (UTR). With UTR, you can control the volume, contents, and layout of Statistics traces and reports.

For information about tailoring report layouts, see the Reporting User's Guide.

EXCEPTION

NOEXCEPTION

Specify EXCEPTION if you want to show those records with at least one field in exception status. Otherwise, a standard report is produced.

If you use this option, your JCL must contain a valid DD definition for the ddname EXCPTDD. For more information about required ddnames, see Figure 1.

TOP

To identify report entries with a high value in certain fields, you can produce an Accounting report with TOP lists. TOP lists indicate which entries on the report have the highest value in the field you have specified by using the TOP subcommand option.

For more information about TOP processing, see TOP subcommand option and the Reporting User's Guide. You can specify:

number

By default, the TOP list contains the top ten entries, but you can change the number to anything from one to fifty.

*

Specifies any combination of the fields. You can generate reports showing TOP lists for all the fields available for use with the TOP subcommand option.

ONLY

ONLY indicates the use of TOP as a filter. When TOP is requested as a filter, the index is not shown. For example, the report produced by the command following shows only the top 3 entries for the default TOP field, elapsed time in application (INAPPLET).

 ACCOUNTING
   REPORT
      TOP (3 ONLY)

TOTAL

Produces reports that show total values instead of averages.

keyword

TOP lists for package fields report the maximum values of these fields in individual packages within a trace entry. TOP lists for buffer pool fields contain the totals for all the buffer pools.

The following list shows the keywords that these fields are specified by:

INAPPLET: The class 1 elapsed time (in an application). This value is an average.
If no TOP subcommand option is specified, the default is class 1 elapsed time (INAPPLET).
BUFUPDTS: The number of buffer updates. This value is an average.
CMPERUPD: The ratio of the sum of commits and rollbacks to the sum of SQL UPDATE, SQL INSERT, and SQL DELETE statements.
DCLSTAT: The total number of DCL statements executed. This value is an average.
DDLSTAT: The total number of DDL statements executed.
DMLSTAT: The total number of SQL DML statements executed. This value is an average.
GETPAGES: The number of Getpage requests. This value is an average.
INAPPLPT: The class 1 CPU time in an application. This value is an average.
INAPPLWT: The class 1 waiting time in an application. This value is an average.
INDB2ET: The class 2 elapsed time accumulated in DB2. This value is an average.
INDB2PT: The class 2 CPU time in DB2. This value is an average.
INDB2WT: The class 2 waiting time in DB2. This value is an average.
MAXWRKF: Identifies the threads with the highest values for the field MAX WFILE BLKS (DB2 field QWAC_WORKFILE_MAX) which is shown in the Highlights data blocks. This value is a maximum.
NOTACCT: The time not accounted in DB2. You use this time to determine whether there is a large percentage of time that has not been captured within the DB2 Accounting record. This value is an average.
OUTDB2ET: The elapsed time outside DB2. This value is an average.
OUTDB2PT: The CPU time outside DB2. This value is an average.
OUTDB2WT: The waiting time outside DB2. This value is an average.
PINDBET: The total elapsed time for executing the package or DBRM. This value is an average.
PINDBPT: The CPU time spent by the package or DBRM (class 7). This value is an average.
PNOTACCT: The total unaccounted time in DB2 caused by the execution of the package or DBRM. This value is an average.
PTSUSTME: The waiting time for the package or DBRM caused by a class 8 suspension. This value is an average.
SYNCREAD: The number of synchronous read I/O operations. This value is an average.
TOTPREF: The number of all types of prefetch requests. This value is an average.
TOTSUSP: The number of suspensions. This value is an average.
TOTSUSTM: The waiting time for all class 3 suspensions. This value is an average.
UPDPERCM: The sum of SQL UPDATE, SQL INSERT, and SQL DELETE statements executed.

DDNAME

Specifies the data set where the report is written. The default ddname for report is ACRPTDD.

ORDER

Specifies which OMEGAMON XE for DB2 PE identifiers are used to aggregate Accounting records.

For details, see ORDER subcommand option and OMEGAMON XE for DB2 PE identifiers.

Additionally, you can use the REDUCE INTERVAL to order data on Accounting reports and Statistics reports. For example, if you want to report data at daily interval, specify INTERVAL (1440) in the REDUCE subcommand and ORDER(INTERVAL) in the REPORT subcommand.

INCLUDE/EXCLUDE

Includes or excludes data associated with specific OMEGAMON XE for DB2 PE identifiers.

For details, see INCLUDE and EXCLUDE subcommand options, which lists other allowed identifiers with this command and subcommand combination, and OMEGAMON XE for DB2 PE identifiers.

Special considerations for DDF trace data:

For single DB2 systems the PLANNAME identifier can be used to filter data and order reports in a meaningful manner.

When DB2 is acting as a DDF server, the PLANNAME identifier has a constant value of DISTSERV for requesters using DRDA®. For OM XE for DB2 PE Accounting data, DISTSERV is replaced with the first eight characters of the name of the client application so that filtering on PLANNAME, is meaningful.

Because this replacement is done by the batch accounting component, you need to consider the impact when using the INCLUDE and EXCLUDE subcommand options with other commands. This can cause unexpected results, for example when GLOBAL INCLUDE is used.

The following example shows how PLANNAME can be used in Accounting reports with DDF data to include data from a PLANNAME with the value CAPPNAME:

GLOBAL
   INCLUDE (PLANNAME(DISTSERV))      // to include all DDF data
ACCOUNTING
   REDUCE
      INCLUDE (PLANNAME(CAPPNAME))
   REPORT
      INCLUDE (PLANNAME(CAPPNAME))
⋮

When GLOBAL INCLUDE is omitted, a report is produced, containing replaced names.

When REDUCE INCLUDE is omitted, no report is produced. This is because REDUCE is called implicitly by REPORT by using the GLOBAL filter. As accounting works with the replaced values, DISTSERV is not found in the input records.

When INCLUDE is omitted from REPORT, there is no data to report. The GLOBAL filter is used as default, explicitly including DISTSERV. As accounting works with the replaced values, DISTSERV is not found in the input records.

If the use of PLANNAME as a filter causes unexpected results across reports, use REQLOC, CONNTYPE and THREADTYPE, which are interpreted in exactly the same way in all OM XE for DB2 PE reports.

Example using REPORT with LAYOUT, INCLUDE, FROM, and TO options

The following example specifies:

An Accounting long report
Data is included that is only associated with the location in the range of LOCN01 to LOCN05
Using records between and including the FROM and TO times

⋮
REPORT
   LAYOUT  (LONG)
   INCLUDE (LOCATION(R(LOCN01 LOCN05)))
   FROM    (03/18/99,10:00:00.00)
   TO      (03/19/99,12:00:00.00)
⋮

Example using ACCOUNTING REPORT, specifying two order sets

This example specifies that two reports are produced:

Both reports use the SHORT layout by default.
The first report is ordered by primary authorization ID within plan name within connection ID.
The second report is ordered by plan name within primary authorization ID.
For both reports, a TOP list is produced identifying the top three report entries for INDB2ET (elapsed time spent in DB2).
Both reports include data for the following primary authorization IDs:
- UID0001
- UID0005
- UID0009.
As no ddname was specified, both reports are written to the data set with the default ddname ACRPTDD.

⋮
ACCOUNTING
   REPORT
      TOP     (3 INDB2ET)
      ORDER   (CONNECTION-PLANNAME-PRIMAUTH,PRIMAUTH-PLANNAME)
      INCLUDE (PRIMAUTH(UID0001 UID0005 UID0009))
⋮

Example using ACCOUNTING with multiple REPORT subcommands

This example demonstrates how you can produce Accounting reports to show all accounting data for different requesting locations.

Because the Statistics report set does not offer a breakdown of the activity of DRDA protocol requests, an Accounting report showing the DDF statistics can be useful.

The first report shows fields summarized by the requesting location.

The information is shown for every requesting location, including the reporting location, regardless of the method of access.

The second report shows accounting data summarized by requesting location for all locations (except the reporting location) using DRDA protocol.

⋮
ACCOUNTING
   REPORT
      LAYOUT  (LONG)
      ORDER   (REQLOC)

   REPORT
      LAYOUT  (LONG)
      ORDER   (REQLOC)
      INCLUDE (CONNTYPE(DRDA))
⋮

Feedback