Unanswered: move stored procedure different database
I am running DB2 UDB 7.2 on AIX. I would like to move a stored procedure from one environment to another (different server and database). The procedure is SQL and has been built/compiled in one environemnt. However I do not have the compiler present on the second environemnt. From what I read it is possible to move the compiled file instead of recompiling in the second database. However I have done so with no luck. Are there steps I am missing to get it "registered" or get it to appear in the second database?
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.
Visit the new-look IDUG Website , register to gain access to the excellent content.