IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

JCL to Set Return code based on DB2 SQL query result


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Mon Mar 13, 2017 9:47 pm
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: 792
Location: Pennsylvania

PostPosted: Tue Mar 14, 2017 12:18 am
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: 2455
Location: Hampshire, UK

PostPosted: Tue Mar 14, 2017 2:43 am
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: 179
Location: India

PostPosted: Tue Mar 14, 2017 9:05 am
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: 179
Location: India

PostPosted: Tue Mar 14, 2017 6:44 pm
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: 8696
Location: Dubuque, Iowa, USA

PostPosted: Tue Mar 14, 2017 7:37 pm
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: 792
Location: Pennsylvania

PostPosted: Tue Mar 14, 2017 7:53 pm
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: 2481
Location: @my desk

PostPosted: Tue Mar 14, 2017 8:41 pm
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: 179
Location: India

PostPosted: Tue Mar 14, 2017 9:23 pm
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: 2481
Location: @my desk

PostPosted: Tue Mar 14, 2017 9:30 pm
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: 179
Location: India

PostPosted: Tue Mar 14, 2017 9:43 pm
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: 2481
Location: @my desk

PostPosted: Tue Mar 14, 2017 9:58 pm
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: 179
Location: India

PostPosted: Tue Mar 14, 2017 10:48 pm
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: 179
Location: India

PostPosted: Wed Mar 15, 2017 10:37 am
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: 2481
Location: @my desk

PostPosted: Wed Mar 15, 2017 6:25 pm
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: 179
Location: India

PostPosted: Thu Mar 16, 2017 1:04 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Mar 16, 2017 1:27 pm
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: 2455
Location: Hampshire, UK

PostPosted: Thu Mar 16, 2017 4:37 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts RC query -Time column CA Products 3
No new posts Return codes-Normal & Abnormal te... JCL & VSAM 7
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top