Troubleshooting
Problem
This document describes recommended actions that should be performed when working with External Stored Procedures that were created in an ILE (non-OPM) environment outside of libraries QSYS or QSYS2 with a minimum of one IBM SQL/400 statement.
Resolving The Problem
When working with External Stored Procedures, the *PGM object and the IBM SQL/400 Catalogs must be synchronized.
These guidelines were designed and apply to External (non-SQL) Stored procedures that were made in an ILE (non-OPM) environment and contain at least one embedded SQL statement and are not in the QSYS or QSYS2 libraries. If your stored procedures do not meet these criteria, the steps required will be increased.
As with any good program change management process, we recommend that you maintain a Change Management Document for each stored procedure that documents compiles, creates, drops, deletes, and moves. This document is required to help trace why certain items happen when actions occur.
| Action Required: | Steps Required: |
| Create New | - Compile - CREATE PROCEDURE(s) - Verify SQL Catalogs |
| Delete | - DROP SPECIFIC PROCEDURE(s) - DLTPGM - Verify SQL Catalogs |
| Recompile | - DROP SPECIFIC PROCEDURE(s) - DLTPGM - Compile - CREATE PROCEDURE(s) - Verify SQL Catalogs |
| Restore from SystemA to SystemB | - DROP SPECIFIC PROCEDURE(s) on SystemB - DLTPGM on SystemB - Restore from SystemA - Verify SQL Catalogs |
| Restore from SystemA LibA to SystemB LibB | - DROP SPECIFIC PROCEDURE(s) on SystemB - DLTPGM on SystemB - Restore from SystemA to SystemB - DROP SPECIFIC PROCEDURE(s) on SystemB of LibA - CREATE PROCEDURE(s) on SystemB of LibB - Verify SQL Catalogs |
| Move from LibA to LibB | - DROP SPECIFIC PROCEDURE(s) on LibA - Move *PGM object - CREATE PROCEDURE(s) for LibB - Verify SQL Catalogs |
| Verify SQL Catalogs (CREATES) | - DSPJRN JRN(QSYS2/QSQJRN) FILE((SYSROUTINE)) JRNCDE((R)) ENTTYP(PX) - SELECT * FROM QSYS2/SYSPROCS WHERE DATE(ROUTINE_CREATED) = CURRENT_DATE |
| Verify SQL Catalogs (DROPS) | - DSPJRN JRN(QSYS2/QSQJRN) FILE((SYSROUTINE)) JRNCDE((R)) ENTTYP(DL) |
Using *LIBL in the declarations of Stored Procedures is not recommended. If *LIBL must be used, ensure the library that contains the *PGM object is in your library list or the program object will not be updated. This goes back to the fundamental idea that the *PGM object and catalog entries for Stored Procedures are two separate entities.
For more information on Stored Procedures, refer to the following publications:
DB2 for AS/400 SQL Reference at: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmst.pdf
Stored Procedures and Triggers on IBM® DB2 Universal Database™ for iSeries, SG24-6503, at: http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf
Historical Number
319030739
Was this topic helpful?
Document Information
Modified date:
18 December 2019
UID
nas8N1016367