STORED PROCEDURE DEPLOYMENT

The CREATE PROCEDURE Statement for SQL Stored Procedures requires a C/C++ compiler for successful execution. Prior to Version 7.2, DB2 required a compiler to be present on every system where the stored procedure will be deployed. From Version 7.2, it is sufficient to have the compiler on only one system for every OS platform. Two new commands were introduced to deploy the Stored Procedure developed/created on one system to another. GET ROUTINE Command for extracting the stored procedure and PUT ROUTINE Command for deploying the Stored procedure on another machine.

GET ROUTINE INTO filename FROM [SPECIFIC] PROCEDURE routine-name

The Specific name of the routine is used when two procedures exist with the same name. The specific name of a procedure can be obtained from the SYSCAT.PROCEDURES Table.

If ftp is used for file transfer, the binary mode should be used.

PUT ROUTINE FROM filename [OWNER new-owner] [USE REGISTERS]

The OWNER Name is used when the Stored Procedure is deployed in another instance with a different name.

USE REGISTERS uses the current values of CURRENT SCHEMA and CURRENT PATH Registry variables. If not used, the original ones at the source are used.