The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

Packaging SQL Procedures into iSeries Applications

Kent Milligan (kmill@us.ibm.com), DB2 UDB Technology Specialist, IBM PartnerWorld for Developers, iSeries

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 kmill@us.ibm.com.

Summary: This article explains a cost-effective way to package SQL procedures that does not require additional software.

Date: 12 Nov 2001
Level: Introductory

Activity: 4751 views
Comments:

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.

About the author

Close [x]

Report abuse help

Report abuse

Thank you. This entry has been flagged for moderator attention.

Close [x]

Report abuse help

Report abuse

Report abuse submission failed. Please try again later.

Close [x]

developerWorks: Sign in

All information submitted is secure.

Close [x]

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.