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
 

 

Using dynamic substitution in jcl on sql commands

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Using dynamic substitution in jcl on sql commands
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    Post subject:
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    Post subject: Hi
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

Moderator Team Head


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

PostPosted: Fri Sep 18, 2009 5:21 pm    Post subject: Reply to: Using dynamic substitution in jcl on sql commands
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    Post subject: Hi
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

Site Director


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

PostPosted: Tue Sep 22, 2009 8:39 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Sep 23, 2009 7:00 pm    Post subject: Reply to: Using dynamic substitution in jcl on sql commands
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts Invoking CA 7 commands through REXX kenshin CLIST & REXX 10 Fri Aug 12, 2016 9:33 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 Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 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