Take the hassle out of managing procedures and functions with some easy-to-use tools.

Procedures and functions work as desired only when they're kept in sync with the QSYS2/SYSROUTINE and QSYS2/SYSPARMS catalogs. For some users, maintaining the executable code and catalog entries over many machines can seem about as difficult as Dorothy finding her way out of Oz and back to Kansas. The goal of this article is to pull back the curtain and expose some of secrets known only by the Wizard.

This article explains the importance and role of SQL catalogs when managing procedures and functions. It also reveals some of the best practices you can use to avoid pitfalls and recover from problem states.

A companion Run SQL Script file includes four utility procedures, which can augment any replication or administration guidelines currently in use.

Procedures and Functions: Foundation Building

Procedures and functions come in two flavors:

1.      LANGUAGE SQL (aka SQL Procedures)

2.      EXTERNAL

LANGUAGE SQL procedures and functions have their underlying executable program or service program built by the database. The executable is built from ILE C source code, which is generated based upon the SQL provided within the CREATE PROCEDURE or CREATE FUNCTION statement.

EXTERNAL procedures and functions only define the language, executable location, and linkages to be used when invoking the procedure or function. The executable is supplied by the user and need not even exist at the time the procedure or function is created.

External procedures and functions provide some unique challenges in areas such as these:

•·        Redeployment of the program or service program executable

•·        Disaster recovery

•·        High availability solutions

To achieve correct and consistent behavior of procedures on DB2 for i5/OS, three elements must be understood:

1.      The program or service program object: The program contains the executable code for the procedure. When LANGUAGE SQL is used, the database generates ILE C code, precompiles the generated source, produces an ILE module, and finally binds an ILE program. When an external procedure is used, the CREATE PROCEDURE statement is directed to the program via the EXTERNAL NAME clause.

2.      The SQL catalogs: The SYSROUTINES and SYSPARMS catalogs are used by the database when operating on a procedure or function. The combination of these catalogs defines the uniqueness of the procedure or function signature, the location of the executable code, and many more definitional details. The SYSROUTINEDEP catalog enumerates those SQL objects upon which a procedure or function is dependent.

3.      The SQL language allows the user to indirectly manipulate SQL catalogs using SQL CREATE, DROP, and ALTER statements, independent of program objects. An external procedure could be declared or created prior to the existence of the program. From a usage perspective, the program merely needs to exist before procedure execution. 

Procedure Signature

It is important to understand what makes a procedure or function unique. For procedures, the signature definition is simple: specific schema, specific name, and the number of parameters.

The signature is checked at CREATE PROCEDURE time, with an SQL0454 failure returned when an attempt is made to violate the uniqueness of the signature. In the following example, the first procedure creates successfully. The second create fails with SQLCODE = -454, while the third succeeds because the number of parameters differs from the first procedure.

Successful creation:

CREATE PROCEDURE PRODLIB/MYproc1 (

      IN VARCHAR(32000) ,

      IN DECIMAL(15, 5) )

      LANGUAGE C

      NOT DETERMINISTIC

      MODIFIES SQL DATA

      CALLED ON NULL INPUT

      EXTERNAL NAME 'PRODLIB/PRODPGM1'

      PARAMETER STYLE GENERAL ;

SQL0454 failure:

CREATE PROCEDURE PRODLIB/MYproc1 (

      IN BIGINT ,

      IN CHAR(1) )

      LANGUAGE C

      NOT DETERMINISTIC

      MODIFIES SQL DATA

      CALLED ON NULL INPUT

      EXTERNAL NAME 'PRODLIB/PRODPGM1'

      PARAMETER STYLE GENERAL ;

Successful creation:

CREATE PROCEDURE PRODLIB/MYproc1 (

      IN BIGINT ,

             IN INTEGER, 

      IN CHAR(1) )

      LANGUAGE C

      NOT DETERMINISTIC

      MODIFIES SQL DATA

      CALLED ON NULL INPUT

      EXTERNAL NAME 'PRODLIB/PRODPGM1'

      PARAMETER STYLE GENERAL ;

Function Signature

Function signature is more complex than procedures. The function signature definition is specific schema, specific name, the number of parameters, and the data types of the parameters.

The signature is checked at CREATE FUNCTION time, with an SQL0454 failure returned when an attempt is made to violate the uniqueness of the signature. In the following example, both functions create successfully because the data types are different. 

Successful creation:

CREATE FUNCTION PRODLIB/MYudf1 (

      P1 VARCHAR(32000) ,

      P2 DECIMAL(15, 5) )

        RETURNS INT

      LANGUAGE C

      NOT DETERMINISTIC

      MODIFIES SQL DATA

      CALLED ON NULL INPUT

      EXTERNAL NAME 'PRODLIB/MYSRVPGM1(EXPORT1)'

      PARAMETER STYLE GENERAL ;

Successful creation:

CREATE FUNCTION PRODLIB/MYudf1 (

      P1 CHAR(1) ,

      P2 BIGINT )

        RETURNS INT

      LANGUAGE C

      NOT DETERMINISTIC

      MODIFIES SQL DATA

      CALLED ON NULL INPUT

      EXTERNAL NAME 'PRODLIB/MYSRVPGM1(EXPORT1)'

      PARAMETER STYLE GENERAL ;

Procedure Mark Information

Procedure mark information is the definition of the procedure or function, built over a program or service program object and stored within the associated space of the program or service program. The mark information is all the information needed for the database to essentially "replay" the create statement when the object is restored. The mark information includes everything needed to re-create the procedure/function while the signature only defines uniqueness in the catalogs.

This mark information is maintained within the program during CREATE/ALTER/DROP statements for functions and procedures. The mark information is referenced when the object is restored. The purpose or role of the mark detail is to allow the QSYS2/SYSROUTINE and QSYS2/SYSPARMS catalogs to be updated to be consistent with the state of the machine when the objects were saved.

When a procedure is created or altered, the database attempts to record the detail within the program-associated space of the program or service program. The procedure and function information stored within the program or service program is used when the object is restored (RSTOBJ/RSTLIB). After the program object is restored, this internal information is used to adjust the SQL catalogs, creating up to 32 procedures and functions, which relate to the program. When a conflicting signature in the SQL catalogs is encountered during this processing, the detail within the program being restored will be used, replacing whatever was already in the catalog.

SQL7909 Warning

When creating, altering, or dropping external procedures and functions, the database does not consider the inability to mark or un-mark the program executable as a hard error. Instead, SQL7909 (SQLCODE = +7909) is returned when the program executable could not be processed as part of the statement.

In V5R3, IBM started sending a warning message (SQL7909) for the inability to mark or unmark the program executable for create procedure or function. The message text indicated a list of possible causes. In V5R4, the message was improved to enumerate the reasons for the SQL7909 and to output a specific reason code as the cause. A recent PTF expands the warning message to also apply to drop procedure or function.

The warning allows customers to self-diagnose more of these cases, which lead to operational problems when a program is saved and restored to a target machine and the SQL catalogs do not contain the correct entries.

The key here is to understand that any time the SQL7909 is sent, the database is indicating that the SQL catalogs were changed without a corresponding change to the procedure mark information. Even though the CREATE/ALTER/DROP PROCEDURE or FUNCTION SQL statement completed, the state of the program or service program may now be incompatible with the state of the catalogs.

SQL7909 message text (V5R4 version):

                         Additional Message Information                        

 Message ID . . . . . . :   SQL7909       Severity . . . . . . . :   10        

 Message type . . . . . :   Information                                        

 Date sent  . . . . . . :   11/11/04      Time sent  . . . . . . :   09:25:00  

 Message . . . . :   Routine <routine-name> was created, but cannot be saved and     

   restored.                                                                    

 Cause . . . . . :   The routine <routine-name> was created successfully in <library-name> 

   with a specific name of <specific-name>, but the routine's attributes could not be 

   saved in the associated program or service program object.  If the *PGM or  

   *SRVPGM object is saved and then restored, the SQL catalogs will not be     

   updated with the attributes for this routine.  Reason code is <reason code>.

 Reason codes and their meanings are:                                               

     1 -- The external program did not exist when the CREATE statement was issued.                  

     2 -- The external program schema is QSYS.                                 

     3 -- The external program was not an ILE *PGM or *SRVPGM.                  

     4 -- The external program was in use by another job.                      

     5 -- The SQL associated space in the external program was in use by another job.                                

     6 -- The SQL associated space in the external program could not be expanded.                       

     7 -- The external program was compiled in a release prior to V4R4M0.      

     8 -- The SQL associated space in the external program already contains the maximum number of routine definitions.    

 Recovery  . . . :   Do one of the following based on the reason code:         

     1 -- Ensure that the external program exists when the CREATE statement is issued.                                

     2 -- Ensure that the external program schema is not QSYS.                 

     3 -- Ensure that the external program is an ILE *PGM or *SRVPGM.          

     4 -- Use WRKOBJLCK to ensure that the external program is available when the routine is created.          

     5 -- Ensure that the external program is available when the routine is created.                        

     6 -- Try recompiling the external program to rebuild the program's associated space.               

     7 -- Recompile the external program in a more recent release.              

     8 -- Drop one of the routines currently defined for the external program. 

Language SQL Example

A simple save/restore example shows how the SQL catalogs are kept in sync with the program executable. When LANGUAGE SQL is used, the database is generating ILE C based on the SQL statements in the body of the procedure or function and building the program or service program. The EXTERNAL_NAME column within QSYS2/SYSROUTINE reveals the generated program name. In the example shown, the names supplied for the procedure and function are both over 10 characters in length. Since i5/OS has a 10-character limit for program names, the database uses leading characters from the procedure or function name in conjunction with numbers to form a unique name for the program.

The key to this example is that while the schema name and specific names remain the same, the number of input parameters and the types of those parameters are changing.

CREATE PROCEDURE PRODLIB/mySQLprocedure1(parm1 IN INT) LANGUAGE SQL BEGIN END;

CREATE FUNCTION PRODLIB/mySQLfunction1(parm1 INT, parm2 CHAR(1)) RETURNS INT LANGUAGE SQL BEGIN return 1; END;

After these statements are executed, two rows have been added to SYSROUTINE and four rows to SYSPARMS. Note the values for IN_PARMS, because they're about to change.

(Editor's note: Click all images to enlarge.)

The program object and the procedure mark information are saved to a save file.

CL: DLTF    QGPL/PRODSAVF;

CL: CRTSAVF QGPL/PRODSAVF;

CL: SAVOBJ OBJ(*ALL) LIB(PRODLIB) DEV(*SAVF) OBJTYPE(*PGM *SRVPGM) SAVF(QGPL/PRODSAVF)  ;

If the objects are dropped and re-created with a new signature, the catalogs are updated to match the executable code. The input parameters have changed to reflect the new procedure and function definition.

DROP PROCEDURE PRODLIB/mySQLprocedure1(INT);

DROP FUNCTION PRODLIB/mySQLfunction1(INT, CHAR(1));

CREATE PROCEDURE PRODLIB/mySQLprocedure1(parm1 IN DECIMAL(15,5), parm2 IN DECIMAL(15,5)) LANGUAGE SQL BEGIN END;

CREATE FUNCTION PRODLIB/mySQLfunction1(parm1 BIGINT) RETURNS INT LANGUAGE SQL BEGIN return 1; END;

Lastly, when the saved object is restored, we find that the catalog entries revert to their previous values. The IN_PARMS values are back to their original state. The parameter definitions found in QSYS2/SYSPARMS also reflect the original state of the objects as they appeared when the Save Objects (SAVOBJ) command was issued.

CL: RSTOBJ OBJ(*ALL) SAVLIB(PRODLIB) DEV(*SAVF) OBJTYPE(*PGM *SRVPGM) SAVF(QGPL/PRODSAVF) RSTLIB(PRODLIB) ;

Using the Run SQL Scripts Utility Procedures

The companion Run SQL Scripts file for this article contains some procedures that could be useful to understanding the current state of a database as it pertains to procedure and functions, as well as helping to avoid and recover from failures.

The procedures are optional and can be created in any schema.

1.      PASCOUNTLIBRARY shows whether programs within a library are close to exceeding the procedure mark limit.

2.      PASCOUNT shows the active procedure mark count for a specific program or service program.

3.      EXPLAIN returns detailed information for the procedure mark information for a specific program or service program.

4.      CHECK_SYSROUTINE: Given a source and target database, the QSYS2/SYSROUTINE catalog entries for a given schema are compared and contrasted

Reviewing the Program Mark Status for a Given Library (PASCOUNTLIBRARY)

-----------------------------------------------------------------------------------

-- Procedure : QGPL.PASCOUNTLIBRARY

--

-- This procedure returns the listing the most frequently marked programs in

-- descending order.  ILE programs and service programs can track up to 32

-- different procedures and functions.  This procedures provides an easy way

-- to understand the active PAS counts across an entire library.

--

-- Examples:  

-- ---------           

-- CALL QGPL. PASCOUNTLIBRARY (<library-name>);

-- CALL QGPL.PASCOUNTLIBRARY('QUSRSYS');

--

-- Output;

-- -------

-- A result set table will be returned, with one row per each executable

-- and its PAS count.

--

-----------------------------------------------------------------------------------

CALL QGPL.PASCOUNTLIBRARY('HPT440');

Determining the Current Program Mark Count for a Specific Executable (PASCOUNT)

The PASCOUNT procedure provides a simple count of the number of procedures and functions marked within a program or service program. The count by itself isn't enough to predict the effect upon the SQL catalogs if the object were to be saved and restored, but it is an easy way to see if a highly reused object is trending toward the limit of 32 active procedure marks.

If a service program provided 50 unique exports and then 50 unique functions were created over the 50 exports, only the first 32 functions would exist within the service program. If that service program was saved and restored to a target machine, the last 18 functions would be "missing." The mark information is referenced when an object is restored. Since the last 18 functions never made it into the service program's mark information, the database has no permanent record of those functions and cannot re-create them when the object is restored.

----------------------------------------------------------------------------------------

-- Procedure : QGPL.PASCOUNT

--

-- This procedure expects to be passed a name of the form 'library/object *PGM|*SRVPGM'.

-- The procedure uses the PRTSQLINF command to determine the active number of procedures

-- and functions which are being maintained within the program or service program.

--

-- Examples:  

-- ---------           

-- CALL QGPL.PASCOUNT(<library-name/executable-name>, <*PGM or *SRVPGM>);

-- CALL QGPL.PASCOUNT('QSYS/QSQSAMPL *PGM', ?);

--

-- Output;

-- -------

-- Output Parameter #2 = <number-of-active-procedures-or-functions>

--

CALL QGPL.PASCOUNT('HPT440/LCPUDFIN01 *SRVPGM', ?);

CALL QGPL.PASCOUNT('HPT440/LCPUDFIN01 *SRVPGM', ?)

Return Code = 0

Output Parameter #2 = 32

Statement ran successfully   (591 ms)

Reviewing the Program Mark Detail (EXPLAIN)

As we saw earlier, the procedure mark detail within any program or service program may be different from the SQL catalogs. Any instance of the SQL7909 warning is an instance where the procedure mark detail is no longer identical to the SQL catalogs. 

Consider what would happen if the program object were temporarily renamed or moved. The CREATE PROCEDURE, ALTER PROCEDURE, or DROP PROCEDURE statement would be unable to find the program object and therefore would be unable to update the procedure mark information stored within the program object. 

The EXPLAIN procedure can be used to review the detailed procedure mark information.

-----------------------------------------------------------------------------------

-- Procedure : QGPL.EXPLAIN

--

-- This procedure returns the PRTSQLINF output to the caller, as a result set.

-- The result set contains the CREATE PROCEDURE or CREATE FUNCTION statement(s).

-- If a procedure or function is "missing" from the program, most like an SQL7909

-- was sent at creation time.  The SQL7909 indicates why the database could not

-- update the program object.

--

-- Examples:  

-- ---------           

-- CALL QGPL.EXPLAIN(<library-name/executable-name>, <*PGM or *SRVPGM>);

-- CALL QGPL.EXPLAIN('QSYS/QSQSAMPL *PGM');

--

-- Output;

-- -------

-- A result set table will be returned, with one row per each procedure or function

-- being tracked within the executable.

--

-----------------------------------------------------------------------------------

CALL QGPL.EXPLAIN('HPT440/LCPUDFIN01 *SRVPGM');

Reviewing the Program Mark Detail Using iSeries Navigator

iSeries Navigator has a similar feature, which is portrayed below. Decide for yourself which approach works best for you.

Comparing Catalog Entries Across Two Databases (CHECK_SYSROUTINE)

For anyone responsible for deployment and maintenance of multiple machines, the CHECK_SYSROUTINE procedure could save a lot of debug or down time. When the dust settles after procedure/function development, program/service program development, and save/restore operations, many customers want and expect that two machines contain identical catalog entries. This procedure provides a mechanism to confirm the catalog consistency and to quickly understand any differences.

----------------------------------------------------------------------------------------------------

--

-- Procedure : QGPL.CHECK_SYSROUTINE

--

-- This procedure is passed the name of a remote database and the name of a library.

-- The procedure will compare the QSYS2/SYSROUTINE catalog entries of the local machine

-- against the entries on the remote database, for the specified library.

--

-- This procedure provides an easy, programmatic means of validating procedure and function

-- catalog entries.

--

--

-- Examples:  

-- ---------           

-- CALL QGPL.CHECK_SYSROUTINE(<remote-system-name>, <library-name>);

-- CALL QGPL.CHECK_SYSROUTINE('MYREMOTEDB', 'MYLIB');

--

-- Output (when the SYSROUTINE entries are the same):

-- --------------------------------------------------

--  Statement ran successfully   (862 ms)

--  > CALL QGPL.CHECK_SYSROUTINE('RD1PIT', 'SYSPROC')

--  Return Code = 0

--

--

-- Output (when the SYSROUTINE entries do NOT match);

-- --------------------------------------------------

-- A result set table will be returned, order by ROUTINE_NAME. 

-- If an entry exists on both the local and remote machines, then the two rows can be compared

-- to isolate the difference(s) between the catalog entries.

-- If a single row exists, that means the entry is missing from machine not listed.

--

-- Setup:

-- ------

-- This statement needs to be executed on the remote system, to allow the DDM file

-- on the local machine to remotely access routine information.

-- The DDM file cannot directly target QSYS2/SYSROUTINE because it contains LOB columns.

-- Note: to limit data transfer, a WHERE clause could be added to this view.

--

CREATE VIEW SYSIBM.CHECKVIEW AS SELECT SPECIFIC_SCHEMA,            

          SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,        

          ROUTINE_BODY, EXTERNAL_NAME, IN_PARMS, OUT_PARMS, INOUT_PARMS FROM

          QSYS2.SYSROUTINE WHERE EXTERNAL_NAME IS NOT NULL;

--

--

-- This procedure will fail as shown below if the remote-system-name isn't configured for *IP

-- in the RDB Directory Entry.  Use the WRKRDBDIRE command to review the RDB configuration.

--

-- SQL State: 38501

-- Vendor Code: -443

-- Message: [CPF2817] Copy command ended because of error.

--

-- Example RDB setup: ADDRDBDIRE RDB(<remote-system-name>) RMTLOCNAME(<remote-system-name> *IP)

--

----------------------------------------------------------------------------------------------------

CALL QGPL.CHECK_SYSROUTINE('RCHAPTF3', 'SYSIBM');

In this example call, I used the CHECK_SYSROUTINE() procedure to compare a machine using V5R3M0 (RCHAPTF2) to a machine using V5R4M0 (RCHAPTF3), for the SYSIBM library. The output tells me that the V5R4M0 machine has three catalog entries not found on the V5R3M0 machine. For this example, the difference is expected, because a new UDF and two new procedures were added to SYSIBM in V5R4M0.

When the CHECK_SYSROUTINE() procedure identifies that the source and target machine both contain an entry for a routine, but the routine definition differs, there would be two rows for the routine.

CALL QGPL.CHECK_SYSROUTINE('RCHAPTF3', 'QUSRSYS');

In this test, the difference between the two procedures is the number of input parameters. This example characterizes a classic scenario that would lead to an operational failure. Once the difference has been resolved, a successful execution of the CHECK_SYSROUTINE() is achieved and no result set is displayed, which indicates that the catalog entries match!

Statement ran successfully   (811 ms)

> CALL QGPL.CHECK_SYSROUTINE('RCHAPTF3', 'QUSRSYS')

Return Code = 0

Non-SQL Operations

System commands and APIs can be used to manipulate program executables. Some operations, like Restore Object (RSTOBJ), result in the consumption of SQL procedure and function detail stored within the program. Other operations have no direct consequence to the SQL catalogs and can lead toward the creation of confusing or erroneous results. The following sections explain what effect system operations (non-SQL) have upon procedures and functions.

Effect System Commands Have upon SQL Procedures and Functions

The program and service programs for SQL procedures and functions should not be managed and manipulated via system commands for copy, duplicate, move, and rename. Those operations provide no support for maintaining the link between the SQL catalogs and the executable. The save, restore, clear, and delete commands can be used to manage the executables because those operations include special processing to properly maintain the catalogs.

Since the program object is generated by the database for SQL procedures and functions, don't attempt to use program commands such as update, create with replace(*YES), or change program. 

Refer to this table for details on how system commands impact SQL procedures and functions.

External Procedure and Function Operational Rule

Since the database did not build the executable object, we do not attempt to manage the object. System commands, APIs, SQL statements, and anything else that can affect the executable object do not alert the user to any unusual operational changes through the use of SQL diagnostic messages.

Effect System Commands Have upon External Procedures and Functions

The restore, delete library, and clear library commands are the only system commands that result in any change to the SQL catalogs for external procedures and functions. 

Programs and service programs can be created, re-created, updated, and deleted with no impact to the catalog entries. Those operations do have an impact on the program mark information contained within the program, so care needs to be taken to understand the desired result.

When a program needs to be re-created, consider using the REPLACE(*YES) command option, which preserves the program mark information.

Refer to this table for details on how system commands impact external procedures and functions.

Service Level Detail

Database customers are always encouraged to apply the most recent "DB2 UDB for iSeries" - Group PTF.

In addition to the Group PTF, the following PTFs may be also need to be applied.

5722SS1 V5R3M0 PTFs

PTF SI29469: OSP-DB-OTHER-INCORROUT SYSROUTINE INCORRECT AFTER RESTORE  

PTF SI29470: OSP-DB-OTHER-INCORROUT SYSROUTINE INCORRECT AFTER RESTORE  

PTF SI29419: Indicate SQL7909 warning on DROP PROCEDURE and DROP FUNCTION    

PTF SI29374: OSP-DB-OTHER-RC6-MSGSQL7909 MSGSQL7909 FOR CREATE FUNCTION

PTF SI23855: OSP-DB PRTSQLINF PRINTING OF EXTERNAL PROCEDURES 

5722SS1 V5R4M0 PTFs

PTF SI24977: Preserving procedure and UDF mark information when rebuilding or updating the program or service program executable code

Reference Material

The following technical books are valuable resources for DB2 for i5/OS development and administration.

DB2 for i5/OS SQL Reference (Version 5 Release 4)

DB2 Universal Database for iSeries SQL Programming (Version 5 Release 4)

DB2 Universal Database for iSeries Embedded SQL programming (Version 5 Release 4)

Conclusion

Procedures and functions have become a very popular way to encapsulate business logic. DB2 for i5/OS users can utilize EXTERNAL procedures and functions to extract maximum value from existing programs and service programs.

The burden on any installation or administrator is to develop and use best practices and processes to avoid catalog and executable inconsistencies.

Consider using the techniques and procedures provided in the article to:

1. Avoid having more than 32 routines marked on any executable.
2. Locate external functions and procedures in the same schema (library) as the executable to simplify Save/Restore.
3. Use the QGPL.CHECK_SYSROUTINE() procedure to validate catalog consistency across source and target databases.
4. Use REPLACE(*YES) to redeploy program and service programs used by external procedures and functions.

As Dorothy learned, the scariest part of Oz was the unknown. I hope this article will help you improve your use of procedures, functions, and catalogs.

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL