많은 기업 환경에서 단일한 DB
환경보다는 이기종 DB 환경을 운영하고 있다.
따라서 DB
개발자나 관리자는 되도록 더 많은
DBMS를 다룰 줄 아는 것이 업무에 도움이 된다.
특히 다양한 DBMS
중 MS SQL Server 2000(이하 MS SQL
Server)과
DB2를 함께 비교해볼
기회는
흔치
않다.
이 문서가 MS SQL
Server 그리고 DB2
환경에서 SQL
프로그래밍을 하는 개발자나 어느 한
DBMS로 마이그레이션을 검토하고 있는 운영 담당자에 조금이나마 도움이 되기를 바라고 서로의 특징을 상호 비교해보면서 MS SQL
Server와
DB2에 모두 친숙해질 수 있는 계기가 되었으면 한다.
임시
테이블, CTE, Merge into문
사용
안내
해당
세션이 DB상에서도
유지할
때만
존재하는
임시
테이블
대해서
활용을
바로
해보자. 비록
문법
부분이
약간
상이하지만
두 DBMS 모두
임시
테이블
생성이
가능하다. 반면에 MS SQL Server 2000에서는 CTE나 Merge into 문장은 DB2는
이미
지원
중이지만, MS SQL Server에서는
업그레이드
버전에서
지원이
되고
있다.
[표 1] DB별 임시 테이블 사용
SQL Server
|
DB2 UDB
|
CREATE TABLE #order (
prod_id INT NOT NULL PRIMARY KEY,
title_id CHAR(6) NOT NULL,
qty INT NOT NULL
)
GO
|
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE employee
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
|
SELECT * FROM #order
|
SELECT * FROM SESSION.gbl_temp
|
CREATE TABLE ##customer (
c_id INT NOT NULL PRIMARY KEY,
name VARCHAR(80) NOT NULL,
addr VARCHAR(200) NOT NULL,
tel CHAR(10) NOT NULL
)
GO
|
CREATE TABLE customer (
c_id INT NOT NULL PRIMARY KEY,
name VARCHAR(80) NOT NULL,
addr VARCHAR(200) NOT NULL,
tel CHAR(10) NOT NULL
)
NOT LOGGED INITIALLY
|
ü CTE(Common Table Expression) 형식은 MS SQL 2005에서부터
지원하고
있고, DB2에서는
더
일찍
사용이
가능했다.
[표 2] DB2
CTE문 사용
DB2
|
DB2에서 오라클 형식 지원(9.5)
|
WITH parent (lvl, deptno, deptname,admrdept) AS
(SELECT 1
,deptno, deptname,admrdept
FROM dept
WHERE deptno =
'E01'
UNION ALL
SELECT p.lvl + 1 ,c.deptno,c.deptname,
c.admrdept
FROM dept c, parent p
WHERE p.deptno=
c.admrdept
)
SELECT * FROM parent
|
select LEVEL, deptno, deptname,admrdept
FROM dept
START WITH
DEPTNO='A00'
CONNECT BY NOCYCLE PRIOR deptno=
admrdept
|
ü DB2에서는
오라클
형식의
재귀쿼리문(connect by 구문)도
지원하는데
작업
전에
미리
오라클
호환성의
옵션을
설정해야
한다. CLP> db2set DB2_COMPATIBILITY_VECTOR=0F
[표 3] MERGE INTO 사용
MERGE INTO table_name alias // 테이블
명만
올수
있음(셀렉트
쿼리 No)
USING (table|view|subquery) alias //
일반적으로
쓰는
쿼리 Ok
ON
(join condition) // where 절이라고
생각하면
됨(조건에
서브 OK)
WHEN MATCHED
THEN // 데이터
중복
건이 있는
경우 Update
UPDATE SET col1 = val1[, col2 = val2…]
WHEN NOT MATCHED THEN // 중복
건이
없는
경우
처리 Insert
INSERT (컬럼리스트) VALUES (값들....); |
ü DB2 MERGE INTO 문은
테이터를
넣어야
할
테이블에
값이
이미
있으면 Update, 값이
없으면 Insert 되는
등으로
활용
가능하다. MS SQL Server 2008부터
지원이
되고
있다.
[표 4] MERGE INTO 구조문
MERGE INTO 테이블명 X
USING (SELECT P_YLD_DATE AS P_YLD_DATE
FROM SYSIBM.SYSDUMMY1
) Y
ON X.YLD_DATE =
Y.P_YLD_DATE
AND X.LINE_ID = Y.P_LINE_ID
AND X.STEP_ID =
Y.P_STEP_ID
WHEN MATCHED THEN
UPDATE
SET X.PKG_CODE = P_PKG_CODE
,
X.ABNSTART_QTY = P_ABNSTART_QTY
WHEN NOT MATCHED THEN
INSERT
VALUES (
P_YLD_DATE , P_LINE_ID ,
P_STEP_ID , P_PART_ID
, P_PRIME_BATCH_YLD , CURRENT
TIMESTAMP , P_ABNSTART_QTY )
|
DB별
함수
표현식
비교
지금부터는 SQL Server와 DB2 함수를
연관시켜서
비교해
보도록
하자.
[표 5] DB별
함수
표현식
예제
DB2
|
OUTPUT
|
VALUES 1/3 *3 * 100
|
0
|
VALUES DEC(1)/3 *3 * 100
|
99.999999999999999999
|
VALUES CAST(DEC(1)/3* 3 *100 AS DEC(6,4))
|
99.9999
|
values digits( smallint(54))
|
00054
|
values digits(int(54))
|
0000000054
|
values digits(DEC(54, 3))
|
054
|
VALUES CURRENT DATE
|
2009. 1. 25
|
VALUES REPLACE(CAST(CURRENT DATE
AS VARCHAR(10)), '-', '')
|
20090125
|
VALUES CURRENT TIMESTAMP, CURRENT TIMESTAMP- 10
MINUTES
|
2009. 1. 25 오후 1:42:42
2009. 1. 25 오후 1:32:42
|
select
timestampdiff (32,
char(
timestamp('2008-12-30-00.00.00')-
timestamp('2008-12-08-00.00.00')))
from sysibm.SYSDUMMY1
|
3
* timestampdiff 파라미터
값
1 = Fractions of a second 2
= Seconds 4 = Minutes
8 = Hours
16 = Days 32 = Weeks
64 = Months
128 = Quarters 256 = Years
|
타 DB
개발자들이 자주 혼동하는
DB2의 날짜 표현식을 점검해보고 가보도록 하자.
[표 6] 날짜
관련
함수
매핑 (1)
SQL Server
|
DB2 UDB
|
결과
|
DATEPART(year, GETDATE())
|
YEAR(CURRENT DATE)
|
2009
|
DATEPART(quarter, GETDATE())
|
QUARTER(CURRENT DATE)
|
1
|
DATEPART(month, GETDATE())
|
MONTH(CURRENT DATE)
|
1
|
DATEPART(dayofyear, GETDATE())
|
DAYOFYEAR(CURRENT DATE)
|
25
|
DATEPART(day, GETDATE())
|
DAY(CURRENT DATE)
|
25
|
DATEPART(week, GETDATE())
|
WEEK(CURRENT DATE)
|
5
|
DATEPART(weekday, GETDATE())
|
DAYOFWEEK(CURRENT DATE)
|
1
|
DATEPART(hour, GETDATE())
|
HOUR(CURRENT TIME)
|
9
|
DATEPART(minute, GETDATE())
|
MINUTE(CURRENT TIME)
|
40
|
DATEPART(second, GETDATE())
|
SECOND(CURRENT TIME)
|
29
|
* VALUES CURRENT TIMESTAMP => 2009. 1. 25 오전 9:40:51
[표 7] 날짜
관련
함수
매핑 (2)
형식
|
RDBMS
|
변환
문법
|
'YYYY.MM.DD'
|
MSSQL
|
CONVERT(VARCHAR, date_exp, 102)
|
|
DB2
|
REPLACE(CHAR(DATE(date_exp),ISO), '-',
'.')
|
'HH:MI:SS'
|
MSSQL
|
CONVERT(VARCHAR, date_exp, 108)
|
|
DB2
|
CHAR(TIME(date_exp) , JIS )
|
'YYYY/MM/DD'
|
MSSQL
|
CONVERT(VARCHAR, date_exp, 111)
|
|
DB2
|
REPLACE(CHAR(DATE(date_exp), ISO), '-',
'/')
|
'YYYYMMDD'
|
MSSQL
|
CONVERT(VARCHAR, date_exp, 112)
|
|
DB2
|
CHAR(DATE(date_exp))
|
'HH24:MI:SS'
|
MSSQL
|
CONVERT(VARCHAR(8), date_exp, 114)
|
|
DB2
|
CHAR(TIME(date_exp) )
|
'YYYY.MM.DD HH24:MI'
|
MSSQL
|
CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp,
114)
|
|
DB2
|
REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp)
AS CHAR(5))
|
'YYYY/MM/DD HH24:MI:SS'
|
MSSQL
|
CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp,
114)
|
|
DB2
|
REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST(
TIME(date_exp))
|
저장
프로시저, 사용자
정의함수
활용
비교
[표 8]
저장
프로시저
문법
비교 (1)
T-SQL
|
SQL PL
|
DECLARE @varname datatype =
defaultvalue
|
DECLARE varname datatype DEFAULT
defaultvalue;
|
SELECT @var1=value
|
SET var1 = value;
|
SELECT @var1=colname FROM table
WHERE…
|
SET var1 = ( SELECT colname FROM table
WHERE…);
|
SELECT @v1=col1,@v2=col2,@v3=col3
FROM table...
|
SELECT col1,col2,col3 INTO v1,v2,v3
FROM table...
|
SELECT column_alias = column_name,…
|
SELECT column_name AS column_alias, ...
|
WHILE expression BEGIN … END
|
WHILE expression DO … END WHILE;
|
CONTINUE
|
ITERATE
|
BREAK
|
LEAVE loop_label
|
IF (…) BEGIN … END ELSE …
|
IF (…) THEN … ELSE … END IF;
|
[표 9] 프로시저
문법
비교 (2)
T-SQL
|
SQL PL
|
EXECUTE (‘INSERT INTO t1 VALUES(2)’)
|
INSERT INTO t1 VALUES (2); OR
EXECUTE IMMEDIATE (‘INSERT INTO t1
VALUES(2)’);
|
EXECUTE procname( parm1,parm2,...)
|
CALL procname( parm1,parm2,…);
|
EXECUTE @retval=procname(
parm1,parm2,…)
|
CALL procname( parm1,parm2,…);
GET DIAGNOSTICS retval =
RETURN_STATUS;
|
RETURN <int_value>
|
RETURN < int_expr>;
|
GOTO <label>
|
GOTO <lable>
|
PRINT
|
사용자
정의
함수 PUT_LINE()
|
RAISERROR, @@ERROR, @@SQLSTATUS
|
SQLCODE , SQLSTATE, SIGNAL
|
@@ROWCOUNT
|
GET DIANOSTICS rc = ROW_COUNT;
|
@@TRANCOUNT
|
-
|
[표 10] DB별로
저장
프로시저
생성
후
직접
해당
프로시저를
실행해
보기
SQL Server
|
DB2 UDB
|
CREATE PROC get_cheap_books
@cost money = 20.00
AS
SELECT title, price FROM redbooks WHERE price <
@cost
GO
|
CREATE PROCEDURE get_cheap_books ( )
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE default_cost DECIMAL(19,4) DEFAULT
20.00;
SET default_cost = v_cost;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO
CLIENT
FOR SELECT title, price
FROM redbooks
WHERE price < default_cost;
OPEN temp_cursor;
END;
|
EXE get_cheap_books
|
CALL get_cheap_books ( )
|
[표 11] DB2
저장
프로시저에서
DYNAMIC RESULT 옵션
사용하기
SQL Server
|
DB2 UDB
|
CREATE PROCEDURE titles_cursor
@cur_titles CURSOR VARYING OUTPUT
AS
SET @cur_titles = CURSOR FORWARD_ONLY STATIC
FOR
SELECT book_no, title FROM redbooks
OPEN @titles_cursor
GO
|
CREATE PROCEDURE titles_cursor()
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE titles_cursor CURSOR WITH HOLD WITH RETURN TO
CLIENT
FOR SELECT book_no, title
FROM redbooks;
OPEN titles_cursor;
END;
|
DB별
사용자
정의
함수
간단
예제
[표 12] 사용자
정의
함수(UDF) 비교
SQL SERVER
|
DB2 UDB
|
CREATE FUNCTION avg_price(@brand_id int)
RETURNS money
AS
BEGIN
RETURN ( SELECT avg(price)
FROM redbooks
WHERE brand_id = @brand_id)
END
GO
|
CREATE FUNCTION avg_price (v_brand_id INTEGER)
RETURNS DECIMAL(19,4)
LANGUAGE SQL
READS SQL DATA
RETURN ( SELECT AVG(price)
FROM redbooks
WHERE brand_id = v_brand_id)
!
|
CREATE FUNCTION books_by_brand (@brand_id int)
RETURNS TABLE
AS
RETURN ( SELECT book_no, title, price
FROM redbooks
WHERE brand_id = @brand_id)
GO
|
CREATE FUNCTION books_by_brand (v_brand_id
INTEGER)
RETURNS TABLE(
book_no CHAR(10),
title VARCHAR(80),
price DECIMAL(19,4))
LANGUAGE SQL
READS SQL DATA
BEGIN ATOMIC
RETURN ( SELECT book_no,title,price
FROM redbooks
WHERE brand_id = v_brand_id );
END
|
에러
처리
코드
안내
[표 13] 에러
코드문
SQL SERVER
|
DB2
|
@fetch_status
0 : Fetch 문이
정상적으로
실행되었을
경우
-1 : Fetch문이
실패하거나
범위에서
벗어났을
경우
-2 : 인출된
행이
없을
경우
|
SQLCODE SQLSTATE
0 00000: SQL Execute OK
SQLSTATE 00000: SQL문의
실행이
성공했으며
어떤
유형의
경고
또는
예외
상태도
발생하지
않았습니다.
100 02000: No Data Found
SQLSTATE 02000: 다음
예외
중
하나가
발생했습니다. SELECT INTO문
또는 INSERT문의
부속선택의
결과가
빈
테이블이었습니다. 탐색한 UPDATE 또는DELETE문에서
식별된
행
수가 0이었습니다. FETCH문에서
참조된
커서의
위치가
결과
테이블의
마지막
행
다음입니다. |
CLP> db2 ? "sql100n" -- SQLCODE 조회
CLP> db2 ? “02000”-- SQLSTATE 조회
|
지금까지 MS SQL Server와 DB2의
차이점을
비교해
보면
조금
다른
듯
하지만
많은
부분에
있어서
유사한
특징도
많이
공유하고
있다는
점을
느낄
수
있을
것이다.
여기까지
읽어보고 MS SQL Server와 DB2를
동시에
다루거나
또는
특정의 DB로
마이그레이션
하는
데
있어서
부담감을
조금
덜고
좀더
친숙하게
다가서는
계기를
되었기를
희망해본다.
[참고
자료]
- Microsoft SQLServer to DB2 Migration Comparison White
Paper
- Microsoft SQL Server to IBM DB2 UDB Conversion
Guide
- Leverage your Microsoft SQL Server 2000 Skills to
Learn DB2 UDB Version 8
§ http://users.handysoft.co.kr/~jelong/dateformat.htm
|
댓글 없음:
댓글 쓰기