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
 

 

JCL to Set Return code based on DB2 SQL query result

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Mon Mar 13, 2017 9:47 pm    Post subject: JCL to Set Return code based on DB2 SQL query result
Reply with quote

Hi All,

I am executing DB2 query in JCL using IKJEFT01 utility.
For the SQL CODE 0, if the extracted row count is > 1, I would like to set Return code as 0 else for all other cases, need to set RC as 4 . Then depending on this RC, I will process the downstream proc steps.

I am planning to have the DB2 query as SYSIN parameter of IKJEFT01 utility.
Please help me to set the Return code based on the SQL query results (based on COUNT as mentioned above)

Thanks
Vinu
Back to top
View user's profile Send private message

daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 671
Location: Pennsylvania

PostPosted: Tue Mar 14, 2017 12:18 am    Post subject:
Reply with quote

One way to approach this:

1) Add FETCH FIRST 2 ROWS ONLY to your sql

2) Write the results (data) to a dataset (in your JCL)

3) Process the above dataset using product of your choice (SORT, IDCAMS, REXX), setting the rcode accordingly. This would vary for 0, 1, or 2 rows found.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1826
Location: UK

PostPosted: Tue Mar 14, 2017 2:43 am    Post subject:
Reply with quote

IKJEFT01 only issues its own return code. If you want your program's return code issued then you need one of IKJEFT01's siblings either IKJEFT1A or IKJEFT1B. I will leave it to you to research which one as it is your problem.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Mar 14, 2017 9:05 am    Post subject:
Reply with quote

Thanks Dave for the idea. Will check that option.

Nic - If I am using IKJEFT1B, can you please let me know how can I set MAXCC Return code. I think the system returned return code from IKJEFT1B is stored in Register 15. I would like to have user return code to be passed to next subsequent proc step, based on Row count 0,1 or 2.

Thanks
Vinu
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Mar 14, 2017 6:44 pm    Post subject: Reply to: JCL to Set Return code based on DB2 SQL query result
Reply with quote

Hi All,

I did something like this.

Wrote 3 steps
- Step1 will make use of IKJEFT01 with DNSTIAUL program and execute SELECT query and get the rows in a dataset
- Step2 uses IDCAMS utility and set Return code based on Record count
If rec count is 2, set RC as 0. Else if rec count is 1, set RC as 8. Or if empty file, set RC as 4.

Code:
//STEP02 EXEC PGM=IDCAMS
//DD1  DD DSN=TEST.ABC.FILE,DISP=SHR
//SYSIN DD *
   PRINT INFILE(DD1) COUNT(2)
   IF LASTCC GT 0 -
       THEN DO
                  PRINT INFILE(DD1) COUNT(1)
                  IF LASTCC EQ 0 THEN DO -
                      SET LASTCC = 8
                      END
                  ELSE -
                       SET LASTCC = 4
                END

/*
Execute STEP03 only if RC is < 5



The problem here is that, if file with rec count is 1, that comes from STEP01, then STEP02 will set RC as 8 and for rest of the proc steps after STEP03, I have to deal with MAXCC 8. Is there any way to reset the max return code back to 0, immediately after STEP03.

Thanks
Vinu
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8121
Location: East Dubuque, Illinois, USA

PostPosted: Tue Mar 14, 2017 7:37 pm    Post subject:
Reply with quote

Quote:
Is there any way to reset the max return code back to 0, immediately after STEP03.
It depends upon why you want to reset it -- for the job or for the following steps. If your site is on z/OS 2.2 and you want the job code set, investigate the JOBRC parameter of the JOB statement. If your site is not yet on z/OS 2.2 or if you want the code 8 changed for following steps in the job, then you pretty much have to deal with the step condition code of 8.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 671
Location: Pennsylvania

PostPosted: Tue Mar 14, 2017 7:53 pm    Post subject:
Reply with quote

Rather than using 4 and 8, use 1 and 2 for your maxcc.

Step three would be run when < 2

You can then code later steps with these codes in mind.

There are no rules that say non zero return codes must be multiples of four.

Perhaps IBM uses that convention to allow the user flexibility to use the numbers in between
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Tue Mar 14, 2017 8:41 pm    Post subject:
Reply with quote

vinu78 wrote:
if the extracted row count is > 1, I would like to set Return code as 0 else for all other cases, need to set RC as 4
Vinu,

Do you really need to issue a separate return-code when exactly 1 row is returned? Just trying to understand if we have 3 scenarios here.
Your original requirement seems to have only 2 conditions - i.e., a: rows > 1 , b: rows = 1 or 0

If that is still the case, you could do something like this using ICETOOL to issue an RC=4 when input has 1 or no records, and RC=0 if you have records >1.
Code:
//STEP01   EXEC PGM=ICETOOL                 
//IN       DD DISP=SHR,DSN= (Input data set)
//TOOLMSG  DD SYSOUT=*                       
//DFSMSG   DD SYSOUT=*                       
//TOOLIN   DD *                             
 COUNT FROM(IN) RC4 LOWER(2)         
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Mar 14, 2017 9:23 pm    Post subject:
Reply with quote

Arun - I have to send an error email (as in STEP03), only if the number of records got from STEP01 is > 1 or for empty file. So for Rec count exactly equal to 1, I need to skip STEP03 and then proceed with downstream steps.
Unfortunately my shop has SYNCSORT installed and so ICETOOL wont work. Whether similar logic is possible in SYNCSORT ? Also whether is it possible to reset the RC back to 0 immediately after STEP03.

Robert - I need to reset the MAXCC back to 0, because I am introducing Step1,2,3 on top of existing proc steps and so don't want to disturb the existing condition code checks used in the proc.

Dave - Thanks for the info. Will implement that.

Thanks
Vinu
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Tue Mar 14, 2017 9:30 pm    Post subject:
Reply with quote

vinu78 wrote:
Unfortunately my shop has SYNCSORT installed and so ICETOOL wont work
In general Syncsort shops would already have the Syncsort's ICETOOL alternative - SYNCTOOL installed. And the above ICETOOL job should run and give you the same results unless you tried it already and it gave you an error.

Normally both PGM=ICETOOL OR PGM=SYNCTOOL would invoke the SYNCTOOL package at a Syncsort shop.
vinu78 wrote:
if the number of records got from STEP01 is > 1 or for empty file. So for Rec count exactly equal to 1, I need to skip STEP03 and then proceed with downstream steps.

Now back to your 'new' requirement, from your latest post it seems like you still have 2 scenarios unless I got it wrong.
Case1 - records > 1 or records = 0
Case2 - records = 1
If you modify the control card to use EQUAL, it would return RC=4 when you have exactly 1 record and RC=0 for the rest, ie. records = 0 or > 1
Code:
//TOOLIN   DD *             
 COUNT FROM(IN) RC4 EQUAL(1)

Alternatively NOTEQUAL(1) could be used, which would return RC=0 for a 1-record input and RC=4 for everything else.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Mar 14, 2017 9:43 pm    Post subject:
Reply with quote

Arun,

Yes you got the requirement right. A little bit detailed mentioning is - Step01 will do SELECT query of DB2 table (using IKJEFT01 with DSNTIAUL) and the rows extracted (no rows, 1 row or 1+ row) are stored in a flat file. This flat file is validated in STEP02 for Maxcc check that is used to send email in STEP03.

Case 1 is No records or 1+ record => Send an email from STEP03
Case 2 is exactly 1 record => Don't send email. Bypass Step03.

I have tried using ICETOOL and it gave some error as TOOLMSG not detected, and so I thought this DFSORT utility won't work in our shop.

Thanks
Vinu
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Tue Mar 14, 2017 9:58 pm    Post subject:
Reply with quote

Thanks for clarifying, could you PM me your synctool error, I could take a look. Am just curious.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Mar 14, 2017 10:48 pm    Post subject:
Reply with quote

Thanks Arun. Will send that once I am in office tomorrow.

Thanks
Vinu
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Wed Mar 15, 2017 10:37 am    Post subject:
Reply with quote

Arun,

The ICETOOL is working fine

//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//INPUT DD DSN=<flat file that has records>,DISP=SHR
//TOOLIN DD *
COUNT FROM(INPUT) NOTEQUAL(1) RC4
/*

So this sets RC as 0 if record count is 1, else for all other cases, it sets Rec count as 4.

Thanks
Vinu
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Wed Mar 15, 2017 6:25 pm    Post subject:
Reply with quote

Good to know it does work for you. Do you mind posting what went wrong last time.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Thu Mar 16, 2017 1:04 pm    Post subject: Reply to: JCL to Set Return code based on DB2 SQL query result
Reply with quote

Thanks Arun.

Yes, I have put the multiple COUNT statements like below and it threw error

//TOOLIN DD *
COUNT FROM(INPUT) EQUAL(0) RC1
COUNT FROM(INPUT) EQUAL(1) RC4
COUNT FROM(INPUT) EQUAL(2) RC8
/*
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10277
Location: italy

PostPosted: Thu Mar 16, 2017 1:27 pm    Post subject: Reply to: JCL to Set Return code based on DB2 SQL query result
Reply with quote

Quote:
Perhaps IBM uses that convention to allow the user flexibility to use the numbers in between


nope icon_biggrin.gif

it was used in prehistoric ages for performance reason when calling a subroutine
the return code would be a 4 bytes offset into a branch table
( faster than doing the comparison )

and convention percolated upstream
( as I was told - once upon a time - by somebody working at OS development )
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1826
Location: UK

PostPosted: Thu Mar 16, 2017 4:37 pm    Post subject:
Reply with quote

Vinu
The message re TOOLMSG not detected was probably due to the TOOLMSG DD statement being missing but you did not show your JCL and your subsequent message did nothing to clarify the matter. And no error was "thrown" - nothing is ever "thrown" on a z/OS mainframe. Errors are raised, generated displayed, shown etc but never "thrown". That is PC terminology and only within the "curly-wurly" family of languages.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Split file based on comparsion to 2 f... Div Grad DFSORT/ICETOOL 3 Thu Jul 27, 2017 9:16 pm
No new posts EXIT or RETURN cvnlynn CLIST & REXX 16 Wed Jul 19, 2017 2:50 am
No new posts UNIT=AFF for Easytrieve code gandikk JCL & VSAM 7 Wed Jul 12, 2017 11:42 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm


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