Saving and Restoring External SQL Routine Definitions

Published: January 30, 2008

Hey, Mike:

A vendor whose software we use separates objects into two main libraries--a data library and an object library. I had to restore all the data, so I deleted the data library and restored it. After that, the application stopped working. The problem turned out to be that some stored procedures that they created in their data library got wiped out, and the Restore Library (RSTLIB) command didn't put them back. The story ends happily because the vendor had a program in place for just such emergencies, but (and I'm sure IBM disagrees), I consider this a big old bug in the SAVE/RESTORE command. If I restore a library, I want everything in the library restored.

--Joe

Well this is indeed a thorny problem. First let's review what's happening here:

• The software has logic existing in program and/or service programs, presumably written in some high-level language such as RPG, C, or COBOL.
• To make these programs available to the SQL world, the vendor executed the external version of the CREATE PROCEDURE SQL statement, which allows SQL to run these programs.
• These external SQL routine definitions, whether they be stored procedures or user-defined functions, are stored in the SQL system catalogs in library QSYS2. Essentially these definitions are merely "pointers" telling DB2 for i5/OS how it can make use of high-level language (HLL) programs.

And now a problem arises. Since these definitions are more or less pointers, not i5/OS objects themselves, how should IBM handle saving and restoring them? The answer IBM came up with is to associate the external SQL routine definition with the corresponding HLL program or service program object. When the program object is backed up, the SQL routine definition is captured as well. Subsequently when the program object is restored, the SQL routine definition is restored. Therefore, restoring the library will not restore the SQL routine definition unless the program object is in that library.

In this case, the vendor chose to assign the SQL routine definitions' schema with the data library rather than the program library. Hence when the data library was deleted, all of the SQL routine definitions were deleted. When the data library was restored, only the data objects were put back on the system. Since the program objects were never restored, the SQL routine definitions were never re-created. As mentioned, IBM chose to associate the routine definition at the program and service program level rather than at the library level. I agree with this because you can control what is being restored at the object level--if you only restore one program then only that program's definition are restored. You wouldn't want to have to restore an entire library to restore the SQL routine definition for one program. Unfortunately, this creates a problem in this situation where the user wanted the routine definitions restored with the library.

To avoid this problem, generally I like to create the external SQL routine definitions in the same schema (i.e., library) as the HLL program objects. I don't know why the vendor opted to use this cross library approach. Perhaps there is a good reason, such as the application builds SQL statements that use the SQL naming convention and use qualified schema names--I'm only guessing.

Incidentally, there is one other thing to note when allowing for the saving and restoring of external SQL routine definitions. Because the definition is saved with the program object, the program object should exist when the external CREATE statement is issued because this is when the program object is supplied the extra SQL information needed to backup the routine definition. If the program does not exist when the CREATE statement is issued, the routine will still exist but you'll receive the following warning: SQL7909--Routine XYZ was created, but cannot be saved and restored.

This message simply signals that the SQL routine definition has been created but since no program object exists yet there is no way to associate the definition with the program for the save operation.

This also has implications for re-compilation of programs and service programs. A service program may have several associated SQL routines defined. Say a service program has 10 external routines defined against it. You fix a bug in the service program and then re-compile it. What then? The routine definitions are no longer associated with the service program object and hence will not be backed up.

The lesson here is that whenever you re-create a program that has one or more external SQL routines defined, you should drop and re-create the associated routine definitions so they continue to be associated with the program object and backed up as expected. To do this effectively, you may need to run SQL scripts for each of your program objects (similar to what this vendor did) so that this process is painless as possible.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot