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

Nope...I'd thought of that.

Steve Needles


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, September 11, 2014 9:52 AM
To: Midrange Systems Technical Discussion
Subject: Re: How to determine the SQL Stored Procedures that are "attached" to a *PGM object?

Just a WAG, but does PRTSQLINF work?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 09/11/2014 10:44 AM
Subject: How to determine the SQL Stored Procedures that are
"attached" to a *PGM object?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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