View previous topic :: View next topic
|
Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Thanks for clarifying, could you PM me your synctool error, I could take a look. Am just curious. |
|
Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
Thanks Arun. Will send that once I am in office tomorrow.
Thanks
Vinu |
|
Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Good to know it does work for you. Do you mind posting what went wrong last time. |
|
Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Quote: |
Perhaps IBM uses that convention to allow the user flexibility to use the numbers in between |
nope
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
|