fhg
Volume 10, Number 25 -- August 25, 2010

DB2 for i: Process Stored Procedure Result Sets as Cursors

Published: August 25, 2010

by 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


Sponsored By
WORKSRIGHT SOFTWARE

Do you need area code information?
Do you need ZIP Code information?
Do you need ZIP+4 information?
Do you need city name information?
Do you need county information?
Do you need a nearest dealer locator system?

We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

Just call us and we'll arrange for 30 days FREE use of either
ZIP/CITY or PER/ZIP4.

WorksRight Software, Inc.
Phone: 601-856-8337
Fax: 601-856-9432
E-mail: software@worksright.com
Web site: www.worksright.com


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

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


 
The Four Hundred
IBM Ducks i Pricing on Most Entry Power7 Servers

BladeCenter S Express i Edition Gets a Power7 Upgrade

The Power 795: Cheaper Performance, Expensive Software

As I See It: The Once and Future HP Way

An Encryption Horror Story

Four Hundred Stuff
PHP and JavaScript Come Together in Zend Studio 8

SafeStone Taps RSA for SIEM Expertise

SkyView Gets Tough on User Profiles

Profound Updates I/O Handler for RPG Open Access

IGEL Adds 5250 Emulation to Linux Thin Clients

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
August 7, 2010: Volume 12, Number 32

July 31, 2010: Volume 12, Number 31

July 24, 2010: Volume 12, Number 30

July 17, 2010: Volume 12, Number 29

July 10, 2010: Volume 12, Number 28

July 3, 2010: Volume 12, Number 27

TPM at The Register
US puts $30bn of IT projects up for review

AMD nabs ex-Intel techie as server CTO

Oracle names self virtualization king

Big biz loved Dell servers and storage in Q2

Mobile PC buyers buying peppier boxes

HP rings up Hurd's final quarter

HP hires headhunter to replace Hurd

AIX 7.1 moves forward to Power7 iron

Amazon challenges cloudy startups

IBM whips out its TPC-C...cluster

SGI previews Q4 financials

IBM completes Power7 server arsenal

THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
System i Developer


Printer Friendly Version


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

Four Hundred Guru

BACK ISSUES




 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2010 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement