There are a few options for reviewing the result sets returned by a stored procedure. One option is to use the SQL script utility that comes with iSeries Navigator. Each result set from the query will get its own grid that you can review. This is a good option, although when dealing with a large amount of data, it becomes difficult to review and compare data in the result sets. A better option is to dump the result sets into individual database tables so they can be queried. There are many ways to do this, such as duplicating the stored procedure and modifying it to dump the result sets to DB2 tables. But I never like to maintain a separate test copy of any program, if I can help it.
Microsoft Access offers an easy way to run a stored procedure and dump the results to Access tables, which can then be easily reviewed and queried. I've found this technique to be an extremely useful and easy way to get troubleshooting underway in a hurry. All it requires is an ODBC DSN configured to talk to your System i database (DB2 for i5/OS).
Here are the steps to capture the result sets in Access:
- Create a new, blank Access database (.mdb).
- Create a New Query in Design View.
- Close the Show Table window and choose Query -> SQL Specific -> Pass-Through Query. Selecting a pass-through query will only allow query design in the SQL designer window. Pass-through queries require that the SQL statement be entered in the syntax of the back-end database (in this case, DB2 for i5/OS) instead of the Access' SQL dialect. For instance, if you have a stored procedure called MULTIRESULT with no parameters, then enter CALL MULTIRESULT. If you have parameters, then you need to include them just like you would in a native AS/400 or System i interface such as STRSQL. For example: CALL MULTIRESULT ('ABC',3)
- Choose View -> Properties to display the Properties box. There are two things to accomplish here: set the ODBC Connect Str and ODBC Timeout properties. ODBC Connect Str is supposed to point to an ODBC DSN with the following syntax (assuming a DSN is defined on your machine called AS400): ODBC;DSN=AS400. Optionally, you can specify a user name and password here to avoid a potential sign-on prompt (although the password is stored in plain text). Further, any number of DSN settings can be overridden, including commitment control level, naming convention, library list, etc. A comprehensive list of all of the connection string properties for the iSeries Access ODBC driver can be found here.
- If you have a long-running procedure, change the ODBC Timeout to the number of seconds (the default is 60); otherwise, you may receive an SQL0666 error message, which states that the estimated query processing time exceeds the time limit of 60 seconds.
- Choose File -> Close. When prompted, save the query as qryMultiResult. Your query should appear in the design window with a globe icon indicating the query type is a pass-through query.
- Create a second query in design mode. Choose View -> SQL View to put the query in SQL mode. (Since this is not a pass-through query, the syntax for this query should follow Access' SQL rules.)
- In the query window, paste the following SQL statement:
- In Access parlance, the INTO clause classifies this statement as a "Make Table" query (similar to a CREATE TABLE / SELECT WITH DATA in DB2). When this statement runs, it will dump the results of the qryMultiResult pass-through query into a table called tblSPResults. However, since the pass-through query returns multiple result sets, Access does us a favor by automatically creating additional tables for each additional result set with the same base table name suffixed with a number (tblSPResults1, tblSPResult2, ...tblSPResultsn, where n is the number of result sets returned by the stored procedure minus 1).
- Choose File -> Close. When prompted, save the query as qryCreateTables. If your stored procedure is long-running, this may take a while as Access may run the procedure in an attempt to figure out the column information that it returns. Alternatively, instead of closing the query, you can run it by choosing Query -> Run. When the query runs, it will create one or more tables, depending on how many result sets are in your stored procedure. You can then save the query definition after the statement has executed. However, you always run the risk of losing your work if the process crashes before saving it first.
This technique will work in Access 2000 and higher. It may work in Access 97, but I haven't tried it.
One last thing: Access often caches the current connection to the database. Therefore, if you adjust your ODBC connection string, the change may not be picked up right away. I've found that re-starting Access is the best way to proceed after tinkering with DSN changes after Access has already created an initial ODBC database connection.
And that's all there is to it. Once your tables are created, you can easily use Access' powerful query, filter, and sort capabilities to examine the results of your procedure. The two minutes it takes to make this connection will likely save you quite a bit of time when testing and debugging complex multiple result set stored procedures.