IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SPUFI Why it give 100 as return code


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Pons

New User


Joined: 25 May 2007
Posts: 61
Location: Coimbatore

PostPosted: Mon Jun 04, 2007 10:19 pm
Reply with quote

Hi,

In SPUFI; What ever row i select in the SQL Query return code is display as 100. But it is sussfully select the records and displayed. But why it gave 100 instead of 0 (100 - Row not found, 0 - Successful completion).

Please let me know...
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Mon Jun 04, 2007 10:27 pm
Reply with quote

Is it showing you all the rows?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jun 04, 2007 10:36 pm
Reply with quote

Hello,

I'd suggest downloading a copy of the DB2 manuals so you will have them closeby (or at least saving the direct link in your favorites).

This is from the DB2 Messages & Codes linked to from thes forums. . .
Quote:
When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion.


Keep in mind that some results (like a 100 return code) depend on what was going at the time the return code was issued (i.e. SPUFI rather than an application program)

When you learn how to look up/verify things, you will get much quicker answers icon_smile.gif

We're here when there are additional questions.
Back to top
View user's profile Send private message
Pons

New User


Joined: 25 May 2007
Posts: 61
Location: Coimbatore

PostPosted: Tue Jun 05, 2007 7:10 pm
Reply with quote

Hi William,

What ever row i select, depending on my condition it will display. But the SQL code is 100.

Thanks dick.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jun 05, 2007 7:21 pm
Reply with quote

You're welcome icon_smile.gif
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA

PostPosted: Tue Jun 05, 2007 11:12 pm
Reply with quote

Yes, Pons. When you use SPUFI, your return code is 100 after the execution of the SQL, regardless of whether anything is returned or not.

When I execute SPUFI SQL where there are no matches, I get this:

DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

When there is a match, my row(s) are displayed along with this message:

DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

It's a SPUFI thing, mate. It simply means that it found no more rows.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Wed Jun 06, 2007 1:33 am
Reply with quote

It's not a SPUFI thing - it means no more records (satisfying the condition if there is a condition).
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Jun 06, 2007 9:05 am
Reply with quote

Its displays sqlcode 100 because there are no more rows to be fetched

for ex I have a table with 4 records when I select * on that table it gave me all records and with sqlcode =100

Code:

---------+---------+---------+---------+---------+---------+-----
SELECT * FROM xxxx.TAB5;                                     
---------+---------+---------+---------+---------+---------+-----
EMPNO       EMPID  EMPFIELD
---------+---------+---------+---------+---------+---------+-----
     7            7  AAA
     8            8  AAA
     9            9  AAA
    10           10  AAA
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-----


Back to top
View user's profile Send private message
Pons

New User


Joined: 25 May 2007
Posts: 61
Location: Coimbatore

PostPosted: Wed Jun 06, 2007 7:17 pm
Reply with quote

Thanks to all.

So it means, 100 is the SQL code of success and failure for SPUFI.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jun 06, 2007 7:30 pm
Reply with quote

Hello,

Not necessarily (at least not as worded).

In your case, you should get the 100 at "end" or if there are no rows found. The 100 is raised when "ROW NOT FOUND" which might be caused by a few other things also (look in the DB2 Messages and Codes linked to from the forums).

Other "failures" will not generate a 100.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Jun 07, 2007 8:36 am
Reply with quote

Pons,

The statement is getting SQLCODE = 100, because "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.It doesnt means that spufi has failed
icon_smile.gif.The spufi is getting SQLCODE = 100 when it is trying to fetch records from the table which is not there in the table.

see I have a table with no records but when i try to fetch some records using select statement I am getting statement executed succesful and
SQLCODE = 100.

Code:

---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM XXXX.ABST;                                             00010007
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO       EMPID  EMPFIELD
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+



Hope this helps.

Thanks,
Prem
Back to top
View user's profile Send private message
Pons

New User


Joined: 25 May 2007
Posts: 61
Location: Coimbatore

PostPosted: Thu Jun 07, 2007 6:08 pm
Reply with quote

Thanks dick.

Hi prem,

Thanks for your reply. But my question is why it gave 100 as a sql code which it get the selected fields. I knew if no rows found it will return 100. Even though i fetched my desired row it will gave as 100.

It's an SPUFI thing.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Jun 07, 2007 7:18 pm
Reply with quote

You're welcome icon_smile.gif

And yes, SPUFI is designed to return 100 for either case.

There is more info in the DB2 Messages & Codes linked to from the "Manuals" link at the top of the page.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri Jun 08, 2007 8:01 am
Reply with quote

Pons,

I have also given an example above select statement.Your above explanation has the answer for your doubt.See you know that the above statement has got your desired result.But how come a cursor which is fetching the records from the table would know that there are no more records icon_question.gif icon_question.gif icon_question.gif icon_question.gif
So In my example I fetching 4 rows.But the cursor which is doing the job after the statement doesnt know that there are anymore records to be fetched from the table.So It assumes that there are some more records after fetching the only present 4 records.When it is doing so it passes the signal that there are no more records to fetch.So it stops there and returns SQLCODE = 100
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jun 08, 2007 11:12 am
Reply with quote

premkrishnan wrote:
Pons,

I have also given an example above select statement.Your above explanation has the answer for your doubt.See you know that the above statement has got your desired result.But how come a cursor which is fetching the records from the table would know that there are no more records icon_question.gif icon_question.gif icon_question.gif icon_question.gif
So In my example I fetching 4 rows.But the cursor which is doing the job after the statement doesnt know that there are anymore records to be fetched from the table.So It assumes that there are some more records after fetching the only present 4 records.When it is doing so it passes the signal that there are no more records to fetch.So it stops there and returns SQLCODE = 100

and
the the execution of the statement itself is successful irrespective of the sqlcode that is why it says ?STATEMENT EXECUTION WAS SUCCESSFUL?
Back to top
View user's profile Send private message
achittu

New User


Joined: 26 Feb 2007
Posts: 14
Location: chennai

PostPosted: Sat Jun 09, 2007 12:06 am
Reply with quote

If you have 4 rows in your table satisfying your condition , then set
the MAXIMUM ROWS TO BE FETCHED option in the SPUFI to 4. If you set to any value more than 4 it would give SQLCODE 100 at the end.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DELETE SPUFI DB2 1
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts Adding QMF and SPUFI to the ISPF menu DB2 20
No new posts Return codes-Normal & Abnormal te... JCL & VSAM 7
Search our Forums:

Back to Top