Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Dynamic sysin parameter

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Thu May 24, 2012 10:02 pm    Post subject: Dynamic sysin parameter
Reply with quote

Hi,

I have a requirement where we want to run a JCL to extract columns of a db2 table based on multiple items which are present in a flat file.


Code:
SELECT * from ITEM_TABLE
WHERE
ITEM_CD IN(#)


# - Here the values will be received from a flat file where the item listing would be there.. So this part should be dynamic.

I know it could be done in two step. 1. Run table unload then sort or a simple cobol program. But how about I want to do in JCL with only 1 single step. Is it possible ?

Let me know if anyone need some more clarification.

Thank you!
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1746
Location: Bloomington, IL

PostPosted: Thu May 24, 2012 10:10 pm    Post subject:
Reply with quote

Note that you can't "do it in JCL", as JCL only establishes environments. You can have JCL run a program (which may be a utility, as *Sort or BMC Unload+) to do what you want, however.

Are you permitted to execute Rexx in the background? What's your estimated volume of extracted rows?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 24, 2012 10:12 pm    Post subject:
Reply with quote

since you are manipulating jcl,
WTF put it in db2 forum..............................?

it will always require 2 steps, if the IN-LIST is to be dynamic.

use sort to build your 'dynamic IN-LIST',
following step would be the unload.

actually, if you have this stupid qsam file that contains the IN-LIST,
why,
when you create the qsam file,
do you not create the complete sysin???????

then that would be one step.

you are so busy counting steps, you are falling over your feet.
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Thu May 24, 2012 10:14 pm    Post subject:
Reply with quote

If you are running some program (unload utility as Akatsukami posted) this might be of some help:-

http://ibmmainframes.com/viewtopic.php?p=287762&highlight=#287762

icon_smile.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1530
Location: Andromeda Galaxy

PostPosted: Thu May 24, 2012 10:26 pm    Post subject:
Reply with quote

Try to use DSNTIAUL utility

Code:
//SQL EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS ('SQL') -
LIB('DB2.RUNLIB.LOAD')
END
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD SYSOUT=*
//SYSIN DD DSN=PANDORA.JCL(XXX1),DISP=SHR
//      DD DSN=PANDORA.JCL(XXX2),DISP=SHR


Have the below in XXX1

Code:
SELECT * from ITEM_TABLE
WHERE
ITEM_CD IN


Have the below in XXX2

Code:
(#)


ensure XXX2 is delimited by a ';'
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Thu May 24, 2012 10:33 pm    Post subject:
Reply with quote

Akatsukami wrote:
Note that you can't "do it in JCL", as JCL only establishes environments. You can have JCL run a program (which may be a utility, as *Sort or BMC Unload+) to do what you want, however.

Are you permitted to execute Rexx in the background? What's your estimated volume of extracted rows?


Thanks Akatsukami.

No I wont be able to Rexx in my current scenario. Thats why I asked if we have this flexibility in JCL, but it seems we dont have. So I think COBOL is the answer as I cant afford to unload the whole table everytime in Batch.
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Thu May 24, 2012 10:38 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
since you are manipulating jcl,
WTF put it in db2 forum..............................?

it will always require 2 steps, if the IN-LIST is to be dynamic.

use sort to build your 'dynamic IN-LIST',
following step would be the unload.

actually, if you have this stupid qsam file that contains the IN-LIST,
why,
when you create the qsam file,
do you not create the complete sysin???????

then that would be one step.

you are so busy counting steps, you are falling over your feet.



As the tweak needs to done in db2 too, thats why I have put here. If thats a big concern for you, you should have ignored. And as I said the execution can be done in other ways too, but I wanted to know if there is any flexibility in here. Just in case you missed my query - read it again.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 24, 2012 10:41 pm    Post subject:
Reply with quote

Niki,
congratulations on determining a solution,
especially since you have read every post.

by the way, cobol solution sucks.

but, use it anyway. thx for dropping by for help and info.

now that we know that you are very limited technically,
and don't bother to think about what is provided,
we know now, not to waste time answering you in the future.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1746
Location: Bloomington, IL

PostPosted: Thu May 24, 2012 10:48 pm    Post subject:
Reply with quote

Niki wrote:
No I wont be able to Rexx in my current scenario. Thats why I asked if we have this flexibility in JCL, but it seems we dont have.

Whoa! Before jumping to that conclusion, look at the example that Pandora provides; is that of use?
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Thu May 24, 2012 11:53 pm    Post subject:
Reply with quote

Pandora-Box wrote:
Try to use DSNTIAUL utility

Code:
//SQL EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS ('SQL') -
LIB('DB2.RUNLIB.LOAD')
END
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD SYSOUT=*
//SYSIN DD DSN=PANDORA.JCL(XXX1),DISP=SHR
//      DD DSN=PANDORA.JCL(XXX2),DISP=SHR


Have the below in XXX1

Code:
SELECT * from ITEM_TABLE
WHERE
ITEM_CD IN


Have the below in XXX2

Code:
(#)


ensure XXX2 is delimited by a ';'


Yes it did work. Thank you Pandora-Box..
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Thu May 24, 2012 11:54 pm    Post subject:
Reply with quote

Akatsukami wrote:
Niki wrote:
No I wont be able to Rexx in my current scenario. Thats why I asked if we have this flexibility in JCL, but it seems we dont have.

Whoa! Before jumping to that conclusion, look at the example that Pandora provides; is that of use?


Yes, that worked perfectly fine.. Thanks
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1530
Location: Andromeda Galaxy

PostPosted: Thu May 24, 2012 11:56 pm    Post subject:
Reply with quote

Glad it worked :-)
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Fri May 25, 2012 1:53 am    Post subject:
Reply with quote

Naish wrote:
If you are running some program (unload utility as Akatsukami posted) this might be of some help:-

http://ibmmainframes.com/viewtopic.php?p=287762&highlight=#287762

icon_smile.gif


Thanks Naish.. The solution by Pandora-Box did suffice..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Dynamic array request/response contai... Suja.Sai CICS 2 Tue Jan 24, 2017 11:37 am
No new posts REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm
No new posts DDNAME parameter mistah kurtz JCL & VSAM 6 Mon Dec 19, 2016 4:41 pm
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us