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® for DB2® PE 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 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) 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: V10 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.000635Summarized by Cursor
This summary level shows totals for each cursor name belonging to the selected combination of OMEGAMON for Db2 PE 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 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) 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: V10 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 for Db2 PE identifiers. Package names are embedded in the summary details.This is an example for an SQL Activity report summarized by program.
LOCATION: SYS1DSN2 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) 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: V10 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 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) 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: V10 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 COMPLETESummarized by Statement Type
This summary level shows totals for each cursor name belonging to the selected combination of OMEGAMON for Db2 PE 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 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) 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: V10 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.134431Summarized by Thread
This summary level presents totals for each statement type executed by this combination of OMEGAMON for Db2 PE 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 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) 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: V10
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