Stored Procedures Restore Overview

Resolving The Problem

There is often confusion over the way in which stored procedures are handled during the restore process.
Why are they registered in some cases and not in others?


The key to understanding why some are registered and others are not is based in how the process works.


When working with stored procedures, there are two varieties: external and SQL.

While both allow a program to be called from an SQL interface, an external stored procedure is built over an external program or service program object, such as RPG or COBOL, while the body of an SQL stored procedure contains the code to be written in SQL PSM (Structured Query Language Persistent Stored Module) standard.

When an SQL stored procedure is created, the IBM System i products translates the SQL PSM code into an ILE C with embedded SQL program. This allows SQL stored procedures to be treated as external stored procedures. This document, therefore, will mainly focus on external stored procedures.

First, we need to realize that an external stored procedure is really made up of two separate parts that can exist independently of each other. The first part is the program or service program object; in this document, it will be referred to as a program object for readability. The second part is the catalog entry. A stored procedure can be created without the program existing. In this case, just the catalog entry is made. The program object can be compiled and exist without the catalog entry.

Restoring Program Objects

When a program object is restored, the restore codes look for a part of the program object called the program associated space (PAS). The PAS of any program contains many pieces of information. One piece is a description of any external procedure(s) defined over the program. The PAS exists only in ILE programs or service programs that contain at least one embedded SQL statement. Additionally, the external procedure information will not be saved if the target program resides in QSYS or QSYS2.

Because the PAS exists in the program object, the existence and life of that version of the program object must be considered. There are only two times when an attempt is made to write the required information to the PAS. The first time is when the CREATE PROCEDURE statement in run. Obviously, if the program object does not exist at that time, the PAS does not exist and cannot be written to. Similarly, if the program is in use at the time the CREATE PROCEDURE is run and the program object is locked, the PAS will not be able to be written to. In either of these cases, on newer releases, when issuing the CREATE PROCEDURE statement and the system cannot write to the PAS, message SQL7909 is issued. This message states Routine &1 was created, but cannot be saved and restored. This is the indication that only the catalog entry was made and the PAS was not updated for various reasons.

The PAS information is dumped when the Print SQL Information (PRTSQLINF) command is run.
This can be used to verify if the information exists or not.

When using an external program for the stored procedure, there is a limit to the number entries the PAS can hold. When this limit is reached and the next stored procedure is created, the message SQL7909 will be issued with reason code 8. The current maximum is 32 stored procedures per program.

By keeping track of the status of the PAS, it becomes easier to determine when a program will register itself at restore or determine what happened to the program object to prevent the restore from registering it again. This is a manual process but one than can be avoided by creating a process that registers the PAS after any creation.

Retrieving CREATE PROCEDURE Statements

If one or more programs are moved from one system to another and they are not registered, there is a way to retrieve the CREATE PROCEDURE statement needed from the system where they are currently registered in the catalogs. The program objects do not need to exist with a complete, information holding PAS to extract the CREATE PROCEDURE statements. Instead, the catalog entries must simply exist.

Using IBM iSeries Navigator (iNav), Access Client Solutions (ACS), or the Generate Data Definition Language (QSQGNDDL) API, the CREATE PROCEDURE statements needed can be recovered and then run against the new system to not only register the stored procedures there, but also to update the PAS of the current copies of the program objects.

To use iSeries Navigator / Access Client Solutions to do this, do the following:

Using the above method will generate the complete entries needed for SQL stored procedures. In addition to generating the CREATE PROCEDURE statements, it will also generate the complete SQL PSM code body. This allows the server to be switched and the new procedures to be compiled fresh on the new system.

While the process might seem hard to understand, they key is to understand when the PAS is written and what happens to that instance of the PAS. By ensuring the PAS always contains the information needed to register a stored procedure on restore, the process of moving a stored procedure from one system to another can be simplified. However, if a system that contains the catalog entries exists, the required CREATE PROCEDURE statements can be generated and used to update the PAS on various systems.

Reasons for message SQL7909:

The following are the methods to recover from the above problems (based on the reason code):

CRTDUPOBJ and SQL Procedures (Routines)

When the CRTDUPOBJ command is used on a routine, the SQL PAS is duplicated. Any procedures within the SQL PAS are left in their original state. The EXTERNAL NAME refers to the original program, not the new program object. The procedure schema name refers to the original procedure schema. The SQL catalogs are unchanged. To adjust the SQL catalogs and program mark information, use the ALTER PROCEDURE statement:

ALTER PROCEDURE <schema name>/<procedure name> () LANGUAGE RPG PARAMETER STYLE GENERAL EXTERNAL NAME <library name>/<new program name>;

Additional information can be found in the following links:

Management of routines:

SQL Routines : http://www.mcpressonline.com/images/stories/ArticleCode/2008/020608Forstie-command_effect_on_sql_procs.zip

External Routines: http://www.mcpressonline.com/images/stories/ArticleCode/2008/020608Forstie-command-effect-on-external-procs.zip

Management of routines at R710 with SF99701 Group 18 or higher:

SQL Routines:
www.mcpressonline.com/images/stories/ArticleCode/2014/012814Forstie-command_effect_on_sql_procs.zip

External Routines:
www.mcpressonline.com/images/stories/ArticleCode/2014/012814Forstie-command-effect-on-external-procs.zip


 
Management of External routines