Get exported rows using DB2 9

/*
　　記錄匯出 table 的筆數
　　1) 使用 system stored procedure ADMIN_CMD 來執行 export
　　2) 將 call ADMIN_CMD 包在另一支 procedure 中，
　　　 使用 ASSOCIATE RESULT 去接傳回的 result set 即 ROWS_EXPORTED
*/

/* call ADMIN_CMD to export table orion */

C:\>db2 call sysproc.admin_cmd('export to c:\orion.del of del select * from orion')

　結果集　1
　--------------
　ROWS_EXPORTED　MSG_RETRIEVAL　　　　　MSG_REMOVAL
　-------------- --------------------- -------------------------
　　　　　　　4959 -　　　　　　　　　　　　-

　　已選取 1 個記錄。

　傳回狀態 = 0


/* Create testing table to store ROWS_EXPORTED */

CREATE TABLE EXP_RESULT
(
　　TBNAME VARCHAR(32),
　　EXP_ROWS BIGINT
);


/*
　　Create testing procedure
　　使用 ASSOCIATE LOCATORS 接 call procedure 傳回的 result set(s)
*/

CREATE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN

　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE RETCODE INTEGER DEFAULT 0;
　　DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
　　DECLARE nRow INT;

　　BEGIN
　　　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
　　　　SET RETCODE = SQLCODE;
　　　　
　　　　CALL SYSPROC.ADMIN_CMD('EXPORT TO C:\ORION.DEL OF DEL SELECT * FROM ORION');

　　　　ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
　　　　ALLOCATE cur1 CURSOR FOR RESULT SET LOC1;

　　　　OPEN CUR1;
　　　　FETCH CUR1 INTO nRow;
　　　　INSERT INTO EXP_RESULT VALUES ('ORION',nRow);
　　　　CLOSE CUR1;
　　　　COMMIT;
　　END;
　　SET RETCODE = 0;
END

/* execute stored procedure */

C:\>db2 call sp_test

　傳回狀態 = 0

/* check target table EXP_RESULT */

C:\>db2 select * from exp_result

TBNAME　　　EXP_ROWS
---------- ---------------
ORION 　　　　　　　　　4959

　　已選取 1 個記錄。

※ 最好 stored procedure: SP_TEST 改寫成 Dynamic SQL 方式

Pipe multi records to one column using Oracle10g

/*
　　將多筆資料pipe成一個column
　　使用 Recursive SQL + Self join Table
*/

/* Create testing Table & Data */

CREATE TABLE SAMPLE
(
　　COL1 INTEGER,
　　COL2 CHAR(3)
) IN USERSPACE1

INSERT INTO SAMPLE VALUES (1,'111');
INSERT INTO SAMPLE VALUES (1,'222');
INSERT INTO SAMPLE VALUES (1,'333');
INSERT INTO SAMPLE VALUES (1,'444');

INSERT INTO SAMPLE VALUES (2,'AAA');
INSERT INTO SAMPLE VALUES (2,'BBB');
INSERT INTO SAMPLE VALUES (2,'CCC');

COMMIT;


/*
　　想辦法將資料做成可以 self join 的資料
　　以下面 SQL 為例，self join 時可以用 PRECURSOR 欄位 join 到 RID 欄位
*/


SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM　　SAMPLE




/* 使用剛才的 SQL 再搭配 Recursive SQL 將 COL2 PIPE 起來，展開資料如下 */

SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM　　SAMPLE
) TMP
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1




/*
　　對SQL再做些微修改：
　　1. 增加欄位 CNT 記錄 PIPE 起來的筆數
　　2. 增加WHERE條件，取 RID = CNT 的資料
*/

SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR,
　　　　COUNT(*) OVER(PARTITION BY COL1) CNT
FROM　　SAMPLE
) TMP
WHERE RID = CNT
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1