©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
- 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
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.
Kent Milligan is a DB2 UDB Technology Specialist in PartnerWorld for Developers, iSeries. Kent spent the first eight years of his IBM career as a member of the DB2 development group in Rochester. He speaks and writes regularly on various iSeries & AS/400e relational database topics. Kent is a software engineer at the IBM Rochester Lab. You can reach him at email@example.com.