使用db2look导出DB2中user defined function和procedure的DDL

本文探讨如何使用db2look来导出DB2中自定义函数和存储过程的定义

1.) 创建一个user defined function和一个procedure，并验证


$ db2 -td@ -vf ADD12984.TXT
create function ADD12984(a int ,b int)
returns int
LANGUAGE SQL
BEGIN ATOMIC
          DECLARE sum int default 0;
          set sum = a + b;
          return sum;
END


DB20000I  The SQL command completed successfully.


$ db2 -td@ -vf createprocedure.txt
CREATE OR REPLACE PROCEDURE PROCEDURE01 ()
        DYNAMIC RESULT SETS 1
P1: BEGIN
        -- Declare cursor
        DECLARE cursor1 CURSOR WITH RETURN for
        SELECT ID FROM E97Q6C.T1;


        -- Cursor left open for client application
        OPEN cursor1;
END P1


DB20000I  The SQL command completed successfully.


$ db2 "values ADD12984(2,3)"


1          
-----------
          5


  1 record(s) selected.
  
$ db2 "call PROCEDURE01()"


  Result set 1
  --------------


  ID         
  -----------
         1242


  1 record(s) selected.


  Return Status = 0  
  
2.)使用db2look导出定义  qsmiao是数据库名
$ db2look -d qsmiao -a -e -o func.sql
-- Generate statistics for all creators 
-- Creating DDL for table(s)
-- Output is sent to file: func.sql
-- Binding package automatically ... 
-- Bind is successful
-- Binding package automatically ... 
-- Bind is successful




在func.sql里可以找到上面函数和存储过程的定义
---------------------------------
-- DDL statements for User Defined Functions
---------------------------------


SET CURRENT SCHEMA = "E97Q6C  ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","E97Q6C";


create function ADD12984(a int ,b int)
returns int
LANGUAGE SQL
BEGIN ATOMIC
          DECLARE sum int default 0;--
          set sum = a + b;--
          return sum;--
END
;


---------------------------------
-- DDL statements for stored procedures
---------------------------------


SET CURRENT SCHEMA = "E97Q6C  ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","E97Q6C";


CREATE PROCEDURE PROCEDURE01 ()
        DYNAMIC RESULT SETS 1
P1: BEGIN
        -- Declare cursor
        DECLARE cursor1 CURSOR WITH RETURN for
        SELECT ID FROM E97Q6C.T1;--


        -- Cursor left open for client application
        OPEN cursor1;--
END P1
;

3.) 理论依据是db2look的-e选项：

-e

Extracts DDL statements for the following database objects:

• Aliases
• Audit policies
• Check constraints
• Function mappings
• Function templates
• Global variables
• Indexes
• Index specifications
• Materialized query tables (MQTs)
• Nicknames
• Primary key constraints
• Referential integrity constraints
• Roles
• Schemas
• Security labels
• Security label components
• Security policies
• Sequences
• Servers
• Stored procedures
• Tables
  Note: Values from column STATISTICS_PROFILE in the SYSIBM.SYSTABLES catalog table are not included.
• Triggers
• Trusted contexts
• Type mappings
• User mappings
• User-defined distinct types
• User-defined functions
• User-defined methods
• User-defined structured types
• User-defined transforms
• Views
• Wrappers

If you use DDL statements that are generated by the  db2look command to re-create a user-defined function, the source code that the function references (the EXTERNAL NAME clause, for example) must be available for the function to be usable.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html?lang=en