©Copyright International Business Machines Corporation 2001. All rights reserved.
DB2 UDB for iSeries SQL Stored Procedures provide capabilities that make it very easy for software developers to create and develop stored procedures within applications. A more complex part of SQL Stored Procedures involves figuring out how to package these procedures within the existing application software and install process, without requiring customers to buy and install extra software. While the SQL stored procedure could simply be re-created on the customer system, that would require the customer to purchase additional IBM software. In this paper, we'll examine a cost-effective alternative.
When a stored procedure is created, DB2 UDB for iSeries generates a C program object that implements the logic specified in the procedure definition and registers the SQL procedure definition in the appropriate catalog tables (SYSPROCS, SYSROUTINES, SYSPARMS). The creation of the C program object requires the DB2 Query Manager and SQL Development Kit for iSeries to be installed on the server (releases prior to V5R1 also required the ILE C compiler). This C program object has an internal flag, so that DB2 UDB for iSeries can recognize it as a program associated with an SQL stored procedure. This internal flagging is the key to allowing you to include an SQL stored procedure with your software package without requiring the customer to buy the SQL Development Kit (which is not installed on most iSeries & AS/400e" customer systems).
Most software packages provide the customer with a CD containing the executable program objects that must be restored and installed onto the customer's system. The C program associated with an SQL Procedure object can be included in this group of program objects, but before doing this you need to understand how DB2 UDB for iSeries processes this specially tagged C program on a restore operation.
Here are the steps (as of V5R1) that DB2 UDB for iSeries goes through to automatically restore a tagged C program object as an SQL stored procedure:
- If DB2 UDB does not find a matching procedure in the catalogs (SYSPROCS, etc), then the C program is registered as an SQL stored procedure by adding a new entry to the catalog. This would be the case the very first time an SQL stored procedure is restored onto the customer system.
- If one procedure with the same name (differences in parameters are ignored) is found in the catalog by DB2 UDB, then the catalog entries for the existing procedure are dropped. And the restored program object is registered as an SQL stored procedure by adding catalog entries. In this case, DB2 UDB for iSeries assumes that you are installing (or restoring) a new version of your SQL stored procedure onto the customer's system -- thus, allowing the catalog entries for the existing procedure to be essentially overwritten.
- If DB2 UDB finds one or more procedure in the catalogs with the same name and a different signature (i.e., parameters that don't match in number or type), then the restored program will be registered as a procedure with the same name and possibly overlay the program object for the existing stored procedure. When the stored procedure's parameter signature has changed it is probably best to drop the existing procedure before the restore operation. Please note that when dropping a procedure from a system that has multiple procedures with the same name, the DROP PROCEDURE request must include the parameter specifications so that DB2 UDB for iSeries knows which stored procedure to drop. For example, DROP PROCEDURE myschema.proc1(INT,CHAR()).
This automatic registration of a C program object as an SQL stored procedure should make packaging SQL stored procedures into your application a snap, now that you understand the rules and process. And the best part is you don't have to convince your customers to buy extra software to support your application.