Passing JCL Parm to QMF

[ks01]

Hi everyone ... I hope I am posting to the correct forum. Please let me know if not.

I am running QMF V8R1 on an z/OS platform running DB2. I am trying to pass a JCL parm to QMF using QMF batch. In my SQL, I have an IN operator like the following:

WHERE VERS_NUM IN (&VERS_NUM)

I am trying to set up my JCL to be able to send the variable correctly, I've only done this before with a basic equal operator before. I would like it to be like:

EXECUTIL SEARCHDD(YES)                                
   ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1,+
   I=USER.QUERYNAME(&&VERS_NUM=7777,7778)

So when QMF goes to run it, it would submit as:

WHERE VERS_NUM IN (7777,7778)

But this does not seem to work. Anyone have any suggestions?

Thank you in advance!

 

[MarcLodge]

Hi Kent,
Have you tried putting the variable in quotes:

EXECUTIL SEARCHDD(YES)                                
   ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1,+
   I=USER.QUERYNAME(&&VERS_NUM='7777,7778')

Marc

 

[ks01]

Hi Marc ...

Thanks for the response.

When I try to manually run the proc via the QMF panels, this is what I get:

+-----------------------------------------------------------------------------+
|                  RUN Command Prompt - Values of Variables                   |
|                                                                             |
| Your RUN command runs a query or procedure with variables that need         |
| values. Fill in a value for each variable named below:                      |
|                                                              1  to 10 of 10 |
| &VERS_NUM          '7777,7778'                                              |
|                                                                             |
|                                                                             |
|                                                                             |
|                                                                             |
|                                                                             |
|                                                                             |
|                                                                             |
|                                                                             |
|                                                                             |
+-----------------------------------------------------------------------------+
| F1=Help  F3=End  F7=Backward  F8=Forward                                    |
+-----------------------------------------------------------------------------+



QUERY MESSAGES:                                                        
Data of different types must not be used in an expression or condition.

When I run it in batch, the sysout shows:

READY                                                                                        
  EXECUTIL SEARCHDD(YES)                                                                     
READY                                                                                        
   ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1,I=RCX1.PCX130V(&&VERS_NUM='7777,7778')
ISPD118                                                                                      
The initially invoked module ended with a return code = 16                                   
READY                                                                                        
END

The data type for the field is numeric if that helps?

 

[MarcLodge]

Hi Kent,

I think you're on the right diagnostic lines. Start off in QMF and use the Global variable function to set up the pass the information to the query, Once you've got that working, try to stitch it in to the batch part.

If you're still having probs, get back to us. Unfortunately I have no access to a QMf session until after the weekend.

Marc

 

[ks01]

Hi Marc ...

Thanks for the follow up. This is what I've tried doing.

I load the SQL of my proc in QMF and issue the following command:

(snipped from SQL):

         DBA2.VTI_VERSION     AS D         
   WHERE D.VERS_NUM         IN (&VERS_NUM) 
     AND A.B1_PROC_RULE_ID IS NOT NULL     
     AND A.B1_PROC_RULE_ID ¬= '           '

I tried to set the following QMF variable:

SET GLOBAL (VERS_NUM='7777,7778'

... the query completes successfully.

It seems as if QMF does not have a problem when I specify the variable. I know that in my JCL, I have set my variable to:

I=USER.QUERYNAME(&&VERS_NUM=7777,7778)

... something I have never understood (the double ampersands) ... but I don't question it, I just do it and it works.

Could it be that this usage of the double ampersands is somehow distorting my variable so that when QMF gets it, it is invalid?

I neglected to mention earlier, as I usually forget, in this QMF procedure, I have a basic procedure I have set which I reference in all of my procs. Its some basic formatting to be more user friendly when users run these procs online:

SET GLOBAL (VERS_NUM=&VERS_NUM


SET PROFILE (CA=UPPER,CO=YES,D=PERIOD,LA=SQL,LE=60,P=' ',W=132)
SAVE PROFILE                                                   
SET GLOBAL (A=(') PERCENT_SIGN=(%)

The [tt]SET GLOBAL (VERS_NUM=&VERS_NUM[/tt] is unique to this specific proc. I only mention it in case this may be the cause of my problems.

I look forward to hearing your thoughts.

Thanks ...

Kent

 

[MarcLodge]

Hi Kent,
The double ampersand bit is because the program you are actually first running is TSO. If you look at your JCL you will see a line saying PGM=IKJEFT01, which is TSO as a background job. What TSO does is read the SYSTSIN file and run anythink it finds there as a TSO command or CLIST. TSO or CLIST interpret any ampersands they find by striping the first of them off, so, in order to pass 1 ampersand to QMF, you have to specify two!!

I have a funny idea that it might do something similar with quotes.

Humour me, and try putting the 7777,7778 in double single quotes (if that makes sense!) eg.

EXECUTIL SEARCHDD(YES)                                
   ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1,+
   I=USER.QUERYNAME(&&VERS_NUM=''7777,7778'')

Not sure if this will work as I've not been able to test it, being at home, but worth a try.

Marc

 

[ks01]

Hi Marc ...

Thanks for the explanation on the double ampersands. Your explanation makes perfect sense ... I'm not too familiar with CLIST or TSO. And yes, you are correct, my QMFBATCH is executing IKJEFT01. I copied this proc from someone else so I've never really been familiar with all the parts of the step.

Unfortunately, the double ticks didn't complete successfully either:

READY                                                                                          
  EXECUTIL SEARCHDD(YES)                                                                       
READY                                                                                          
   ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1,I=RCX1.PCX130V(&&VERS_NUM=''7777,7778'')
ISPD118                                                                                        
The initially invoked module ended with a return code = 16                                     
READY                                                                                          
END

Any other suggestions?

Thank you again ...

Kent

 

[MarcLodge]

Kent,

I'm in the office tomorrow and will look and see if I can find something similar.

Marc

 

[MarcLodge]

Kent,
I've had a play with this and have managed to get it to work after a fashion.

Like you, I was unable to get QMF to recognise more than one value in the variable field, so what I did was increased the number of variables in the query to be the total possible values ever used in the IN clause. Let's assume for sake of argument that the max is 4. Your query would then read:

WHERE D.VERS_NUM         IN (&VERS_NUM1,
                             &VERS_NUM2,
                             &VERS_NUM3,
                             &VERS_NUM4)

Your executing parm in the JCL would read:

PARM(M=B,S=DBP1,I=RCX1.PCX130V(&&VERS_NUM1=7777,+
                               &&VERS_NUM2=7778,+
                               &&VERS_NUM3=,+
                               &&VERS_NUM4=,))

Not very satisfactory I must admit, but at least it works. It may well be that there is a way and we've just not found it, so if anybody else out there has managed to get this to work without the fudge I'm suggesting above, please let us know!

Marc

 

[fredericofonseca]

ahhhh- I read this and didn't think of the obvious.

QMF is most likely defining this as a parameter - if you define it globaly on QMF it replaces it before preparing the SQL - but if you do it otherwise it prepares the SQL (and this means a SINGLE variable is defined), and then binds that parameter to the external value.
This either results on a error like
"
QUERY MESSAGES:
Data of different types must not be used in an expression or condition." (from one of your posts)

or it will result on the variable having "7777,8888" on it and hence not being a valid value.

most likely if you supply a single value to QMF (from JCL) it will work. If it does then this is definitely the issue with it - and only 2 solutions - one is the one Marc mentioned already - the other is to create a function that will convert a string of values into a table and return that each entry as a individual record - not quite sure how to do this on DB2.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ks01]

Hi Marc & Frederico ...

Thanks for both of your suggestions. I have gotten it to work. This is what I did:

In my JCL, I set the SYSTSIN card as follows:

//SYSTSIN   DD *                                        
  EXECUTIL SEARCHDD(YES)                                
   ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM(M=B,S=DBP1,+
   I=RCX1.PCX130VT(&&VERS_NUM1=XXXX,+                   
                   &&VERS_NUM2=XXXX))                   
/*

My stored proc in QMF is set up as follows:

SET PROFILE (CA=UPPER,CO=YES,D=PERIOD,LA=SQL,LE=60,P=' ',W=132)             
SAVE PROFILE                                                                
SET GLOBAL (A=(') PERCENT_SIGN=(%) VERS_NUM1=&VERS_NUM1 VERS_NUM2=&VERS_NUM2
IMPORT QUERY FROM 'PCX.SQL.LIB' ( M QCX130V3                                
IMPORT FORM  FROM 'PCX.QMF.FORM' ( M FCX130VT                               
RUN QUERY (F FORM                                                           
+ &&VERS_NUM1=&VERS_NUM1 &&VERS_NUM2=&VERS_NUM2                             
PRINT REPORT (D N PA N L C                                                  
IMPORT QUERY FROM 'PCX.SQL.LIB' ( M QCX130V4                                
IMPORT FORM  FROM 'PCX.QMF.FORM' ( M FCX130VT                               
RUN QUERY (F FORM                                                           
+ &&VERS_NUM1=&VERS_NUM1 &&VERS_NUM2=&VERS_NUM2                             
PRINT REPORT (D N PA N L C

Just a note, the reason why I'm loading the query twice is there are over 1,000 lines of SQL and QMF refused to accept any more so I had to split the main SQL out in to two sections.

Thanks again Marc for helping me troubleshoot this!

Kent