UDB Administrator for LINUX, UNIX and Windows ( 주 ) 다우기술인프라사업본부파트너사업부 IM 기술지원팀
VIII. 테이블스페이스 1. 테이블스페이스관리 2. DMS vs SMS 비교 1) DMS vs SMS 비교 3. Tablespace 옵션 1) PAGE 정의 2) 테이블제한사항 3) EXTENT 정의 4) PREFETCH 정의 4. 명령어를이용한테이블스페이스생성 5. 명령어를이용한테이블스페이스변경 / 삭제 6. 명령어를이용한테이블스페이스조회 2
VIII. 테이블스페이스 1. 테이블스페이스관리 테이블스페이스는테이블을저장하는논리적개념입니다. 테이블스페이스를구성하는물리적인개념은 Container 입니다. 저장모델 디폴트테이블스페이스 SYSCATSPACE Catalog Table 및데이터베이스를관리하기위한관리테이블들이저장됩니다. TEMPSPACE1 디폴트 Temporary 테이블스페이스입니다. USERSPACE1 사용자가생성한오브젝트및데이터를저장하는디폴트테이블스페이스입니다. 테이블생성시테이블스페이스를지정하지않을경우디폴트로이테이블스페이스를사용합니다. 3
VIII. 테이블스페이스 1. 테이블스페이스관리 테이블스페이스관리방식 System Managed Space (SMS) 운영체제가테이블스페이스를관리하는방식입니다. 컨테이너로디렉토리가사용되며운영체제의파일시스템이스토리지할당및관리를제어합니다. Database Managed Space (DMS) 데이터베이스가테이블스페이스를관리하는방식입니다. 컨테이너로 file 과 raw device 가이용되어집니다. 오라클의 Locally Managed Tablespace 와유사합니다. Automatic Storage table space 가자동으로스토리지를관리하는방식으로테이블스페이스의타입에따라 SMS 또는 DMS 로자동생성하며컨테이너를지정할필요가없습니다. 테이블스페이스타입 Regular 오브젝트및인덱스의모든데이터를저장하는공갂입니다. Temporary System Temporary - 정렧및 join 같은 SQL 문및기타작업실행시데이터베이스 Manager 가사용하는영역입니다. User Temporary - 세션의젂역임시테이블의데이터를임시로저장하는영역입니다. Large Regular 처럼모든데이터를저장하나시스템관리유형이 DMS 일경우에만생성이가능하며한페이지에저장되는행의수가 255 개를초과하는테이블을저장할수있습니다. 참고 ) 는 UNDO 정보를로그버퍼또는로그파일에저장하므로 UNDO 정보를위한별도의 UNDO 테이블스페이스는존재하지않습니다. 4
VIII. 테이블스페이스 2. DMS vs SMS 비교 DMS tablespaces dms01 및 dms02 는파일이며 데이터정보및제어정보가 두모두파일안에포함된다. SMS Tablespaces SQLTAG.NAM : 이디렉토리가 사용중임을표시하는파일. SQL*.LF : LONG VARCHAR, LONG VARGRAPHIC 데이터 가포함된다. SQL*.LB : BLOB, CLOB, 또는 DBCLOB 데이터가포함된다. SQL*.LBA : SQL*.LB 파일의할 당정보및 Free 정보가포함 된다. SQL*.INX : 인덱스테이블데이터가포함된다. 5
VIII. 테이블스페이스 2. DMS vs SMS 비교 1) DMS vs SMS 비교 DMS SMS 제어 인스턴스가제어합니다. 운영체제가제어합니다. 컨테이너 파일혹은 Raw Device 디렉토리 공갂할당 테이블위치 장점 테이블스페이스생성시크기를지정할수있으며 AUTOMATIC STORAGE 지정시자동으로크기가증가됩니다. 공갂이미리할당되며하나의 Extent 안에있는공갂은물리적으로연속적입니다. 하나의테이블을 LOB 데이터와일반데이터로테이블스페이스를분리하여저장할수있으며인덱스도분리하여별도의테이블스페이스에저장할수있습니다. 테이블이저장되는데이터타입에따라여러개의테이블스페이스로저장가능하므로성능이우수합니다. DMS 테이블스페이스는 SMS 방식보다성능이우수하므로 TEMPORARY 를제외한대부분의경우에주로사용됩니다. 테이블스페이스생성시크기를지정할수없으며크기는운영시스템의파일시스템용량에좌우됩니다. 추가공갂이요구되어질때마다한 Page 씩할당됩니다. 한테이블은한테이블스페이스내에존재합니다. 공갂이필요할때에만한페이지씩할당되므로사용치않는공갂을미리할당해놓지않습니다. 테이블스페이스생성시컨테이너를사젂정의할필요가없으므로초기작업이줄어듭니다. TEMPORARY 테이블스페이스또는소형테이블을저장하는데에주로사용됩니다. 6
VIII. 테이블스페이스 3. Tablespace 옵션 1) PAGE 정의 버퍼풀및테이블스페이스의입출력단위로한행은여러개의페이지에나누어저장될수없습니다. 이는오라클의체인또는마이그레이션으로인한성능저하현상을사젂에방지하기위한조치입니다. Random 하게데이터를읽고쓰는 OLTP 응용프로그램의경우 PAGESIZE 를작게하여버퍼풀에원치않는데이터를올려놓지않도록하는것이성능향상에도움이됩니다. 한번에연속적인많은행을접근하는 DSS 응용프로그램의경우 I/O 요청을줄이기위해보다큰 PAGESIZE 가유리합니다. 하지만만약한행의길이가 PAGESIZE/255 보다작을경우에는좀더작은 PAGESIZE 가적합합니다. 2) 테이블제한사항 테이블제한 Page Size 4K 8K 16K 32K 컬럼최대개수 500 개 1012 개 1012 개 1012 개 행최대길이 4005 bytes 8101 bytes 16293 bytes 32677 bytes 테이블최대크기 64GB (Regular) 2048GB (Large) 128GB (Regular) 4096GB (Large) 256GB (Regular) 8192GB (Large) 512GB (Regular) 16384GB (Large) 7
VIII. 테이블스페이스 3. Tablespace 옵션 3) EXTENT 정의 데이터베이스관리프로그램이라운드로빈방식으로데이터를골고루분배하기위해테이블스페이스내의모든 Container 들에게돌아가면서 EXTENT 크기만큼데이터를입력합니다. 데이터베이스생성시 DFT_EXTENT_SZ 파라미터또는테이블스페이스생성시 EXTENTSIZE 에값을지정하여 Extent 크기를지정할수있습니다. 4) PREFETCH 정의 프리페치는프리페치프로세스가응용프로그램이데이터를요구하기젂에미리디스크의데이터를검색한후데이터를버퍼풀로옮겨놓는방식입니다. 데이터베이스구성매개변수인 num_ioservers 를통해충분한입출력서버를구성할경우데이터의프리페치를사용할수있습니다. 또한이를통하여쿼리의성능을크게향상시킬수있습니다. 프리페치크기는프리페처프로세스가한번에읽을페이지수입니다. 데이터베이스생성시 dft_prefetch_sz 옵션을지정하거나테이블생성시 prefetchsize 옵션을지정하면프리페치크기를지정할수있습니다. 프리페치크기는 EXTENT 크기의배수로지정하는것이가장성능이좋습니다. 8
VIII. 테이블스페이스 4. 명령어를이용한테이블스페이스생성 Create Tablespace 구문 예제 db2=> CREATE TEMPORARY TABLESPACE TEMPSPACE2 MANAGED BY DATABASE USING ( d:\acc_tbsp, e:\acc_tbsp ) EXTENTSIZE 64 PREFETCHSIZE 32 db2=> CREATE TABLESPACE DATATS MANAGED BY AUTOMATIC STORAGE db2=> CREATE LARGE TABLESPACE LARGEDATA INCREASESIZE 10 PERCENT MAXSIZE 512 M db2=> CREATE TABLESPACE ACCOUNTING MANAGED BY DATABASE USING (FILE /tbs/acc_1 50000, FILE /tbs/acc_2 50000 EXTENTSIZE 256 9
VIII. 테이블스페이스 5. 명령어를이용한테이블스페이스변경 / 삭제 Alter Tablespace 구문 Drop Tablespace 구문 예제 db2=> ALTER TABLESPACE PAYROLL ADD (DEVICE /dev/rhdisk9 10000) db2=> ALTER TABLESPACE DATA_TS EXTEND (ALL 1000) db2=> ALTER TABLESPACE TS0 ADD (FILE cont2 2000, FILE cont3 2000) ADD (FILE cont4 2000) db2=> DROP TABLESPACE TS0 10
VIII. 테이블스페이스 6. 명령어를이용한테이블스페이스조회 list Tablespace 구문 예제 11
1. 스키마 1) 스키마정의 2) 스키마생성 3) 스키마제거 4) 스키마정보조회 2. 테이블 1) 테이블생성 / 변경 / 삭제 2) 파티션테이블 3. 제약조건 4. 뷰 5. 인덱스 1) create index 구문 6. 시퀀스 1) Identity 컬럼 7. 저장프로시저 12
1. 스키마 1) 스키마정의 데이터베이스오브젝트의이름은 < 스키마명 >.< 오브젝트명 > 과같이 2- part 형식으로구성됩니다. 스키마는오브젝트의이름을수식하는수식자역할을합니다. CREATE SCHEMA, DROP SCHEMA 문으로관리합니다. 스키마이름이사용자이름과동일할필요는없습니다. 스키마를생성할경우그스키마를생성한사용자가그스키마의소유자가됩니다. 데이터베이스생성시다음과같은기본스키마가생성됩니다. 스키마 SYSIBM SYSCAT SYSSTAT SYSFUN 설명시스템카탈로그테이블의스키마시스템카탈로그뷰의스키마통계자료와관렦된시스템카탈로그뷰의스키마기본적으로제공되는사용자정의함수의스키마 13
1. 스키마 2) 스키마생성 CREATE SCHEMA < 스키마명 > AUTHORIZATION <authorization_name> ~ 옵션 설명 < 스키마명 > 임의의고유한이름으로지정합니다. AUTHORIZATION 스키마의소유자를지정합니다. 3) 스키마제거 DROP SCHEMA < 스키마명 > RESTRICT 4) 스키마정보조회생성된스키마에대한정보는 SYSCAT.SCHEMATA 뷰를이용하여확인합니다. 예제 db2=> CREATE SCHEMA AMJ AUTHORIZATION HR db2=> CREATE TABLE AMJ.T1 (C1 INT) db2=> list tables for schema < 스키마명 > db2=> select * from syscat.schemata 14
2. 테이블 테이블은데이터를저장하는논리적인저장장소이며뷰는하나이상의테이블로부터논리적으로데이터를추출한부분집합으로논리적이고가상적인테이블입니다. 1) 테이블생성 / 변경 / 삭제 CREATE TABLE table명 ( 칼럼정의 Primary constraint Referential constraint check constraint ) IN 테이블공간명 INDEX IN 테이블공간명 LONG IN 테이블공간명 ALTER TABLE table 명 ADD COLUMN ( 칼럼정의 ) Primary constraint Referential constraint check constraint DROP PRIMARY KEY FOREIGN KEY UNIQUE CHECK CONSTRAINT 제한조건명 DROP TABLE table 명 Constraint 절 CONSTRAINT 제한조건명 UNIQUE PRIMARY FOREIGN KEY ( 칼럼명 ) CHECK ( 점검조건 ) 15
2. 테이블 테이블설명 테이블종류 테이블저장장소지정방법 테이블정보조회 테이블이저장되는테이블스페이스를지정함으로써물리적인저장장소를선택합니다. 테이블마다물리적인저장옵션을지정할수없습니다. 일반데이터와 LOB 데이터, 인덱스등을각기다른테이블스페이스에저장할수있습니다. 컬럼삭제및컬럼속성을변경할수있습니다. 일반테이블 젂역임시테이블 파티션테이블 Multi Dimensional Clustering(MDC) 테이블 지정된테이블스페이스에저장됩니다. 별도의스토리지옵션은존재하지않으며테이블스페이스에서지정된스토리지옵션값을적용합니다. 스토리지옵션생략시지정된테이블스페이스의디폴트값을적용합니다. DMS 테이블스페이스의경우한테이블의데이터를여러개의테이블스페이스로나누어저장할수있습니다. 특정스키마에속하는테이블과뷰를확인할경우 list tables for schema < 스키마명 > 명령어를실행합니다. 테이블구조정보 DESCRIBE TABLE [ 테이블명 ] 을이용하여테이블구조를확인합니다. Dummy 테이블 SYSIBM.SYSDUMMY1 입니다. db2 => SELECT TO_CHAR(CURRENT TIMESTAMP, YYYYMMDDHH24MISS ) from SYSIBM.SYSDUMMY1; 카탈로그테이블 SYSIBM.XXX: 데이터베이스내의모든객체에대한정보제공. SYSCAT.XXX: SYSIBM 에속하는테이블중중요정보만조합해서제공. SYSSTAT.XXX: SYSIBM 에속하는테이블중통계정보만조합하여제공. SYSFUN.XXXX: 사용자정의함수에대한정보제공. SYSIBMADM.XXXX: 의성능분석및관리정보제공 카탈로그테이블의종류를확인할경우다음과같은명령어를통해확인가능합니다. db2=> LIST TABLES FOR SYSTEM 16
2. 테이블 2) 파티션테이블 특정컬럼의테이터를키값을기반으로파티션이라고불리는스토리지오브젝트에나누어저장하도록하는기능입니다. Range 파티션테이블이존재합니다. ALTER TABLE 의 ATTACH 옵션을이용하여기존테이블을파티션테이블의파티션으로붙일수있습니다. ALTER TABLE 의 DETACH 옵션을이용하여파티션테이블의파티션을단일테이블로분리할수있습니다. STSCAT.DATAPARTIOTIONS 카탈로그테이블을조회하면파티션정보를확인할수있습니다. 17
2. 테이블 2) 파티션테이블 파티셔닝기능 대용량데이터베이스와테이블은성능및확장에있어서많은제약사항이따릅니다. 는데이터베이스파티셔닝, 테이블파티셔닝및다차원클러스터링을통해데이터를분배및클러스터함으로써성능을향상시킬수있습니다. 는다음과같이 3 가지방식을이용하여데이터를분산할수있습니다. Databasr Partitioning Feature 다차원클러스터링 (MDC) 테이블파티셔닝 (TP) 설명 데이터베이스파티셔닝젂반에골고루레코드를분배합니다. 테이블내의유사한값을갖는레코드를 Block 이라고하는같은물리적인위치에다차원적으로그룹핑하여저장합니다. 지정된범위내에있는데이터를그룹핑하여같은데이터파티션에놓습니다. 혜택 확장성이좋아집니다 쿼리성능이좋아집니다. 쿼리성능이좋아지며데이터이동이용의합니다. 테이블디자인 대용량테이블로서버의리소스를초과하는테이블일경우적합합니다. 쿼리의결과값이동일한값을갖는레코드를반환하는경우에적합합니다. 대량의레코드가주기적으로추가되거나주기적으로삭제될경우적합합니다. CREATE TABLE 구문젃 파티션키 DISTRIBUTE BY HASH ORGANIZED BY DIMENSION PARTITION BY RANGE Distribution Key Dimension table partition key 파티션키추천컬럼 서로다른값이많은컬럼을선택합니다. data 컬럼을포함하여 region 과 product_type 과같이서로다른컬럼을선택합니다. 시갂에관렦된데이타를갖는컬럼을선택합니다. 인덱스 인덱스는테이블이위치한데이터베이스파티션에존재합니다. 레코드기반의인덱스가아닌 Block 기반의인덱스가생성되어 Block 을가르키는포인터정보가들어있습니다. 인덱스는하나의테이블스페이스에저장됩니다. 18
3. 제약조건 제약조건이란테이블에부적젃한자료가입력되는것을방지하기위한여러가지규칙입니다. 갂단하게테이블안에서데이터의성격을정의하는것이바로제약조건입니다. 데이터의무결성유지를위하여사용자가지정할수있는성질입니다. 모든 CONSTRAINT 는데이터사젂 (DATA DICTIONARY) 에저장됩니다. 의미있는이름을부여했다면 CONSTRAINT 를쉽게참조할수있습니다. 제약조건은테이블을생성할당시에지정할수도있고, 테이블생성후구조변경 (ALTER) 명령어를통해서도추가가가능합니다. NOT NULL 제약조건은반드시컬럼레벨에서만정의가가능합니다. 에서제공하는제약조건의종류 제약조건 NOT NULL 조건 UNIQUE 조건 CHECK 조건 DEFAULT( 컬럼기본값 ) 지정 PRIMARY KEY( 기본키 ) 지정 FOREIGN KEY( 외래키 ) 지정 설명 컬럼에값이반드시존재해야할때사용 데이터의유일성을보장, 자동으로 index 가생성 컬럼의값을어떤특정범위로제한 컬럼에값을입력히지않을때미리지정된값을입력 기본키는 UNIQUE 와 NOT NULL 의결합 기본키를참조하는컬럼또는컬럼들의집합 19
4. 뷰 사용자가원본테이블의특정행과특정컬럼들만액세스할수있도록하려면뷰를생성합니다. 한개이상의원본테이블의데이터를조인하여뷰를생성하는것도가능합니다. 기본테이블에대해최소한 SELECT 특권이있어야합니다. 원본테이블이제거되면뷰는작동불능상태가되어서액세스를할수없습니다. 원본테이블이다시생성되어도작동불능상태의뷰는액세스할수없으므로재생성이필요합니다. create view 문으로생성하며, SELECT 문으로액세스가허용되는데이터를제한합니다. list tables 명령에서뷰의목록을확인할수있습니다 뷰에대한정보는 SYSCAT.VIEWS, SYSCAT.VIEWDEP, SYSCAT.TABLES 뷰를이용하여확인합니다. SYSCAT. TABLES 뷰에서 TYPE 컬럼의값이 V 입니다 20
4. 뷰 뷰생성및삭제 옵션 모드 설명 < 뷰명 > 임의의고유한이름을지정합니다 < 컬럼명 > 지정하지않으면베이스테이블의컬럼명또는 SELECT 문의결과컬럼명이사용됩니다 WITH < 공통테이블표현식 > AS <SELECT 문 > SELECT 문에서사용될일반테이블표현식을정의합니다 뷰의내용이되는 SELECT 문을지정합니다 WITH CASCADE CHECK OPTION WITH LOCAL CHECK OPTION 뷰의정의에맞지않는데이터를처리하지않습니다. 해당뷰를이용한다른뷰를생성했를때, 이특성을젂달합니다 뷰의정의에맞지않는데이터를처리하지않습니다. 해당뷰를이용한다른뷰를생성했를때, 이특성을젂달하지않습니다. 21
5. 인덱스 효율적인데이터액세스를위해서한테이블에한개이상의인덱스를생성할수있습니다. CREATE INDEX 문과 DROP INDEX 문으로관리합니다. create index 문으로컬럼명과컬럼별정렧순서를지정합니다. 기본적으로인덱스는중복된값을허용하므로중복된행을허용하지않는인덱스를생성하려면 UNIQUE 옵션을이용합니다. UNIQUE 인덱스는 NULL 값을허용하며, NULL 값을가짂행은한개만허용됩니다. 테이블에기본키또는고유키를정의하면해당컬럼에대한자동적으로 UNIQUE 인덱스가생성됩니다. CLUSTER 옵션을이용하면, 해당인덱스의정렧순서를기준으로테이블의데이터가물리적으로배치되므로효율적인액세스가가능합니다. CLUSTER 옵션을가짂인덱스는한테이블에한개만가능하므로, 가장중요한인덱스를 CLUSTER 인덱스로정의합니다. INCLUDE 옵션으로추가된컬럼들은인덱스의데이터페이지에 RID 와함께저장되어, 인덱스젂용액세스를가능하게합니다. INCLUDE 옵션은 UNIQUE 인덱스에서만사용가능합니다. ALLOW REVERSE SCANS 옵션으로생성된인덱스는양방향액세스를허용합니다. drop index 문으로제거하며, 테이블이제거되면자동적으로제거됩니다. 인덱스에대한정보는 SYSCAT.INDEXES 뷰또는 describe indexes 명령어를이용하여확인합니다. 22
5. 인덱스 1) create index 구문 23
6. 시퀀스 데이터베이스차원에서제공되는자동생성일렦번호를시퀀스라고합니다. CREATE SEQUENCE 문, ALTER SEQUENCE 문, DROP SEQUENCE 문으로관리합니다. PREVAL FOR < 시퀀스이름 > 과 NEXTVAL FOR < 시퀀스이름 > 를이용하여현재값과다음값을조회합니다. SYSCAT.SEQUENCE 카탈로그그테이블을조회하면시퀀스정보를조회할수있습니다. 24
6. 시퀀스 다음은 시퀀스생성방법입니다. 옵션 AS <data-type> START WITH INCREMENT BY MINVALUE MAXVALUE CYCLE CACHE 설명 SMALLINT, INT, BIGINT, DECIMAL 중에서원하는데이터유형을선택합니다 음수또는양수의시작값을지정합니다 음수또는양수의증가값을지정합니다 음수또는양수의최소값을지정합니다 음수또는양수의최대값을지정합니다 최대값또는최소값에도달하면다시최소값또는최대값을생성하여순차적값을계속생성합니다 지정된개수의생성값을미리캐쉬에보관하는옵션으로성능향상에도움이됩니다 25
7. 저장프로시저 서버에저장된프로그램로직입니다. 클라이언트에서 CALL 문으로서버의저장프로시저를호출하면, 서버에서로직이실행되어결과만클라이언트로반환됩니다. CREATE PROCEDURE 문과 DROP PROCEDURE 문으로관리합니다. 저장프로시저는클라이언트에응용프로그램에서실행해야하는로직을서버의데이터베이스에저장하여서버에서직접실행함으로써클라이언트와서버갂의데이터젂송량을줄이고, 성능을향상시킵니다. 공용로직을저장프로시저로만들어서사용하면관리가용이합니다. 클라이언트응용프로그램의개별적인코딩으로인한오류와소스를반복적으로작성해야하는부담을줄일수있습니다. 프로시저의로직이변경되면, 서버의저장프로시저만재생성하면됩니다. 저장프로시저는실행시에서버의자원을사용하여실행됩니다. 일반적으로클라이언트보다서버의사양이좋으므로, 동일한로직을실행할때실행시갂이단축될수있습니다. 서버 OS 에의존적인로직의구현이가능합니다. 클라이언트가 Windows 이고, 서버가 UNIX 인경우에 UNIX 에서만지원되는기능을프로시저의로직에포함시킬수있습니다. 26
7. 저장프로시저 저장프로시저의유형은작성하는언어에의해 2 가지로분류됩니다. 유형 외부소스 설명 ESQL, C, Java 등의프로그래밍언어로작성된라이브러리를이용하며, 스칼라값또는결과집합을반환합니다. SQL SQL/PL 로작성되며, 스칼라값또는결과집합을반환합니다. 저장프로시저는 SQL/PL, ESQL, C, Java 등의언어를이용하여생성합니다. $ db2 select * from syscat.routines 저장프로시저에대한정보는 SYSCAT.ROUTINES syscat.procedures 뷰를이용해서확인합니다. 27
7. 저장프로시저 create procedure 문 28
7. 저장프로시저 옵션 모드 설명 < 프로시저명 > 프로시저의이름을지정합니다. < 인수입출력유형 > 인수의유형은 IN, OUT, INOUT으로지정합니다. < 인수이름 > 인수의이름을지정합니다. < 데이터유형 > 인수의데이터유형을지정합니다. SPECIFIC 임의의고유한이름으로지정합니다 DYNAMIC RESULT SETS 반환할결과집합의개수를지정합니다. CONTAINS SQL 데이터의조회, 변경을위한 SQL 문을포함하지않습니다. READS SQL DATA 데이터를변경하는 SQL 문을포함할수없습니다. MODIFIES SQL DATA 지원되는모든 SQL 문을사용합니다. DETERMINISTIC CALLED ON NULL INPUT 동일한입력인수에대해서항상동일한값을반환합니다. 입력인수의값이 NULL 인경우에도호출됩니다. LANGUAGE SQL SQL/PL 로작성한 SP 입니다. NO EXTERNAL ACTION 데이터베이스시스템이관리하지않는외부오브젝트에대한상태를변경시키는로직의포함여부를지정합니다. <SQL 함수본문 > SQL/PL 블록을이용하여구현합니다. 저장프로시저의 <SPECIFIC 명 > 은 < 프로시저명 > 과동일하게지정할수있습니다. 옵션을지정하지않으면, SQLyymmddhhmmssxxx 형식으로엔짂이자동생성합니다. 29