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

-----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?


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].