About DATABASE

출처 : 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)등으로 세분화 되어 쓰여지게 되어 있다.

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 처리

Outer join

(+) 기호를 지원하지 않음으로, RIGHT OUTER JOIN / LEFT OUTER JOIN / FULL OUTER JOIN으로 대체해야 한다.

Sequence

데이터베이스 전체에서 관리되는 Sequence와 별도로 테이블마다 제공되는 컬럼 sequence도 존재한다. 각 사용법은 아래와 같다.

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….)

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여야 한다.

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을 방지할 수 있다.

커서 예외 처리

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)