How to read a DB2 column value in JCL

baljinders · New User Joined: 21 Aug 2006 Posts: 72

Hi,

I have a db2 table having column COL1 of datatype CHAR(01) that can store either a 'Y' or 'N'.
I want to read the value of this column using JCL in step01 and if the value is 'Y', it will allow step02 to execute. If value is 'N', step02 would be bypassed.
How can i achieve this using JCL?

Thanks

technut · New User Joined: 27 Dec 2007 Posts: 73 Location: India

I am just writing an idea that came to my mind first. There might be other better ways, but this one should work.
Just the algorithm here,
STEP0A. Write a step to read from the table into a file by giving the SQL instream.
STEP0B. The output of STEP0A is passed to STEP0B. Here you INCLUDE records with the first column as 'Y' using SORT.
STEP0C: You check the existence of data inside the output file of STEP0B using pgm=T60130. If RC=0, then it means data is present in the file and you allow STEP02 to execute, else flush the step. This can be done thru IF ELSE logic in JCL.

enrico-sorichetti · Posted: Tue Jun 15, 2010 11:32 am

usually processes that depend on database values are controlled inside the program ...

if some_database_value is ... then
some preocess
else
some other process

I guess that a yes/no switch like in this case is the consequence of the success/failure of some previous process/program/job
why not investigate how to do it with a scheduler ???

or if this way of proceeding is a standard practice of Your organization
the fastest would be to write a small program ( generalized of course )
to match some_table/some_column_name/some_value and return the appropriate return code to be checked in later steps
the easiest way would be a small REXX script, quick, effective

enrico-sorichetti · Posted: Tue Jun 15, 2010 11:36 am

technut · New User Joined: 27 Dec 2007 Posts: 73 Location: India

OK. I should have mentioned that...I was not sure of that either! Well the intention to help was genuine.. sorry if somebody's time was wasted..

@Baljinders - Instead of T60130, you may use some locally available program available in your end to check existense of data in a file. The IBM utility IEBPTPCH could be used as well.

baljinders · New User Joined: 21 Aug 2006 Posts: 72

CICS Guy · Posted: Tue Jun 29, 2010 12:43 am

dbzTHEdinosauer · Posted: Tue Jun 29, 2010 12:55 am

I assume you are using either IKJEFT01,IKJEFT1A, or IKJEFT1B

the above link will tell you which one to use to provide a return code to the system.

baljinders · New User Joined: 21 Aug 2006 Posts: 72

I am making some progress here.
I am running the rexx routine using JCL. Through JCL parm i would like to pass in the variable value for SQL to execute. For example, I would like to pass the table_name. Here's how i have written the JCL and REXX

//SYSTSIN DD *
ISPSTART CMD(A,TABLE1)

in rexx
PARSE ARG TBLNAME
.
.
.
(SELECT MAX(DATE) FROM ' || TBLNAME......

But I am not able to run it successfully as i am getting the following error -

PREPARE S01 FAILED.
-514
SQLCODE FOR OPEN CURSOR C01 FAILED.
SQLCODE FOR CLOSE CURSOR C01 FAILED

Please suggest a remedy

dick scherrer · Posted: Wed Jun 30, 2010 10:36 pm

Hello,

What was the sqlcode of the prepare? The -514 is because the process tried to use a cursor that was not prepared. . .