fhg
Volume 9, Number 31 -- October 7, 2009

Passing an Entire Result Set as a Parameter: Part 1

Published: October 7, 2009

Hey, Mike:


Note: The code accompanying this article is available for download here.


We are moving from green screen to PC GUI screens and have come across a need to have the "reverse" of returning a result set. We need to be able to pass a stored procedure of one or more rows (i.e., a result set) in a single call. We could have a single table, but more than one user will be sending data at the same time. It's a heavily used green screen that we will be replacing. The green screen itself is an input capable sub-file. It allows the users to enter any number of transactions up to 9,999 and debits must equal credits.

We would like the GUI application to collect all of these transactions and send them as one set to a stored procedure for processing.

Thanks for any help you can give us.

--Doc


Hi, Doc:

This question seems to be surfacing as more "i" developers leave the green screen world and opt for the client/server paradigm. I recently answered a similar question by demonstrating a technique to do "simulated" array handling using large text parameters and a table function (see Designing DB2 for i Stored Procedures for Simulated Array Handling), but it's not quite the same as passing an entire result set.

The answer to this question would be easier if DB2 for i supported arrays like its sister products do, or if it supported something similar to user-defined table types, which are new to SQL Server 2008. I'm guessing IBM will give us something in this area relatively soon.

Even so, there are a few ways we can approach this problem:

  • Create and populate a temporary table before calling the stored procedure. The stored procedure can then process the rows in the temporary table.
  • Package the entire data set as delimited text and pass it to a stored procedure parameter with a CLOB (Character Large Object) data type where it can be converted back into a row set used by SQL. The CLOB is a good data type choice because it can hold a large amount of data (currently about 2GB).
  • Package the entire data set as XML and pass it to a stored procedure CLOB parameter where the XML can be converted back to a row set that can be used by SQL.

Which approach to take depends on how much development time you have and how easy it is for the client-side developer to perform a certain task, such as packaging text or XML and passing it as a CLOB. As for the first approach, it may be easy to design a stored procedure to work with a temporary table in QTEMP. However, if I can help it, I don't like designing procedures with this kind of dependency. I'd rather pass a parameter instead of remembering to create and populate a table before invoking a procedure. Also, this technique is relatively straight-forward and therefore there's not much to write about it!

Therefore, in this tip I will demonstrate my second suggestion: how to pass CSV text to a generic Java table function that will convert it to a row set. In the next tip, I'll demonstrate how to pass XML text to a special purpose RPG table function program that will parse the text and return it as a row set for use by SQL (requires V5R4 or higher).

Our first problem concerns passing a large amount of data as a parameter. We can use a CLOB data type to handle this problem. The second problem is once we have this data, how do we convert it to a form that a SQL Stored Procedure can use? The answer is a table function.

While this will not be a tutorial on table functions, here is an overview of how they work. A table function is a program (that could be written in SQL, COBOL, RPG, C, etc.) that gathers data into a pre-defined tabular format and gives it back to DB2. Once DB2 has the data in a tabular format, the data from a table function can participate in SQL queries similar to how an actual database table would.

Normally, an SQL query is constructed as follows:

Select * From MyTable

--This tabular data comes from a table (a.k.a., physical file).

A table function, in principle, works like this:

Select * From MyJavaFunction

--This tabular data comes from a program.

Both statements can participate in JOINs, have their results sorted by an ORDER BY clause, etc. A bonus of table functions is that they can accept parameters:

Select * From MyJavaFunction(@MyCSVData)

So in this case, if @MyCSVData has the following text:

GL Account,Debit,Credit
100020,100,0
100030,0,100
100025,250,0
100060,0,250

The text can be converted to a row set by the table function program. So the statement:

Select * From MyJavaFunction(@MyCSVData)

. . . will return this:


GL Account

Debit

Credit

100020

100

0

100030

0

100

100025

250

0

100060

0

250


Because a table function can be used in a query, we can theoretically sum both the DEBIT and CREDIT column to make sure they're equal before allowing the procedure to continue.

The Java program that accompanies this article is intended to accomplish this task. It will use a regular expression pattern to convert a CSV file into a series of rows and columns that it will give back to SQL. Once SQL has the data in a tabular format, it can be dropped into a temporary table or be used directly within a SELECT statement.

To use the Java program as a table function with SQL, the program needs to be registered using the CREATE FUNCTION statement:

Create Function xxxxx/ParseCSVData
(CSVData  CLOB(2M) AS Locator,
StartRow  Int)
Returns Table
(RowId   Int,
Field1  VarChar(400) CCSID 37,
Field2  VarChar(400) CCSID 37,
Field3  VarChar(400) CCSID 37,
Field4  VarChar(400) CCSID 37,
Field5  VarChar(400) CCSID 37,
Field6  VarChar(400) CCSID 37,
Field7  VarChar(400) CCSID 37,
Field8  VarChar(400) CCSID 37,
Field9  VarChar(400) CCSID 37,
Field10 VarChar(400) CCSID 37,
Field11 VarChar(400) CCSID 37,
Field12 VarChar(400) CCSID 37,
Field13 VarChar(400) CCSID 37,
Field14 VarChar(400) CCSID 37,
Field15 VarChar(400) CCSID 37,
Field16 VarChar(400) CCSID 37,
Field17 VarChar(400) CCSID 37,
Field18 VarChar(400) CCSID 37,
Field19 VarChar(400) CCSID 37,
Field20 VarChar(400) CCSID 37,
Field21 VarChar(400) CCSID 37,
Field22 VarChar(400) CCSID 37,
Field23 VarChar(400) CCSID 37,
Field24 VarChar(400) CCSID 37,
Field25 VarChar(400) CCSID 37,
Field26 VarChar(400) CCSID 37,
Field27 VarChar(400) CCSID 37,
Field28 VarChar(400) CCSID 37,
Field29 VarChar(400) CCSID 37,
Field30 VarChar(400) CCSID 37,
Field31 VarChar(400) CCSID 37,
Field32 VarChar(400) CCSID 37,
Field33 VarChar(400) CCSID 37,
Field34 VarChar(400) CCSID 37,
Field35 VarChar(400) CCSID 37,
Field36 VarChar(400) CCSID 37,
Field37 VarChar(400) CCSID 37,
Field38 VarChar(400) CCSID 37,
Field39 VarChar(400) CCSID 37,
Field40 VarChar(400) CCSID 37,
Field41 VarChar(400) CCSID 37,
Field42 VarChar(400) CCSID 37,
Field43 VarChar(400) CCSID 37,
Field44 VarChar(400) CCSID 37,
Field45 VarChar(400) CCSID 37,
Field46 VarChar(400) CCSID 37,
Field47 VarChar(400) CCSID 37,
Field48 VarChar(400) CCSID 37,
Field49 VarChar(400) CCSID 37,
Field50 VarChar(400) CCSID 37,
Field51 VarChar(400) CCSID 37,
Field52 VarChar(400) CCSID 37,
Field53 VarChar(400) CCSID 37,
Field54 VarChar(400) CCSID 37,
Field55 VarChar(400) CCSID 37,
Field56 VarChar(400) CCSID 37,
Field57 VarChar(400) CCSID 37,
Field58 VarChar(400) CCSID 37,
Field59 VarChar(400) CCSID 37,
Field60 VarChar(400) CCSID 37,
Field61 VarChar(400) CCSID 37,
Field62 VarChar(400) CCSID 37,
Field63 VarChar(400) CCSID 37,
Field64 VarChar(400) CCSID 37,
Field65 VarChar(400) CCSID 37,
Field66 VarChar(400) CCSID 37,
Field67 VarChar(400) CCSID 37,
Field68 VarChar(400) CCSID 37,
Field69 VarChar(400) CCSID 37,
Field70 VarChar(400) CCSID 37,
Field71 VarChar(400) CCSID 37,
Field72 VarChar(400) CCSID 37,
Field73 VarChar(400) CCSID 37,
Field74 VarChar(400) CCSID 37,
Field75 VarChar(400) CCSID 37,
Field76 VarChar(400) CCSID 37,
Field77 VarChar(400) CCSID 37,
Field78 VarChar(400) CCSID 37,
Field79 VarChar(400) CCSID 37,
Field80 VarChar(400) CCSID 37
)
External Name 'ParseCSVFile.getCLOBData'
Language Java
Parameter Style DB2General
Disallow Parallel
No SQL
Fenced
ScratchPad
Final Call
Returns Null On Null Input

The function will be called ParseCSVData and will be used in a DB2 Select as follows:

Select *
  From Table(ParseCSVData(
'GL Account,Debit,Credit
100020,100,0
100030,0,100
100025,250,0
100060,0,250',2)) Data

Notice that DB2 requires the special TABLE keyword to be used when invoking a table function.

The function's first parameter accepts a large character object and the second parameter accepts a row number to start with (which is useful in case you want to skip the heading row). As implemented above, the function will return up to 80 CSV columns with a maximum of 400 characters in each column (DBCS characters are not allowed). Columns that are not populated will return NULL. Each column will be named generically as well (FIELD1-FIELD80) since DB2 table functions don't support a way to do late column binding (at least not that I know of). So the trade-off here is the ability to use an all-purpose function to handle many situations versus having the benefits of a specific function for returning a strong data type for each column in the CSV file.

However, we can "mimic" a strongly typed set by adding a wrapper around the function as follows:

Select Cast(Field1 As Char(10)) As GL_Account,
       Cast(Field2 As Dec(11,2)) As Debit,
       Cast(Field3 As Dec(11,2)) As Credit
  From Table(ParseCSVData(
'GL Account,Debit,Credit
100020,100,0
100030,0,100
100025,250,0
100060,0,250',2)) Data

Of course this extra work is the price we pay for generic data conversion. Also note this method will not handle bad data within the CSV file. If there is character data in the credit column (such as 100CR) then additional logic will need to be employed to strip these characters before converting the column to numeric. One other consideration of using this technique is that the initial startup of the Java Virtual Machine (JVM) can be costly from a performance perspective.

Compiling the Java Program

The compile instructions for the Java program are included in the header. Basically, when the Java program is compiled for use as a table function, the class file needs to be stored in this special location on the IFS: /qibm/userdata/os400/sqllib/function.

If you place the Java source in folder /mysource, the Java compile command would look like this:

javac -d /qibm/userdata/os400/sqllib/function /mysourcefolder/ParseCSVFile.java

And in case you didn't know, the javac command is accessed via the QSHELL environment, so issue the STRQSH command first and then enter the above javac command within QSHELL.

To learn more about the ins and outs of how to create and register a Java table function, see the section on Java SQL Routines in the IBM Developer Kit for Java.

While this technique is very useful, it does have the disadvantage of being somewhat brittle. If the CSV file is constructed wrong on the 30,000th row, you won't know until you've processed all the preceding rows. Additionally, the function's columns are not strongly typed. This means that although we may think that column two of our example will always have numeric data, it in fact may not. Because the function initially returns everything it encounters as a string, the function may inadvertently let some bad data through (i.e., data that isn't compatible with its intended data type).

The next tip will demonstrate how to pass XML data (requiring V5R4 or higher) to an RPG table function that will return a strongly typed result set. Using this technique will be more work than the generic Java function, but the resulting table data coming from the RPG function will be named accurately and strongly typed, which is a worthwhile investment.


Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.


RELATED STORY

Designing DB2 for i Stored Procedures for Simulated Array Handling



                     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

Manta Technologies:  Fall Sale on i training courses! Order by October 15 and SAVE 25%
ARCAD Software:  Testing=Productivity=Profits. Online presentation, October 22
RJS Software Systems:  RPG2SQL Integrator shares information between IBM i and PC databases


 

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
Moore's Law and the Performance Wall

iManifest Begins i Marketing Fundraising in the States

SafeData Makes Backup and Recovery Deal with Omni Solutions

Mad Dog 21/21: CIO, Get Out Of That Glass House

Ellison Wants Oracle to Be IBM 1.5

Four Hundred Stuff
No Longer an IBM Partner, Randr Navigates the Open Source Track

Krengel Tech Eases XML Integration with DB2 Web Service Enabler

Aldon Eases Compliance, Project Management Burdens with Reporting Tool

QlikTech Welcomes Competition with IBM Cognos Express

Wine Management Systems Takes to the Cloud

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

System i PTF Guide
October 3, 2009: Volume 11, Number 40

September 26, 2009: Volume 11, Number 39

September 19, 2009: Volume 11, Number 38

September 12, 2009: Volume 11, Number 37

September 5, 2009: Volume 11, Number 36

August 29, 2009: Volume 11, Number 35

August 22, 2009: Volume 11, Number 34

TPM at The Register
Mellanox jacks up revenue and profit guidance

IBM readies Exadata killer

How Perot Systems played hard to get with Dell

Chinese IT shops love the free-ness of open source

Techies suffer as US unemployment inches up

Oak Ridge goes gaga for Nvidia GPUs

Oracle cuts database tags for Sparc T2+ servers

Unisys takes services to the desktop

TPC slaps Oracle on benchmark claims

Cisco preps Nexus switches for third-party blades

Newegg hatches IPO

Microsoft, nVidia tag team on HPC

THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
Halcyon Software


Printer Friendly Version


TABLE OF CONTENTS
Getting Started with RDi's Application Diagram, Part 1: Source Call Diagram

Passing an Entire Result Set as a Parameter: Part 1

Admin Alert: The Great CBU Survey and More

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-2009 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement