DB2 요약
1. 데이타베이스 생성이 다음과 같은 기본 스키마가 제공됩니다.
- SYSIBM: 시스템 카탈로그 테이블의 스키마
- SYSCAT: 시스템 카탈로그 뷰의 스키마
- SYSFUN: 기본적으로 제공되는 사용자 정의 함수의 스키마
2. 스키마 생성 구문
-> Create schema <스키마명> authorization <authorization_name>
3. 스키마 제거 구문
-> Drop schema <스키마명> restrict
4. 생성된 스키마에 대한 정보는 syscat.schemata 뷰를 이용하여 확인합니다.
5. 예문
db2=> create schema amj authorization HR
db2=> create table amj.T1 (C1 int)
db2=> list tables for schema <스키마명>
db2=> select * from syscat.schemata
6. 특정 스키마에 속하는 테이블과 뷰를 확인할 경우 "list tables for schema <스키마명>"를 실행합니다.
7. describe table [테이블명]을 이용하여 테이블 구조를 확인합니다.
8. 더미테이블은 sysibm.sysdummy1 입니다.
db2=> select to_char(current timestamp, 'yyyymmddhh24miss') from sysibm.sysdummy1;
9. 뷰 정보 조회는 syscat.view 카탈로그 테이블을 조회합니다.
10. 얘문
db2=> create view view01 as select id,name from emp;
11. DB2 카탈로그 테이블
- sysibm.xxx : 데이타베이스 내의 모든 객체에 대한 정보 제공
- syscat.xxx : sysibm에 속하는 테이블 중 중요 정보만 조합하여 제공
- sysstat.xxxx : sysibm에 속하는 테이블 중 통계정보만 조합하여 제공
- sysibmadm.xxxx: db2의 성능분석 및 관리 정보 제공
12. 테이블 비교
- syscat.tables
- syscat.columns
- syscat.tablespaces
- syscat.indexes
- syscat.tabconst
- syscat.colchecks, syscat.references, syscat.indexes
- syscar.views
- syscat.sequences
- syscat.triggers
- dba_users=> OS의 사용자를 사용함으로 해당 정보에 대한 관리가 필요치 않습니다.
- syscat.roles
- syscat.tabauth, syscat.colauth, syscat.indexauth등 각 object마다 정보를 담고 있는 테이블이 다름.
13. 테이블 타입 비교
오라클 <-> DB2
-------------------------------
char(n) <-> char(n)
varchar2(n) <-> varchar(n)
long <-> long varchar(n)
long <-> clob
number(p) <-> smallint, integer, bigint
number(o,s) <-> decimal (p,s)
number <-> float, real, double
blob <-> <blob(n)
nclob <-> dbclob(n)
date <-> timestamp, date(mm/dd/yyyy), time(hh24:mi:ss)
timestamp <-> timestamp
xmltype <-> xml
raw(n) <-> char(n) for bit data, varchar(n) for bit data, blob(n)
long raw <-> long varchar(n) for bit data, blob(n)
14. alter table의 'attach' 옵션을 이용하여 기존 테이블을 파티션 테이블의 파티션으로 붙일 수 있습니다.
15. alter table의 'detach' 옵션을 이용하여 파티션 테이블의 파티션을 단일 테이블로 분리할 수 있습니다.
16. syscat.datapartitions 카탈로그 테이블을 조회하면 파티션 정보를 확인할 수 있습니다.
17. 예문
- 파티션 테이블 생성
create table order_trans
(ord_number bigint,
ord_date date,
prod_id varchar(15),
quantity dec(15,3)
)
partition by range (ord_date)
(
partition fy2007q4
starting '10/1/2007' inclusive
ending '1/1/2008' exclusive
in fy2007q4
,partition fy2008q1
starting '1/1/2008' inclusive
ending '4/1/2008' exclusive
in fy2008q1
) not logged initially;
- 파티션 추가
alter table order_trans
add partition fy2008q3
starting '7/1/2008' inclusive
ending '10/1/2008' exclusive
in fy2008q3;
- 단일 테이블을 파티션 테이블의 파티션으로 병합
alter table order_trans
attach partition fy2008q2
starting '4/1/2008' inclusive
ending '7/1/2008' exclusive
from table new_trans;
- 기존 파티션을 단일 테이블로 분리
alter table order_trans
detach partition fy2007q4 into fy2007q4;
18. db2는 다음과 같이 4가지 방식을 이용하여 데이터를 분산할 수 있습니다.
- Database partitioning feature
. 데이터베이스 파티셔닝 전반에 골고루 레코드를 분배합니다.
. 확장성이 좋습니다.
- 다차원 클러스터링(MDC)
. 테이블내의 유사한 값을 갖는 레코드를 block이라고 하는 같은 물리적인 위치에 다차원적으로 그룹핑하여 저장합니다.
. 쿼리성능이 좋아집니다.
- 테이블 파티셔닝(TP)
. 지정된 범위내에 있는 데이터를 그룹핑하여 같은 데이터 파티션에 놓습니다.
. 쿼리성능이 좋아지며 데이터 이동이 용이합니다.
19. 명령어를 이용한 테이블 변경/삭제
- 예문
. 컬럼추가 예제
alter table department add column rating char(1);
alter table project add site_notes varchar(1000) for bit data;
.외래키 추가예제
alter table equipment
add constraint deptquip
foreign key (equip_owner) references department on delete set null;
.check 제약조건 추가 예제
alter table employee add constraint revenue check (salary + comm > 30000);
. 기존 컬럼 제거 예제
alter table employee drop constraint revenue;
. 디폴트값 변경예제
alter table employee alter column workdept set default '123';
. security policy 추가 예제
alter table employee add security policy data_access;
, salary 컬럼을 보호하기 위해 employee 테이블 변경
alter table employee alter column salary secured with employeeseclabel;
20. 명령어를 이용한 테이블 조회
- 파라미터 설명 : user / all / system /schema [스키마명]
db2=> list tables for schema amj
db2=> list tables for schema amj
21. 명령어를 이용한 테이블 구조 조회
- 파라미터 설명: table / indexes for table / data partitions for table
db2=> describe table user1.departmentzzzz
cb2=> describe data partitions for table amj.sales.show detail
22. db2look -d sample -a -e -t aa.t01
23. 명령어를 이용한 MDC + 파티션 테이블
create table amj.sales
(
date date not null,
region varchar(15) not null,
product_id bigint
revenue decimal(15,0) not null
)
partition by range("date")
(
part fy2008iq starting('2008-01-01') in tbs1q long in ltbs1q,
part fy20082q starting('2008-04-01') in tbs2q long in ltbs2q,
part fy20083q starting('2008-07-01') in tbs3q long in ltbs3q,
part fy20084q starting('2008-10-01') ending ('2009-01-01') exclusive in tbs4q long in ltbs4q
)
organize by
(
( "region","product_id")
);
create unique index amj.partition_idx on amj.sales(date asc) include ("region")
not partitioned in dms01
pctfree 10 micpctused 10 allow reverse scans;
select datapartitionname, tabschema, tabname,
from syscat.datapartitions where tabschema = 'amj' and tabname='sales';
24. 인덱스 비교
- syscat.indexes 카탈로그 테이블을 조회합니다.
- describe indexes for table <테이블명> show detail 명령어를 이용하여 조회합니다.
- 예문
. create index idx_sales on sales (date asc);
. drop index idx_sales;
. alter index rebuild는 존재하지 않습니다.
25. 시퀀스 비교
- preval for <시퀀스 이름>과 nextval for <시퀀스이름>를 이용하여 현재값과 다음 값을 조회합니다.
- syscat.sequences 카탈로그 테이블을 조회하면 시퀀스 정보를 조회할 수 있습니다.
> create sequence order_seq
start with 100
increment by 1
no maxvalue
no cycle
nocache;
> insert into order_his values
(nextval for order_seq, 'book',100)
> select preval for order_seq
from sysibm,sysdummy1;
> drop sequence order_seq;
26. DB2 시퀀스 생성
- 예문
. 시퀀스 생성
create sequence order_seq
start with 100
increment by 1
no maxvalue
no cycle
cache 24
. 시퀀스 활용
insert into order values
( nextval for order_seq, 'paper bag');
insert into order values
( nextval for order_seq, 'note');
. 시퀀스 조회
select * from order;
select preval for order_seq
from sysibm.sysdummy1;
27. 트리거 비교
create trigger con_audit_trg
no cascade before insert on con_audit
referencing new as n
for wach row
mode db2sql
begin atomic
set n.timestamp = current timestamp;
end!
create trigger con_audit_trg
no cascade before insert on con_audit
referencing new as n
for each row
begin atomic
set n.timstamp = current timestamp;
end!
create trigger emp_history_trg
alter delete on employees
referenvcing old as d
for each row
begin atomic
insert into emp_history (emp_id, first_name, last_name)
values (d.emp_id, d.first_name, d.last_name);
end!
<-> 오라클 트리거
create or replace trigger con_autit_trg
before insert on con_audit
for each row
begin each row
begin
:new.timestamp :=sysdate;
end;
create or replace trigger con_audit_trg
before insert on con_audit
for each row
begin
:new.timestamp :=sysdate;
end;
create trigger emp_history_trg
alter delete on employees
for each row
begin
insert into emp_history (emp_id, first_name, last_name)
values (:old.emp_id, :old.first_name,:old.last_name);
end;
28. 함수 비교
- 테이블 생성시 check 제약 조건내에서 사용자 함수 사용 가능합니다.
- 테이블 타입으로 리턴이 가능합니다.
- 예문
create function test01 (x numeric(10,2))
returns numeric (10,2)
language sql
begin atomic
return (0.5*x*x*3.14);
end@
- 함수 사용
db2 => select abs(-2) from sysibm.sysdummy1
db2=> values round(45.926,2)
db2=> values trunc(45.926)
db2=> values mod(1600,300)
db2=> select ceil(13.11) from susibm.sysdummy1;
db2=> values rand()
db2=> values concat ('abc','cde')
db2=> values substr('string',1,3)
29. 내장함수/ 날짜/시간 함수
- current date / current timestamp / current date
- year() / month() / day() / hour () / minute() / second()
db2=> values (current date, current date+1 month)
30. 내장함수 / 변환함수
- to_char
- dayname
- mothname
- date() / format_timestamp() / to_date()
- bigint() / int() / smallint() / float () / double() / real () / decimal()
- to_date / time / timestamp / timestamp_format / timestamp_iso
- nvl / coaleace
- 예문
db2 => values to_char(current timestamp, 'yyyy/mm/dd')
db2 => select coaleace(manager_name, 'no value') from employee
db2 => values dayname (current_timestamp)
31. 내장함수 / 그룹함수, 그룹연산자
- rank() over (분석절)
- union : 중복값이 제거된 합집합 연산자
- union all : 중복값이 제거되지 않은 합집합 연산자
- minus : 차집합 연산자
- intersect : 교집합 연산자
32. 조회쿼리 비교
- decode(condition, case1, assgin1,case2,assgin2..default) <= DB2 9.5에서 제공하는 decode 구문
- case condition
when case1 then assgin 1
when case2 then assgin 2
else default
end
- 예문
.오라클인 경우
select avg(decode(grade,'A',1,'B',2,'C',3)) into v_grade
from students
where department =p_department
and course_id=p_course_id;
.DB2 9.5인경우는 오라클인 경우와 동일
.DB2 9.5가 아닌경우는 case문으로 변경
select avg(case grade
when 'A' then 1
when 'B' then 2
when 'C' then 3
end) into v_grade
from students
where department =-p_department and
course_id = p_course_id;
33. rownum 전환
.오라클인 경우 <-> DB2 경우
select * from tab1 where rownum < 10 <-> select * from tab1 ferch first 9 rows only
delete from tab1 where rownum <= 100 <-> delete from (select 1 from tab1 fetch first 100 rows only)
update tab1 set c1=v1 where c2 =v2 and rownum <= 10
<-> update (select c1 from tab1 where c2=v2 fetch first 10 rows only) set c1=v1
34. join 방법 비교
- right outer join
select a.last_name, a.id, b.name
from emp a, customer b
where a.id(+) = b.sales_rep_id;
<-> select a.last_name, a.id, b.name
from emp a right outer join cusomer b
on a.id=b.sales_rep_id;
- left outer join
select a.last_name, a.id, b.name
from emp a, customer b
where a.id = b.sales_rep_id(+);
<-> select a.last_name, a.id, b.name
from emp a left outer join cusomer b
on a.id=b.sales_rep_id;
- full outer join
select a.last_name, a.id, b.name
from emp a, customer b
where a.id(+) = b.sales_rep_id(+);
<-> select a.last_name, a.id, b.name
from emp a full outer join cusomer b
on a.id=b.sales_rep_id;
- 예문 (오라클 조인)
select t1.surname,
from example_table1 t1,
example_table2 t2,
example_table3 t3,
example_table4 t4
where
(t1.emptype =1) or (t1.position ='Manager'))
and (t1.empid = t2.empid(+))
and (t2.empid = t3.empid(+))
and (t2.sin = t3.sin(+))
and (t3.jobtype(+) ='Full-Time')
and (t2.empid = t4.empid(+))
and (t2.sin = t4.sin(+))
order by t1.emptype,t2.other
<-> 예문 (DB2 조인)
select t1.surname,
from example_table1 t1
left outer join example_table2 t2 on (t1.empid=t2.empid)
left outer join example_table3 t3 on (t3.empid=t2.empid)
and (t2.empid = t3.empid)
and (t3.jobtype ='Full-Time')
left outer join example)table4 t4 on (t4.sin=t2.sin) and (t3.empid=t2.empid)
where ((t1.emptype =1 ) or (t1.position="Manager'))
order by t1.emptype, t2.other
35. DB2 9.5에서 제공하는 오라클 feature
DB2 registry 를 사용한 ㅇ라클 가능
- 아래 오라클 함수 및 연산자를 DB2 V9.5에서 사용하기 위해서는 db2 registry 변수인
db2_compatibility_vector 를 설정해야 합니다. 사용하고자 하는 기능의 hex 값을
설정한 후 하고자 하는 기능의 hex 값을 설정한 후 인스턴스를 재시작합니다.
오라클/DB2 HEX값
------------------------------
rownum 0x01
dual 0x02
+(outer 조인 연산자) 0x04
connect by 0x04
==> 설정방법
-> 위의 오라클 기능을 그대로 사용학기 위해서는 db2 registry 변수인
db2_compatibility_vector에 활성화하는 hex값을 설정합니다.
>db2stop
>db2set db_compatibility_vector=0x08
>db2start
****** db2 v9.5에서 사용하고자 하는 오라클 함수 또는 연산자를 각각 따로 Hex값을 이용하여 설정이
가능합니다. 그러나 만약 혼합되어 사용할 경우, 예를 들면 connect by 와 dual,rownum를 사용하고자 하는
경우에는 0x08 + 0x02 + 0x01 = 0x0B를 설정하면 됩니다.
36. DB2 registry 설정없이 사용가능한 오라클 기능
to_char / to_date / nvl / greatest / least / decode / bitand /bitandnot /bitor / bitxor / bitnot
minus
unique
인라인 뷰의 이름 지정
시퀀스 currval_nextval 시퀀스의 현재값과 다음값을 리턴합니다.
36. DB2 9.5에서 제공하는 오라클 feature
확인해보기
- DB2에서 (+) join 연산자 사용
db2set db2_compatibility_vector=03(?)
to_char 함수 / to_date함수 / minus 연산자 / 시퀀스 연산자
37. PL/SQL과 SQL PL 비교
- 오라클과 DB2는 모두 구조화 데이터베이스 프로그래밍 언어를 사용하여 서버 프로그램을 작성합니다.
오라클에서 사용되는 이러한 언어를 PL/SQL이라 하며 DB2에서는 SQLPL이라고 합니다. 이 곳에서는
두데이터베이스의 서버 프로그래밍 언어에 대해 비교합니다.
38. PL/SQL과 SQL PL 비교
-오라클 pl/sql
i_balnace number(10,2):=0.0 ;
i_balance := 19.99;
- DB2 SQLPL
declare i_balnace numeric(10,2) default 0.0;
set i_balance = 19.99l
* set 구문은 또한 다음과 같이 local 변수에 Table column 값을 할당 할 수 있습니다.
st i_balance = (select balance from account_info where account_no=actno);
*** 조건 구문 및 흐름 제어
1)오라클 PL/SQL
<------>
if - then - end if;
if -then - else - end if;
if - then - elseif - end if;
DB2 SQLPL
<------>
it - then - end if;
if - then - else -end if;
if - then - elseif - end if;
2)오라클 PL/SQL
<------>
loop
statements;
end loop;
DB2 SQLPL
<------>
[L1:] loop
statements;
level L1;
end loop [L1];
3) 오라클 PL/SQL
<------>
while condition loop
statements;
end loop;
DB2 SQLPL
<------>
while condition do
statements;
end while;
4) 오라클 PL/SQL
<------>
loop
statements;
exit when condition;
end loop;
DB2 SQLPL
<------>
repeat
statements;
until condition;
end repeat;
5) 오라클 PL/SQL
<------>
For i_count in
lower_bound..upper_bound
loop
statements;
end loop;
DB2 SQLPL
<------>
set i_count = lower_bound;
while i_count <= upper_bound do
statements;
set i_count = i_count +1;
end while;
6) 오라클 PL/SQL
<------>
open cursor_variable for
select_statements;
DB2 SQLPL
<------>
for variable as cursor_name
cursor for select_statement do
statements;
end for;
39. Dynamic SQL 비교
오라클
<------>
create or replace procedure
get_emp_name_v2 (emp_id in number) as
sql_stmt varchar2(1000);
v_result varchar2(20);
begin
sql_stmt:= 'select last_name from employees
where emp_id =:x';
execute immediate sql_stmt
into v_result
using emp_id;
dbms_output.putline(v_result.last_name);
end;
/
DB2
<------>
create procedure
get_emp_name_v2 (in emp_id float)
language sql
begin
declare v_dyn_sql varchar(1000);
declare v_sql_stmt statement;
declare c_employees cursor for v_sql_stmt;
set v_dyn_sql = 'select last_name from emplyees where emp_id='
||char(emp_id);
prepare v_sql_stmt from v_dyn_sql;
open c_employees;
-- fetch...
close c_emplyees;
end!
40. 커서 정의 비교
1) 오라클: 커서 정의
- cursor cursor_name {(cursor_parameter(s))]
is select_statement
2) 오라클: 커서 열기
- open cursor_name [(cursor_parameter(s))]
3) 오라클: 커서로부터 fetch
- fetch cursor_name into variables(s)
4) 오라클: fetched된 행 수정
- update table_name set statement(s)...
where current of
cursor_name
5) 오라클: fetch된 행 삭제
- delete from table_name
where current of cursor_name;
6) 오라클: 커서 닫기
- close cursor_name
1) DB2: 커서 정의
- declare cursor_name
cursor [with hold] [with return]
[t ocaller|to client] for
select_statement
2) DB2: 커서 열기
- open cursor_name [using host-variable]
3) db2: 커서로부터 fetch
- fetch [from ] cursor_name into variable(s)
4)DB2: fetch된 행 수정
- update table_name set statements...
where current of cursor_name
5)DB2: Delete된 행 삭제
- delete from table_name
where current of cursor_name
6)DB2: 커서 닫기
- close cursor_name;
오라클 예문
procedure get_sum_projects
(v_office_id in number , sum_projects out number)
as v_prj number(3);
cursor c1 is
select current_projects from employees
where office_id = v_office_id;
begin
sum_projects :=0;
open c1;
loop
fetch c1 into v_prj;
exit when c1%notfound;
sum_projects :=sum_projects + v_prj;
end loop;
end;
DB2 예문
create procedure get_sum_projects
(in v_office_id integer,
out sum_projects integer)
begin
declare sqlcode int default 0;
declare v_prj samllint default 0;
declare v_no_data smallint default 0;
declare c1 cursor for
select current_projects from employees
where office_id=v_office_id;
declare continue handler for not found
set v_no_data = 1;
set sum_projects =0;
open c1;
fetch c1 into v_prj;
while (v_no_data=0) do
set sum_projects = sum_projects + v_prj;
fetch c1 into v_prj;
end while;
close c1;
emnd!
41. 커서 속성 비교
%isopen (오라클)
-------------------
if c1$isopen then
fetch c1 into var1;
else
open c1;
fetch c1 into var1;
end if;
%isopen (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 (오라클)
-------------------
open cur1;
loop
fetch cur1 into v_var1;
exit when cur1%notfounf;
...
end loop;
%notfound (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 (오라클)
---------------------------
delete from emp
where empno = my_empno;
if sql%found then -- delete succeeded
insert into emp_table
values (my_empno,my_ename);
%found (DB2)
---------------------------
delete from emp
where empno = my_empno;
if sqlcode = 0 them -- delete succeeded
insert into emp_table
values
%rowcount (오라클)
-------------------
loop
fetch c1 into my_ename, my_deptno;
if c1%rowcount > 10 then exit;
end if;
...
end loop
%rowcount (DB2)
-------------------------
declare c1 cursor for
select ename,deptno from emp_table
fetch first 10 rows only;
declare continue handler for not found
begin
set end-of-fetch =1;
end;
L1:loop
fetch c1 into my_ename, my_deptno;
if end-of-fetch = 1 then
leve l1;
end if;
...
end loop l1;
%rowcount (oracle)
--------------------------------
loop
fetch c1 into my_ename, my_deptno;
if c1%rowcount > 10 then
...
end if;
...
end loop;
%rowcount (db2)
--------------------------------
declare v_cursor int default 0;
...
l1:loop
fetch c1 into my_ename, my_deptno;
v_curcount = v_curcount +1;
if v_curcount > 10 then
...
end if;
end loop l1;
#rowcount (오라클)
---------------------------
delete from emp_table
where...
if sql%rowcount > 10 then
...
end if;
#rowcount (db2)
-----------------------------
declare rc int default 0;
...
delete from emp_table where...
get diagnostics rc=row-count;
if rc> 10 then
...
end if;
42. collection 비교
collection을 비교한 pl/sql을 DB2 sqlpl로 변환합니다.
nested table/varray 변환
오라클 code
----------------
declare
type emplist is table of emp_table.ename%type;
cursor c1 is
select emp_name
from dept=v_dept;
empName emp_table.ename%type;
empnum number;
begin
loop
fetch c1 into empname;
when c1%notfound exit;
empnum :=empnum +1;
emplist(empnum):=empname;
end loop;
close c1;
end;
db2 code
----------------
declare global temporary table session.temp_emp_list
(num integer, empname varchar(30))
with replace
on commit preserve rows
not logged;
insert into session.temp_emp_list
select row_number() over(컬럼명), emp_name
from emp_table
where dept=v_dept;
bulk collect변환 (오라클)
-----------------------------
delcare
type emplist is table of emp_table.ename%type;
cursor c1 is
select emp_name
from dept=v_dept;
begin
open c1;
fetch c1 bulk collect into emplist;
close c1;
end;
bulk collect변환 db2 code
---------------------------------------
declare v_empname varchar(30);
declare v_num int default 0;
declare global temporary table session.temp_emp_list
(num integer, empname varchar(30))
with replace on commit preserve rows
not logged;
insert into session.temp_emp_list ( select emp_name from emp_table
where dept=v_dept);
