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

Using dynamic substitution in jcl on sql commands


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Fri Sep 18, 2009 3:58 pm
Reply with quote

Hi,

Can any one say me, can we use dynamic statements on sql in SYSIN cards,

For e.g.,

//SYSIN DD *
SELECT * FROM TABLE WHERE COL1 BETWEEN '1000' AND '2000';
/*

Is there any way to use dynamic variables to be used in place of 1000 and 2000.so that i can change the dynamic variables alone...
I tried using, but cant find a way...


Thanks in advance...
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 18, 2009 4:06 pm
Reply with quote

Hi Bala,
On what factor does that value depends on what How do you want to pass those values to the Sysin card.
I am not sure but I think that Symname will work,
We too are creating a dynamic unload card for that we are written a small code prior to this step to generate this card.
Back to top
View user's profile Send private message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Fri Sep 18, 2009 4:17 pm
Reply with quote

Its an PROC which removes some contents from the table & takes a back up of it in a dataset & then deletes the same in table.

The value which is selected & deleted from the table depends upon the ranges specified....

Its dynamic variables has to be specified either in the proc or else somehere..... so that each time i dont need to change the query... i can simply change the variable value alone.....
Back to top
View user's profile Send private message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA

PostPosted: Fri Sep 18, 2009 5:21 pm
Reply with quote

Bala, look at this issue simply. It's a PROC, so //DD SYSIN DD * (i.e. instream data) is out of the question. Your input HAS to come from a dataset. Therefore, you HAVE to write your required substitutions into a dataset in a previous job or jobstep.

The best suggestion I have is to use the JCL SET statement to define the variables, then pass them into a program via the PARM= statement and have your prgram write them out into a dataset for the PROC.
Back to top
View user's profile Send private message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Tue Sep 22, 2009 4:21 pm
Reply with quote

Is it possible to pass the value through jcl to a proc which is holding a dsn (which comes under //SYSIN DD* & containing the sql query) , for which the values has to be substituted............


for eg

if
//SYSIN DD DSN=ABC.SAMPLE



ABC.SAMPLE- - FILE CONTAINING THE FOLL QUERY

SELECT * FROM TABLE WHERE COL1 BETWEEN &INITAIN AND &FINAL



Is it posssible to pass the value of initail & final thru jcl......
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Sep 22, 2009 8:39 pm
Reply with quote

Hello,

Suggest you re-read this:
Quote:
The best suggestion I have is to use the JCL SET statement to define the variables, then pass them into a program via the PARM= statement and have your prgram write them out into a dataset for the PROC.
You need to do some work. There is nothing that will do this automagically.
Back to top
View user's profile Send private message
dominickim

New User


Joined: 28 Feb 2007
Posts: 65
Location: NS, CA

PostPosted: Tue Sep 22, 2009 9:39 pm
Reply with quote

Hello Bala,

I'm using below but you need to write a simple program if you don't have such a program on your site.
Code:

//JOBCARD JOB ...
// SET FR='1000',TO='2000'
//STEP1   EXEC PGM=PARM2CTL,
// PARM='SELECT * FROM TABLE WHERE COL1 BETWEEN ''&FR'' AND ''&TO'';'
//CTLCARD   DD DSN=&&CTLCARD,DISP=(,PASS),
//             SPACE=(100,(100,100),RLSE),UNIT=DISK,
//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)
//*
//STEP2   EXEC PGM=IEBGENER
//SYSIN     DD DUMMY
//SYSPRINT  DD SYSOUT=*
//SYSUT1    DD DSN=&&CTLCARD,DISP=(OLD,DELETE)
//SYSUT2    DD SYSOUT=*
//


Output:
Code:

********************************* TOP OF DATA **********************************
SELECT * FROM TABLE WHERE COL1 BETWEEN '1000' AND '2000';                       
******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Wed Sep 23, 2009 6:52 pm
Reply with quote

hi

thankz for the solution........

sorry for questioning againg ........

whether PARM2CTL is a utility or a cobol pgm ?

wat does it actually does ?
Back to top
View user's profile Send private message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Wed Sep 23, 2009 6:58 pm
Reply with quote

can you tel me what must be the pgm....
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Sep 23, 2009 7:00 pm
Reply with quote

Quote:
whether PARM2CTL is a utility or a cobol pgm


utilities are programs

it is an in house program,which takes the string passed as a parm and writes it to a dataset...

almost all the shops have one, the name might be different, the language used might be different but the result would be the same
a more sophisticated one would take a semiformatted parm string like
Code:
xxC<............>

the xx would be the staring column for the output data
th C would be simply moved to col 72 for possible continuation
a blank would do nothing
Back to top
View user's profile Send private message
dominickim

New User


Joined: 28 Feb 2007
Posts: 65
Location: NS, CA

PostPosted: Wed Sep 23, 2009 7:04 pm
Reply with quote

It is simple COBOL program that reads PARM and writes output.
Ask your senior members about this kind of program, they might know in your site.
I can show you mine privately if you want, since this is JCL forum.
Back to top
View user's profile Send private message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Wed Sep 23, 2009 7:09 pm
Reply with quote

Thanks a lot dominic............ it would be more helpful if u show that...

Thanks once again.....
Back to top
View user's profile Send private message
dominickim

New User


Joined: 28 Feb 2007
Posts: 65
Location: NS, CA

PostPosted: Wed Sep 23, 2009 7:21 pm
Reply with quote

Hello Bala,

I sent the COBOL source via PM.
Back to top
View user's profile Send private message
Balamurugan3
Currently Banned

New User


Joined: 02 Sep 2009
Posts: 14
Location: chennai

PostPosted: Wed Sep 23, 2009 8:12 pm
Reply with quote

thankz dominic it worked.... thanks a lot for your help........
Back to top
View user's profile Send private message
dominickim

New User


Joined: 28 Feb 2007
Posts: 65
Location: NS, CA

PostPosted: Wed Sep 23, 2009 8:19 pm
Reply with quote

No problem.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
No new posts disable /*XEQ substitution in TWS IBM Tools 4
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 Console Commands All Other Mainframe Topics 4
Search our Forums:

Back to Top