Examples of an SQL Activity Report
This topic shows examples of an SQL Activity report.
Summarized by Statement Number to Summarized by Statement Type show excerpts of an SQL Activity report summarized by all. The layout of the report is similar for each of the possible summary levels. The order is plan name within primary authorization ID, by default.
Summarized by Statement Number
This summary level presents totals for each statement number belonging to the selected combination of OMEGAMON AI for Db2 identifiers. The events are qualified by package name.
By default, the package names are sorted alphabetically and the statement numbers within packages are sorted numerically, in ascending order.
Note: Not every statement
can be summarized by statement number. DDL, for example, has no statement
numbers. An event name is chosen from the closest possible level
of summarization, which is the statement type LOCK in this example.
The following command produces an SQL Activity report summarized by Statement Number:
⋮
SQLACTIVITY
REPORT
SUMMARIZEBY (STMTNO)
⋮This is an example for an SQL Activity report summarized by statement number.
LOCATION: SYS1DSN2 IBM Z OMEGAMON AI FOR DB2 (V6R1M0) PAGE: 1-1 GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED MEMBER: SE11 TO: NOT SPECIFIED SUBSYSTEM: SE11 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58 DB2 VERSION: V13 TO: 01/30/15 07:05:37.61 SUMMARIZED BY STMTNO PRIMAUTH: WRL PLANNAME: PARALCPU THREAD TOTAL: 11 START AET: 0.053771 STOP AET: N/P EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL AET/EVENT TCB/EVENT -------------------- ----------- ----------- --------- ----------------------------------------------------------------------------- PACKAGE SYS1DSN2.PARAL.PARALC01.X'158A622D10FD8B50' DB2OMPETEST ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN) PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P) IMMEDWRITE(PH1) # 120 1 7:10.524819 47.134431 OPEN CURSOR: CRS1 ISO(CS) REOPT(NO) KEEP UPD LOCKS: NO 7:10.524819 47.134431 # 137 12888 36.562407 8.188774 FETCH CURSOR: CRS1 0.002837 0.000635
Summarized by Cursor
This summary level shows totals for each cursor name belonging to the selected combination of OMEGAMON AI for Db2 identifiers that are qualified by package name. By default, the package names and the events within each package are sorted alphabetically.
This is an example for an SQL Activity report summarized by cursor.
LOCATION: STLEC1 IBM Z OMEGAMON AI FOR DB2 (V6R1M0) PAGE: 1-1 GROUP: N/P SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED MEMBER: N/P TO: NOT SPECIFIED SUBSYSTEM: VA1A ORDER: ENDUSER-PRIMAUTH-ORIGAUTH ACTUAL FROM: 01/22/15 21:00:05.88 DB2 VERSION: V13 TO: 01/22/15 21:00:37.11 SUMMARIZED BY CURSOR ENDUSER : SYSADM PRIMAUTH: SYSADM ORIGAUTH: SYSADM THREAD TOTAL: 1 START AET: N/P STOP AET: N/P EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL AET/EVENT TCB/EVENT ----------- ------------ ----------- --------- ---------------------------------------------------------------------------- PACKAGE STLEC1.BARTCOB.PGSPNL1.X'1959EE260805DDDE' TESTE-CURSOR SORTS : 0 GET PAGES : 92 PARALLEL GRP CREATES: 0 SYNC BUFF READS : 0 BUFFER WRITES : 0 INDEX SCANS : 91 TABLESPACE SCANS : 0 ROWS EXAMINED : 0 ROWS PROCESSED : 5203 RID-LIMIT EXC. : 0 RID-NO STORAGE : 0 IN-DB2 ELAPSED : 0.028019 IN-DB2 CPU : 0.018367 GLOBAL LOCK : 0.000000 DRAIN LOCK : 0.000000 LOCK/LATCH : 0.000000 LATCH : 0.000000 PAGE LATCH : 0.000000 CLAIM COUNT : 0.000000 SYNCHRON. I/O : 0.000000 UNIT SWITCH : 0.000000 READ-OTH. THREAD: 0.000000 WRITE-OTH. THREAD : 0.000000 LOG WRITER : 0.000000 1 0.000842 0.000058 STMTTYPE COUNT AET/OCCUR TCB/OCCUR 0.000842 0.000058 CLOSE 1 0.000014 0.000011 OPEN 1 0.000828 0.000047 SQL ACTIVITY REPORT COMPLETE
Summarized by Program
This summary level presents totals for all programs belonging to the selected combination of OMEGAMON AI for Db2 identifiers. Package names are embedded in the summary details.This is an example for an SQL Activity report summarized by program.
LOCATION: SYS1DSN2 IBM Z OMEGAMON AI FOR DB2 (V6R1M0) PAGE: 1-3 GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED MEMBER: SE11 TO: NOT SPECIFIED SUBSYSTEM: SE11 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58 DB2 VERSION: V13 TO: 01/30/15 07:05:37.61 SUMMARIZED BY PROGRAM PRIMAUTH: WRL PLANNAME: PARALCPU THREAD TOTAL: 11 START AET: 0.053771 STOP AET: N/P EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL AET/EVENT TCB/EVENT -------------------- ----------- ----------- --------- ----------------------------------------------------------------------------- PARALC01 1 7:47.087226 55.323204 PACKAGE: SYS1DSN2.PARAL.PARALC01.X'158A622D10FD8B50' 7:47.087226 55.323204 DB2OMPETEST ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN) PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P) IMMEDWRITE(NO) STMTTYPE COUNT AET/OCCUR TCB/OCCUR FETCH 12888 0.002837 0.000635 OPEN 1 7:10.524819 47.134431 N/P 1 6.823537 0.025761 STMTTYPE COUNT AET/OCCUR TCB/OCCUR 6.823537 0.025761 CREATE 1 6.823537 0.025761
Summarized by Statement ID
This summary level groups statements by ID.The following command produces an SQL Activity report summarized by ID:
⋮
SQLACTIVITY
REPORT
SUMMARIZEBY (STMTID)
⋮This is an example for an SQL Activity report summarized by ID.
LOCATION: SYSDBZE IBM Z OMEGAMON AI FOR DB2 (V6R1M0) PAGE: 1-1 GROUP: DBZE SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED MEMBER: SZE1 TO: NOT SPECIFIED SUBSYSTEM: SZE1 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 05/01/15 07:57:15.39 DB2 VERSION: V13 TO: 05/01/15 07:59:12.18 SUMMARIZED BY STMTID PRIMAUTH: BBE PLANNAME: DISTSERV THREAD TOTAL: 2 START AET: N/P STOP AET: N/P EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL AET/EVENT TCB/EVENT -------------------- ----------- ----------- --------- ----------------------------------------------------------------------------- PACKAGE PMODA11GANZLANGE.DSNADM.DSNADMCD.X'0E4D2F6F07F6F0F7' UK67607 ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN) PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P) 304747 2 0.000012 CLOSE 0.000006 STMT ID : 304747 STMT TYPE : STATIC SORTS : 2 GET PAGES : 4 PARALLEL GRP CREATES: 0 SYNC BUFF READS : 0 BUFFER WRITES : 0 INDEX SCANS : 0 TABLESPACE SCANS : 4 ROWS EXAMINED : 0 ROWS PROCESSED : 78 RID-LIMIT EXC. : 0 RID-NO STORAGE : 0 IN-DB2 ELAPSED : 0.000299 IN-DB2 CPU : 0.000296 GLOBAL LOCK : 0.000000 DRAIN LOCK : 0.000000 LOCK/LATCH : 0.000000 LATCH : 0.000000 PAGE LATCH : 0.000000 CLAIM COUNT : 0.000000 SYNCHRON. I/O : 0.000000 UNIT SWITCH : 0.000000 READ-OTH. THREAD: 0.000000 WRITE-OTH. THREAD : 0.000000 LOG WRITER : 0.000000 304764 7 0.000133 DELETE 0.000019 STMT ID : 304764 STMT TYPE : STATIC SORTS : 0 GET PAGES : 0 PARALLEL GRP CREATES: 0 SYNC BUFF READS : 0 BUFFER WRITES : 0 INDEX SCANS : 0 TABLESPACE SCANS : 0 ROWS EXAMINED : 0 ROWS PROCESSED : 0 RID-LIMIT EXC. : 0 RID-NO STORAGE : 0 IN-DB2 ELAPSED : 0.000103 IN-DB2 CPU : 0.000100 GLOBAL LOCK : 0.000000 DRAIN LOCK : 0.000000 LOCK/LATCH : 0.000000 LATCH : 0.000000 PAGE LATCH : 0.000000 CLAIM COUNT : 0.000000 SYNCHRON. I/O : 0.000000 UNIT SWITCH : 0.000000 READ-OTH. THREAD: 0.000000 WRITE-OTH. THREAD : 0.000000 LOG WRITER : 0.000000 SQL ACTIVITY REPORT COMPLETE
Summarized by Statement Type
This summary level shows totals for each cursor name belonging to the selected combination of OMEGAMON AI for Db2 identifiers that are qualified by package name. By default, the package names and the events within each package are sorted alphabetically.This is an example for an SQL Activity report summarized by statement type.
LOCATION: SYS1DSN2 IBM Z OMEGAMON AI FOR DB2 (V6R1M0) PAGE: 1-4 GROUP: DSN2 SQL ACTIVITY - REPORT REQUESTED FROM: NOT SPECIFIED MEMBER: SE11 TO: NOT SPECIFIED SUBSYSTEM: SE11 ORDER: PRIMAUTH-PLANNAME ACTUAL FROM: 01/30/15 06:55:37.58 DB2 VERSION: V13 TO: 01/30/15 07:05:37.61 SUMMARIZED BY STMTTYPE PRIMAUTH: WRL PLANNAME: PARALCPU THREAD TOTAL: 11 START AET: 0.053771 STOP AET: N/P EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL AET/EVENT TCB/EVENT -------------------- ----------- ----------- --------- ----------------------------------------------------------------------------- FETCH 12888 36.562407 8.188774 0.002837 0.000635 OPEN 1 7:10.524819 47.134431 7:10.524819 47.134431
Summarized by Thread
This summary level presents totals for each statement type executed by this combination of OMEGAMON AI for Db2 identifiers. By default, the events are sorted alphabetically. There is no further qualification at this level.
This is an example for an SQL Activity report summarized by thread.
LOCATION: OMPDB2L IBM Z OMEGAMON AI FOR DB2 (V6R1M0) PAGE: 1-2 GROUP: DB2L SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED MEMBER: SDL2 TO: NOT SPECIFIED SUBSYSTEM: SDL2 ACTUAL FROM: 01/30/15 11:36:56.45 DB2 VERSION: V13 SUMMARIZED BY THREAD PRIMAUTH: N/P CONNECT : N/P CORRNAME: N/P CONNTYPE: 'BLANK' ORIGAUTH: N/P PLANNAME: N/P CORRNMBR: THRDTYPE: ALLIED ENDUSER : N/P WSNAME : N/P TRANSACT: N/P TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPUAWDL2.X'C4B7EAC08924' ACE ADDRESS: X'1AC558F8' START TIME: 01/30/15 11:36:56.45 START ELAPSED: N/A START REASON: IN PROGRESS STOP TIME : 01/30/15 11:37:03.28 STOP ELAPSED : N/A STOP REASON : END OF FILE EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL AET/EVENT TCB/EVENT -------------------- ----------- ----------- --------- ---------------------------------------------------------------------- X'C4B7EAC08924' 1 6.823537 0.025761 STMTTYPE COUNT AET/OCCUR TCB/OCCUR 6.823537 0.025761 CREATE 1 6.823537 0.025761