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

Doubt in MULTI ROW FETCH


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

Active User


Joined: 15 Dec 2005
Posts: 271

PostPosted: Wed Oct 28, 2009 2:13 pm
Reply with quote

Hi all,

I need to select 297 rows from the table using multirow fetch cursor. If i use,

exec sql
declare cursor csrname
with rowset positioning for
select col1,col2 from table where
col3= 'ex'
end-exec

*******fetch
fetch first 298 only into
:s-host-var-array

After fetch operation will it result 297 rows into the array variable,even after returning sqlcode=+100?

Please reply,since i am new to multirow fetch method.
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Wed Oct 28, 2009 2:20 pm
Reply with quote

After your FETCH check the SQLCA field SQLERRD(3). This field is populated with the number of rows actually returned, so you will have this many rows stored in the array.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Wed Oct 28, 2009 4:24 pm
Reply with quote

Hello Murali,

I believe Multi row fetch structure is
Code:

EXEC SQL FETCH Cursor_1 FOR 297 ROWS INTO :s-host-var-array


As mentioned by Steve check value of SQLERRD(3) to identify the number of row fetched.

Let us know if you have any doubts
Back to top
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Wed Oct 28, 2009 5:36 pm
Reply with quote

hi Murali,
In multifetch if the number of rows fetched is less than the row set size defined in fetch statement SQLCODE will be 100. But SQLERRD(3) will have the actual no of rows for that transaction(fetch).

For example if the rowset size is 500 and the fetched rows is 300 assuming array is defined with size 500, SQLCODE=+100 & SQLERRD(3)=300.

If the cursor returns more than 500 you need to do a fetch until you end up with less than 500.Since you need to know the count of the fetched rows for processing, SQLERRD(3) will return this number when SQLCODE=+100 else the no of rows is rowset size(in this case 500)
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 Fetch data from programs execute (dat... DB2 3
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts Code Multi Row fetch in PL1 program PL/I & Assembler 1
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts Selective extract of multi-record-typ... SYNCSORT 4
Search our Forums:

Back to Top