Reporting is an important feature of any information management system. The AS/400 was well known for reporting during the mid-90s. Since then, IBM has added many new advanced features to the server's reporting suite. But the third-party reporting tools in the open system environment are much more flexible in terms of their GUI and other capabilities. However, there are a few things to be aware of when using those tools to get data from iSeries-AS/400 and your solutions.
Problem
Lots of shops work with multiple databases such as Oracle, SQL Server and DB2/400 for different purposes. For example their merchandise system may run on an iSeries, their sourcing may run on an Oracle-based system, and their financial package may run on SQL Server. To generate a demand and forecast report, they have to take data on all of these databases at once. In such cases, reporting tools such as Crystal Report and Actuate can help accomplish that. In addition, features such as creating formatted Excel reports, XML and drag-and-drop capabilities also attract users.
All of these reporting tools use ODBC as their key weapon to get the data from different database. With iSeris Access ODBC driver installed, you can generate reports using these reporting tools. As long as only data from tables are to be fetched and simple queries are used, these tools are very efficient.
However, often users experience issues such as the following:
- Very complex business logic needs to be put in the program to generate the report.
- An existing report on the iSeries needs to be converted into Crystal Report.
- The performance of the ODBC does not give results in expected time.
- Because of un-tuned SQLs, many times access paths start building on the huge files and lock up the table for considerable time.
Solution
The solution is stored procedures. DB2/400 is capable of creating SQL stored procedures, but it creates a CLI...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
code, which again uses the embedded SQL and may take time to run if proper query tuning was not done.
In such cases, we have to look back into an efficient high-level language for help. On the iSeries what else is more efficient than RPG when it is the matter of I/O and logic handling?
Many times we already have reporting programs that have lots of logic embedded in them and are difficult to replicate into SQLs. And we don't want to write fresh stored procedures out of such badly written programs. Thus an external stored procedure is the ultimate solution.
How to implement an external stored procedure
Identify the program
If you have existing programs that create a RLU report using RPG, then it is essential to look into these programs in detail, as these programs can be used for your stored procedure.
First identify the program that writes to the work file before the output is written to the printer file.
If no intermediate work file is used in the program, then you have to replicate the program and you have to do a few code changes to write to a physical file instead of the printer file. A few days of analysis will be OK to identify such codes.
Write a wrapper program
There is a chance that several people may run the same report. In that case, the work files need to be overridden to the QTEMP and cleared before the actual program is called.
The output of the wrapper program should be a cursor that will be used by the stored procedure as result set. Hence, it is essential that the wrapper program is a SQLRPGLE/SQLCBLLE/SQLC.
The last statements of the wrapper program should open a cursor that reads the record from the final file. Please follow the code in Table 1.1.
Table 1.1: Sample code for the wrapper program (RPGWRAP')
Create a stored procedure
The essential step to bridge the open system with the iSeries program is the stored procedure. This procedure will be an external stored procedure.
On STRSQL
This statement creates a stored procedure MYSTRPRC in library MYLIB that calls the program MYLIB/RPGWRAP and returns one result set.
Authorization
Give proper authorization to your program (Compile with *Owner) so that it does not create any problems while accessing the files.
ODBC DSN configuration
Assuming that you are familiar with the DSN configuration for Client Access ODBC Driver, let me explain a few things that are important from the stored procedure point of view. The important thing that you have to take care with during ODBC connection is the connection type (In Server Tab). Check Read/Call here.
But many times an error occurs when report tools try to access the stored procedure using these ODBC DSNs. It throws an error message "Statement violates access rule: connection is set to read only".
In that case, please check your registry.
- Close all applications that use the DSN
- Click the Windows Start button and then click Run. Type "Regedit" and click 'OK'.
- Display all current ODBC DSNs by navigating to HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI.
- Click the folder with the name of your iSeries DSN.
- Right-click in the right window pane, click 'New' and then click 'String value'.
- Name the new String value "AllowProcCalls".
- Right-click 'AllowProcCalls' and click 'Modify'.
- Assign the 'Value Data' to "1".
- Click 'OK' and then close Registry Editor.
Check the output
The most essential part now is to check the output of your stored procedure. It is not possible to check the output of the stored procedure in the green screen, so you have to open your iSeries Navigator. Goto Database->Run SQL Script. In the script window, just say Call MYLIB. MYSTRPRC(); and then run it.
[IMAGE]
Table 1.2: Run a SQL Script
If you can see your desired output in the bottom window, then you are done and the stored procedure is ready to be used. Otherwise, check your program again.
How to use it in Reporting Tools
In the reporting tools, select the stored procedure as the data source and use it as if you are using the table.
Conclusion
Stored procedures are also helpful when migrating applications to Java. When you migrate to Java, it is better not to replicate the code if possible. External stored procedures play an important role when you want to re-use existing RPG programs. They save time and help increase performance.
---------------------------
About the author: Jagannath Prasad Lenka is a programmer/analyst at Infosys Technologies Ltd. He has worked as programmer and senior programmer on the iSeries for this company for the past five years.
