The objective of this is to create a formatted report in an EXPLAIN to assist is tuning various programs.
//REPORT1 EXEC QMF
//QMFINVOK.DSQPRINT DD SYSOUT=*
//QMFINVOK.SYSTSIN DD *
ISPSTART PGM(DSQQMFE) NEWAPPL +
PARM(M=B,S=DB2C,P=DSQBATCH,+
I=DBA.P_PLANTAB3(+
&&QUALPLANTAB_NF=ACCEPT,+
&&PROGNAME_LIKE='PIPBN22%',+
&&COLLID='ACCEPT'
//
//QMFINVOK.DSQPRINT DD SYSOUT=*
//QMFINVOK.SYSTSIN DD *
ISPSTART PGM(DSQQMFE) NEWAPPL +
PARM(M=B,S=DB2C,P=DSQBATCH,+
I=DBA.P_PLANTAB3(+
&&QUALPLANTAB_NF=ACCEPT,+
&&PROGNAME_LIKE='PIPBN22%',+
&&COLLID='ACCEPT'
//
where the PROC is...
//*
//* FUNCTION: INVOCATION OF QMF IN BATCH
//* CREATED : EDSOSB
//* ACTION : ADDED 90-03-19
//* LIBRARY : SYSPROC.WAB282PB.DPDBDC.PROCLIB
//* MODIFY : ADDED SYSPROC /TWGN
//* : IKJEFT1B /TWGN
//* 930902 : DSQPNLE ADDED /TWGN
//* 060527 : MODIFIED FOR QMF V7.1 /EDTLT
//* 080610 : MODIFIED FOR QMF V8.1 /EDSWEND
//*
//* WARNING : INFORM USERS WHEN ADDING OR CHANGING
//* : CONCATENATION ON DD CARDS TO PROC.
//*
//QMFPROC PROC WORK='1,1',CPUTIME=10
//QMFINVOK EXEC PGM=IKJEFT1B,TIME=&CPUTIME,
// DYNAMNBR=30,REGION=4M
//SYSPROC DD DSN=WAB125PT.QMF.SDSQCLTE,DISP=SHR
// DD DSN=SYS1.PROCMVS,DISP=SHR
//SYSEXEC DD DSN=WAB125PT.QMF.SDSQEXCE,DISP=SHR
//ISPPLIB DD DSN=SPF.PANELS,DISP=SHR
// DD DSN=WAB125PT.QMF.SDSQPLBE,DISP=SHR
//ISPMLIB DD DSN=SPF.MSGS,DISP=SHR
// DD DSN=WAB125PT.QMF.SDSQMLBE,DISP=SHR
//ISPSLIB DD DSN=SPF.SKELS,DISP=SHR
// DD DSN=WAB125PT.QMF.SDSQSLBE,DISP=SHR
//ISPTLIB DD DSN=SPF.TABLES,DISP=SHR
//ISPPROF DD UNIT=SYSDA,SPACE=(TRK,(9,1,4)),
// DCB=(LRECL=80,BLKSIZE=3120,RECFM=FB)
//SYSUDUMP DD SYSOUT=*
//ADMGGMAP DD DSN=WAB125PT.QMF.SDSQMAPE,DISP=SHR
//DSQPNLE DD DSN=WAB125PT.QMF.DSQPNLE,DISP=SHR
//DSQPRINT DD SYSOUT=*,
// DCB=(RECFM=VBA,LRECL=133,BLKSIZE=6233)
//DSQDEBUG DD SYSOUT=*,DCB=(RECFM=FBA,LRECL=121,BLKSIZE=1210)
//DSQUDUMP DD DUMMY
//DSQSPILL DD DSN=&SPILL,DISP=(NEW,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(&WORK),RLSE),
// DCB=(RECFM=F,LRECL=4096,BLKSIZE=4096)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD DUMMY
//* FUNCTION: INVOCATION OF QMF IN BATCH
//* CREATED : EDSOSB
//* ACTION : ADDED 90-03-19
//* LIBRARY : SYSPROC.WAB282PB.DPDBDC.PROCLIB
//* MODIFY : ADDED SYSPROC /TWGN
//* : IKJEFT1B /TWGN
//* 930902 : DSQPNLE ADDED /TWGN
//* 060527 : MODIFIED FOR QMF V7.1 /EDTLT
//* 080610 : MODIFIED FOR QMF V8.1 /EDSWEND
//*
//* WARNING : INFORM USERS WHEN ADDING OR CHANGING
//* : CONCATENATION ON DD CARDS TO PROC.
//*
//QMFPROC PROC WORK='1,1',CPUTIME=10
//QMFINVOK EXEC PGM=IKJEFT1B,TIME=&CPUTIME,
// DYNAMNBR=30,REGION=4M
//SYSPROC DD DSN=WAB125PT.QMF.SDSQCLTE,DISP=SHR
// DD DSN=SYS1.PROCMVS,DISP=SHR
//SYSEXEC DD DSN=WAB125PT.QMF.SDSQEXCE,DISP=SHR
//ISPPLIB DD DSN=SPF.PANELS,DISP=SHR
// DD DSN=WAB125PT.QMF.SDSQPLBE,DISP=SHR
//ISPMLIB DD DSN=SPF.MSGS,DISP=SHR
// DD DSN=WAB125PT.QMF.SDSQMLBE,DISP=SHR
//ISPSLIB DD DSN=SPF.SKELS,DISP=SHR
// DD DSN=WAB125PT.QMF.SDSQSLBE,DISP=SHR
//ISPTLIB DD DSN=SPF.TABLES,DISP=SHR
//ISPPROF DD UNIT=SYSDA,SPACE=(TRK,(9,1,4)),
// DCB=(LRECL=80,BLKSIZE=3120,RECFM=FB)
//SYSUDUMP DD SYSOUT=*
//ADMGGMAP DD DSN=WAB125PT.QMF.SDSQMAPE,DISP=SHR
//DSQPNLE DD DSN=WAB125PT.QMF.DSQPNLE,DISP=SHR
//DSQPRINT DD SYSOUT=*,
// DCB=(RECFM=VBA,LRECL=133,BLKSIZE=6233)
//DSQDEBUG DD SYSOUT=*,DCB=(RECFM=FBA,LRECL=121,BLKSIZE=1210)
//DSQUDUMP DD DUMMY
//DSQSPILL DD DSN=&SPILL,DISP=(NEW,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(&WORK),RLSE),
// DCB=(RECFM=F,LRECL=4096,BLKSIZE=4096)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD DUMMY
When it runs I get this output...for SYSTSPRT...
XSTECOA1 JOB12003 <QMFINVOK.REPORT1 .SYSTSPRT> Line 1 of 5
Command ===> Scroll ===> CSR
Current Find Text: Dataset 1 of 1
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+---->
1READY
ISPSTART PGM(DSQQMFE) NEWAPPL PARM(M=B,S=DB2C,P=DSQBATCH,I=DBA.P_PLANTAB3(&&QUA
D='ACCEPT'
ISPD118
The initially invoked module ended with a return code = 16
******************************** Bottom of Data ********************************
Command ===> Scroll ===> CSR
Current Find Text: Dataset 1 of 1
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+---->
1READY
ISPSTART PGM(DSQQMFE) NEWAPPL PARM(M=B,S=DB2C,P=DSQBATCH,I=DBA.P_PLANTAB3(&&QUA
D='ACCEPT'
ISPD118
The initially invoked module ended with a return code = 16
******************************** Bottom of Data ********************************
and this output... for DSQDEBUG...
XSTECOA1 JOB12003 <QMFINVOK.REPORT1 .DSQDEBUG> Line 1 of 53
Command ===> Scroll ===> CSR
Current Find Text: Dataset 1 of 1
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+---->
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.04 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID:
MESSAGE NUMBER: DSQ10344
MESSAGE TEXT:
The GDDM CCSID 351 does not match the CCSID of 278 at database DB2C.
&C1: 351
&C2: 278
&C3: QMF V8R1.0
&C4: DB2C
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.07 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
MESSAGE NUMBER: DSQ90558
MESSAGE TEXT:
Warning messages have been generated.
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.09 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
COMMAND TEXT: (Q.SYSTEM_INI)
RUN PROC Q.SYSTEM_INI
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
MESSAGE NUMBER: DSQ21127
MESSAGE TEXT: (Q.SYSTEM_INI)
OK, you may enter a command.
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
COMMAND TEXT:
RUN PROC DBA.P_PLANTAB3(&QUALPLANTAB_NF=ACCEPT,&PROGNAME_LIKE='
PIPBN22%',&COLLID='ACCEPT'
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
COMMAND TEXT:
RUN QUERY DBA.Q_PLANTAB3 (FORM=DBA.F_PLANTAB3)
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
MESSAGE NUMBER: DSQ23193
MESSAGE TEXT:
Please give a value for each variable name.
&C1: RUN
&C2: &QUALPLANTAB_NF
******************************** Bottom of Data ********************************
Command ===> Scroll ===> CSR
Current Find Text: Dataset 1 of 1
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+---->
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.04 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID:
MESSAGE NUMBER: DSQ10344
MESSAGE TEXT:
The GDDM CCSID 351 does not match the CCSID of 278 at database DB2C.
&C1: 351
&C2: 278
&C3: QMF V8R1.0
&C4: DB2C
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.07 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
MESSAGE NUMBER: DSQ90558
MESSAGE TEXT:
Warning messages have been generated.
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.09 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
COMMAND TEXT: (Q.SYSTEM_INI)
RUN PROC Q.SYSTEM_INI
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
MESSAGE NUMBER: DSQ21127
MESSAGE TEXT: (Q.SYSTEM_INI)
OK, you may enter a command.
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
COMMAND TEXT:
RUN PROC DBA.P_PLANTAB3(&QUALPLANTAB_NF=ACCEPT,&PROGNAME_LIKE='
PIPBN22%',&COLLID='ACCEPT'
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
COMMAND TEXT:
RUN QUERY DBA.Q_PLANTAB3 (FORM=DBA.F_PLANTAB3)
--------------------------------------------------------------------------------
0---------- ****** 11/06/01 10.31.10 ****** -----------------------------
-USERID: XSTECOA
AUTHORIZATION-ID: XSTECOA
MESSAGE NUMBER: DSQ23193
MESSAGE TEXT:
Please give a value for each variable name.
&C1: RUN
&C2: &QUALPLANTAB_NF
******************************** Bottom of Data ********************************
Here is the PROC ...
RUN QUERY DBA.Q_PLANTAB3 (FORM=DBA.F_PLANTAB3)
Here is the FORM ...
FORM.MAIN DBA.F_PLANTAB3
COLUMNS: Total Width of Report Columns: 124
NUM COLUMN HEADING USAGE INDENT WIDTH EDIT SEQ
--- ---------------------------------------- ------- ------ ----- ----- ---
1 QUERY_NR 1 6 L 1
2 QRY_BLK 1 3 L 2
3 APPLNAME OMIT 2 8 C 3
4 PROGNAME OMIT 2 8 C 4
5 QRY_BLK_STEG 1 4 L 5
6 M_E_T_H 1 2 L 6
7 CREATOR 1 6 C 7
8 TNAME 1 8 C 8
9 TABNO OMIT 1 6 L 9
10 ACC_TYP 1 3 C 10
11 M_C_O_L 1 2 L 11
12 ACCESSCREATOR OMIT 1 13 C 12
13 ACCESSNAME 1 8 C 13
14 I_X_O 1 1 C 14
15 S_-_N_U 1 1 C 15
16 S_-_N_J 1 1 C 16
17 S_-_N_O 1 1 C 17
18 S_-_N_G 1 1 C 18
19 S_-_C_U 1 1 C 19
20 S_-_C_J 1 1 C 20
21 S_-_C_O 1 1 C 21
22 S_-_C_G 1 1 C 22
23 TS_LOCK_MODE 1 4 C 23
24 REMARKS OMIT 1 254 C 24
25 PF 1 2 C 25
26 COL_FN_EVAL 1 4 C 26
27 M_IX_OP_SEQ 1 3 L 27
28 VERSION OMIT 1 10 C 47
29 COLLID OMIT 1 8 C 48
30 ACCESS_DEGREE OMIT 1 6 L 30
31 ACCESS_PGROUP_ID OMIT 1 6 L 31
32 JOIN_DEGREE OMIT 1 6 L 32
33 JOIN_PGROUP_ID OMIT 1 6 L 33
34 SORTC_PGROUP_ID OMIT 1 6 L 34
35 SORTN_PGROUP_ID OMIT 1 6 L 35
36 PARALLELISM_MODE OMIT 1 11 C 36
37 MERG_J_COL 1 4 L 37
38 CORRELATION_NAME 1 8 C 38
39 P_R_A_N_G 1 1 C 39
40 J_T_Y_P_E 1 1 C 40
41 GROUP_MEMBER OMIT 1 8 C 41
42 IBM_SERVICE_DATA OMIT 1 254 C 42
43 WHEN_OPTIMIZE OMIT 1 8 C 43
44 QBLOCK_TYPE 1 4 C 44
45 BIND_TIME OMIT 1 26 TSI 45
46 OPTHINT OMIT 1 8 C 46
47 HINT_USED 1 7 C 47
48 P_ACC_TP 1 1 C 48
49 PAR_BLK_NO 1 2 L 49
50 TAB_TP 1 1 C 50
*** END ***
PAGE: HEADING ===> &4 VERSION &28 COLLID &29 BINDTIME &45
FOOTING ===>
FINAL: TEXT ===>
BREAK1: NEW PAGE FOR BREAK? ===> NO
FOOTING ===>
BREAK2: NEW PAGE FOR BREAK? ===> NO
FOOTING ===>
OPTIONS: OUTLINE? ===> YES DEFAULT BREAK TEXT? ===> YES
COLUMNS: Total Width of Report Columns: 124
NUM COLUMN HEADING USAGE INDENT WIDTH EDIT SEQ
--- ---------------------------------------- ------- ------ ----- ----- ---
1 QUERY_NR 1 6 L 1
2 QRY_BLK 1 3 L 2
3 APPLNAME OMIT 2 8 C 3
4 PROGNAME OMIT 2 8 C 4
5 QRY_BLK_STEG 1 4 L 5
6 M_E_T_H 1 2 L 6
7 CREATOR 1 6 C 7
8 TNAME 1 8 C 8
9 TABNO OMIT 1 6 L 9
10 ACC_TYP 1 3 C 10
11 M_C_O_L 1 2 L 11
12 ACCESSCREATOR OMIT 1 13 C 12
13 ACCESSNAME 1 8 C 13
14 I_X_O 1 1 C 14
15 S_-_N_U 1 1 C 15
16 S_-_N_J 1 1 C 16
17 S_-_N_O 1 1 C 17
18 S_-_N_G 1 1 C 18
19 S_-_C_U 1 1 C 19
20 S_-_C_J 1 1 C 20
21 S_-_C_O 1 1 C 21
22 S_-_C_G 1 1 C 22
23 TS_LOCK_MODE 1 4 C 23
24 REMARKS OMIT 1 254 C 24
25 PF 1 2 C 25
26 COL_FN_EVAL 1 4 C 26
27 M_IX_OP_SEQ 1 3 L 27
28 VERSION OMIT 1 10 C 47
29 COLLID OMIT 1 8 C 48
30 ACCESS_DEGREE OMIT 1 6 L 30
31 ACCESS_PGROUP_ID OMIT 1 6 L 31
32 JOIN_DEGREE OMIT 1 6 L 32
33 JOIN_PGROUP_ID OMIT 1 6 L 33
34 SORTC_PGROUP_ID OMIT 1 6 L 34
35 SORTN_PGROUP_ID OMIT 1 6 L 35
36 PARALLELISM_MODE OMIT 1 11 C 36
37 MERG_J_COL 1 4 L 37
38 CORRELATION_NAME 1 8 C 38
39 P_R_A_N_G 1 1 C 39
40 J_T_Y_P_E 1 1 C 40
41 GROUP_MEMBER OMIT 1 8 C 41
42 IBM_SERVICE_DATA OMIT 1 254 C 42
43 WHEN_OPTIMIZE OMIT 1 8 C 43
44 QBLOCK_TYPE 1 4 C 44
45 BIND_TIME OMIT 1 26 TSI 45
46 OPTHINT OMIT 1 8 C 46
47 HINT_USED 1 7 C 47
48 P_ACC_TP 1 1 C 48
49 PAR_BLK_NO 1 2 L 49
50 TAB_TP 1 1 C 50
*** END ***
PAGE: HEADING ===> &4 VERSION &28 COLLID &29 BINDTIME &45
FOOTING ===>
FINAL: TEXT ===>
BREAK1: NEW PAGE FOR BREAK? ===> NO
FOOTING ===>
BREAK2: NEW PAGE FOR BREAK? ===> NO
FOOTING ===>
OPTIONS: OUTLINE? ===> YES DEFAULT BREAK TEXT? ===> YES
Here is the QUERY ...
SELECT QUERYNO
,QBLOCKNO
,APPLNAME
,PROGNAME
,PLANNO
,METHOD
,CREATOR
,TNAME
,TABNO
,ACCESSTYPE
,MATCHCOLS
,ACCESSCREATOR
,ACCESSNAME
,INDEXONLY
,SORTN_UNIQ
,SORTN_JOIN
,SORTN_ORDERBY
,SORTN_GROUPBY
,SORTC_UNIQ
,SORTC_JOIN
,SORTC_ORDERBY
,SORTC_GROUPBY
,TSLOCKMODE
,REMARKS
,PREFETCH
,COLUMN_FN_EVAL
,MIXOPSEQ
,VERSION
,COLLID
,ACCESS_DEGREE
,ACCESS_PGROUP_ID
,JOIN_DEGREE
,JOIN_PGROUP_ID
,SORTC_PGROUP_ID
,SORTN_PGROUP_ID
,PARALLELISM_MODE
,MERGE_JOIN_COLS
,CORRELATION_NAME
,PAGE_RANGE
,JOIN_TYPE
,GROUP_MEMBER
,IBM_SERVICE_DATA
,WHEN_OPTIMIZE
,QBLOCK_TYPE
,BIND_TIME
,OPTHINT
,HINT_USED
,PRIMARY_ACCESSTYPE
,PARENT_QBLOCKNO
,TABLE_TYPE
FROM &QUALPLANTAB_NF.PLAN_TABLE A
WHERE PROGNAME LIKE &PROGNAME_LIKE
AND COLLID = &COLLID
AND BIND_TIME = ( SELECT MAX(BIND_TIME)
FROM &QUALPLANTAB_NF.PLAN_TABLE B
WHERE B.PROGNAME = A.PROGNAME
AND B.COLLID = A.COLLID
)
ORDER BY PROGNAME
,QUERYNO
,QBLOCKNO DESC
,PLANNO
,MIXOPSEQ
WITH UR
;
,QBLOCKNO
,APPLNAME
,PROGNAME
,PLANNO
,METHOD
,CREATOR
,TNAME
,TABNO
,ACCESSTYPE
,MATCHCOLS
,ACCESSCREATOR
,ACCESSNAME
,INDEXONLY
,SORTN_UNIQ
,SORTN_JOIN
,SORTN_ORDERBY
,SORTN_GROUPBY
,SORTC_UNIQ
,SORTC_JOIN
,SORTC_ORDERBY
,SORTC_GROUPBY
,TSLOCKMODE
,REMARKS
,PREFETCH
,COLUMN_FN_EVAL
,MIXOPSEQ
,VERSION
,COLLID
,ACCESS_DEGREE
,ACCESS_PGROUP_ID
,JOIN_DEGREE
,JOIN_PGROUP_ID
,SORTC_PGROUP_ID
,SORTN_PGROUP_ID
,PARALLELISM_MODE
,MERGE_JOIN_COLS
,CORRELATION_NAME
,PAGE_RANGE
,JOIN_TYPE
,GROUP_MEMBER
,IBM_SERVICE_DATA
,WHEN_OPTIMIZE
,QBLOCK_TYPE
,BIND_TIME
,OPTHINT
,HINT_USED
,PRIMARY_ACCESSTYPE
,PARENT_QBLOCKNO
,TABLE_TYPE
FROM &QUALPLANTAB_NF.PLAN_TABLE A
WHERE PROGNAME LIKE &PROGNAME_LIKE
AND COLLID = &COLLID
AND BIND_TIME = ( SELECT MAX(BIND_TIME)
FROM &QUALPLANTAB_NF.PLAN_TABLE B
WHERE B.PROGNAME = A.PROGNAME
AND B.COLLID = A.COLLID
)
ORDER BY PROGNAME
,QUERYNO
,QBLOCKNO DESC
,PLANNO
,MIXOPSEQ
WITH UR
;
Can someone expert give me some advice on this ?
