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: 12345
Summarized 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 0
Summarized 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