开发进行时...

1) 把远程数据库编目到本地,在这里我们远程的DB2主机地址是ip，端口是50001

db2 catalog tcpip node node01 remote ip server 50001

2)在本地建立远程DB2的别名。在这里我们使用testDB作为别名。

db2 catalog db testDB at node node01

3)创建customer数据表

DROP TABLE DSW.CUSTOMER
@
CREATE TABLE DSW.CUSTOMER 
(
    CUST_NUM                 VARCHAR(10) NOT NULL,
    CUST_NAME                VARCHAR(35) NOT NULL,
    ADDRESS                  VARCHAR(35) NOT NULL,
    CITY                     VARCHAR(35) NOT NULL,
    COUNTRY                  VARCHAR(3)  NOT NULL,
    ADD_DATE                 TIMESTAMP     NOT NULL
)
@
ALTER TABLE DSW.CUSTOMER 
    ADD CONSTRAINT CUSTOMER_PK
PRIMARY KEY (CUST_NUM)
@

4)创建存储过程

DROP PROCEDURE DSW.S_CUSTOMER
@
CREATE PROCEDURE DSW.S_CUSTOMER (
  OUT poStatus     INTEGER,
  IN  piCust_num      VARCHAR(10)
)
LANGUAGE SQL
SPECIFIC DSW.S_CUSTOMER
RESULT SETS 2

P1: BEGIN NOT ATOMIC
   -------------------------------------------------------------
   -- CONDITION declaration
   -------------------------------------------------------------
   DECLARE sqlReset    CONDITION for sqlstate '80100';

   -- Generic Variables
   DECLARE SQLCODE      INTEGER       DEFAULT 0;
   DECLARE SQLSTATE     CHAR(5)       DEFAULT '00000';
  
   -- Generic handler variables
   DECLARE hSqlcode     INTEGER       DEFAULT 0;
   DECLARE hSqlstate    CHAR(5)       DEFAULT '00000';

   DECLARE v_Cnt INT;

   -- Cursor for returning RS to the client
   DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR (
	   SELECT CUST_NUM, CUST_NAME FROM DSW.CUSTOMER
   );

   DECLARE cursor2 CURSOR WITH RETURN TO CLIENT FOR (
	   SELECT * FROM DSW.CUSTOMER
	   WHERE CUST_NUM = piCust_num
   );
   
   OPEN cursor1;
   
   OPEN cursor2;

   SET poStatus = 2;
   RETURN poStatus;

END P1
@

5) 插入测试数据

INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('1','Steven','shang di','BeiJing','CHN')
@
INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('2','David','shang di','BeiJing','CHN')
@
INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('3','Tony','shang di','BeiJing','CHN')
@
INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('4','Maggie','shang di','BeiJing','CHN')
@