Re: Is is possible to Save/Restore SQL Stored procedures from one system to another

On 19-Oct-2011 10:23 , John.BresinaJr wrote:

We are doing a Hardware upgrade from an 840 to Power6 560 and
upgrading the OS from V5R4 to 7.1. The users have been testing all
applications successfully except one external interface was receiving
a Error: [DB2/400 SQL]SQL0204 - X09031SPS in QGPL type *N not found.
After talking with the application support staff and not finding the
object on the OLD and NEW systems I discovered it was a Stored
procedure. I then looked in iSeries Navigator> Databases> Our
System> Schemas> QGPL> Procedures and there it was. Is it
possible to Save/Restore the stored procedures? Do I have to using
navigator generate the SQL and rerun it on the new system? If so, I
have never attempted this before, how?

Having properly followed a documented migration and upgrade path, the procedures should have been restored and then upgraded as part of the install [of the OS OPTION(01)]. After the upgrade [has already been completed], the best method to resolve an issue for a failure in maintaining the routines [by whatever origin] is generally to create the missing procedure(s) using whatever change management enabling the proper creation. If the origin for missing routine(s) is from using a migration path that failed to restore the quasi-system library QSYS2 [a "user library"; part of *ALLUSR] from the v5r4 system before upgrading those objects\data to v7r1, then there may be more missing or incorrect user-data than just that one routine.

Save\Restore of the object which represents the routine, in order to have the procedure entry generated automatically, can be a better option than CREATE since saving\restoring enables the ownership assignment and an option to include the private authorities. But save\restore is not as good an option as compared to a new CREATE PROCEDURE if the reference object is both defined as EXTERNAL and does not have the registration details stored; i.e. lacking those registration details, after restore, a routine to represent the object will remain unregistered. An object created for a LANGUAGE SQL routine [i.e. not EXTERNAL] would always have the registration details.

The iNav Database feature can extract the definition of the procedure and the RunSQL script feature then used to perform the CREATE. I do not recall if there is a capability of the feature [to extract, to include necessary scripting] to set both ownership and authorities to the new routine to match those of the old routine, but those need to be handled in addition to just the CREATE. In part, why a CMS should be generating the object, rather than using a generic tooling like the iNav Database to get the object into production.

Regards, Chuck