• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • DB2 for i: Process Stored Procedure Result Sets as Cursors

    August 25, 2010 Michael Sansoterra

    It eventually happens to every developer. You write a stored procedure to return one or more sophisticated result sets to a Java or .NET client. Everyone is (temporarily) happy. Eventually, a need arises for an RPG or COBOL program to execute this stored procedure and process the result set(s). But, alas, high level language programs have no way to open and process a result set (except when using the cumbersome CLI API).

    Phooey, that means we’ll need to duplicate the stored procedure code in an RPG friendly manner! But wait, here comes DB2 for i 7.1 to the long-anticipated rescue! There are new SQL language features that allow this amazing feat to be done.

    Let’s get right to the heart of the matter. How does one process stored procedure result sets from an embedded SQL program? Consider the following simple stored procedure that returns two result sets (a product list and a customer list):

    CREATE OR REPLACE PROCEDURE sp_Lists
    LANGUAGE SQL
    RESULT SETS 2
    BEGIN
    
     DECLARE ProductList CURSOR FOR
      SELECT ProductId,Name || 
             '('||ProductNumber||')' AS Description
        FROM Product
    ORDER BY Name
    FOR READ ONLY;
    
     DECLARE CustomerList CURSOR FOR
      SELECT C.CustomerId,S.Name
        FROM Customer C
        JOIN Store S ON S.CustomerId=C.CustomerId 
       WHERE C.CustomerType='S'
    FOR READ ONLY;
    
    OPEN ProductList;
    
    OPEN CustomerList;
    
    END;
    

    And here’s an RPG program that will execute the procedure, open the result sets as cursors, and block fetch the first 500 rows from each cursor into data structure arrays:

     // Section A
     // SQL Pre-compiler will convert these definitions to 20I 0
    DrsProducts       S                   SQLTYPE(RESULT_SET_LOCATOR)
    DrsCustomers      S                   SQLTYPE(RESULT_SET_LOCATOR)
    
      // These data structures are used for block fetching data from cursors
    DdsProducts       DS                  Qualified Dim(500)
    D  ProductId                    10I 0
    D  ProductName                  50    Varying
    
    DdsCustomers      DS                  Qualified Dim(500)
    D  CustomerId                   10I 0
    D  CustomerName                 50    Varying
    
      /Free
         // Section B
        // Invoke procedure that has two result sets
        Exec SQL
            CALL sp_Lists;
    
         // Section C
        // Associate a "locator" variable with each result set from the
        // procedure.  A locator variable is like a pointer that keeps
        // an RPG reference to the actual result sets.
        //
        // NOTES:
        // Procedure name can be a variable
        // A SPECIFIC PROCEDURE name can be coded as well
        Exec SQL
            ASSOCIATE RESULT SET LOCATORS
            (:rsProducts, :rsCustomers) 
            WITH PROCEDURE sp_Lists;
    
        // Create cursors from result sets
        Exec SQL
            ALLOCATE Products CURSOR
            FOR RESULT SET :rsProducts;
        Exec SQL
            ALLOCATE Customers CURSOR
            FOR RESULT SET :rsCustomers;
    
         // Section D
        // Read from cursor using block fetch
        // (Cursor doesn't need to be explicitly opened)
        Exec SQL
            FETCH Products FOR 500 ROWS INTO :dsProducts;
        Exec SQL
            CLOSE Products;
        // Repeat for 2nd result set
        Exec SQL
            FETCH Customers FOR 500 ROWS INTO :dsCustomers;
        Exec SQL
            CLOSE Customers;
    
        *InLR=*On;
        Return;
     /End-Free

    I have four special sections highlighted in the program that detail specifics:

    Section A–Declare variables as special RESULT SET LOCATORS.

    Each result set that needs to be processed within an RPG program must have a specific “handle” or “pointer” defined in order to maintain a reference to the cursor that is under the control of the database engine. The SQLTYPE keyword is only valid in embedded SQL programs and is used to define a variable as a specified SQL type. (This keyword is used in cases where RPG has no direct data type equivalent to an SQL data type.) RESULT_SET_LOCATOR is a special DB2 data type specifically used for the special case of referencing a result set as a cursor in a HLL program.

    Section B–This recognizable code simply invokes the stored procedure.

    Section C–This code marries the stored procedure and HLL worlds together. The ASSOCIATE RESULT SET LOCATORS statement is called to give RPG the “handles” or “references” to the result sets that the stored procedure is expected to return. Notice the variables specified here match those defined in Section A.

    Next, the ALLOCATE CURSOR statement is used as a quasi-cursor declaration statement. This statement uses the newly retrieved result set “locator” variable to make a result set available as a local cursor. This statement is executed twice–once for each result set left open by the stored procedure.

    Section D–Process and close the cursors.

    Once the result sets have been “allocated” as cursors, they can be used like a normal cursor within the program. When ALLOCATE CURSOR is used, an explicit OPEN cursor statement is not required (because technically the stored procedure itself opens the cursor). So at this point, the program continues on with fetching the data. In this case, instead of looping through the rows I just dumped the first 500 rows into a data structure array using a block fetch.

    Notice that there are no options such as SCROLL and SENSITIVE specified on the ALLOCATE CURSOR statement. This is because ALLOCATE CURSOR simply accepts how the cursor is defined in the stored procedure. If a stored procedure opens a cursor that is scrollable and updateable, the RPG program can scroll through and update the cursor (once it has been allocated).

    And of course you’ll want to add appropriate error handling code to your RPG program.

    The ASSOCIATE LOCATORS and ALLOCATE CURSOR statements are only allowed in embedded SQL programs. Unfortunately, they cannot be used in an SQL routine that utilizes the SQL programming language (which would’ve come in handy). I have not seen a need to use the FREE LOCATOR statement (used for cleaning up LOB locator references) to free the RESULT_SET_LOCATOR data types. Once the cursor has been closed the resources should be released by the database engine.

    A great new feature in DB2 for i 7.1, the ability to do row based processing on a stored procedure result set, allows HLL embedded SQL programs to take advantage of a powerful and oft-used stored procedure feature.

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Bytware:  Try StandGuard Network Security FREE for 30 days
    DRV Technologies:  SpoolFlex transforms & distributes System i reports as PDF & Excel in REAL time!
    COMMON:  Join us at the Fall 2010 Conference & Expo, Oct. 4 - 6, in San Antonio, Texas

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    ACOM to Throw In Free Printer on Software Sale MKS Offers Full Support for IBM i 7.1 in ALM Tool

    2 thoughts on “DB2 for i: Process Stored Procedure Result Sets as Cursors”

    • Tim Rhoades says:
      October 11, 2018 at 2:40 pm

      Is there a way to determine how many rows were returned by a stored procedure? I’ve tried using db2_number_rows and sqlerrd(3) but so far I haven’t been able to get the number. I’m building a dynamic array with the results returned however, I need to know the row count to be able to do that.

      Reply
    • Mark Crawford says:
      November 19, 2020 at 4:28 am

      I know this article is ten years old now, but still one of the best examples I’ve seen for doing this, so thank you for that ! I have everything set up and running, however the associate result set locator fails with “procedure not previously called” (and I have made sure to specify the name exactly the same as on the call). The procedure is being called and is returning a result set. I was wondering if you might have encountered this issue.

      Thank you !

      Reply

    Leave a Reply Cancel reply

Volume 10, Number 25 -- August 25, 2010
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
System i Developer

Table of Contents

  • An Introduction to Python on IBM i, Part 1
  • DB2 for i: Process Stored Procedure Result Sets as Cursors
  • Admin Alert: One Year Out–Preparing for Your Next Power IBM i Upgrade

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It
  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.