### 테이블 생성
create table artists
(
artno smallint not null,
name varchar(50) with default 'abc',
classification char(1) not null,
bio clob(100k) logged,
picture blob (10M) not logged compact)
### 인덱스 생성.
create unique index idx_id on staff(id);
create index idx_id2 on sotck (name) cluster;
create unique index empidx on employee (empno)
include (lastname, firstname)
### 인덱스 정보보기. (테이블명은 반드시 대문자)
select INDEX_OBJECTID, numrids from syscat.indexes where tabname = 'STAFF' and tabschema = ''
INDEX_OBJECTID
NUMRIDS
NUMRIDS_DELETED
NUM_EMPTY_LEAFS
AVERAGE_RANDOM_FETCH_PAGES
AVERAGE_RANDOM_PAGES
AVERAGE_SEQUENCE_GAP
AVERAGE_SEQUENCE_FETCH_GAP
AVERAGE_SEQUENCE_PAGES
AVERAGE_SEQUENCE_FETCH_PAGES
TBSPACEID
### 뷰생성
create view staff2 as select id, name from staff where id < 200
### 뷰 삭제
drop view staff2
### 트리거 생성.
create trigger reorder
after update
of dept on staff
### export
export to expstaff of ixf messages staffexmsg select * from staff
export
- to artexprt : 요 화일로 저장이 됩니다.
- of ixf : pc/ixf, wsf, del ascii ascii 방식으로 export합니다.
- messages staffexmsg : 실행 결과 메시지 내용.
--- staffexmsg 내용 ---------------------------------------------
--- SQL3104N 익스포트 유틸리티가 파일 "test2"(으)로 데이터 익스포트를 시작합니다.
--- SQL3105N 익스포트 유틸리티가 "35"개 행의 익스포트를 완료했습니다.
- selet * from staff : 백업내용
db2 => export to expstaff of ixf messages staffexmsg select * from staff
위의 명령을 실행하시겠습니까? (y/n) y
익스포트된 행 수: 35
### import
import from expstaff of ixf messages staffimmsg replace_create into staff
? create, insert, insert_update, replace, replace_create
db2 => drop table staff
DB20000I SQL 명령이 완료되었습니다.
db2 => commit
DB20000I SQL 명령이 완료되었습니다.
db2 => import from expstaff of ixf messages staffimmsg replace_create into staff
읽은 행 수 = 35
건너뛴 행 수 = 0
삽입된 행 수 = 35
갱신된 행 수 = 0
거부된 행 수 = 0
커미트된 행 수 = 35
db2 => select * from staff
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
10 Sanders 20 Mgr 7 18357.50 -
20 Pernal 20 Sales 8 18171.25 612.45
30 Marenghi 38 Mgr 5 17506.75 -
40 O'Brien 38 Sales 6 18006.00 846.55
.....
.....
### backup
db2 => create table staff4 like staff
DB20000I SQL 명령이 완료되었습니다.
db2 => select * from staff4
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
0 레코드가 선택됨.
db2 => backup db mydb
SQL1433N 발행한 명령을 실행하려면 "MYDB"에 연결해야 하지만, 응용프로그램이
이미 "SAMPLE"에 연결되었습니다.
db2 => backup db sample
백업이 완료되었습니다. 이 백업 이미지에 대한 시간소인은 20060427094337입니다.
### backup 확인
db2 => list backup all for sample
sample에 대한 실행기록 파일 목록
일치하는 파일 항목의 수 = 1
Op Obj 시간소인+순서 유형 Dev 최초 로그 현재 로그 백업 ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20060427094337001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
3 테이블 스페이스를 포함함:
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
----------------------------------------------------------------------------
주석: DB2 BACKUP SAMPLE OFFLINE
시작 시간: 20060427094337
종료 시간: 20060427094401
상태: A
----------------------------------------------------------------------------
EID: 5 위치: C:\Program Files\IBM\SQLLIB\BIN\SAMPLE.0\DB2\NODE0000\CATN0000\20060427
### restore
restore db sample
db2 => delete from emp_act where actno < 100
DB20000I SQL 명령이 완료되었습니다.
db2 => select * from emp_act
EMPNO PROJNO ACTNO EMPTIME EMSTDATE EMENDATE
------ ------ ------ ------- ---------- ----------
000130 IF1000 100 0.50 1982-10-01 1983-01-01
000140 IF2000 100 1.00 1982-01-01 1982-03-01
...
000340 OP2013 140 0.50 1982-01-01 1983-02-01
000340 OP2013 170 0.50 1982-01-01 1983-02-01
22 레코드가 선택됨.
db2 => restore db sample
SQL2539W 경고! 백업 이미지 데이터베이스와 동일한 기존 데이터베이스로 리스토어
이스 파일이 삭제됩니다.
계속하시겠습니까? (y/n) y
DB20000I RESTORE DATABASE 명령이 완료되었습니다.
db2 => select * from emp_act
SQL1024N 데이터베이스 연결이 없습니다. SQLSTATE=08003
db2 => connect to sample
데이터베이스 연결 정보
데이터베이스 서버 = DB2/NT 8.2.0
SQL 권한 부여 ID = NERD
로컬 데이터베이스 별명 = SAMPLE
db2 => select * from emp_act
EMPNO PROJNO ACTNO EMPTIME EMSTDATE EMENDATE
------ ------ ------ ------- ---------- ----------
000010 MA2100 10 0.50 1982-01-01 1982-11-01
000010 MA2110 10 1.00 1982-01-01 1983-02-01
...
000340 OP2013 170 0.50 1982-01-01 1983-02-01
000020 PL2100 30 1.00 1982-01-01 1982-09-15
75 레코드가 선택됨.
db2 => db2 connect reset
###
### 튜닝관련 툴.
db2expln
--- dynexpln
C:\PROGRA~1\IBM\SQLLIB\BIN>dynexpln
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1997, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Dynamic SQL Explain Tool
NOTE: db2expln can now process dynamic statements directly and should
be used instead of dynexpln.
Enter Database Name ==> sample
Enter Statement ==> select * from staff
Enter Output File Name (default is to terminal) ==> 1.txt
CONNECT TO sample
데이터베이스 연결 정보
데이터베이스 서버 = DB2/NT 8.2.0
SQL 권한 부여 ID = NERD
로컬 데이터베이스 별명 = SAMPLE
PREP DYNEXPLN.sqc PACKAGE USING DYNEXPLN
LINE MESSAGES FOR DYNEXPLN.sqc
------ --------------------------------------------------------------------
SQL0060W "C" 프리컴파일러가 진행 중입니다.
SQL0091W 프리컴파일 또는 바인드가 "0"개의 오류와 "0"개의
경고와 함께 종료되었습니다.
TERMINATE
DB20000I TERMINATE 명령이 완료되었습니다.
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
Output is available in "1.txt".
C:\PROGRA~1\IBM\SQLLIB\BIN>
--------------- 1.txt 내용 ------------------------------------
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** PACKAGE ***************************************
Package Name = "NERD"."DYNEXPLN" Version = ""
Prep Date = 2006/04/27
Prep Time = 11:43:44
Bind Timestamp = 2006-04-27-11.43.44.406001
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "NERD"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
select *
from staff
Section Code Page = 1363
Estimated Cost = 12.906988
Estimated Cardinality = 68.000000
Access Table Name = NERD.STAFF ID = 2,3
| #Columns = 7
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 7
Return Data Completion
End of section
------------------------------------------------------------------------------
### reorgchk
테이블에 대한 재구성 필요 여부를 확인.
db2 reorgchk on table all
db2 reorgchk on table user
db2 reorgchk on table system
db2 reorgchk on table kes.empl
db2 reorgchk on table sysibm.sysindexes
--- 실행
C:\PROGRA~1\IBM\SQLLIB\BIN>db2 reorgchk on table user
RUNSTATS 실행 중....
테이블 통계:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (데이터 페이지의 실제 스페이스 사용) > 70
F3: 100 * (필수 페이지 수 / 총 페이지 수) > 80
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
테이블: NERD.ARTISTS
NERD ARTISTS - - - - - - - - - ---
테이블: NERD.CL_SCHED
NERD CL_SCHED - - - - - - - - - ---
테이블: NERD.DEPARTMENT
NERD DEPARTMENT 9 0 1 1 - 549 0 - 100 ---
테이블: NERD.EMP_ACT
NERD EMP_ACT 75 0 1 1 - 2850 0 - 100 ---
테이블: NERD.EMP_PHOTO
NERD EMP_PHOTO 12 0 1 1 - 2028 0 - 100 ---
테이블: NERD.EMP_RESUME
NERD EMP_RESUME 8 0 1 1 - 984 0 - 100 ---
테이블: NERD.EMPLOYEE
NERD EMPLOYEE 32 0 2 2 - 2784 0 69 100 -*-
테이블: NERD.IN_TRAY
NERD IN_TRAY - - - - - - - - - ---
테이블: NERD.ORG
NERD ORG 8 0 1 1 - 440 0 - 100 ---
테이블: NERD.PROJECT
NERD PROJECT 20 0 1 1 - 1340 0 - 100 ---
테이블: NERD.SALES
NERD SALES 41 0 1 1 - 1845 0 - 100 ---
테이블: NERD.STAFF
NERD STAFF 35 0 1 1 - 1575 0 - 100 ---
테이블: NERD.STAFF4
NERD STAFF4 - - - - - - - - - ---
테이블: NERD.STAFFG
NERD STAFFG 35 0 1 1 - 2065 0 - 100 ---
테이블: NERD.TAB3
NERD TAB3 - - - - - - - - - ---
----------------------------------------------------------------------------------------
인덱스 통계:
F4: CLUSTERRATIO 또는 표준화된 CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) >
0
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96)
/ (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
테이블: NERD.EMP_PHOTO
SYSIBM SQL060425165849350 12 1 0 1 12 0 12 100 - - 0 0 -----
테이블: NERD.EMP_RESUME
SYSIBM SQL060425165850870 8 1 0 1 14 0 8 100 - - 0 0 -----
테이블: NERD.STAFF
NERD IDX_ID 35 1 0 1 2 0 35 100 - - 0 0 -----
NERD IDX_NAME 35 1 0 1 9 0 35 100 - - 0 0 -----
-------------------------------------------------------------------------------------------------
CLUSTERRATIO 또는 표준화된 CLUSTERFACTOR(F4)가 디폴트 테이블과 동일한 순서로
되어 있지 않은 인덱스에 대해 REORG가 필요함을 나타낼 것입니다. 복수의 인덱스가
한 테이블에 정의되면, 하나 이상의 인덱스가 REORG가 필요한 것으로 표시될 수
있습니다. REORG 순서에 대해 가장 중요한 인덱스를 지정하십시오.
ORGANIZE BY절 및 해당 차원 인덱스를 사용하여 정의한 테이블은 이름에 '*'
접미부를 갖습니다. 차원 인덱스의 카디낼리티는 테이블의 활성 블록 통계와
같습니다.
C:\PROGRA~1\IBM\SQLLIB\BIN>
### runstats
db2 runstats on table nerd.emp_photo
db2 runstats on table kes.empl for index kes.empl_A
db2 runstats on table kes.empl for indexes all
db2 runstats on table kes.empl and index kes.empl_A
db2 runstats on table kes.empl and indexes all
### 프로시져
edit 1.db2
---------------------------------------------------------------------------
CREATE PROCEDURE myproc
(IN deptNumber CHAR(3),
OUT medianSalary DOUBLE)
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM employee
WHERE workdept = deptNumber
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*)
INTO v_numRecords
FROM employee
WHERE workdept = deptNumber;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
END @
-----------------------------------------------------------------------------
C:\Program Files\IBM\SQLLIB\BIN>db2 -td@ 0-svf 1.db2
DB21034E 명령이 유효한 명령행 처리기 명령이 아니므로 SQL문으로 처리되었습니다.
SQL 처리 중에 이 명령은 다음을 리턴했습니다.
SQL0103N 숫자 리터럴 "1.db2"이(가) 유효하지 않습니다. SQLSTATE=42604
C:\Program Files\IBM\SQLLIB\BIN>db2 -td@ -svf 1.db2
CREATE PROCEDURE myproc
(IN deptNumber CHAR(3),
OUT medianSalary DOUBLE)
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM employee
WHERE workdept = deptNumber
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*)
INTO v_numRecords
FROM employee
WHERE workdept = deptNumber;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
END
DB20000I SQL 명령이 완료되었습니다.
C:\Program Files\IBM\SQLLIB\BIN>db2 "call myproc('A00',?)"
출력 매개변수 값
--------------------------
매개변수 이름 : MEDIANSALARY
매개변수 값 : +4.65000000000000E+004
리턴 상태 = 0
C:\Program Files\IBM\SQLLIB\BIN>
--------------------------------------------------------------------------------
### Table 1-1 Mapping of Oracle terminology to DB2 UDB
==========================================================
Oracle // DB2 UDB
==========================================================
Instance // Instance
Database // Database
Initialization File // Database Manager Configuration File
Table spaces // Table spaces
Data blocks // Pages
Extents // Extents
Datafiles // DMS containers
Redo Log Files // Transaction Log Files
PL/SQL // SQL/PL
Data Buffers // Buffer Pool
SGA // Database Manager and Database shared memory
Data Dictionary // Catalog
Library Cache // Package Cache
Large Pool // Utility heap
Data Dictionary cache // Catalog cache
SYSTEM tablespace // SYSCATSPACE tablespace