Packaging SQL Procedures into iSeries Applications

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

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.



12 November 2001

©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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


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

All information submitted is secure.

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.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=14295
ArticleTitle=Packaging SQL Procedures into iSeries Applications
publish-date=11122001