Restoring Procedures to a Development Machine

Hey, Howard:

My company is building stored procedures on our test AS/400. When we first attempted to transmit stored procedures from the test AS/400 to a production AS/400, one of the programmers noted that when he saved the object and moved it to the production environment, the procedure automatically was registered in SQL. When I sent one set of new stored procedures it did not get registered. The second time I sent a set along with a program to define the procedure. It got created twice in sysprocs; I believe the specific_names matched, one routine_name matched the specific_name, and one routine_name was system-generated. The third time I sent the program, I thought I made a mistake the first time and I sent just the program once again; the stored procedure was not registered. I believe all three times, the program was saved as a savf and FTPed to the target environment. My question is, when we transfer programs, what needs to be done to register a stored procedure in sysproc on the target environment?

--Rock

This is a great question, Rock. It took me a while to figure it out, and I love a mystery! To figure this one out, I had to poke, prod, and observe the behavior of the iSeries, and call in a few favors from my secret contacts at IBM. Here is the skinny.

You want to put a stored procedure on a production machine, so you save the procedure to a save file. If the stored procedure is declared on the save from iSeries, and it is an ILE *PGM object, and it contains at least one SQL statement, then when you restore it the iSeries will automatically try to create the entries in the SYSROUTINES and SYSPARMS tables in QSYS2 to register the procedure. However, there are some conditions:

• The target program must not live in the QSYS or QSYS2 library.
• The target program must be an ILE *PGM object.
• The target program must contain at least one SQL statement.

So if the external program is an OPM or ILE program with no embedded SQL, you must manually register the program with a CREATE PROCEDURE statement, via SQLThing, STRSQL, or the Operations Navigator environment.

But here is some additional information for you to digest. DB2 will be registered in the library that you restored the program into, not the original library on your development machine. So, say that you create a program in library FOO of the development machine and issue a create procedure statement defining the procedure as FOO1, living in library FOO. You then create a save file and back up the procedure to the save file and FTP to the development machine and restore the program to library BAD; the procedure will be registered in SYSROUTINES as FOO1 living in library BAD, not FOO1 living in library FOO, as on the development machine. I think this is a good thing.

However, say you already have a procedure FOO1 living in library BAD on the target machine. If the AS/400 finds a procedure definition with the matching signature (i.e., matching number and data types for all parameters to the procedure), the program FOO will be restored by the catalog information in SYSROUTINES and SYSPARMS will not be updated. This is hunky dory as long as the CREATE PROCEDURE definition did not change, but if it did it is your responsibility to issue the DROP PROCEDURE and then CREATE PROCEDURE to get the procedure correctly defined in the system catalogue.

Furthermore, if an existing procedure named FOO1 is found, but the signatures do not match, (i.e., number of parameters or data type of parameters is different), the object will be restored and the SYSROUTINES and SYSPARMS entries will be updated.

What's It All Mean, Mr. Natural?

You need to be really careful when moving procedures from development to production, as you could overlay an existing program object and render the existing programs that call the procedure unusable. Always run a query over the SYSROUTINES table on the target machine to determine which procedures are declared, and what their external objects and signatures are, before restoring procedures to the target, and do not count on the iSeries to keep up with this for you automatically.

I tend to do this myself, and pay particular attention to the SPECIFIC_NAME of the procedure versus the ROUTINE_NAME of the procedure. Say I have a procedure called SAFEPCT in library SQLBOOK that takes two decimal(10,2) numbers as arguments. Then I create a PROCEDURE called SAFEPCT in library SQLBOOK that takes three integer arguments. The first procedure will have ROUTINE_NAME of SAFEPCT and the SPECIFIC_NAME of SAFEPCT; whereas the second procedure will have the ROUTINE_NAME of SAFEPCT and the SPECIFIC_NAME of SAFEP0001. See, the AS/400 allows for overloading of procedures; this means that you can have several procedures with the differing numbers of arguments that have differing data types. The iSeries will choose the correct procedure at runtime based on the number and data type of the arguments that you pass to the stored procedure. This is cool, but can be a pain when you are trying to manage procedures and functions on your iSeries.

I always run SQL statements over the system catalogue tables to look at the procedures on the target iSeries and DROP and recreate the procedure definitions as necessary and do not rely on the restore getting it correct. The following query may be helpful:

SELECT SPECIFIC_NAME, SPECIFIC_SCHEMA, 
ROUTINE_NAME, ROUTINE_SCHEMA, EXTERNAL_NAME 
FROM QSYS2.SYSROUTINES 
WHERE ROUTINE_TYPE='PROCEDURE'

The above SQL statement will list your stored procedures, where the definition lives, and where the external object lives.

For more information on procedures, see the Stored Procedures and Triggers on DB2 Universal Database for iSeries Redbook from IBM or go to my Web site, www.sqlthing.com.

Howard F. Arner, Jr., is a writer and consultant with Client Server Development, and author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities. You can purchase a copy of Howard's book or learn more about SQLThing at www.sqlthing.com. Send your SQL questions to harner@sqlthing.com or go to www.sqlthing.com to find out more about SQL on the iSeries.

Sponsored By PROFOUND LOGIC SOFTWARE

RPG Smart Pages (RPGsp) makes it simple! Web / Browser Development on the iSeries has never been easier! There is no need to struggle with web development. Do it the easy way! Do it with RPGsp! RPGsp lets you develop web applications quickly using the skills you already have. RPGsp is a complete development environment for creating flexible, reliable, and fast browser applications. It lets you combine native ILE RPG logic with HTML content. The learning curve is minimal! Here is how it works . . . 1. Wizards can generate working applications for you. 2. To alter functionality, change or add RPG code. 3. To change the application's look, use an integrated point-and-click HTML designer. 4. Test & deploy with the click of a button. RPGsp lets you reuse existing RPG code in many different ways, including binding to modules and importing the code directly into the RPGsp Environment. Advanced RPG editing and analysis features are part of the development environment. You will be creating applications that are guaranteed to be faster than Java, ODBC, Code Generators, and CGIDEV. RPGsp applications do not consume interactive resources! For a Free Trial or more information, contact Profound Logic Software at www.ProfoundLogic.com.