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
 

 

How to read a DB2 column value in JCL

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

New User


Joined: 21 Aug 2006
Posts: 72

PostPosted: Tue Jun 15, 2010 10:33 am    Post subject: How to read a DB2 column value in JCL
Reply with quote

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
Back to top
View user's profile Send private message

technut

New User


Joined: 27 Dec 2007
Posts: 73
Location: India

PostPosted: Tue Jun 15, 2010 11:13 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Tue Jun 15, 2010 11:32 am    Post subject: Reply to: How to read a DB2 column value in JCL
Reply with quote

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
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Tue Jun 15, 2010 11:36 am    Post subject: Reply to: How to read a DB2 column value in JCL
Reply with quote

Quote:
pgm=T60130
please avoid using local jargon/program names
t60130 certainly is not a program generally available,
suggesting it' s use ids just a waste of time for everybody
Back to top
View user's profile Send private message
technut

New User


Joined: 27 Dec 2007
Posts: 73
Location: India

PostPosted: Tue Jun 15, 2010 2:14 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
baljinders

New User


Joined: 21 Aug 2006
Posts: 72

PostPosted: Tue Jun 29, 2010 12:34 am    Post subject: Re: Reply to: How to read a DB2 column value in JCL
Reply with quote

Quote:

the easiest way would be a small REXX script, quick, effective


Can you help me write a rexx routine? I learned REXX after i read your post and so far have been able to 1)connect to DB2 2)execute the sql and 3) disconnect from DB2
I now want to generate a return code based on the value selected from the sql in # 2 above. As i mentioned before, if the value is 'Y', the subsequent step in JCL to execute. If the selected value from REXX routine is 'N', the subsequent step would be bypassed.
Can you please share some code snippet similar to one i am looking for?

Thx
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Tue Jun 29, 2010 12:43 am    Post subject:
Reply with quote

Quote:
so far have been able to 1)connect to DB2 2)execute the sql and 3) disconnect from DB2
I now want to generate a return code based on the value selected from the sql in # 2 above.
Post your REXX?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jun 29, 2010 12:55 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
baljinders

New User


Joined: 21 Aug 2006
Posts: 72

PostPosted: Wed Jun 30, 2010 9:48 pm    Post subject:
Reply with quote

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
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: Wed Jun 30, 2010 10:36 pm    Post subject:
Reply with quote

Hello,

What was the sqlcode of the prepare? The -514 is because the process tried to use a cursor that was not prepared. . .
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 Loading data to table gives wrong for... Raghu navaikulam DB2 16 Thu Jul 13, 2017 2:11 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Read of TDQ fails in LENGERR Tracy Hansen CICS 10 Wed Apr 05, 2017 12:10 am
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm


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