JCL to Execute QMF Procs from TSO 2

[ks01]

Hello ...

I have 4 stored procedures in QMF. Each linear procedure runs the following steps:

1. Sets global variables
2. Runs an SQL query
3. Displays the result set in a pre-defined QMF form

Two of the queries prompt the user to enter a date so I assume that my JCL will have to have a variable the user enters prior to submitting the job.

My objective is for the end-user to submit 1 JCL job and have the 4 different QMF procs executed and the result set exported/printed to a data set (or multiple data sets, whichever is easer).

I've seen other users do this, but I was unable to find any help at ibm.com. I assume this is due to my lack of experience with JCL and not enough knowledge of more advanced functions such as this (developers at my organization don’t usually like to share their knowledge with other non-programming staff).

If anyone could help or point me to some documentation that could, it would be much appreciated.

Thank you in advance ...

Kent

 

[MarcLodge]

Hi Kent,
You need a standard piece of ISPF/TSO jcl (have a look here:

http://www.intcom.com/overview/code_other/jcl1.html

if you've not got this - searching for QMF) and you need to set your tsin to something like:
ISPSTART PGM(DSQQMFE) +
NEWAPPL +
PARM(M=B,I=USERID.P_BQMF,S=DB2T)
where M=B means "mode is batch", I=USERID.P_BMQF is the initial startup procedure, and S=DB2T is the DB2 system to be used. There is a method by which you input the global variables which I think you name them preceding them with '&' followed by the values. I haven't done this in a very long while, so I'm afraid I'm a bit rusty on the variable bit. Have a go, and if you still get problems, get back to me.

Marc

 

[MarcLodge]

Kent,
I also have a very vague recollection of using a command from within QMF (possibly QMFBATCH?) to build a batch QMF JCL submittable job.
Marc

 

[ks01]

Marc ...

Hi!

I copied this from one of our developers, substituting my RACF ID (#KENT) with hers, and changing the output data set:
[tt]
//JOBCARD JOB (0331,0000,'KENT'),'KENT STOKES - OCE',
// CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//PROCS JCLLIB ORDER=(#KENT.JCL.LIB)
//STEP00 EXEC PGM=IEFBR14
//DEL1 DD DSN=#KENT.JOBCARD.OUTPUT,
// DISP=(MOD,DELETE,DELETE),
// SPACE=(CYL,(0))
//STEP01 EXEC QMFBATCH
//*DSQPRINT DD SYSOUT=0,DEST=N4R151,HOLD=YES,
//* DCB=(RECFM=FBA,LRECL=133,BLKSIZE=1330)
//DSQPRINT DD DSN=#KENT.JOBCARD.OUTPUT,
// DISP=(NEW,CATLG,DELETE),
// UNIT=RESDA,
// SPACE=(CYL,(60,15),RLSE),
// BLKSIZE=26600
//DSQDEBUG DD SYSOUT=*,
// DCB=(RECFM=FBA,LRECL=121,BLKSIZE=1210)
//SYSTSIN DD *
//*DSQPRINT DD SYSOUT=0,DEST=N4R151,HOLD=YES,
//* DCB=(RECFM=FBA,LRECL=133,BLKSIZE=1330)
//DSQPRINT DD DSN=#KENT.JOBCARD.OUTPUT,
// DISP=(NEW,CATLG,DELETE),
// UNIT=RESDA,
// SPACE=(CYL,(60,15),RLSE),
// BLKSIZE=26600
//DSQDEBUG DD SYSOUT=*,
// DCB=(RECFM=FBA,LRECL=121,BLKSIZE=1210)
//SYSTSIN DD *
EXECUTIL SEARCHDD(YES)
ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE)
PARM(M=B,S=DBP1,P=QMF311 +
I=RCX1.PSTA315T(&C1='2003-04-21'))
//* [/tt]

It’s funny you mentioned the QMFBATCH because when I copied the developers JCL, I noticed the EXEC PGM=QMFBATCH so I copied this from her library too:
[tt]
//QMFBATCH PROC
//*
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=25,REGION=8M
//*
//*STEPLIB DD DISP=SHR,DSN=DBP1.USER.DSNEXIT
//STEPLIB DD DISP=SHR,DSN=SYS1.DB2.SDSNEXIT
// DD DISP=SHR,DSN=SYS1.DB2.SDSNLOAD
// DD DISP=SHR,DSN=DBP1.SDSQLOAD
// DD DISP=SHR,DSN=VLM.C1.PRODLOAD
//SYSPROC DD DSN=VLM.C1.PRODCLST,DISP=SHR
//SYSEXEC DD DSN=VLM.C1.PRODREXX,DISP=SHR
// DD DSN=DBP1.SDSQEXCE,DISP=SHR
//ISPPLIB DD DSN=DBP1.SDSQPLBE,DISP=SHR
// DD DSN=SYS1.ISP.SISPPENU,DISP=SHR
//ISPMLIB DD DSN=DBP1.SDSQMLBE,DISP=SHR
// DD DSN=SYS1.ISP.SISPMENU,DISP=SHR
//ISPSLIB DD DSN=DBP1.SDSQSLBE,DISP=SHR
// DD DSN=SYS1.ISP.SISPSENU,DISP=SHR
//ISPTLIB DD DSN=SYS1.ISP.SISPTENU,DISP=SHR
//ISPPROF DD UNIT=SYSDA,SPACE=(TRK,(9,1,4)),
// DCB=(LRECL=80,BLKSIZE=3120,RECFM=FB)
//ADMGGMAP DD DSN=DBP1.DSQMAPE,DISP=SHR
//SYSUDUMP DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//DSNDUM DD DUMMY
//DSQPRINT DD SYSOUT=*,
// DCB=(RECFM=FBA,LRECL=133,BLKSIZE=23408)
//DSQDEBUG DD SYSOUT=*,
// DCB=(RECFM=FBA,LRECL=121,BLKSIZE=23474)
//DSQUDUMP DD SYSOUT=*,
// DCB=(RECFM=VBA,LRECL=125,BLKSIZE=23375)
//DSQSPILL DD UNIT=SYSDA,SPACE=(CYL,(50,50),RLSE),
// DCB=(RECFM=F,LRECL=4096,BLKSIZE=4096).
[/tt]
Now I looked at the JCL at the link you provided, but there is so much there that it totally blows me away (sorry, I really don't know that much about JCL).

As for the PARM variable (not sure if it's referred to as a variable), I have:
[tt]
M=B,
S=DBP1,
P=QMF311,
I=RCX1.PSTA315T
&C1='2003-04-21'[/tt]

I assume the plus sign (+) at the end of each line tells the JCL that the variable continues on the next line? From your explanation, I understand that M represents batch mode and S is the DB2 subsystem. However the JCL I copied has a P= which I don't know what that represents. The name of the first QMF proc is RCX1.PSTA315T which contains a date variable which prompts the user, &RPT_DT. The reason I used &C1 was because when the job failed (condition code 0016) the error log shows:
[tt]
USERID: #KENT
AUTHORIZATION-ID: #KENT
MESSAGE NUMBER: DSQ20933
MESSAGE TEXT:
You cannot use = with RUN.
&C1: RUN
&C2: = [/tt]

My QMF proc is pretty simple, its:
[tt]
RUN RCX1.SETGLBL
RUN RCX1.QSTA315D (F RCX1.FSTA315D[/tt]

Where RCX1.SETGLBL:
[tt]
SET PROFILE (CA=UPPER,CO=YES,D=PERIOD,LA=SQL,LE=60,P=' ',W=132)
SAVE PROFILE
SET GLOBAL (A=(') PERCENT_SIGN=%)[/tt]

And RCX1.QSTA315D:
[tt]
SELECT DISTINCT
C.REQST_TYP_DESC,
A.USER_NM,
B.USER_ID,
B.REQST_ID,
D.REQST_RSN_DESC,
B.CMNT,
DATE(B.CHG_DT) AS CHG_DT
FROM RCX1.VUSER_SECRY_PROF AS A,
RCX1.VUSER_SECRY_MAINTC AS B,
RCX1.VUSER_MAINTC_TYP AS C,
RCX1.VUSER_MAINTC_RSN AS D
WHERE A.USER_ID = B.USER_ID
AND B.REQST_TYP = C.REQST_TYP
AND B.REQST_RSN = D.REQST_RSN
AND DATE(B.CHG_DT) = &A&RPT_DT&A
ORDER BY 1, 2; [/tt]

Sorry for the long message, I greatly appreciate your help!

Kent

 

[MarcLodge]

Kent,
The + sign is in fact TSO's method of taking a command over a line. Your SYSTSIN statement and the following commands are in fact TSO commands that you are using in batch.

I can't remeber what the P=QMF311 parameter does, but I suspect we can leave it in as it doesn't appear to be causing any trouble. If you have a QMF developer Application manaul or similar, it should say in there.

The first thing that strikes me is that you are passing to the proc a variable called C1, but the proc isn't using it. The variable is used further down the chain and called &RPT_DT. I would imagine that in the start up proc (RCX1.PSTA315T) you are going to need a SET GLOBAL command that sets &RPT_DT = &C1. You may have to fiddle in order to get it to work correctly.

By the way, does the proc/query run ok from within native QMF?

Apologies for not being a little clearer, but it is quite some time since I've dabbled in this area. Another by the way, don't be shy of clicking on the 'mark this' button if you want to ;-)

Marc

 

[ks01]

Marc ...

Hi!

Thanks for the feedback. I'm pretty new here, I wasn't aware of the 'Mark this post as a helpful/expert post!' link. You definitely qualify there!

OK, I stayed up late last night trying to figure this out. I do have a copy of the Developing QMF Applications V6 manual, but all I was able to find was information regarding application use. Since I'm not a programmer, it was a little foreign to me. I did try looking in the Installing & Managing QMF on OS/390 V6 manual and found a little more help. I also found the use of the QMF line command "BATCH" (I think you mentioned this earlier). I still haven't figured out how to use it yet because as soon as it submits the job, I get a condition code of 0016; the reason for the failure is "TIME PARAMETER NOT ALLOWED ON CLASS A JOBS". However, I don't see anywhere where I can modify the JCL prior to submission.

To answer your question, does the query run OK from within native QMF, yes, it does. I also tried to troubleshoot by removing the &RPT_DT variable from the query and specifying an actual date (i.e. '2003-04-23'). I then saved the query, removed the &C1 variable from the JCL and re-ran it. The job completed with a condition code of 0000 and I verified it created the data set with the information I want. Success!

So the job seems to be working OK, it's just I'm doing something wrong with the user variable. Now you referred to the RCX1.PSTA315T as a “start up” proc. Is a start up proc any different that a regular stored procedure? I thought that the user variable &RPT_DT may be causing a problem perhaps because the job didn’t like the underbar, so I changed the user variable to &RPTDT in the query, modified it in the JCL and re-ran it. It failed again. What do you think?

Thank you for your patience in helping me ...

Kent

 

[MarcLodge]

Kent,
as I said, it's quite a while since I did anything in this arena, so the relevant brain cells are VERY dusty. But.....

The user variable that you pass from the batch JCL must tie up with that on the QMF side of the wall. At the moment, you appear to be calling it C1. All well and good, but I can't see any link up between the TSO C1 variable (one side of the wall) and the QMF RPT_DT variable (other side of the wall). These have got to balance/link, or you're not going to get the variable passed through.

The start up proc, is just a term that I (and others) have used to determine a proc that (unsurprisingly!) is used at startup to QMF. There is no difference in the actual proc.

Is it possible to add to the RCX1.PSTA315T proc a 1st line that says SET GLOBAL (RPT_DT= &C1) or something similar? You may have to mess about with this (in fact, I'm sure you will!) to get it to work. This will link the C! variable in the JCL with the QMF variable.

Let me know how you get on (thanks for the star!)

Marc

 

[ks01]

Marc ...

Hi!

OK, I tried your suggestion regarding adding the [tt]SET GLOBAL (RPT_DT= &C1[/tt] to my proc, but the job still fails with a condition code 0016. Here are the errors I get:

[tt]READY
EXECUTIL SEARCHDD(YES)
READY
ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1, I=RCX1.PSTA315T(&C1='2003-04-01'))
ISPD118
THE INITIALLY INVOKED MODULE ENDED WITH A RETURN CODE = 16
READY
END
------------------------------------------------------------
---------- ****** 03/04/25 22.53.43 ****** ----------
USERID: #KENT
AUTHORIZATION-ID: #KENT
MESSAGE NUMBER: DSQ90556
MESSAGE TEXT:
OK, YOU MAY ENTER A COMMAND.
------------------------------------------------------------
---------- ****** 03/04/25 22.53.43 ****** ----------
USERID: #KENT
AUTHORIZATION-ID: #KENT
COMMAND TEXT: (Q.SYSTEM_INI)
RUN PROC Q.SYSTEM_INI
------------------------------------------------------------
---------- ****** 03/04/25 22.53.43 ****** ----------
USERID: #KENT
AUTHORIZATION-ID: #KENT
MESSAGE NUMBER: DSQ21127
MESSAGE TEXT: (Q.SYSTEM_INI)
OK, YOU MAY ENTER A COMMAND.
------------------------------------------------------------
---------- ****** 03/04/25 22.53.43 ****** ----------
USERID: #KENT
AUTHORIZATION-ID: #KENT
COMMAND TEXT:
RUN PROC RCX1.PSTA315T(='2003-04-01')
------------------------------------------------------------
---------- ****** 03/04/25 22.53.44 ****** ----------
USERID: #KENT
AUTHORIZATION-ID: #KENT
MESSAGE NUMBER: DSQ20933
MESSAGE TEXT:
YOU CANNOT USE = WITH RUN.
&C1: RUN
&C2: =[/tt]

Now the only reason why I specified &C1 in my JCL as a variable was because I saw it here in the DSQDEBUG. I originally had the same variable in the JCL as in my SQL [tt](&RPT_DT)[/tt].

From looking at this, it looks as if it's having a problem executing [tt]RUN PROC RCX1.PSTA315T(='2003-04-01')[/tt]. For some reason it doesn't seem to understand the variable name. Any ideas?

Thanks again ...

Kent

 

[MarcLodge]

Kent,
This is a real leap in the dark, but try putting &&c1 in the JCL.
Marc

 

[chandoba]

Hi Marc and Kent,

I had a very similar bugging problem with QMF Batch, and luckily hit upon your discussion (via Ask Jeeves!).

From your discussion, I was able to identify my problem finally. But not sure where to go from here.

In brief, I am trying to copy data from one table (CSDMT01C) to another table (CSDMT010). Both tables have the same format. This proc runs fine in native QMF.

Part of the proc E06876.CSDMPR01(stored in QMF): (assume there's no space between & and COPY; displaying © symbol when previewed for this forum; B-))

-- INSERT ALL ROWS FROM MAINT. COPY INTO RELEASE VERSION --
IMPORT QUERY FROM 'E06876.SHIPPED.QUERY' (MEMBER=CSDMQRIN          
RUN QUERY  (&&TABLE=E06876.CSDMT010,
+           && COPY=E06876.CSDMT01C )

The query (CSDMQRIN) which is run by the above proc:

INSERT   INTO &TABLE
SELECT * FROM & COPY

Control code that I am using to run the QMF in batch mode:

PROFILE PREFIX(E06876)
ISPSTART PGM(DSQQMFE) +
PARM(S=DSNZ,M=B,I=E06876.CSDMPR01) NEWAPPL(DSQE)

I am getting the following error, ofcourse:
ISPD118
The initially invoked module ended with a return code = 16

Any help? :-D

Thanks,
Chandrasekhar

 

[MarcLodge]

Not giving you a lot of diagnostic info is it!

I'd be inclined to save the CSDMQRIN query in QMF and then change the proc to execute it directly, if this is possible.

Marc

 

[chandoba]

Marc,

Here's more diagnostic info. if that helps:

********************************* TOP OF DATA ***********
---------------------------------------------------------
----------      ****** 03/06/16  17.36.42 ******  -------
USERID: E06876                                           
AUTHORIZATION-ID:                                        
MESSAGE NUMBER: DSQ10283                                 
MESSAGE TEXT:                                            
DSQ10283 Cannot use database; RC = 84806000    00000004. 
******************************** BOTTOM OF DATA *********

Puzzles me why my auth. id is not showing up. Any help? Btw, I am also the DBA with the same id i.e, E06876.

Meanwhile, let me give a try on your suggestion (storing the query in database), though this not how the app. ran on some other mvs system.

-Chandrasekhar

 

[MarcLodge]

The auth Id not showing is a little odd and makes me wonder whether you are getting a good connection to QMF.

If I was in your shoes, I would probably save a query that ran an implicit select from a table of your own, and then create a proc to run it, and feed that proc in through batch QMF. If I've not explained that clearly:
Query:
SELECT * E06876.CSDMT010
(save query as Q_BATCHTEST)

Proc:
RUN QUERY E06876.Q_BATCHTEST
(save proc as P_PROCTEST)

Batch Parm:
PROFILE PREFIX(E06876)
ISPSTART PGM(DSQQMFE) +
PARM(S=DSNZ,M=B,I=E06876.P_PROCTEST) NEWAPPL(DSQE)

On running this, you should be able to determine whether you have a good connection to QMF in batch. If you haven't then your problem does not lie in the stuff that you have already coded but is external to it.

Have a go, see what happens and get back to me. If you've got problems, it may be something to do with RACF groups and batch QMF, and you may possibly have to code a SET CURRENT SQLID statement.

Marc

 

[chandoba]

Marc,

You are right there! I have tested the proc and query. It failed.. The reason being an imp. CAF interface DSNALI (connects QMF and DB2) is missing.

Our sysadmin/sys programmer should now be alarmed now.. to resolve this.

-Chandrasekhar

 

[MarcLodge]

Good news. Thought it was a bit odd. If you think I've been of help, feel free to mark the post as such!

 

[chandoba]

Problems are like balloons. They look big but weigh less and can be pricked easily.

I have included a db2 load lib called
'DSN710.SDSNLOAD'(missing earlier) in my jcl proc and the entire QMF batch has run fine.

Thanks Marc for your help. You helped me think in a proper direction.

-Chandrasekhar