DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
AIX (또는 기타 UNIX) 환경에서 Instance를 작성하는 방법에는 다음과 같이 두 가지가 있다. 화살표 키를 이용하여 커서를 'DB2 인스턴스 작성'에 위치시킨 후 Enter fenced user는 저장 프로시져 및 사용자 정의 함수를 작성하는데 필요한 ID로서 기존의 ID를 공 DB2 웨어하우스 제어 데이터베이스 및 DB2 Distributed Join기능은 필요한 경우, 설치를 선택하 모든 설정이 완료되면 커서를 [확인]에 위치시키고 Enter 설치에 들어가기에 앞서 요약보고서에서 입력사항을 확인한다. 설치가 완료되면 다음과 같은 상태보고서에서 성공여부를 확인하고 [로그보기]를 선택하여 명령센터에서 스크립트를 입력하여 실행한다 스크립트가 완료된 후 제어 센터에 보면 새로운 DataBase가 생="PADDING-LEFT: 10px" AIX 환경 Script를 file로 작성하여 Command Line에서 실행 Syntax Syntax 내용에 대하여 ☞ Tablespace에 할당되는 페이지수와 물리적인 Size Sample Script Sample Script - device : container로 raw device를 사용. TableSpace에 공간 부족시 발생하는 Error → SQL3306N : Table에 행을 삽입하는 동안 sql 오류발생 "-289" TableSpace의 공간 확인 → '상태'필드가 0x0000으로 나오는지 확인 ☞ Table Space가 Full일 경우 Logical Volumn(L/V) Size를 확대하여 그 L/V에 작성된 Table Space 처리내용 1) Alter Tablespace 기능을 이용한 Size 확장작업 add (device '/dev/new_dev' 1000) ex) TS01의 공간이 부족할 경우 Syntax Syntax 내용에 대하여 Sample Script select * from syscat. dbauth 권한 revoke 각 user-id별 정당한 권한 부여 (1) Tablespace 정보 조회 (2) Table 목록 조회 (3) Table의 Column 정보 조회 (1) Alter Table 문 : Table의 정의를 변경 가. Table에 칼럼 추가 나. Primary key, foreign key 추가 및 삭제 다. 점검 강제 규정 정의 삭제 (2) Alter TableSpace 문 : TableSpace의 정의를 변경 가. 컨테이너를 DMS TableSpace에 추가 (2.3.2 참조) 나. TableSpace에 대해 Prefetchsize 설정값 수정 라. TableSpace에 대해 Transferrate 설정값 수정 (1) Grant : 전체 Database에 적용되는 권한을 부여 가. DataBase 권한 나. 색인 특권 다. Package 특권 → alter, index, reference 특권은 View에는 적용되지 않음 (2) Revoke : 전체 DataBase에 적용되는 권한을 취소 가. DataBase 권한 나. 색인특권 다. Package 특권 →run은 excute에 대한 동의어, package는 Program에 대한 동의어로 사용가능 라. Table/ View 특권 (1) Create Trigger 특정 Table에 대한 삭제, 삽입 또는 갱신 조작시 수행되는 또는 조작으로 트리거되는 조치세트를 정의 → EMPLOYEE Table에 새로운 Row가 Insert된 다음에는 COMPANY_STATS Table의 nbemp (2) Create View (3) Drop 각 Object(Alias, distinct, event, function, index, package, table, tablespace, trigger, view,오브젝트 작성
DB2 Instance 작성 (AIX)
db2setup 유틸리티를 이용한 Instance 정의
추가로 작성하는 경우에 사용할 수 있다.
/usr/lpp/db2_07_01/install 디렉토리에서 실행시킬 수 있다.
어를 실행한다.
# cd /cdrom
# ./db2setup
실행해도 된다.
# ./db2setup
소유자 user ID이며, '그룹 이름', '홈 디렉토리', '암호' 등의 필드들을 입력한 후 '확인'에서
Enter.
을 함께 사용하였다. 미리 'db2inst2'라는 ID가 만들어져 있는 경우에는 해당 ID를 명시하고, 그
렇지 않은 경우 '기본 UID 사용' 필드를 체크하여 새로 user ID가 만들어 질 수 있도록 한다.
유하여 사용하거나 또는 추가로 만들어 줄 수 있다
면 인스턴스와 함께 작성된다. 필요없는 경우는 설치하지 않는다.
즉 관리 서버는 한 시스템에 1개 이상 있을 필요는 없다.
로그를 조회할 수 있다.
db2icrt 명령어를 이용한 Instance 정의
a. DB2 Instance 작성
b. 관리 서버 작성
하고자 하는 경우에는 사용할 User-ID 및 Group-ID를 미리 만들어 놓아야 한다.
# cd /usr/lpp/db2_07_01/instance
# db2icrt -u fenced_user_id InstName
래 명령문을 이용하여 관리서버를 작성한다.
# dasicrt DasNameDataBase 작성
Database 작성 구문
[Using codeset(970)] [Numsegs 10] [DFT_Extente_Size 32]
[Catalog Tablespace tblspace-defn]
[Using codeset(970)] => Language code set
[DFT_Extente_Size 32] => Directory extent size
[Catalog Tablespace tblspace-defn] => Tablespace 관련 정보
create database db7
-- connect database
connect to db7
-- dssba1에 대한 권한 부여
revoke createtab on database from public
revoke bindadd on database from public
grant dbadm on database to user db7dba1
grant dbadm on database to user db7dba2
<< 권한 부여 내용 >>
db2inst1 (Database 작성자) => DBADM 권한
Inidzp1, Inidzp2 => DBADM 권한
public => create table및 bind application 권한 박탈
connect 권한만 자동 부여됨Windows 환경
Table Space 작성
Table Space 작성
Table Space 작성 구문
일반적으로 작은 테이블이 많을 때 적합하고 공간 할당은 추가 공간에 대한 요구가 있을 때
수행된다.또한 관리에는 용이하나, DB2가 제공하는 여러가지 옵션을 사용할 수 없다.
공간을 제어한다. SMS에 비해 지속적인 관리가 필요하지만, long 데이타, index등을 따로 저장할
수 있고, 하나의 테이블을 여러 테이블 공간에 저장할 수 있는 등 성능면에서 이점이 있다.
using ( 'x:\...\...' 1000, file 'x:\...\...' 2000)
extentsize 10
-. AIX 환경 Create tablespace TS-name managed by database
using (device '/dev/raaa' 1000, device '/dev/rbbb' 2000)
extentsize 10
-. 1000, 2000 : TableSpace에서 사용할 Page(4K단위) 개수로 Logical Volumn보다
크면 Error 발생
-. extentsize : 복수 컨테이너 사용시 컨테이너간 이동 간격
(예, 컨테이너 1에서 10개의 content에 data를 적재했을 경우
다음부터는 컨테이너 2에 적재, 컨테이너 1과 2가 반복하여 data 저장)
Table Space에 할당되는 페이지 개수는 4K(4, 8, 16, 31k 가능) 단위이며, Logical Volum 작성시
부여한 L/V의 Size보다 크게 지정할 경우 Error가 발생하며 적게 지정할 경우 적은 Size만큼은 사
용이 불가능 하기 때문에 정확하게 계산하여 지정해야 함
Windows 환경
-- connect to database
connect to DB01
-- Table Space 생성
create tablespace TS01 managed by database using
(file 'd:\container\cont1' 2500)
create tablespace TS02 managed by database using
(file 'd:\container\cont2' 2500)
create tablespace TS03 managed by database using
(file 'd:\container\cont3' 2500)
create tablespace TS04 managed by database using
(file 'd:\container\cont4' 2500)
AIX 환경
-- connect to database
connect to DB01
-- Table Space 생성
create tablespace TS01 managed by database using
(device '/dev/rd001' 6144);
create tablespace TS02 managed by database using
(device '/dev/rd002' 13312);
create tablespace TS03 managed by database using
(device '/dev/rd003' 25600);
create tablespace TS04 managed by database using
(device '/dev/rd004' 13312);
Table Space 확장
Table Space Size 확장 방법
→ SQL0289N : TableSpace에 새로운 페이지를 작성할수 없습니다.
- list tablespaces show detail | more
→ '가용 페이지 수'가 충분한지 확인
→ 만약 가용페이지 수가 충분하지 못하면 Table Space의 크기를 확장
Size를 확대하는 방법은 없다. 따라서 별도의 L/V(컨테이너)를 작성하여 해당TableSpace에 추
가하는 방법으로 해결한다. 이럴 경우 Table Space가 복수개의 컨테이너로 구성된다.
(Owner = db2inst1, Group = db2grp)
→ /dev/new_dev : tablespace에 새로 추가하고자 하는 Raw Device명
→ 10000 : 추가하고자 하는 size (기존 tablespace의 page 크기를 따라감)
smit에서 datavg1에 Logical Volume 64M 짜리(lv002)를 하나 만듬
# cd /dev
# chown db2inst1:db2iadm1 lv002
$ su - db2inst1
$ db2 connect to DB01
$ db2 "alter tablespace TS01
add (device '/dev/lv002' 16000)"
Table 및 Index 작성
Table 작성 구문
(column .... ,
column .... ,
column .... )
in table-space-name;
-- create unique index index-name on table-name(column, column);
create index index-name on table-name(column, column) (asc | desc);
Userspace에 Table이 작성된다.
- create table문 내에서 primary key를 지정하고 작성할 경우 unique Index가
자동으로 작성(name 확인불가)되기 때문에 별도로 primary key와 동일한
unique index 작성시 Warning Error 발생 따라서 Primary key를 지정하여 처리하거나,
지정하지 않고 Unique index를 작성하여 처리
- Index를 신규로 작성했을 경우에는 Package를 Rebind해야 새로운 Path가 지정되어
처리속도가 향상된다.
-- create table db2usr.tab01
(cpagjym dec(7,0) not null with default,
cpabrno char(3) not null with default,
cpaspid char(9) not null with default,
cpagjcd char(6) not null with default,
cpajs dec(9,0) not null with default,
cpajanak dec(15,0) not null with default,
cpagyeak dec(15,0) not null with default,
cpawpjan dec(15,0) not null with default,
cpagpjan dec(15,0) not null with default)
in tablespace1;
-- index 작성
create unique index db2admin.idx01 on
db2admin.tab01(cpagjym, cpabrno, cpaspid);
create index db2admin.idx02 on
db2admin.tab01(cpagjym, cpabrno, cpagjcd);
-- 권한부여
grant select on db2usr.tab01 to public;
grant insert on db2usr.tab01 to user db2usr;
grant delete on db2usr.tab01 to user db2usr ;
grant update on db2usr.tab01 to user db2usr ;
권한 부여 및 관리
Database 권한
DB2 Start
Grantor Grantee Granteetype dbadmauth createtabauth bindaddauth
connectaut create_not_fenced
sysibm db2inst1 U Y Y Y Y Y
sysibm public G N Y Y Y Y
1. Create database시 Creator user-id에 모든권한이 부여되고,
2. pulic group에도 상기와 같은 권한이 자동 부여된다.
3. 따라서 create database후 public에 자동 부여된 권한을 revoke한후 user-id 또는
group-id별 정당한 권한을 부여해야함.
2. revoke createtab on database from public
3. revoke bindadd on database from public
4. revoke connect on database from public
5. revoke create_not_fenced on database from public
2. grant connect on database to public
(모든 사용자에게 DB에 Connect 권한만 부여하는 예임)
☞ 종류
Connect 권한 : 사용자가 DB를 Access할 수 있는 기초권한
Bindadd 권한 : 사용자가 DB를 새로운 Package를 작성할 수 있는 권한
Createtab권한 : 사용자가 DB에 Table을 작성할 수 있는 권한
Create_not_fenced : 사용자함수(UDF)를 작성할 수 있는 권한
Index 권한
grantor grantee granteetype indschema indname controlauth
sysibm db2inst1 u svrba xsba001u y
1. connect to database 명
2. grant control on index to user-id
☞ Create index user-id에만 control 권한이 자동 부여된다.Package 권한
Grantor Grantee Granteetype pkgschema pkgname controlauth bindauth executeauth
ysibm svrba U svrba brpb001 y y y
☞ 다음과 같은 특권을 사용하기 위해 DB에 Connect 권한이 필요함
Control 권한 : 사용자에게 Package에 대한 제어권을 제공
Bind 권한 : 기존 Package를 Rebind 할 수 있는 권한
Execute 권한 : 사용자가 기존 Package를 실행할 수 있는 권한Table / View 권한
Grantor Grantee Granteetype tabschema tabname control alter delete_index
insert select replace
가. create table user-id에만 모든권한(control, alter, delete, index, insert, select,
refauth, update)이 자동 부여된다.
나?하여야 한다.
☞ 다음과 같은 특권을 사용하기 위해 DB에 Connect 권한이 필요함
1. grant all on table Table명 to user-id로 권한을 부여할 경우
→ Control을 제외한 모든 권한이 부여됨
2. grant control on table Table명 to user-id
3. grant (all | alter | control | delete | index | insert |
reference | select | update) on table (Table명 | View 명)
to (user | group | public) (Userid | Group-id)
→ Alter, index, reference 특권은 View에 적용되지 않음오브젝트 작성 및 관리를 위한 SQL
Table/Table Space 정보 조회
- list tablespaces | more
or
- list tablespace show detail | more
- list tables for all | more
- select tabschema, tabname, colname, typename,
length, scale, default, nulls
from syscat.columns
또는 제어센타 이용
Table/Table Space 특성 변경
foreign key(COL001) reference TAB02 on delete set null
다. TableSpace에 대해 Overhead 설정값 수정
prefetchsize 64
overhead 19.3
권한 관리를 위한 SQL
-- grant (bindadd | connect | createtab | create_not_fenced | dbadm)
on database to (user | group | public)
권한명(group-id, user-id)
-- grant control on index index-name to (user | group | public)
권한명(group-id, user-id)
-- grant (bind | control | excute) on package
package-name to (user | group | public)
권한명(group-id, user-id)
→ all 특권을 부여할 경우
alter, delete, index, insert, reference, select, update의 특권이 동시에 부여됨
-- revoke (bindadd | connect | createtab | create_not_fenced |dbadm) on database
from (user | group | public)
권한명(group-id, user-id)
-- revoke control on index index-name
from (user | group | public)
권한명(group-id, user-id)
-- revoke (bind | control | excute) on package package-name
from (user | group | public)
권한명(group-id, user-id)
-- revoke (all | alter | control | delete | index | insert | reference | select | update)
on table (table명 | view명)
from (user | group | public)
권한명 (group-id, user-id)
기타 오브젝트 관리를 위한 SQL
After Insert On EMPLOYEE
For Each Row Mode DB2SQL
Update COMPANY_STATS set nbemp = nbemp + 1
Column에 1을 자동 Add하는 Trigger임
as select HENGBUN, NAME, JUSO, DEPTNO, SALARY,
SALARY+BONUS+COMM as TOTAL_PAY
from INSATBL, PAYTABLE
database)를 삭제하는 기능