Examples of using the MAINPACK and PACKAGE identifiers
The following examples show how you can use the MAINPACK and PACKAGE identifiers. The first example is a short report and shows the short format of the package information. The second example shows the long format of the package information. Both examples show how to use these identifiers in INCLUDE/EXCLUDE and ORDER processing.
Example 1: Using MAINPACK
Assume that, during the reporting interval used in this example, two transactions are performed on behalf of a non-DB2 requester. One transaction executes package CUSTINQR and the other transaction executes package ACCTCLOS. You only want to report the activities that are performed by these non-DB2 requesters, that is, DISTSERV plans.
If you use PLANNAME to order the report, you cannot distinguish between the two packages or the two transactions because they both execute plan DISTSERV. So to identify the transactions by means of a package, the MAINPACK identifier is used in the ORDER. For this case the default specification for MAINPACK is used, which is to select the package ID of the first package executed within a plan. The Accounting report is then summarized and ordered by this MAINPACK (first package ID).
The following command was used:
⋮
ACCOUNTING
REPORT
INCLUDE (PLANNAME(DISTSERV))
ORDER (MAINPACK)
⋮
Short Accounting report - ordered by MAINPACK
Here is an example of a short Accounting report that is ordered by MAINPACK.
LOCATION: VTAMA OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-1
GROUP: N/P ACCOUNTING REPORT - SHORT REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: MAINPACK INTERVAL FROM: 04/29/15 20:18:50.43
DB2 VERSION: V11 SCOPE: MEMBER TO: 05/20/15 17:57:56.76
#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
---------------------------------- ------- ------- ------- ------- ------- ------- -------------- -------------- -------- -------- --------
CUSTINQR 1 0 0.00 0.00 0.00 0.00 6.057603 5.969593 31.00 11.00 0.00
0 1 3.00 3.00 3.00 0.00 0.095606 0.080949 0.00 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 TCB TIME CL8 SUSP.TIME CL8 SUSP|
|CUSTINQR PACKAGE 1 3.00 5.560211 0.050515 1.167913 7.00|
------------------------------------------------------------------------------------------------------
ACCTCLOS 1 0 0.00 0.00 0.00 0.00 0.271892 0.172424 17.00 2.00 0.00
0 1 5.00 5.00 5.00 0.00 0.035721 0.014262 0.00 0.00 0
------------------------------------------------------------------------------------------------------
|PROGRAM NAME TYPE #OCCURS SQLSTMT CL7 ELAP.TIME CL7 TCB TIME CL8 SUSP.TIME CL8 SUSP|
|ACCTCLOS PACKAGE 1 3.00 0.078471 0.005308 0.006995 1.00|
------------------------------------------------------------------------------------------------------
Example 2: Using PACKAGE
Assume that, during the reporting interval used in this example, three transactions are performed and that DB2 Accounting Trace classes 7 and 8 are active.
- The first transaction executes plan PLANINQ1 and uses packages CUSTINQA and CUSTINQB and DBRM CUSTINQC.
- The second transaction executes plan PLANINQ2 and uses packages CUSTINQB and CUSTINQD.
- The third transaction executes plan PLANINQ1 also but uses package CUSTINQA only.
All the packages are executed once within the transaction and the transactions are executed only once.
If you want to find out the resource utilization for each package or DBRM, you ORDER the report by PACKAGE and print only the package or DBRM specific data from the Accounting records. In this example, you want detailed information about the packages or DBRM, therefore you request a LONG report.
You have previously determined that you are not interested in package CUSTINQD, so you exclude that package. By default all plan names present in the input data are included in the report.
⋮
ACCOUNTING
REPORT
LAYOUT (LONG)
EXCLUDE (PACKAGE(CUSTINQD))
ORDER (PACKAGE)
⋮
Long Accounting report - ordered by PACKAGE
Here is an example of a long Accounting report that is ordered by PACKAGE. It shows the Package Information blocks for the different packages. In this example, each entry begins on a new page, even though all entries fit on a single page.
On this report:
- Information for package CUSTINQA is derived from the first and the third transaction.
- Information for package CUSTINQB is derived from the first and the second transaction.
- Information for DBRM CUSTINQC is derived from the first transaction.
To print all entries on one page you can create your own layout
by modifying the sample layout LONG. You can do this without changing
the actual layout. Simply press F3 in the UTR Block Selection panel
to display the UTR Save Layout Details panel (see Figure 1). From this panel,
you can specify no for the question Is each
new record or entry to start on a new page? You can save
the layout under a new name and use it when ordering by PACKAGE.
LOCATION: VTAMA OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-1
GROUP: N/P ACCOUNTING REPORT - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: PACKAGE INTERVAL FROM: 04/29/15 20:18:50.43
DB2 VERSION: V10 SCOPE: MEMBER TO: 05/20/15 17:57:56.76
PACKAGE: 'BLANK'.BOSNACOL.CUSTINQA
CUSTINQA VALUE CUSTINQA TIMES CUSTINQA AVERAGE TIME AVG.EV TIME/EVENT
------------------ ------------------ ------------------ ------------ ------------------ ------------ ------ ----------
TYPE PACKAGE ELAP-CL7 TIME-AVG 0.023464 LOCK/LATCH 0.000000 0.00 N/C
CPU TIME 0.004540 SYNCHRONOUS I/O 0.012075 1.00 0.012075
LOCATION 'BLANK' AGENT 0.004540 OTHER READ I/O 0.000000 0.00 N/C
COLLECTION ID BOSNACOL PAR.TASKS 0.000000 OTHER WRITE I/O 0.000000 0.00 N/C
PROGRAM NAME CUSTINQA SUSPENSION-CL8 0.012075 SERV.TASK SWITCH 0.000000 0.00 N/C
AGENT 0.012075 ARCH.LOG(QUIESCE) 0.000000 0.00 N/C
OCCURRENCES 2 PAR.TASKS 0.000000 ARCHIVE LOG READ 0.000000 0.00 N/C
SQL STMT - AVERAGE 3.00 NOT ACCOUNTED 0.211530 DRAIN LOCK 0.000000 0.00 N/C
SQL STMT - TOTAL 6 AVG.DB2 ENTRY/EXIT 6.00 CLAIM RELEASE 0.000000 0.00 N/C
STOR PROC EXECUTED 20 DB2 ENTRY/EXIT 12 PAGE LATCH 0.000000 0.00 N/C
UDF EXECUTED 0 SCHED STORED PROC 0.000000 0.00 N/C
USED BY STOR PROC 0 CPU SERVICE UNITS 11200 SCHEDULE UDF 0.000000 0.00 N/C
USED BY UDF 0 AGENT 8000 NOTIFY MESSAGES 0.000000 0.00 N/C
USED BY TRIGGER 0 PAR.TASKS 3200 GLOBAL CONTENTION 0.000000 0.00 N/C
SUCC AUTH CHECK 0 TOTAL CL8 SUSPENS. 0.012075 1.00 0.012075
LOCATION: VTAMA OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-2
GROUP: N/P ACCOUNTING REPORT - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: PACKAGE INTERVAL FROM: 04/29/15 20:18:50.43
DB2 VERSION: V10 SCOPE: MEMBER TO: 05/20/15 17:57:56.76
PACKAGE: 'BLANK'.BOSNACOL.CUSTINQB
CUSTINQB VALUE CUSTINQB TIMES CUSTINQB AVERAGE TIME AVG.EV TIME/EVENT
------------------ ------------------ ------------------ ------------ ------------------ ------------ ------ -----------
TYPE PACKAGE ELAP-CL7 TIME-AVG 0.092622 LOCK/LATCH 0.000000 0.00 N/C
CPU TIME 0.004247 SYNCHRONOUS I/O 0.027006 1.00 0.027006
LOCATION 'BLANK' AGENT 0.004247 OTHER READ I/O 0.000000 0.00 N/C
COLLECTION ID BOSNACOL PAR.TASKS 0.000000 OTHER WRITE I/O 0.000000 0.00 N/C
PROGRAM NAME CUSTINQB SUSPENSION-CL8 0.027006 SERV.TASK SWITCH 0.000000 0.00 N/C
AGENT 0.019006 ARCH.LOG(QUIESCE) 0.000000 0.00 N/C
OCCURRENCES 2 PAR.TASKS 0.008000 ARCHIVE LOG READ 0.000000 0.00 N/C
SQL STMT - AVERAGE 3.00 NOT ACCOUNTED 0.258774 DRAIN LOCK 0.000000 0.00 N/C
SQL STMT - TOTAL 6 AVG.DB2 ENTRY/EXIT 6.00 CLAIM RELEASE 0.000000 0.00 N/C
STOR PROC EXECUTED 0 DB2 ENTRY/EXIT 12 PAGE LATCH 0.000000 0.00 N/C
UDF EXECUTED 0 SCHED STORED PROC 0.000000 0.00 N/C
USED BY STOR PROC 0 CPU SERVICE UNITS 11920 SCHEDULE UDF 0.000000 0.00 N/C
USED BY UDF 0 AGENT 7100 NOTIFY MESSAGES 0.000000 0.00 N/C
USED BY TRIGGER 0 PAR.TASKS 4820 GLOBAL CONTENTION 0.000000 0.00 N/C
SUCC AUTH CHECK 0 TOTAL CL8 SUSPENS. 0.027006 1.00 0.027006
LOCATION: VTAMA OMEGAMON XE for DB2 Performance Expert (V5R4M0) PAGE: 1-3
GROUP: N/P ACCOUNTING REPORT - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: SSDQ ORDER: PACKAGE INTERVAL FROM: 04/29/15 20:18:50.43
DB2 VERSION: V10 SCOPE: MEMBER TO: 05/20/15 17:57:56.76
PACKAGE: 'BLANK'.'BLANK'.CUSTINQC
CUSTINQC VALUE CUSTINQC TIMES CUSTINQC AVERAGE TIME AVG.EV TIME/EVENT
------------------ ------------------ ------------------ ------------ ------------------ ------------ ------ -----------
TYPE DBRM ELAP-CL7 TIME-AVG 0.031617 LOCK/LATCH 0.000000 0.00 N/C
CPU TIME 0.004170 SYNCHRONOUS I/O 0.027447 1.00 0.027447
LOCATION 'BLANK' AGENT 0.004170 OTHER READ I/O 0.000000 0.00 N/C
COLLECTION ID 'BLANK' PAR.TASKS 0.000000 OTHER WRITE I/O 0.000000 0.00 N/C
PROGRAM NAME CUSTINQC SUSPENSION-CL8 0.027447 SERV.TASK SWITCH 0.000000 0.00 N/C
AGENT 0.019000 ARCH.LOG(QUIESCE) 0.000000 0.00 N/C
OCCURRENCES 1 PAR.TASKS 0.008447 ARCHIVE LOG READ 0.000000 0.00 N/C
SQL STMT - AVERAGE 3.00 NOT ACCOUNTED 0.268774 DRAIN LOCK 0.000000 0.00 N/C
SQL STMT - TOTAL 3 AVG.DB2 ENTRY/EXIT 6.00 CLAIM RELEASE 0.000000 0.00 N/C
STOR PROC EXECUTED 21 DB2 ENTRY/EXIT 6 PAGE LATCH 0.000000 0.00 N/C
UDF EXECUTED 0 SCHED STORED PROC 0.000000 0.00 N/C
USED BY STOR PROC 0 CPU SERVICE UNITS 12400 SCHEDULE UDF 0.000000 0.00 N/C
USED BY UDF 0 AGENT 8600 NOTIFY MESSAGES 0.000000 0.00 N/C
USED BY TRIGGER 0 PAR.TASKS 3800 GLOBAL CONTENTION 0.000000 0.00 N/C
SUCC AUTH CHECK 0 TOTAL CL8 SUSPENSIONS 0.27447 1.00 0.027447