MS SQL Server 개발자를 위한 DB2 가이드 (2)

MS SQL Server 개발자를 위한 DB2 가이드 (2) * 출처: http://www.kdug.kr/ * 저자: 강창성 님 개발/관리/마이그레이션 | 2009-07-03 16:02:48

많은 기업 환경에서 단일한 DB 환경보다는 이기종 DB 환경을 운영하고 있다. 따라서 DB 개발자나 관리자는 되도록 더 많은 DBMS를 다룰 줄 아는 것이 업무에 도움이 된다. 특히 다양한 DBMS 중 MS SQL Server 2000(이하 MS SQL Server)과 DB2를 함께 비교해볼 기회는 흔치 않다. 이 문서가 MS SQL Server 그리고 DB2 환경에서 SQL 프로그래밍을 하는 개발자나 어느 한 DBMS로 마이그레이션을 검토하고 있는 운영 담당자에 조금이나마 도움이 되기를 바라고 서로의 특징을 상호 비교해보면서 MS SQL Server와 DB2에 모두 친숙해질 수 있는 계기가 되었으면 한다. 오브젝트 생성 방법 [표 1] 테이블 생성 예시문 SQL Server DB2 UDB CREATE TABLE [employee] ( [empid] INT, [name] VARCHAR(40) NOT NULL, [job] VARCHAR(15) NOT NULL, [hire_date] DATETIME NOT NULL, [department] INT NULL, [basic salary] DECIMAL(8,2) NULL, [commission] DECIMAL(8,2) NULL) CREATE TABLE employee ( empid INT, name VARCHAR(40) NOT NULL, job VARCHAR(15) NOT NULL, hire_date TIMESTAMP NOT NULL, department INT, basic_salary DECIMAL(8,2), commission DECIMAL(8,2)) CREATE TABLE employee1( empid INT IDENTITY, basic_salary DECIMAL(8,2) NULL, commission AS (basic_salary * 0.5)) CREATE TABLE employee1( empid INT GENERATED ALWAYS AS IDENTITY, basic_salary DECIMAL(8,2), commission GENERATED ALWAYS AS (basic_salary * 0.5)) DB2와 MS SQL Server에서의 클러스터 인덱스를 지정하는 문법이 약간 상이하다. [표 2] 인덱스 생성(1) 예제 SQL Server DB2 UDB CREATE CLUSTERED INDEX [PK_author_id] ON [authors] ( [author_id] ASC ) CREATE INDEX PK_author_id ON authors (author_id ASC) CLUSTER CREATE NONCLUSTERED INDEX [idx_authors] ON [authors] ([name] ASC, [firstname] ASC ) CREATE INDEX idx_authors ON authors (name ASC, firstname ASC) [표 3] 인덱스 생성(2) 예제 SQL Server DB2 UDB CREATE UNIQUE INDEX t1_idx ON t1 (col1) CREATE UNIQUE INDEX t1_idx ON t1 (col1) INCLUDE col2 - CREATE INDEX <name> ON <table> (<columns>) ALLOW REVERSE SCANS ü DB2에서는 UNIQUE INDEX 에서만 INCLUDE 옵션을 지원하는데 이 옵션을 사용하면 인덱스 생성시 좀더 인덱스 space 할당 영역을 절약할 수 있다. [그림] Index 비교 [composite index 경우] [INCLUDE index 경우] [표 4] Clustered Primay key 지정하는 예제 SQL Server DB2 UDB > create table clu_test ( quater1 datetime not null, address varchar(20) not null ) > ALTER TABLE clu_test ADD CONSTRAINT PK_clu_test PRIMARY KEY clustered ( quater1, address ) Command was executed successfully > sp_helpindex clu_test index_name index_description index_keys ------------- ------------------------------------------------- ---------------- PK_clu_test clustered, unique, primary key located on PRIMARY quater1, address > select SERVICE_LEVEL from sysibmadm.env_inst_info SERVICE_LEVEL ---------------- DB2 v9.1.0.3 >create table clu_test ( quater1 date not null, address varchar(20) not null ) >CREATE UNIQUE INDEX PK_clu_test ON clu_test ( quater1, address ) CLUSTER; >ALTER TABLE clu_test ADD CONSTRAINT PK_clu_test PRIMARY KEY ( quater1, address ) ; > select TABNAME ,INDNAME,COLNAMES ,UNIQUERULE, INDEXTYPE from syscat.indexes where tabname='CLU_TEST' TABNAME INDNAME COLNAMES UNIQUERULE INDEXTYPE ---------- ----------- ---------------- ------------- ------------ CLU_TEST PK_CLU_TEST +QUATER1+ADDRESS P CLUS ü DB2에서는 Clustered Primary key를 사용하려면 바로 primary 키로 지정하지 않고 먼저 클러스터 키를 생성 후 Primary key를 선언하는 절차가 있다. 반면에 MS SQL Sever는 바로 디폴트로 Clustered Primary key 지정이 가능하다. SQL문 비교 문자 연결식으로 MS SQLServer에서는 ‘+’를, DB2에서는 CONCAT 또는 || 를 사용한다. [표 5] 문자 연결식 SQL 비교 SQL Server DB2 UDB SELECT ‘Author’=RTRIM(name) + ‘, ‘ + firstname, ‘Title’=title FROM redbook.dbo.authors AS A JOIN redbook.dbo.redbooks AS RB ON A.author_id=RB.author_id WHERE A.state <> ‘CA’ ORDER BY 1 SELECT RTRIM(name) CONCAT ‘, ‘ CONCAT firstname AUTHORS, title TITLE FROM asn.authors AS A INNER JOIN asn.redbooks AS RB ON A.author_id=RB.author_id WHERE A.state <> ‘CA’ ORDER BY 1 [표 6] SELECT INTO문 비교 SQL Server DB2 UDB Select into SELECT * INTO t2 FROM t1 CREATE TABLE t2 AS (SELECT t1.* FROM t1 ) DEFINITION ONLY; INSERT INTO t2 SELECT t1.* FROM t1; [표 7] SELECT * INTO 문 결과 SQL Server DB2 UDB SELECT stor_id , ord_num INTO SAMPLE_SALES FROM SALES CREATE TABLE SAMPLE_SALES AS (SELECT stor_id , ord_num FROM SALES) DEFINITION ONLY INSERT INTO SAMPLE_SALES SELECT stor_id , ord_num FROM SALES stor_id ord_num ---------- ---------- 6380 6871 8042 423LL930 8042 P723 stor_id ord_num ---------- ---------- 6380 6871 8042 423LL930 8042 P723 Lock 제어 옵션 소개 DB2는 트랜잭션 처리 부분에서 MS SQL Server와 마찬가지로 commit, rollback 기능을 지원한다. DB2 저장 프로시저에서 ATOMIC 옵션을 지정하였을 경우에는 해당 소스를 하나의 트랜잭션으로 간주하여 프로그램 중간에 ROLLBACK, SAVEPOINT 등을 사용하지 않는다. [표 8] [DB2] isolation levels SQL Server DB2 UDB Read Committed (default) Cursor Stability (CS, default) Serializable Repeatable Read (RR) Repeatable Read Read Stability (RS) Uncommitted Read Uncommitted Read (UR) [표 9] TRANSACTION 처리 SQL Server DB2 COMMIT [ TRAN [ SACTION ] [<transaction name>]] COMMIT ROLLBACK [TRAN[SACTION]] [<transaction name>] [<savepoint name>] ROLLBACK [WORK] [TO SAVEPOINT <savepoint name>] SAVE TRAN[SACTION] <savepoint name> SAVEPOINT <savepoint name > [UNIQUE] [ON ROLLBACK RETAIN CURSORS] [ON ROLLBACK RETAIN LOCKS] * NOT ATOMIC Compound Statement [표 10] DB별 트랜잭션 처리 실습 예제 MS SQL DB2 begin tran INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING', 301) SAVE TRANSACTION T1 INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520) SAVE TRANSACTION T2 INSERT INTO DEPARTMENT VALUES ('C40', 'IT SUPPORT', 430) SAVE TRANSACTION T3 INSERT INTO DEPARTMENT VALUES ('R50', 'RESEARCH', 150) ROLLBACK TRANSACTION T2 COMMIT CREATE PROCEDURE SP_TRAN ( ) LANGUAGE SQL BEGIN INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING', 301) ; SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS ; INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520) ; SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS ; INSERT INTO DEPARTMENT VALUES ('C40', 'IT SUPPORT', 430) ; SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS ; INSERT INTO DEPARTMENT VALUES ('R50', 'RESEARCH', 150) ; ROLLBACK WORK TO SAVEPOINT SAVEPOINT2; COMMIT; END SELECT * FROM DEPARTMENT DEPTNO DEPTNAME MGRNO --------- ----------- -------- A20 MARKETING 301 B30 FINANCE 520 CALL SP_TRAN ( ) SELECT * FROM DEPARTMENT DEPTNO DEPTNAME MGRNO --------- ----------- -------- A20 MARKETING 301 B30 FINANCE 520 [표 11] 조회문 쿼리 사용시 lock blocking을 원치 않을 때 SQL Server DB2 UDB Select * From sales with (nolock) Select * From sales nolock Select * From sales with ur ü 미확약 읽기: uncommitted read 작업 단위(UOW)동안 읽은 행을 다른 응용프로그램 프로세스가 변경할 수 있다. ü 반복읽기: repeatable read, 작업 단위동안 읽혀진 행은 작업 단위가 완료될 때까지 다른 응용프로그램 의해 변경되지 않는다. ü 읽기 안정성: read stability, 작업 단위(UOW) 동안 읽혀진 행은 작업이 완료될 때까지 다른 응용프로그램 프로세스에 의해 변경되지 않는다. ü 커서 안정성: cursor stability 커서 안정성(CS)은 다른 응용프로그램 프로세스가 변경한 행을 그 응용프로그램 프로세스 가 확약할 때까지 읽을 수 없게 한다.