About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
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.
If the same procedure name and number of parameters already exists in the catalog:
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.
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
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) |
| 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. |
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..
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
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->SQL Programming, IBM i Db2->Stored Procedures
Software version:
All Versions
Operating system(s):
IBM i
Document number:
640107
Modified date:
26 November 2024
UID
nas8N1016731
Manage My Notification Subscriptions