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: 1738
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: 1529
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: 1738
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: 1529
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 TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts How to display value of an exit param... lind sh CICS 2 Sun Oct 09, 2016 12:28 pm
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Need to know what sysin for DFHTU690 ... pkmurali CICS 3 Mon Jul 25, 2016 1:36 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm


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