Example of an SQL Activity Trace
The following examples show excerpts of an SQL Activity trace summarized at all levels.
They are generated by the following command:
⋮
SQLACTIVITY
TRACE
SUMMARIZEBY (ALL)
⋮SQL Activity Trace Summarized by All
This summary level shows totals for each cursor name belonging to the thread that is qualified by package name. By default, the package names and the events within each package are sorted alphabetically.
This is an example of an SQL Activity Trace Summarized by All
LOCATION: PMODA21 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DA21 ACTUAL FROM: 03/02/15 15:57:19.88
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE
PRIMAUTH: EDVA CONNECT : BATCH CORRNAME: EDVADDL CONNTYPE: TSO
ORIGAUTH: EDVA PLANNAME: DSNTEP2 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : EDVA WSNAME : BATCH TRANSACT: EDVADDL
TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPSARA21.X'CE9608EA5959' ACE ADDRESS: X'1E4CEC60'
START TIME: 03/02/15 15:57:19.88 START ELAPSED: 0.005397 START REASON: CREATE THREAD
STOP TIME : 03/02/15 15:57:20.22 STOP ELAPSED : 0.002567 STOP REASON : TERMINATE THREAD
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
DESCRIBE 15:57:19.94 STMT# 1415 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.09 0.022793 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: INSERT INTO BRT.BRTTB001 ( DEC_17 , CHR_40 ) VALUES ( 1, 'One' )
DESCRIBE 15:57:20.12 STMT# 1900 SQLST:00000 SQLCO: 0
INSERT 15:57:20.12 0.000521 STMT# 1924 ISO(CS) SQLST:00000 SQLCO: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS(N/A)
DESCRIBE 15:57:20.12 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.12 STMT# 5390 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.12 0.014470 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: COMMIT
DESCRIBE 15:57:20.13 STMT# 1900 SQLST:00000 SQLCO: 0
SYNC. 15:57:20.13 0.021406
DBRM DSN@EP2L
DESCRIBE 15:57:20.15 STMT# 1924 SQLST:00000 SQLCO: 0
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
DESCRIBE 15:57:20.15 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.15 STMT# 5390 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.15 0.000021 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0SQL Activity Trace Summarized by Occurrence
Summary by occurrence shows individual SQL statement occurrences. In this thread the SQL statements belong to one package, the name of which is printed at the head of its work. When present, SQL text and DDF information is embedded in the events. Commits appear as standalone events. The events are, by default, sorted in timestamp sequence.
This is an example of an SQL Activity Trace Summarized by Occurrence.
LOCATION: STLEC1 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: V71A ACTUAL FROM:01/30/13 17:16:27.41
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE
PRIMAUTH: SYSADM CONNECT : BATCH CORRNAME: INS CONNTYPE: TSO
ORIGAUTH: SYSADM PLANNAME: DSNTEP3 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : 'BLANK' WSNAME : 'BLANK' TRANSACT: 'BLANK'
TRACE # 1.1 DB2 LUWID: USIBMSY.SYEC1DB2.X'B3D971189B05' ACE ADDRESS: X'06111A88'
START TIME: 01/30/15 17:16:27.41 START ELAPSED: 0.003678 START REASON: CREATE THREAD
STOP TIME : 01/30/15 17:16:27.85 STOP ELAPSED : 0.012713 STOP REASON : TERMINATE THREAD
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
PACKAGE STLEC1.DSNTEP3.DSNTEP3.X'167241E51B69975C'
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
IMMEDWRITE(NO)
PREPARE 17:16:27.41 0.036485 0.011015 STMT# 1505 CURSOR: C1 SQLSTATE: 00000 SQLCODE: 0
TEXT: INSERT INTO M80119.SBDEALS VALUES(1000003,'EEEE','FFFFFFFFF',
'GGGGGGGGG','HH')
DESCRIBE 17:16:27.45 STMT# 1511 SQLSTATE: 00000 SQLCODE: 0
1 TRIGGER 17:16:27.46 0.180439 0.001409 STMT# 1216 TRIGGER : SBTRIGR SQLSTATE: N/P SQLCODE: 0
COLLID : DSNTEP3 PROGRAM : DSNTEP3 SCHEMA : M80119
EXT_NAM: SBTRIGR ACT_TIME : AFTER
GRAN : STMT STMT : INSERT EVAL : TRUE
PACKAGE STLEC1.M80119.SBTRIGR.X'167B2D671A3417BC'
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(BIND)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
IMMEDWRITE(NO)
2 CALL 17:16:27.46 0.178961 0.000739 STMT# 0 PROCEDURE: POPULATE_SBDEALS SQLSTATE: N/P SQLCODE: 0
SCHEDULE TIME: 0.058490 SCHEDULE TCB: 0.062429 SCHEMA : M80119
PACKAGE STLEC1.M80119.DEALPROC.X'167B2D5A18AD18EC'
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
IMMEDWRITE(NO)
2 OPEN 17:16:27.52 0.000468 0.000136 STMT# 44 CURSOR: C1 ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: NO
2 FETCH 17:16:27.52 0.000190 0.000069 STMT# 52 CURSOR: C1 SQLSTATE: 02000 SQLCODE: 100
2 INSERT 17:16:27.64 0.000961 0.000698 STMT# 64 ISO(CS) SQLSTATE: 23505 SQLCODE: -803
REOPTIMIZED(NO) KEEP UPDATE LOCKS: N/A
2 CLOSE 17:16:27.64 0.000162 0.000068 STMT# 66 CURSOR: C1 SQLSTATE: 00000 SQLCODE: 0
PACKAGE STLEC1.DSNTEP3.DSNTEP3.X'167241E51B69975C'
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
IMMEDWRITE(NO)
PREPARE 17:16:27.74 0.013520 0.006982 STMT# 1505 CURSOR: C1 SQLSTATE: 00000 SQLCODE: 0
TEXT: SELECT * FROM M80119.TRIGTBL
DESCRIBE 17:16:27.75 STMT# 1511 SQLSTATE: 00000 SQLCODE: 0
OPEN 17:16:27.75 0.000091 0.000051 STMT# 1574 CURSOR: C1 ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: NO
FETCH 17:16:27.75 0.001488 0.000559 STMT# 1618 CURSOR: C1 SQLSTATE: 00000 SQLCODE: 0
FETCH 17:16:27.75 0.001106 0.000398 STMT# 1618 CURSOR: C1 SQLSTATE: 02000 SQLCODE: 100
CLOSE 17:16:27.75 0.000123 0.000070 STMT# 2056 CURSOR: C1 SQLSTATE: 00000 SQLCODE: 0
CREATE 11:36:56.45 6.823537 0.025761 TYPE: ROW PERMISSION NAME: DEATEST SQL Activity Trace Summarized by Occurrence
This summary level presents totals for each program name belonging to the thread.
This is an example of an SQL Activity Trace Summarized by Occurrence.
LOCATION: PMODA21 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DA21 ACTUAL FROM: 03/02/15 15:57:19.88
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE
PRIMAUTH: EDVA CONNECT : BATCH CORRNAME: EDVADDL CONNTYPE: TSO
ORIGAUTH: EDVA PLANNAME: DSNTEP2 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : EDVA WSNAME : BATCH TRANSACT: EDVADDL
TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPSARA21.X'CE9608EA5959' ACE ADDRESS: X'1E4CEC60'
START TIME: 03/02/15 15:57:19.88 START ELAPSED: 0.005397 START REASON: CREATE THREAD
STOP TIME : 03/02/15 15:57:20.22 STOP ELAPSED : 0.002567 STOP REASON : TERMINATE THREAD
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
DESCRIBE 15:57:19.94 STMT# 1415 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.09 0.022793 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: INSERT INTO BRT.BRTTB001 ( DEC_17 , CHR_40 ) VALUES ( 1, 'One' )
DESCRIBE 15:57:20.12 STMT# 1900 SQLST:00000 SQLCO: 0
INSERT 15:57:20.12 0.000521 STMT# 1924 ISO(CS) SQLST:00000 SQLCO: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS(N/A)
DESCRIBE 15:57:20.12 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.12 STMT# 5390 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.12 0.014470 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: COMMIT
DESCRIBE 15:57:20.13 STMT# 1900 SQLST:00000 SQLCO: 0
SYNC. 15:57:20.13 0.021406
DBRM DSN@EP2L
DESCRIBE 15:57:20.15 STMT# 1924 SQLST:00000 SQLCO: 0
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
DESCRIBE 15:57:20.15 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.15 STMT# 5390 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.15 0.000021 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0 SQLST:00000 SQLCO: 0
LOCATION: PMODA21 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-2
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DA21 ACTUAL FROM: 03/02/15 15:57:19.88
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE
PRIMAUTH: EDVA CONNECT : BATCH CORRNAME: EDVADDL CONNTYPE: TSO
ORIGAUTH: EDVA PLANNAME: DSNTEP2 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : EDVA WSNAME : BATCH TRANSACT: EDVADDL
TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPSARA21.X'CE9608EA5959' ACE ADDRESS: X'1E4CEC60'
START TIME: 03/02/15 15:57:19.88 START ELAPSED: 0.005397 START REASON: CREATE THREAD
STOP TIME : 03/02/15 15:57:20.22 STOP ELAPSED : 0.002567 STOP REASON : TERMINATE THREAD
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
TEXT: SELECT * FROM BRT.BRTTB001
DESCRIBE 15:57:20.15 STMT# 1900 SQLST:00000 SQLCO: 0
OPEN 15:57:20.15 0.000007 STMT# 1952 CURSOR: C1 ISO(CS) SQLST:00000 SQLCO: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS(NO) SCROLL(NO) SENSITIVE(UNS) TABLE(UNS)
IMPLICIT COMMIT(NO )
FETCH 15:57:20.15 0.000069 STMT# 1982 CURSOR: C1 SQLST:00000 SQLCO: 0
SENSITIVE(UNS) ORIENTATION(NEXT)
FETCH 15:57:20.15 0.000014 STMT# 1982 CURSOR: C1 SQLST:02000 SQLCO: 100
SENSITIVE(UNS) ORIENTATION(NEXT)
CLOSE 15:57:20.15 0.000005 STMT# 2277 CURSOR: C1 SQLST:00000 SQLCO: 0
CLOSE TYPE(EXPLICIT)
STMT ID : 29 STMT TYPE : DYNAMIC
SORTS : 0
GET PAGES : 2 PARALLEL GRP CREATES: 0
SYNC BUFF READS : 0 BUFFER WRITES : 0
INDEX SCANS : 0 TABLESPACE SCANS : 1
ROWS EXAMINED : 1 ROWS PROCESSED : 1
RID-LIMIT EXC. : 0 RID-NO STORAGE : 0
IN-DB2 ELAPSED : 0.000089 IN-DB2 CPU : 0.000070
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
PREPARE 15:57:20.15 0.000049 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: COMMIT
DESCRIBE 15:57:20.15 STMT# 1900 SQLST:00000 SQLCO: 0
SYNC. 15:57:20.15 0.000020
DBRM DSN@EP2L
DESCRIBE 15:57:20.15 STMT# 1924 SQLST:00000 SQLCO: 0
LOCATION: PMODA21 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-3
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DA21 ACTUAL FROM: 03/02/15 15:57:19.88
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE
PRIMAUTH: EDVA CONNECT : BATCH CORRNAME: EDVADDL CONNTYPE: TSO
ORIGAUTH: EDVA PLANNAME: DSNTEP2 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : EDVA WSNAME : BATCH TRANSACT: EDVADDL
TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPSARA21.X'CE9608EA5959' ACE ADDRESS: X'1E4CEC60'
START TIME: 03/02/15 15:57:19.88 START ELAPSED: 0.005397 START REASON: CREATE THREAD
STOP TIME : 03/02/15 15:57:20.22 STOP ELAPSED : 0.002567 STOP REASON : TERMINATE THREAD
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
DESCRIBE 15:57:20.15 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.15 STMT# 5390 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.15 0.024552 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: DELETE FROM BRT.BRTTB001 WHERE DEC_17 = 1
DESCRIBE 15:57:20.18 STMT# 1900 SQLST:00000 SQLCO: 0
DELETE 15:57:20.18 0.020686 STMT# 1924 CURSOR: C1 ISO(CS) SQLST:00000 SQLCO: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS(N/A)
DESCRIBE 15:57:20.20 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.20 STMT# 5390 SQLST:00000 SQLCO: 0
PREPARE 15:57:20.20 0.000070 STMT# 1846 CURSOR: C1 SQLST:00000 SQLCO: 0
TEXT: COMMIT
DESCRIBE 15:57:20.21 STMT# 1900 SQLST:00000 SQLCO: 0
SYNC. 15:57:20.21 0.005373
DBRM DSN@EP2L
DESCRIBE 15:57:20.21 STMT# 1924 SQLST:00000 SQLCO: 0
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
DESCRIBE 15:57:20.21 STMT# 5388 SQLST:00000 SQLCO: 0
DESCRIBE 15:57:20.21 STMT# 5390 SQL Activity Trace Summarized by Occurrence, sorted by ELAPSEDTIME
This summary level sorts the SQL activity trace that belongs to the thread, by elapsed time. The section number field (SECT#) provides the section number of the Relational Data system Input parameter list (RDI).
This is an example of an SQL Activity Trace Summarized by Occurrence, sorted by ELAPSEDTIME.
LOCATION: PMODA21 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DA21 ACTUAL FROM: 03/02/15 15:57:19.88
DB2 VERSION: V12
SUMMARIZED BY OCCURRENCE, SORTED BY ELAPSEDTIME
PRIMAUTH: EDVA CONNECT : DB2CALL CORRNAME: EDVADML2 CONNTYPE: DB2CALL
ORIGAUTH: EDVA PLANNAME: DSNREXX CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : EDVA WSNAME : DB2CALL TRANSACT: EDVADML2
TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPSARA21.X'CEA11F533D97' ACE ADDRESS: X'1BE0DC00'
START TIME: 03/11/15 11:36:09.68 START ELAPSED: N/A START REASON: IN PROGRESS
STOP TIME : 03/11/15 11:36:09.68 STOP ELAPSED : N/A STOP REASON : END OF FILE
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DELETE 11:36:09.68 0.000286 STMT# 3747 ISO(CS) SQLST:01504 SQLCO: 0
SECT#: 1
REOPTIMIZED(NO) KEEP UPDATE LOCKS(N/A)
TEXT: DELETE FROM BRT.BRTTB001
PACKAGE: PMODA21.DSNREXX.DSNREXX.X'18B85F4A18B078EA'
INSERT 11:36:09.68 0.000083 STMT# 3747 ISO(CS) SQLST:00000 SQLCO: 0
SECT#: 1
REOPTIMIZED(NO) KEEP UPDATE LOCKS(N/A)
TEXT: INSERT INTO BRT.BRTTB001 ( DEC_17 , CHR_40 ) VALUES ( 20 , '0000000020'
)
SQL Activity Trace Summarized by Occurrence with All Workload
Summary by occurrence with all workload shows individual SQL statement occurrences. It also shows workload highlights, scan activity and minibind activity.
This is an example of an SQL Activity Trace Summarized by Occurrence with All Workload.
LOCATION: STLEC1 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: DSNCAT SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: V71A TO: NOT SPECIFIED
SUBSYSTEM: V71A ACTUAL FROM:01/30/15 17:31:25.34
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE, WITH ALL WORKLOAD
PRIMAUTH: ADMF001 CONNECT : BATCH CORRNAME: T1240109 CONNTYPE: TSO
ORIGAUTH: ADMF001 PLANNAME: DSNTEP3 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : 'BLANK' WSNAME : 'BLANK' TRANSACT: 'BLANK'
TRACE # 1.1 DB2 LUWID: USIBMSY.SYEC1DB2.X'B0B890589A02' ACE ADDRESS: X'05B38E08'
START TIME: 01/30/15 17:31:25.34 START ELAPSED: N/A START REASON: IN PROGRESS
STOP TIME : 01/30/15 17:31:27.66 STOP ELAPSED : N/A STOP REASON : END OF FILE
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
CREATE 17:31:25.88 0.559483 0.034879 TYPE: FUNCTION NAME: UF04F
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : N/P RECS/SORT: N/P I/O REQS: N/P SUSPENDS : N/P EXITS : N/P AMS : N/P
ROWSPROC: 26G WORK/SORT: N/P AET/I/O : N/P AET/SUSP : N/P AET/EXIT : N/P AET/AMS : N/P
PAGESCAN: 0 PASS/SORT: N/P DATACAPT: N/P RIDS UNUSED: N/P CHECKCON : N/P DEGREE REDUCTION : N/P
LOB_PAGSCAN: 0 LOB_UPD_PAGE : 0
--- 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
1 25770M 8589935K 0 16 25770M 0 0 0 0 0
V71A INDX
1 0 12885M 0 21475M 9 0 0 0 0 0
V71A SEQD
TOTAL 2 25770M 21475M 0 21475M 25770M 0 0 0 0 0
--- MINIBIND -------------------------------------------------------------------------------------------------------------
QUERYNO : 1358 PLANNAME : DSNTEP3 COST : 3302 PARALLELISM_DISABLED : N/A
QBLOCKNO : 1 COLLID : DSNTEP3 PROGNAME : DSNTEP3 CONSISTENCY_TOKEN : 16149E8E18DC45A4
APPLNAME : N/P WHEN_OPTIMIZE : 'BLANK' OPT_HINT_IDENT: MANFREDW OPTIMIZE_HINTS_USED : YES
UNITS : 0 MILLI_SEC : 0 COST_CATEGORY : N/P
BIND_TIME: 01/30/15 10:31:27.38 VERSION : N/P
..........................................................................................................................
PLANNO : 1 METHOD : FIRST TABLE ACCESSED SORTN_UNIQ : NO SORTC_UNIQ : NO
DATABASE : DSNDB04 NEXTSTEP : NOT APPLICABLE SORTN_JOIN : NO SORTC_JOIN : NO
OBJECT : 13 ACCESSTYPE: TABLE SPACE SCAN (R) SORTN_ORDERBY : NO SORTC_ORDERBY : NO
CREATOR : X PAGE_RANGE : NO SORTN_GROUPBY : NO SORTC_GROUPBY : NO
TNAME : TBUF0401 JOIN_TYPE : NO SORTN_PGROUP_ID : 0 SORTC_PGROUP_ID: 0
CORRELATION_NAME: N/P MERGE_JOIN_COLS : 0 ACCESS_DEGREE : 0 JOIN_DEGREE : 0
TSLOCKMODE : IS PARALLELISM_MODE: NO ACCESS_PGROUP_ID: 0 JOIN_PGROUP_ID : 0
PREFETCH : SEQ COLUMN_FN_EVAL : N/P PAGES_FOR_TABLE : 111 TAB_CARDINALITY: 1
DIRECT_ROW_ACC : NO STARJOIN : YES
..........................................................................................................................
ACCESS_CREATOR ACCESS_NAME MATCHCOLS INDEXONLY PREFETCH_INDEX OPERATION MIXOPSEQ
N/P N/P 16448 NO SEQUENTIAL INTERSECTION 16448
N/P N/P 16448 NO SEQUENTIAL INTERSECTION 16448SQL Activity Trace Summarized by Program
This summary level presents totals for each program name belonging to the thread.
This is an example of an SQL Activity Trace Summarized by Program.
LOCATION: DSNAPC3 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: GROUP_1 SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: MEMBER_1 TO: NOT SPECIFIED
SUBSYSTEM: APC3 ACTUAL FROM: 01/30/15 06:42:18.13
DB2 VERSION: V10
SUMMARIZED BY PROGRAM
PRIMAUTH: XXASP09 CONNECT : BATCH CORRNAME: XXASP09F CONNTYPE: TSO
ORIGAUTH: XXASP09 PLANNAME: LOCCURHL CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : 1234567890123456 WSNAME : 123456789012345678 TRANSACT: 12345678901234567890123456789012
TRACE # 1.7 DB2 LUWID: DEIBMIPS.IPVANE21.X'AD7F37CCED27' ACE ADDRESS: X'05A493B8'
START TIME: 01/30/15 06:42:18.13 START ELAPSED: 0.079205 START REASON: CREATE THREAD
STOP TIME : 01/30/15 06:55:33.00 STOP ELAPSED : 0.009735 STOP REASON : TERMINATE THREAD
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DSNESM68 1 1.923991 0.120291 PACKAGE: SYS1DSN2.DSNESPRR.DSNESM68.X'149EEA901A79FE48'
1.923991 0.120291 ACQUIRE(USE) REOPT RELEASE(COMMIT) ISO(RR) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
IMMEDWRITE(NO)
STMTTYPE COUNT AET/OCCUR TCB/OCCUR COMMITS: 2
LOCK 3 0.014405 0.011091
OPEN 1 0.000444 0.000271
PREPARE 4 0.470083 0.021687SQL Activity Trace Summarized by Statement Number
This summary level presents totals for each statement number belonging to the thread. The events are qualified by the package name.
From this level on, timestamps are not appropriate so the second column becomes a count of the occurrences of each event. SQL text is omitted.
By default, the package names are printed alphabetically and the statement numbers are sorted numerically within each package.
Note: Not every
statement can be summarized by statement number. DCL, for example,
has no statement number. An event name is chosen from the closest
possible level of summarization, which is the statement type GRANT
in this example.
This is an example of an SQL Activity Trace Summarized by Statement Number (STMTNO).
LOCATION: PMODA21 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-6
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DA21 ACTUAL FROM: 03/02/15 15:57:19.88
DB2 VERSION: V10
SUMMARIZED BY STMTNO
PRIMAUTH: EDVA CONNECT : BATCH CORRNAME: EDVADDL CONNTYPE: TSO
ORIGAUTH: EDVA PLANNAME: DSNTEP2 CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : EDVA WSNAME : BATCH TRANSACT: EDVADDL
TRACE # 1.1 DB2 LUWID: DEIBMIPS.IPSARA21.X'CE9608EA5959' ACE ADDRESS: X'1E4CEC60'
START TIME: 03/02/15 15:57:19.88 START ELAPSED: 0.005397 START REASON: CREATE THREAD
STOP TIME : 03/02/15 15:57:20.22 STOP ELAPSED : 0.002567 STOP REASON : TERMINATE THREAD
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DBRM DSN@EP2L
# 1924 3 DESCRIBE
PACKAGE PMODA21.DSNTEP2.DSN@EP2L.X'198749160FEF1D1B'
VAR1.PM96278
ACQUIRE(USE) REOPT(N) RELEASE(COMMIT) ISO(CS) DYNAMICRULES(RUN)
PREPARE(NODEFER) KEEPDYNAMIC(NO) PROTOCOL(DRDA) OPTHINT(N/P)
# 1415 1 DESCRIBE
# 1846 6 0.061955 PREPARE CURSOR: C1
0.010326
# 1900 6 DESCRIBE
# 1924 1 0.020686 DELETE CURSOR: C1 ISO(CS) REOPT(NO) KEEP UPD LOCKS: N/A
0.020686
# 1924 1 0.000521 INSERT ISO(CS) REOPT(NO) KEEP UPD LOCKS: N/A
0.000521
# 1952 1 0.000007 OPEN CURSOR: C1 ISO(CS) REOPT(NO) KEEP UPD LOCKS: NO
0.000007 SCROLL(NO) SENSITIVE(UNS) TABLE(UNS)
IMPLICIT COMMIT(NO )
# 1982 2 0.000083 FETCH CURSOR: C1 SENSITIVE(N/P) ORIENTATION(NEXT)
0.000042
# 2277 1 0.000005 CLOSE CURSOR: C1
0.000005 CLOSE TYPE(N/P)
# 5388 5 DESCRIBE
# 5390 5 DESCRIBESQL Activity Trace Summarized by Statement Type
This summary level presents totals for each statement type executed by the thread. By default, the events are sorted alphabetically. There is no further qualification at this level.
This is an example of an SQL Activity Trace Summarized by Statement Type.
LOCATION: DSNAPC3 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-1
GROUP: GROUP_1 SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: MEMBER_1 TO: NOT SPECIFIED
SUBSYSTEM: APC3 ACTUAL FROM: 01/30/15 06:42:18.13
DB2 VERSION: V10
SUMMARIZED BY STMTTYPE
PRIMAUTH: XXASP09 CONNECT : BATCH CORRNAME: XXASP09F CONNTYPE: TSO
ORIGAUTH: XXASP09 PLANNAME: LOCCURHL CORRNMBR: 'BLANK' THRDTYPE: ALLIED
ENDUSER : 1234567890123456 WSNAME : 123456789012345678 TRANSACT: 12345678901234567890123456789012
TRACE # 1.7 DB2 LUWID: DEIBMIPS.IPVANE21.X'AD7F37CCED27' ACE ADDRESS: X'05A493B8'
START TIME: 01/30/15 06:42:18.13 START ELAPSED: 0.079205 START REASON: CREATE THREAD
STOP TIME : 01/30/15 06:55:33.00 STOP ELAPSED : 0.009735 STOP REASON : TERMINATE THREAD
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
LOCK 3 0.043214 0.033272
0.014405 0.011091
OPEN 1 0.000444 0.000271
0.000444 0.000271
PREPARE 4 1.880333 0.086749
0.470083 0.021687
--- MINIBIND -------------------------------------------------------------------------------------------------------------
QUERYNO : 1383 PLANNAME : DSNTEP61 COST : 35 PARALLELISM_DISABLED : N/A
QBLOCKNO : 2 COLLID : DSNTEP61 PROGNAME : DSNTEP61 CONSISTENCY_TOKEN : 15769AE806DB8B8E
APPLNAME : N/P WHEN_OPTIMIZE : 'BLANK' OPT_HINT_IDENT: N/P OPTIMIZE_HINTS_USED : YES
UNITS : 12345 MILLI_SEC : 12345 COST_CATEGORY : N/P
BIND_TIME: 01/30/15 03:28:55.211328 VERSION: N/P
.........................................................................................................................
PLANNO : 1 METHOD : FIRST TABLE ACCESSED SORTN_UNIQ : NO SORTC_UNIQ : NO
DATABASE : DSNDB04 NEXTSTEP : NOT APPLICABLE SORTN_JOIN : NO SORTC_JOIN : NO
OBJECT : 21 ACCESSTYPE: TABLE SPACE SCAN (R) SORTN_ORDERBY : NO SORTC_ORDERBY : NO
CREATOR : X PAGE_RANGE : NO SORTN_GROUPBY : NO SORTC_GROUPBY : NO
TNAME : TBUF0401 JOIN_TYPE : STAR SORTN_PGROUP_ID : 0 SORTC_PGROUP_ID: 0
CORRELATION_NAME: N/P MERGE_JOIN_COLS : 0 ACCESS_DEGREE : 0 JOIN_DEGREE : 0
TSLOCKMODE : IS PARALLELISM_MODE: NO ACCESS_PGROUP_ID: 0 JOIN_PGROUP_ID : 0
ACCESS_NAME : N/A ACCESS_CREATOR : N/A MATCHCOLS : N/A PREFETCH : SEQ
OPERATION : N/A PREFETCH_INDEX : N/A MIXOPSEQ : N/A DIRECT_ROW_ACC : N/A
INDEXONLY : N/A COLUMN_FN_EVAL : N/A PAGES_FOR_TABLE : 12345 TAB_CARDINALITY: 123456789A
STARJOIN : NO