RE: DB2 stored procedure not shown by DSPSAVF

Stored Procedure is not an object, just a registration in the SQL
catalogues. There should always be an object associated with it though
(external program or service program, even in case of an SQL sourced SP).

When you CREATE a stored PROCEDURE, system does 2-3 things that pertain to
your question, not necessarily in this order:
- if SQL sourced SP, create the back-end (service) program object
- alters the (service) program associated space (PAS) to include 'mark'
information pertaining to the stored procedure definition
- adds the SP attributes to the SQL catalogues

As long as you have less than 32 marks in the object's PAS, you can
save/restore the program object and system will automatically register the
SP into the SQL catalogues.

So, an EASY way to propagate stored procedures, UDFs and like is to simply
save/restore the target objects (as they have instructed you to do). Since
this is simply a fix, there is nothing wrong with this approach.

If you were to create commercial products using SPs etc., IBM recommends you
create a script that re-registers (or recreates if SQL sourced) all of your
SQL objects since that alleviates any potential issues with the save/restore
scheme (authority, mark information, erroneous *LIBL setup, conflicts with
existing objects etc.).
Nice side-effect of recreating SQL sourced SPs is that you also get the
benefit of any compiler enhancements added with the new version of the OS or
a database fix pack, which can make a very big difference in performance of
said SP.

Hth, Elvis

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


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

How do I display the stored procedure objects contained in a save
file? IBM support sent me a save file containing a program and 3 sql
procedures. When I DSPSAVF, only the program object is displayed. But
running RSTOBJ *ALL placed the stored procedures on my system. Is a
stored procedure considered to be an object? How do I save them?

thanks,