Orion's DB Lab Diary - since 2008: 11月 2008

2008年11月20日星期四

LOAD From and using ADMIN_CMD in DB2 9

/*
　　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')

2008年11月18日星期二

Sorting an alphanumeric column in DB2 9

/*
　　sorting alphanumeric column.
　　say, column COL0 values with below:
　　　　C1
　　　　C2
　　　　C3
　　　　C10
　　after order by COL0,it turns out to be
　　　　C1
　　　　C10
　　　　C2
　　　　C3
　　but it should be
　　　　C1
　　　　C2
　　　　C3
　　　　C10

　　1) sorting alphanumeric column support Multi-Byte character
　　2) sorting alphanumeric column support Single-Byte character
*/

/* Create testing table */


CREATE TABLE TEST
(
　　COL0    VARCHAR(6)
) IN USERSPACE1;

/* insert testing table */


INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4'); 
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');
INSERT INTO TEST VALUES ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');

/*　1) Multi-Byte character sorting */


WITH N(CID,RID,COL0,LENS,COL1,COL2) AS
(
SELECT　1 CID,RID,COL0,LENS,
　　　　CASE WHEN ASCII(SUBSTRING(COL0,1,1,CODEUNITS32)) NOT BETWEEN 48 AND 57 THEN 
　　　　　　　　　　SUBSTRING(COL0,1,1,CODEUNITS32)  ELSE '' END COL1,
　　　　CASE WHEN ASCII(SUBSTRING(COL0,1,1,CODEUNITS32)) BETWEEN 48 AND 57 THEN 
　　　　　　　　　　SUBSTRING(COL0,1,1,CODEUNITS32)  ELSE '' END COL2
FROM
(
SELECT　ROW_NUMBER() OVER() RID,COL0,LENGTH(RTRIM(COL0)) LENS
FROM　　TEST
) T
UNION ALL
SELECT　N.CID + 1,N.RID,N.COL0,N.LENS,
　　　　N.COL1||
　　　　CASE WHEN ASCII(SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32)) 
　　　　　　　　　　　　NOT BETWEEN 48 AND 57 THEN 
　　　　　　　　　　SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32)  ELSE '' END COL1,
　　　　N.COL2||
　　　　CASE WHEN ASCII(SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32)) 
　　　　　　　　　　　　BETWEEN 48 AND 57 THEN 
　　　　　　　　　　SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32)  ELSE '' END COL2
FROM　　N
WHERE　　N.CID + 1 <= N.LENS
)
SELECT COL0  FROM N
WHERE  CID = LENS
ORDER BY COL1,INT(CASE WHEN COL2='' THEN '0' ELSE COL2 END);

/* And the Result */


COL0
----
B8
BE1
BE2
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10
C11
C12
C13
CE
CE1
CE2
E1
教1
教2
教10

/* Truncate testing table */


ALTER TABLE TEST ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

/* insert testing data */


INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4'); 
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');

/* 2) Single-Byte character sorting */


SELECT  COL0
FROM
(
SELECT　COL0,
　　　　LTRIM(RTRIM(TRANSLATE(COL0,'','0123456789'))) COL1,
　　　　LTRIM(RTRIM(TRANSLATE(UPPER(COL0),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) COL2
FROM　　TEST
) TMP
ORDER BY COL1,INT(CASE WHEN COL2='' THEN '0' ELSE COL2 END);

/* And the Result */


COL0
-----
B8
BE1
BE2
C1
C10
C11
C12
C13
C2
C3
C4
C5
C6
C7
C8
C9
CE
CE1
CE2
E1

2008年11月17日星期一

Sorting an alphanumeric column in Oracle10g

/*
　　sorting alphanumeric column.
　　say, column COL1 values with below:
　　C1
　　C2
　　C3
　　C10
　　after order by COL1,it turns out to be
　　C1
　　C10
　　C2
　　C3
　　but it should be
　　C1
　　C2
　　C3
　　C10
*/

/* create testing table */


CREATE TABLE TEST
(
　　COL0    CHAR(3)
);

/* insert testing data */


INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4'); 
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');
INSERT INTO TEST VALUES ('教');
INSERT INTO TEST VALUES ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');

/* using TRANSLATE Function to separate alphabets and numbers */


SELECT  COL0 FROM
(
SELECT COL0,
　　　　TRIM(TRANSLATE(COL0,'1234567890',' ')) COL1,
　　　　TO_NUMBER(COALESCE(TRIM(TRANSLATE(COL0,
　　　　　　　　　　　　　　　　　　　　　　　TRANSLATE(COL0,'1234567890',' '),
　　　　　　　　　　　　　　　　　　　　　　　' ')
　　　　　　　　　　　　　　　),NULL,'0')) COL2
FROM　TEST
) T
ORDER BY COL1,COL2;

/* The Result */


COL0
--------
B8
BE1
BE2
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10
C11
C12
C13
CE
CE1
CE2
E1
教
教1
教2
教10

2008年11月16日星期日

Grant privileges to Role and Test Dynamic SQL using DB2 9

/*
　　Privilege Grant to Role，再以 Dynamic SQL create objects
　　在 Oracle 上測試的結果：
　　會產生 Oracle insufficient privileges error, ‘ORA-01031.’
　　解決方法是，必須直接 grant 相關的 privileges 給 User
　　而在 DB2 上測試並不會發生這樣的問題
*/

/* 接上篇，給定createin權限給TestingUser */


C:\>db2 connect to sample user db2admin using db2admin

　　資料庫連線資訊

　資料庫伺服器　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　= DB2ADMIN
　本地資料庫別名　　　= SAMPLE

/* 讓 TestingUser 可以對 Orion 這個 schema 做 alter,create,drop */


C:\>db2 grant alterin,createin,dropin on schema orion to role role_o
DB20000I  SQL 指令已順利完成。

C:\>db2 terminate
DB20000I  TERMINATE 指令已順利完成。

C:\>db2 connect to sample user testinguser using testinguser

　　資料庫連線資訊

　資料庫伺服器　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　= TESTINGU...
　本地資料庫別名　　　= SAMPLE

/*
　　用TestingUser create procedure ，內容是 dynamic recreate sequence
　　傳入參數 Sequence name
*/


CREATE PROCEDURE SP_CRTSEQ(in SeqName varchar(32))
SPECIFIC SP_CRTSEQ
LANGUAGE SQL
BEGIN
　　DECLARE SQLCODE     INTEGER DEFAULT 0;
　　DECLARE RETCODE     INTEGER DEFAULT 0;
　　declare szStr varchar(128);  
　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,
　　　　　　　　　　　　　　　　　　　SQLWARNING,
　　　　　　　　　　　　　　　　　　　NOT FOUND
　　SET RETCODE = SQLCODE;
  
　　set szStr = 'DROP SEQUENCE ORION.' || Seqname;
　　EXECUTE IMMEDIATE szStr;
　　set szstr = 'create SEQUENCE ORION.' || Seqname || 
　　　　　　　　　' start with 1 increment by 1'; 
　　EXECUTE IMMEDIATE szstr;
　　COMMIT;
END

/* 執行 */


C:\>db2 call sp_crtseq('seq2')

　　傳回狀態 = 0

C:\>db2 select orion.seq2.nextval from sysibm.sysdummy1

1
-----------
          1

　　已選取 1 個記錄。

2008年11月13日星期四

Create Role using DB2 9

/*
　　1) 使用 DB2 9 的新功能建立 ROLE
　　2) 建立測試 user: TestingUser
　　3) grant ROLE to TestingUser
　　4) 由 system table/view 檢視 TestingUser 的權限
*/

/*　1)create role
　　以 db administrator 登錄，將安全管理員角色(secadm)授予User Orion
*/


C:\>db2 connect to sample user db2admin using db2admin

　　資料庫連線資訊

　資料庫伺服器　　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　　= DB2ADMIN
　本地資料庫別名　　　　= SAMPLE

C:\>db2 grant secadm on database to user orion
DB20000I  SQL 指令已順利完成。

/* ※ 否則直接以未授予secadm角色的user orion create role 會出現以下錯誤:
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。在SQL 處理程序期間，他已傳回：
SQL0552N "ORION" 沒有執行作業 "CREATE ROLE" 的專用權。 SQLSTATE=42502
*/

/* 以orion登錄 */


C:\>db2 connect to sample

　　資料庫連線資訊

　資料庫伺服器　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　= ORION
　本地資料庫別名　　　= SAMPLE

C:\>db2 create role role_o
DB20000I  SQL 指令已順利完成。

C:\>db2 grant all on table department to role role_o
DB20000I  SQL 指令已順利完成。

/*　2)建立測試 user: TestingUser
　　　使用「控制台」→「使用者帳戶」建立新的帳戶
　　　db2 不像 oracle
　　　db2 沒有create/drop user、或設定password，完全交由 os 來管理
*/

/*　3) grant ROLE to TestingUser */


C:\>db2 grant role_o to user TestingUser
DB20000I  SQL 指令已順利完成。

/*　4) 由 system table/view 檢視 TestingUser 的權限　*/


C:\>db2 connect to sample user TestingUser using testinguser

　　資料庫連線資訊

　資料庫伺服器　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　= TESTINGU...
　本地資料庫別名　　　= SAMPLE

C:\>db2 select rolename from syscat.roleauth where grantee = 'TESTINGUSER'

ROLENAME
-----------------
ROLE_O
　　
　　已選取 1 個記錄。

C:\>db2 select privilege,grantable,substr(objectname,1,16), \
db2 (續) => => substr(objectschema,1,16),substr(objecttype,1,10), \
db2 (續) => => substr(grantee,1,16) from sysibmadm.privileges, \
db2 (續) => => syscat.roleauth where authid = 'ROLE_O' \
db2 (續) => => and authid = rolename


PRIVILEGE　　GRANTABLE 3　　　　　 4　　　5　　　　6
----------- --------- ---------- ------ ------- ------------
UPDATE　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
REFERENCE　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
SELECT　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
INSERT　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
INDEX　　　　N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
DELETE　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
ALTER　　　　N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER

　　已選取 7 個記錄。

2008年11月11日星期二

Get Error Message using Function SYSPROC.SQLERRM in DB2 9.5

/*
　　DB2 9.5 FUNCTION　SYSPROC.SQLERRM
　　傳入 SQLCODE
　　傳回 Error Message
　　ex:
　　　　SELECT SYSPROC.SQLERRM(-402) FROM SYSIBM.SYSDUMMY1
　　傳回
　　　　SQL0402N　The data type of an operand of an arithmetic function or
　　　　operation "" is not numeric.
*/

/* create table for testing */


CREATE TABLE EXCEPTION_TEST
(
　　COL1　INTEGER NOT NULL PRIMARY KEY
) IN USERSPACE1

/* create table for storing log */


CREATE TABLE EXCEPTION_LOG
(
　　SYSDT           TIMESTAMP,
　　INPUT_VALUE     VARCHAR(50),
　　ERRCODE         INTEGER,
　　ERRMSG          VARCHAR(1024)
) IN USERSPACE1;

/* create procedure */


CREATE PROCEDURE SP_EXCEPTION(IN iNum INTEGER)
SPECIFIC SP_EXCEPTION
LANGUAGE SQL
BEGIN

　　DECLARE SQLCODE     INTEGER DEFAULT 0;
　　DECLARE RETCODE     INTEGER DEFAULT 0;
          
　　BEGIN        
　　　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,
　　　　　　　　　　　　　　　　　　　　　SQLWARNING,
　　　　　　　　　　　　　　　　　　　　　NOT FOUND
　　　　SET RETCODE = SQLCODE;

　　　　INSERT INTO EXCEPTION_TEST VALUES (iNum);

　　　　IF (RETCODE <> 0) AND (RETCODE <> 100) THEN
　　　　　　INSERT INTO EXCEPTION_LOG
　　　　　　VALUES (CURRENT TIMESTAMP,CHAR(iNum),RETCODE,
　　　　　　　　　　　SYSPROC.SQLERRM(RETCODE));
　　　　END IF;             
　　　　COMMIT;
　　END;     
END

/* call procedure twice for testing duplicate */


C:\>db2 call sp_exception(1)

　　傳回狀態 = 0

C:\>db2 call sp_exception(1)

　　傳回狀態 = 0

/* check exception_log table */


SELECT * FROM EXCEPTION_LOG

訂閱：文章 (Atom)

Orion's DB Lab Diary - since 2008

2008年11月20日星期四

LOAD From and using ADMIN_CMD in DB2 9

2008年11月18日星期二

Sorting an alphanumeric column in DB2 9

2008年11月17日星期一

Sorting an alphanumeric column in Oracle10g

2008年11月16日星期日

Grant privileges to Role and Test Dynamic SQL using DB2 9

2008年11月13日星期四

Create Role using DB2 9

2008年11月11日星期二

Get Error Message using Function SYSPROC.SQLERRM in DB2 9.5

About Myself

網誌存檔

分類

References

Orion's DB Lab Diary - since 2008

2008年11月20日 星期四

LOAD From and using ADMIN_CMD in DB2 9

2008年11月18日 星期二

Sorting an alphanumeric column in DB2 9

2008年11月17日 星期一

Sorting an alphanumeric column in Oracle10g

2008年11月16日 星期日

Grant privileges to Role and Test Dynamic SQL using DB2 9

2008年11月13日 星期四

Create Role using DB2 9

2008年11月11日 星期二

Get Error Message using Function SYSPROC.SQLERRM in DB2 9.5

About Myself

網誌存檔

分類

References

2008年11月20日星期四

2008年11月18日星期二

2008年11月17日星期一

2008年11月16日星期日

2008年11月13日星期四

2008年11月11日星期二