Example of an SQL Activity Report with Workload
The following examples show excerpts of an SQL Activity report with workload detail.
They are generated by the following command:
SQLACTIVITY
REPORT
SUMMARIZEBY (ALL)
WORKLOAD (ALL)
Summarized by Statement Number, with All Workload
This page of the report shows the summary by statement number for primary authorization ID WRL and plan name DSNESPRR for location SYS1DSN2. It includes all workload contained in the input. LOCATION: SYS1DSN2 IBM OMEGAMON FOR DB2 PERFORMANCE
EXPERT (V5R5M0) PAGE: 2-1
GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SE21 TO: NOT SPECIFIED
SUBSYSTEM: SE21 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58
DB2 VERSION: V10 TO: 01/30/15 07:05:37.61
SUMMARIZED BY STMTNO, WITH ALL WORKLOAD
PRIMAUTH: WRL PLANNAME: DSNESPRR THREAD TOTAL: 11 START AET: N/P STOP AET: N/P
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DBRM DSNESM68
# 119 13 DESCRIBE
# 119 25 0.777747 0.641076 PREPARE CURSOR: C1
0.031110 0.025643
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345
--- SCAN ACTIVITY ------------------------------------------------------------------------------------------------------------
------ROWS------ --QUALIFIED AT-- ----------ROWS----------- --PAGES- ---------RI--------
DATABASE PAGESET SCANS PROCESS EXAMINE STAGE 1 STAGE 2 INSERTS UPDATES DELETES SCANNED SCANS DELETES
MEMBER TYPE
DSNDB06 SYSDBASE 70 70 0 70 0 0 0 0 164 0 0
SE21 INDX
DSNDB06 SYSDBASE 46 46 46 0 0 0 0 0 46 0 0
SE21 SEQD
TOTAL 116 116 46 70 0 0 0 0 210 0 0
# 193 12 0.003194 0.002885 CLOSE CURSOR: C1
0.000266 0.000240
# 193 69 2:12.298682 0.070083 FETCH CURSOR: C1
1.917372 0.001016
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345
--- SCAN ACTIVITY ------------------------------------------------------------------------------------------------------------
------ROWS------ --QUALIFIED AT-- ----------ROWS----------- --PAGES- ---------RI--------
DATABASE PAGESET SCANS PROCESS EXAMINE STAGE 1 STAGE 2 INSERTS UPDATES DELETES SCANNED SCANS DELETES
MEMBER TYPE
DSNDB04 DGO71J5L 6 31 31 31 0 0 0 0 24 0 0
SE21 SEQD
DSNDB04 DGO719AX 6 25 25 25 0 0 0 0 24 0 0
SE21 SEQD
TOTAL 12 56 56 56 0 0 0 0 48 0 0
--- LOCK SUSPENSION ACTIVITY -------------------------------------------------------------------------------------------------
---------- SUSPEND REASON ---------- NORML RESUME TIMEO RESUME DEADL RESUME
RESOURCE NAME TYPE REQUEST LOCAL LATCH IRLMQ GROUP NOTIF OTHER COUNT AET COUNT AET COUNT AET
MEMBER
DSNDB04 DGO71J5L PAGESET LOCK 0 0 0 2 0 0 1 59.5816 1 72.4844 0 N/C
SE21
# 193 13 0.003721 0.003398 OPEN CURSOR: C1 ISO(RR) REOPT(NO) KEEP UPD LOCKS: NO
0.000286 0.000261
# 218 4 0.010126 0.007825 DELETE CURSOR: C1 ISO(RR) REOPT(NO)Summarized by Cursor, with All Workload
This page of the report shows the summary by cursor for primary authorization ID WRL and plan name DSNESPRR for location SYS1DSN2. It includes all workload contained in the input.
LOCATION: SYS1DSN2 IBM OMEGAMON FOR DB2 PERFORMANCE
EXPERT (V5R5M0) PAGE: 2-4
GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SE21 TO: NOT SPECIFIED
SUBSYSTEM: SE21 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58
DB2 VERSION: V10 TO: 01/30/15 07:05:37.61
SUMMARIZED BY CURSOR, WITH ALL WORKLOAD
PRIMAUTH: WRL PLANNAME: DSNESPRR THREAD TOTAL: 11 START AET: N/P STOP AET: N/P
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DBRM DSNESM68
C1 13 2:12.305597 0.076366 STMTTYPE COUNT AET/OCCUR TCB/OCCUR COMMITS: 2
10.177354 0.005874 CLOSE 12 0.000266 0.000240
FETCH 69 1.917372 0.001016
OPEN 13 0.000286 0.000261
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345
--- SCAN ACTIVITY ------------------------------------------------------------------------------------------------------------
------ROWS------ --QUALIFIED AT-- ----------ROWS----------- --PAGES- ---------RI--------
DATABASE PAGESET SCANS PROCESS EXAMINE STAGE 1 STAGE 2 INSERTS UPDATES DELETES SCANNED SCANS DELETES
MEMBER TYPE
DSNDB04 DGO71J5L 6 31 31 31 0 0 0 0 24 0 0
SE21 SEQD
DSNDB04 DGO719AX 6 25 25 25 0 0 0 0 24 0 0
SE21 SEQD
TOTAL 12 56 56 56 0 0 0 0 48 0 0
--- LOCK SUSPENSION ACTIVITY -------------------------------------------------------------------------------------------------
---------- SUSPEND REASON ---------- NORML RESUME TIMEO RESUME DEADL RESUME
RESOURCE NAME TYPE REQUEST LOCAL LATCH IRLMQ GROUP NOTIF OTHER COUNT AET COUNT AET COUNT AET
MEMBER
DSNDB04 DGO71J5L PAGESET LOCK 0 0 0 2 0 0 1 59.5816 1 72.4844 0 N/C
SE21
--- PAGE & ROW LOCKING ------------------------------------------------------------------------------
LOCK MAXIMUM PAGE # LOCK HIGHEST TS LOCK AVOID
MEMBER DATABASE PAGESET COUNT SIZE OR ROW LOCKS ESCAL LOCK TYPE SUCCESSFUL
SE21 DSNDB04 DGO71J5L 2 PAGE 1 0 X SIMPL NO
SE21 DSNDB04 DGO719AX 2 PAGE 1 0 X SIMPL NO
SE21 DSNDB06 SYSDBASE 2 TABLE 0 0 IS SIMPL NO
SUMMARY : MAX PAGE OR ROW LOCKS HELD 3 LOCK ESCALATIONS : SHARED 0 EXCLUSIVE 0
TOTAL 6 0
# 119 13 DESCRIBE
# 119 25 0.777747 0.641076 PREPARE CURSOR: C1
0.031110 0.025643
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345Summarized by Program, with All Workload
This page of the report shows the summary by program for primary authorization ID WRL and plan name DSNESPRR for location SYS1DSN2. It includes all workload contained in the input.
LOCATION: SYS1DSN2 IBM OMEGAMON FOR DB2 PERFORMANCE
EXPERT (V5R5M0) PAGE: 2-7
GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SE21 TO: NOT SPECIFIED
SUBSYSTEM: SE21 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58
DB2 VERSION: V10 TO: 01/30/15 07:05:37.61
SUMMARIZED BY PROGRAM, WITH ALL WORKLOAD
PRIMAUTH: WRL PLANNAME: DSNESPRR THREAD TOTAL: 11 START AET: N/P STOP AET: N/P
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DSNESM68 1 2:15.220670 0.785512 DBRM: DSNESM68
STMTTYPE COUNT AET/OCCUR TCB/OCCUR COMMITS: 2
CLOSE 12 0.000266 0.000240
DELETE 4 0.002531 0.001956
DESCRIBE 13
FETCH 69 1.917372 0.001016
INSERT 4 0.523041 0.008089
OPEN 13 0.000286 0.000261
PREPARE 25 0.031110 0.025643
UPDATE 4 0.008759 0.006973
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345
--- SCAN ACTIVITY ------------------------------------------------------------------------------------------------------------
------ROWS------ --QUALIFIED AT-- ----------ROWS----------- --PAGES- ---------RI--------
DATABASE PAGESET SCANS PROCESS EXAMINE STAGE 1 STAGE 2 INSERTS UPDATES DELETES SCANNED SCANS DELETES
MEMBER TYPE
DSNDB04 DGO71J5L 12 53 53 33 0 2 2 0 44 0 0
SE21 SEQD
DSNDB04 DGO719AX 12 43 43 25 0 2 0 0 44 0 0
SE21 SEQD
DSNDB06 SYSDBASE 70 70 0 70 0 0 0 0 164 0 0
SE21 INDX
DSNDB06 SYSDBASE 46 46 46 0 0 0 0 0 46 0 0
SE21 SEQD
TOTAL 140 212 142 128 0 4 2 0 298 0 0
--- LOCK SUSPENSION ACTIVITY -------------------------------------------------------------------------------------------------
---------- SUSPEND REASON ---------- NORML RESUME TIMEO RESUME DEADL RESUME
RESOURCE NAME TYPE REQUEST LOCAL LATCH IRLMQ GROUP NOTIF OTHER COUNT AET COUNT AET COUNT AET
MEMBER
N/P N/P CHANGE 0 0 0 1 0 0 1 0.09668 0 N/C 0 N/C
SE21
N/P N/P LOCK 0 0 0 2 0 0 2 0.14444 0 N/C 0 N/C
SE21
DSNDB04 DGO71J5L PAGESET LOCK 0 0 0 2 0 0 1 59.5816 1 72.4844 0 N/C
SE21
DSNDB04 DGO719AX P/P CAST LOCK 0 0 0 1 0 0 1 0.00707 0 N/C 0 N/C
SE21
--- PAGE & ROW LOCKING ------------------------------------------------------------------------------
LOCK MAXIMUM PAGE # LOCK HIGHEST TS LOCK AVOID
MEMBER DATABASE PAGESET COUNT SIZE OR ROW LOCKS ESCAL LOCK TYPE SUCCESSFUL
SE21 DSNDB04 DGO71J5L 2 PAGE 1 0 X SIMPL NO
SE21 DSNDB04 DGO719AX 2 PAGE 1 0 X SIMPL NO
SE21 DSNDB06 SYSDBASE 2 TABLE 0 0 IS SIMPL NO
SUMMARY : MAX PAGE OR ROW LOCKS HELD 3 LOCK ESCALATIONS : SHARED 0 EXCLUSIVE 0
TOTAL 6 0Summarized by Statement Type, with All Workload
This page of the report shows the summary by statement type for primary authorization ID WRL and plan name DSNESPRR for location SYS1DSN2. It includes all workload contained in the input.
LOCATION: SYS1DSN2 IBM OMEGAMON FOR DB2 PERFORMANCE
EXPERT (V5R5M0) PAGE: 2-8
GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED
MEMBER: SE21 TO: NOT SPECIFIED
SUBSYSTEM: SE21 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58
DB2 VERSION: V10 TO: 01/30/15 07:05:37.61
SUMMARIZED BY STMTTYPE, WITH ALL WORKLOAD
PRIMAUTH: WRL PLANNAME: DSNESPRR THREAD TOTAL: 11 START AET: N/P STOP AET: N/P
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
CLOSE 12 0.003194 0.002885
0.000266 0.000240
DELETE 4 0.010126 0.007825
0.002531 0.001956
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345
--- SCAN ACTIVITY ------------------------------------------------------------------------------------------------------------
------ROWS------ --QUALIFIED AT-- ----------ROWS----------- --PAGES- ---------RI--------
DATABASE PAGESET SCANS PROCESS EXAMINE STAGE 1 STAGE 2 INSERTS UPDATES DELETES SCANNED SCANS DELETES
MEMBER TYPE
DSNDB04 DGO71J5L 2 11 11 0 0 0 0 0 8 0 0
SE21 SEQD
DSNDB04 DGO719AX 2 9 9 0 0 0 0 0 8 0 0
SE21 SEQD
TOTAL 4 20 20 0 0 0 0 0 16 0 0
DESCRIBE 13
FETCH 69 2:12.298682 0.070083
1.917372 0.001016
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 8 RECS/SORT: 3.00 I/O REQS: 1 SUSPENDS : 2 EXITS : 2 AMS : 1
ROWSPROC: 8 WORK/SORT: 2.00 AET/I/O : 1.374752 AET/SUSP : 0.485483 AET/EXIT : 0.048234 AET/AMS : 0.094745
PAGESCAN: 47 PASS/SORT: 2.00 DATACAPT: YES RIDS UNUSED: 2 CHECKCON : REJECTED DEGREE REDUCTION : 3
LOB_PAGSCAN: 12345 LOB_UPD_PAGE: 12345
--- SCAN ACTIVITY ------------------------------------------------------------------------------------------------------------
------ROWS------ --QUALIFIED AT-- ----------ROWS----------- --PAGES- ---------RI--------
DATABASE PAGESET SCANS PROCESS EXAMINE STAGE 1 STAGE 2 INSERTS UPDATES DELETES SCANNED SCANS DELETES
MEMBER TYPE
DSNDB04 DGO71J5L 6 31 31 31 0 0 0 0 24 0 0
SE21 SEQD
DSNDB04 DGO719AX 6 25 25 25 0 0 0 0 24 0 0
SE21 SEQD
TOTAL 12 56 56 56 0 0 0 0 48 0 0
--- LOCK SUSPENSION ACTIVITY -------------------------------------------------------------------------------------------------
---------- SUSPEND REASON ---------- NORML RESUME TIMEO RESUME DEADL RESUME
RESOURCE NAME TYPE REQUEST LOCAL LATCH IRLMQ GROUP NOTIF OTHER COUNT AET COUNT AET COUNT AET
MEMBER
DSNDB04 DGO71J5L PAGESET LOCK 0 0 0 2 0 0 1 59.5816 1 72.4844 0 N/C
SE21
INSERT 4 2.092164 0.032354
0.523041 0.008089