用 DB2 例程来简化迁移

>>--TRUNCATE_TABLE--(--schema_name--,--table_name--)--------><

/* create and insert some values into the table tab1 */
CREATE TABLE tab1 (col1  INTEGER  NOT NULL  PRIMARY KEY, col2  VARCHAR(15) )
DB20000I  The SQL command completed successfully.
INSERT INTO tab1 VALUES ( 1, 'some data' ), ( 2, NULL )
DB20000I  The SQL command completed successfully.
/* verify the current contents of table tab1 */
        

SELECT * FROM tab1 
COL1        COL2           
----------- ---------------
          1 some data      
          2 -              
  2 record(s) selected.
/* Call the truncate stored procedure for the DB2INST1 schema, and the table tab1 */
        

CALL truncate('DB2INST1', 'tab1')
  Return Status = 0
/* Verify that the table contents have been truncated. */
SELECT * FROM tab1 
COL1        COL2           
----------- ---------------
  0 record(s) selected.
/* Insert some new values into the tab1 table */
        

INSERT INTO tab1 VALUES ( 2, 'some new data' ), ( 3, NULL )
        

DB20000I  The SQL command completed successfully.
        

SELECT * FROM tab1 
COL1        COL2           
----------- ---------------
          2 some new data  
          3 -              
  2 record(s) selected.
/* Call the truncate procedure with a NULL schema */
        

CALL truncate(NULL, 'tab1')
  Return Status = 0
/* Verify that the table contents have been truncated. */
        

SELECT * FROM tab1 
COL1        COL2           
----------- ---------------
  0 record(s) selected.
      

>>--HOST_NAME--( )-------------><

      下面这个例子测试演示了用以获取本地连接主机名的函数的执行。
        

      在该场景中，DB2 数据库驻留在一个本地 AIX 机器上。
        
 
      地址 127.0.0.1 在 /etc/hosts 文件中被映射到计算机名 demoaix：
/* connect to the local database */
connect to sample 
Database Connection Information
 Database server        = DB2/6000 8.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
/* execute the host_name function */
values host_name()"
1                                                                               
------------------------------------------------
demoaix                                                                         
                                                
  1 record(s) selected.                                                
	下一个例子测试演示了远程连接上的函数的执行。
        
 
	在该场景中，DB2 数据库与上面一样驻留在同一 AIX 机器上。
        
 
	到 AIX 上数据库的连接是由一个 Windows 2000 客户机建立的；
        
  
	该客户机的名字为 mycomputer。
/* The database samplaix is an alias for the SAMPLE database on AIX */
        

connect to samplaix 
   Database Connection Information
 Database server        = DB2/6000 8.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLAIX
/* execute the host_name UDF against the remote database 鈥?
        

it returns the name of the computer of the client connection */
        
 
values host_name() 
1                                              
------------------------------------------------
mycomputer
      

>>--CALL_PROCEDURE--(--procedure_name--,--parameter_list--,----->
>-----database_name--,--user_name--,--password--)-------------><

下面这个例子测试演示了从触发器调用包含一个参数的存储过程。
        
 
在该示例中，我们创建 t1 和 t2 这两个表，带有一个输入参数（p）
        
 
的过程（abc）以及一个触发器（ins）。在执行触发器时，它将调用该过程。
        
 
然后，过程将会将 num 列的新值（NEW.coll）插入到表 t1 中。
        
 
这可以通过以下操作来测试：在表 t2 上执行插入后对 t1 发出 select 
        

来检验该表内容 —— 进而检验该过程是否成功执行。
create table t1 ( col1 int)
        

DB20000I  The SQL command completed successfully.
create table t2 ( col1 int )
        

DB20000I  The SQL command completed successfully.
create procedure abc(in p int) begin insert into t1 values(p); end
        
 
DB20000I  The SQL command completed successfully.
create trigger ins after insert on t2 referencing NEW as new for EACH ROW MODE
        
 
DB2SQL BEGIN ATOMIC values ( call_procedure('DB2INST1.ABC', char(new.col1 * 2),
        

 'SAMPLE', 'DB2INST1', 'db2inst1') ); END
        

DB20000I  The SQL command completed successfully.
insert into  t2 values 20
        

DB20000I  The SQL command completed successfully.
/* validate that the trigger has fired - it should update t1 */
        

select * from t1
COL1       
-----------
         40
  1 record(s) selected.
      

create table c ( a int check (a <> 8), a1 int )
        

DB20000I  The SQL command completed successfully.
create procedure abc(in p int, in p2 int) begin insert into c values(p,p2); end
        
 
DB20000I  The SQL command completed successfully.
create function udf_withcall (parm1 int, parm2 int) 
returns int 
Language SQL 
not deterministic
external action
return call_procedure('DB2INST1.ABC', char(parm1) ||','|| char(parm2), 'SAMPLE', 'DB2INST1', 'db2inst1'))
        

DB20000I  The SQL command completed successfully.
select udf_withcall(30,40) from sysibm.sysdummy1 
1          
-----------
          0
  1 record(s) selected.
/* verify that the UDF has called the procedure and updated the table */
        

select * from c
A           A1         
----------- -----------
         10          20
         30          40
  2 record(s) selected.
      

bldrtn  <file_name> [dbname userid password]

bldrtn functions 
值得说明的是，可能需要对 Windows 平台上的 bldrtn 批文件进行编辑
        

以便在链接指令中包含 ws2_32.lib。应按照下列方法来修改该文件：
        

:link_step
rem Link the program.
link -debug -out:%1.dll -dll %1.obj db2api.lib ws2_32.lib -def:%1.def 
      

DROP SPECIFIC PROCEDURE truncate_table@
CREATE PROCEDURE truncate ( IN schemaName VARCHAR(130), IN tableName VARCHAR(130) )
   SPECIFIC truncate_table
   DYNAMIC RESULT SETS 0
   MODIFIES SQL DATA
   NOT DETERMINISTIC
   CALLED ON NULL INPUT
   LANGUAGE C
   EXTERNAL NAME 'functions!truncate_table'
   FENCED  THREADSAFE
   INHERIT SPECIAL REGISTERS
   PARAMETER STYLE SQL
   PROGRAM TYPE SUB
   NO DBINFO
@
DROP SPECIFIC FUNCTION client_host_name@
CREATE FUNCTION host_name ( )
   RETURNS VARCHAR(128)
   SPECIFIC client_host_name
   EXTERNAL NAME 'functions!host_name'
   LANGUAGE C
   PARAMETER STYLE SQL
   DETERMINISTIC
   FENCED
   NOT THREADSAFE
   RETURNS NULL ON NULL INPUT
   NO SQL
   NO EXTERNAL ACTION
   NO SCRATCHPAD
   NO FINAL CALL
   ALLOW PARALLEL
   DBINFO
@
DROP FUNCTION call_procedure@
CREATE FUNCTION call_procedure ( procedure VARCHAR(257),
      parameters VARCHAR(30000), databaseName VARCHAR(8),
      userName VARCHAR(128), password VARCHAR(200) )
   RETURNS INTEGER
   SPECIFIC call_stp
   EXTERNAL NAME 'functions!call_procedure'
   LANGUAGE C
   PARAMETER STYLE SQL
   NOT DETERMINISTIC
   NOT FENCED
   THREADSAFE
   CALLED ON NULL INPUT
   NO SQL
   EXTERNAL ACTION
   NO SCRATCHPAD
   NO FINAL CALL
   DISALLOW PARALLEL
   NO DBINFO
@

/* connect to the database */
db2 connect to SAMPLE
/*  specify the terminating character (-td@), verbose output (-v), and the */
/* file name (f functions.db2) for script execution*/
db2 -td@ -vf  functions.db2