multirow fetch with embedded SQL

[Frank Swarbrick]

Is there a way to do a multi-row fetch in to a COBOL table with DB2/LUW?
Apparently the following is supported in z/OS, but not LUW (or at least I
couldn't get it to work):

WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry.
10 t2-name pic x(30) occurs 100.
10 t2-dept pic x(04) occurs 100.
EXEC SQL
END DECLARE SECTION
END-EXEC
EXEC SQL
DECLARE CURSOR-X CURSOR FOR
select name, dept
from test.table2
where name in (select distinct name from test.table1)
END-EXEC.
.......
PROCEDURE....
exec sql
FETCH NEXT CURSOR-X
into :T2-NAME, :T2-DEPT
for 100 rows
end-exec
Frank

[Hemant Shah]

You need to open the cursor before fetching.

In procedure division:

exec sql
open cursor-x
end-exec

perform until sqlcode not = zero

exec sql
fetch cursor-x
into :T2-NAME, :T2-DEPT
end-exec

* Do something with the data.

end-perform.

exec sql
close cursor-x
end-exec

While stranded on information super highway Frank Swarbrick wrote:

Is there a way to do a multi-row fetch in to a COBOL table with DB2/LUW?
Apparently the following is supported in z/OS, but not LUW (or at least I
couldn't get it to work):

WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry.
10 t2-name pic x(30) occurs 100.
10 t2-dept pic x(04) occurs 100.
EXEC SQL
END DECLARE SECTION
END-EXEC
EXEC SQL
DECLARE CURSOR-X CURSOR FOR
select name, dept
from test.table2
where name in (select distinct name from test.table1)
END-EXEC.
......
PROCEDURE....
exec sql
FETCH NEXT CURSOR-X
into :T2-NAME, :T2-DEPT
for 100 rows
end-exec
Frank

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Frank Swarbrick]

Well, yes, but that was not the question. (I guess my question was a bit
ambiguous. Sorry.)

A 'multi-row' fetch (not sure if that is the correct name) appears to allow
you to declare a COBOL table in working storage, and do a single FETCH
statement to populate it with a multi-row result set.

Apparently this is supported in DB2 for z/OS, as in the example I gave. Not
having access to DB2 for z/OS myself I can't test it, but it is stated in
the manual, and a z/OS programmer is the one who I got the example from.

Personally, I'm fairly happy with using OPEN FETCH/FETCH/FETCH CLOSE, but
there's an argument on comp.lang.cobol that retrieving multiple rows with a
single FETCH is show how 'better' or 'more modern' or some such thing! :-)

Frank

n 1/31/2008 at 12:47 PM, in message <fn**********@n ew7.xnet.com>, Hemant
Shah<sh**@typho on.xnet.comwrot e:

You need to open the cursor before fetching.

In procedure division:

exec sql
open cursor-x
end-exec

perform until sqlcode not = zero

exec sql
fetch cursor-x
into :T2-NAME, :T2-DEPT
end-exec

* Do something with the data.

end-perform.

exec sql
close cursor-x
end-exec

While stranded on information super highway Frank Swarbrick wrote:

>Is there a way to do a multi-row fetch in to a COBOL table with DB2/LUW?

>Apparently the following is supported in z/OS, but not LUW (or at least

I

>couldn't get it to work):

WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry.
10 t2-name pic x(30) occurs 100.
10 t2-dept pic x(04) occurs 100.
EXEC SQL
END DECLARE SECTION
END-EXEC
EXEC SQL
DECLARE CURSOR-X CURSOR FOR
select name, dept
from test.table2
where name in (select distinct name from test.table1)
END-EXEC.
......
PROCEDURE....
exec sql
FETCH NEXT CURSOR-X
into :T2-NAME, :T2-DEPT
for 100 rows
end-exec
Frank

[Serge Rielau]

This support hasn't been added yet.
The same is true for INSERT and MERGE.
DB2 for zOS is leading the charge here.

First time is see the requirement against DB2 for LUW.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Karl Hanson]

Serge Rielau wrote:

This support hasn't been added yet.
The same is true for INSERT and MERGE.
DB2 for zOS is leading the charge here.

First time is see the requirement against DB2 for LUW.

DB2 for i5/OS has supported the multiple-row-fetch clause for a number
of releases (aka "FOR n ROWS"):

http://publib.boulder.ibm.com/infoce...afzmstfets.htm
http://publib.boulder.ibm.com/infoce...afzmstfets.htm

--
Karl Hanson

[Frank Swarbrick]

>>On 1/31/2008 at 6:26 PM, in message
<60************ *@mid.individua l.net>,
Serge Rielau<sr*****@ ca.ibm.comwrote :

This support hasn't been added yet.
The same is true for INSERT and MERGE.
DB2 for zOS is leading the charge here.

First time is see the requirement against DB2 for LUW.

Do you mean that there *is* a requirement, or that I should submit one?

Thanks,
Frank

[Serge Rielau]

Frank Swarbrick wrote:

>>>On 1/31/2008 at 6:26 PM, in message

<60************ *@mid.individua l.net>,
Serge Rielau<sr*****@ ca.ibm.comwrote :

>This support hasn't been added yet.
The same is true for INSERT and MERGE.
DB2 for zOS is leading the charge here.

First time is see the requirement against DB2 for LUW.

Do you mean that there *is* a requirement, or that I should submit one?

You should submit it. Obviously it exists (and was addressed) for DB2
for i5/OS and zOS.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

>>On 2/1/2008 at 11:20 AM, in message
<60************ *@mid.individua l.net>,
Serge Rielau<sr*****@ ca.ibm.comwrote :

Frank Swarbrick wrote:

>>>>On 1/31/2008 at 6:26 PM, in message

<60*********** **@mid.individu al.net>,
Serge Rielau<sr*****@ ca.ibm.comwrote :

>>This support hasn't been added yet.
The same is true for INSERT and MERGE.
DB2 for zOS is leading the charge here.

First time is see the requirement against DB2 for LUW.

Do you mean that there *is* a requirement, or that I should submit one?

You should submit it. Obviously it exists (and was addressed) for DB2
for i5/OS and zOS.

Sounds good.
Thanks,
Frank