Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Significance of "DYNAMIC RESULT SETS <n>" in a procedure definition

    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.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    if you dont specify then DYNAMIC RESULT SETS <n> is set to 0 meaning returns all resultset's ...
    Specifying 0 (which is the default) means NO result set is returned. Any other positive number indicates the upper limit of allowable result sets.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by Stealth_DBA
    Specifying 0 (which is the default) means NO result set is returned. Any other positive number indicates the upper limit of allowable result sets.
    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)
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How do you know the default is 0?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  9. #9
    Join Date
    Aug 2009
    Posts
    3

    Smile

    Quote Originally Posted by rahul_s80


    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?

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by priyanka_24125


    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2009
    Posts
    3
    @Marcus_A
    Quite possible


    Thanks Rahul_S80 and Marcus_A

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •