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:
SELECT qryMULTIResult.*
INTO tblSPResults
FROM qryMULTIResult;
- 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.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online