Configuring to replicate SQL stored procedures and user-defined functions - assure_mimix - 10.0

DB2 UDB for IBM Power™ Systems supports external stored procedures and SQL stored procedures. This information is specifically for replicating SQL stored procedures and user-defined functions. SQL stored procedures are defined entirely in SQL and may contain SQL control statements. MIMIX can replicate operations related to stored procedures that are written in SQL (SQL stored procedures), such as CREATE PROCEDURE (create), DROP PROCEDURE (delete), GRANT PRIVILEGES ON PROCEDURE (authority), and REVOKE PRIVILEGES ON PROCEDURE (authority).

An SQL procedure is a program created and linked to the database as the result of a CREATE PROCEDURE statement that specifies the language SQL and is called using the SQL CALL statement. For example, the following statements create program SQLPROC in LIBX and establish it as a stored procedure associated with LIBX:

CREATE PROCEDURE LIBX/SQLPROC(OUT NUM INT) LANGUAGE SQL
SELECT COUNT(*) INTO NUM FROM FILEXSELECT COUNT(*) INTO NUM FROM FILEX

For SQL stored procedures, an independent program object is created by the system and contains the code for the procedure. The program object usually shares the name of the procedure and resides in the same library with which the procedure is associated. A DROP PROCEDURE statement for an SQL procedure removes the procedure from the catalog and deletes the external program object.

Procedures are associated with a particular library. Because information about the procedure is stored in the database catalog and not the library, it cannot be seen by looking at the library. Use System i Navigator to view the stored procedures associated with a particular library (select Databases > Libraries).