View previous topic :: View next topic
|
Author |
Message |
Niki
Active User
Joined: 20 Sep 2008 Posts: 106 Location: Bangalore
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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
ensure XXX2 is delimited by a ';' |
|
Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008 Posts: 106 Location: Bangalore
|
|
|
|
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 |
|
|
Niki
Active User
Joined: 20 Sep 2008 Posts: 106 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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 |
|
|
Niki
Active User
Joined: 20 Sep 2008 Posts: 106 Location: Bangalore
|
|
|
|
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
ensure XXX2 is delimited by a ';' |
Yes it did work. Thank you Pandora-Box.. |
|
Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008 Posts: 106 Location: Bangalore
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Glad it worked :-) |
|
Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008 Posts: 106 Location: Bangalore
|
|
|
|
Thanks Naish.. The solution by Pandora-Box did suffice.. |
|
Back to top |
|
|
|