Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37

    Unanswered: Questions about Stored procedure result sets

    DB2 allows a calling program to receive an entire result set from a stored procedure, by specifying something like "FETCH xxxxxxx FOR 500 ROWS INTO array". It's my guess this doesn't really have any effect on the way the cursor actually performs and is simply a way to transmit more data between calling program and SP. Since the calling program and SP run in different address spaces, the savings are simply due to fewer address space swaps.
    Please comment.

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    the quote below comes from What's New in db2 vsn 8 (which is where you can find Figure 7, if you are so inclined)
    , which is when multi-row fetch.... was introduced.
    There is more in the
    DB2 UDB for z/OS Version 8 Technical Preview, a Redbook found here
    as well as this Redbook:
    DB2 UDB for z/OS Version 8: Everything You Ever Wanted to Know, ... and More
    found here


    Multiple fetches and inserts allowed within a single SQL statement
    You can enhance the performance of your application programs by using multiple-row FETCH and INSERT statements to request that DB2 send multiple rows of data, at one time, to and from the database. Using these multiple-row statements in local applications results in fewer accesses of the database. Using these multiple-row statements in distributed applications results in fewer network operations and a significant improvement in performance. Figure 7 illustrates the difference between a series of single fetches and a single, multiple-row fetch operation.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    "Using these multiple-row statements in distributed applications results in fewer network operations and a significant improvement in performance" would seem to support what I said. If this were an advantage to the way the cursor performs, why wouldn't it be offered in a conventional batch DB2 program?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You have a different/new interface. The precompiler has to support that in order to generate the respective constructs in the host language.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Quote Originally Posted by Balr14
    If this were an advantage to the way the cursor performs, why wouldn't it be offered in a conventional batch DB2 program?
    If you have DB2 Vsn 8 (or higher), whether on mainframe or pc, you have multi-row constructs available.
    Dick Brenholtz, Ami in Deutschland

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There is no advantage to the way cursor performs. The benefit is you would perform far less fetches than you currently do. And, it is available for use in a conventional batch DB2 program.

    Dave

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Balr14 View Post
    "Using these multiple-row statements in distributed applications results in fewer network operations and a significant improvement in performance" would seem to support what I said. If this were an advantage to the way the cursor performs, why wouldn't it be offered in a conventional batch DB2 program?
    This applies to a distributed client application calling a stored procedure, but not one stored procedure calling another stored procedure if both stored procedures are on the same physical server. Same applies to a batch program running on the database server.
    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
    Nov 2010
    Posts
    2
    Some, of course, will complain about WOW Gold the result, including those desperados in the stock exchange, the crave for any type of shock, however cynically orchestrated to Buy WOW Gold Japan from its economic and financial lethargy jerk. There are many reasons to doubt that Mr. Kan has the charisma and the ability to re-energize the country. He has failed to use the campaign to light on the need for final fantasy gil. He is WOW Power Leveling still not sufficient to achieve rival parties, the Aion Gold parliamentary havebeen overcome, he lost face for the DPJ Upper House majority in July.

  9. #9
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    Quote Originally Posted by Marcus_A View Post
    This applies to a distributed client application calling a stored procedure, but not one stored procedure calling another stored procedure if both stored procedures are on the same physical server. Same applies to a batch program running on the database server.
    In other words, I can't use this feature in a batch DB2 program that calls a DB2 module. I tried it in a batch COBOL program that invoked a COBOL stored procedure. It was pretty disappointing..... due to address space swaps.

Posting Permissions

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