RE: DB2 stored procedure not shown by DSPSAVF

If you refer back to the article I listed, you'll notice that there are many
possible reasons PAS cannot be changed (DSPMSGD SQL7909 QSQLMSG to see them
all). One of them is the scenario you've described, running a CREATE
PROCEDURE and the program does not exist or if using *LIBL, not in the
*LIBL. So the SP would be in the catalogues but PAS would not have been
changed and thus the save/restore scheme would not work to register it on
another system. This and other reasons like it is why IBM insists folks
should not rely on the save/restore scheme and instead build re-runnable SQL
scripts to recreate all of the SQL objects and catalogue entries.

Now, the fact that PAS has not been changed does not mean that the procedure
won't work, provided it's either created before the SP is invoked or in the
case of *LIBL issue, the *PGM library is there at runtime. The SQL7909 is a
warning and not an exception message, since it only applies to save/restore
of the SP rather than actual execution.

To summarize, SP catalogue entry is not guaranteed to be 'saved' to PAS, so
burden is on you mister developer to ensure you create re-runnable SQL
scripts that register your SPs, UDFs etc. I sometimes use RUNSQLSTM to run
the scripts on the 'other' box.
Other times, if building a commercial product and leveraging the
save/restore behavior with PAS, we scrub the build joblog for any instance
of SQL7909 to verify we are good with our approach. This has been working
very well for us, as long as we take the 32 mark limit into account (yes, we
have service programs that have hit that limit, so we had to split them up).
I actually thank IBM for this approach, as it relieves me from maintaining
SQL scripts as long as I'm careful with handling SQL7909 warning.

Pick your poison.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: DB2 stored procedure not shown by DSPSAVF

I will have to test it, but how do you save and restore a stored
procedure which is the stub for an external program which does not yet
exist? When the stored proc is created the defn is stored in the
global procedures table of DB2 and as some sort of associated data in
the EXTERNAL PROGRAM or SPECIFIC that the procedure is created as. If
the EXTERNAL PROGRAM does not exist, how is the stored proc saved?

I think my question is broader and relates to how a DB2 database is
saved and restored on AIX vs IBM i. On another platform, can you save
a DB2 schema, display the save media and see a listing of DB2 objects
saved, then restore all or parts of that schema from the save media?