Managing SQL and external routine objects

SQL and external functions and procedures are implemented using system programs and service programs. When managed correctly, these objects can regenerate the routine registration in the QSYS2/SYSROUTINES, QSYS2/SYSPARMS, and QSYS2/SYSROUTINEDEP system catalogs.

When an SQL procedure or function is created or altered, an ILE C program or service program is generated. In addition to containing executable statements, this object contains all the information used to define it as an SQL routine.

When an external procedure or function is created and is associated with an ILE program or service program object, information about the routine definition is saved in the *PGM or *SRVPGM object. By successfully adding the routine information to the object, maintaining the object requires less manual effort.

The external procedure or function definition can only be saved when:
  • The external program is an ILE *PGM or *SRVPGM object.
  • The program or service program is not in QSYS, QSYS2, SYSIBM, SYSPROC, or SYSIBMADM.
  • The program exists when the CREATE PROCEDURE or CREATE FUNCTION statement is issued and the program can be found.
  • The CREATE or ALTER statement can get an exclusive lock on the program.
  • The program does not already contain attributes for 32 routines.

Message SQL7909 will be issued when an external routine is created, changed, or dropped and the *PGM or *SRVPGM could not be modified. This message includes a reason code.

When these procedure and function *PGM and *SRVPGM objects are administered like other system objects, the information saved in the object is used to maintain the SYSROUTINES and SYSPARMS catalog information. The following CL commands (and their API counterparts) will keep the catalogs in sync with the executable object for procedures and functions.
Save/Restore (SAVOBJ/RSTOBJ and SAVLIB/RSTLIB)
A row is inserted into the SYSROUTINES catalog for each routine. The EXTERNAL_NAME column contains the name of the newly restored executable object.
Create Duplicate Object (CRTDUPOBJ)
A new row is inserted into the SYSROUTINES catalog. The EXTERNAL_NAME column contains the name of the newly duplicated executable object.
Copy Library (CPYLIB)
A new row is inserted into the SYSROUTINES catalog. The EXTERNAL_NAME column contains the name of the newly duplicated executable object.
Rename Object (RNMOBJ)
The existing row in SYSROUTINES is modified. The EXTERNAL_NAME column contains the name of the renamed executable object.
Move Object (MOVOBJ)
The existing row in SYSROUTINES is modified. The EXTERNAL_NAME column contains the name of the moved executable object.
Clear Library (CLRLIB)
All rows in SYSROUTINES are deleted where the SPECIFIC_SCHEMA column matches the name of the library being cleared.
Delete Program (DLTPGM) and Delete Service Program (DLTSRVPGM)
For an SQL routine, the corresponding rows in SYSROUTINES are deleted using the SPECIFIC_NAME and SPECIFIC_SCHEMA columns. No updates are made for external routines.

Message SQL9015 will be issued when a *PGM or *SRVPGM is operated upon using a system command and the catalog entries for the associated routine(s) could not be updated.