|
View previous topic :: View next topic
|
| Author |
Message |
Saravana Manikandan
New User
Joined: 09 Aug 2011
Posts: 14
Location: India
|
|
|
|
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 |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
Saravana Manikandan
New User
Joined: 09 Aug 2011
Posts: 14
Location: India
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
Saravana Manikandan
New User
Joined: 09 Aug 2011
Posts: 14
Location: India
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
Saravana Manikandan
New User
Joined: 09 Aug 2011
Posts: 14
Location: India
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| 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 |
|
|
Saravana Manikandan
New User
Joined: 09 Aug 2011
Posts: 14
Location: India
|
|
|
|
| Thanks a lot for the suggestions! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Peter Nancollis
New User
Joined: 15 Mar 2011
Posts: 47
Location: UK
|
|
|
|
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 |
|
|
Heeraj
New User
Joined: 07 Dec 2010
Posts: 16
Location: Bangalore
|
|
|
|
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 |
|
|
|
|
