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

On 11-Sep-2014 09:43 -0500, Needles,Stephen J wrote:

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.

Seems to imply a defect with the support described below.? Perhaps such that opening a defect support ticket [a PMR] might be a good approach?

Excerpt below from the
IBM 7.1 Technical Overview with Technology Refresh Updates
Redbooks publication:
" 6.4.37 Improved catalog management for procedures and functions
<<SNIP>> "

The following IBM i 7.2 docs show additional support that may or may not have been added in v7r1; notably, effects of restore and deletion [CLRLIB, DLTPGM, DLTSRVPGM] activity, plus messages that perhaps could be /watched/?

<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafyroutinemgmt.htm>
"...
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. ...
...

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.
..."

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...

The EXTERNAL_NAME is changed successfully.?

Here's the problem. It is possible to have more than one
SQL SP stored in the Program Associated Space.

The EXTERNAL_NAME is not changed successfully.? At least not for all occurrences in either or both TABLE of the catalogs?

Is anyone aware of a mean to list the contents of the Program
Associated Space?

Dump Object (DMPOBJ). Or better, Dump System Object (DMPSYSOBJ) with the SPACE() parameter.

Presumably also:

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/qclras.htm>
_Retrieve Associated Space_ (QbnRetrieveAssociatedSpace) API

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/qclrpgas.htm>
_Retrieve Program Associated Space_ (QCLRPGAS) API

Particularly, the list of Stored Procedures that
might reside there?

I am not sure how easily inferred will be the layout of the PAS entries for the routine definitions, no matter the means to /see/ them.

FWiW:

The journal environment that logs activity to the SYSROUTINE and SYSPARMS catalog TABLEs [presumably still the QSQJRN in QSYS2] can be modified so as not to automatically delete so the history of any particular routine and the associated parameters could be tracked; the journaling of the files can also be changed to log both before and after images.

One would hope that the update activity is performed under isolation [so as to prevent any /timing/ problems] and that the Maximum Record Wait Time (WAITRCD) setting on the files is such that conflicts for update activity have the opportunity to get resolved. The journal and a trace could be helpful for review to better understand, for the rename or move of an external routine with dual definitions.