INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Passing JCL Parm to QMF

Passing JCL Parm to QMF

(OP)
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:

CODE

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:

CODE

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:

CODE

WHERE VERS_NUM IN (7777,7778)
But this does not seem to work.  Anyone have any suggestions?

Thank you in advance!

RE: Passing JCL Parm to QMF

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

CODE

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

Marc

RE: Passing JCL Parm to QMF

(OP)
Hi Marc ...

Thanks for the response.

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

CODE


+-----------------------------------------------------------------------------+
|                  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:

CODE

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?
 

RE: Passing JCL Parm to QMF

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

RE: Passing JCL Parm to QMF

(OP)
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):

CODE

         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:

CODE

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:

CODE

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:

CODE

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 SET GLOBAL (VERS_NUM=&VERS_NUM 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

RE: Passing JCL Parm to QMF

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.

CODE

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

RE: Passing JCL Parm to QMF

(OP)
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:

CODE

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

RE: Passing JCL Parm to QMF

Kent,

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

Marc
 

RE: Passing JCL Parm to QMF

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:

CODE

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

Your executing parm in the JCL would read:

CODE

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

RE: Passing JCL Parm to QMF

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Passing JCL Parm to QMF

(OP)
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:

CODE

//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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close