SQL 活動追蹤範例
下列範例顯示在所有層次彙總的「SQL 活動」追蹤摘錄。
它們由下列指令產生:
⋮
SQLACTIVITY
TRACE
SUMMARIZEBY (ALL)
⋮
依全部彙總的 SQL 活動追蹤
此摘要層次顯示屬於由套件名稱限定之執行緒的每一個游標名稱總計。 依預設,套件名稱及每一個套件內的事件會按字母順序排序。
這是「依全部彙總 SQL 活動追蹤」的範例
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
依出現次數彙總的 SQL 活動追蹤
依出現項目的摘要顯示個別 SQL 陳述式出現項目。 在此執行緒中, SQL 陳述式屬於一個套件,其名稱會列印在其工作的頭。 當存在時, SQL 文字和 DDF 資訊會內嵌在事件中。 確定顯示為獨立式事件。 依預設,事件會依時間戳記順序排序。
這是「依出現次數彙總 SQL 活動追蹤」的範例。
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 活動追蹤
此摘要層次呈現屬於執行緒之每一個程式名稱的總計。
這是「依出現次數彙總 SQL 活動追蹤」的範例。
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 活動追蹤,依 ELAPSEDTIME 排序
這個摘要層次會依經歷時間來排序屬於執行緒的 SQL 活動追蹤。 區段編號欄位 (SECT#) 提供「關聯式資料系統輸入」參數清單 (RDI) 的區段編號。
這是「SQL 活動追蹤」的範例「依出現項目彙總」,依 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 活動追蹤
依出現項目列出所有工作量的摘要會顯示個別 SQL 陳述式出現項目。 它也會顯示工作量強調顯示、掃描活動及 minibind 活動。
這是「SQL 活動追蹤」的範例,依「所有工作量的發生」彙總。
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 16448
依程式彙總的 SQL 活動追蹤
此摘要層次呈現屬於執行緒之每一個程式名稱的總計。
這是「依程式彙總 SQL 活動追蹤」的範例。
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.021687
依陳述式號碼彙總的 SQL 活動追蹤
此摘要層次呈現屬於執行緒之每一個陳述式號碼的總計。 事件由套件名稱限定。
從這個層次開始,時間戳記並不適當,因此第二個直欄會變成每一個事件的出現次數計數。 省略 SQL 文字。
依預設,會按字母順序列印套件名稱,並在每一個套件內以數字方式排序陳述式號碼。
這是依陳述式號碼 (STMTNO) 彙總的 SQL 活動追蹤範例。
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 DESCRIBE
依陳述式類型彙總的 SQL 活動追蹤
此摘要層次呈現執行緒所執行之每一個陳述式類型的總計。 依預設,事件會按字母順序排序。 在此層次沒有進一步的資格。
這是「依陳述式類型彙總 SQL 活動追蹤」的範例。
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