Passing an Entire Result Set as a Parameter: Part 1
October 7, 2009 Hey, Mike
|
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:
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:
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
|
