I wrote a tool to auto-generate the stored procedures for DB2 UDB and output as sql script file, Now I need to build and deploy the stored procedures to database server. Seems like db2 is somewhat different from SQL server and Oracle, first you need a C Compiler to build the stored procedures. Can anyone direct me to the documentation or manual show us how to build and deploy the stored procedures step by step?

In SQL Server and Oracle, life is very easy, you just open the sql script file in Query Analyzer or SQLPLus Worksheet, then execute the script and the procedures will be build and deploy to the database server.

Can we use DB2's stored procedure builder or Command Center to do the same thing?

Thanks in advance for help,

Kevin

A suggestion using DB2 Command Line Processor. You can issue:

db2 -td@ -f proc.sql

Make sure you use a different EOF marker other than ; (as it is used as the end of line marker for SQL commands within the stored proc code).

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.

Thank you for response. Now I understand how it works in DB2 to compile and deploy stored procedures. I used gcc as my c compile and run db2 -td@ -v sample.sql in my DB2 server's command window, the stored procedure was successfully built in the database server.

Kevin