A defect? Maybe. According to their documentation, IBM names it an improvement, the change control vendor we have certainly believes that it is a bug. To me...it's a PitA. I'm thinking that I'll be forced into a PMR situation.
Chuck - Of course, not being on 7.2, I can't speak to the behavior on that OS directly. As to monitoring messages, etc...the events I speak of are managed by our change management system. So they are difficult to monitor.
The EXTERNAL_NAME value is also changed to reflect the new name of the *PGM/*SRVPGM object.
SYSROUTINE is updating selected instances of the named SQL SP...not all of them throughout the entire table. This is why I suspect some reference from the *PGM/*SRVPGM object being renamed includes all of the names of the updated rows in the SYSROUTINE table. I didn't check into the SYSPARMS table.
I've been monitoring the journal for SYSROUTINE. What actually happens is a delete of the row containing the original data and a write of the new-named object's new SQL SP.
I'm trying to find a way to predict/explain the impact of renaming a *PGM/*SRVPGM object in libA, also impacting an SQL SP in an otherwise unrelated library. Compiles in our development libraries are impacting out testing libraries, causing the report of bugs that are not associated with the changes in the release, but are in fact due to SQL SP's that have gone missing due to this.
Steve Needles
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, September 11, 2014 1:32 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
________________________________
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
midrange.com
