Examples of ordering by Plan, Main Package, and Package
These examples show data ordered by PLANNAME, MAINPACK, and PACKAGE identifiers.
The data shown in Accounting trace - input data for ordering reports is
used to produce the reports shown in Accounting report - ordered by plan, Accounting report - ordered by plan and MAINPACK - sample, and Accounting report - ordered by package - sample.
Accounting trace - input data for ordering reports
The data in the following example has been simplified to ease readability.
LOCATION: STLEC1 OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 7-1
GROUP: N/P Accounting TRACE - SHORT REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: V51A ACTUAL FROM: 08/25/15 22:02:59.55
DB2 VERSION: V10 PAGE DATE: 08/25/15
PRIMAUTH CORRNAME CONNECT ACCT TIMESTAMP COMMITS OPENS UPDATES INSERTS EL. TIME(CL1) EL. TIME(CL2) GETPAGES SYN.READ LOCK SUS
PLANNAME CORRNMBR THR.TYPE TERM. CONDITION SELECTS FETCHES DELETES PREPARE CPU TIME(CL1) CPU TIME(CL2) BUF.UPDT TOT.PREF LOCKOUTS
-------- -------- -------- --------------- ------- ------- ------- ------- -------------- -------------- -------- -------- --------
XXUSER01 CORRXXXX BATCH 12:00:00.000000 1 1 0 0 10.000000 4.444444 2 0 0
PLANX 'BLANK' ALLIED NORM DEALLOC 0 1 0 0 0.100000 0.044444 2 0 0
---------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKA PACKAGE 1 1.000000 0.010000 0.000000 0|
|PACKB PACKAGE 1 2.000000 0.020000 0.000000 0|
|PACKC PACKAGE 1 1.000000 0.010000 0.000000 0|
---------------------------------------------------------------------------------------------
XXUSER01 CORRXXXX BATCH 12:01:00.000000 1 1 0 0 20.000000 15.555555 1 0 0
PLANX 'BLANK' ALLIED NORM DEALLOC 0 1 0 0 0.200000 0.155555 1 0 0
---------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKB PACKAGE 1 10.000000 0.100000 0.000000 0|
|PACKD PACKAGE 1 4.000000 0.040000 0.000000 0|
---------------------------------------------------------------------------------------------
XXUSER01 CORRXXXX BATCH 12:02:00.000000 1 1 0 0 30.000000 16.666666 4 0 0
PLANY 'BLANK' ALLIED NORM DEALLOC 0 1 0 0 0.300000 0.166666 0 0 0
---------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKD PACKAGE 1 10.000000 0.100000 0.000000 0|
|PACKA PACKAGE 1 5.000000 0.050000 0.000000 0|
---------------------------------------------------------------------------------------------
XXUSER01 CORRXXXX BATCH 12:03:00.000000 1 1 0 0 30.000000 8.888888 3 0 0
PLANX 'BLANK' ALLIED NORM DEALLOC 0 1 0 0 0.300000 0.088888 0 0 0
---------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKB PACKAGE 1 6.000000 0.060000 0.000000 0|
|PACKD PACKAGE 1 2.000000 0.020000 0.000000 0|
---------------------------------------------------------------------------------------------
The input data contains information about:
- Two plans. PLANX is used by three threads and PLANY by one thread. The first instance of PLANX shows that three packages (PACKA, PACKB, and PACKC) are executed. The elapsed time for the entire PLANX is 10 seconds and CPU times for the individual packages are 0.01, 0.02, and 0.01 seconds.
- Four packages. Different combinations of packages were executed under the different plans, because a plan does not necessarily invoke the same packages each time it is executed. This can happen when, for example, a number of packages are bound in a single plan in a CICS environment and different packages are executed in different circumstances.
Ordering by Plan
This
example shows the result of ordering the input data by plan. The following
command was used to produce the example shown in Accounting report - ordered by plan.
⋮
ACCOUNTING
REPORT
ORDER (PLANNAME)
⋮
Accounting report - ordered by plan
The following example shows an Accounting report that contains an entry for both plans in the input data.
Data for the different packages is summarized under the plans. Also, different DB2 executions of PLANX are summarized in one entry.
LOCATION: DSNCAT OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-1
GROUP: DSNCAT Accounting REPORT - SHORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SSDQ TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: PLANNAME INTERVAL FROM: 07/14/15 18:47:13.28
DB2 VERSION: V10 SCOPE: MEMBER TO: 07/14/15 19:55:28.69
#OCCURS #ROLLBK SELECTS INSERTS UPDATES DELETES CLASS1 EL.TIME CLASS2 EL.TIME GETPAGES SYN.READ LOCK SUS
PLANNAME #DISTRS #COMMIT FETCHES OPENS CLOSES PREPARE CLASS1 CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF #LOCKOUT
---------------------------------- ------- ------- ------- ------- ------- ------- -------------- -------------- -------- -------- --------
PLANX 3 0 0.00 0.00 0.00 0.00 20.000000 9.629629 2.00 0.00 0.00
0 3 1.00 1.00 1.00 0.00 0.200000 0.096292 1.00 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKA PACKAGE 1 1.00 1.000000 0.010000 0.000000 0.00|
|PACKB PACKAGE 3 1.00 6.000000 0.060000 0.000000 0.00|
|PACKC PACKAGE 1 1.00 1.000000 0.010000 0.000000 0.00|
|PACKD PACKAGE 2 1.00 3.000000 0.030000 0.000000 0.00|
------------------------------------------------------------------------------------------------------
PLANY 1 0 0.00 0.00 0.00 0.00 30.000000 16.666667 4.00 0.00 0.00
0 1 1.00 1.00 1.00 0.00 0.300000 0.166667 0.00 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKD PACKAGE 1 1.00 10.000000 0.100000 0.000000 0.00|
|PACKA PACKAGE 1 1.00 5.000000 0.050000 0.000000 0.00|
------------------------------------------------------------------------------------------------------
Ordering by Plan and MAINPACK: ORDER (PLANNAME-MAINPACK)
The MAINPACK identifier is used to distinguish between records with the same plan name, but which executed different packages.
This example shows the result of ordering the input data by plan and MAINPACK.
MAINPACK identifies a representative package within the plan. The first package ID is the default for MAINPACK. However, in this example, the MAINPACK member of the DPMPARMS data set has been modified so that it is the package ID of the last executed package, see Defining the MAINPACK identifier for information about MAINPACK.
In the input data shown in Accounting trace - input data for ordering reports, it is assumed that PACKC is the last executed package in the first entry for PLANX, PACKD for the second and third entries of PLANX, and PACKA for the entry of PLANY.
The following command was
used to produce the report in Accounting report - ordered by plan and MAINPACK - sample.
⋮
ACCOUNTING
REPORT
ORDER (PLANNAME-MAINPACK)
⋮
Accounting report - ordered by plan and MAINPACK - sample
The following example shows an Accounting report that contains an entry for each combination of PLANNAME and MAINPACK.
LOCATION: DSNCAT OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-1
GROUP: DSNCAT Accounting REPORT - SHORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SSDQ TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: PLANNAME-MAINPACK INTERVAL FROM: 07/14/15 18:47:13.28
DB2 VERSION: V11 SCOPE: MEMBER TO: 07/14/15 19:55:28.69
PLANNAME #OCCURS #ROLLBK SELECTS INSERTS UPDATES DELETES CLASS1 EL.TIME CLASS2 EL.TIME GETPAGES SYN.READ LOCK SUS
MAINPACK #DISTRS #COMMIT FETCHES OPENS CLOSES PREPARE CLASS1 CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF #LOCKOUT
---------------------------------- ------- ------- ------- ------- ------- ------- -------------- -------------- -------- -------- --------
PLANX 1 0 0.00 0.00 0.00 0.00 10.000000 4.444444 2.00 0.00 0.00
PACKC 0 1 1.00 1.00 0.00 0.00 0.100000 0.044444 2.00 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKA PACKAGE 1 1.00 1.000000 0.010000 0.000000 0.00|
|PACKB PACKAGE 1 1.00 2.000000 0.020000 0.000000 0.00|
|PACKC PACKAGE 1 1.00 1.000000 0.010000 0.000000 0.00|
------------------------------------------------------------------------------------------------------
PLANX 2 0 0.00 0.00 0.00 0.00 25.000000 12.222222 2.00 0.00 0.00
PACKD 0 1 1.00 1.00 1.00 0.00 0.250000 0.122222 0.50 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKB PACKAGE 2 1.00 8.000000 0.080000 0.000000 0.00|
|PACKD PACKAGE 2 1.00 3.000000 0.030000 0.000000 0.00|
------------------------------------------------------------------------------------------------------
PLANY 1 0 0.00 0.00 0.00 0.00 30.000000 16.666666 4.00 0.00 0.00
PACKA 0 1 1.00 1.00 1.00 0.00 0.300000 0.166666 0.00 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8 SUSP|
|PACKD PACKAGE 1 1.00 10.000000 0.100000 0.000000 0.00|
|PACKA PACKAGE 1 1.00 5.000000 0.050000 0.000000 1.00|
------------------------------------------------------------------------------------------------------
There are two entries for PLANX:
- One entry where PACKC is the last package executed.
- Another entry where PACKD is the last package executed. Threads 2 and 4 are combined in this entry.
There is one entry for PLANY with the representative package PACKA.
You cannot use this report to attribute the General Accounting data to one package, unless only one package exists within a plan.
Ordering by Package or DBRM: ORDER (PACKAGE)
The previous examples present packages within plans. To summarize the package Accounting data regardless of the plan under which the packages or DBRMs were executed, you can order by package.
This example shows the result of ordering the input data by package.
The
following command was used to produce the report in Accounting report - ordered by package - sample.
⋮
ACCOUNTING
REPORT
ORDER (PACKAGE)
⋮
Accounting report - ordered by package - sample
The following example shows the use of resources on a per package/DBRM basis in an Accounting report, regardless of the plan under which a particular package is executed.
LOCATION: DSNCAT OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-1
GROUP: DSNCAT Accounting REPORT - SHORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SSDQ TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: PACKAGE INTERVAL FROM: 07/14/15 18:47:13.28
DB2 VERSION: V11 SCOPE: MEMBER TO: 07/14/15 19:55:28.69
TYPE SQLSTMT CL7.CPU TIME CL8 SUSP
PACKAGE #OCCURS CL7 ELAP.TIME CL8 SUSP.TIME
---------------------------------------------------------- ------- ------------- ------------- --------
APC1.COLL1.PACKA PACKAGE 1.00 0.030000 0.00
2 3.000000 0.000000
APC1.COLL1.PACKB PACKAGE 1.00 0.060000 0.00
3 6.000000 0.000000
APC1.COLL1.PACKC PACKAGE 1.00 0.010000 0.00
1 1.000000 0.000000
APC1.COLL1.PACKD PACKAGE 1.00 0.053333 0.00
3 5.333333 0.000000
Note: Accounting reports that are ordered by package identifier
(created by using the PACKAGE keyword with the ORDER subcommand option)
show only the following report blocks:
- Package Identification
- Times - Class 7 - Package Times
- Package Suspensions
- Global Contention L-Locks
- Global Contention P-Locks
- Package Buffer Pool Activity
- Package SQL Activity
- Package Locking Activity