|
View previous topic :: View next topic
|
| Author |
Message |
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
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 |
|
 |
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
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 |
|
|
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
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 |
|
|
superk
Global Moderator
Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA
|
|
|
|
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 |
|
|
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dominickim
New User
Joined: 28 Feb 2007
Posts: 65
Location: NS, CA
|
|
|
|
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 |
|
|
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
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 |
|
|
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
| can you tel me what must be the pgm.... |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
| 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
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 |
|
|
dominickim
New User
Joined: 28 Feb 2007
Posts: 65
Location: NS, CA
|
|
|
|
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 |
|
|
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
Thanks a lot dominic............ it would be more helpful if u show that...
Thanks once again..... |
|
| Back to top |
|
|
dominickim
New User
Joined: 28 Feb 2007
Posts: 65
Location: NS, CA
|
|
|
|
Hello Bala,
I sent the COBOL source via PM. |
|
| Back to top |
|
|
Balamurugan3
New User
Joined: 02 Sep 2009
Posts: 14
Location: chennai
|
|
|
|
| thankz dominic it worked.... thanks a lot for your help........ |
|
| Back to top |
|
|
dominickim
New User
Joined: 28 Feb 2007
Posts: 65
Location: NS, CA
|
|
|
|
| No problem. |
|
| Back to top |
|
|
|
|
