/*
0) create testing table
1) Load from
- 一般 Load and 指定欄位 Load
- Load from cursor
2) Load from and ADMIN_CMD procedure
3) get Result using ADMIN_CMD
4) Note: load fail
*/
/* 0) create testing table & insert testing data */
CREATE TABLE LOAD_RESULT
(
TABLENAME VARCHAR(32),
ROWS_READ INTEGER,
ROWS_SKIPPED INTEGER,
ROWS_LOADED INTEGER,
ROWS_REJECTED INTEGER,
ROWS_DELETED INTEGER,
ROWS_COMMITTED INTEGER
) IN USERSPACE1;
create table ldr
(
col1 integer,
col2 varchar(32),
col3 decimal(19,2)
) in userspace1;
create table ldr2
(
col4 varchar(64),
col5 bigint,
col6 integer
) in userspace1;
create table source_ldr
(
col1 integer,
col2 varchar(32),
col3 decimal(19,2),
col4 varchar(64),
col5 bigint,
col6 integer
) in userspace1
insert into source_ldr values (1,'Red Wine',95,'Taipei City,R.O.C',124500,100);
insert into source_ldr values (2,'Coffee',35,'Columbia',13500,109);
insert into source_ldr values (3,'Apple Juice',80,'NY, U.S.A',19500,150);
insert into source_ldr values (4,'Beer',31,'Paris, France',12900,130);
insert into source_ldr values (5,'Bottle of Water',18,'Barcelona, Spain'19500,15);
/* export */
export to c:\ldr.del of del select * from source_ldr
/* 1) Load from */
/* 一般Load */
Load from c:\ldr.del of del insert into ldr
/* 指定欄位Load */
Load from c:\ldr.del of del method p (4,6) insert into ldr2(col4,col6)
/* Load from cursor */
C:\>db2 declare cur cursor for select * from source_ldr
DB20000I SQL 指令已順利完成。
C:\>db2 load from cur of cursor replace into ldr
SQL3501W 由於禁止資料庫向前回復， 所以表格常駐的表格空間將不放入備份懸置狀態。
SQL1193I 公用程式正在開始從 SQL 陳述式 " select * from source_ldr" 載入資料。
SQL3500W 公用程式在 "2008-11-21 13:28:24.050661" 時開始 "LOAD" 階段。
SQL3519W 開始載入「一致點」。輸入記錄數 = "0"。
SQL3520W 成功載入「一致點」。
SQL3110N 公用程式已完成處理。自輸入檔讀取第 "5" 列。
SQL3519W 開始載入「一致點」。輸入記錄數 = "5"。
SQL3520W 成功載入「一致點」。
SQL3515W 公用程式已在 "2008-11-21 13:28:24.464409" 時完成 "LOAD" 階段。
已讀取的列數 = 5
已略過的列數 = 0
已載入的列數 = 5
已拒絕的列數 = 0
已拒絕的列數 = 0
已確定的列數 = 5
/* 2) Load from and ADMIN_CMD procedure */
C:\>db2 call sysproc.admin_cmd('load from c:\ldr.del of del insert into ldr')
結果集 1
--------------
ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED...
------------ --------------- -------------- ---------------- --------------- -----------------
5 0 5 0 0 5
已選取 1 個記錄。
傳回狀態 = 0
/* 或者load from cursor(結果同上) */
C:\>db2 call sysproc.admin_cmd('load from (select * from source_ldr) of cursor
insert into ldr')
/* 3) get Result using ADMIN_CMD */
DROP PROCEDURE SP_LOADCURSOR
GO
CREATE PROCEDURE SP_LOADCURSOR
SPECIFIC SP_LOADCURSOR
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
DECLARE nRead INT;
DECLARE nSkip INT;
DECLARE nLoad INT;
DECLARE nReject INT;
DECLARE nDel INT;
DECLARE nCommit INT;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET RETCODE = SQLCODE;
CALL SYSPROC.ADMIN_CMD('LOAD FROM (SELECT * FROM SOURCE_LDR)' ||
'OF CURSOR INSERT INTO LDR');
ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
ALLOCATE cur1 CURSOR FOR RESULT SET LOC1;
OPEN CUR1;
FETCH CUR1 INTO nRead,nSkip,nLoad,nReject,nDel,nCommit;
INSERT INTO LOAD_RESULT
VALUES ('LDR',nRead,nSkip,nLoad,nReject,nDel,nCommit);
CLOSE CUR1;
COMMIT;
END;
SET RETCODE = 0;
END
/* 執行 */
call SP_LOADCURSOR
/* SELECT LOAD_RESULT 結果 */
/* 4) Load Fail */
/* issue command and press Ctrl-C */
C:\>db2 call sysproc.admin_cmd('load from c:\ldr.del of del replace into ldr')
C:\>db2 select * from ldr
COL1 COL2 COL3
----------------- ----------------------------------------------------------- ---------------------
SQL0668N 表格 "ORION.LDR" 上不容許作業，原因碼為 "3"。 SQLSTATE=57016
/* 終止load，使table可被使用 */
C:\>db2 call sysproc.admin_cmd('load from c:\ldr.del of del terminate into ldr')
