QMFBATCH - How to run a QMF PROC in batch + n*parms
Posted: Wed Jun 01, 2011 2:30 pm
I am trying to run QMF in batch and having problems passing parameters.
The objective of this is to create a formatted report in an EXPLAIN to assist is tuning various programs.
where the PROC is...
When it runs I get this output...for SYSTSPRT...
and this output... for DSQDEBUG...
Here is the PROC ...
Here is the FORM ...
Here is the QUERY ...
Can someone expert give me some advice on this ?
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 ?