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
 

 

SPUFI Why it give 100 as return code

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: SPUFI Why it give 100 as return code
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: 3158
Location: Tucson AZ

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

Is it showing you all the rows?
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: Mon Jun 04, 2007 10:36 pm    Post subject:
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    Post subject: Re: SPUFI Why it give 100 as return code
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

Site Director


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

PostPosted: Tue Jun 05, 2007 7:21 pm    Post subject:
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: 147
Location: Columbia, MO

PostPosted: Tue Jun 05, 2007 11:12 pm    Post subject: Re: SPUFI Why it give 100 as return code
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

Active Member


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

PostPosted: Wed Jun 06, 2007 1:33 am    Post subject:
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

Moderator


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

PostPosted: Wed Jun 06, 2007 9:05 am    Post subject:
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    Post subject: Re: SPUFI Why it give 100 as return code
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

Site Director


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

PostPosted: Wed Jun 06, 2007 7:30 pm    Post subject:
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

Moderator


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

PostPosted: Thu Jun 07, 2007 8:36 am    Post subject:
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    Post subject: Re: SPUFI Why it give 100 as return code
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

Site Director


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

PostPosted: Thu Jun 07, 2007 7:18 pm    Post subject:
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

Moderator


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

PostPosted: Fri Jun 08, 2007 8:01 am    Post subject:
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    Post subject:
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    Post subject: Re: SPUFI Why it give 100 as return code
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    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
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts scheduling between OPC and TWS give u... nsbl IBM Tools 1 Sun Mar 12, 2017 5:35 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm
No new posts CA Workload Automation event code help vinu78 CA Products 10 Wed Mar 01, 2017 10:05 am


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