Midrange Guru

We are having problems resolving calls from the Web via ODBC to ILE RPG programs on the iSeries. The IBM tech we talked to yesterday recommended we have external SQL procedures defined for any call. I added one through Operations Navigator and the call worked fine after that. He indicated that using Operations Navigator wasn't the best way to maintain those, and that when you change and recompile the RPG you have to rebuild the SQL definition. I know the definitions are stored in SYSPROCS and SYSPARMS. He recommended some other method of entering these, and to save them to another file somewhere. What do you use for maintaining SQL procedure definitions?

Wrapping your RPG programs in an SQL procedure is a good idea because it gives you the ability to better match parameter types and to perform conversion of data types if necessary. I generally use Operations Navigator to maintain external SQL procedure and user-defined function (UDF) definitions. Use the SQL script running in Operations Navigator to enter the source for and to create SQL procedures. To access the SQL script runner in Operations Navigator, expand your databases, right-click system name, and select Run SQL Scripts.

The V5R2 release of Operations Navigator has a neat feature that allows you to generate and export the source of SQL procedures to a file or the SQL script runner. That feature is not limited to SQL procedures; it also supports UDFs, tables, indexes, views, constraints, and more.

To use the SQL statement generator, your iSeries should be at V5R2, although you can use the V5R2 client against a V5R1 system. If you are still using V5R1, you might want to check IBM's iSeries Access end-of-service dates and start planning a move to V5R2.

With the V5R2 Operations Navigator client installed, bring up a list of database objects for the library that contains your SQL procedures. Figure 1 shows the database library view for a library on my system. If the library containing your SQL procedures does not come up in the list of libraries, right-click libraries, then click Select Libraries to Display.

From the list shown on the right panel of Operations Navigator, right-click a procedure and select Generate SQL. The Generate SQL dialog allows you to save the SQL statement used to generate a database object. Just click the Write to file radio button and specify an appropriate source file on your iSeries system. I generally save my SQL source to a QSQLSRC source file. Once your procedure is in a source file, you can use the Run SQL Statement (RUNSQLSTM) command on your system to recreate your procedures.

That's all there is to it. If you would rather perform these steps from a green screen, you can key your SQL procedure source into a source file and bypass Operations Navigator altogether, but the convenient new SQL features of Operations Navigator make it a better choice.