IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Dynamic sysin parameter


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

Active User


Joined: 20 Sep 2008
Posts: 106
Location: Bangalore

PostPosted: Thu May 24, 2012 10:02 pm
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: 1788
Location: Bloomington, IL

PostPosted: Thu May 24, 2012 10:10 pm
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
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
Reply with quote

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

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

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

Global Moderator


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

PostPosted: Thu May 24, 2012 10:26 pm
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: 106
Location: Bangalore

PostPosted: Thu May 24, 2012 10:33 pm
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: 106
Location: Bangalore

PostPosted: Thu May 24, 2012 10:38 pm
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
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: 1788
Location: Bloomington, IL

PostPosted: Thu May 24, 2012 10:48 pm
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: 106
Location: Bangalore

PostPosted: Thu May 24, 2012 11:53 pm
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: 106
Location: Bangalore

PostPosted: Thu May 24, 2012 11:54 pm
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

Global Moderator


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

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

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

Active User


Joined: 20 Sep 2008
Posts: 106
Location: Bangalore

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

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

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. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
No new posts Using the Jobname parameter in a Qual... ABENDS & Debugging 1
No new posts JCL Dynamic System Symbols JCL & VSAM 3
No new posts Synctool-dynamic split job for varyin... JCL & VSAM 7
No new posts Demand with DEADLINE TIME parameter CA Products 4
Search our Forums:

Back to Top