PASS variable via JCL to query in PDS



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

Re: PASS variable via JCL to query in PDS

Postby steve-myers » Sun Dec 16, 2012 4:12 am

What you are requesting is not very clear. In any event, the programming required to use a member name specified through a data set can be difficult to impossible. High level languages such as Cobol have no mechanism to use a member name. None. Period. Full stop. Even Assembler programmers rarely deal with this. The techniques are fairly simple for advanced Assembler programmers, but are far beyond the skill set of beginners.

These users thanked the author steve-myers for the post:
ceekayrocks (Sat Dec 29, 2012 2:16 pm)
steve-myers
Global moderator
 
Posts: 2105
Joined: Thu Jun 03, 2010 6:21 pm
Has thanked: 4 times
Been thanked: 243 times

Re: PASS variable via JCL to query in PDS

Postby dick scherrer » Sun Dec 16, 2012 4:28 am

Hello and welcome to the forum,

Suggest you re-post and explain in detail (with examples of the query and what you dynamically want to change) what you are trying to accomplish rather than talking about passing " a variable via JCL".

Basically variables cannot be passed thru JCL. JCL does nothng but execute "thngs".

I beileve what you want to do is rather straightforward, but the method of imlementation will be rather different.
Hope this helps,
d.sch.

These users thanked the author dick scherrer for the post:
ceekayrocks (Sat Dec 29, 2012 2:16 pm)
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: PASS variable via JCL to query in PDS

Postby ceekayrocks » Sat Dec 29, 2012 2:41 pm

Hi Dick / Steve

Thanks for replying,

My plan is to pass a value in a PS file to the WHERE Clause of a Query in A PDS member. I am not sure about the position of the where clause in the query in the member so i need to know how to send this value in the PS file to the Where clause in the query .

I will just give a outline of my requirement as i am not able to share any codes.

//File1 DD DSN = XXXX --------------------------------> (the flat file which has the only the value that i need to pass eg: 128768)
//File2 DD DSN = YYYY(mem1) ------------------------> (the PDS memeber which has the query in which the where clause needs to be changed)

eg of the query in the PDS member:

content Inside FILE2:

select * from table
where salary = 56789 -------------------> This "56789" is to be change to " 128768" from input PS File1.

^^^^^^^^ above is just a sample query , the real time query will be complex but will have only one WHERE clause which needs to be change and its position will be random which we will not be able to know .


Also there are several members of the PDS "YYYYY" like mem2, mem3,.... that have similar queries where the WHERE clause ( same "where salary =") needs to be modified with the same infile FILE1 value "128768".

So My other question will be is there a way to change the member name alone with SET parameter and loop the job? so that i don't have to use too many steps to do the same function of passing a value to the query in different members......

Any suggestions would really guide with the above requirement , thanks all in advance.
ceekayrocks
 
Posts: 2
Joined: Sun Dec 16, 2012 12:55 am
Has thanked: 2 times
Been thanked: 0 time

Re: PASS variable via JCL to query in PDS

Postby NicC » Sat Dec 29, 2012 5:44 pm

Frequent reference to forums and the JCL manuals will show that there is no such thing as looping in JCL - it is straight 'top-to-bottom' processing with the ability to skip on down. What you will need to do is have a program that reads the member and seq file and generate the query and then execute it. You cannot use JCL to directly manipulate data. JCL is for controlling the Job processing not the data manipulation within the job.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: PASS variable via JCL to query in PDS

Postby dick scherrer » Sun Dec 30, 2012 7:34 am

Hello,

If i understand what you want, you can do this by putting this process in a cataloged PROCedure, defining the query in a number of pds members (before and after the predicate), and the "where(predicate)" member name can be specified at execution.

When there are several of these, there would be a job with a PROC execution for each query and the unique member would be specifiec by a symbolic parameter.

If you post the jcl and control statements you use to run a batch query, we can help you modify it to do this.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: PASS variable via JCL to query in PDS

Postby c62ap90 » Mon Dec 31, 2012 8:38 pm

I do not know what tools your shop has and how familiar you are with them but we do this with Easytrieve…

The two input files to Easytrieve are the SQL SELECT clause file and the WHERE variable file.
The output file is the combined SQL SELECT clause with the WHERE variable.
The JCL later executes a batch SQL job using the output of the Easytrieve step.
c62ap90
 
Posts: 125
Joined: Thu Oct 11, 2012 10:24 pm
Has thanked: 1 time
Been thanked: 7 times

Re: PASS variable via JCL to query in PDS

Postby dick scherrer » Mon Dec 31, 2012 8:46 pm

Hello,

Yup, this can be done with Easytrieve, Sort, IEGBENER, FileAid, and any number of other utilities.

If the current JCL and control statements are posted, we can help with the new mechanics.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post