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
 

 

Displaying SQLCODE returned by a query in SPUFI or JCL

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

New User


Joined: 09 Aug 2011
Posts: 14
Location: India

PostPosted: Tue Jul 10, 2012 12:19 pm    Post subject: Displaying SQLCODE returned by a query in SPUFI or JCL
Reply with quote

Hi,

I need to display the SQLCODE returned by a query executed through a SPUFI or JCL.

I need to continue a process through REXX based on the SQLCODE.

Could you pls help me with this?

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

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Tue Jul 10, 2012 12:23 pm    Post subject:
Reply with quote

Sorry, I'm not clear with your input.

How is your query executed? SPUFI in batch? or user-program (COBOL, REXX or any other)?

Please elaborate.
Back to top
View user's profile Send private message
Saravana Manikandan

New User


Joined: 09 Aug 2011
Posts: 14
Location: India

PostPosted: Tue Jul 10, 2012 12:26 pm    Post subject:
Reply with quote

Hi Gnana Sekaran,

I am invoking a DB2 query through JCL. I need the SQLCODE returned by the query in some variable.

Would it be possible to get in the SELECT clause?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Tue Jul 10, 2012 12:38 pm    Post subject:
Reply with quote

DSNTEP* in TSO batch?

What type of DML operation you do?
Quote:
Would it be possible to get in the SELECT clause?

DB2 SELECT query?

Please post some lines of JCL step/DB2 query to understand what you're trying to do.
Back to top
View user's profile Send private message
Saravana Manikandan

New User


Joined: 09 Aug 2011
Posts: 14
Location: India

PostPosted: Tue Jul 10, 2012 12:51 pm    Post subject:
Reply with quote

Yes, its a DSNSTEP in TSO batch and its a SELECT query.

SELECT FIELD1 FROM TABLE1
WHERE FIELD2 = "VALUE2";

I would need the SQLCODE returned by the above query.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Tue Jul 10, 2012 1:22 pm    Post subject:
Reply with quote

Assuming that you're catching SYSPRINT in a data set, you may need to parse (in next step) SYSPRINT data set which holds DB2 statement results.

If you see the messages like below, you can interpret SQLCODE as 0 or +100 based on number of rows returned. And, STEP' RC would be Zero.
Code:
0SUCCESSFUL RETRIEVAL OF          0 ROW(S) 

or
Code:
0SUCCESSFUL RETRIEVAL OF          1 ROW(S)

If there is an error like below, read SQLCODE from the same line. And, STEP' RC would be 8 or higher depending on severity.
Code:
DSNT408I SQLCODE = -199, ERROR:...

Finally, you may need to consider RC=4 which might be of warnings.
Back to top
View user's profile Send private message
Saravana Manikandan

New User


Joined: 09 Aug 2011
Posts: 14
Location: India

PostPosted: Tue Jul 10, 2012 2:15 pm    Post subject:
Reply with quote

This will not solve my purpose when the query did not fetch any rows.

I would need a SQLCODE +100 in that case but it gives
"0SUCCESSFUL RETRIEVAL OF 0 ROW(S) "

Any suggestions to handle this?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Tue Jul 10, 2012 3:00 pm    Post subject:
Reply with quote

Quote:
I would need a SQLCODE +100 in that case but it gives
"0SUCCESSFUL RETRIEVAL OF 0 ROW(S) "

This is how DSNTEP* works, I think. If 0 Rows returned, you need to assume that SQLCODE as +100

Someone here may offer you better suggestions if you explain how this requirement is originated.
Back to top
View user's profile Send private message
Saravana Manikandan

New User


Joined: 09 Aug 2011
Posts: 14
Location: India

PostPosted: Tue Jul 10, 2012 3:18 pm    Post subject:
Reply with quote

Thanks a lot for the suggestions!
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: Tue Jul 10, 2012 9:33 pm    Post subject:
Reply with quote

Hello,

Quote:
Someone here may offer you better suggestions if you explain how this requirement is originated.
If you post why someone believes this approach is needed and how it came about, someone may have a suggestion. . .
Back to top
View user's profile Send private message
Peter Nancollis

New User


Joined: 15 Mar 2011
Posts: 47
Location: UK

PostPosted: Wed Jul 11, 2012 6:04 am    Post subject:
Reply with quote

write some code ?
then you have control and can do what you like with the sqlcode [and in batch the condcode] .... sorry if this is sucking eggs!
Back to top
View user's profile Send private message
Heeraj

New User


Joined: 07 Dec 2010
Posts: 16
Location: Bangalore

PostPosted: Wed Jul 11, 2012 11:24 am    Post subject: Reply to: Displaying SQLCODE returned by a query in SPUFI or
Reply with quote

If you need to continue "A PROCESS through REXX based on SQL CODE", I think you can read the SYSPRINT dataset in that REXX before you do your process, if you find a record
0SUCCESSFUL RETRIEVAL OF 0 ROW(S)
consider the SQL code as +100.
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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