출처 : http://soff.tistory.com/106
이번 회에는 DB2 애플리케이션 개발 중에 오라클 DB와 상이한 구문과 SQL 차이점에 대해서 설명하도록 하겠다. 곧 출시될 DB2 Viper 2에서는 오라클 DB에서만 사용하는 outer join의 + 구문이나 connect by의 Recursive SQL, row number, 오라클 DB 전용 함수들이 DB2의 레지스터리 값 변경 이후에 오라클 DB와 동일하게 사용할 수 있어져서 오라클 DB 사용자들의 DB2 사용이 용이해 졌다.
Data Type
오라클 DB와 DB2의 데이터 타입의 차이점은 DB2에서의 데이터 타입은 사용자 편의보다는 옵티마이저가 가장 잘 해석할 수 있도록 세분화 되어 있다는 점이다. 이는 DB2는 기본적으로 오라클 DB와 달리 Rule base 옵티마이저 모드를 지원하지 않기에 옵티마이저가 최대한 잘 해석할 수 있도록 만들어 주기 위함으로 보인다.
예를 들어 오라클 DB에서의 number와 같이 정수, 소수를 대표하는 Data Type은 DB2에서는 크기에 따라 SMALLINT, INTEGER, BIGINT, DECIMAL(p,s)등으로 세분화 되어 쓰여지게 되어 있다.
|
Oracle Data Type
|
DB2 Data Type
|
Scope (DB2)
|
|
CHAR(n)
|
CHAR(n)
|
254 byte
|
|
VARCHAR2(n)
|
VARCHAR(n)
|
32,672 byte
|
|
LONG
|
LONG VARCHAR(n)
|
32,700 byte
|
|
NUMBER
|
SMLLINT
|
+ - 32,768 (5 digits, 2byte)
|
|
INTEGER
|
2 147 483 648 (10 digits, 4byte)
|
|
BIGINT
|
9,223,372,036,854,775,808 (64bit integers, 8type)
|
|
DECIMAL(p,s)
NUMERIC(p.s)
|
정밀도 31
|
|
REAL
|
|
|
DOUBLE (FLOAT)
|
+2.225E-307
|
|
BLOB
|
BLOB(n)
|
2GB
|
|
CLOB
|
CLOB(n)
|
2GB
|
|
NCLOB
|
DBCLOB
|
2GB
|
|
DATE
|
TIMESTAMP
|
0001-01-01-00.00.00.000000
|
|
DATE
|
0001-01-01
~ 9999-12-31
|
|
TIME
|
00:00:00
~ 24:00:00
|
DB2는 오라클 DB에서와 같이 숫자와 문자간 자동 형 변환을 지원하지 않는다 따라서 문자와 숫자간 비교나 조인시 반드시 CAST, INT, CHAR 함수 등으로 형 변환을 해주어야 한다.
예>
select * from tab1 where col1 = int(‘1’)
select * from tab1 where col1 = cast (‘1’ as int)
DB2에서의 날짜 연산
1. to_char() 함수: DB2에도 오라클 DB의 to_char()와 동일한 이름의 함수는 있으나 오라클 DB에서와 같이 날짜 형식의 다양한 포멧팅을 지원하지는 않는다. 오라클 DB의 TO_CAHR() 함수를 쓰기 위해서는 별도의 UDF가 필요하다.(오라클/MS-SQL Built in Function에 대한 DB2 UDF는 첨부되어 있는 zip 파일을 참조해서 사용하기 바란다.)
2. sysdate(oracle) => current date(현재 날짜), current timestamp (현재 시간소인)
3. hex(current date) à 문자열 YYYYMMDD 형식 예> 20080101
4. date / year / month / day 함수 à 입력된 시간소인(혹은 날짜형식 문자열)에 날짜(date yyyy-mm-dd), 년도(year –yyyy), 월(month – mm 2월인 경우 02가 아니고 2로 표시됨), 일(day – dd)로 변환하는 scalar 함수
5. 날짜 더하기/ 빼기 “+/- n days”, “+/- months”, “+/- years” 예>current date + 1 months
6. 날짜끼리 뺄 경우 결과는 YYYYMMDD(decimal(8,0))형식으로 두 일자 사이의 기간을 나타내게 된다. 예) values(date('2008-02-10') -date('2007-01-01')); à 00010109 (1년 1개월 9일)
7. 날짜 포멧 변경
char(current date, iso) – 2008-01-01
char(current date, eur) – 01.01.2008
char(current date, usa) – 01/01/2008
Page navigation을 위한 Row Number
OLAP 함수인 row_number() over(order by 컬럼이름)로 대체해서 사용해야 한다.
Select empno, fullname
From (select empno
, firstnme || ' ' || lastname as fullname
, row_number() over (order by empno) as r_num
from employee ) as t1
Where r_num >= 10 and r_num <20
Dummy Table
Sysibm.sysdummy1 (DB2)
select * from sysibm.sysdummy1
Truncate Table
DB2에서는 오라클 DB에서의 Truncate table은 존재하지 않는다. DB2에서 테이블의 데이터를 효율적으로 지우는 방법은 아래와 같다.
Import from /dev/null of del replace into 테이블이름
Alter table 테이블이름 activate not logged initially with empty table
à ALTER TABLE EMPLOYEE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
Decode문
Decode문은 지원하지 않는다. Case문으로 대체해야 한다.
UPDATE staff
SET comm = salary + DECODE(job,'Mgr',100,0)
UPDATE staff
SET comm = salary + CASE job
WHEN 'Mgr' THEN 100 ELSE 0 END
NULL 처리
|
Oracle
|
DB2
|
|
NVL(TO_CHAR(MGR_ID),'No Manager')
|
COALESCE(MGR _ID,'No Manager')
VALUE(MGR _ID,'No Manager')
|
Outer join
(+) 기호를 지원하지 않음으로, RIGHT OUTER JOIN / LEFT OUTER JOIN / FULL OUTER JOIN으로 대체해야 한다.
Sequence
데이터베이스 전체에서 관리되는 Sequence와 별도로 테이블마다 제공되는 컬럼 sequence도 존재한다. 각 사용법은 아래와 같다.
|
|
Oralce
|
DB2
|
|
시스템
시퀀스 정의
|
Create sequence test_seq start with 1
Increment by 1
|
Create sequence test_seq start with 1
Increment by 1
|
|
시퀀스 사용
|
Test_seq.nextval
Test_seq.currval
|
Nextval for test_seq
Prevval for test_seq
|
Procedure
1. 프로시저 생성시 CREATE OR REPLACE 옵션을 제공하지 않는다. 따라서 이미 존재하고 있는 프로시저일 경우 Drop 후 재생성 해야 한다. 이때 프로시저를 참조하는 Routine이 있으면 같이 Drop하고 생성해 주어야 한다.
2. 데이터 타입 정의시 반드시 길이를 정해 주어야 한다. 길이가 정해져 있지 않은 데이터 타입은 사용이 불가능 하다. 또한 참조되는 객체의 길이가 서로 다를 경우 생성되지 않으므로 참조되는 객체의 길이와 타입은 반드시 동일하게 생성해 주어야 한다.
3. 입/출력 값에 설정시 IN, OUT, INOUT을 변수 명 앞에 선언하고 변수와 변수 타입(크기가 명시된)을 선언한다.
4. LANGUAGE SQL 옵션은 ver7.x에서는 반드시 써야 하지만 ver8.x부터는 선택 사항이다.
5. Procedure 호출은 Call 명령어를 통해 호출한다.
à Call stored_procedure_name (input1, input2….)
|
CREATE OR REPLACE PROCEDURE test_sum_pr (i_log IN DATE, i_type IN VARCHAR2)
|
|
à CREATE PROCEDURE test_sum_pr (IN i_log DATE, IN i_type VARCHAR(10))
|
Temporary Table
Temporary table을 사용하기 위해서는 반드시 사용자 임시 테이블 스페이스 타입의 테이블 스페이스가 존재해야 하며, 세션에 독립적으로 생성되어 세션이 종료됨과 함께 자동으로 소멸된다.
-- User Temp 테이블 스페이스 작성
CREATE USER TEMPORARY TABLESPACE apptemps
MANAGED BY SYSTEM USING ('apptemps');
-- Temp Table 작성
DECLARE GLOBAL TEMPORARY TABLE t_employees LIKE employee NOT LOGGED;
Trigger
1. 트리거 생성시 CREATE OR REPLACE 옵션을 제공하지 않는다. 따라서 이미 존재하는 트리거일 경우 drop후 재 생성해야 한다.
2. Before 트리거에서 NO CASCADE 옵션은 필수 항목이다.
3. MODE DB2SQL 옵션은 필수 항목이다.
4. UPDATE & DELETE 구문은 Before&After를 사용해야 한다.
5. 오라클 DB의 INSERT OR UPDATE OR DELETE ON과 같은 다중 역할을 하는 트리거 생성이 불가능하다. 따라서 각 역할에 따라 트리거를 별도로 생성해 주어야 한다.
6. INNER SQL(Select .. INTO)을 통한 변수 설정이 불가능 하기 때문에 반드시 SET을 통해 SELECT 결과를 변수에 입력하여야 한다. 이때 SELECT 문장의 값은 반드시 단일 Row여야 한다.
|
ID_EVENT (crud char(1), id varchar(40), name varchar(40))
ID_LOG (seq int, crud char(1), id varchar(40), name varchar(40), date date)
|
|
Oracle
|
|
CREATE OR REPLACE TRIGGER ID_EVENT_TR
AFTER INSERT OR UPDATE OR DELETE ON ID_EVENT
FOR EACH ROW
DECLARE
ret binary_integer;
crud char(1);
vid varchar2(40);
vname varchar2(40);
BEGIN
if inserting then
crud := 'C'; vid := :new.ID; vname := :new.NAME;
elsif deleting then
crud := 'D'; vid := :old.ID; vname := :old.NAME;
else
crud := 'U'; vid := :old.ID; vname := :old.NAME;
end if;
insert into ID_LOG (seq, crud, id, name, date)
values (test.nextval, crud, vid, vname ,sysdate);
END;
|
|
DB2
|
|
CREATE TRIGGER ID_EVENT_TR_DEL1
AFTER DELETE ON ID_EVENT
REFERENCING OLD AS OLD
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE ret DECIMAL(31,0);
DECLARE crud CHAR(1);
DECLARE vid VARCHAR(40);
DECLARE vname VARCHAR(40);
SET crud = 'D';
SET vid = OLD.ID;
SET vname = OLD.DOC_ID;
insert into ID_LOG (seq, crud, id, name, date)
VALUES (nextval FOR test, crud, vid, vname ,current date);
END;
CREATE TRIGGER ID_EVENT_TR_INS1
AFTER INSERT ON ID_EVENT
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE ret DECIMAL(31,0);
DECLARE crud CHAR(1);
DECLARE vid VARCHAR(40);
DECLARE vname VARCHAR(40);
SET crud = 'C';
SET vid = NEW.ID ;
SET vname = NEW.NAME;
insert into ID_LOG (seq, crud, id, name, date)
VALUES (nextval FOR test, crud, vid, vname ,current date);
END;
CREATE TRIGGER ID_EVENT_TR_UPD1
AFTER UPDATE ON ID_EVENT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE ret DECIMAL(31,0);
DECLARE crud CHAR(1);
DECLARE vid VARCHAR(40);
DECLARE vname VARCHAR(40);
SET crud = 'U';
SET vid = OLD.ID ;
SET vname = OLD.NAME;
insert into ID_LOG (seq, crud, id, name, date)
VALUES (nextval FOR test, crud, vid, vname ,current date);
END;
|
Function
Built-in 함수를 제외하고 유저에 의해 생성된 Function을 UDF(User Define Function)라 한다.
1. Input parameter는 선택 사항이지만 가로는 필수 사항이다.
2. Return Type은 아래 3가지 중 하나는 반드시 명시되어야 한다.
A. Scalar: 단일 값 반환
B. Table: From절에 사용되는 Table 반환
C. Row: Transform 함수로 사용되면 하나의 row를 반환
3. LANGUAGE SQL은 V8부터 옵션 사항이다.
4. Return 키워드 후에는 반드시 SQL 함수 Body를 작성해야 한다.
예제)
DB2의 LAST_DAY 함수
CREATE FUNCTION DB2DEV.LAST_DAY (D DATE)
RETURNS DATE
SPECIFIC DB2APP.LAST_DAYDATE
LANGUAGE SQL
RETURN D + 1 month - day(D + 1 month) day;
오라클 DB의 LAST_DAY 함수
CREATE FUNCTION months_between(d1 TIMESTAMP, d2 TIMESTAMP)
RETURNS FLOAT
LANGUAGE SQL
RETURN 12*(year(d1) - year(d2)) + month(d1) - month(d2)
+ (TIMESTAMPDIFF(2,CHAR(d1 - (d2 + (12*(year(d1) - year(d2))
+ month(d1) - month(d2)) MONTHS))) / 2678400.0)
SQL PL (Oracle PL/SQL)
변수 정의와 규칙
Oracle PL/SQL 같은 경우 아래의 4가지 위치에 변수 값 정의가 가능하다.
1. 스토어드 프로시저 또는 함수 파라미터 리스트 안
2. 스토어드 프로시저, 함수, 트리거의 내부
3. 패키지 정의
4. 패키지 body 정의
DB2에서는 오라클 DB에서와 같이 함수나 프로시저를 그룹화하는 패키지 개념이 없다. 따라서 변수 값 정의는 패키지를 제외한 위의 두 가지 경우만 허용된다.
DB2 SQL PL에서도 native data type과 user defined distinct type의 여러 형태 정의가 가능하다. 변수 정의시 반드시 DECLARE 구문을 써서 정의하여야 하며, 변수 값 정의를 위해서는 반드시 BEGIN … END 블록 안에서 정의되어야 한다. 초기값 설정 이후 변수에 값을 설정할 경우 DB2에서는 SET 문장을 써서 변수를 assign 한다.
예> 초기값 설정
Oracle PL/SQL: l_value NUMBER(10,2) :=0.0;
DB2 SQL PL: DECLARE l_value NUMERIC(10,2) DEFAULT 0.0;
예> 변수 값 할당
Oracle PL/SQL: l_value = 99.99;
DB2 SQL PL: SET l_value = 99.99;
à Trigger의 경우 INNER SQL(Select .. INTO)을 통한 변수 설정이 불가능 하기 때문에 반드시 SET을 통해 SELECT 결과를 변수에 입력하여야 한다. 이때 SELECT 문장의 값은 반드시 단일 Row여야 한다.
SET l_value =(SELECT balance from account_info where account_no = actNo);
Cursor 처리
1. 응용프로그램에서 결과 집합을 검색하기 위해 사용하는 기법으로 Select 문에서 여러 건의 데이터를 반환하는 경우에 사용된다.
2. 커서는 declare / open / fetch / close의 단계로 사용된다.
3. Open된 커서는 UOW(unit of work) 종료시(commit / rollback) 소멸되지만 DB2에서는 With HOLD 옵션으로 커서의 위치를 유지할 수 있다.
4. DB2는 Memory level의 Lock을 사용하며 그에 따른 Cursor의 유형도 읽기 전용(READ ONLY)과 업데이트 가능한 UPDATEABLE 커서가 있다.
5. UPDATEABLE(FOR UPDATE OF)를 사용하면 데이터를 Fetch 하는 동안 S모드 대신 U모드 LOCK이 적용되어 Deadlock을 방지할 수 있다.
|
Oracle
|
DB2
|
용도
|
|
CURSOR cursor_name
[(cursor_parameter(s))]
IS select_statement;
|
DECLARE cursor_name
CURSOR [WITH HOLD] [WITH RETURN] [TO CALLER | TO CLIENT] FOR select-statement
[FOR READ ONLY|UPDATE OF|]
|
Declare
|
|
OPEN cursor_name
[(cursor_parameter(s))];
|
OPEN cursor_name [USING
host-variable]
|
Open
|
|
FETCH cursor_name INTO
variable(s)
|
FETCH [from] cursor_name
INTO variable(s)
|
Fetch
|
|
UPDATE table_name
SET statement(s)...
WHERE CURRENT OF
cursor_name;
|
UPDATE table_name
SET statement(s)...
WHERE CURRENT OF
cursor_name
|
Update Fetch
|
|
DELETE FROM table_name
WHERE CURRENT OF
cursor_name;
|
DELETE FROM table_name
WHERE CURRENT OF
cursor_name
|
Delete Fetch
|
|
CLOSE cursor_name;
|
CLOSE cursor_name
|
Close
|
커서 예외 처리
|
Oracle
|
DB2
|
|
%ISOPEN
|
Open cursor에서 커서가 이미 열려 있는 상태면 SQLCODE = -501 / SQLSTATE=24501
Fetch에서 커서가 아직 열려 있지 않은 상태면 SQLCODE = -502 / SQLSTATE=24502
|
|
Oracle
|
IF c1%ISOPEN THEN
fetch c1 into var1;
ELSE -- cursor is closed, so open it
OPEN c1;
fetch c1 into var1;
END IF;
|
|
DB2
|
DECLARE cursor_notopen CONDITION FOR SQLSTATE 24501;
DECLARE CONTINUE HANDLER FOR cursor_notopen
BEGIN
open c1;
FETCH c1 int var1;
END;
...
FETCH c1 into var1;
|
|
%NOTFOUND
|
SQLCODE = 100이거나 SQLSTATE = ‘02000’
|
|
Oracle
|
OPEN cur1;
LOOP
FETCH cur1 INTO v_var1;
EXIT WHEN cur1%NOTFOUND;
...
END LOOP;
|
|
DB2
|
DECLARE SQLCODE int DEFAULT 0;
……
OPEN c1;
L1: LOOP
FETCH c1 INTO v_var1;
IF SQLCODE = 100 THEN
LEAVE L1;
END IF;
...
END LOOP L1;
|
|
%FOUND
|
SQLCODE = 0이거나 SQLSTATE = ‘00000’
|
|
Oracle
|
DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN -- 행이 삭제 되었을 경우
INSERT INTO emp_table
VALUES (my_empno, my_ename);
|
|
DB2
|
DELETE FROM emp WHERE empno = my_empno;
IF SQLCODE = 0 THEN -- delete succeeded
INSERT INTO emp_table VALUES (my_empno, my_ename);
|
|
%ROWCOUNT
|
Fetch First n rows only를 사용하거나 Loop를 사용하여 count 증가
exit/GET DIAGNOSTICS 문을 사용하여 반환된 행 계산
|
|
Oracle
|
LOOP
FETCH c1 INTO my_ename,my_deptno;
IF c1%ROWCOUNT > 10 THEN
EXIT;
END IF;
...
END LOOP;
DELETE FROM emp_table
WHERE ...
IF SQL%ROWCOUNT > 10 THEN
-- 10행 이상 삭제 시
...
END IF;
|
|
DB2
|
DECLARE rc INT DEFAULT 0;
…….
DELETE FROM emp_table WHERE ...
GET DIAGNOSTICS rc = ROW_COUNT;
IF rc > 10 THEN ...
END IF;
|
|
GET DIAGNOSTICS는 SELECT /SELECT INTO 문장은 지원하지 않는다.
한 개의 ROW도 SELECTING 되지 않을 경우 SQLCODE = 100
하나의 ROW가 SELECTING 될 경우 SQLCODE=0
하나의 ROW 이상이 SELECTING 될 경우 SQLCODE=-811 (SQLSTATE=21000 – SQLERROR)
|
| |
|
|
|
Debugging
DB2에는 오라클 DB의 dbms.output.put_line과 같은 함수가 존재 하지 않는다. 따라서 dbms.output.put_line 함수와 같은 UDF Fuction을 만들거나, 디버깅 테이블을 만들어 디버깅 하고자 하는 값을 새로 만든 디버깅 테이블에 Insert 하는 방법이 있다.
아래의 방법은 put_line UDF를 생성하여 디버깅 하는 방법에 대한 설명이다. 해당 원문은 아래의 Site에서 확인 하기 바란다.
http://www.ibm.com/developerworks/db2/library/techarticle/0302izuha/0302izuha.html
put_line UDF생성 방법
CREATE PROCEDURE TESTCASE() RESULT SETS 0 LANGUAGE SQL
L_TESTCASE:
BEGIN NOT ATOMIC
DECLARE V_DEPTNO SMALLINT;
DECLARE V_DEPTNAME VARCHAR(20);
DECLARE V_DIVISION VARCHAR(20);
-- FOR DEBUG
DECLARE V_NUM SMALLINT DEFAULT 0;
DECLARE V_MSG1 VARCHAR(4000);
DECLARE V_MSG2 VARCHAR(1);
-- END
-- SAMPLE1
SET V_NUM=2000;
SET V_MSG1='debugging start';
VALUES(PUT_LINE(V_NUM,V_MSG1)) INTO V_MSG2;
-- SAMPLE2
VALUES(PUT_LINE(SMALLINT(2),'this is no2'))
INTO V_MSG2;
-- SAMPLE3
VALUES(PUT_LINE(3)) INTO V_MSG2;
-- SAMPLE4
VALUES(PUT_LINE('##number4##')) INTO V_MSG2;
-- SAMPLE5
SET V_NUM = 0;
FOR V_C1_REC AS C1 CURSOR FOR
SELECT DEPTNUMB, DEPTNAME, DIVISION
FROM ORG ORDER BY DEPTNUMB DESC
DO
SET V_DEPTNO = v_C1_REC.DEPTNUMB;
SET V_DEPTNAME = v_C1_REC.DEPTNAME;
SET V_DIVISION = v_C1_REC.DIVISION;
SET V_NUM = V_NUM + 1;
SET V_MSG1 ='DEPTNO=' || CHAR(V_DEPTNO)||',' ||'DEPTNAME='||V_DEPTNAME||','
||'DIVISION='||V_DIVISION;
VALUES(PUT_LINE(V_NUM,V_MSG1)) INTO V_MSG2;
END FOR;
VALUES(PUT_LINE(32000,'end of the program'))
INTO V_MSG2;
END L_TESTCASE
<참조>
§ Oracle to DB2 UDB Conversion Guide(IBM.com/redbooks)
§ DB2 사용자 가이드(IBM)
