Returning multiple result sets

[Stanley Sinclair]

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?

[Rhino]

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?

You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

[Stanley Sinclair]

Thanks Rhino (??? Nose ???)

I am, in fact, writing in DB2 SQL procedural language. I'll see if
there are equivalents to what you have written.

In the meantime, I'd much appreciate responses from others who have
encountered the same problem,

SS

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?

You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

[Stanley Sinclair]

I forgot to ask if anyone has experience with using more than one
connection to return result sets faster in VB6 on XP with Win 2003
server?

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?

You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

[Dan Scott]

Stanley Sinclair wrote:

Thanks Rhino (??? Nose ???)

I am, in fact, writing in DB2 SQL procedural language. I'll see if
there are equivalents to what you have written.

In the meantime, I'd much appreciate responses from others who have
encountered the same problem,

SS

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google .com...

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?

You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

Check SQLLIB\samples\sqlproc\rsultset.db2 for a sample of an SQL
procedure that returns two result sets.

Dan

[Stanley Sinclair]

Thanks, Rhino:

I'm using v8.1.4 in Win 32, programming in VisualBasic 6 with latest update.

I will try to follow through on your example.

But, if someone knows how to do this in SQL procedural language, I'd apprecieate.

SS
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?

You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino