How to determine the SQL Stored Procedures that are "attached" to a *PGM object?

Under 7.1 and with the latest, greatest TR's and PTF's, we have begun to experience the disappearance of SQL Stored Procedures from the SYSROUTINES table.

This seems to be due to a change in timing of updating this table when a program object is Renamed or Moved.

Excerpt below from the IBM 7.1 Technical Overview with Technology Refresh Updates redbook:

" 6.4.37 Improved catalog management for procedures and functions
When an SQL or external procedure or function is created, the routine information is stored within the *PGM or *SRVPGM. Previously, when Librarian commands were used to copy, move, or rename the object, the QSYS2/SYSROUTINE, SYSPARMS, and SYSRTNDEP catalogs were left unchanged.

The following commands (and their API counterparts) were changed to keep the catalogs in sync with the executable object for procedures and functions:

_ Create Duplicate Object (CRTDUPOBJ): The routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object.

_ Copy Library (CPYLIB): The routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object.

_ Rename Object (RNMOBJ): The routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column, which points to the renamed executable object.

_ Move Object (MOVOBJ): The routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column, which points to the moved executable object

There is coverage for Librarian APIs or other operations that are built upon these commands.

The changed behavior can be partially disabled by adding an environment variable. If this environment variable exists, Move Object and Rename Object operations do not update the catalogs. The environment variable has no effect on the CPYLIB and CRTDUPOBJ commands.

Setting the environment variable is shown in Example 6-56.

Example 6-56 Setting the environment variable to partially disable the function

ADDENVVAR
ENVVAR(QIBM_SQL_NO_CATALOG_UPDATE)
LEVEL(*SYS)"

Our change management software uses these rename and move commands. When they occur, the program is renamed and the SQL SP is renamed. No problem...here's the problem. It is possible to have more than one SQL SP stored in the Program Associated Space.

Is anyone aware of a mean to list the contents of the Program Associated Space? Particularly, the list of Stored Procedures that might reside there?

Thanks!

Steve Needles

________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.

TRVDiscDefault::1201