Pass Dynamic value to SYSIN card while executing SQLs



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

Pass Dynamic value to SYSIN card while executing SQLs

Postby shankar_dh » Wed Feb 26, 2014 4:48 pm

My requirement is Every time I need to pass different value to my sysin card of JCL

Example --
Below is the Query I am passing thru SYSIN CARD( I am executing this query thru DB2BATCH )
Select ,LEDGER_DESCRIPTION
,DEBIT
,CREDIT
FROM T14DBC.BAGG1600

In above example ...
I need to query different tables through JCL. I have four JCLs. Query remains same but only table name gets changes.

Is there any option to pass dynamic value (symbolic variable to SYSIN card of JCL) to avoid multiple control cards?
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Pass Dynamic value to SYSIN card while executing SQLs

 

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby NicC » Wed Feb 26, 2014 7:55 pm

If you have the very latest version of z/OS then the answer is yes - symbols in in-stream data do get resolved. Otherwise you need to have a program read a parameter defined by a symbolic and generate your query in a previous step.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby shankar_dh » Wed Feb 26, 2014 10:44 pm

Thanks Nic for the reply.
I do have the latest Zos but How do I need to pass the parameter to my SQLs.
Below is my JCL to UNLOAD the data.

//STEP01 EXEC DSNPGM,
// VER='TEST',
// SYSTEM='TD0T',
// PRM='SQL',
// PROGRAM='DSNTIAUL'
//SYSIN DD DSN=ABCD.UTTTTTT.CTLCARDS(CABC101A),DISP=SHR
//SYSREC00 DD DSN=ABCD.UNLOAD.DATA,DISP=SHR
//SYSPUNCH DD DUMMY

below is the SQLs I am passing through SYSIN

Select LEDGER_DESCRIPTION
,DEBIT
,CREDIT
FROM T14DBC.BAGG1600

I want to parameter the table name in the SQLs.
Appreciate your reply on the same.
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby NicC » Thu Feb 27, 2014 3:31 am

in your SYSIN as per your post
FROM T14DBC.BAGG&TAB


and

//        TAB=1600
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby Terry Heinze » Thu Feb 27, 2014 3:39 am

NicC wrote:If you have the very latest version of z/OS then the answer is yes - symbols in in-stream data do get resolved. Otherwise you need to have a program read a parameter defined by a symbolic and generate your query in a previous step.

I'm using z/OS 01.13.00 so I guess that's not the latest. I tried and JCL did't like symbolic variables in my SYSIN.
.... Terry
Terry Heinze
 
Posts: 193
Joined: Wed Dec 04, 2013 11:08 pm
Location: Richfield, MN, USA
Has thanked: 10 times
Been thanked: 11 times

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby shankar_dh » Thu Feb 27, 2014 11:29 am

Hi Nic,

Even my JCL too did not support it!!
I have tried the below code,

// SET TAB=1600
//STEP01 EXEC DSNPGM,
// VER='TEST',
// SYSTEM='TD0T',
// PRM='SQL',
// PROGRAM='DSNTIAUL'
//SYSIN DD *
SELECT * FROM T14DBC.BAGG&TAB;
//SYSREC00 DD DSN=ABCD.TEST.SDH,DISP=SHR
//SYSPUNCH DD DUMMY
//*

I have got the below error ,
DSNT502I ERROR IN DSNTIAUL SQL INPUT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "&". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: . <IDENTIFIER> JOIN INNER
LEFT RIGHT FULL CROSS , HAVING GROUP
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 28 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby dick scherrer » Thu Feb 27, 2014 8:47 pm

Hello,

I tried and JCL did't like symbolic variables in my SYSIN.

I believe the data to be substituted must be inline, not in a file/member.

We have no current plan to move to 1.13, so i cannot run a test.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby Terry Heinze » Thu Feb 27, 2014 8:59 pm

My symbolic variable WAS instream data (data following a DD for SYSIN).:
//SYSIN    DD  *
  &VAR
/* 
.... Terry
Terry Heinze
 
Posts: 193
Joined: Wed Dec 04, 2013 11:08 pm
Location: Richfield, MN, USA
Has thanked: 10 times
Been thanked: 11 times

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby NicC » Thu Feb 27, 2014 9:29 pm

Yes - inline and I think you need z/OS 2.0 but there are references in the forum to the exact level required.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Pass Dynamic value to SYSIN card while executing SQLs

Postby Peter_Mann » Thu Feb 27, 2014 9:52 pm

Looks like z/OS 2.1 --
IBM Inside Sales
© 2013 IBM Corporation
International Technical Support Organization Global Content Services
Topic 14: Symbolic substitution in instream data
 New SYMBOLS keyword on DD statement
SYMBOLS=[ ( ] JCLONLY | EXECSYS | CNVTSYS [ , DDname ) ]
– SYMBOLS=JCLONLY
Substitute JCL symbols made available by the EXPORT statement and JES symbols
dynamically created via JES Symbol Service (IAZSYMBL)
– SYMBOLS=EXECSYS
Perform substitution as for JCLONLY plus substitute system symbols from the system
where this job is executing.
– SYMBOLS=CNVTSYS
Perform substitution as for JCLONLY plus substitute system symbols from the system
where this job was converted.
– DDname
is an optional DD name (which must be provided by the user) which will receive the symbol

if you google search z/OS V2R1 Technical Updates, you will see all the new functions and features

HTH's
Peter
Peter_Mann
 
Posts: 139
Joined: Fri Jun 24, 2011 7:37 pm
Location: Lowell,AR
Has thanked: 14 times
Been thanked: 3 times

Next

Return to JCL

 


  • Related topics
    Replies
    Views
    Last post