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
 

 

-811 & fetched record

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

New User


Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India

PostPosted: Mon Dec 13, 2010 3:05 pm    Post subject: -811 & fetched record
Reply with quote

can anyone please tell me if a singleton query returns more than a row then which row(record) will be stored in the host variable.

with my tests everytime i got the second record in the host variable irrespective of no. of record satisfies the condition

lets say the below query[ignore the INTO stmnt] returns 4 rows with seq_nbr 1,2,3,4 in spufi in that order
& when i embedded the sql in a cobol code it always gives seq_nbr- 2

And i didnt give any order by clause

Code:

SELECT seq_nbr               
  INTO :ws-seq-nbr           
  FROM emp1             
 WHERE emp_id = 1


Initially i thought that it may depends on some default installation values & may get varied from shop to shop.
but the same thing happens in my new project also & no one is aware of this.

Need a clarification.
Back to top
View user's profile Send private message

don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Mon Dec 13, 2010 6:29 pm    Post subject:
Reply with quote

The row returned in this situation is, by definition, unpredictable. It is possible that in a future release, DB2 may return no row at all.

The proper way to handle this is to code your program as if no row was returned.

If you are relying on -811 as an "existence check" there are many better ways to achieve this.

If this is a case of wanting one row returned, but you don't care which one, then use a CURSOR.
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 Dec 13, 2010 9:00 pm    Post subject:
Reply with quote

Hello,

Quote:
can anyone please tell me if a singleton query returns more than a row then which row(record) will be stored in the host variable.
Only one row will be returned if the sqlcode is zero. Before attempting to use the host variable(s) value(s), the sqlcode must be tested.

If the sqlcode of the SELECT is non-zero, do not use the value(s) in the host variables.

Keep in mind that your code must follow the rules or the way the code works may change when there is a ptf applied or an upgrade is done.
Back to top
View user's profile Send private message
razesh84

New User


Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India

PostPosted: Tue Dec 14, 2010 12:34 pm    Post subject:
Reply with quote

Hi,

In some of our production code sqlcode 0 & -811 were handled together hoping that in case of multiple rows satisfies the condition the first row will be fetched in the host variable.
Code:

EVALUATE SQLCODE
   WHEN ZERO
   WHEN -811
       MOVE TABLE-DATA TO WS-TABLE-DATA
   WHEN OTHER
       .........
END-EVALUATE


One of such code requires some tuning & when i added FETCH FIRST ROW there the o/p files didnt match.After buring few ATPs I found that conditions where multiple rows satisfied were unmatched.Previoulsy it was picking the 2nd record now the first one.

I asked this question to every senior members of my team & got so many different answers[first row,junk,last row,dont know,unpredictable] that made me think it may vary on some parameter & in our shop it definately returns 2nd row.Now the same thing happens in my new project.

So does it happen everwhere?can you guys do a little test & post the result here.Curious to know!!!!

@don
Quote:
If you are relying on -811 as an "existence check" there are many better ways to achieve this.

If this is a case of wanting one row returned, but you don't care which one, then use a CURSOR.

its not for existence checking.even if cursor was used are you telling me to fetch the cursor single time?may be FETCH FIRST ROW serve the situation better.

@dick
Quote:

If the sqlcode of the SELECT is non-zero, do not use the value(s) in the host variables

thanks for the suggestion
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Dec 14, 2010 1:40 pm    Post subject:
Reply with quote

as of db2 vsn 7, a -811 will not guarrentee population of the result set - there may or may not be some data there.

prior to vsn 7 (that means vsn 6 or less) a -811 would populate the result.

with vsn 7 came FETCH FIRST ROW ONLY.

with vsn 8 you can ORDER BY and FETCH FIRST ROW ONLY.

razesh84,

my suggestion to you is after your experience with your senior people (who obviously do not know) is to read the manual.
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 Dec 14, 2010 8:31 pm    Post subject:
Reply with quote

Hello,

Quote:
In some of our production code sqlcode 0 & -811 were handled together hoping that in case of multiple rows satisfies the condition the first row will be fetched in the host variable.
Suggest you get rid of any/all code that is based on hope. . .

Code must be written to work all of the time because it is correctly coded - not that the result "hoped for". . . There is no end to the damage that can be caused implementing this way.
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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


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