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
 

 

Update query variable in JCL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Thu Jun 28, 2012 7:02 pm    Post subject: Update query variable in JCL
Reply with quote

Hi,

I have 2 steps in a jcl for validation , if validation fails step will abend.
if step abends I need to update a status table with values according to step abended.
if step 1 abends..update database with value S1.
if step 2 abends update databse with value S2.
===========
I would like to know is there any way I can parameterize this value in jcl to make the jcl shorter, here i have given only as e.g 2 steps, but my jcl has many steps, which make it a big Jcl.
=========================================
My current jcl


step 1 exec..... abend if validation fails.
step 2 exec..... abend if validation fails.
====
if step1.abend=true
update table
set value = 'S1'
where........
====
if step2.abend=true
update table
set value = 'S2'
where.....
and so on.
=================================
I would like this to be somewhat like.

step 1 exec..... abend if validation fails.
step 2 exec..... abend if validation fails.
====
if step1.abend=true then
set var = 'S1'
else
set var = 'S2'
=======
if (abend) then
update table
set value = '&var'
where.....

thanks for valuable suggestions
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 28, 2012 7:22 pm    Post subject:
Reply with quote

that is not JCL for a JOB.
that is your typed pseudo-JCL-bs.
Cut/COPY and Paste your JCL for the JOB.

and what is
Quote:
update table

supposed to mean.

we don't do theoretical dogshit for rookies.
if you have a JOB that is not working correctly,
provide the JCL,
msgs,
expected output,
actual output,
and we can help.

but we don't do your work for you.
very inconsiderate on your part.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 28, 2012 7:58 pm    Post subject:
Reply with quote

Why not have have something like this

Have 4 PDS members

MEM1 having
Code:
update table


MEM2 having
Code:
where.....


S1 having
Code:
SET VALUE = 'S1'


S2

Code:
SET VALUE = 'S2'


Code:
if step1.abend=true then
set var=S1
else
set var=S2
=======
if (abend) then
/* concantnate */
 (mem1)
 (&var)
 (mem2)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 28, 2012 8:29 pm    Post subject:
Reply with quote

Anju,
another one of those rubber-band-and-bailing-wire sites that do not have an automated scheduler.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Thu Jun 28, 2012 9:51 pm    Post subject:
Reply with quote

Dick,
disappointing reply from you..
I didnt ask anyone to do my work and nor do I expect.....
with the above requirement ,I have a jcl which runs ok, just wanted to get suggestion from others to optimize it, or get some better idea.

just for your info ,about my requirement ,
once the status is updated in the mainframe database, I read this status from an As400 machine and do some processing..it not that site has no scheduler..
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Thu Jun 28, 2012 10:03 pm    Post subject:
Reply with quote

@Pandora-Box, thx

I have simulated a workable jcl of my requirment, which works ok for me . I would like to parameterise only the variable S1 or S2.
SET PYAADATA ='S1' or S2

below my jcl...step 1 will abend..in this example.
==========
Code:
                           
//********************************************************
//* VALIDATION 1-                                   
//********************************************************
//STEP1 EXEC SORT                                         
//SORTIN  DD *                                           
TEST1                                                     
//SORTOUT DD SYSOUT=*                                     
//SYSIN   DD *                                           
  OPTION COPY
//********************************************************
//* VALIDATION 2-                                   
//********************************************************
//STEP2 EXEC SORT                                         
//SORTIN  DD *                                           
TEST2                                                     
//SORTOUT DD SYSOUT=*                                     
//SYSIN   DD *                                           
  OPTION COPY                                             
//********************************************************
//* SET  STATUS IN TABLE STEP1                           
//********************************************************
//STEP  IF STEP1.SORT.ABEND=TRUE THEN                   
//STEP     EXEC DB2TEP2(DSNTEP2)                                   
//DB2TEP2.SYSIN DD *                                     
  UPDATE TABLE                                           
  SET PYAADATA ='S1'                                     
  WHERE DTYP ='STATS';                                   
                                                         
//STEP  ENDIF                                             
//********************************************************
//* SET  STATUS IN TABLE STEP2                           
//********************************************************
//STEP  IF STEP2.SORT.ABEND =TRUE THEN                   
//STEP     EXEC DB2TEP2 (DSNTEP2)                           
//DB2TEP2.SYSIN DD *                               
  UPDATE TABLE                                     
  SET DATA ='S2'                                   
  WHERE DTYP ='STATS';                             
                                                   
//STEP  ENDIF
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: Thu Jun 28, 2012 10:19 pm    Post subject:
Reply with quote

Hello,

Is this in a cataloged PROCedure? If not, suggest it be put into a PROC.

Then you could add a first step that uses a PARM that has a symbolic parameter. This step would write a dataset containing the SQL as:
Code:
  UPDATE TABLE                                     
  SET DATA = whateverTheParmIs                                   
  WHERE DTYP ='STATS'

and this dataset would be named in your //DB2TEP2.SYSIN DD statement.

I suspect that trying to quickly post your "JCL" there were several inconsistancies introduced . . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 28, 2012 10:30 pm    Post subject:
Reply with quote

Optimization of a process involves knowing and understanding
the preceding and following steps of a process.

in your case, what is it that you have to communicate to the other machine
and why.

I have always avoided the use of IF ABEND,
due to the fact that there are Errors that Prevent Execution, Regardless of IF Statement Tests

So, instead of making entries to indicate negative activity,
i always make entries for positive activity,
and if the last positive is not the highest achievable/desirable,
then I know I have a problem.

Instead of inline (or even inproc) steps for the DSNTMP2,
I would make that a separate proc,
where
TABLE Name
SET Column
= value
WHERE ...
are all separate pds members which are addressed via Symbolic Variables
concatenated in SYSIN

asking for help 'optimizing' an inch of a two mile process
makes it hard for someone with experience to give you a helpful answer,
and also leaves you open for bad suggestions.

but then again, isolating your topic this way prohibits anyone from
making a suggestion that may imply that 'your process' is sorta stupid
and should be redesigned.

and looking for your (or any other posters') approval never has been/will be on my list of things to accomplish.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Fri Jun 29, 2012 6:22 pm    Post subject:
Reply with quote

Thanks all..
I have used concat dataset to optimise my 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: Fri Jun 29, 2012 7:17 pm    Post subject:
Reply with quote

Hello,

Please DO NOT post screenshots - they waste space and are NOT as readable as the simple copy/paste and applying the Code Tag.

Look at the earlier replies in this topic and notice the "Code'd" entries.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Thu Jul 05, 2012 10:59 pm    Post subject:
Reply with quote

Hi ,

The earlier method of using PDS member and concatenation was needing 15 pds members to be created , as i had 15 steps.

so I used the below logic which worked well, just wanted to share this.
I am using internal reader to submit the job ,dynamicaly changing the update variable by set command and passing it as parm.



Code:
//***************************************************************
//* SET  STATUS IN TABLE STEP1                                   
//***************************************************************
// SET D='S0'                                                   
//STEP  IF STEP1.SORT.ABENDCC =U0005 THEN                       
// SET D='S1'                                                   
//STEP  ELSE STEP2.SORT.ABENDCC =U0005                           
// SET D='S2'                                                   
//      ENDIF                                                   
//***************************************************************
//STEP  IF (ABEND) THEN                                         
//STEP     EXEC SORT,PARM='JP1"&D"'                             
//SORTIN DD DSN=File name-jcl with update query,DISP=SHR                 
//SORTOUT  DD SYSOUT=(A,INTRDR)                                 
//SYSIN DD *                                                     
 OPTION COPY                                                     
  INREC IFTHEN=(WHEN=(3,3,CH,EQ,C'SET'),                         
  OVERLAY=(18:JP1))                                             
//STEP  ENDIF
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jul 05, 2012 11:09 pm    Post subject:
Reply with quote

Was this tested???
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: Thu Jul 05, 2012 11:26 pm    Post subject:
Reply with quote

Hello,

Quote:
Was this tested???

Sounds as though it was. . .
Quote:
so I used the below logic which worked well,


Possibly there is a difference between what was used and what was posted?

It would be good to know that the posted solution was actually the one that worked icon_cool.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jul 05, 2012 11:37 pm    Post subject:
Reply with quote

Hi Dick,

Exactly I just wanted to know what is present in SORTIN DD?
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Fri Jul 06, 2012 12:28 am    Post subject:
Reply with quote

the sortin has a jcl with update query.

where SET starts at pos 3 and XX at 18.

XX would be replaced based on steps abends to s1 or s2 and INT reader will submit the job.

Code:
//joba job
//STEP     EXEC DB2TEP2 (DSNTEP2)                           
//DB2TEP2.SYSIN DD *                               
  UPDATE TABLE                                     
  SET DATA ='xx'                                   
  WHERE DTYP ='STATS';                             
                                                   
//STEP  ENDIF
Back to top
View user's profile Send private message
parsesource

New User


Joined: 06 Feb 2006
Posts: 97

PostPosted: Sat Jul 14, 2012 9:16 pm    Post subject:
Reply with quote

knickraj wrote:
Hi ,

The earlier method of using PDS member and concatenation was needing 15 pds members to be created , as i had 15 steps.

so I used the below logic which worked well, just wanted to share this.
I am using internal reader to submit the job ,dynamicaly changing the update variable by set command and passing it as parm.



Code:
//***************************************************************
//* SET  STATUS IN TABLE STEP1                                   
//***************************************************************
// SET D='S0'                                                   
//STEP  IF STEP1.SORT.ABENDCC =U0005 THEN                       
// SET D='S1'                                                   
//STEP  ELSE STEP2.SORT.ABENDCC =U0005                           
// SET D='S2'                                                   
//      ENDIF                                                   
//***************************************************************
//STEP  IF (ABEND) THEN                                         
//STEP     EXEC SORT,PARM='JP1"&D"'                             
//SORTIN DD DSN=File name-jcl with update query,DISP=SHR                 
//SORTOUT  DD SYSOUT=(A,INTRDR)                                 
//SYSIN DD *                                                     
 OPTION COPY                                                     
  INREC IFTHEN=(WHEN=(3,3,CH,EQ,C'SET'),                         
  OVERLAY=(18:JP1))                                             
//STEP  ENDIF



your jcl is seriously flawed!

* set statements are checked before the job runs. they are not conditionally executed. D is always "S2" in this case (subsequent set-statements override previous set values with the same name). if step1 abends you´ll get wrong results. this IF THEN ELSE is totally useless.

look at your screenshot. the alarm bell must ring. the syntax highlighter dyes "STEP2.SORT.ABENDCC =U0005 " as a comment. it is a comment.


The "IF (ABEND) " check is a bad idea too. A Job may abend for various other reasons.
Back to top
View user's profile Send private message
parsesource

New User


Joined: 06 Feb 2006
Posts: 97

PostPosted: Sat Jul 14, 2012 9:45 pm    Post subject: Reply to: Update query variable in JCL
Reply with quote

maybe such a jcl is a possible alternative (not tested)

Code:

//ABCD PROC
//SORT EXEC PGM=SORT
//...
//IF (&STEP..SORT.ABENDCC=...) THEN
//rexx EXEC PGM=IKJEFT01,PARM='rexxxy &STEP'
//OUT DD ...
//upd exec pgm=dsntep2
//... use sysin generated in previous step.
// ENDIF
// PEND
//*
//S1 EXEC ABCD,STEP=S1
//SORTIN DD ..
//*
//S2 EXEC ABCD,STEP=S2
//SORTIN DD ...
//*
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Mon Jul 16, 2012 2:15 am    Post subject:
Reply with quote

@parsesource,

THX... indeed a bad JCL icon_sad.gif

I was out of mind icon_rolleyes.gif while testing my jcl which i said worked..earlier..

finally I adapted the jcl as below.

Also with my design, I just need to capture a user abend with my jcl or jcl run ok...other abends are not of importance

Code:

//joba  job
//MYPROC   PROC                                                 
//STEP  IF &STEP..SORT.ABENDCC =U0005 THEN                     
//MYSORT   EXEC PGM=SORT,PARM='JP1"&D"'                         
//SORTIN   DD DSN=file1,DISP=SHR             
//SORTOUT  DD SYSOUT=(A,INTRDR)                                 
//SYSOUT   DD SYSOUT=*                                         
//SYSIN     DD DSN=file2,DISP=SHR                 
//      ENDIF                                                   
//         PEND                                                 
//**************************************************************
//* VALIDATION 1- RUN                                           
//**************************************************************
//STEP1 EXEC SORT                                               
//SORTIN  DD *                                                 
TEST1                                                           
//SORTOUT DD SYSOUT=*                                           
//SYSIN   DD *                                                 
OPTION COPY                                                     
//**************************************************************
//* VALIDATION 2- ABEND                                         
//**************************************************************
//STEP2 EXEC SORT                                               
//SORTIN  DD *                                                 
TEST2                                                           
//SORTOUT DD SYSOUT=*                                           
//SYSIN   DD *                                                 
OPTION COPY                                                     
//**************************************************************
//* SET  STATUS IN TABLE STEP1                                 
//**************************************************************
//STEP     EXEC MYPROC,D=S1,STEP=STEP1                         
//*---------------------------------*                           
//STEP     EXEC MYPROC,D=S2,STEP=STEP2                         


file1:
Code:
//joba job
//STEP     EXEC DB2TEP2 (DSNTEP2)                           
//DB2TEP2.SYSIN DD *                               
  UPDATE TABLE                                     
  SET DATA ='xx'                                   
  WHERE DTYP ='STATS';                             
                                                   


file2:

Code:
OPTION COPY                                                     
  INREC IFTHEN=(WHEN=(3,3,CH,EQ,C'SET'),                         
  OVERLAY=(18:JP1))
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us