Is there any significance of specifying "DYNAMIC RESULT SETS <n>" in CREATE PROCEDURE STATEMENT??

I read on Google that it may increase performance but couldn't understand how?

Please give me atleast some clue to understand it.

if you dont specify then DYNAMIC RESULT SETS <n> is set to 0 meaning returns all resultset's ... however giving some no. will restrict it to that upper limit throwing SQL0464W
... performance ... may be some internal optimizations by the engine

Specifying 0 (which is the default) means NO result set is returned. Any other positive number indicates the upper limit of allowable result sets.

I would assume that specifying 0 if there are no results sets to be returned would perform slightly better (or take less memory). Otherwise they would not ask us to specify how many there will be.

Obviously, you must specify the number that may be returned, but there is no syntax or run time error if the SP actually returns fewer than specified.

My assumption was also the same... but on my DB2 9.5.4 over Linux its returning all the resultsets even if i am not specifing anything (0)

create procedure TEST.test23
p1: begin

declare c1 cursor with return to client for
select 1 from dual;

declare c2 cursor with return to client for
select 2 from dual;

open c1;
open c2;

end p1 @

$ db2 " call TEST.test23 () "


Result set 1
--------------

1
-----------
1

1 record(s) selected.


Result set 2
--------------

1
-----------
2

1 record(s) selected.

Return Status = 0

How do you know the default is 0?

see the syntax @ infocentre

.-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-.
>--+------------------------------+--●--+-------------------+--->
'-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+
'-READS SQL DATA----'

moreover explicitely writing DYNAMIC RESULT SETS 0 will also return the 2 resultsets as mentioned in my example above

This sounds confusing . If DB2 considers this statement, it shouldn't return any result set and if it doesn't , no use of the statement at all.

First of all, what i meant to ask in this thread is, how this statement is significant in increasing PERFORMANCE?? or how DB2 manages returning of result sets???

For e.g. , if my procedure expects to return only 2 result sets, and i specify the following statement
DYNAMIC RESULT SETS 3 (or might be more)
how it may decrease the performace?
if its just a memory issue or some other factor also?

At one time, if number of maximum result sets specified was less than the number actually returned, an error would occur. So it is probably safe to assume there was a memory issue (more likely) and possibly a performance issue involved (less likely) of specifying result sets > 0 even if none were returned.

But since Rahul now reports that in 9.5 that restriction has been removed (result sets can be returned even if DYNAMIC RESULT SETS 0 is used), then it appears that whatever performance/memory issue existed previously was determined by IBM to not be big enough to enforce that clause anymore. But obviously, that deals with DB2 internals, and it is hard to find definitive answers on that even if you call IBM support.

@Marcus_A
Quite possible


Thanks Rahul_S80 and Marcus_A