IBM Support

Save & Restore for IBM i Stored Procedure & Functions

Troubleshooting


Problem

This document describes the process of Save and Restore for IBM i Stored Proceres & Functions.

Resolving The Problem

Overview

The best practice for procedures is to locate the procedures and programs they depend upon in the same schema (library).

Restore Considerations

When a procedure's associated program or service program is saved and subsequently restored and the object was updated with the procedure attributes when the procedure was created, the saved attributes will be processed and possibly changed during the restore.

Programs that do not have a program associated space (PAS) or the PAS was not updated at create time to include the procedure/function information, will NOT update our system catalogs with the procedure/function information. The following SQL can be run to find programs that do not have a PAS space:

select *
from qsys2.sysprocs
where EXTERNAL_LANGUAGE = 'CL'
or SQL_DATA_ACCESS = 'NONE'

However, this will NOT cover the case when the CREATE PROCEDURE/FUNCTION was unable to be stored in the PAS.

If the 'Saved library' (SAVLIB) of the program or service program is different from the 'Restore to library' (RSTLIB), the procedure's schema name, specific schema name, and the external name may be changed as a result of the restore.
o If the saved procedure schema name and the library name of the saved object match, the procedure schema will be changed to the 'Restore to library' (RSTLIB) (Example A). Otherwise, the procedure schema name is the saved procedure schema name (Example B).
o The specific schema name is always the same as procedure schema name.
o If the saved EXTERNAL NAME library and the library name of the saved object match, the EXTERNAL NAME library will be changed to the 'Restore to library' (RSTLIB) (Examples A and B). Otherwise, the EXTERNAL NAME library is the saved library name. If the saved EXTERNAL NAME library is *LIBL, it will not change (Example C)
If the same procedure name and number of parameters already exists in the catalog:
o If the external program name or service program name is the same as the one that already exists in the catalog, the information in the catalog for that procedure will be replaced with the saved attributes (including the specific name).
o Otherwise, the saved attributes are not restored, and a warning (SQL9015) is issued.
If the same specific name already exists in the catalog, a warning is issued and a new specific name is generated. Otherwise, the specific name of the procedure is preserved.
  • - Examples A - C (Working with three libraries: SLF1, SLF2 and SLF3)

    • - A. The procedure and program reside within the same library



      CREATE PROCEDURE SLF1.PROCEDURE_1234() EXTERNAL NAME SLF1.RPGPGM12 LANGUAGE RPG  

      SELECT ROUTINE_NAME, ROUTINE_SCHEMA, SPECIFIC_NAME, SPECIFIC_SCHEMA, EXTERNAL_NAME
         FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'PROCEDURE_1234'

      ROUTINE_NAME    ROUTINE_SCHEMA  SPECIFIC_NAME   SPECIFIC_SCHEMA  EXTERNAL_NAME
      ==============  ==============  ==============  ===============  =============
      PROCEDURE_1234  SLF1            PROCEDURE_1234 SLF1             SLF1/RPGPGM12

      (after SLF1/RPGPGM12 is saved and restored to library SLF3)

      SELECT ROUTINE_NAME, ROUTINE_SCHEMA, SPECIFIC_NAME, SPECIFIC_SCHEMA, EXTERNAL_NAME
         FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'PROCEDURE_1234'

      ROUTINE_NAME    ROUTINE_SCHEMA  SPECIFIC_NAME   SPECIFIC_SCHEMA  EXTERNAL_NAME
      ==============  ==============  ==============  ===============  =============
      PROCEDURE_1234  SLF1            PROCEDURE_1234 SLF1             SLF1/RPGPGM12
      PROCEDURE_1234  SLF3            PROCEDURE_1234 SLF3             SLF3/RPGPGM12

    • - B. The procedure and program reside within different libraries



      CREATE PROCEDURE SLF2.PROCEDURE_1234() EXTERNAL NAME SLF1.RPGPGM12 LANGUAGE RPG  

      SELECT ROUTINE_NAME, ROUTINE_SCHEMA, SPECIFIC_NAME, SPECIFIC_SCHEMA, EXTERNAL_NAME
         FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'PROCEDURE_1234'

      ROUTINE_NAME    ROUTINE_SCHEMA  SPECIFIC_NAME   SPECIFIC_SCHEMA  EXTERNAL_NAME
      ==============  ==============  ==============  ===============  =============
      PROCEDURE_1234  SLF2            PROCEDURE_1234 SLF2             SLF1/RPGPGM12

      (after SLF1/RPGPGM12 is saved and restored to library SLF3)

      SELECT ROUTINE_NAME, ROUTINE_SCHEMA, SPECIFIC_NAME, SPECIFIC_SCHEMA, EXTERNAL_NAME
         FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'PROCEDURE_1234'

      ROUTINE_NAME    ROUTINE_SCHEMA  SPECIFIC_NAME   SPECIFIC_SCHEMA  EXTERNAL_NAME
      ==============  ==============  ==============  ===============  =============
      PROCEDURE_1234  SLF2            PROCEDURE_1234 SLF2             SLF3/RPGPGM12

    • - C. The procedure external name schema and program library name are different



      ADDLIBLE SLF1
      CREATE PROCEDURE SLF2/PROCEDURE_1234() EXTERNAL NAME RPGPGM12 LANGUAGE RPG

      SELECT ROUTINE_NAME, ROUTINE_SCHEMA, SPECIFIC_NAME, SPECIFIC_SCHEMA, EXTERNAL_NAME
         FROM QSYS2/SYSROUTINE WHERE ROUTINE_NAME = 'PROCEDURE_1234'

      ROUTINE_NAME    ROUTINE_SCHEMA  SPECIFIC_NAME   SPECIFIC_SCHEMA  EXTERNAL_NAME
      ==============  ==============  ==============  ===============  ==============
      PROCEDURE_1234  SLF1            PROCEDURE_1234 SLF1             *LIBL/RPGPGM12

      (after SLF1/RPGPGM12 is saved and restored to library SLF3)

      SELECT ROUTINE_NAME, ROUTINE_SCHEMA, SPECIFIC_NAME, SPECIFIC_SCHEMA, EXTERNAL_NAME
         FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'PROCEDURE_1234'

      ROUTINE_NAME    ROUTINE_SCHEMA  SPECIFIC_NAME   SPECIFIC_SCHEMA  EXTERNAL_NAME
      ==============  ==============  ==============  ===============  ==============
      PROCEDURE_1234  SLF1            PROCEDURE_1234 SLF1             *LIBL/RPGPGM12
      PROCEDURE_1234  SLF3            PROCEDURE_1234 SLF3             *LIBL/RPGPGM12



For more information on this topic, consult IBM i infoCenter or this article:

https://www.ibm.com/docs/en/i/7.5?topic=integrity-save-restore-sql-objects
https://www.mcpressonline.com/programming/sql/procedures-and-functions-and-catalogs-oh-my

Additional keywords added for search ability: RSTLIB, RSTOBJ, external stored procedure, ESP, SQL stored procedure, msgSQL9015..

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i3HAAQ","label":"IBM i Db2-\u003ESQL Programming"},{"code":"a8m0z0000001iICAAY","label":"IBM i Db2-\u003EStored Procedures"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

28930007

Document Information

Modified date:
26 November 2024

UID

nas8N1016731

Page Feedback