Creating an SQL Activity trace
Use your ISPF editor to create a JCL job that creates an SQL Activity trace.
The following JCL is an example. Remember to change the setup
information (starting with Figure 1. SQL
Activity job stream using ISPF/PDF editor
// in your JCL) as appropriate
for your installation.
EDIT ---- ANDREW.OMPE.JOBS.SQLTRC--------------------- Columns 001 072
Command ===> ________________________________________________ Scroll ===> CSR_
****** ***************************** TOP OF DATA ******************************
0001 /* JCL to produce an SQL activity trace */
0002 //AWOMPEA JOB (TTS1,YUS7),'ANDREW',
0003 // MSGCLASS=V,CLASS=D,NOTIFY=ANDREW
0004 // EXEC PGM=FPECMAIN
0005 //STEPLIB DD DSN=OMPE.RKANMOD,DISP=SHR
0006 //INPUTDD DD DSN=ANDREW.OMPE.TRACES.TRACE2,DISP=SHR
0007 //DPMOUTDD DD DSN=ANDREW.OMPE.OUT.SQLT01,DISP=OVR
0008 //JOBSUMDD DD SYSOUT=A
0009 //SYSIN DD *
0010 SQLACTIVITY
0011 TRACE
0012 SUMMARIZEBY (OCCURRENCE)
0013 WORKLOAD (SORT)
0014 EXEC
****** **************************** BOTTOM OF DATA ****************************
F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up
F8=Down F9=Swap F10=Left F11=Right F12=Cancel
To submit the job, type SUBMIT on the command
line and press Enter.
SQL Activity trace, summarized by occurrence
Here is an example of an SQL Activity trace, summarized by occurrence.
LOCATION: DH1G OMEGAMON for DB2 Performance Expert (V5R5M0) PAGE: 1-1
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DH1G ACTUAL FROM: 09/27/15 20:16:13.13
DB2 VERSION: V10
SUMMARIZED BY OCCURRENCE, WITH SORT WORKLOAD
PRIMAUTH: DB2PE CONNECT : IMSA CORRNAME: PYPIB CONNTYPE: IMS-MPP
ORIGAUTH: LTA0244 PLANNAME: PMOMDEV CORRNMBR: 0052 THRDTYPE: ALLIED
ENDUSER : 'BLANK' WSNAME : 'BLANK' TRANSACT: 'BLANK'
TRACE # 1.1 DB2 LUWID: DH1G.DH1G.X'B575F4B56662' ACE ADDRESS: X'1148E8F8'
CICS LUWID: CICS1.CICS1.X'006C264C006C'
START TIME: 09/27/15 20:16:13.13 START ELAPSED: 0.000017 START REASON: NEW USER
STOP TIME : 09/27/15 20:16:13.23 STOP ELAPSED : 0.000014 STOP REASON : NEW USER
NL EVENT TIMESTAMP ELAP.TIME TCB TIME DETAIL
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
DBRM PYPIA
SELECT 20:16:13.13 0.000162 STMT# 408 ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: N/A
OPEN 20:16:13.13 0.000016 STMT# 416 CURSOR: CUR_CUSTNAME ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: NO
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 2 RECS/SORT: 1.00 I/O REQS: 1 SUSPENDS : N/P EXITS : N/P AMS : N/P
ROWSPROC: 2 WORK/SORT: 1.00 AET/I/O : 0.000005 AET/SUSP : N/P AET/EXIT : N/P AET/AMS : N/P
PAGESCAN: 30 PASS/SORT: 0.00 DATACAPT: N/P RIDS UNUSED: N/P CHECKCON : N/P DEGREE REDUCTION : N/P
LOB_PAGSCAN: 0 LOB_UPD_PAGE : 0
--- SORT ACTIVITY ------------------------------------------------------------------------------------------
MEMBER : N/P WORKFILES : 1.00 RECORDS : 1.00 MAX REQUESTED : N/P
TOTAL SORTS : 1 INITIAL WORKFILES : 1.00 RECORD SIZE : 29.00 AVG REQUESTED : N/P
SORT KEYS : 1.00 WORKFILES PARTITIONED : 0.00 KEY SIZE : 8.00 MAX NOT ACQUIRED: N/P
SORT COLUMNS: 3.00 PARTITIONING : NO DATA SIZE : 21.00 AVG NOT ACQUIRED: N/P
AET/SORT : 0.000014 PARTITIONING & SORTING: NO ROWS DELETED: 0.00 MAX RETURN CODE : 0
SORT TYPE : ESA PARTITION TYPE : NONE MERGE PASSES: 0.00
FETCH 20:16:13.13 0.000065 STMT# 425 CURSOR: CUR_CUSTNAME SQLSTATE: 00000 SQLCODE: 0
UPDATE 20:16:13.15 0.000130 STMT# 483 CURSOR: CUR_CUSTOMER ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: N/A
OPEN 20:16:13.15 0.000015 STMT# 493 CURSOR: CUR_WAREHOUSE ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: NO
--- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
SCANS : 2 RECS/SORT: 1.00 I/O REQS: 1 SUSPENDS : N/P EXITS : N/P AMS : N/P
ROWSPROC: 2 WORK/SORT: 1.00 AET/I/O : 0.000006 AET/SUSP : N/P AET/EXIT : N/P AET/AMS : N/P
PAGESCAN: 30 PASS/SORT: 0.00 DATACAPT: N/P RIDS UNUSED: N/P CHECKCON : N/P DEGREE REDUCTION : N/P
LOB_PAGSCAN: 0 LOB_UPD_PAGE : 0
--- SORT ACTIVITY ------------------------------------------------------------------------------------------
MEMBER : N/P WORKFILES : 1.00 RECORDS : 1.00 MAX REQUESTED : N/P
TOTAL SORTS : 1 INITIAL WORKFILES : 1.00 RECORD SIZE : 15.00 AVG REQUESTED : N/P
SORT KEYS : 1.00 WORKFILES PARTITIONED : 0.00 KEY SIZE : 10.00 MAX NOT ACQUIRED: N/P
SORT COLUMNS: 2.00 PARTITIONING : NO DATA SIZE : 12.00 AVG NOT ACQUIRED: N/P
AET/SORT : 0.000012 PARTITIONING & SORTING: NO ROWS DELETED: 0.00 MAX RETURN CODE : 0
SORT TYPE : ESA PARTITION TYPE : NONE MERGE PASSES: 0.00
FETCH 20:16:13.15 0.000094 STMT# 497 CURSOR: CUR_WAREHOUSE SQLSTATE: 00000 SQLCODE: 0
UPDATE 20:16:13.15 0.000071 STMT# 507 CURSOR: CUR_WAREHOUSE ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: N/A
INSERT 20:16:13.15 0.000096 STMT# 544 ISO(CS) SQLSTATE: 00000 SQLCODE: 0
REOPTIMIZED(NO) KEEP UPDATE LOCKS: N/A
COMMIT PHASE 1 20:16:13.16 0.004708
COMMIT PHASE 2 20:16:13.16 0.006613
LOCATION: DH1G OMEGAMON for DB2 Performance Expert (V5R5M0) PAGE: 1-3
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DH1G ACTUAL FROM: 09/27/15 20:16:13.13
DB2 VERSION: V10
SUMMARIZED BY THREAD
PRIMAUTH: DB2PE CONNECT : IMSA CORRNAME: PYPIB CONNTYPE: IMS-MPP
ORIGAUTH: LTA0244 PLANNAME: PMOMDEV CORRNMBR: 0052 THRDTYPE: ALLIED
ENDUSER : 'BLANK' WSNAME : 'BLANK' TRANSACT: 'BLANK'
TRACE # 1.1 DB2 LUWID: DH1G.DH1G.X'B575F4B56662' ACE ADDRESS: X'1148E8F8'
CICS LUWID: CICS1.CICS1.X'006C264C006C'
START TIME: 09/27/15 20:16:13.13 START ELAPSED: 0.000017 START REASON: NEW USER
STOP TIME : 09/27/15 20:16:13.23 STOP ELAPSED : 0.000014 STOP REASON : NEW USER
EVENT COUNT TOT.ELAPS TOTAL TCB DETAIL
AET/EVENT TCB/EVENT
-------------------- ----------- ----------- --------- -----------------------------------------------------------------------------
X'B575F4B56662' 1 0.011792 STMTTYPE COUNT AET/OCCUR TCB/OCCUR COMMITS: 1
0.011792 FETCH 2 0.000080
INSERT 1 0.000096
OPEN 2 0.000015
SELECT 1 0.000162
UPDATE 2 0.000100
LOCATION: DH1G OMEGAMON for DB2 Performance Expert (V5R5M0) PAGE: 0-1
GROUP: N/P SQL ACTIVITY - TRACE REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DH1G ACTUAL FROM: 09/27/15 20:16:13.13
DB2 VERSION: V10 TO: 09/27/15 20:16:13.40
INDEX
SQL TRACE # 1
REQUESTER ACE TRACE STARTS
TRACE START CONNECT CORRNAME CORRNMBR PRIMAUTH ORIGAUTH PLANNAME SERVER INSTANCE ADDRESS NO PAGE NO
-------------------- -------- -------- -------- -------- -------- -------- ------------------ ------------ -------- -------- -------
09/27/15 20:16:13.13 IMSA PYPIB 0052 DB2PE LTA0244 PMOMDEV DH1G B575F4B56662 1148E8F8 1.1 1-1
SQL ACTIVITY TRACE COMPLETE