DB2 Administration

Transcription

1 Manual PA&TS 팀 1

2 문서업데이트 날짜 내용 V1.0, 9.1/9.5 기준작성 2

3 Contents 문서업데이트... 2 CONTENTS... 3 INSTALLATION(IST)... 7 IST001. SW INSTALLATION 설치전준비 설치 IST002. FIXPAK INSTALLATION FIXPAK 적용 IST003. INSTANCE CREATION 인스턴스생성 인스턴스시작 / 종료 인스턴스삭제 인스턴스생성후작업 IST004. DATABASE CREATION Database 생성 Database 삭제 Database 시작 / 종료 DB2 생성후작업 DB 정보 접속 기타 MAINTENANCE (MAT) MAT001. DBM DB CFG SETTING DBM CFG DB CFG MAT002. BUFFERPOOL OPERATION AND MONITORING Bufferpool Operation 버퍼풀활동모니터링 MAT003. TABLESPACE OPERATION AND MONITORING Tablespace Operation MAT004. TABLE CREATION 개요 create table ALTER TABLE MAT005. INDEX CREATION 개요 CREATE INDEX DROP INDEX문 인덱스정보조회 MAT006. CONSTRAINT CREATION 제한조건 참조무결성 MAT007. TRIGGER CREATION TRIGGER 내에서만사용가능한특수 SQL Create Trigger MAT008. IDENTITY COLUMN CREATION IDENTITY COLUMN MAT009. PACKAGE BIND Package 개요

4 2. Package Rebind 데이터베이스유틸리티 Package Bind MAT010. SEQUENCE OPERATION Sequence Operation MAT011. DB2LOOK UTILITY OPERATION db2look MAT012. EXPLAIN SQL Explain 도구 준비 Visual Explain 예 ) View MAT013. DATA EXPORT 개요 export 를위한필요사항 export command 여러경우의 export 문 MAT014. DATA IMPORT 개요 import 를위한필요사항 identity column import import 시 table lock Import Command 여러경우의 IMPORT MAT015. DATA LOAD 개요 Load 를위한필요사항 load 단계 INDEX BUILD identity column load 점검보류상태 (check pending) 백업보류상태 (backup pending) Load Command 여러경우의 LOAD LOAD QUERY CROSS LOADER MAT016. DATABASE BACKUP Backup MAT017. DATABASE RESTORE Restore MAT018. SPLIT MIRROR CREATION Split Mirror MAT019. REDIRECT RESTORE Redirect Restore MAT020. RELOCATE DATABASE Relocate Database MAT021. LOG SETTING 로그 MAT022. CONNECTION CONCENTRATOR SETTING 개요 Setting MAT023. STMM SETTING self tuning memory 관련리소스용데이터베이스매개변수 STMM(self tuning memory menager) MAT024. RUNSTATS 개요 RUNSTATS OPTION 자동통계수집 MAT025. REORG 개요 REORGCHK

5 3. REORG TABLE REORG INDEX reorg 절차 MONITOR(MON) MON001. SNAPSHOT SNAPSHOT MON002. UTILITY MONITORING Utility Monitoring MON003. DB2PD,DB2TOP db2pd db2top MON004. EVENT MONITOR Database Tables Statements MON005. HEALTH CENTER 구성 사용예 MON006. DB2DIAG ANALYZE DB2 UDB 의오류진단파일 진단로그 (db2diag.log) 의파일항목해석 db2diag 유틸리티를이용한특정에러레코드검출방법 MIGRATION(MIG) MIG001. DB2 INSTANCE MIGRATION DB2 Instance Migration MIG002. DAS INSTANCE MIGRATION DAS 인스턴스마이그레이션 MIG003. DATABASE MIGRATION 데이터베이스마이그레이션 MIG004. DATABASE CATALOG UPDATE 시스템카탈로그테이블업데이트 (db2updvn) PROGRAM(PROG) PRG001. SQL/PL-SP/FN SQL/PL-SP/FN PRG002. BASIC EMBEDED SQL Basic Embeded SQL PRG003. JDBC CONNECTION JDBC Connection

6 시작전 ; Caution) 1. 명령구문에서프롬프트 # 은 root 로수행하고 $ 는 db2 인스턴스유저로실행함을의미합니다. 그외특정유저로수행될때 $[username] 로서표기됩니다. 참고문서 1. Info Center 9.5 : 9 : 8 : 2. DB2 9 PDF Manual 9.5 : 1.ibm.com/support/docview.wss?rs=71&uid=swg : ibm.com/software/data/db2/udb/support/manualsv9.html 6

7 Installation(IST) 7

8 IST001. SW Installation 1. 설치전준비 1.1 Disk/Memory 요구사항서버디스크 : 1G 미만메모리 : 최소 256M, GUI 필요시 512M, 권장 1G 클라이언트 1.2 OS 별설치요구사항 설치요구 플랫폼별확인 : * DB2 9.1 fixpack4 이후 db2prereqcheck.exe 으로설치가능한환경인지확인가능 설치파일안에 db2prereqcheck.exe(win), db2prereqcheck(unix) 실행 E:\ESE>db2prereqcheck.exe Checking install prereq for DB2 9 Server OS is WINXP_PRO; SP 2 OS is NT WORKSTATION OS Version = Installation Prereq is OK. * db2prereqcheck 를 AIX 에서실행시 xlc 를 이상으로하여야함 Windows Windows XP Professional(32bit, x64) + SP2 Windows 2003 Standard, Advanced, Datacenter + SP AIX AIX 5.2, 5.3 DB2 V9 : xlc.rte 를포함하는 AIX 용 IBM C++ Runtime Enironment 구성요소 필요 HP UX PA RISC, Itanium HP UX 11iv2( ) 커널변경 : /etc/system 8

9 SAM 을이용한커널변경 패치 : < 설치디렉토리 >/instance/db2osconf 를돌림 HPUX 명령어 db2osconf ex) 500 thread 에대한커널 db2osconf t 500 set msgsys:msginfo_msgmax = set msgsys:msginfo_msgmnb = set msgsys:msginfo_msgssz = 32 set msgsys:msginfo_msgseg = set msgsys:msginfo_msgmap = 2562 set msgsys:msginfo_msgmni = 2560 set msgsys:msginfo_msgtql = 2560 set semsys:seminfo_semmap = 3074 set semsys:seminfo_semmni = 3072 set semsys:seminfo_semmns = 6452 set semsys:seminfo_semmnu = 3072 set semsys:seminfo_semume = 600 set shmsys:shminfo_shmmax = set shmsys:shminfo_shmmni = 3072 set shmsys:shminfo_shmseg = 600 Total kernel space for IPC: 0.35MB (shm) MB (sem) MB (msg) == 3.46MB (total) # sam 9

10 The correlated reference T.C identifies a value of C in a row or group of T to which kmtune ex) system parameter shmmax 를보기를원하는경우 # /usr/sbin/kmtune q shmmax swlist ex) java 로시작하는모든제품 # swlist l product grep Java ex) PHSS_23823 패치가적용되었는지확인 # swlist l product grep PHSS_23823 swinstall ex) java 인스톨예, hp.com 에서다운받은후 # swinstall s sdk_13100os11.depot glance topas, top 같은모니터툴 Linux 최신지원리눅스업데이트 ibm.com/software/data/db2/linux/validate/ 명령어 ipcs ex) kernal parameter 확인 # ipcs l 10

11 공유메모리한계 최대세그먼트수 = 4096 최대세그먼트크기 ( 킬로바이트 ) = 최대총공유메모리 ( 킬로바이트 ) = 최소세그먼트크기 ( 바이트 ) = 1 세마포어한계 최대배열수 = 1024 배열당최대세마포어 = 250 최대세마포어시스템너비 = 세마포어호출당최대작동수 = 32 세마포어최대값 = // SHMMNI // SHMMAX // SHMALL // SEMMNI // SEMMSL // SEMMNS // SEMOPM 메시지 : 한계 최대큐시스템너비 = 1024 최대메시지크기 ( 바이트 ) = 디폴트최대큐크기 ( 바이트 ) = // MSGMNI // MSGMAX // MSGMNB sysctl # cat /etc/sysctl.conf # Controls whether core dumps will append the PID to the core filename. # Useful for debugging multi threaded applications. kernel.core_uses_pid = 1 # hhlee kernel.sem = kernel.shmmax = kernel.shmall = kernel.shmmni = 4096 fs.file max = net.core.rmem_default = net.core.rmem_max = net.core.wmem_default = net.core.wmem_max = net.ipv4.ip_local_port_range = Solaris /etc/system 수정 사용자생성 Windows 사전생성필요없음 Unix( 파일편집 ) /etc/group, /etc/passwd 에추가 os 에따라 (Linux, Solaris 등 ) pwconv 실행 11

12 Unix( 명령어사용 ) ex) Linux # useradd 사용법 : useradd [ u uid( 사용자아이디 ) [ o]] [ g 그룹명 ][ G 그룹,...] [ d 홈디렉토리 ][ s 쉘 ][ c 적요사항 ][ m [ k template]] [ f 비활성화 ] [ e 만기 ] [ p passwd] [ M] [ n] [ r] [ l] name useradd D [ g 그룹명 ][ b 초기디렉토리 ][ s 쉘 ] [ f 비활성화 ] [ e 만기 ] # adduser 사용법 : adduser [ u uid( 사용자아이디 ) [ o]] [ g 그룹명 ][ G 그룹,...] [ d 홈디렉토리 ][ s 쉘 ][ c 적요사항 ][ m [ k template]] [ f 비활성화 ] [ e 만기 ] [ p passwd] [ M] [ n] [ r] [ l] name adduser D [ g 그룹명 ][ b 초기디렉토리 ][ s 쉘 ] [ f 비활성화 ] [ e 만기 ] # groupadd usage: groupadd [ g gid [ o]] [ r] [ f] group 2. 설치 2.1 GUI 모드 windows : 설치파일에서 setup.exe 실행 unix : # db2setup ( 실행전 export DISPLAY=<ip>:0.0) 2.2 TEXT 모드 unix : db2_install #./db2_install 제품설치를위한디폴트디렉토리 /opt/ibm/db2/v9.1 *********************************************************** 설치할다른디렉토리를선택하시겠습니까 [yes/no]?no [Enter] 스페이스로구분하여다음키워드를하나이상지정하여 DB2 제품을설치하십시오. CLIENT RTCL ESE ʺhelpʺ 를입력하여제품이름을다시표시하십시오. 종료하려면 ʺquitʺ 를입력하십시오. ESE ( 서버설치의경우 ) 2.3 라이센스 GUI 모드 db2cc > 도구 > 라이센스센터 12

13 2.3.2 TEXT 모드 # db2licm a db2ese.lic // 라이센스추가 # db2licm l // 리스트보기제품이름 : ʺDB2 Enterprise Server Editionʺ 라이센스유형 : ʺ 제한 ʺ 만기날짜 : ʺ 영구 ʺ 제품 ID: ʺdb2eseʺ 버전정보 : ʺ9.1ʺ 주석 : ʺ 4;(_o)ʺ 제품이름 : ʺWebSphere Federated Serverʺ 만기날짜 : ʺ 영구 ʺ 제품 ID: ʺwsiifʺ 버전정보 : ʺ9.1ʺ 동시커넥터수 : ʺ 사용불가능 ʺ 강제규정 : ʺ 소프트중지 ʺ # db2licm r db2ese // 라이센트삭제 13

14 IST002. FIXPAK Installation 1. FIXPAK 적용 1.1 새 DB2 데이터베이스제품설치를위한 Fix Pack 설치 $ db2stop #./db2setup (Fix Pack 이미지가있는디렉토리에서 ) 1.2 기존 DB2 데이터베이스제품갱신을위한 Fix Pack 설치 $ db2stop #./installfixpack b DB2DIR (Fix Pack 이미지가있는디렉토리에서, DB2DIR 은 DB2 제품이있는위치 ) 1.3 Fix Pack 사후설치태스크 WebSphere Federation Server 가설치되어있는경우 # cd DB2DIR/lib ( DB2DIR 은 DB2 제품이있는위치 ) # mv djxlink.out djxlink_old.out ( 파일을삭제하거나이름을변경함 ) # cat db2dj.ini (db2dj.ini 파일에모든변수가올바른지확인 ) # djxlink 새레벨의 DB2 사용할인스턴스를갱신 $ db2stop # DB2DIR/instance/db2ilist (DB2DIR 은 DB2 제품이있는위치 ) # DB2DIR/instance/db2iupdt InstName ( 인스턴스에대한갱신 ) # DB2DIR/instance/dasupdt (DAS 에대한갱신 ) $ db2updv9 d dbname ( 데이터베이스의시스템카탈로그오브젝트를갱신 ) $ db2start ( 기존설치에관한갱신을위해 ) $ db2admin start 바인드파일에대한바인딩작업 $ db2stop $ db2 terminate $ db2 connect to dbname $ db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE 14

15 $ db2 BIND BLOCKING ALL GRANT PUBLIC ACTION ADD ( 필수바인드파일을바인딩, path 는바인드파일이있는전체경로이며예로는 DB2DIR\sqllib\bnd 과같습니다. DB2DIR 은인스턴스설치디렉토리 ) $ db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD ( 필수바인드파일을바인딩 ) $ db2 terminate $ db2rbind dbname l logfile all ( 바인드되지않은패키지의내재적리바인딩과관련한오버헤드를줄이기위해모든패키지를수동으로리바인딩 ) 1.4. 기타 제한사항 DB2 UDB V8 에서만 V9 으로업그레이드가지원 DB2 UDB V7 이전인경우는먼저 DB2 UDB V8 로업그레이드한후에 V9 으로업그레이드 DB2 UDB V8 에선지원하지만 V9 에서지원하지않거나중지된기능들 ( 아래사이트참조 ) DB2 V9 설치가지원되지않는경우 ) AIX 4.3.3, Solaris 8, Windows 95, Windows 98, Windows NT 및 Windows Me 와같은 Unix, Linux 및 Windows 운영체제의특정버전은지원이안됨 소프트웨어고려사항 Kerberos 인증을사용하려면 IBM Network Authentication Service(NAS) 클라이언트 v1.4 이상이필요 nas p 에서다운로드 64 비트커널전환시루트권한을이용해아래명령어를입력한다. # ln sf /usr/lib/boot/unix_64 /unix # ln sf /usr/lib/boot/unix_64 /usr/lib/boot/unix # bosboot a # shutdown Fr xlc.rte 를포함하는 ʺAIX 용 IBM C++ Runtime Environment 구성요소 ʺ 가필요 NFS 에 DB2 제품설치는여러수동설정단계로인해할수는있으나권장하지않음 DB2 UDB V8 32bit 에서 DB2 V9 64bit 서버로업그레이드수행 (Linux 및 Unix) 64 비트커널이있는시스템에서먼저 32bit 인스턴스를 64bit 인스턴스로갱신 # DB2DIR/instance/db2iupdt w 64 instance_name 15

16 ( DB2DIR 은 DB2 UDB V8 설치경로로설정 ) DB2 V9 설치 인스턴스를이주 ( /tmp 디렉토리에 20MB 의여유공간이필요 ) # DB2DIR/instance/db2imigr u fencedid InstName (Unix 인경우 ) ( DB2DIR 은 DB2 UDB V9 설치경로로설정, 작업전 db2stop 한다 ) # ʺ%DB2PATH%ʺ\bin\db2imigr InstName /u: user,password (Window 인경우 ) $ db2level (DB2 V9 로인스턴스를실행중인지확인 ) DAS 를이주 # DB2DIR/instance/dasmigr (Unix 인경우 ) ( DB2DIR 은 DB2 UDB V9 설치경로로설정, 작업전 db2stop 한다 ) # %DB2PATH%\bin\dasmigr (Window 인경우 ) $ db2daslevel (DB2 V9 로업그레이드되었는지를확인 ) 데이터베이스를이주 $ db2 MIGRATE DATABASE database alias USER username USING password (MIGRATE DATABASE 대신 RESTORE DATABASE 명령을사용하여데이터베이스를업그레이드가능 ) DB2 UDB V8 32bit 에서 DB2 V9 64bit 서버로업그레이드수행 (Window) Windows 의경우, 설치중에자동으로인스턴스를이주할수있는옵션이제공됩니다. 설치중에인스턴스를이주하도록선택할경우데이터베이스만이주해야합니다. DB2 V9 32bit 데이터베이스제품을설치한후기존의 DB2 UDB V8 사본을이주토록선택 ( 모든 DB2 UDB V8 인스턴스및 DB2 Administration Server 는자동이주 ) DB2 V9 64bit 데이터베이스제품을설치한후이주토록선택 ( 이프로시저는 DB2 V9 32bit 데이터베이스제품제거후기존의인스턴스를 64bit 인스턴스로갱신 ) 데이터베이스를이주 $ db2 MIGRATE DATABASE database alias USER username USING password (MIGRATE DATABASE 대신 RESTORE DATABASE 명령을사용하여데이터베이스를업그레이드가능 ) 16

17 IST003. Instance Creation 1. 인스턴스생성 $ su # cd <db2 설치디렉토리 >/instance # db2icrt p u db2fenc1 db2inst1 (db2inst1 생성 ) * db2fenc1, db2inst1 은미리생성되어있어야함. * db2icrt p < 포트번호 or 포트이름 > u <fence user> < 인스턴스명 > 2. 인스턴스시작 / 종료 $ su db2inst1 < 인스턴스명으로로그인 > $ db2start or db2stop < 시작 or 종료 > 3. 인스턴스삭제 $ su # db2idrop db2inst1 4. 인스턴스생성후작업 4.1 통신프로토콜 ( 필수 ) $ su db2inst1 $ db2set DB2COMM=tcpip I db2inst1 (TCP/IP 프로토콜로통신 ) 4.2 포트변경 ( 옵션 ) # su db2inst1 $ db2 update dbm cfg using SVCENAME (70100 프트로접속 ) * 이경우 /etc/services 에 db2c_db2inst /tcp 으로되어있어야함 4.2 DAS 서버설정 ( 옵션 ) # su # cd <db2 설치디렉토리 >/instance # dascrt dasusr1 (dasusr1 으로 DAS 관리서버생성 ) * dasusr1 이미리생성되어있어야함 # daslist (DAS 리스트 ) # su db2inst1 17

18 $ db2set DB2ADMINSERVER=dasusr1 i db2inst1 ( 현재인스턴스의 DAS 설정 ) 4.4 클라이언트설정 c:\> db2 catalog tcpip node node01 remote ds1 server (db2inst1 접속정보 ) c:\> db2 attach to node01 user db2inst1 using db2inst1 (db2inst1 으로접속 ) c:\> db2 get dbm cfg show detail(db2inst1 환경확인 ) c:\> db2 detach ( 접속해제 ) c:\> db2 uncatalog node node02 (node 정보삭제 ) * 관련 table : SYSIBMADM.DBMCFG 5. 인스턴스정보 5.1 현재서버에생성된인스턴스리스트 $ su # cd <db2 설치디렉토리 >/instance # db2ilist ( 현재서버에생성된인스턴스리스트 ) 5.2 현재인스턴스 $ su db2inst1 $ db2 get instance 5.3 인스턴스구성정보 # su db2inst1 $ db2 attach to db2inst1 (show detail 을사용하기위해 attach) $ db2 get dbm cfg show detail 5.4 인스턴스환경 # su db2inst1 $ db2set all 6. 기타 6.1 윈도우에서인스턴스변경 $ set DB2INSTANCE=< 변경인스턴스명 >.. [i] DB2COMM=TCPIP [i] DB2CODEPAGE=

19 IST004. Database Creation 1. Database 생성 1.1 DB 생성 # su db2inst1 $ db2 ʺcreate db PROD on /db2dataʺ </db2data 에 PROD db 생성 > $ db2 ʺcreate database demo automatic storage yes on ʹc:\ʹ dbpath on ʹc:\ʹ using codeset utf 8 territory kr collate using identity pagesize 4096ʺ $ db2 ʺcreate database Alt_UTF8 using codeset UTF 8 territory KRʺ $ db2 ʺcreate db dbli using codeset ibm 949 territory krʺ 1.2 Sample data 를가진 DB 생성 # su db2inst1 $ db2sampl (SAMPLE 이라는이름으로 DB 생성 ~/db2inst1/ NODE0000 에기본으로생성됨 $ db2sampl name TEST (TEST 라는이름으로 sample db 생성 ) $ db2sampl name TEST sql xml (SQL 과 XML object 생성 ) 2. Database 삭제 # su db2inst1 $ db2 ʺdrop db PRODʺ (PROD db 삭제 ) 3. Database 시작 / 종료 $ db2 ʺactivate db PRODʺ (DB start) $ db2 ʺdeactivate db PRODʺ (DB stop) * 종료시사용자가접속해있는경우 $ db2 ʺlist applications for db PRODʺ (PROD db 에접속된 applicatoin) $ db2 ʺforce applications (1)ʺ ( 핸들번호 1 번삭제 ) $ db2 ʺforce applications allʺ ( 전체삭제 ) 4. DB2 생성후작업 4.1 파라미터변경 ( 예 ) $ db2 ʺupdate db cfg for sample using maxappls 50ʺ ( 응용프로그램수최대 50) 19

20 $ db2 ʺget db cfg for sample show detailʺ (Delayed Value 확인 ) 4.2 Log 변경 ( 예 ) db2 update db cfg using LOGBUFSZ 1024 db2 update db cfg using LOGFILSIZ db2 update db cfg using LOGPRIMARY 5 db2 update db cfg using LOGSECOND DB 정보 $ db2 ʺget db cfg for sampleʺ grep code ( 현재 code page) $ db2 ʺlist db directoryʺ (DB List) $ db2 ʺlist active databasesʺ ( 현재 Active 되어있는 DB 리스트 ) $ db2 ʺlist db directory on /db2dataʺ (/db2data 에존재하는 DB List) 6. 접속 6.1 로컬서버에서 $ db2 connect to sample ( 로컬의 sample 접속 )ʺ $ db2 ʺconnect to sample user db2inst2 using db2inst2ʺ ( 다른인스턴스의 DB 접근 ) $ db2 connect reset ( 접속종료 )ʺ $ db2 terminate (back end process 까지종료 )ʺ 6.2 클라이언트에서 $ catalog db sample as sample at node node01 (sample 를 sample 로연결 ) $ db2 uncatalog db sample ( 접속정보삭제 ) 7. 기타 7.1 제한모드 $ db2 connect to sample $ db2 quiesce database immediate force connections $ db2 unquiesce database $ db2 terminate $ db2 deactivate db sample 20

21 7.2 사용자생성 * unix 에서 db2 client 만을설치후서버로접속하기전에사용자인스턴스생성 db2icrt u testuser testuser 21

22 Maintenance (MAT) 22

23 MAT001. DBM DB CFG Setting 1. DBM CFG 1.1 DBM CFG 확인하기 현재값확인하기인스턴스구성파라메터들을보기위해서 get dbm cfg 명령을이용합니다. get dbm cfg 명령을이용하여확인 $db2 get dbm cfg 현재값및지연된값확인하기 구성파라메터의값을변경하였으나아직반영되지않은값을지연된값이라합니다. 지연된값을확인하기위해서는인스턴스 Attach 라는사전절차가필요합니다. 그러나, db2pd 유틸리티를이용할때에는인스턴스 Attach 작업이필요없습니다. db2pd 유틸리티에서현재값은 Memory Value, 지연된값은 Disk Value 항목으로나타냅니다. get dbm cfg 명령을이용하여확인 $db2 attach to < 인스턴스이름 > $db2 get dbm cfg show detail 예 ) $db2 attach to db2inst1 $db2 get dbm cfg show detail db2pd 유틸리티를이용하여확인 $db2pd dbmcfg 1.2 DBM CFG 변경하기 구성파라메터값을변경하기위해서 update dbm cfg 명령을이용합니다. DB2 Admin Client 모듈의일부로서설치된 GUI 프로그램인구성지원프로그램이나제어센터혹은 3ʹrd Party 제품을이용할수도있으나여기에서는 GUI 를이용한방법은다루지않습니다. update dbm cfg 명령을이용하여변경 $db2 update dbm cfg using < 파라메터이름 > < 설정할값 > 예 ) $db2 update dbm cfg using java_heap_sz

24 1.3 DBM CFG 전체초기화하기 reset 명령을이용하여초기화 $db2 reset database manager configuration 1.4 DBM CFG 파라메터 export DBM CFG 파라메터들을 export 해두고차후, 인스턴스를새로생성시에백업한파라메터값들을적용하도록사용할수있습니다. db2cfexp 유틸리티를이용하여백업하기 db2cfexp 유틸리티는 DB2 인스턴스가가지고있는접속환경및파라메터들을추출하여인스턴스자체접속환경및파라메터의백업, 다른인스턴스로의접속환경및파라메터이식혹은접속환경만의이식을위해사용되도록제공되는유틸리티입니다. 제공되는옵션은 3 가지입니다. 여기서접속환경이란로컬및리모트로카탈로그된노드, 데이터베이스정보를말합니다. 데이터베이스로접속하기위한카탈로그정보를추출하였다가클라이언트설치에배포하여동일한접속환경을가지도록할때 유용합니다. 주의 ) db2cfexp 로파라메터추출할때, 데이터베이스구성파라메터들은추출되지않습니다. 1) BACKUP 접속환경및인스턴스파라메터들을백업하였다가추후, 인스턴스재작성시에사용될수있습니다. 2) TEMPLATE 접속환경및인스턴스파라메터들을다른인스턴스에이식할때사용할수있습니다. 3) MAINTAIN 접속환경만다른인스턴스에이식할때사용할수있습니다. $db2cfexp < 파일이름 > < 옵션 > 예 ) $db2cfexp db2inst1.cfg.bkp backup 1.5 DBM CFG 파라메터 import 앞서 db2cfexp 유틸리티를이용하여 export 해둔파일을이용하여인스턴스파라메터들을 import 합니다. $db2cfimp < 파일이름 > 예 ) db2cfimp db2inst1.cfg.bkp 24

25 2. DB CFG 2.1 DB CFG 확인하기 현재값확인하기 위에서인스턴스구성파라메터를보기위해서 get dbm cfg 를사용하였으나, 데이터베이스구성파라메터를보기위해서 get db cfg 명령을사용합니다. get db cfg 명령을이용하여확인 $db2 get db cfg 현재값및지연된값확인하기 구성파라메터의값을변경하였으나아직반영되지않은값을지연된값이라합니다. 지연된값을확인하기위해서는먼저데이터베이스접속이필요합니다. 그러나, db2pd 유틸리티를이용할때에는비록접속은없더라도데이터베이스가 Activate 상태이면가능합니다. db2pd 유틸리티에서현재값은 Memory Value, 지연된값은 Disk Value 항목으로나타냅니다. get db cfg 명령을이용하여확인 $db2 connect to < 데이터베이스알리아스 > $db2 get db cfg for < 데이터베이스알리아스 > show detail 예 ) $db2 connect to sample $db2 get db cfg for sample show detail db2pd 유틸리티를이용하여확인 activate 명령수행할때해당데이터베이스가이미활성화되어있다면 SQL1490W 메시지가나타납니다. 명령을수행하기전에데이터베이스가이미활성화된것을알고있으면 activate 명령부분은건너뛰십시오. $db2 activate db < 데이터베이스알리아스 > $db2pd d < 데이터베이스이름 > dbcfg 예 ) $db2 activate db sample $db2pd d sample dbcfg 2.2 DB CFG 변경하기 구성파라메터값을변경하기위해서 update db cfg 명령을이용합니다. DB2 UDB Admin Client 모듈의일부로서설치된 GUI 프로그램인구성지원프로그램이나제어센터혹은 3ʹrd Party 제품을이용할수도있으나여기에서는 GUI 방법은다루지않습니다. 25

26 update db cfg 명령을이용하여변경 $db2 update db cfg for < 데이터베이스알리아스 > using < 파라메터이름 > < 설정할값 > 예 ) $db2 update db cfg for sample using logsecond DB CFG 전체초기화하기 reset 명령을이용하여초기화 $db2 reset database configuration for < 데이터베이스알리아스 > 예 ) $db2 reset database configuration for sample 2.4 DB CFG 파라메터 export 데이터베이스구성파라메터는백업시같이백업되고리스토어되므로데이터베이스파라메터를이식을염두하고추출하기위한유틸리티는존재하지않습니다. 그러나, 필요에따라이식을염두해두고백업하고자한다면 get db cfg 결과나 db2pd 결과를참조하여스크립트를작성하여추출할수도있습니다. 예 ) #!/bin/ksh # # # # Generate DB2 UDB Database Configuration Parameters Import Script # # # Name : exp4dbcfg.sh # Purpose : DB CFG Extract and Make Import Script # # # Made by hdson # Created : 2005/09/23 # Last Modified : 2006/06/16 # INSTNAME=`whoami`; alias now=ʹdate +ʺ%y%m%d %H%M%Sʺʹ; print ʺ\nʺ; for DBNAME in `db2 LIST DB DIRECTORY grep E ʺDatabase aliasʺ cut d ʺ=ʺ f 2` do DB_OF=${INSTANCE}.${DBNAME}.`now`.dbcfg # Caution : Do Not Split!!! This is One Line Commands. db2 get db cfg for ${DBNAME} awk ʹ/) = / {print $0}ʹ sed ʹ/ SHEAPTHRES/dʹ cut c cut d ʺ(ʺ f 2 sed ʹs/) =//ʹ sed ʹ/ $/dʹ sed ʺs/^/db2 \ʺupdate db cfg for ${DBNAME} using /ʺ sed ʹs/$/\ʺ/ʹ > ${DB_OF}; 26

27 print ʺDBCFG FILE : ( ${DB_OF} ) Generated.ʺ; done echo ʺʺ 2.5 DB CFG 파라메터 import 앞서 exp4dbcfg.sh 쉘스크립트를이용하여 export 해둔출력파일을이용하여데이터베이스구성파라메터들을 import 합니다. $chmod +x < 파일이름 > $./< 파일이름 > 예 ) $chmod +x db.sample dbcfg $./db.sample dbcfg 27

28 MAT002. BufferPool Operation And Monitoring 1. Bufferpool Operation 1.1 Bufferpool 정보보기 데이터베이스에생성되어있는버퍼풀정보보기 데이터베이스에는언제나 4 개의 Hidden Bufferpool 과최소 1 개이상의 Real Bufferpool 이존재합니다. Hidden Bufferpool 의이름은 IBMSYSTEMBP<page size : 4,8,16,32>K 로서명명되어있으며, 이버퍼풀은실제버퍼풀의정의된크기가물리적인시스템상의메모리보다커서실제버퍼풀이할당되지못할때실제버퍼풀을대신하여데이터베이스가임시로시작되도록하고, 데이터베이스가시작됨으로써해당버퍼풀의사이즈를재조정가능한상태로만들어주는용도로존재합니다. DB2 데이터베이스는유효한버퍼풀없이시작되지못하도록설계되어있고, 데이터베이스시작없이는버퍼풀의크기를조정할수없도록되어있기에히든버퍼풀은유사시에임시로실제버퍼풀을대신합니다. 카탈로그테이블 syscat.bufferpools 테이블을이용하여버퍼풀정보를조회하는방법 ( 크기가자동관리되도록설정된버퍼풀의경우 syscat.bufferpools 테이블의 npages 칼럼값이 2 로표시됩니다. 물론, 수동으로설정된경우에는실제설정값이표시됩니다.) $db2 ʺselect * from syscat.bufferpoolsʺ db2pd 를이용하여버퍼풀정보를조회하는방법 db2pd 유틸리티를통해정보조회를하기위해서는대상데이터베이스가 activate 상태이어야합니다. 만약, 데이터베이스가이미접속이있거나 activate 상태라면 activate 명령단계는수행하지않으셔도됩니다. $db2 activate db < 데이터베이스 Alias> $db2pd db < 데이터베이스이름 > bufferpools 예 ) $db2 activate db sample $db2pd db sample bufferpools 1.2 Bufferpool 생성하기 버퍼풀을생성하기위해기본적으로결정해야하는값에는기본적으로두가지가있습니다. 첫번째는, 이버퍼풀을사용하게되는테이블스페이스의페이지사이즈와같게버퍼풀의페이지사이즈가정해져야하므로, 어떤페이지사이즈의버퍼풀이필요한지알아야합니다. 28

29 두번째는, 버퍼풀을생성하여사용할때그크기를수동으로관리할지데이터베이스가자동으로관리하게할지선택하는것입니다. 버퍼풀의사이즈관리를자동관리로사용하게되면자동관리로바꾸기전의수동크기가버퍼풀의초기크기가됩니다. 생성명령문에서 immediate size 옵션을생략하면디폴트값 1000 의크기로생성됩니다. 생성명령문에서 pagesize 옵션을생략하면데이터베이스의디폴트페이지사이즈와동일한크기로생성됩니다. 이두가지정해졌다면아래예제들처럼버퍼풀을간편하게생성하실수있습니다. 물론, 버퍼풀생성을위해서는생성명령을실행하시기전에미리데이터베이스에접속해있어야합니다. 주의 : 자동관리환경에서초기크기가너무크게설정되면버퍼풀을메모리에올리는데많은시간이소요되어데이터베이스기동시간이길어질수있습니다. 데이터베이스의디폴트페이지사이즈확인하기 $db2 ʺget db cfg for < 데이터베이스 Alias>ʺ grep i ʺdatabase page sizeʺ 예 ) $db2 ʺget db cfg for sampleʺ grep i ʺdatabase page sizeʺ 디폴트크기로데이터베이스기본페이지사이즈와동일한크기로생성 $db2 ʺcreate bufferpool < 버퍼풀이름 >ʺ 예 ) $db2 ʺcreate bufferpool testbp01ʺ 4K 짜리 40M 크기의버퍼풀생성 $db2 ʺcreate bufferpool < 버퍼풀이름 > immediate size < 페이지수 > pagesize < 페이지크기 > kʺ 예 ) $db2 ʺcreate bufferpool bp4k0001 immediate size pagesize 4 kʺ 8K 짜리 120M 크기의버퍼풀생성 예 ) $db2 ʺcreate bufferpool bp8k0001 immediate size pagesize 8 kʺ 4K 짜리자동크기관리의버퍼풀생성 $db2 ʺcreate bufferpool < 버퍼풀이름 > immediate size < 페이지수 > pagesize < 초기페이지크기 > kʺ $db2 ʺalter bufferpool < 버퍼풀이름 > immediate size automaticʺ 예 ) $db2 ʺcreate bufferpool bp4k0001 immediate size pagesize 4 kʺ $db2 ʺalter bufferpool bp4k0001 immediate size automaticʺ 29

30 16K 짜리자동크기관리의버퍼풀생성 $db2 ʺcreate bufferpool < 버퍼풀이름 > immediate size < 페이지수 > pagesize < 초기페이지크기 > kʺ $db2 ʺalter bufferpool < 버퍼풀이름 > immediate size automaticʺ 예 ) $db2 ʺcreate bufferpool bp16k0001 immediate size 5000 pagesize 16 kʺ $db2 ʺalter bufferpool bp16k0001 immediate size automaticʺ 1.3 Bufferpool 크기조정하기 버퍼풀의크기를조정하는것은생성할때와비슷하게 alter bufferpool 명령문에서 immediate size 키워드에원하는크기를설정해주는것으로수행되며, 크기조정작업은동적으로이루어집니다. 그러나, 버퍼풀을줄이고자할때에는해당버퍼풀에올라와있는데이터가사용되고있는동안은해당부분아래로줄어들지않고대기하다가사용이없어지면서줄어들기때문에가급적사용량이적을때조정작업을하는것이원하는크기로줄이기에용이합니다. 버퍼풀이한번생성되면버퍼풀의전체크기는조정가능하지만페이지크기단위는조정할수없습니다. 4K 짜리버퍼풀의크기를 1.5G 로조정 $db2 ʺalter bufferpool < 버퍼풀이름 > immediate size < 페이지수 >ʺ 예 ) $db2 ʺalter bufferpool bp4k0001 immediate size ʺ 32K 짜리버퍼풀의크기를 1.75G 로조정 $db2 ʺalter bufferpool < 버퍼풀이름 > immediate size < 페이지수 >ʺ 예 ) $db2 ʺalter bufferpool bp32k0001 immediate size ʺ 버퍼풀의크기가자동증가하도록변경 $db2 ʺalter bufferpool < 버퍼풀이름 > immediate size automaticʺ 예 ) $db2 ʺalter bufferpool bp16k0001 immediate size automatic ʺ 1.4 Bufferpool 삭제하기 버퍼풀에사용중인데이터가있을때버퍼풀을삭제할수없습니다. 사용중인어플리케이션을모두종료하고삭제하십시오. 또한, hidden bufferpool 을제외하고최소한개이상의실제버퍼풀이있어야하므로마지막실제버퍼풀은그냥삭제할수없으므로, 추가로다른버퍼풀을먼저생성한후지울버퍼풀을삭제하는트릭이필요할수도있습니다. 버퍼풀삭제 $db2 ʺdrop bufferpool < 버퍼풀이름 >ʺ 30

31 예 ) $db2 ʺdrop bufferpool bp4k0001ʺ 2. 버퍼풀활동모니터링 db2 데이터베이스는명령및유틸리티를통해서버퍼풀의기본정보, 활동정보및 Hit Ratio 등등여러정보들을모니터링할수있도록도구를제공합니다. 2.1 snapshot 을이용한버퍼풀활동모니터링 snapshot 을이용한버퍼풀모니터링명령은다음과같습니다. $db2 ʺupdate monitor switches using bufferpool onʺ $db2 ʺget snapshot for bufferpool on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺupdate monitor switches using bufferpool onʺ $db2 ʺget snapshot for bufferpool on sampleʺ 2.2 db2pd 를이용한버퍼풀활동모니터링 db2pd 유틸리티를통해정보조회를하기위해서는대상데이터베이스가 activate 상태이어야합니다. 만약, 데이터베이스가이미접속이있거나 activate 상태라면 activate 명령단계는수행하지않으셔도됩니다. $db2 activate db < 데이터베이스 Alias> $db2pd db < 데이터베이스이름 > bufferpools 예 ) $db2 activate db sample $db2pd db sample bufferpools 31

32 MAT003. Tablespace Operation And Monitoring 1. Tablespace Operation 1.1 Tablespace 정보보기 llist tablespaces [show detail] 명령을통해정보조회 이명령을통해테이블스페이스의타입, 상태정보를조회할수있습니다. 또한 show detail 키워드를추가하여크기및속성에대한상세정보를부가적으로조회할수도있습니다. list tablespaces 명령을수행하기위해서는대상데이터베이스에접속이필요합니다. 이미접속이존재한다면접속단계는수행하지않으셔도됩니다. 일반적인정보조회 $db2 connect to < 데이터베이스알리아스 > $db2 list tablespaces 예 ) $db2 connect to sample $db2 list tablespaces 상세정보부가조회 $db2 connect to < 데이터베이스알리아스 > $db2 list tablespaces show detail 예 ) $db2 connect to sample $db2 list tablespaces show detail tablespace container 정보조회 list tablespace containers for <tablespace id> [show detail] 명령명령을통해대상테이블스페이스에생성된컨테이너정보를조회할수있습니다. 일반정보조회를통해테이블스페이스에속한컨테이너이름및유형을조회할수있으며, 상세정보조회를통해컨테이너의크기및엑세스가능상태를조회할수있습니다. 이명령을수행하기위해서는대상데이터베이스에접속이되어있어야합니다. 이미접속하였다면데이터베이스에접속하는부분은수행하지않으셔도됩니다. 일반적인정보조회 $db2 connect to < 데이터베이스알리아스 > $db2 list tablespace containers for <tablespace id> 예 ) $db2 connect to sample $db2 list tablespace containers for 2 32

33 상세정보부가조회 $db2 connect to < 데이터베이스알리아스 > $db2 list tablespace containers for <tablespace id> show detail 예 ) $db2 connect to sample $db2 list tablespace containers for 2 show detail db2pd 를통해 tablespace 및 tablespace container 정보조회 db2pd 유틸리티를통해정보조회를하기위해서는대상데이터베이스가 activate 상태이어야합니다. 만약, 데이터베이스가이미접속이있거나 activate 상태라면 activate 명령단계는수행하지않으셔도됩니다. db2pd 유틸리티를통해정보조회 $db2 activate db < 데이터베이스알리아스 > $db2pd db < 데이터베이스이름 > tablespaces 예 ) $db2 activate db sample $db2pd db sample tablespaces 카탈로그테이블을이용한테이블스페이스정보조회 DB2 V9.1 버전부터 SYSIBMADM 스키마가도입되면서많은정보성카탈로그뷰테이블들이추가되었습니다. 테이블스페이스정보와관련한카탈로그테이블및뷰리스트는다음과같습니다. 이리스트중에서 SNAPTBSP 로시작하는이름의뷰테이블들을조회하면각종테이블스페이스관련정보를확인할수있습니다. $db2 list tables for system grep i ʺtbspʺ TBSPACEAUTH SYSCAT V SYSTBSPACEAUTH SYSIBM T SNAPTBSP SYSIBMADM V SNAPTBSP_PART SYSIBMADM V SNAPTBSP_QUIESCER SYSIBMADM V SNAPTBSP_RANGE SYSIBMADM V TBSP_UTILIZATION SYSIBMADM V 테이블스페이스권한부여상태조회 ( USE, PUBLIC 권한부여상태조회가능 ) $db2 select * from sysibm.systbspaceauth $db2 select * from syscat.tbspaceauth 33

34 테이블스페이스속성정보조회 ( 아이디, 타입, 페이지사이즈, 속성 ) $db2 select * from sysibmadm.snaptbsp 테이블스페이스상세부가정보조회 ( 속성정보, HWM 포함한용량지표, 리밸런싱상태등등 ) $db2 select * from sysibmadm.snaptbsp_part 테이블스페이스오브젝트중 QUIESCE 가걸린 OBJECT 조회 ( QUIESCE 가걸린테이블, QUIESCE 모드및 QUIESCE 수행 Agent 조회가능 ) $db2 select * from sysibmadm.sanptbsp_quiescer 테이블스페이스익스텐트분포 (Extent Range) 정보조회 $db2 select * from sysibmadm.snaptbsp_range 테이블스페이스사용량위주로정보조회 (HWM 지표가없음 ) $db2 select * from sysibmadm.tbsp_utilization 1.2 테이블스페이스생성하기 ASM(Auto Storage Management) 데이터베이스환경에서테이블스페이스생성 DB2 V9.1 이후부터데이터베이스기본생성환경이 ASM 으로변화하였습니다. 즉, 데이터베이스생성옵션중 Automatic Storage NO 를명시적으로지정하지않는다면모두 ASM 환경의데이터베이스가되는것입니다. 자동스토리지관리데이터베이스환경상에서기본적으로테이블스페이스생성은컨테이너세부옵션없이지정된스토리지경로를사용하여각경로위치마다컨테이너가자동생성되게합니다. 그러나, 자동스토리지관리데이터베이스환경일지라도명시적으로컨테이너옵션절을명시하여수동으로컨테이너들을생성할수도있습니다. Notice. 스토리지경로조회에정보가없다면 Non ASM 환경의데이터베이스입니다. db2pd 이용데이터베이스자동스토리지경로조회 $db2pd db < 데이터베이스이름 > storagepaths 예 ) $db2pd db sample storagepaths snapshot 이용데이터베이스자동스토리지경로조회 $db2 get snapshot for db on < 데이터베이스알리아스 > grep i ʺautomatic storage pathʺ 예 ) $db2 get snapshot for db on sample grep i ʺautomatic storage pathʺ 34

35 자동으로컨테이너가생성되도록테이블스페이스생성 $db2 create tablespace < 테이블스페이스이름 > 수동으로컨테이너를명시하여테이블스페이스생성 아래예제들에서테이블스페이스의페이지사이즈단위는 16 K 로지정하여제공합니다. 테이블스페이스타입별데이터저장가능유형을 SMS 는 USER TEMPORARY, DMS 는 LARGE 로생성하였습니다. 실제환경에서적용하실때에만약페이지사이즈단위를바꾸어사용하시려면 pagesize <size> K 부분에서 <size> 부분을수정하면됩니다. 또한, 상황에맞게타입과유형, 페이지사이즈, 컨테이너옵션절을변경하여지정하시면됩니다. 가. SMS 타입으로생성 $db2 ʺcreate user temporary tablespace < 테이블스페이스이름 > page size 16 k managed by system(ʹ<path>ʹ, ʹ<path>ʹ,..., ʹ<path>ʹ)ʺ 예 ) $db2 ʺcreate user temporary tablespace usertempspace1 pagesize 16 k managed by system ( ʹ/db2/db2utemp1/db2temp1ʹ, ʹ/db2/db2utemp1/db2temp2ʹ ) ʺ 나. DMS(filesystem 형식 ) 타입으로생성 $db2 ʺcreate large tablespace < 테이블스페이스이름 > managed by database ( file ʹ<filesystem>ʹ <size>, file ʹ<filesystem>ʹ <size>,..., file ʹ<filesystem>ʹ <size> )ʺ 예 ) $db2 ʺcreate large tablespace userspace2 pagesize 16 k managed by database ( file ʹ/db2/db2data1/userspace2.c00001ʹ 5000, file ʹ/db2/db2data2/userspace2.c00002ʹ 5000, file ʹ/db2/db2data3/userspace2.c00003ʹ, 5000, file ʹ/db2/db2data3/userspace2.c00004ʹ, 5000 ) ʺ 다. DMS(raw device 형식 ) 타입으로생성 $db2 ʺcreate large tablespace < 테이블스페이스이름 > managed by database ( device ʹ<raw device name>ʹ <size>, device ʹ<raw device name>ʹ <size>,..., device ʹ<raw device name>ʹ <size> )ʺ 예 ) $db2 ʺcreate large tablespace userspace2 pagesize 16 k managed by system ( device ʹ/dev/rdb2tbls1ʹ 20000, device ʹ/dev/rdb2tbls2ʹ 20000, device ʹ/dev/rdb2tbls3ʹ 20000, device ʹ/dev/rdb2tbls4ʹ ) ʺ Non ASM 데이터베이스환경에서테이블스페이스생성 DB2 V9.1 이전버전의기본이었던 Non ASM 관리형태로테이블스페이스를작성할수있습니다. Non ASM 관리형태에서테이블스페이스컨테이너는컨테이너절을명시적으로입력하여작성하는방법으로만작성가능합니다. 이는, ASM 환경에서수동으로컨테이너를지정하는요령과같습니다. 35

36 수동으로컨테이너를명시하여테이블스페이스생성 아래예제들에서테이블스페이스의페이지사이즈단위는 16 K 로지정하여제공합니다. 테이블스페이스타입별데이터저장가능유형을 SMS 는 USER TEMPORARY, DMS 는 LARGE 로생성하였습니다. 실제환경에서적용하실때에만약페이지사이즈단위를바꾸어사용하시려면 pagesize <size> K 부분에서 <size> 부분을수정하면됩니다. 또한, 상황에맞게타입과유형, 페이지사이즈, 컨테이너옵션절을변경하여지정하시면됩니다. 가. SMS 타입으로생성 $db2 ʺcreate user temporary tablespace < 테이블스페이스이름 > page size 16 k managed by system(ʹ<path>ʹ, ʹ<path>ʹ,..., ʹ<path>ʹ)ʺ 예 ) $db2 ʺcreate user temporary tablespace usertempspace1 pagesize 16 k managed by system ( ʹ/db2/db2utemp1/db2temp1ʹ, ʹ/db2/db2utemp1/db2temp2ʹ ) ʺ 나. DMS(filesystem 형식 ) 타입으로생성 $db2 ʺcreate large tablespace < 테이블스페이스이름 > managed by database ( file ʹ<filesystem>ʹ <size>, file ʹ<filesystem>ʹ <size>,..., file ʹ<filesystem>ʹ <size> )ʺ 예 ) $db2 ʺcreate large tablespace userspace2 pagesize 16 k managed by database ( file ʹ/db2/db2data1/userspace2.c00001ʹ 5000, file ʹ/db2/db2data2/userspace2.c00002ʹ 5000, file ʹ/db2/db2data3/userspace2.c00003ʹ, 5000, file ʹ/db2/db2data3/userspace2.c00004ʹ, 5000 ) ʺ 다. DMS(raw device 형식 ) 타입으로생성 $db2 ʺcreate large tablespace < 테이블스페이스이름 > managed by database ( device ʹ<raw device name>ʹ <size>, device ʹ<raw device name>ʹ <size>,..., device ʹ<raw device name>ʹ <size> )ʺ 예 ) $db2 ʺcreate large tablespace userspace2 pagesize 16 k managed by system ( device ʹ/dev/rdb2tbls1ʹ 20000, device ʹ/dev/rdb2tbls2ʹ 20000, device ʹ/dev/rdb2tbls3ʹ 20000, device ʹ/dev/rdb2tbls4ʹ ) ʺ 1.3 테이블스페이스변경하기 이미작성된테이블스페이스에서컨테이너를추가하거나삭제혹은크기를변경할수있습니다. 또한, 테이블스페이스의속성값을일부변경하는방법을표시합니다 컨테이너추가하기 테이블스페이스데이터들은같은스트라이프셋안에서리밸런싱을일으킵니다. 36

37 여기서말하는스트라이프셋은 OS 의스트라이프셋이아니며데이터베이스테이블스페이스레벨에서데이터를라운드로빈형식으로흩뿌리는단위가되는세트를말합니다. 테이블스페이스가작성될때디폴트로생성되는스트라이프셋번호는 0 으로새로스트라이프셋을생성할때마다 1 씩증가합니다. Notice. 스트라이프셋이여러개생성된경우추후, 리스토어가수행되더라도스트라이프셋간데이터이동은일어나지않습니다. 즉, 리스토어시에는기존에존재했던각스트라이프셋에해당하는데이터는해당스트라이프셋내에서만적재되게된다는것입니다. 스트라이프셋을추가및스트라이프셋지정없이컨테이너를추가하는경우 데이터가중. 소용량이고데이터리밸런싱으로인한부하가적다고판단될때스트라이프셋을추가하지않고컨테이너를추가하면서사용하려는경우에사용됩니다. $db2 ʺalter tablespace < 테이블스페이스이름 > add ( <type> ʹcontainer stringʹ <numer of pages or unitsize> )ʺ 예 ) db2 alter tablespace userspace2 add ( file ʹ/db2/db2data1/userspace2.c00005ʹ 5000, file ʹ/db2/db2data2/userspace2.c00006ʹ 5000 ) 새로운스트라이프셋을추가하여새로작성된스트라이프셋에추가하려는경우 이미데이터가대용량이되어기존스트라이프셋에추가하면과도한리밸런싱이발생할수있는경우새로운스트라이프셋을추가하는것을권장합니다. 다만, I/O 를잘고려하여새로컨테이너를추가할때필요한컨테이너개수와분포를적절히하여야합니다. 예를들어기존에여러개의컨테이너를이용하여사용하다가새스트라이프셋추가시이스트라이프셋에아주적은수의컨테이너를추가한다면 I/O 감소가있을수있고나중에추후컨테이너를더추가함으로써리밸런싱을일으키게할소지가있으므로잘고려하여야합니다.( 기존컨테이너개수를유지하고충분한 I/O 채널을확보하는것이바람직합니다.) $db2 ʺalter tablespace < 테이블스페이스이름 > begin new stripeset ( <type> ʹcontainer stringʹ <numer of pages or unitsize> )ʺ 예 ) db2 alter tablespace userspace2 begin new stripset ( file ʹ/db2/db2data1/userspace2.c00007ʹ 5000, file ʹ/db2/db2data2/userspace2.c00008ʹ 5000, file ʹ/db2/db2data2/userspace2.c00009ʹ 5000, file ʹ/db2/db2data2/userspace2.c00010ʹ 5000, file ʹ/db2/db2data2/userspace2.c00011ʹ 5000, file ʹ/db2/db2data2/userspace2.c00012ʹ 5000 ) 이미만들어진스트라이프셋중특정스트라이프셋에추가하려는경우 이미만들어져있는스프라이프셋중특정한스트라이프셋에컨테이너를추가하려는경우에사용됩니다. 37

38 $db2 ʺalter tablespace < 테이블스페이스이름 > add to stripeset <stripeset number> ( <type> ʹcontainer stringʹ <numer of pages or unitsize> )ʺ 예 ) db2 alter tablespace userspace2 add to stripeset 1 ( file ʹ/db2/db2data1/userspace2.c00013ʹ 5000, file ʹ/db2/db2data2/userspace2.c00014ʹ 5000 ) 컨테이너삭제하기 컨테이너에테이블스페이스헤더정보데이터를제외하고데이터가없을때만컨테이너삭제가가능합니다.( DMS 유형의테이블스페이스만가능하며, 기존의테이블스페이스에서새로운스트라이프셋을만들면서컨테이너들을생성을하였는데잘못생성된컨테이너들전체혹은일부를삭제하거나, 기존테이블스페이스내특정스트라이프셋에속한컨테이너들에속한오브젝트를모두삭제한후해당컨테이너들을삭제를하려하거나, 신규테이블스페이스생성후잘못생성된일부컨테이너를삭제하는경우가해당될수있습니다.) $db2 ʺalter tablespace < 테이블스페이스이름 > drop ( <type> ʹcontainer stringʹ )ʺ 예 ) $db2 alter tablespace userspace2 drop ( file ʹ/db2/db2data1/userspace2.c00013ʹ, file ʹ/db2/db2data2/userspace2.c00014ʹ ) 컨테이너사이즈변경하기가. REDUCE 현재컨테이너크기에서감소시키고자하는크기를지정합니다. 명령수행후컨테이너크기는 [ 결과크기 = ( 현재크기 지정크기 ) ] 와같습니다. REDUCE ( 개별컨테이너 ; 적용하고자하는컨테이너 ( 들 ) 만명기하여도됩니다. ) $db2 ʺalter tablespace < 테이블스페이스이름 > reduce ( <type> ʹcontainer string 1ʹ <numer of pages or unitsize>... <type> ʹcontainer string Nʹ <numer of pages or unitsize> )ʺ 예 ) $db2 alter tablespace userspace2 reduce ( file ʹ/db2/db2data1/userspace2.c00001ʹ 3000, file ʹ/db2/db2data2/userspace2.c00002ʹ 3000 ) $db2 alter tablespace userspace2 reduce ( file ʹ/db2/db2data1/userspace2.c00001ʹ 48 M, file ʹ/db2/db2data2/userspace2.c00002ʹ 48 M ) REDUCE ( 전체컨테이너 ) $db2 ʺalter tablespace < 테이블스페이스이름 > reduce ( all <numer of pages or unitsize> )ʺ 예 ) $db2 alter tablespace userspace2 reduce ( all 3000 ) $db2 alter tablespace userspace2 reduce ( all 48 M ) 나. EXTEND 현재컨테이너크기에서증가시키고자하는크기를지정합니다. 38

39 명령수행후컨테이너크기는 [ 결과크기 = ( 현재크기 + 지정크기 ) ] 와같습니다. EXTEND ( 개별컨테이너 ; 적용하고자하는컨테이너 ( 들 ) 만명기하여도됩니다. ) $db2 ʺalter tablespace < 테이블스페이스이름 > extend ( <type> ʹcontainer string 1ʹ <numer of pages or unitsize>... <type> ʹcontainer string Nʹ <numer of pages or unitsize> )ʺ 예 ) $db2 alter tablespace userspace2 extend ( file ʹ/db2/db2data1/userspace2.c00001ʹ 2000, file ʹ/db2/db2data2/userspace2.c00002ʹ 2000 ) $db2 alter tablespace userspace2 extend ( file ʹ/db2/db2data1/userspace2.c00001ʹ 32 M, file ʹ/db2/db2data2/userspace2.c00002ʹ 32 M ) EXTEND ( 전체컨테이너 ) $db2 ʺalter tablespace < 테이블스페이스이름 > extend ( all <numer of pages or unitsize> )ʺ 예 ) $db2 alter tablespace userspace2 extend ( all 2000 ) $db2 alter tablespace userspace2 extend ( all 32 M ) 다. RESIZE 현재컨테이너크기에서결과적으로만들고자하는크기를지정합니다. 명령수행후컨테이너크기는 [ 결과크기 = 지정크기 ] 와같습니다. RESIZE ( 개별컨테이너 ; 적용하고자하는컨테이너 ( 들 ) 만명기하여도됩니다. ) $db2 ʺalter tablespace < 테이블스페이스이름 > resize ( <type> ʹcontainer string 1ʹ <numer of pages or unitsize>... <type> ʹcontainer string Nʹ <numer of pages or unitsize> )ʺ 예 ) $db2 alter tablespace userspace2 resize ( file ʹ/db2/db2data1/userspace2.c00001ʹ 5000, file ʹ/db2/db2data2/userspace2.c00002ʹ 5000 ) $db2 alter tablespace userspace2 resize ( file ʹ/db2/db2data1/userspace2.c00001ʹ 80 M, file ʹ/db2/db2data2/userspace2.c00002ʹ 80 M ) RESIZE ( 전체컨테이너 ) $db2 ʺalter tablespace < 테이블스페이스이름 > resize ( all <numer of pages or unitsize> )ʺ 예 ) $db2 alter tablespace userspace2 resize ( all 5000 ) $db2 alter tablespace userspace2 resize ( all 80 M ) 39

40 1.3.4 prefetch 속성변경하기 AUTOMATIC $db2 ʺalter tablespace < 테이블스페이스이름 > prefetch automaticʺ 예 )$db2 ʺalter tablespace userspace1 prefetch automaticʺ MANUAL VALUE $db2 ʺalter tablespace < 테이블스페이스이름 > prefetch <numer of pages or unitsize>ʺ 예 )$db2 ʺalter tablespace userspace1 prefetch 32 kʺ 버퍼풀재지정하기 테이블스페이스에기존에지정된버퍼풀을다른용도로사용하거나삭제를계획하면서기존버퍼풀대신에다른버퍼풀로대체하여사용하도록할때사용합니다. ( 테이블스페이스의단위페이지사이즈와버퍼풀의단위페이지사이즈는동일해야합니다. ) 명령을수행하여버퍼풀을재지정하여도재지정명령수행이전에접속한어플리케이션은이미기존버퍼풀을사용하므로, 버퍼풀의완전한이전은해당버퍼풀을사용하던어플리케이션이모두접속해제한후이루어집니다. 또한, 그이후에라야기존버퍼풀의삭제도가능합니다. 하여, 온라인상으로버퍼풀재지정을하려할때에는재지정후에기존버퍼풀의사용량을체크하면서그사용량이감소할때기존버퍼풀의사이즈를줄여놓고추후, 삭제하도록계획하시는것이좋습니다. $db2 ʺalter tablespace < 테이블스페이스이름 > bufferpool < 버퍼풀이름 >ʺ 예 ) $db2 ʺalter tablespace userspace1 bufferpool bp8k0002ʺ 자동증가속성 ( AUTORESIZE ) 지정하기 자동증가에한계는무한사이즈및증가폭은디폴트값으로지정하려할때 $db2 ʺalter tablespace < 테이블스페이스이름 > autoresize yesʺ 예 ) $db2 ʺalter tablespace userspace1 autoresize yesʺ 자동증가한계를특정값으로제한하려할때 $db2 ʺalter tablespace < 테이블스페이스이름 > autoresize yes maxsize <numer of pages or unitsize>ʺ 예 ) $db2 ʺalter tablespace userspace1 autoresize yes maxsize 256 Mʺ 자동증가폭및자동증가한계를특정값으로제한하려할때 $db2 ʺalter tablespace < 테이블스페이스이름 > autoresize yes increasesize <numer of pages or unitsize> maxsize <numer of pages or unitsize>ʺ 예 ) $db2 ʺalter tablespace userspace1 autoresize yes increasesize 512 k maxsize 256 mʺ 40

41 자동증가폭은특정값으로제한하지만자동증가한계는무한사이즈로지정하려할때 $db2 ʺalter tablespace < 테이블스페이스이름 > autoresize yes increasesize <numer of pages or unitsize> maxsize noneʺ 예 ) $db2 ʺalter tablespace userspace1 autoresize yes increasesize 512 k maxsize noneʺ 1.4 테이블스페이스삭제하기 테이블스페이스삭제를하려면기본적으로해당테이블스페이스내존재하는오브젝트 ( 테이블, UDF, 시퀀스... 등등 ) 들을삭제한후삭제하는것을원칙으로합니다. 그러나, 아주심플한환경 ( 한테이블스페이스에모든오브젝트가있고테이블에관련한 FUNCTION 이없음 ) 이라면해당테이블스페이스에엑세스가없을때단순하게 drop tablespace < 테이블스페이스이름 > 명령으로삭제할수있지만대부분의복잡한운영환경에서는단순삭제가불가능합니다. 아래는단순삭제가불가능한두가지상황에서원하는테이블스페이스의삭제를하기위한절차를설명합니다. 첫번째, 오브젝트가여러테이블스페이스에걸쳐있을경우 ( 테이블은 tabspace1 에인덱스는 tablespace2 에 LOB 데이터는 tablespace3 이런형태 ) 테이블스페이스삭제시에에러 SQL0282N 을만나게됩니다. 이경우에는기본적으로는해당테이블스페이스에존재하는테이블들을모두삭제하고난후완전히비게되는테이블스페이스를단순삭제명령을이용하여삭제하여야합니다. 그러나, 이경우에서오브젝트가걸쳐있는테이블스페이스들모두삭제해도된다고판단되시면관련되는테이블스페이스들을 drop 문에같이명기함으로써오브젝트들의삭제없이한번에테이블스페이스들을삭제하실수도있습니다. 물론, 해당테이블스페이스들에있는오브젝트로의액세스가없을때하셔야하며, 만약액세스가존재하면 Lock 에의해삭제작업이 Waiting 이걸리게됩니다. ( SQL0478N 이해소되고 SQL0278N 상황이된경우도같은방식으로적용할수있습니다. ) 두번째, 테이블스페이스에존재하는테이블을참조하는 FUNCTION 이존재함으로써 SQL0478N 을만나게됩니다. 이경우에우선테이블에종속되어있는오브젝트확인을위해 syscat. db2lk_dep_of() 테이블함수를이용합니다. 그리고, 리스트에보이는테이블에종속된오브젝트들중테이블을참조하는 FUNCTION 을먼저삭제하고테이블을삭제하면테이블의일반데이터및 LOB 그리고인덱스도같이삭제됩니다. 그러나, 뷰와트리거는자동으로작동불능 ( 작동불능이란참조오브젝트의미싱으로해당오브젝트를사용할수없는상태를의미하며추후오브젝트가다시생성되면해당뷰나트리거에액세스가발생할때자동으로컴파일되면서사용가능하게됩니다.) 상태로빠지며삭제가일어나지않으므로추후필요에따라삭제합니다. 그리고테이블을참조하는 Stored Procedure 도그대로남아있으므로추후필요에따라삭제합니다. 이제해당테이블스페이스를삭제하시면됩니다. 단일테이블스페이스삭제 ( 심플한환경의테이블스페이스일때 ) $db2 ʺdrop tablespace < 테이블스페이스이름 >ʺ 예 ) $db2 ʺdrop tablespace userspace1ʺ 41

42 테이블에종속된오브젝트확인 ( 참고 : 테이블외다른오브젝트도가능하나테이블타입만예를들었음. ) $db2 ʺselect * from table(sysproc.db2lk_dep_of(ʹ<type>ʹ,ʹ<schema>ʹ,ʹ<name>ʹ))ʺ 예 ) $db2 ʺselect * from table(sysproc.db2lk_dep_of(ʹtʹ,ʹsonʹ,ʹtest1ʹ))ʺ 여러테이블스페이스동시삭제 $db2 ʺdrop tablespace < 테이블스페이스이름 1>, < 테이블스페이스이름 2>... < 테이블스페이스이름 N>ʺ 예 ) $db2 ʺdrop tablespace userspace1, userspace2, userspace3ʺ 1.5 테이블스페이스정보모니터링 snapshot 을이용한테이블스페이스모니터링 테이블스페이스정보외에버퍼풀항목을수집하기위해모니터스위치가켜져있어야함. $db2 ʺupdate monitor switches using bufferpool onʺ $db2 ʺget snapshot for tablespaces on < 데이터베이스알리아스 >ʺ 예 ) $db2 ʺupdate monitor switches using bufferpool onʺ $db2 ʺget snapshot for tablespaces on sampleʺ db2pd 를이용한테이블스페이스모니터링 db2pd 유틸리티를통해정보조회를하기위해서는대상데이터베이스가 activate 상태이어야합니다. 만약, 데이터베이스가이미접속이있거나 activate 상태라면 activate 명령단계는수행하지않으셔도됩니다. $db2 activate db < 데이터베이스알리아스 > $db2pd db < 데이터베이스이름 > tablespaces 예 ) $db2 activate db sample $db2pd db sample tablespaces 42

43 MAT004. Table Creation 1. 개요 테이블은 UDB 에정보를저장하는데사용되는기본오브젝트이므로, 가장기본적인데이터정의명령문이 CREATE TABLE 문입니다. CREATE TABLE 문의기본작업은작성할테이블의이름과, 이테이블의모든컬럼에대한이름과데이터유형을지정하는것입니다. 또한이명령문을통해선택적으로테이블의특정컬럼이널 (NULL) 값을수락하지않도록하고하나이상의컬럼이테이블의기본키를구성하도록지정할수있습니다. 1.1 필요한권한 IMPLICIT_SCHEMA : 존재하지않는 SCHEMA 를생성하며테이블생성시 SCHEMA 에대한 CREATIN : 기존에존재하는스키마를이용하여테이블생성시 TABLESPACE 에대한 USE 권한 : IN, LONG IN, INDEX IN 옵션적용시 TABLE 에대한 REFERENCE : 외부키정의시부모테이블에대한 REFERENCE 권한필요 1.2 페이지크기 DB2 UDB 는 4, 8, 16, 32 KB 크기의페이지를지원합니다. 컬럼의개수, 행의최대크기, 테이블의최대크기등은페이지의크기에따라달라집니다. 페이지 크기 컬럼의 개수행의 최대 길이테이블 최대 크기 9.1, , k 500 개 4005byte 64G 2T 8k 1012 개 8010 byte 128G 4T 16k 1012 개 byte 256G 8T 32k 1012 개 byte 512G 16T * 참고 : 9.5 SQL Reference Vol1. (db2s1e950.pdf) 의 Apendix A. SQL and XML Limits 의 631 페이지의 Table 47. 실제컬럼수는다음공식에의해변할수있다. 총컬럼수 * 8 + LOB 컬럼수 * 12 <= 페이지크기에대한행크기한계 2. create table 2.1 Table Space 를지정하여생성 데이터가지정될테이블스페이스를지정합니다. 지정된테이블스페이스는변경불가합니다. 지정하지않으면기본사용자테이블스페이스에지정됩니다. 43

44 2.1.1 IN IN 만설정하면테이블의모든데이터와인덱스가지정된동일한테이블스페이스에저장됩니다. ex) DB2 ʺCREATE TABLE tablename (column definition) IN tablespace name LONG IN LONG 데이터를별도의테이블스페이스에저장할수있습니다. 지정된테이블스페이스는 DMS 방식의 LARGE 유형이여야합니다. in 과함께지정되어야하며, long in 만지정될수는없습니다. ex) DB2 ʺCREATE TABLE tablename (column definition) IN tablespace name LONG IN tablespace name INDEX IN INDEX 데이터를별도의테이블스페이스에저장할수있습니다. 지정된테이블스페이스는 DMS 방식의 REGULAR 유형이여야합니다. in 과함께지정되어야하며, index in 만지정될수는없습니다. ex) DB2 ʺCREATE TABLE tablename (column definition) IN tablespace name INDEX IN tablespace name ex) $ db2 ʺcreate table table1(col1 int not null, col2 varchar(10) with default ʹcolʹ, col3 blob(2k) not logged) in tbs1 index in idxtbs1 long in ltbs1ʺ 2.2 NOT LOGGED INITIALLY 테이블이작성된트랜잭션과동일한트랜잭션에서테이블에작성된변경사항 ( 예 : 테이블에행을삽입 ) 은복구로그에기록되지않는다는것을의미합니다. 대형테이블의초기로드중로깅활동을줄이기위해 NOT LOGGED INITIALLY 옵션을사용할수도있습니다. ALTER TABLE 문을사용하여활성화시킬수있으며 COMMIT 문을실행하면상태가해제됩니다. Ex) $ DB2 ʺCREATE TABLE table name(column definition) NOT LOGED INITTALLY $ DB2 ʺALTER TABLE table name ACTIVATE NOT LOGGED INITIALLYʺ $ DB2 ʺinsert into table name ~~ $ COMMIT(UOW 가완료되면다시해제됨 ) ( 참고 ) not logged mode 로입력된데이터는복구시 rollforward 명령어로복구불가 ( 참고 ) not logged mode 상태에서 UOW 가실패하면테이블을재생성해야함 ex) $ DB2 ʺDROP TABLE table name $ db2look d dbname z schema name f table name o file name $ db2 svtf file name 44

45 2.3 DATA CAPTURE CHANGES 사용자테이블의변경사항을다른데이터베이스에복제하고자할때필요합니다. 이절은데이터복제를지원하기위해시스템로그에특수항목이작성되도록합니다. 이절을생략하거나 DATA CAPTURE NONE 을지정할경우, 사용자테이블에변경사항이작성되어도로그에특수항목이작성되지않습니다. 2.4 LIKE 다른테이블과동일한모양의테이블을생성합니다. ex) DB2 ʺCREATE TABLE table1 LIKE table2ʺ 2.5 파티션된테이블작성 CREATE TABLE <NAME> (<column_name> <data_type> <null_attribute>) IN <table space list> PARTITION BY RANGE (<column expression>) STARTING FROM <constant> ENDING <constant> EVERY <constant> 자동으로데이터파티션생성 (EVERY) $ db2 ʺCREATE TABLE test1(col1 INT) PARTITION BY RANGE (col1) (STARTING FROM (1) ENDING AT (100) EVERY (20))ʺ 수동으로데이터파티션생성 $ db2 ʺCREATE TABLE test2 (year INT, month INT) IN tbsp1, tbsp2, tbsp3, tbsp4, tbsp5, tbsp6, tbsp7, tbsp8 PARTITION BY RANGE(year, month) (STARTING FROM (2001, 1) ENDING (2001,3) IN tbsp1, ENDING (2001,6) IN tbsp2, ENDING (2001,9) IN tbsp3, ENDING (2001,12) IN tbsp4, ENDING (2002,3) IN tbsp5, ENDING (2002,6) IN tbsp6, ENDING (2002,9) IN tbsp7, ENDING (2002,12) IN tbsp8)ʺ ( 참고 ) starting 값과 ending 값은 512 자내로정의되어야합니다. 넘어갈경우 error SQL0636N 발생 2.6 temporary table 작성 테이블을작성할때 ROLLBACK 또는 ROLLBACK TO SAVEPOINT 를지정할경우, 테이블에있는모든행이삭제되도록 ( 디폴트값인 DELETE ROWS) 또는테이블에있는행이보존되도록 (PRESERVE ROWS) 지정할수있습니다. 45

46 고유제한조건, 외부키제한조건, 트리거및인덱스를포함하는다른모든컬럼속성은정의되지않습니다. COMMIT 조작이수행될때, WITH HOLD 커서가테이블에서열려있지않은경우테이블에있는모든데이터가삭제됩니다. ex) DB2 ʺ DECLARE GLOBAL TEMPORARY TABLE gbl_temp LIKE table1 ON COMMIT DELETE ROWS NOT LOGGED IN temp_tbspʺ 2.7 column option NOT NULL 지정된컬럼의컬럼값에널값이올수없습니다. ex) $ db2 ʺCREATE TABLE table1(col1 varchar(10) NOT NULL, col2 smallint) IN tbs1ʺ WITH DEFAULT insert 시컬럼값이입력되지않을경우자동으로입력될 DEFAULT 값을설정할수있습니다. with default 만지정하고값을지정하지않으면시스템기본값이적용됩니다. ex) $ db2 ʺCREATE TABLE table1(col1 varchar(10) NOT NULL, col2 smallint WITH DEFAULT 3) ex) $ db2 ʺCREATE TABLE table2(col1 varchar(10) NOT NULL, col2 smallint WITH DEFAULT) > 두번째예제에서 db2 ʺinsert into table2(col1) values (ʹ 금 ʹ) 을하면 col2 에는시스템기본값인 0 이들어간다 NOT LOGGED 컬럼에대한변경사항이로그에기록되지않음을지정합니다. 1GB 보다큰 lob 는 not logged 를지정해야합니다. $ db2 ʺCREATE TABLE table1(col1 int NOT NULL, col2 varchar(10) WITH DEFAULT ʹcolʹ, col3 blob(2k) NOT LOGGED) in tbs1 index in idxtbs1 long in ltbs1ʺ COMPACT 후속추가조작이용이하도록 LOB 스토리지끝에공간을남겨두는대신 LOB 컬럼의값이최소의디스크스페이스를차지하도록지정합니다. $ db2 ʺCREATE TABLE table1(col1 int NOT NULL, col2 varchar(10) WITH DEFAULT ʹcolʹ, col3 blob(2k) NOT LOGGED COMPACT) in tbs1 index in idxtbs1 long in ltbs1ʺ NOT COMPACT 컬럼에서 LOB 데이터값을쉽게변경할수있도록약간의공간을지정 46

47 ( 참고 ) LOGGED/NOT LOGGED 및 COMPACT/NOT COMPACT 옵션은대형오브젝트데이터유형컬럼에만적용됩니다. 이러한옵션에대한디폴트값은 LOGGED 및 NOT COMPACT 입니다 ( 참고 ) 컬럼에 PRIMARY KEY, UNIQUE 옵션이설정된경우, NOT NULL 도설정되어야합니다. ( 참고 )compact 지정후데이터를저장하면컬럼에연산이추가될때 ( 길이가증가될때 ) 성능이저하됩니다. 2.8 테이블제한조건 Constraint creation 참조 3. ALTER TABLE 테이블의특성을변경할때사용하는 SQL 문, ALTER TABLE 문은기존테이블에컬럼을추가하고, Varchar 컬럼의길이를늘리거나제한조건과같은기타테이블등록정보를추가하거나삭제하는데사용됩니다 3.1 COMMAND OPTION DATA CAPTURE NONE 데이터복제에대한추가정보를로그에기록하지않을때사용합니다 DATA CAPTURE CHANGES 이테이블에대한 SQL 변경사항과관련된추가정보가로그에기록됨을나타냅니다. 이옵션은이테이블이복제되며캡처프로그램을사용하여이테이블에대한변경사항을로그에서캡처할경우에필요합니다 3.2 example 테이블에컬럼추가 ALTER TABLE table name ADD COLUMN column name data type ex) $ db2 $ ALTER TABLE table1 ADD COLUMN col1 varchar(10) ( 참고 ) 컬럼을변경해도모든컬럼의총바이트수가지정된최대레코드크기를초과해서는안됩니다 (SQLSTATE 발생 ). ( 참고 ) 고유제한조건또는인덱스에서컬럼이사용되는경우, 새로운길이는고유제한조건또는인덱스에대해저장된길이의합이페이지크기에대한인덱스키길이한계를초과하면안됩니다 테이블에컬럼추가후 NOTT NULL 과 DEFAULT 값지정 ALTER TABLE table name ADD COLUMN column name data type NOT NULL DEFAULT value ex) $ db2 ʺ ALTER TABLE table1 ADD COLUMN col1 varchar(10) NOT NULL DEFAULTʺ 47

48 ( 참고 ) 컬럼에 default 값을주고컬럼을추가하면기존의컬럼들은값은모두 default 값으로채워집니다. ( 참고 ) 테이블에컬럼추가시 NOT NULL 옵션으로주고 DEFAULT 값을주지않으면 error 발생 (sql0193) 테이블에 CONSTRAINT 추가 ALTER TABLE table name ADD CONSTRAINT constraint name FOREIGN KEY(column name) REFERENCES reference table name ex) $ db2 ʺALTER TABLE table1 ADD CONSTRAINT fk_table1 FOREIGN KEY(col1) REFERENCES reference table2 ON DELETE SET NULLʺ SQL 변경사항을확장형식으로로그하도록테이블을변경 ALTER TABLE table name DATA CAPTURE CHANGES ex) $ db2 ʺALTER TABLE table1 DATA CAPTURE CHANGES 테이블에컬럼삭제 ALTER TABLE table name DROP COLUMN column name ex) $ db2 ʺ ALTER TABLE table1 DROP COLUMN col1 ( 참고 ) 컬럼삭제시 data capture 가설정되어있으면컬럼이삭제되지않습니다. ( 참고 ) 컬럼을삭제후 REORG 를해주어야테이블조작이가능합니다.(reorg pending 상태 ) ( 참고 ) 컬럼삭제시해당컬럼에 primary key, foreign key 가생성되어있으면제약조건도같이삭제되어집니다 테이블에 CONSTRAINT 제거 ALTER TABLE table name DROP CONSTRAINT constraint name ex) $ db2 ʺALTER TABLE table1 DROP CONSTRAINT constraint name 파티션된테이블에데이터파티션추가 각새데이터파티션값의범위는 STARTING 및 ENDING 절에의해판별됩니다. STARTING 및 ENDING 절중하나또는둘다를제공해야합니다. 48

49 새범위가기존데이터파티션의범위와겹치지않아야합니다. 첫번째기존데이터파티션앞에새데이터파티션을추가할경우 STARTING 절을지정해야합니다. 이범위를개방된종료범위로만들려면 MINVALUE 를사용하십시오. 마찬가지로, 마지막기존데이터파티션뒤에새데이터파티션을추가하려면 ENDING 절을지정해야합니다. 이범위를개방된종료범위로만들려면 MAXVALUE 를사용하십시오. ex) $db2 ʺ CREATE TABLE hole (c1 int) PARTITION BY RANGE (c1) (STARTING FROM 1 ENDING AT 10, STARTING FROM 20 ENDING AT 30)ʺ $ db2 ʺALTER TABLE hole ADD PARTITION STARTING 15ʺ $ db2 ʺ SELECT SUBSTR(tabname, 1,12) tabname, SUBSTR(datapartitionname, 1, 12) datapartitionname, seqno, SUBSTR(lowvalue, 1, 4) lowvalue, SUBSTR(highvalue, 1, 4) highvalue FROM SYSCAT.DATAPARTITIONS WHERE TABNAME=ʹHOLEʹ ORDER BY seqnoʺ TABNAME DATAPARTITIONNAME SEQNO LOWVALUE HIGHVALUE HOLE PART HOLE PART HOLE PART 파티션된테이블에데이터파티션삭제 ALTER TABLE 문을 DETACH PARTITION 절과함께사용하여데이터파티션을삭제한후 DROP TABLE 문을사용하여개별테이블을삭제할수있습니다. ex) $ db2 ʺALTER TABLE table1 DETACH PART part1 INTO table2ʺ $ db2 ʺ DROP TABLE junkʺ part1 데이터파티션은 table1 접속해제되어 table2 에배치됩니다. 49

50 MAT005. Index Creation 1. 개요 인덱스는테이블에있는하나이상의컬럼을인덱스의키컬럼으로사용하여테이블에작성하는액세스지원도구로서하나의테이블에한개이상의인텍스를생성할수있습니다. 2. CREATE INDEX 2.1 unique 테이블에인덱스키와같은값을갖는둘이상의행이포함되지못하도록합니다. 지정하지않으면기본적으로중복된값을허용합니다. Null 값을허용하지만 2 개이상의 Null 값을가질수없습니다. On nickname 을설정하면, 인텍스키에대한데이터가데이터소스테이블의모든행에대해고유한값을포함하는경우에만 Unique 로지정가능합니다. 테이블에 primary key or unique key 생성시해당컬럼에자동으로 UNIQUE 인덱스가생성됩니다. ( 참고 ) 자동으로인덱스가생성된컬럼에인덱스를생성하면경고가발생하지만설정한이름으로인덱스가생성됩니다. 테이블에분산키가지정된경우지정된컬럼에분산키의컬럼이모두포함되어야합니다. ex) $ db2 CREATE UNIQUE INDEX inx1 ON table1(col1, col2) 2.2 column name 최대 64 개의컬럼을지정할수있으며지정된컬럼의길이의합은페이지크기의인덱스키길이한계보다작아야합니다. 2.3 ASC/DESC EXTEND USING 으로정의된인덱스에서는지정할수없습니다 default 값은 ASC 입니다.. ex) $ db2 CREATE UNIQUE INDEX inx1 ON table1(col1 desc) 2.4 INCLUDE 인덱스키컬럼세트에추가될컬럼을지정하는절을사용합니다. INCLUDE 지정시 UNIQUE 도함께지정되어야합니다. 고유성은키컬럼에만적용되며 INCLUDE 절의추가컬럼에는적용되지않습니다. ex) $ db2 CREATE UNIQUE INDEX inx1 ON table1(col1) INCLUDE(col2, col3) 50

51 2.5 CLUSTER 인덱스를클러스터링인덱스로선언하여인덱스키와같거나거의같은값을갖는행들이실제스토리지에서근접하게저장됨을의미합니다. 클러스터링인덱스는시스템이실제페이지를최소한으로페치하면서테이블의모든행을스캔할수있는방안을제공합니다. cluster index 는테이블에한개만만들수있습니다. ex) $ db2 CREATE UNIQUE INDEX inx1 ON table1(col1) CLUSTER 2.6 PCTFREE 추후삽입및갱신이가능하도록인덱스작성시, 빈스페이스로남겨둘인덱스페이지비율을지정합니다. DEFAULT 값은 10% 입니다. ex) db2 CREATE INDEX inx1 ON table1(col1) CLUSTER PCTFREE ALLOW REVERSE SCANS 정방향및역방향스캔을모두지원할수있도록합니다. ex) db2 $ ʺCREATE UNIQUE INDEX idx1 ON table1(col1) ALLOW REVERSE SCANSʺ 3. DROP INDEX 문 3.1 DROP INDEX SchemaName.IndexName ex) $ db2 ʺDROP INDEX schema1.inx1 명령문을사용하지않아도테이블이삭제되면자동으로삭제되어집니다. 4. 인덱스정보조회 $ db2 SELECT * FROM syscat.indexes $ db2 DESCRIBE INDEXES FOR TABLE table1 SHOE DETAIL ( 참고 ) LOB 는인덱스의키컬럼이될수없습니다. 인덱스를작성한후옵티마이저가인덱스를잘활용할수있도록통계정보를수집해야합니다. COLLECT STATISTICS 옵션를사용할경우인텍스생성후즉시 RUNSTATS 를실행하지않아도됩니다 ex) CREATE INDEX inx1 ON table1(col1) COLLECT STATISTICS V9.1 부터는인덱스에대하여 default 로 reverse scan 이사용가능합니다. 51

52 MAT006. Constraint Creation 1. 제한조건 데이터값의변경방법을관리하는규칙으로, 데이터값의유효성을확인하고필요할때자동으로값을생성할수있습니다. 각제한조건은특정테이블에만적용되므로 create table 문의일부로정의되며테이블생성후 alter table 문을이용하여추가하거나삭제할수있습니다. 또한테이블이삭제되면연관된모든제한조건도함께삭제됩니다. 1.1 제한조건의종류 NOT NULL UNIQUE KEY 제한조건 PRIMARY KEY 제한조건 FOREIGN KEY 제한조건 CHECK 제한조건 NOT NULL 컬럼이 NULL 값을가질수없습니다. ex) CREATE TABLE table1 (col1 varchar(10) NOT NULL, ) UNIQUE KEY 제한조건 컬럼이나컬럼세트값에대한고유값을보장하며 UNIQUE KEY 의모든컬럼은 NOT NULL 제한조건을가집니다. UNIQUE KEY 를생성하면자동으로 UNIQUE INDEX 를생성하며, 제한조건에이름이있으면동일한이름으로인덱스를생성합니다. 이름을명시하지않으면시스템이자동으로이름을생성합니다. SELECT * FROM SYSCAT.KEYCOLUSE 를사용하면정의된모든 UNIQUE KEY 와그조건이적용되는테이블과컬럼에대하여조회할수있습니다 create table 문에 UNIQUE KEY 추가시 CONSTRAINT constraint name UINQUE (column name) ALTER TABLE schema name.table name ADD CONSTRAINT constraint name UNIQUE(column name) ex) $ db2 ʺCREATE TABLE schema1.table1(col1 char(10) NOT NULL, col2 varchar(10) NOT NULL, col3 smallint, constraint uk_col1 UNIQUE(col1))ʺ $ db2 ʺALTER TABLE schema1.table1 ADD CONSTRAINT uk_col2 UNIQUE(col2)ʺ UNIQUE KEY 삭제 52

53 ALTER TABLE schema name.table.name DROP CONSTRAINT constraint name ex) $ db2 ʺALTER TABLE schema1.table1 DROP CONSTRAINT uk_col2ʺ PRIMARY KEY 제한조건 UNIQUE KEY 와비슷한특성을가지지만, 테이블당한개만생성가능하며고유성과 NOT NULL 로조합된등록정보를갖는컬럼이나컬럼의조합입니다. 데이터유형이 LOB 인컬럼은기본키가될수없습니다. PRIMARY KEY 는참고무결성관계에대해상위키의역할을합니다 create table 문에 PRIMARY KEY 추가시 CONSTRAINT constraint name PRIMARY KEY (column name) 생성된테이블에 alter table 문을이용하여 PRIMARY KEY 추가시 ALTER TABLE schema name.table name ADD CONSTRAINT constraint name PRIMARY KEY(column name) ex) $ db2 ʺCREATE TABLE schema1.table1(col1 char(10) NOT NULL, col2 varchar(10) NOT NULL, KEY(col1))ʺ col3 smallint, constraint pk_col1 PRIMARY $ db2 ʺALTER TABLE schema1.table1 ADD CONSTRAINT pk_col1 PRIMARY KEY(ol1)ʺ ( 참고 ) NOT NULL 을설정하지않은컬럼에 PRIMARY KEY 를설정하면 SQL0524N EROR 발생 발생 UNIQUE 하지않은행에 PRIMARY KEY 를설정하면 SQL0673N ERROR PRIMARY KEY 삭제 ALTER TABLE schema name.table name DROP CONSRAINT constraint name ex) db2 ʺALTER TABLE schema1.table1 DROP CONSTRAINT pk_col1ʺ FOREIGN KEY 제한조건 두개의테이블사이의관계를지정합니다. 두개의테이블이동일한테이블일수도있습니다.( 자체참조 ) NULL 값을가질수있습니다, create table 문에 FOREIGN KEY 추가시 CONSTRAINT constraint name FOREIGN KEY (column name) REFERENCES schema name.table name 53

54 생성된테이블에 alter table 문을이용하여 PRIMARY KEY 추가시 ALTER TABLE schema name.table name ADD CONSTRAINT constraint name FOREIGN KEY(column name) REFERENCES schema name.table name ex) $ db2 ʺCREATE TABLE schema1.table1(col1 char(10) NOT NULL, col2 varchar(10) NOT NULL, KEY(col1) col3 smallint, constraint fk_col1 FOREIGN REFERENCES schema1.table2)ʺ $ db2 ʺALTER TABLE schema1.table1 ADD CONSTRAINT fk_col1 FOREIGN KEY(col1) REFERENCES schema1.table2ʺ ( 참고 ) 상위키에없는값을가지는외부키의행이있으면 SQL0667N ERRR 발생 FOREIGN KEY 삭제 ALTER TABLE schema name.table name DROP CONSRAINT constraint name ex) db2 ʺ ALTER TABLE schema1.table1 DROP CONSTRAINT fk_col1ʺ CHECK 제한조건 CHECK 제한조건은컬럼에입력되는데이터의값의유효성을확인하는역할을합니다. 점검조건이라는술어를포함하여데이터의삽입이나갱신시점검조건에의해테스트되어거짓인경우그 SQL 문은롤백됩니다 create table 문에 PRIMARY KEY 추가시 CONSTRAINT constraint name CHECK(check condition) 생성된테이블에 alter table 문을이용하여 PRIMARY KEY 추가시 ALTER TABLE schema name.table name ADD CONSTRAINT constraint name CHECK(check condition) ex) $ db2 ʺCREATE TABLE schema1.table1(col1 char(10) NOT NULL, constraint ck_col3 CHECK(col3<20))ʺ col2 varchar(10) NOT NULL, col3 smallint, $ db2 ʺALTER TABLE schema1.table1 ADD CONSTRAINT ck_col3 CHECK(col3<20)ʺ CHECK 제한조건삭제 54

55 ALTER TABLE schema name.table name DROP CONSRAINT constraint name ex) $ db2 ʺALTER TABLE schema1.table1 DROP CONSTRAINT ck_col1ʺ set integrity 문을이용하여제약조건체크연기 $ db2 create table test1(col1 varchar(10), col2 samllint) $ db2 insert into test1 values( 123, 1234) $ db2 alter table test add constraint test1_ck check (length(rtrim(col1))=5) sql0544n 발생 $ db2 set integrity for test1 off $ db2 alter table test add constraint test1_ck check (length(rtirm(col1))=5) $ db2 create table except_test1 like test1 $db2 set integrity for test1 immediate checked for exception in test1 use except_test1 SQL3602W 데이터점검처리시제한조건위반이발견되어예외테이블로 이동시켰습니다. SQLSTATE=01603 $ db2 select * from except_test1 COL1 COL 참조무결성 두테이블이 primary key 와 foreign key 로연결되어 foreign key 를가진데이터를추가, 변경하는경우데이터의참조무결성을유지하여야합니다. 2.1 부모키값과일치하지않는외부키값을하위테이블에 INSERT 하거나 UPDATE 하려고하면 SQL 문은실패하며모두 ROLLBACK 되어집니다. 2.2 부모테이블의 PRIMARY KEY 행을 DELETE 할경우 ON DELETE CASCADE PRIMARY KEY 행이삭제되면일치되는 FOREIGN KEY 의행도모두삭제됩니다. ex) $ db2 ʺ CREATE TABLE schema1.test1(a samllint, b char(10))ʺ $ db2 ʺ ALTER TABLE schema1.test1 ADD CONSTRAINT fk_test1 FOREIGN KEY(a) REFERENCES test ON DELETE CASCADEʺ 55

56 2.2.2 ON DELETE SET NULL PRIMARY KEY 행이삭제되면일치되는 FOREIGN KEY 의 NULL 입력가능컬럼이 NULL 로변경됩니다. ex) $ db2 ʺ CREATE TABLE schema1.test1(a samllint, b char(10))ʺ $ db2 ʺ ALTER TABLE schema1.test1 ADD CONSTRAINT fk_test1 FOREIGN KEY(a) REFERENCES test ON DELETE SET NULLʺ ( 참고 ) FK 에 NOT NULL 이적용되어있으면 ON DELETE SET NULL 옵션을줄수없다. SQL0629N 발생 ON DELETE NO ACTION PRIMARY KEY 행이삭제되면모든변경사항이롤백됩니다. 모든연쇄갱신및삭제가실행된후점검됩니다. DEFAULTL 값입니다. ex) $ db2 ʺ CREATE TABLE schema1.test1(a samllint, b char(10))ʺ $ db2 ʺ ALTER TABLE schema1.test1 ADD CONSTRAINT fk_test1 FOREIGN KEY(a) REFERENCES test ON DELETE NO ACTIONʺ ON DELETE RESTRICT NO ACTION 과마찬가지로상위행의삭제에실패하고모든변경사항이롤백됩니다. 그러나연쇄갱신및삭제가실행되기전에점검됩니다. ex) $ db2 ʺ CREATE TABLE schema1.test1(a samllint, b char(10))ʺ $ db2 ʺ ALTER TABLE schema1.test1 ADD CONSTRAINT fk_test1 FOREIGN KEY(a) REFERENCES test ON DELETE RESTRICTʺ 2.3 부모테이블의 PRIMARY KEY 행을 UPDATE 할경우 ON UPDATE NO ACTION UPDATE 후 NULL 이아닌외부키를가진모든하위행에일치하는상위행이반드시있어야합니다. 조건불충시 UPDATE 문은실패하고롤백됩니다.(UPDATE 문실행전에보유했던행과동일한상위행을보유할필요는없습니다.) DEFAULT 값입니다 ON UPDATE RESTRIC UPDATE 후 NULL 이아닌외부키를가진모든하위행에 UPDATE 문실행이전에보유했던행과일치하는상위행이반드시있어야합니다. 56

57 ( 참고 ) RI 관계에있는테이블에서 PK 컬럼이나 FK 컬럼을삭제했을경우 $ DB2 ʺCREATE TABLE test1(a smallint NOT NULL, b smallint, CONSTRAINT pk_t1_a PRIMARY KEY(a))ʺ $ DB2 ʺINSERT INTO test1 VALUES(1, 10)ʺ $ DB2 ʺINSERT INTO test1 VALUES(2, 20)ʺ $ DB2 ʺCREATE TABLE test2(c smallint NOT NULL, d smallint, CONSTRAINT fk_test2 FOREIGN KEY(c) REFERENCES TEST1)ʺ $ DB2 ʺINSERT INTO test2 VALUES(1, 100)ʺ $ DB2 ʺINSERT INTO test2 VALUES(2, 200)ʺ $ DB2 ʺALTER TABLE TEST1 DROP COLUMN aʺ 컬럼삭제 $ DB2 ʺ SELECT * FROM SYSCAT.KEYCOLUSE WHERE TABNAME=ʹTET1ʹ PK 조회 $ DB2 ʺSELECT * FROM SYSCAT.REFERENCES WHERE TABNAME=ʹTET2ʹ FK 조회 컬럼삭제와동시에 PRIMARY KEY, FOREIGN KEY 가모두삭제되어진다. $ DB2 ʺALTER TABLE test2 DROP COLUMN cʺ $ DB2 ʺ SELECT * FROM SYSCAT.KEYCOLUSE WHERE TABNAME=ʹTET1ʹ PK 조회 $ DB2 ʺSELECT * FROM SYSCAT.REFERENCES WHERE TABNAME=ʹTET2ʹ FK 조회 컬럼삭제와동시에 FOREIGN KEY 는삭제되어지고 PRIMARY KEY 는그대로남는다. ( 참고 ) 테이블, 컬럼및제한조건을설명하는시스템카탈로그테이블 카탈로그테이블 설명된오브젝트 TABLES 테이블및뷰 COLUMNS 테이블및뷰의컬럼 TABCONST 제한조건및적용되는테이블 CHECKS 점검제한조건 COLCHECKS 점검제한조건에참여하는컬럼 REFERENCES 참조무결성관계 ( 외부키제한조건 ) KEYCOLUSE 기본, 고유및외부키에참여하는컬럼 57

58 MAT007. Trigger Creation 1. TRIGGER 내에서만사용가능한특수 SQL 1.1 SET Before Trigger 의주목적은 insert, update 동작을수정하기위함인데이를위해서 set 절을사용합니다. set 절의효과는새로운행 ( 삽입이나갱신할행 ) 의값을등호오른쪽에있는값과바꾸는것입니다. ex) set deptno = aaa, bonus = 500 set newrow.perimeter = perimeter(newrow.parcel) set salary = (select min(salary) from emp where jobcode = newrow.jobcode) 1.2 SIGNAL Before Trigger, After Trigger 에서모두사용가능합니다. 사용목적은오류조건을일으키고 sql 문의효과를 rollback 하기위함입니다. 즉, signal 문이 trigger 내에서실행되면 triggering sql 은모두롤백되며 trigger 를실행한 application 은지정된 SQLSTATE 및 SQLCODE 를수신합니다. ex) SIGNAL SQLSTATE ( No such code ) SIGNAL SQLSTATE Z0000 ( No such code char(newrow.code)) ( 참고 ) SQLSTATE 선택시 IBM 또는 SQL92 표준에의해예약된값을사용할수없습니다. 2. Create Trigger 2.1 Before Trigger Insert, update, delete 문을실행하기전에먼저정의된작업들을실행합니다. ex) SAMLPE database 의 employee 테이블을이용하여 trigger 를만들어보도록하겠습니다. $ vi emp_trg1.sql Create Trigger emp_trg1 NO CASCADE BEFORE UPDATE ON employee REFERENCING OLD AS OLDROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL WHEN(newrow.comm > 1.5 * oldrow.comm) No cascade 는 before 트리거가다른트리거를활성화시키지않음을의미행이전변수선언, 행상태를식별하는상관이름지정. 트리거된조치가트리거랑 sql 조작에의해영향을받는테이블의각행에한번만적용갱신되는 comm 의크기가기존의값보다 50% 이상큰경우에도최대 50% 인상 58

59 SET newrow.comm = 1.5 * oldrow.comm; $ db2 stvf emp_trg1.sql $ db2 select empno, comm from employee where empno= EMPNO COMM $ db2 update employee set comm=20000 where empno= $ db2 select empno, comm from employee where empno=ʹ000010ʹ EMPNO COMM > 이아니라 50% 증가한값이들어감 ( 참고 ) NEW 상관이름과 NEW_TABLE 이름은 DELETE TRIGGER 에허용되지않습니다. OLD 상관이름및 OLD_TABLE 이름은 INSERT TRIGGER 에허용되지않습니다. OLD_TABLE 이름과 NEW_TABLE 이름은 BEFORE TIRGGER 에허용되지않습니다. 컬럼이 NOT NULL 로선언되고 BEFORE TIRGGER 가해당컬럼의 default 값을생성하는경우, 컬럼의정의시 DEFAULT 절도포함되어있어야합니다. 생략한경우, 행은테이블에삽입될수없게하고 trigger 는활성화되지않습니다. $ vi emp_trg2.sql Create Trigger emp_trg2 NO CASCADE BEFORE DELETE ON EMPLOYEE REFERENCING OLD AS OLDROW FOR EACH ROW MODE DB2SQL WHEN (OLDROW.JOB = ʹPRESʹ) SIGNAL SQLSTATE ʹD8888ʹ (ʹYou can not delete himʹ); JOB 이 PRES 인행삭제시에러를 발생시키며 ROLLBACK $ db2 stfv emp_trg2.sql $ db2 DELETE FROM employee WHERE JOB=ʹPRESʹ 59

60 2.2 After Trigger after trigger 는 sql 문과모든해당제한조건이정상적으로실행된후실행됩니다. after trigger 를실행하여다른 trigger 를활성화시킬수있습니다. 최대 16 레벨까지가능합니다. ex) SAMLPE database 의 employee 테이블을이용하여 trigger 를만들어보도록하겠습니다 $ vi emp_trg3.sql Create Trigger emp_trg3 AFTER UPDATE OF salary ON employee REFERENCING OLD AS OLDROW NEW AS NEWROW employee 테이블의 salary 컬럼이 update 된후트리거가작동합니다. FOR EACH ROW MODE DB2SQL WHEN(newrow.salary < oldrow.salary) SIGNAL SQLSTATE ʹZ7777ʹ (ʹsalary decrease for employeeʹ oldrow.empno); 새로의 salary 의값이기존값보다적으면에러를발생하고 rollback 됩니다. $ db2 stfv emp_trg3.sql $ db2 update employee set salary=0 where empno=ʹ000010ʹ DB21034E 명령이유효한명령행처리기명령이아니므로 SQL 문으로처리되었습니다. SQL 처리중에이명령은다음을리턴했습니다. SQL0438N 응용프로그램이진단텍스트 ʺsalary decrease for employee ʺ 과 ( 와 ) 함께오류를표시했습니다. SQLSTATE=Z7777 $ db2 create table temp_emp like employee $ db2 insert into temp_emp select * from employee $ vi emp_trg4.sql Create Trigger emp_trg4 AFTER UPDATE OF salary ON employee REFERENCING OLD AS OLDROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL WHEN (NEWROW.SALARY > OLDROW.SALARY) UPDATE temp_emp SET salary=newrow.salary where empno=maxrow.empno; 다른테이블과연관되어갱신시다른테이블을자동으로갱신한다. 60

61 $ db2 stvf emp_trg4.sql $ db2 update employee set salary= where empno=ʹ000010ʹ $ db2 select empno, salary from temp_emp where empno=ʹ000010ʹ EMPNO SALARY Insted Of Trigger view 를대상으로정의되어본질적으로갱신이불가능한 view 에대해삭제, 삽입또는갱신요청을수행할수있습니다. 이러한유형의 trigger 를이용하는응용프로그램은 view 가테이블인것처럼 view 에갱신조작을쓸수있습니다. ex) $ db2 create table emp1(firstname varchar(20) not null, sex char(1), varchar(40), hiredate date) $ db2 create view emp1_v as select firstname, sex, from emp1 $ emp_trg5.sql CREATE TRIGGER emp1_insert INSTEAD OF INSERT ON emp1_v REFERENCING NEW AS NEWROW FOR EACH ROW MODE DB2SQL INSERT INTO emp1_v VALUES(newrow.firstname, case upper(newrow.sex) when M then M when F then F ELSE NULL END, current date); INSERT 요청을수행할 insted of trigger 를생성 view 를통해서 table 에값을입력 $ db2 insert into emp1_v values(ʹkeumʹ,ʹmʹ,null) $ db2 insert into emp1_v values(ʹkimʹ,ʹbʹ,null) $ db2 insert into emp1_v values(ʹchoiʹ,ʹfʹ,null) $ db2 select * from emp1 FIRSTNAME SEX HIREDATE keum M keum@ibm.co.kr kim kim@ibm.co.kr

62 choi F choi@ibm.co.kr ( 참고 ) for each statement 옵션은사용할수없으며, 하나의뷰에대한 insert, update, delete trigger 는한개씩만생성이가능합니다. 62

63 MAT008. Identity Column Creation 1. IDENTITY COLUMN DB2 데이터베이스관리프로그램이테이블에삽입되는각행에대한숫자값을자동으로생성할수있는방법을제공하는컬럼. 식별컬럼은 AS IDENTITY 절로정의됩니다. 테이블에는한개의식별컬럼만있을수있습니다 1.1 주요옵션 GENERATED ALWAYS 엔진에의해자동적으로생성된값만허용, 사용자가명시적으로값입력을못함, 값입력시에러발생 ex) $ db2 insert into test1 values(1,ʹ1ʹ,ʹ1ʹ,1,ʹ1ʹ) DB21034E 명령이유효한명령행처리기명령이아니므로 SQL 문으로처리되었습니다. SQL 처리중에이명령은다음을리턴했습니다. SQL0798N GENERATED ALWAYS 로정의된컬럼 ʺDEPTNOʺ 에값을지정할수없습니다. SQLSTATE=428C GENERATED BY DEFAULT 사용자가값을지정하지않을경우에만엔진이자동적으로값을생성, 고유성보장못함 ex) $ DB2 CREATE TABLE TEST2 (COL1 SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10, INCREMENT BY 1, MAXVALUE 20), COL2 SMALLINT, COL3 SMALLINT, COL4 SMALLINT NOT NULL, COL5 CHAR(30)) $ DB2 ʺINSERT INTO TEST2(COL2, COL3, COL4, COL5) VALUES(1,1,1,ʹ1ʹ)ʺ $ DB2 ʺINSERT INTO TEST2(COL1, COL2, COL3, COL4, COL5) VALUES(15,1,1,1,ʹ1ʹ)ʺ $ DB2 ʺINSERT INTO TEST2(COL2, COL3, COL4, COL5) VALUES(1,1,1,ʹ2ʹ)ʺ $ DB2 ʺSELECT * FROM TEST2ʺ COL1 COL2 COL3 COL4 COL col1 15 가들어감 63

64 결과가고유하지못하고사용자가입력한값에따라서변동된다 START WITH 양수또는음수의 sequence 시작값을지정 INCREMENT BY 양수또는음수의증가값을지정 1.2 identity column 이있는테이블에 import 및 load 사용 modified by ~ 를이용하여 sequence 값을적재한다 import 할경우 identityignore, identitymissing option 을지원 identityignore 파일에서 import 된 sequence 값은무시되며, 기존의값에서 sequence 가증가되면서 Data 가 insert 됨 $ db2 CREATE TABLE TEST1 (COL1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10, INCREMENT BY 1), COL2 SMALLINT, COL3 SMALLINT, COL4 SMALLINT NOT NULL, COL5 CHAR(30)) $ db2 export to test1.del of del modified by coldel; chardelʹʹ select * from test1 $ vi Test1.del 10;1;11;111;ʹ100 ʹ 21;2;21;221;ʹ200 22;3;31;331;ʹ300 ʹ ʹ $ db2 import from test1.del of del modified by coldel; chardel identityignore insert into test1 $ db2 select * from test1 64

65 COL1 COL2 COL3 COL4 COL 번째행 col1 에 21 을넣었으나 sequence 가증가하며서 11 이입력되며 3 번째행도 22 을넣었으나 12 가입력됨 identitymissing export 된 sequence 값은무시되며, 기존의값에서 sequence 가증가되면서 data 가 insert 되며 indentity column 의값은다음컬럼으로넘어가서저장된다. 즉컬럼이밀리면서값이저장된다. $ db2 CREATE TABLE TEST1 (COL1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10, INCREMENT BY 1), COL2 SMALLINT, COL3 SMALLINT, COL4 SMALLINT NOT NULL, COL5 CHAR(30)) $ db2 export to test1.del of del modified by coldel; chardelʹʹ select * from test1 $ vi Test1.del 10;1;11;111;ʹ100 ʹ 21;2;21;221;ʹ200 22;3;31;331;ʹ300 ʹ ʹ $ db2 import from test1.del of del modified by coldel; chardel identityignore insert into test1 $ db2 select * from test1 COL1 COL2 COL3 COL4 COL

66 Col1 에 sequence 의값이순서대로들어가며 col2 부터 test1.del 에서읽어들인값들이들어간다. 만약 6 번째컬럼이있고데이터타입이동일하거나호환이되면 col5 의값이 6 번째컬럼으로들어간다. 데이터타입이호환되지않으면 NULL 값이입력되어진다 load identityignore, identitymissing, identityoverride option 을지원합니다. ( 참고 ) identityoverride 는 generated always 옵션에서만가능하므로주의바람니다 identityignore, identitymissing import 와동일하게작동 identityoverride 존재하는 sequence 값을무시하고파일에있는 sequence 값을적용하여데이터를적재 $ db2 CREATE TABLE TEST1 (COL1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10, INCREMENT BY 1), COL2 SMALLINT, COL3 SMALLINT, COL4 SMALLINT NOT NULL, COL5 CHAR(30)) $ db2 export to test1.del of del modified by coldel; chardelʹʹ select * from test1 Test1.del file 10;1;11;111;ʹ100 ʹ 11;2;21;221;ʹ200 12;3;31;331;ʹ300 ʹ ʹ $ db2 load from test1.del of del modified by coldel; chardel identityoverride insert into test1 $ db2 select * from test1 COL1 COL2 COL3 COL4 COL

67 > 이하새로입력된값 번째행 col1 의값에 33 이입력되어야하나무시하고 export 파일에있는값인 10 이입력됨 67

68 MAT009. Package Bind 1. Package 개요 패키지는단일소스파일에서특정 SQL 문을실행하는데필요한정보가포함된데이터베이스에저장된오브젝트입니다. 패키지는바인딩이작동되는소스파일에대해프리컴파일러를실행하거나, 나중에하나이상의바인드파일을이용하여바인더를실행함으로써작성됩니다. 데이터베이스응용프로그램은응용프로그램이컴파일되는것과같이성능향상을위해패키지를사용합니다. Notice. 소스프로그램의 Embedded SQL 이변경되었거나참조테이블의데이터량에많은변화가있는경우소스변경분반영혹은엑세스플랜변화반영을위해재프리컴파일을통해패키지를재작성해야할수도있습니다. 이번예제에서는이렇게이미작성된패키지의리바인드와데이터베이스유틸리티의패키지작성을위한바인드파일을바인드하는방법을기재합니다. 2. Package Rebind 데이터베이스이주시사용자응용프로그램및루틴의모든패키지는유효하지않은것으로표시됩니다. DB2 UDB 서버및새통계의변경사항을이용하려면유효성이확인되지않은패키지를리바인드해야합니다. 패키지가테이블, 보기, 별명, 인덱스, 트리거, 참조제한조건및테이블점검제한조건과같이삭제한데이터베이스오브젝트에종속적인경우패키지는유효하지않은상태가됩니다. UDF 를삭제할경우, 패키지는작동불능상태가됩니다. 패키지는데이터베이스를이주한후응용프로그램에서처음패키지를사용할때내재적으로리바인드됩니다. 이오버헤드를제거하기위해이주프로세스완료후 REBIND 명령또는 db2rbind 명령을실행하여유효하지않은패키지를리바인드할수있습니다. 작동불능패키지를명시적으로리바인드해야합니다. 유효한패키지와유효하지않은패키지모두리바인드 $db2rbind < 데이터베이스이름 > l < 로그로남길파일이름 > all 예 ) $db2rbind sample l sample_rebind.log all 3. 데이터베이스유틸리티 Package Bind 데이터베이스유틸리티 ( 임포트, 익스포트, REORG, 명령행처리기 ) 와 DB2 CLI 바인드파일을각데이터베이스에바인드해야그데이터베이스에서사용할수있습니다. DB UDB V9.5 버전부터는데이터베이스이주시에자동으로바인드하므로추가작업은필요없게되었지만유틸리티프로그램이변경되거나문제가있을경우아래와같이수동으로바인드할수있습니다. #su < 인스턴스유저 > $cd./sqllib/bnd $db2 connect to < 데이터베이스 Alias> $db2 grant publicʺ 68

69 $db2 grant public $db2 connect reset 예 ) #su db2inst1 $cd./sqllib/bnd $db2 connect to sample $db2 grant publicʺ $db2 grant public $db2 connect reset Notice. db2ubind.lst 파일에는데이터베이스유틸리티에대한패키지를작성하는데필요한바인드파일 (.bnd) 목록이포함되어있습니다. db2cli.lst 파일에는 DB2 CLI 와 DB2 ODBC 드라이버를위한패키지를작성하는데필요한바인드파일 (.bnd) 의목록이포함되어있습니다 69

70 MAT010. Sequence Operation 1. Sequence Operation 1.1 이미만들어진 Sequence 리스트조회하기 $db2 ʺselect * from syscat.sequencesʺ 1.2 Sequence 생성 $db2 ʺcreate sequence < 시퀀스이름 > as < 데이터타입 > start with < 시작값 > increment by < 증가폭 > [minvalue 속성 ] [maxvalue 속성 [cycle 속성 ]] [cache 속성 [order 속성 ]]ʺ 각속성 default 값 속성 Default 값 < 데이터타입 > Integer start with 1 increment by 1 minvalue no minvalue maxvalue 이값은 sequence 값의데이터형에따라서그데이터형의 한계값으로설정됩니다. 예를들어 sequence 생성시데이터 형을지정하지않으면기본형으로 integer 가선택되어 maxvalue 가 이되고데이터형을 bigint 로 설정하면 maxvalue 가 가됩니다. cycle no cycle cache cache 20 order no order 기본속성값을이용하여 sequence 생성 $db2 ʺcreate sequence test_seq1ʺ 데이터형을 bigint 로설정하여 sequence 생성 ( maxvalue 가자동확장됩니다. ) $db2 ʺcreate sequence test_seq1 as bigintʺ 시작값 1, 증가값 1, no maxvalue( 최대값에서도달해도되돌아와서값재활용하지않기 ), no cache( 메모리에 sequence 값을예약해놓지않고필요할때마다그때마다생성 ) 속성으로 sequence 생성 $db2 ʺcreate sequence test_seq1 start with 1 increment by 1 no maxvalueʺ 70

71 시작값 1, 증가값 1, no maxvalue( 최대값에서도달해도되돌아와서값재활용하지않기 ), cache 24( 메모리에 sequence 값 40 개미리생성하여예약해놓고사용 ) 속성으로 sequence 생성 $db2 ʺcreate sequence test_seq1 start with 1 increment by 1 no maxvalue cache 40ʺ 1.3 Sequence 속성값변경 sequence 의속성값을변경시킬때해당시퀀스가 cache 옵션을사용하고있다면, 속성값변경후 cache 된마지막값으로 sequence 값이변화하므로주의하여야합니다. cache 옵션을 no cache 로변경한후필요속성값을변경하고필요하다면다시 cache 속성을재지정하도록하여야 sequence 번호의결손을피할수있습니다. 물론, 이런사실을사전에미리알고있고필요속성변경후시퀀스값을재초기화를할계획이라면 no cache 변경없이계획대로하여도상관은없습니다. sequence 값을시퀀스생성시 start with 에서설정한초기값으로재초기화 $db2 ʺalter sequence < 시퀀스이름 > restartʺ 예 ) $db2 ʺalter sequence test_seq1 restartʺ sequence 값을특정한값으로재초기화 $db2 ʺalter sequence < 시퀀스이름 > restart with < 재초기값 >ʺ 예 ) $db2 ʺalter sequence test_seq1 restart with 103ʺ sequence 증가속성값 increamental 을변경 $db2 ʺalter sequence < 시퀀스이름 > increment by < 값 >ʺ 예 ) $db2 ʺalter sequence test_seq1 increment by 3ʺ sequence 가최대값에도달했을때초기값인 minvalues 을변경 $db2 ʺalter sequence < 시퀀스이름 > minvalue < 값 >ʺ 예 ) $db2 ʺalter sequence test_seq1 minvalue 5ʺ sequence 의최대값속성 maxvalue 값을변경 $db2 ʺalter sequence < 시퀀스이름 > maxvalue < 값 >ʺ 예 ) $db2 ʺalter sequence test_seq1 maxvalue 1000ʺ sequence 의최대값에도달했을때값재활용하도록속성 cycle 을변경 $db2 ʺalter sequence < 시퀀스이름 > cycleʺ 예 ) $db2 ʺalter sequence test_seq1 cycleʺ sequence 의메모리예약값속성 cache 값을 10 으로변경 71

72 $db2 ʺalter sequence < 시퀀스이름 > cache < 값 >ʺ 예 ) $db2 ʺalter sequence test_seq1 cache 10ʺ 1.4 Sequence 삭제 $db2 ʺdrop sequence < 시퀀스이름 >ʺ 예 ) $db2 ʺdrop sequence test_seq1ʺ 1.3 Sequence Value 조회 NEXTVAL 조회 $db2 ʺvalues nextval for < 시퀀스이름 >ʺ 예 ) $db2 ʺvalues nextval for test_seq1ʺ PREVVAL 조회반드시 nextval 을먼저조회한세션에한해서조회가가능하다. $db2 ʺvalues prevval for < 시퀀스이름 >ʺ 예 ) $db2 ʺvalues prevval for test_seq1ʺ 1.5 Sequence 를이용하여테이블에값입력하기 본예제에서는 nextval 을이용하여입력하는형태를취하였지만, 상황에따라 nextval 을조회하는쿼리를먼저수행하고 prevval 을이용해서입력하여도됩니다. $db2 ʺinsert into < 테이블이름 > values(nextval for < 시퀀스이름 >, <column 입력값 1>, <column 입력값 2>,..., <column 입력값 N>)ʺ 예 ) $db2 ʺinsert into test_table1 values(nextval for test_seq1, ʹhdsonʹ, ʹtest engineer1ʹ)ʺ 72

73 MAT011. db2look Utility Operation 1. db2look 1.1 개요 데이터베이스내오브젝트의생성문및레이아웃 ( 테이블스페이스, 버퍼풀생성문 ) 을추출하기위해 DB2 DBMS 는 db2look 유틸리티를기본제공합니다. 이도구를통하여개발테스트를위한더미데이터베이스를생성하거나기존 DB 전체혹은일부오브젝트의마이그레이션또는이식에사용할수있습니다. 1.2 기본명령구조 명령을수행하는옵션으로데이터베이스이름을지정하는필수옵션인 d 가있으며그외옵션들이단독혹은여러개로지정되어사용됩니다. 각옵션에대한설명은 Appendix A. db2look 옵션을참고하십시오. db2look d DBname [ e] [ xs] [ xdir Path] [ u Creator] [ z Schema] [ t Tname1 Tname2...TnameN] [ tw Tname] [ h] [ o Fname] [ a] [ m] [ c] [ r] [ l] [ x] [ xd] [ f] [ fd] [ td x] [ noview] [ i userid] [ w password] [ v Vname1 Vname2... VnameN] [ dp] [ ct] [ wrapper WrapperName] [ server ServerName] [ nofed] [ wlm] [ ap] [ wrapper WrapperName] [ server ServerName][ fedonly] [ nofed] 1.3 사용예제 Database Layout 및 DB 내존재하는모든 object ddl 추출하기 $db2look d < 데이터베이스이름혹은 Alias> e l o < 출력파일이름 > 예 ) $db2look d sample e l o sample_total.out Layout DDL 추출하기 $db2look d < 데이터베이스이름혹은 Alias> l o < 출력파일이름 > 예 ) $db2look d sample l o sample_layout.out 테이블 DDL 추출하기 테이블생성문, 인덱스생성문, 제약사항생성문, 트리거생성문, 권한부여문을추출하는것으로 t 옵션혹은 tw ( 와일드카드를쓸수있는옵션 ) 옵션을 73

74 사용하여추출하면테이블및테이블에관련된인덱스, 제약사항, 트리거, 뷰 DDL 들이모두같이추출됩니다. 가. 생성자 (definer) 가가진권한부여문 (grant) 을포함하여추출하기 xd 옵션을사용하여테이블을 define 한유저가가진 control 권한을추출할수있습니다. 테이블을생성한유저는기본적으로해당테이블의모든조작이가능한 control 특권을가집니다. 이와같이테이블을생성한유저와실제테이블의스키마가다를때동일한권한환경구현을위해 definer 의 control 권한부여문추출이필요할수있습니다. 단일테이블추출 $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > t < 테이블이름 > xd o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 t employee xd o sample_singtable.out 다중테이블추출 $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > t < 테이블이름 1> < 테이블이름 2>... < 테이블이름 N> xd o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 t employee sales department xd o sample_multitable.out 나. 생성자 (definer) 가가진권한부여문 (grant) 을포함하지않고추출하기 xd 옵션대신에 x 옵션을사용하여 definer 가가진 control 권한부여문은추출하지않을때사용합니다. 단일테이블추출 $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > t < 테이블이름 > x o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 t employee x o sample_singtable.out 다중테이블추출 $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > t < 테이블이름 1> < 테이블이름 2>... < 테이블이름 N> x o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 t employee sales department x o sample_multitable.out 다. 와일드카드를사용하여테이블 DDL 추출 ( 권한부여문은제외하고추출 ) 테이블이름을지정하기위해 t 옵션을사용하였으나와일드카드를사용할때에는 tw 옵션을사용합니다. 사용가능한와일드카드에는다중문자대체와일드카드인 % 와단일문자대체와일드카드인 _ 을사용할수있습니다. 74

75 다중문자대체와일드카드 % 사용 $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > tw < 와일드카드를사용한테이블이름 > o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 tw pro% o sample_multiwildcard.out 단일문자대체와일드카드 _ 사용 $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > tw < 와일드카드를사용한테이블이름 > o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 tw proj_ct o sample_singlewildcard.out 라. view 생성문은제외하고추출 ( 권한부여문제외, 와일드카드이용 ) db2look 명령문에 noview 옵션을추가하면 view 생성문은추출에서제외됩니다. $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > tw < 와일드카드를사용한테이블이름 > noview o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 tw proj_ct noview o sample_noview.out 뷰 DDL 만추출하기 v 옵션을이용하여 view 를대상으로특정 view 에대한 DDL 을추출할수있습니다. $db2look d < 데이터베이스이름혹은 Alias> e z < 스키마이름 > v < 뷰이름 > o < 출력파일이름 > 예 ) $db2look d sample e z db2inst1 v vproj o sample_view.out 통계정보추출하기 e 옵션을사용하지않고 t 혹은 tw 를사용하여테이블을지정하면해당테이블들의통계정보를추출할수있습니다. 추출된통계정보는타시스템에이식되어 access plan 을고정하거나테스트하는용도로사용될수있습니다. $db2look d < 데이터베이스이름혹은 Alias> z < 스키마이름 > tw < 와일드카드를사용한테이블이름 > o < 출력파일이름 > 예 ) $db2look d sample z db2inst1 tw proj_ct o sample_runstats.out 구성파라메터 ( 옵티마이저비용계산에관련한파라메터 ) 추출하기 f 와 fd 옵션을이용하여옵티마이저비용계산에관련한인스턴스, 데이터베이스, 레지스트리환경파라메터들을추출할수있습니다. 이렇게추출된파라메터를통계정보추출정보와같이사용하여 access plan 테스트에사용할수있습니다. 75

76 Notice. 아래 URL 을통해이와관련된더자세한내용을습득하실수있습니다 kapoor/ $db2look d < 데이터베이스이름혹은 Alias> f fd o < 출력파일이름 > 예 ) $db2look d sample f fd o sample_configure.out DROP 문을추가하기 dp 옵션을추가하면 CREATE 문장에앞서 DROP 문장이추가되도록추출됩니다.( 레이아웃및오브젝트 DDL 에공통적용되는사항입니다.) $db2look d < 데이터베이스이름혹은 Alias> e l dp o < 출력파일이름 > 예 ) $db2look d sample e l dp o sample_total_withdp.out federation 개체의 ddl 문장을제외하기 nofed 옵션을추가하면 federation 에관련된 server, wrapper, nickname, usermapping 등등 federation 관련오브젝트들의생성문들이제외됩니다. $db2look d < 데이터베이스이름혹은 Alias> e l nofed o < 출력파일이름 > 예 ) $db2look d sample e l nofed o sample_total_nofed.out 명령문종결문자바꾸어추출하기 추출된명령문들의기본문장종결문자는세미콜론 (;) 입니다. 그러나트리거나스토어드프로시저를추출하면서문장종결문자가다르게지정되어야하는경우나모든명령문종결문자를다른문자로바꾸어사용하려는경우에 td 옵션을사용하여문장종결문자를바꾸어줄수있습니다. $db2look d < 데이터베이스이름혹은 Alias> e l td < 문장종결문자 > o < 출력파일이름 > 예 ) $db2look d sample e l o sample_total_td.out $db2look d sample e l td! o sample_total_td.out TIP1. a 와 u 그리고 z 옵션의차이점오브젝트추출과관련하여오브젝트유저를지정함에있어쓸수있는옵션이 a,u,z 세가지가존재합니다. 다음은세옵션의정확한의미를설명합니다. a : 이옵션을사용하면모든작성자 ( definer : 오브젝트작성을했던유저 ) 와모든스키마이름이유저탐색범위에해당됩니다. 가령 schema1.employee 와 schema2.employee 가데이터베이스상에존재할때 a 옵션과 z emp% 를지정한다면두테이블모두검색범위에해당되어추출됩니다. u : 이옵션을사용하면실제로오브젝트를작성했던작성자만을기준으로유저탐색범위에해당됩니다. 가령 schema1 유저가테이블 schema1.table1 과 schema2.table2 라는테이블을생성하였다고가정할때 76

77 u schema1 옵션과 tw table% 를지정한다면두테이블모두검색범위에속하게되지만, u schema2 옵션과 tw table% 를지정한다면아무것도검색되지않습니다. 그이유는 schema2 유저로는어떤테이블도실제로생성하지않았기때문입니다. z : 이옵션을사용하면스키마이름이검색범위대상이됩니다. 정의자가누구이건스키마이름으로정의된오브젝트가검색됩니다. 가령 schema1 유저가테이블 schema1.table1 과 schema2.table2 라는테이블을생성하였다고가정할때 z schema1 옵션과 tw table% 를지정한다면 schema1.table1 이검색범위에속하게되어추출되며 z schema2 옵션과 tw table% 를지정한다면 schema2.table2 가검색범위에속하게되어추출된다는것입니다. TIP 2. u 옵션를명시하지않는다면? 유저를명시하지않으면환경변수 ${USER} 에해당하는변수가자동으로적용됩니다. 즉, 유닉스환경이라면현재로그인된유저이름이디폴트값이된다는의미입니다. Appendix A. db2look 옵션 ( DB2 V9.5 db2look 유틸리티기준 ) d 데이터베이스이름 : 반드시지정해야하는필수옵션입니다. e 데이터베이스중복을위해필요한 DDL 파일추출이옵션은 DDL UPDATE 문이포함된스크립트를생성합니다. 이스크립트는데이터베이스오브젝트를다시작성하기위해다른데이터베이스에대해수행될수있습니다. 이옵션은 m 옵션과의결합에사용할수있습니다. xs XSR 오브젝트익스포트및 DDL 문이포함된스크립트생성이스크립트는 XSR 오브젝트를재등록하기위해다른데이터베이스에대해실행될수있습니다. u, z 및 a 옵션을사용하여 XSR 오브젝트세트가지정됨 xdir 경로이름 : XSR 오브젝트가저장될디렉토리지정되지않은경우현재디렉토리가사용됩니다. xs 옵션이지정되지않은경우이옵션이무시됨 u 작성자 ID: u 와 a 모두지정되지않은경우, $USER 가사용됩니다. a 옵션이지정되지않은경우에는 u 옵션이무시됩니다. z 스키마이름 : z 및 a 가모두지정되면, z 는무시됩니다. 스키마이름이페더레이티드섹션에대해무시되었습니다. t 지정된테이블에대해통계생성지정될수있는최대테이블수는 30 개입니다. tw 이름이테이블이름의패턴기준 ( 와일드카드문자 ) 과일치하는테이블의 DDL 생성 tw 가지정된경우 t 옵션이무시됨 ap AUDIT USING 문생성 wlm WLM 특정 DDL 문생성여기에는막대그래프, 서비스클래스, 워크로드, 임계값, 작업클래스세트, 작업조치세트및 WLM 이벤트모니터에대한 DDL 이포함됩니다. e 옵션과 x/xd 옵션과같이사용할수있습니다. v 뷰전용 DDL 을생성하십시오. 이옵션은 t 가지정되면무시됩니다. dp CREATE 문이전에 DROP 문생성 h 추가세부사항도움말메시지 o 제공된파일이름으로출력경로재지정 o 옵션이지정되지않은경우, 출력이 stdout 으로갑니다. a 모든작성자에대한통계생성이옵션이지정되지않은경우, u 옵션이무시됩니다. 77

78 m 가상갱신모드에서 db2look 유틸리티실행이옵션은 SQL UPDATE 문이포함된스크립트를생성합니다. 이 SQL UPDATE 문은모든통계를캡처합니다. 이스크립트는원본을복사하기위해다른데이터베이스에대해실행될수있습니다. m 옵션이지정된경우, p, g 및 s 옵션이무시됩니다. c: 가상갱신을위해 COMMIT 문생성안함 m 이나 e 가지정되지않은경우, 이옵션이무시됩니다. CONNECT 및 CONNECT RESET 문도또한생성되지않습니다. COMMIT 이생략되었습니다. 명시적커미트가스크립트실행후에요청되었습니다. r: 가상갱신을위해 RUNSTATS 문생성안함디폴트값은 RUNSTATS 입니다. 이옵션은 m 이지정된경우에만유효합니다. l 데이터베이스레이아웃생성 : 데이터베이스파티션그룹, 버퍼풀및테이블스페이스 x 이옵션을지정하면, db2look 유틸리티가권한부여 DDL 을생성합니다. 기존의권한이부여된특권의경우, 오브젝트의원래정의자를제외 xd 이옵션을지정하면, db2look 유틸리티가권한부여 DDL 을생성합니다. 기존의권한이부여된특권의경우, 오브젝트의원래정의자를포함 f 구성매개변수및환경변수추출이옵션을지정하면, wrapper 및 server 옵션이무시됩니다. fd 기타 cfg 및 env 매개변수와함께 opt_buffpage 및 opt_sortheap 에대한 db2fopt 문을생성하십시오. td x 를명령문분리문자로지정합니다 ( 디폴트값은세미콜론 (;) 임 ). 트리거또는 SQL 루틴이존재할경우, e 옵션과함께사용되어야합니다. i 데이터베이스가상주하는서버로로그온하기위한사용자 ID w 데이터베이스가상주하는서버로로그온하기위한암호 noview CREATE VIEW DDL 문생성안함 wrapper 이랩퍼에적용되는페더레이티드오브젝트에대한 DDL 을생성합니다. 생성된오브젝트는다음을포함할수도있습니다. 랩퍼, 서버, 사용자맵핑, 별칭, 유형맵핑, 함수템플리트, 함수맵핑및인덱스스펙 server 이서버에적용되는페더레이티드오브젝트에대한 DDL 을생성합니다. 생성된오브젝트는다음을포함할수도있습니다. 랩퍼, 서버, 사용자맵핑, 별칭, 유형맵핑, 함수템플리트, 함수맵핑및인덱스스펙 fedonly 페더레이티드관련 OBJECT DDL 생성 nofed 페더레이티드 DDL 생성안함이옵션을지정하면, wrapper 및 server 옵션이무시됩니다. ct 오브젝트작성시간별로 DDL 문생성 78

79 MAT012. Explain SQL 1. Explain 도구 1.1 Explain 테이블 $ db2 ʺexplain plan for select * from staffʺ > EXPLAIN_INSTANCE,, exp 관련테이블에저장 $ db2 ʺexplain plan set queryno=33 for select * from deptʺ > explain_statement.queryno 에 33 이저장 $ db2 ʺexplain plan set querytag=ʹtestʹ for select * from deptʺ > explain_statement.querytag 에 test 가저장 $ db2 ʺexplain plan set queryno=33 set querytag=ʹtestʹ for select * from deptʺ 1.2 db2exfmt $ db2 set current explain snapshot=explain $ db2 ʺselect * from staffʺ $ db2exfmt d sample g TIC w 1 n % s % # O t 예 ) $ db2 set current explain snapshot=explain DB20000I The SQL command completed successfully. [db2inst1*test.ibm.com, /app/db2inst1] $ db2 ʺselect * from staffʺ SQL0217W The statement was not executed as only Explain information requests are being processed. SQLSTATE=01604 [db2inst1*test.ibm.com, /app/db2inst1] $ db2exfmt d sample g TIC w 1 n % s % # O t 이하일부생략 Original Statement: select * from staff Optimized Statement: SELECT Q1.ʺIDʺ AS ʺIDʺ, Q1.ʺNAMEʺ AS ʺNAMEʺ, Q1.ʺDEPTʺ AS ʺDEPTʺ, Q1.ʺJOBʺ AS ʺJOBʺ, Q1.ʺYEARSʺ AS ʺYEARSʺ, Q1.ʺSALARYʺ AS ʺSALARYʺ, Q1.ʺCOMMʺ AS ʺCOMMʺ 79

80 FROM DB2INST1.STAFF AS Q1 Access Plan: Total Cost: Query Degree: 1 NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT) NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT) Executing Connect Reset Connect Reset was Successful. * 정적 sql 문의예 $ db2 pref select64.sqc EXPLAIN YES $ db2exfmt d sample g TIC w 1 n % s % # 0 t 1.3 db2expln * 직접 Query 를입력 $ db2expln d sample q ʺselect * from staff where dept=1ʺ t g i $ db2expln d sample q ʺselect * from staff where dept=1ʺ t g I o out.txt * Query 대신파일을입력으로. $ db2expln d sample f < 파일명 > z ʺ;ʺ o out.txt g i * 정적 SQL 문인경우 $ db2expln d < 데이터베이스명 > c < 스키마명 > p < 패키지명 > s < 섹션번호 > $ db2 connect to sample $ db2 prep select64.sqc $ db2 list packages $ db2expln d sample c db2inst1 p select64 s 0 t g 예 ) $ db2expln d sample q ʺselect * from staff where dept=1ʺ t g i..... 일부결과생략 ********* DYNAMIC ************** ========= STATEMENT ============ Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Intra Partition Parallel = No SQLPath=ʺSYSIBMʺ,ʺSYSFUNʺ,ʺSYSPROCʺ,ʺSYSIBMADMʺ,ʺDB2INST1ʺ 80

81 Statement: select * from staff where dept=1 Section Code Page = 970 Estimated Cost = Estimated Cardinality = ( 2) Access Table Name = DB2INST1.STAFF ID = 2,15 Index Scan: Name = DB2INST1.STAFF_I ID = 1 Regular Index (Not Clustered) Index Columns: 1: DEPT (Ascending) #Columns = 7 #Key Columns = 1 Start Key: Inclusive Value 1: 1 Stop Key: Inclusive Value 1: 1 Data Prefetch: Eligible 0 Index Prefetch: None Lock Intents Table: Intent Share Row : Next Key Share Sargable Predicate(s) ( 2) Return Data to Application #Columns = 7 ( 1) Return Data Completion End of section Optimizer Plan: RETURN ( 1) FETCH ( 2) / \ IXSCAN Table: ( 2) DB2INST1 STAFF Index: DB2INST1 STAFF_I $ db2 list packages Total Package Schema Version sections Valid 81

82 P DB2INST1 15 N P DB2INST1 1 Y 2 record(s) selected. $ db2expln d sample c db2inst1 p P s 1 t g SECTION Section = 1 Statement: insert into A values(default) Estimated Cost = Estimated Cardinality = Insert: Table Name = DB2INST1.A ID = 3,261 End of section Optimizer Plan: INSERT ( 2) Table: DB2INST1 A 2. 준비 explain 을사용하기위한세팅. db 생성시 explain 으로시작하는테이블은존재하지않음 db2 tvf < 인스턴스홈 >/sqllib/misc/explain.ddl 테이블 : EXPLAIN_INSTANCE, EXPLAIN_STATEMENT, EXPLAIN_ARGUMENT, EXPLAIN_OBJECT, EXPLAIN_OPERATOR, EXPLAIN_PREDICATE, EXPLAIN_STREAM delete from EXPLAIN_INSTANCE 를하면모든 exp 테이블의내용이삭제됨 2.1 세션에 explain 걸기 db2 set current explain snapshot=explain : 이후에실행되는모든 sql 에 explain 테이블 (EXPLAIN_INSTANCE) 에기록, 그러나 sql 결과는보여주지않고다음메시지만보여준다. SQL0217W Explain 정보요청만처리중이므로명령문이실행되지않았습니다. 82

83 SQLSTATE=01604 db2 set current explain snapshot=yes : 이후에실행되는모든 sql 에 explain, 그리고 sql 결과를보여준다. db2 set current explain snapshot=no : explain 을 off static sql 을위한 explain snapshot : bind < 파일 > explsnap yes * 제어센터에서 History 보기 > 3. Visual Explain 예 ) 3.1 준비 $ db2sampl v8 (8 버전의 sample 을만듬 ) $ db2 tvf $HOME/sqllib/misc/ EXPLAIN.DDL (explain 테이블생성 ) 3.2 Explain Query Step1. Explain 쿼리 83

84 Step2. 쿼리작성 Step3. 실행 84

85 Step4. 사각형 DB2INST1.STAFF 더블클릭 아래는 Staff 통계정보가없다는것을의미 db2 ʺrunstats on table db2inst1.staffʺ db2 ʺrunstats on table db2inst1.orgʺ 후재실행 85

86 staff table 이 35 의 row 를가지고통계정보를 1.26 오후 10:57 분에실행, 실제공간은 1 page 를차지 3.3 상세정보보기 (8 각형더블클릭 ) 86

87 * 최적화된 Query * Query Optimizer Class $ db2 get db cfg grep i queryopt 디폴트쿼리최적화클래스 (DFT_QUERYOPT) = 5 87

88 4. View 4.1 Explain 결과 select queryno,querytag,explain_time,varchar(source_name,12) source,varchar(explain_level,1) lv,varchar(statement_type,1) stmt_type,varchar(updatable,1) upd,varchar(deletable,1) del,cast(total_cost as decimal(4,1)) cost,statement_text text from EXPLAIN_STATEMENT where queryno=33 explain_level : O( 사용자 Query), P(DB2 가생성한 Query) 4.2 Access 하는 Object 통계정보 select varchar(object_name,15) object_name,object_type,statistics_time,column_count,row_count,width,pages,overhead,prefetchsize,tablespace_name from EXPLAIN_OBJECT where explain_time=ʹ ʹ 4.3 단계별비용 select operator_id,operator_type,cast(total_cost as decimal(5,1)) ottal_cost,io_cost,cpu_cost,cast(first_row_cost as decimal(10,1)) first_row_cost,cast(re_total_cost as decimal(10,1)) re_total_cost,cast(re_cpu_cost as decimal(10,1)) re_cpu_cost,buffers from EXPLAIN_OPERATOR where explain_time=ʹ ʹ; 88

89 89

90 MAT013. Data Export 1. 개요 export 유틸리티는 DB2 테이블에서파일로자료를이동시키는데사용되는유틸리티로서여러파일형식중하나를사용하여데이터베이스에서데이터를발췌하여파일에저장합니다. 발췌할데이터는 SQL 쿼리로지정됩니다 export 유틸리티를사용하려면데이터가익스포트되고있는 table 또는 view 각각에대한 SYSADM 또는 DBADM 권한, CONTROL 또는 SELECT 특권이있어야합니다. 데이터를 DEL 파일로 export 할때행, 컬럼및문자열분리문자와소수점및더하기부호에사용할문자를지정할수있습니다. 데이터를 IXF 파일로 export 할때 export 하는데이터에컬럼이름을지정할수있습니다. 또한 export 프로세스중에생성된메시지를저장하는파일을지정할수도있습니다. IXF 유형의파일로데이터를저장하면데이터와컬럼의속성정보가함께저장되므로동일한구조의테이블을생성하고데이터도함께입력할때이용됩니다. 파일을지정하지않으면메시지는표준출력으로표시됩니다. 유틸리티가 COMMIT 문을발행하므로, 익스포트를호출하기전에 COMMIT 또는 ROLLBACK 을수행하여모든트랜잭션을완료하고모든잠금을해제해야합니다. 2. export 를위한필요사항 반출될자료를지정하는 SQL SELECT 문 반출된자료를저장할운영체제파일의경로와이름 입력파일에있는자료의형식. 형식은 IXF, WSF 또는 DEL 메세지파일명 반출조작을조정하기위한추가옵션 (modified by) ( 참고사항 ) export, import, load 에서지원되는파일유형요약 컬럼식별자가있는 ASCII(DEL) 컬럼식별자가없는 ASCII(ASC) 통합교환형식 (IXF) Expot Yes No Yes Yes Import Yes Yes Yes Yes Load No Yes Yes No 워크시트파일 (WSF) DEL(Delimited ASCII) : 다양한데이터베이스관리프로그램및파일관리프로그램간의데이터교환을위한칼럼식별자가있는 ASCII 로써사용자가파일내용을확인할수있습니다. IXF(Integrated Exchange Format) : 통합교환형식 (IXF) 의 PC 버전. 데이터베이스관리프로그램내에서데이터교환을위해선호되는방법이다. IXF 형식으로 export 받은경우, data import 시테이블을생성시킬수있으며사용자가파일내용을확인할수없습니다. 90

91 3. export command EXPORT TO filename OF filetype MODIFIED BY filetype mod MESSAGES messagefile select statement 3.1 to filename 데이터가 export 될파일의이름을지정, 경로가올바르지않을경우현재디렉토리에저장합니다. 3.2 of filetype 출력파일의형식을지정 (del, ixf, wsf) 3.3 MODIFIED BY filetype mod 파일유형수정자옵션을지정, 각 modifier 에대한정보는 oc/doc/r htm 를참조하시기바랍니다. ( 참고 ) 시간소인의경우월 (month) 및분 (minute) 모두영문자 M 을사용하기때문에주의해서사용해야합니다. 모호할경우 error 가발생하면서조작이실패하게됩니다. 3.4 METHOD N column name 출력파일에사용할하나이상의컬럼이름을지정합니다. 지정하지않으면테이블에있는컬럼명을그대로사용하며 WSF, IXF 파일에만유효합니다. 3.5 MESSAGES message file export 중발생하는경고및오류메시지가저장될위치를지정합니다. 지정하지않으면표준출력으로작성됩니다. 3.6 LOB TO lob path LOB 파일이저장될디렉토리에대한경로를하나이상지정합니다. 3.7 LOBFILE filename LOB 파일에대한하나이상의기본파일이름을지정합니다. 처음설정된파일공간이모두사용되면그다음지정한파일이사용되어집니다. 91

92 4. 여러경우의 export 문 4.1 일반적인 example $ db2 ʺexport to /export/test1.del of del modified by chardelʹʹ coldel; messages /export/msg/testmsg1.msg select * from schema1.test where test_day > with urʺ 4.2 lob file 을 del 로 export example $ db2 export to /export/test2.del of del lobs to /export/mylobs/ lobfile lobs1, lobs2 modified by lobsinfile select * from test1 $ db2 export to /export/test2.del of del lobs to /export/mylobs/, /export/mylobs2/ modified by lobsinfile select * from test1 일반 export 문과같이실행하여도실행은되나 data 가 default 문자데이터크기로절단되기때문에데이터가완벽하지않습니다. lobs to, lobfile, modified by lobsinfile 를사용하는것이좋습니다. LOB 파일을 export 하는경우이름지정규칙 : 버전 V9.1 에서 export 된 LOB 파일의확장자는.lob 입니다 ( 예 : filename.001.lob, filename.002.lob). 디폴트이름은입력데이터파일이름으로지정됩니다 ( 예 : <datafile>.001.lob, <datafile>.002.lob). 입력데이터파일이 DB2 UDB V8 에서생성된경우 DB2 V9.1 IMPORT 유틸리티로이파일을올바르게읽을수있습니다 V9.1 에서는 lob export 시 lobs to or lobfile 을지정하면자동으로 LOBSINFILE 이지정됩니다. V8 에서는지정하지않으면 lobs to or lobfile 옵션이무시됩니다. 4.3 사용자정의시간소인형식이포함된데이터 export sample $ db2 export to /export/test3.del of del modified by timestampformat=ʺyyyy.mm.dd hh:mm ttʺ select * from test1 4.4 export 된모든 10 진수컬럼에서앞에있는 0 을제거 (striplzeros) $ db2 export to /export/test4.del of del modified by STRIPLZEROS select * from test1 ( 참고사항 ) export 시작하기전에모든테이블조작을완료하고모든잠금을해제해야합니다. select 문에서테이블별명을사용할수있습니다. 255 자이상의문자컬럼을 del 형식파일로 export 하면경고가발생합니다. IXF 형식을사용하는것이좋습니다. DBEDW 는 DEL 형식으로, DBISW, DBBSS, DBCCS 는 IXF 형식으로 export 받는것이좋습니다. 92

93 MAT014. Data Import 1. 개요 임포트유틸리티는지원되는파일형식중하나를사용하여파일에서데이터베이스로데이터를삽입합니다. 파일의데이터는 SQL INSERT 문을사용하여한번에한행이처리됩니다. 목표테이블과연관된제한조건과트리거는임포트프로세스중에유효한상태로남아있습니다. 행을삽입하지못한경우 (ex 행이제한조건을위반 ), 임포트유틸리티는실패한행의행번호를나타내는메시지를생성한후나머지행의삽입을진행합니다. 임포트프로세스를끝낼때삽입된행수와실패한행수를요약하는메시지가생성됩니다. 그런다음임포트유틸리티는트랜잭션을커미트합니다. 2. import 를위한필요사항 입력파일의경로와이름 목표테이블또는뷰의이름이나별명 입력파일에있는자료의형식 기존자료가입력또는갱신되거나대체되는지의여부 (insert/replace) 메시지파일명 (messages) 자료를반입하기위해사용하는방법 : 칼럼위치 (location), 칼럼명또는상대칼럼위치 (position) 테이블에대한변경사항을확약하기전에반입하기위한행의수 (commitcount) 반입조작을시작하기전에건너뛰고시작할파일레코드의수 (restartcount) 자료가삽입될테이블에있는칼럼명 3. identity column import 3.1 generated always 인경우 입력파일에해당행에식별컬럼에대한값이누락되었거나, null 값이명시적으로지정될때마다테이블행에대한 sequence 가생성됩니다. NULL 이아닌값이지정되면행이거부됩니다. 보다자세한사항은 identity column creation 을참조하기바랍니다 generated by default 인경우 sequence 값이제공되면그값을사용합니다. 데이터가누락되거나명시적으로 NULL 인경우값을생성합니다. 보다자세한사항은 identity column creation 을참조하기바랍니다. 93

94 3.3 identitymissing 을사용 ex) create table table1 (c1 char(30), c2 int generated by default as identity, c3 real, c4 char(1)) 다음파일을 import 하려고할때 $ vi import.del Robert, 45.2, J Mike, 76.9, K Leo, 23.4, I $ db2 import from import.del of del replace into table1 (c1, c3, c4) $ db2 import from import.del of del modified by identitymissing replace into table1 두식모두같은결과를보여준다. 경우에따라서편한것을사용한다. 3.4 identityignore 를사용 ex) vi import.del Robert, 1, 45.2, J Mike, 2, 76.9, K Leo, 3, 23.4, I $ db2 import from import.del of del method P(1, 3, 4) replace into table1 (c1, c3, c4) $ db2 import from import.del of del modified by identityignore replace into table1 두식모두같은결과를보여준다. 경우에따라서편한것을사용한다. 4. import 시 table lock 4.1 ALLOW WRITE ACCESS(online mode) 목표테이블에 IX lock 을획득합니다. 온라인등록정보를보호하고교착상태가발생하는것을줄이기위해, X 테이블잠금으로에스컬레이션하기전에정기적으로현재트랜잭션을커밋하고모든행잠금을해제한다. 그결과 commitcount 옵션이사용되지않은경우에도커밋이수행될수있다. commitconut 값을명시적으로 0 으로지정해야커밋이수행되지않는다. 94

95 4.2 ALLOW NO ACCESS(offline mode) default 값입니다. import 할때기존의잠금을배타적잠금 (X lock) 으로에스컬레이션합니다. 5. Import Command IMPORT FROM filename OF filetype MODIFIED BY filetype mod METHOD L(column startcolumn end) N(column name) P(column position) COMMITCOUNT n RESTARTCOUNT n MESSAGES message file INSERT INTO table name (insert column) INSERT_UPDATE REPLACE REPLACE_CREATE CREATE 5.1 ALLOW NO ACCESS 오프라인모드에서임포트실행, default 값 5.2 ALLOW WRITE ACCESS 온라인모드에서임포트실행 COMMITCOUNT n/automatic 매 n 개레코드가임포트된후에 COMMIT 을수행합니다. AUTOMATIC 이지정되면 COMMIT 시기를내부적으로판별합니다. ( 참고 ) ALLOW WRITE ACCESS 옵션이지정되고 COMMITCOUNT 옵션이지정되지않은경우 COMMIT AUTOMATIC 이지정된것과같이 COMMIT 을수행하니참고바랍니다. 5.3 FROM file name 임포트할데이터를포함하는파일을지정합니다. 경로가생략되면현재작업디렉토리를사용합니다. 5.4 IN tablespace name 테이블이작성될테이블스페이스를식별합니다. REGULAR 테이블스페이스가존재해야하며다른테이블스페이스가지정되지않은경우모든테이블부분이이곳에저장됩니다. 지정되지않을경우권한부여 ID 가작성한테이블 95

96 스페이스에작성되며테이블스페이스가발견되지않을시 USERSPACE1 에작성됩니다. 없을경우작성에실패합니다. 5.5 INDEX IN tablespace name 테이블에대한인덱스가작성될테이블스페이스를식별합니다. REGULAR 또는 REGULAR DMS 테이블스페이스여야합니다. 5.6 LONG IN tablespace name LOB 데이터유형의값이저장될테이블스페이스를식별합니다. LARGE DMS 테이블스페이스여야합니다. 5.7 INSERT 기존테이블데이터를변경하지않고임포트된데이터를테이블에추가합니다. 5.8 INSERT_UPDATE 기존행의기본키와일치하는새행으로기존행이갱신됩니다. 기존행의기본키와일치하지않는새행은삭제됩니다. 5.9 REPLACE 기존의내용이삭제되며임포트된데이터로대체됩니다. 인덱스와테이블정의는변경되지않습니다. ( 참고 ) DATALINK 컬럼이있는테이블에서는사용할수없습니다 CREATE(IXF 만해당 ) 목표테이블이존재하지않아야합니다. 목표테이블을작성한후데이터가새테이블에삽입됩니다 REPLACE_CREATE(IXF 만해당 ) 목표테이블이있는경우그내용이삭제되고임포트된데이터로대체되며, 테이블이없는경우테이블을생성한뒤새데이터가삽입됩니다. 인덱스도생성합니다. ( 참고 ) DATALINK 컬럼이있는테이블에서는사용할수없습니다 LOBS FROM lob path LOB 파일을저장하는하나이상의경로를지정합니다. 96

97 5.13 MESSAGES message file 임포트중발생하는경고및오류메시지가저장될파일위치를지정합니다 METHOD L > 데이터를임포트할시작및끝컬럼번호를지정합니다. 컬럼번호는 1 부터매겨집니다. ( 참고 ) ASC 파일에서만사용가능 N > 임포트할컬럼이름을지정합니다. ( 참고 ) IXF 파일에서만사용가능 P > 임포트될입력데이터필드의필드번호를지정합니다. ( 참고 ) IXF, DEL 파일에서사용가능합니다 MODIFIED BY filetype mod 파일유형수정자옵션을지정합니다. 자세한사항은 db.admin.doc/doc/r htm 참고바랍니다 RESTARTCOUNT n 임포트가 n+1 레코드에서시작도되록지정합니다. 첫번째 N 번째레코드는생략됩니다 ROWCOUNT n 임포트될실제레코드수 N 을지정합니다 WARNINGCONUT n n 경고후임포트조작을중지합니다. n 이 0 이거나지정되지않으면발생경고수에상관없이계속진행됩니다. 97

98 6. 여러경우의 IMPORT 6.1 일반적인 example $ db2 import from test1.del of del modified by coldel; usedefaults commitcount 100 restartcount 1000 messages import.msg insert into sch1.test 번째행부터임포트를하며 100 건마다 commit 을하고, 해당컬럼의값이데이터파일에없을경우 default 값을입력합니다. 6.2 테이블을생성하면서 import $ db2 IMPORT FROM tools.ixf OF IXF MESSAGES import.msg CREATE INTO shop.tools 6.3 테이블의내용을바꾸고자할때 $ db2 IMPORT FROM tools.asc OF ASC METHOD L(1 20, 21 26, 29 30, 33 51, 54 63, 66 73) MESSAGES import.msg REPLACE INTO shop.tools ( 참고 ) 데이터컬럼이있는파일에서문자의위치를정확하게지정해야한다. 6.4 lob 컬럼이있는데이터 import $ db2 IMPORT FROM pictures.ixf OF IXF MODIFIED BY LOBSINFILE MESSAGES import.msg INSERT_UPDATE INTO employees $ db2 IMPORT FROM test.del OF DEL LOBS FROM /MYLOB MODIFIED BY COLDEL; LOBSINFILE MESSAGES import.msg INSERT INTO employees 98

99 MAT015. Data Load 1. 개요 로드유틸리티는새로작성된테이블이나이미데이터가들어있는테이블에많은양의데이터를효율적으로이동시킬수있습니다. LOB 와 UDF 를포함하는대부분의데이터유형을처리할수있습니다. sql insert 를수행하는 IMPORT 와달리페이지를데이터베이스에직접기록하기때문에속도가빠릅니다. 트리거를시작하지않으며참조또는테이블제한조건점검을수행하지않습니다. 2. Load 를위한필요사항 입력파일, Named Pipe 또는장치의이름과경로 목표테이블의이름이나별명 입력파일에있는자료의형식. 형식은 DEL, ASC 또는 PC/IXF 입력자료가테이블에추가될것인지의여부또는테이블에이미있는자료를대체할것인지의여부 메세지파일명 자료를로드하기위해사용할방법 : 컬럼위치 (location), 컬럼명또는상대컬럼위치 유틸리티가일관성지점을설정하려는빈도. SAVECOUNT 매개변수를사용하여이값을지정 ( 이매개변수가지정되면, 로드재시작조작은처음부터시작하지않고마지막일관성지점에서시작 ) 자료가삽입될테이블컬럼의이름 로드프로세스중통계가수집될것인지의여부. 이옵션은로드조작이 REPLACE 모드로수행중일경우에만지원 3. load 단계 3.1 로드 데이터가테이블에작성되는단계로서. 인덱스키와테이블통계가수집됩니다. 추가된데이터는로그파일에기록되지않습니다. INSERT, REPLACE 2 가지의방법으로데이터를입력합니다. 3.2 빌드 테이블에정의된모든인덱스가리빌드됩니다. 3.3 삭제 기본키, 고유제한조건또는고유인덱스위반을발견한모든행은로드된테이블에서삭제되어예외테이블로이동합니다. 예외테이블이제공되지않은 99

100 경우, 고유성규칙을위반한행이경고메시지와함께폐기됩니다. 이단계중에고유성규칙만이점검되며, 외부키및점검제한조건은여전히점검되지않습니다. 3.4 인덱스복사 인덱스데이터는시스템임시테이블스페이스에서원래의테이블스페이스로복사됩니다. 이단계는 READ ACCESS 옵션을지정하여로드조작중시스템임시테이블스페이스를인덱스작성용으로지정한경우에만발생합니다. ( 참고 ) 로드는기존인덱스를처음부터리빌드하는반면임포트는기존인덱스를 incremental 갱신합니다. 따라서인덱스가정의된대형테이블에적은수의행을추가할때임포트가더좋은성능을구현할수있습니다. 4. INDEX BUILD 4.1 REBUILD 모든인덱스가리빌드됩니다. 4.2 INCREMENTAL 새테이터로인덱스가확장됩니다. 4.3 AUTOSELECT 로드유틸리티가 REBUILD, INCREMENTAL 중에서자동으로선택합니다. DEFAULT 값입니다. 4.4 DEFERRED 인덱스작성을하지않습니다.,INDEXREC 데이터베이스구성변수값에의해재생성시점이결정됩니다. ALLOW READ ACCESS 옵션과호환되지않습니다. 5. identity column load 5.1 generated always 인경우 입력파일에해당행에식별컬럼에대한값이누락되었거나, null 값이명시적으로지정될때마다테이블행에대한 sequence 가생성됩니다. NULL 이아닌값이지정되면행이거부됩니다 generated by default 인경우 sequence 값이제공되면그값을사용합니다. 데이터가누락되거나명시적으로 NULL 인경우값을생성합니다. 100

101 5.3 identitymissing 을사용 ex) $ db2 create table table1 (c1 varchar(30), c2 int generated by default as identity, c3 decimal(7,2), c4 char(1)) $ vi load.del Robert, 45.2, J Mike, 76.9, K Leo, 23.4, I 컬럼을명시적으로나열하여 load 를실행 db2 load from load.del of del replace into table1 (c1, c3, c4) identitymissing 을사용하여 load 를실행 $ db2 load from load.dell of del modified by identitymissing replace into table1 두식모두같은결과를보여준다. 경우에따라서편한것을사용한다. 5.4 identityignore 를사용 ex) vi import.del Robert, 1, 45.2, J Mike, 2, 76.9, K Leo, 3, 23.4, I 컬럼을명시적으로나열하여 load 를실행 $ db2 load from load.del of del method P(1, 3, 4) replace into table1 (c1, c3, c4) identityignore 를사용하여 load 를실행 $ db2 load from load.del of del modified by identityignore replace into table1 두식모두같은결과를보여준다. 경우에따라서편한것을사용한다. 5.5 identityoverride 를사용 IDENTITY COLUMN CREATION 참조 101

102 6. 점검보류상태 (check pending) 점검또는외부키제한조건이있는테이블에데이터를로드할때마다로드유틸리티는일부제한조건을점검하지않은데이터가들어있다는것을나타내기위해테이블을점검보류상태로둡니다. TABLES 라는카탈로그테이블의 STATUS 컬럼을검토하여특정테이블이점검보류상태인지알수있습니다. 특정테이블의경우, STATUS 값 N 은테이블이정상상태에있음을나타내며, STATUS 값 C 는테이블이점검보류상태에있음을나타냅니다. 세번째상태값 X 는뷰에만적용되며이때뷰는작동불능상태에있음을나타냅니다. 6.1 테이블상태보는쿼리 $ db2 SELECT status FROM syscat.tables WHERE tabschema = test1 AND tabname = test1 6.2 SET INTEGRITY 테이블이 check pending 상태인경우예외테이블과 set integrity 문을이용하여해결 $ db2 set integrity for table name immediate checked for exception in table name use exception table name 7. 백업보류상태 (backup pending) 아카이브로그상태에서로드를실행하면목표테이블이속한테이블스페이스가 backup pending 상태가됩니다. 순환로그상태에서는발생하지않습니다. copy 옵션과 nonrecoverable 옵션으로제어합니다. $ db2 load from test1.del of del insert into test1 COPY YES TO /load $ db2 load from test2.del of del insert into test2 COPY NO $ db2 load from test3.del of del insert into test3 NONRECOVERABLE ( 참고 ) COPY NO 사용시로드후백업을해주어야 backup pending 상태를해결할수있습니다. NONRECOVERABLE 옵션을설정하면 load 후 backup pending 상태가되지않지만 rollforward 복구시복구가 load 로추가한데이터는복구할수없습니다. COPY NO 옵션보다는 COPY YES 옵션을사용하는것이안정적입니다. 8. Load Command 8.1 ALLOW NO ACCESS 로드중독점 access 를위하여테이블을잠급니다. 테이블은 load in progress 로설정됩니다. 102

103 테이블에대한제약조건이있는경우 load in progress 및 check pending 상태로설정됩니다. 8.2 ALLOW WRITE ACCESS 공유모드로테이블을잠급니다. load in progress 및 read access 로설정됩니다. 8.3 CHECK PENDING CASCADE 사용자가로드된테이블의점검보류상태를즉시모든하위테이블로연쇄적으로진행할지결정합니다 IMMEDIATE 외부키제한조건에대한점검보류상태를모든하위테이블로즉시확장합니다 DEFERRED 로드된테이블만이점검보류상태에놓여있음을나타냅니다. 8.4 COPY NO 아카이브로그상태일경우테이블이상주하는테이블스페이스가백업보류상태에놓이도록지정합니다. 테이블스페이스백업이나전체데이터베이스백업이이루어질때까지백업보류상태에놓이게됩니다. 8.5 COPY YES 로드된데이터백업이저장되도록지정합니다. 8.6 FOR EXCEPTION table name 오류가발생한행이복사될예외테이블을지정합니다. 고유인덱스나기본키인덱스가잘못되어있는행이복사됩니다. 8.7 FROM file name 로드중인데이터를포함하는 SQL 문을참조하는파일을지정합니다. 8.8 INDEXING MODE 로드유틸리티가인덱스를리빌드하고점진적으로확장할지여부를결정합니다. 8.9 INSERT 기존테이블데이터를변경하지않고로드된데이터를테이블에추가합니다. 103

104 8.10 LOCK WITH FORCE 로딩을처리하는중테이블잠금을포함하여다양한잠금을확보합니다. 이옵션을사용하면목표테이블에서충돌잠금을보유하는다른응용프로그램을로드시강제로중단할수있습니다 LOBS FROM lob path 로드될 LOB 값을포함하는데이터파일에대한경로를나타냅니다 MESSAGES message file 로드조작중발생하는경고및오류메시지의오류메시지의목적지를지정합니다 METHOD L > 데이터를로드할시작및끝컬럼번호를지정합니다. 컬럼번호는 1 부터매겨집니다. ( 참고 ) ASC 파일에서만사용가능 N > 로드할컬럼이름을지정합니다. ( 참고 ) IXF 파일또는 CURSOR 에서사용가능 P > 로드될입력데이터필드의필드번호를지정합니다. ( 참고 ) IXF, DEL 파일, CURSOR 에서사용가능합니다 MODIFIED BY filetype mod 파일유형수정자옵션을지정합니다 REPLACE 테이블에서모든기존데이터를삭제한후데이터를삽입합니다 RESTART 이전에인터럽트된로드조작을재시작합니다 RESTARTCOUNT n 로드가 n+1 레코드에서시작되도록지정합니다. 첫번째 N 번째레코드는생략됩니다. 104

105 8.18 ROWCOUNT n 로드될실제레코드수 N 을지정합니다 SAVECOUNT n 로드유틸리티가모든 n 행다음에일관성지점을설정하도록지정합니다. 로드로 n 건을복사할때마다복사완료정보를보관합니다. 디폴트값은 0 입니다 TERMINATE 이전에인터럽트된로드조작을중지하고, 조작이시작되었던특정시점으로조작을롤백합니다. ( 참고 ) 만약 REPLACE 로작업을진행했을경우 TEMINATE 이후테이블은빈테이블이됩니다. INSERT 일경우에는모든레코드가보존됩니다 TEMPFILE PATH temp pathname 로드조작중임시파일을작성할때사용될경로이름을지정합니다 WARNINGCONUT n n 경고후로드조작을중지합니다. n 이 0 이거나지정되지않으면발생경고수에상관없이계속진행됩니다. 9. 여러경우의 LOAD 9.1 일반적인 example $ db2 load from /load/test.del of del modified by coldel; dumpfile=/dump/test.dump savocount 5000 rowcount warningcount 1000 messages /msg/test.msg insert into test1 for exception test1_exp test.dump 에는 data 의자료형식이맞지않아 Load 가거부되는데이터가저장되며 5000 건마다 savecount 를가지며로드후삭제단계에서지워지는데이터는 test1_exp 로저장됩니다. 9.2 파일로부터 lob 로드 $ db2 load from ascfile of asc lobs from /lobs modified by lobsinfile reclen=22 method L(1,3,5,10,17,22) insert into table1 9.3 delprioritychar 과 usedefaults 를이용한 load $ db2 v ʺload from test1.del of del modified by delprioritychar usedefaults 105

106 TIMESTAMPFORMAT=\ʺYYYY MM DD HH:MM:SS\ʺ warningcount 100 messages /msg/test.msg tempfiles path /tmp/ insert into test1ʺ 9. 5 병렬데이터베이스로 load $ db2 ʺload from test.del of del modified by usedefaults warningcount 500 messages /msg/test.msg tempfiles path /load/temp/ insert into test1 data buffer 1000 cpu_parallelism 1 partitioned db config mode partition_and_load partitioning_dbpartnums (10,11,12) 적재시 Data partitioning 과 load 를수행하며 Data 적재전 Data partitioning 을수행하는노드를 10, 11, 12 로지정. 10. LOAD QUERY 처리중로드조작의상태를점검하고테이블상태를리턴합니다. 로드가진행중이아니면테이블상태만리턴합니다. 로드를실행하고있는세션이아닌새로운세션에서실행합니다 load query command option TO local message file 로드조작중발생하는경고및오류메시지의목적지를지정합니다 NOSUMMARY 로드요약정보 ( 읽은행, 생략된행, 로드된행, 거부된행, 삭제된행, 커미트된행및경고수 ) 가보고되지않도록지정합니다 SHOWDEATIL load query 명령의최종호출이후발생된로드이벤트에대한새정보만보고되도록지정합니다 SUMMARYONLY 로드요약정보만보고되도록지정합나다 TABLE table name 데이터가현재로드중인테이블이름을지정합니다. 106

107 10.2 example $ db2 load query table test1 to /mymsg/test.tempmsg 11. CROSS LOADER cursor 를이용하여 export 를할필요없이바로 load 를할수있습니다. ex) $ db2 create table test1 like employee sample 데이터베이스의 employee 테이블을이용하여 load 할테이블을생성합니다. $ db2 ʺdeclare test_cursor cursor for select * from employee where job=ʹmanagerʹʺ 커서를정의합니다. $ db2 load from test_cursor of cursor messages /msg/test1.msg insert into test1 copy yes to /backup 파일이아닌커서를이용하여로드를합니다. $ db2 select * from test1 EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM MICHAEL L THOMPSON B MANAGER 18 M SALLY A KWAN C MANAGER 20 F JOHN B GEYER E MANAGER 16 M IRVING F STERN D MANAGER 16 M EVA D PULASKI D MANAGER 16 F EILEEN W HENDERSON E MANAGER 16 F THEODORE Q SPENSER E MANAGER 14 M

108 MAT016. Database Backup 1. Backup 1.1 Backup 기능 Local 또는 Remote 에있는데이터베이스를 Copy 해주는유틸리티입니다. 1.2 Backup 권한 SYSADM, SYSCTRL, SYSMAINT 1.3 Backup 제한사항 테이블스페이스백업조작및테이블스페이스리스토어조작은비록다른테이블스페이스에서실행되더라도동시에실행할수없습니다. DMS 테이블스페이스에대한온라인백업조작은다음의조작과호환되지않습니다. 로드 재구성 ( 온라인및오프라인 ) 테이블스페이스삭제 테이블절단 인덱스작성 처음에로그되지않음 (CREATE TABLE 및 ALTER TABLE 문과함께사용 ) 현재사용중인데이터베이스의오프라인백업은수행할경우오류가발생하기에오프라인백업실행전에 DEACTIVATE DATABASE 명령을수행해야합니다. $ db2 deactivate db db 명 1.4 Backup 레벨 Full Database Backup : 특정 Database 전체의이미지를 Backup 합니다. Tablespace Backup : 특정 Database 내의 1 개이상의 Tablespace 이미지를 Backup 합니다. 1.5 Backup 모드 On Line Backup : Share Mode 상태에서 Backup 실행합니다. Off Line Backup : Exclusive Mode 상태에서 Backup 실행합니다. 1.6 Backup 관련 Configuration Parameter LOGRETAIN (Database Configuration File) 복구를위한로그유지로기본값은 OFF 이다 ( 작동 : ON 또는 RECOVERY, 미작동 : OFF) LOGRETAIN 적용시는 Database 가 Archive Log 상태로적용합니다. 108

109 1.6.2 USEREXIT (Database Configuration File) 로깅에대한사용자나감프로그램작동으로기본값은 OFF 입니다 ( 작동 : ON, 미작동 : OFF) USEREXIT 적용시는 Database 가 Archive Log 상태로적용합니다 BACKBUFSZ (Database Manager Configuration File) Backup 을실행하는동안사용되는 Buffer 의크기로서기본값은 1024 Page 입니다 NUM_IOSERVERS (Database Configuration File) Backup 을실행하는동안 Data prefetch 나 Asynchronous I/O 를담당하는 I/O Server 의개수로서기본값은 3 입니다. 1.7 Backup 관련 Processor Buffer Manipulator(db2bm) : Database 로부터 Backup buffer 로 backup image 를 I/O 하는 processor 로서 Database 당한개씩존재합니다. Media I/O Controller : Backup Buffer 로부터 Tape 등의특정 Device 로 backup image 를 I/O 하는 processor 로서 Database 당한개씩존재합니다. * 백업수행시 (db2bm, db2med 검색 ) $ ps ef grep ʹdb2[bm][me]ʹ db2inst :26 pts/2 00:00:00 db2bm db2inst :37 pts/2 00:00:00 db2bm db2inst :37 pts/2 00:00:00 db2bm db2inst :37 pts/2 00:00:00 db2bm db2inst :45 pts/2 00:00:00 db2med Backup Image Backup Image 의예 : SAMPLE.0.DB2.NODE0000.CATN Backup Image 의순서대로아래에서설명합니다. Database alias : Backup 대상이되는 database alias 명을나타냅니다. Type: Backup type 으로 ʹ0ʹ 은 Full Database Backup, ʹ3ʹ 은 Tablespace Backup, ʹ4ʹ 는 Load Copy 를나타냅니다. Instance Name : Database 가속한 Instance 명을나타냅니다. Node Number : Backup Image 의해당 Node 번호입니다. Catalog Node Number : Catalog Image 가있는 Node 번호입니다. Timestamp : Backup 이실행된 Timestamp 값을표시합니다. Sequential Number : File 의 Extension 을나타내는일련번호입니다. 109

110 1.9 Backup 수행 Full Database Off Line Backup (Circular Logging 이거나 Archival) $ db2 backup database(db) sample(db 명 ) to /backup Database Full Backup 을실행합니다. TO 이하는 Backup Image 가생성되는경로를지정하며, TSM 장비로 Image 를받으려면 TO 대신에 ʹUSE TSM WITHOUT PROMPTINGʹ 을사용합니다 Full Database On Line Backup (Archival Logging 방식일때만가능 ) $ db2 backup database(db) sample(db 명 ) online to /backup Database 명다음에 ʹonlineʹ 을사용하여사용자가 Connect 상태를유지하더라도 Database 에대한 Backup 을실행하도록합니다. Database Full Backup 을실행합니다. TO 이하는 Backup Image 가생성되는경로를지정하며, TSM 장비로 Image 를받으려면 TO 대신에 ʹUSE TSM WITHOUT PROMPTINGʹ 을사용합니다 Tablespace Off Line $ db2 backup database(db) sample(db 명 ) tablespace(tablespace1, Tablespace2...) to /backup TO 이하는 Tablespace Backup Image 가생성되는경로를지정하며, TSM 장비로 Image 를받으려면 TO 대신에 ʹUSE TSM WITHOUT PROMPTINGʹ 을사용합니다 Tablespace On Line $ db2 backup database(db) sample(db 명 ) tablespace(tablespace1, Tablespace2...) online to /backup Tablespace 명다음에 ʹonlineʹ 을사용하여사용자가 Connect 상태를유지하더라도 Tablespace 에대한 Backup 을실행하도록합니다. TO 이하는 Tablespace Backup Image 가생성되는경로를지정하며, TSM 장비로 Image 를받으려면 TO 대신에 ʹUSE TSM WITHOUT PROMPTINGʹ 을사용합니다. 110

111 MAT017. Database Restore 1. Restore 1.1 Crash Recovery 기능 System Power failure 나 Application Error 발생등으로인해 Database 가불일치하는상태가된경우 Active Log 를다시적용함으로써 Database 의상태를일관성있게유지하는복구합니다 권한 특별한권한은필요없습니다 관련 Configuration Parameter AUTORESTART (Database Configuration File) Crash Recovery 가필요한경우자동적으로 Recovery 작업을실행하여기본값은 ʹONʹ 입니다. 1.2 Restore Recovery 기능 특정시간에생성된 Backup Image 를이용하여 Database 를 Backup 당시의 Image 와동일하게복구혹은새로생성해주는복구입니다. 권한 이미존재하는 Database 에대한복구 : SYSADM, SYSCTRL, SYSMAINT 새로생성하는 Database 에대한복구 : SYSADM, SYSCTRL Restore Level Full Database Restore : 특정 Database 전체의 Image 를 Restore Tablespace Restore : 특정 Database 내의 1 개이상의 Tablespace Image 를 Restore Recovery History file : 특정 Database 의 Recovery History file 만 Restore Restore Mode On Line Restore : Share Mode 상태에서 Restore 실행 Off Line Restore : Exclusive Mode 상태에서 Restore 실행 관련 Configuration Parameter RESTBUFSZ (Database Manager Configuration File) : Restore 를실행하는동안사용되는 Buffer 의크기로서기본값은 1024 Page 입니다. 111

112 NUM_IOSERVERS (Database Configuration File) : Restore 를실행하는동안 Data prefetch 나 Asynchronous I/O 를담당하는 I/O Server 의개수로서기본값은 Aucomatic 입니다 관련 Processor Buffer Manipulator : Restore buffer 로부터 Database 로 backup image 를 I/O 하는 processor 로서 Database 당 1 개씩존재합니다. Media I/O Controller : Tape 등의특정 Device 로부터 Restore Buffer 로 backup image 를 I/O 하는 processor 로서 Database 당한개씩존재합니다. 1.3 Rollforward Recovery 기능 특정시간에생성된 Backup Image 와 Active Log 를이용하여 Database 를 Backup 이후의특정기간이나가장최근의 Image 로복구혹은새로생성해주는복구 권한 이미존재하는 Database 에대한복구 : SYSADM, SYSCTRL, SYSMAINT 새로생성하는 Database 에대한복구 : SYSADM, SYSCTRL Rollforward Level Log 적용하지않음 > Log 를하나도적용하지않는방식으로 Circular Logging 방식에서 Off Line Full Database Backup image 를적용시키는경우나, Archival Logging 방식에서 Full Database Backup Image 를적용시 ʹwithout rolling forwardʹ 를적용시킨경우에해당됩니다. 특정시간까지적용 > Log 내용중특정시간까지의내용만을적용하는방법 가장최근 Log 까지적용 > 가장최근시점까지의 Log 내용을적용하는방법 1.4 Recovery History File 기능 특정 Database 에행한 Backup, Restore, Load 작업에대한정보를기록하는 File 권한 SYSADM, SYSCTRL, SYSMAINT, DBADM History File 의내용확인 $ db2 list history all for sample Recovery History File 의내용을확인합니다. $ db2 list history since for sample 특정시간까지적용, Log 의내용중특정시간이후의내용만확인합니다. 112

113 $ db2 list history containing userspace1 for db sample Database 내의특정 Tablespace 내용만확인합니다 관련 Configuration Parameter REC_HIS_RETENTION (Database Configuration File) > 복구실행기록보유일수 Recovery History File 의구조 예제 List History File for sample Number of matching file entries = 4 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID B D N O S LOG S LOG Contains 17 tablespace(s): SYSCATSPACE USERSPACE SYSTOOLSPACE USERSPACE2 Comment: DB2 BACKUP SAMPLE ONLINE Start Time: End Time: Status: A EID: 1639 Location: /backup/libubtbr.a 후략 History file 의구조설명 Column name Type 설명 OPERATION Char(1) 작업형태 : B = Backup R = Restore L = Load OBJECT Char(1) 작업범위 : D = Full Database P = Tablespace T = Table OBJECT_PART Char(17) 시간소인 + 순서 : Timestamp (14) + Sequence (3) OPTYPE Char(1) 유형 : F = Off Line Backup N = On Line Backup 113

114 R = Load Replace A = Load Append C = Load Copy = 기타 DEVICE_TYPE Char(1) 장치 (Dev) : D = Disk K =Diskette T = Tape A = ADSM U = User Exit O = Other FIRST_LOG Char(12) 최초로그 : Rollforward Recovery 에사용될최초로그 LAST_LOG Char(12) 현재로그 : Backup 당시마지막로그 BACKUP_ID Char(14) 백업 ID SCHEMA Char(8) Load 시사용되는 Table 이름의식별자 TABLE_NAME Char(18) Table 이름 NUM_TABLESPACE Char(3) Backup / Restore 시포함될 Tablespace 개수 LOCATION Char(255) Backup / Load Copy 의결과파일 COMMENT Char(30) 주석 1.5 Recovery 수행 Crash Recovery Database configuration Parameter 의 ʹAUTOSTARTʹ가 ʹONʹ인지확인합니다. $ db2 get db cfg for sample(db 명 ) grep AUTOSTART 자동재시작사용 (AUTORESTART) = ON) $ db2 restart database sample Database 의재시작을통해자체적으로 database 일관성을유지시키는 Crash Recovery 를실행합니다 Restore Recovery 현재의 Logging 상태가 Circular Logging 상태인지확인합니다. $ db2 restore database sample(db 명 ) from /backup Restore 명령어로 backup Image 가있는 /backup 폴더로부터읽어서 db 를갱신합니다. $ db2 restore database sample(db 명 ) use tsm without prompting Backup image 를 TSM 장비로부터읽어서 Database Restore 를실행합니다. $ db2 restore database sample(db 명 ) use tsm taken at without prompting 여러버전의 Backup Image 중특정 Timestamp 값을지정해서 TSM 장비로부터 Database Restore 를실행합니다. $ db2 restore database sample(db 명 ) from /backup to /db2/dbhome 114

115 목표 Database 디렉토리를지정하여 Database 를특정디렉토리에생성합니다. 기존에존재하는 Database 에대해 Restore 를하는경우생략해도무방합니다. $ db2 restore database sample(db 명 ) from /backup into TEST 목표데이터베이스의별명을지정함. 만약데이터베이스가존재하지않는다면 into 절에지정한새로운데이터베이스가작성됩니다 Rollforward Recovery 현재의 Logging 상태가 Archival Logging 인지확인합니다. Restore 명령을실행합니다. $ db2 restore database sample(db 명 ) from /backup without rolling forward without prompting Log 를전혀적용시키지않는경우로 Full Database Off Line Image 인경우에해당됩니다. $ db2 restore database sample(db 명 ) from /backup without prompting $ db2 rollforward database sample(db 명 ) to :30:00 and stop 특정 CPU 시간까지의 Log 만을적용시키는경우 Restore 명령으로 Backup Image 를적용을시키고, Log file 에대해서는 Rollforward 명령을수행합니다. $ db2 restore database sample(db 명 ) from /backup without prompting $ db2 rollforward database sample(db 명 ) to end of logs and stop 현재시점까지의 Log 를모두적용시키는경우 Restore 명령으로 Backup Image 를적용을시키고, Log file 에대해서는 Rollforward 명령을수행합니다. $ db2 restore databse sample(db 명 ) tablespace(tablespace1) use tsm without prompting $ db2 rollforward database sample(db 명 ) to end of logs and stop tablespace(tablespace1) Restore 명령으로 Tablespace Backup Image 를적용을시키고, Log file 에대한 Rollforward 명령을수행합니다. CPUTime : 현재시간에서 9 시간을빼야합니다. > CPU Time = Current Time 9 시간 115

116 MAT018. Split Mirror Creation 1. Split Mirror 1.1 Split Mirror 를 Backup Image 로사용 ( 동일서버에백업 ) Split Mirror 를 Backup Image 로사용하기위한단계 $ db2 connect to sample(db 명 ) $ db2 set write suspend for database 접속한데이터베이스에서입출력을일시중단합니다. Write suspend 가되었는지는 db2diag.log 의 MESSAGE 내용으로체크하면됩니다. 예 ) The table space ʺSYSCATSPACEʺ (ID ʺ0ʺ) has been placed in the WRITE_SUSPEND state $ db2 ʺselect * from sysibmadm.dbpathsʺ 적절한운영체제레벨의명령을사용하여기본데이터베이스에서미러를분할합니다 (flash copy 와같은 Utility 를사용하여매일변경분을적용합니다 ). 위의 DBPATHS 뷰를통해분할해야할데이터베이스의모든파일및디렉토리를복사합니다. $ db2 set write resume for database 기본데이터베이스에서입출력을재개합니다. Write resume 가되었는지는 db2diag.log 의 MESSAGE 내용으로체크하면됩니다. 예 ) The table space ʺSYSCATSPACEʺ (ID ʺ0ʺ) which was previously in the WRITE_SUSPEND state is no longer in that state. Write I/O has been resumed to the table space 시스템오류시 Split Mirror 를이용한 Restore 단계 $ db2stop 데이터베이스인스턴스를중지합니다. 운영체제레벨의명령을사용하여분할된데이터를기본시스템 ( 장애시스템 ) 에복사합니다. 로그와관련한모든로그 (1 차로그, 2 차로그, Archive 로그 ) 가필요하기에분할된로그파일은복사하지않습니다. $ db2start 데이터베이스인스턴스를시작합니다. $ db2inidb sample(db 명 ) as mirror 데이터베이스를초기화합니다. $ db2 rollforward db sample to isotime using local time and stop 로그의끝이나특정시점으로데이터베이스를 Rollforward 합니다. 116

117 1.2 Split Mirror 를대기데이터베이스로사용 ( 다른서버에백업 ) Split Mirror 를대기데이터베이스로사용하기위한단계 $ db2 connect to sample(db 명 ) $ db2 set write suspend for database 접속한데이터베이스에서입출력을일시중단합니다. Write suspend 가되었는지는 db2diag.log 의 MESSAGE 내용으로체크하면됩니다. 예 ) The table space ʺSYSCATSPACEʺ (ID ʺ0ʺ) has been placed in the WRITE_SUSPEND state $ db2 ʺselect * from sysibmadm.dbpathsʺ 적절한운영체제레벨의명령을사용하여기본데이터베이스에서미러를분할합니다 (flash copy 와같은 Utility 를사용하여매일변경분을적용합니다 ). 위의 DBPATHS 뷰를통해분할해야할데이터베이스의모든파일및디렉토리를복사합니다. $ db2 set write resume for database 기본데이터베이스에서입출력을재개합니다. Write resume 가되었는지는 db2diag.log 의 MESSAGE 내용으로체크하면됩니다. 예 ) The table space ʺSYSCATSPACEʺ (ID ʺ0ʺ) which was previously in the WRITE_SUSPEND state is no longer in that state. Write I/O has been resumed to the table space. $ db2 catalog db sample(db 명 ) as sample1(db 별명 ) on path(dbpath 에서위치한 db 위치 ) 보조시스템에서미러링된데이터베이스를카탈로그합니다. 단, 보조시스템에는 DB2 제품과인스턴스가설치가되어있어야합니다. $ db2inidb sample(db 명 ) as standby relocate using relocatedbcfg.txt db 명, path 등 Configuration 내용을바꿀경우에 db2inidb 명령에 RELOCATE USING 옵션을지정하여대기데이터베이스를재배치합니다. $ db2relocatedb f relocatedbcfg.txt db2inidb 대신에 db2relocatedb 를사용할수도있습니다. db2relocatedb 를사용하는경우엔 db2inidb db 명 as standby 를사용하지않아도됩니다. relocatedbcfg 파일구성의예 > 아래의내용과같이 db 명, path, 인스턴스명등을지정해서바꿀수있습니다. DB_NAME=TESTDB DB_PATH=/databases/TESTDB,/DB2 INSTANCE=db2inst1,newinst NODENUM=0 CONT_PATH=/Data/TS5_Cont0,/DB2/TESTDB/TS5_Cont0 CONT_PATH=/dev/rTS5_Cont1,/dev/rTESTDB_TS5_Cont1 117

118 동일한시스템에미러데이터베이스와기본데이터베이스가존재하는경우는 db2relocatedb 유틸리티또는 db2inidb 명령의 relocate using 옵션을사용합니다. $ db2start 보조시스템에서데이터베이스인스턴스를시작합니다. $ db2inidb sample(db 명 ) as standby 미러데이터베이스를롤포워드보류상태로만들어보조시스템에서초기화합니다. 기본시스템의 Active 로그 (1 차로그, 2 차로그 ) 와 Archive 로그를보조시스템의 Active 로그디렉토리 (db 파라미터값의 LOGPATH 값 ) 에복사를해야합니다. $ db2 rollforward db sample to end of logs and stop 대기데이터베이스를온라인으로전환하기위해 STOP 옵션을지정하여 Rollforward 명령을수행합니다. 1.3 Split Mirror 를클론데이터베이스로사용 ( 다른서버에백업 ) Split Mirror 를클론데이터베이스로사용하기위한단계 $ db2 connect to sample(db 명 ) $ db2 set write suspend for database 접속한데이터베이스에서입출력을일시중단합니다. Write suspend 가되었는지는 db2diag.log 의 MESSAGE 내용으로체크하면됩니다. 예 ) The table space ʺSYSCATSPACEʺ (ID ʺ0ʺ) has been placed in the WRITE_SUSPEND state $ db2 ʺselect * from sysibmadm.dbpathsʺ 적절한운영체제레벨의명령을사용하여기본데이터베이스에서미러를분할합니다 (flash copy 와같은 Utility 를사용하여매일변경분을적용합니다 ). 위의 DBPATHS 뷰를통해분할해야할데이터베이스의모든파일및디렉토리를복사합니다. $ db2 set write resume for database 기본데이터베이스에서입출력을재개합니다. Write resume 가되었는지는 db2diag.log 의 MESSAGE 내용으로체크하면됩니다. 예 ) The table space ʺSYSCATSPACEʺ (ID ʺ0ʺ) which was previously in the WRITE_SUSPEND state is no longer in that state. Write I/O has been resumed to the table space. $ db2 catalog db sample(db 명 ) as sample1(db 별명 ) on path(dbpath 에서위치한 db 위치 ) 보조시스템에서미러링된데이터베이스를카탈로그합니다. $ db2inidb sample(db 명 ) as snapshot relocate using relocatedbcfg.txt 118

119 db 명, path 등 Configuration 내용을바꿀경우에 db2inidb 명령에 RELOCATE USING 옵션을지정하여대기데이터베이스를재배치합니다. $ db2relocatedb f relocatedbcfg.txt db2inidb 대신에 db2relocatedb 를사용할수도있습니다. db2relocatedb 를사용하는경우엔 db2inidb db 명 as standby 를사용하지않아도됩니다. 동일한시스템에미러데이터베이스와기본데이터베이스가존재하는경우는 db2relocatedb 유틸리티또는 db2inidb 명령의 relocate using 옵션을사용합니다. $ db2start 보조시스템에서데이터베이스인스턴스를시작합니다. $ db2inidb sample(db 명 ) as snapshot 보조시스템에서미러데이터베이스를초기화해서사용을합니다. 119

120 MAT019. Redirect Restore 1. Redirect Restore 1.1 Redirect Restore 수행단계 $ db2 restore db sample(db 명 ) replace existing redirect redirect 옵션을사용해서데이터베이스를 restore 합니다. $ db2 set tablespace containers for 5 using (file ʹf:\ts3con1ʹ 20000, file ʹf:\ts3con2ʹ 20000) 새로지정을원하는컨테이너가있는각각의테이블스페이스에새롭게컨테이너의저장위치를지정해줍니다. 숫자 5 는 db2 list tablespaces 에서나타나는테이블스페이스 ID 를의미합니다. Automatic Storage 인경우는새로지정을할수없습니다. 데이터베이스리스토어시정의한데이터베이스경로 (ON 절 ) 에저장이됩니다. 파일사이즈설정시 K, M, G 를사용하지못하고테이블스페이스에지정한페이지수를단위로해서사이즈를설정해야합니다. 각테이블의페이지사이즈는 select tbspace, pagesize from syscat.tablespaces 를이용해확인이가능합니다. $ db2 list tablespace containers for 5 show detail 컨테이너의위치가제대로변경이되었는지확인합니다. $ db2 restore db sample(db 명 ) continue restore 작업을계속진행을합니다. $ db2 restore db sample(db 명 ) abort restore 작업을강제종료하거나 continue 작업중오류시는처음부터다시 restore 작업을시작해야합니다. 1.2 자동으로생성된스크립트를사용하여경로재지정된리스토어수행 제한사항 스크립트생성을위해서는데이터베이스에연결이가능해야합니다. 따라서데이터베이스에서이주또는응급복구를요청하는경우, 리스토어스크립트생성하기전에수행해야합니다. RESTORE 명령을사용하여스크립트생성시 REPLACE EXISTING 옵션을지정해도 REPLACE EXISTING 옵션은스크립트에주석처리됩니다. 보안상의이유로생성된스크립트에는암호가나타나지않기에암호는직접입력해야합니다. 제어센터의리스토어마법사를사용해서는경로재지정된리스토어스크립트를생성할수없습니다. 120

121 리스토어수행 $ db2 restore db sample(db 명 ) from /home/backups taken at on c:\ into test_re redirect generate script test_redirect.clp 위명령은 test_redirect.clp 라고하는경로재지정된리스토어스크립트를작성합니다. db 경로변경을지정할때는위와같이 ʺon C:\ʺ 설정합니다. 윈도우는드라이브만설정이가능합니다. 즉 ʺC:\testʺ 와같이설정한경우는데이터베이스경로를찾지못한다는에러와함께실행이되지않습니다. 경로재지정된리스토어스크립트 (test_redirect.clp) 에서변경이가능한내용 * 리스토어옵션 * 자동스토리지경로 * 컨테이너레이아웃및경로 경로재지정된리스토어스크립트예제 * 아래의내용에서 로되어있는부분중에수정을원하는경우 를제거하시면됩니다. 설정값또한스크립트에서보고수정을하시면됩니다. ***************************************************************************** ** 자동으로작성된경로재지정리스토어스크립트 ***************************************************************************** UPDATE COMMANDOPTIONS USINGSONZ ONSAMPLE_NODE0000.out V ON; SET CLIENTATTACH_DBPARTITIONNUM 0; SET CLIENTCONNECT_DBPARTITIONNUM 0; ***************************************************************************** ** 자동으로작성된경로재지정리스토어스크립트 ***************************************************************************** RESTORE DATABASESAMPLE USER <username> USINGʹ<password>ʹ FROMʹC:\TEST\BACKUPʹ TAKEN AT ONʹC:ʹ DBPATH ONʹ<target directory>ʹ INTOTEST_RE NEWLOGPATHʹC:\DB2\NODE0000\SQL00001\SQLOGDIR\ʹ WITH<num buff>buffers BUFFER<buffer size> REPLACE HISTORYFILE REPLACEEXISTING REDIRECT PARALLELISM<n> WITHOUT ROLLINGFORWARD WITHOUTPROMPTING ; ***************************************************************************** ** 테이블스페이스정의 ***************************************************************************** ***************************************************************************** ** 테이블스페이스이름 =SYSCATSPACE ** 테이블스페이스 ID = 0 121

122 ** 테이블스페이스유형 = 데이터베이스관리스페이스 ** 테이블스페이스내용유형 = 모든영구데이터. 일반테이블스페이스 ** 테이블스페이스페이지크기 ( 바이트 ) =4096 ** 테이블스페이스 Extent 크기 ( 페이지 ) = 4 ** 자동스토리지사용 = 예 ** 자동크기조정사용가능 = 예 ** 총페이지수 = ** 사용가능한페이지수 = ** 상위워터마크 ( 페이지 ) = ***************************************************************************** *****************************************************************************.. ** 테이블스페이스이름 =TEST ** 테이블스페이스 ID = 7 ** 테이블스페이스유형 = 데이터베이스관리스페이스 ** 테이블스페이스내용유형 = 모든영구데이터. 대형테이블스페이스 ** 테이블스페이스페이지크기 ( 바이트 ) =4096 ** 테이블스페이스 Extent 크기 ( 페이지 ) =32 ** 자동스토리지사용 = 아니오 ** 자동크기조정사용가능 = 아니오 ** 총페이지수 = 1024 ** 사용가능한페이지수 = 992 ** 상위워터마크 ( 페이지 ) = 96 ***************************************************************************** SET TABLESPACECONTAINERS FOR 7 IGNORE ROLLFORWARDCONTAINER OPERATIONS USING( FILE ʹc:\test.datʹ 4M ); ***************************************************************************** ** 경로재지정된리스토어시작 ***************************************************************************** RESTORE DATABASESAMPLE CONTINUE; ***************************************************************************** ** 파일의끝 (EOF) ***************************************************************************** 122

123 MAT020. Relocate database 1. Relocate Database 1.1 Relocate Database 란? DB 관리자가아래에정의된내용과같은 Configuration file 을이용해데이터베이스부분 ( 예 : 컨테이너와로그위치등 ) 이나데이터베이스위치또는데이터베이스명을변경할수있습니다. 1.2 Configuration File 정의 예 : DB_NAME=oldName,newName DB_PATH=oldPath,newPath INSTANCE=oldInst,newInst NODENUM=nodeNumber LOG_DIR=oldDirPath,newDirPath CONT_PATH=oldContPath1,newContPath1 CONT_PATH=oldContPath2,newContPath2 STORAGE_PATH=oldStoragePath1,newStoragePath1 STORAGE_PATH=oldStoragePath2,newStoragePath2 DB_NAME: 변경될데이터베이스명과현재데이터베이스명을지정합니다. 반드시지정해야합니다. DB_PATH: 변경될데이터베이스위치와와현재데이터베이스위치를지정합니다. 반드시지정해야합니다. INSTANCE: 변경될인스턴스명과현재인스턴스명을지정합니다. 반드시지정해야합니다. NODENUM: 변경될데이터베이스노드의노드번호를지정합니다. LOG_DIR: 변경될로그위치와현재로그위치를지정합니다. CONT_PATH: 변경될컨테이너위치와현재컨테이너위치를지정합니다. 현재와변경될컨테이너위치간에특정디렉토리아래의디렉토리를똑같이가져간다면 *( 와일드카드 ) 를이용해지정할수있습니다. CONT_PATH=/maininst_files/allconts/*, /MAINDB/* STORAGE_PATH: Automatic Storage 로지정된데이터베이스에서만사용이가능합니다. 변경을원하는저장위치와현재저장위치를지정합니다. 1.3 Relocate Database 수행 $ db2relocatedb f relocate.cfg 123

124 relocate.cfg 는 5.2 내용을참조해서작성을하면됩니다. 124

125 MAT021. Log Setting 1. 로그 1.1 로그설정과관련한파라미터 LOGFILSIZ ( 로그파일크기 ) 로그파일에대한사이즈이며기본값은 1000 페이지입니다. 범위는 [ ] 입니다. 최대파일사이즈는 2GB 입니다. 파일사이즈변경시는모든사용자의연결이끊긴후다음데이터베이스의연결중에크기가조정됩니다 LOGPRIMARY (1 차로그파일수 ) 1 차로그에대한파일수이며기본값은 3 입니다. 범위는 [2 256] 입니다. 1 차로그와 2 차로그의수를포함해서최대 256 이됩니다. 예로 1 차로그가 200 이면 2 차로그는 56 만쓰일수있습니다 LOGSECOND (2 차로그파일수 ) 1 차로그파일이가득찬경우 2 차로그파일이필요에따라한번에하나씩, 이매개변수로제어할수있는최대수까지할당이되며기본값은 2 입니다. 범위는 [ 1; 0 254] 입니다. 한달에한번과같은주기적으로많은로그공간이필요한데이터베이스에 2 차로그파일을사용합니다 NEWLOGPATH ( 로그파일에대한변경된경로 ) 로그파일이저장되는위치를변경하며문자열은최대 242 바이트까지지정하며기본값은 NULL 입니다. 범위는 [ 유효한모든경로또는디바이스 ] 입니다. 로그파일은입출력이많지않은물리디스크에두는것이좋습니다 LOGPATH ( 로그파일에대한경로 ) 로그목적으로사용되는현재경로입니다. 경로의변경은 NEWLOGPATH 매개변수에대한변경사항으로적용이되며사용자가직접이파라미터를변경할수없습니다 MIRRORLOGPATH ( 미러로그경로 ) 1 차로그경로에있는로그를디스크오류나실수로삭제하지않도록보호하기위해동일한로그세트가미러로그경로에유지되도록지정하는경로이며문자열은최대 242 바이트까지지정하며기본값은 NULL 입니다. 1 차로그경로가원시디바이스인경우에는이구성매개변수가지원되지않으며또한이매개변수값도원시디바이스값으로지정할수없습니다. 125

126 미러로그는 1 차로그파일이저장되는경로와물리적으로구분된별도의디스크로설정하는것이좋습니다. MIRRORLOGPATH 를처음사용할경우, 데이터베이스를재구동해야만적용이됩니다. 단계는아래와같습니다. $ db2 list applications for db sample(db 명 ) $ db2 ʺforce applications (id1, id2...)ʺ id1 과 id2 등은 db2 list applications 에서나타나는응용프로그램핸들 (Application Handle) 응아이디입니다. $ db2 list applications for db sample(db 명 ) 메시지가 ʺ 데이터베이스시스템모니터에서리턴된데이터가없습니다.ʺ 일때까지 force applications 를해야합니다. $ db2 terminate $ db2 deactivate db db 명실행후 $ db2 activate db db 명 1.2 로깅방식 순환로그 (CIRCULAR LOG) Inactive Log file 을재사용하는 Logging 방법 $ db2 update db cfg for db 명 using LOGRETAIN OFF USEREXIT OFF Database Configuration File 의 ʹlogretainʹ 이 off 상태이고, ʹuserexitʹ 이 off 상태인경우입니다 보존로그 (Archive LOG) Inactive Log file 을재사용하지않고보관해둠으로써데이터베이스복구에가장최근시점까지의 Log 를적용시키는 Logging 방법이다. 처음 Archive Log 적용시 backup Pending 상태로 Full backup 을받아야한다. 126

127 $ db2 update db cfg for sample(db 명 ) using USEREXIT ON USER EXIT 프로그램이로그파일을보존하고검색하는데사용합니다. $ db2 update db cfg for sample(db 명 ) using LOGRETAIN ON 이경우에는아카이브된로그는데이터베이스로그경로디렉토리에저장됩니다. $ db2 get db cfg for sample(db 명 ) Archive 로그는로그경로디렉토리에서한글로 처음에사용되는로그파일 의로그파일명의시퀀스값이전의파일명들입니다. $ db2 update db cfg for db 명 using LOGARCHMETH1 DISK:/arch/logs DB2 V9 부터는 logarchmeth1 을지정하는방법을강력하게권장합니다. 기본값은 OFF 이며값은추가로 [LOGRETAIN, USEREXIT, DISK, TSM, VENDOR] 입니다. * OFF: 로그아카이브메소드가사용되지않도록지정합니다. * LOGRETAIN: 이값을지정하면 logretain 매개변수가 RECOVERY 로설정하는것과같이자동으로갱신됩니다. * USEREXIT: 이값을지정하면 userexit 매개변수가 ON 으로설정하는것과같이자동으로갱신됩니다. * DISK: 이값은뒤에 : 이오고, 로그파일이아카이브된기존의완전한경로이름이나와야합니다. 위와같이 DISK:/arch/logs 로설정한경우아카이브로그파일은 /arch/logs 라는디렉토리에저장됩니다. * TSM: 추가구성매개변수없이지정한경우이값은디폴트관리클래스를사용하여로컬 TSM 서버에로그파일을아카이브해야함을나타냅니다. 콜론 (:) 과 TSM 관리클래스가뒤에오면로그파일이지정된관리클래스를사용하여아카이브됩니다. * VENDOR: 벤더라이브러리가로그파일을아카이브하는데사용되도록지정합니다. 이값뒤에콜론 (:) 과라이브러리이름이와야합니다. 라이브러리에제공된 API 는벤더제품에대한백업및리스토어 API 를사용해야합니다 무한로그설정 $ db2 update db cfg for db 명 using LOGSECOND 1 데이터베이스가사용중인로그스페이스가무제한으로구성됩니다. 데이터베이스에서실행되는인플라이트 (Inflight) 트랜잭션의크기나수에는한계가없습니다. 127

128 사용중인로그스페이스가무제한이면, 일반적으로 1 차로그에할당하는로그스페이스보다많은양의로그스페이스를필요로하는대형작업을수용해야하는환경에서유용합니다. DB2 가아카이브된로그파일을검색해야하므로응급복구시간이증가할수있습니다. 128

129 MAT022. Connection Concentrator Setting 1. 개요 많은동시사용자연결을필요로하는환경의경우, 연결된응용프로그램수와데이터베이스에의해서처리될수있는응용프로그램의수의관계가일대일이아닌다대일관계를요구하는상황에서이를제어하기위하여구성데이터베이스관리프로그램구성매개변수를사용합니다. 각연결에대한메모리사용을줄이고컨텍스트전환의수를줄일수도있습니다. 2. Setting max_connections 매개변수를사용하여최대연결된응용프로그램수를지정하고 max_coordagents 매개변수를사용하여처리할수있는응용프로그램요청수를지정합니다. connection concentrator 는 max_connection 값이 max_coordagents 값보다큰경우사용됩니다. db2disp 프로세스가구동되고있으면 connection concentrator 가작동중인것입니다. 129

130 MAT023. STMM Setting 1. self tuning memory 관련리소스용데이터베이스매개변수 1.1 BUFFER POOLS 1.2 PACKAGE CACHE 1.3 LOCKING MEMORY 1.4 SORT MEMORY 1.5 TOTAL DATABASE SHARED MEMORY 2. STMM(self tuning memory menager) 2.1 self_tuning_mem default value ( 참고 ) 단일파티션데이터베이스를새로만들경우 ON 이며다중파티션에서생성시는 OFF 입니다. 2.2 STMM setting $ db2 update db cfg for database name using self_tuning_mem on ( 참고 ) self_tuning_mem 매개변수를 on 으로설정하여도 1 번에서언급한리소스용데이터베이스매개변수의값들중 AUTOMATIC 이 2 개이하, 즉자체조정이가능한매개변수가 2 개이하이면 STMM 이 INACTIVE 상태가되어자체조정이발생하지않습니다. ( 참고 ) 메모리자체조정은 HADR 1 차서버에서만실행됩니다. HADR 시스템에서메모리자체조정이활성화되면 2 차서버에서는절대실행되지않으며구성이제대로설정된경우에만 1 차서버에서실행됩니다. HADR 데이터베이스역할을전환하는명령을실행할경우새로운 1 차서버에서실행되도록메모리자체조정조작이전환됩니다 2.3 self tuning memory 관련리소스용데이터베이스매개변수 setting buffer pool Buffer pool PART 참조 PACKAGE CACHE $ db2 update db cfg for database name using PCKCASHESZ AUTOMATIC 130

131 2.3.3 LOCKING MEMORY $ db2 update db cfg for database name using LOCKLIST AUTOMATIC $ db2 update db cfg for database name using MAXLOCKS AUTOMATIC SORT MEMORY $ db2 update db cfg for database name using SHEAPTHRES_SHR AUTOMATIC $ db2 update db cfg for database name using SORTHEAP AUTOMATIC ( 참고 ) sort memory 의자동조정은데이터베이스관리프로그램구성매개변수 sheapthres 가 0 으로설정될때만허용됩니다 데이터베이스관리프로그램구성매개변수 sheapthres 가 0 으로설정될때만허용됩니다 $ db2 get dbm cfg grep SHEAPTHRES Sort heap threshold (4KB) (SHEAPTHRES) = TOTAL DATABASE SHARED MEMORY $ db2 update db cfg for database name using DATABASE_MEMORY AUTOMATIC 131

132 MAT024. Runstats 1. 개요 RUNSTATS 는데이터베이스의통계정보를최신상태로갱신하는데사용됩니다. DB2 optimizer 는 access path 를결정할때바로이통계정보를참조하므로대량의데이터가삽입되거나또는지워졌을경우항상 RUNSTATS 를실행시켜서통계정보를갱신하는것이중요합니다. 또한 REORGCHK utility 도이통계정보를참조하여 REORG 필요유무를판단하므로 REORGCHK 를수행하기전에항상 RUNSTATS 를먼저수행하는것이좋습니다. SYSSTAT 스키마를가진시스템카탈로그뷰에그정보가저장되며, 값이 1 로된항목은통계자료가갱신되지않았다는의미입니다 * RUNSTATS 수행시점테이블에데이터가 LOAD 되고적합한인덱스가생성된후새로운인덱스를생성한후 REORG Utility 로테이블을재구성한후테이블과인덱스에대규모의 INSERT, DELETE, UPDATE 가발생한후 Prefetch Size 가변경된후 Redistribute Database Partition Group Utility 가실행된후 2. RUNSTATS OPTION 2.1 WITH DISTRIBUTION 테이터의분포가일정하지않을때 with distribution 옵션을주고실행합니다. 통계자료와컬럼값의분포에대한통계자료를수집할수있습니다. 만약중복된데이터값이많고, 일정하지않으면데이터별분포자료정보를주어 optimizer 의판단을돕는것이좋습니다. ex) $ db2 RUNSTATS ON TABLE schema1.test1 WITH DISTRIBUTION AND INDEXES ALL 테이블에대한 distribution 통계수집과인덱스통계수집 ex) $ db2 RUNSTATS ON TABLE schema1.table1 ON COLUMNS (col1, col2) WITH DISTRIBUTION ON COLUMNS (col3, col3) 특정컬럼 (COL1, COL2) 은기본정보를, 특정컬럼 (COL3, COL4) 은 distribution 통계정보를수집 2.2 FOR INDEXES 특정테이블의인덱스에대한통계자료만갱신합니다. 132

133 ex) $ db2 RUNSTATS ON TABLE schema1.test1 FOR INDEXES ALL ex) $ db2 RUNSTATS ON TABLE schema1.test1 FOR INDEXES schema1.idx1 2.3 DETAILED 인덱스에대한상세한통계자료를갱신합니다. ex) $ db2 RUNSTATS ON TABLE schema1.test1 FOR DETAILED INDEXES ALL 2.4 ALLOW WRITE ACCESS 통계자료를갱신하는동안읽고쓰기가가능합니다. DEFAULT 값입니다. $ db2 RUNSTATS ON TABLE schema1.test1 ALLOW WRITE ACCESS 2.5 ALLOW READ ACCESS 통계자료를갱신하는동안읽기만가능하고쓰기는제한합니다. $ db2 RUNSTATS ON TABLE schema1.test1 ALLOW READ ACCESS 2.6 컬럼그룹통계수집 컬럼그룹통계를수행하면, 해당컬럼그룹에대한 distinct 조합값의정보를저장합니다. 기본정보로는 db2 optimizer 가데이터상관관계를알수없는데, 컬럼그룹통계정보를통해좀더정확하게 db2 optimizer 가 multi predicate 에대한선택을수행하도록하여줍니다. ex) db2 RUNSTATS ON TABLE schema1.table1 ON COLUMNS ((col1, col2), col3, (col4, col5) 2.7 SAMPLED DETAILED 데이터의일부만스캔하여 runstats 를수행합니다. 만약쿼리의내용이전체트랜드와패턴을조사하는내용이며, 일정부분의오차를가져도패턴과트랜드를알아내는데충분하다면, data sampling 이 FULL 테이블스캔보다는효율적입니다. 옵션을주면인덱스통계를계산할때 sampling 이사용되며 detailed 정보를수집하는데필요한시간과자원소모가줄어들게됩니다. ex) db2 RUNSTATS ON TABLE schema1.test1 AND SAMPLED DETAILED INDEXES ALL ( 참고 ) Load 를수행한후통계정보를수집 133

134 LOAD FROM test1.del OF DEL REPLACE INTO db2admin.test1 STATISTICS USE PROFILE index 를작성하면서통계정보도함께수집 CREATE INDEX db2admin.inx1 ON db2admin.table1(col1) COLLECT STATISTICS 3. 자동통계수집 특정한때에자동적으로통계정보가수집됩니다. 통계정보가필요하다고생각되는근거는 Workload 를보고 DB2 가판단합니다. 자동으로설정하기위해서는 DB CFG 파라미터를변경하여야합니다. $ db2 update db cfg for SAMPLE using AUTO_MAINT ON $ db2 update db cfg for SAMPLE using AUTO_TBL_MAINT ON $ db2 update db cfg for SAMPLE using AUTO_RUNSTATS ON $ db2 update db cfg for SAMPLE using AUTO_STATS_PROF ON $ db2 update db cfg for SAMPLE using AUTO_PROF_UPD ON 자동통계프로파일링은언제, 어떻게통계를수집해야하는지정보를얻기위해서내부적으로테이블 (query feedback warehouse) 을작성하고해당테이블의데이터를근거로통계프로파일을작성합니다. 자동통계프로파일링은 SMP, MPP, federate 환경에서는수행되지않습니다. 134

135 MAT025. Reorg 1. 개요 REORG utility 는레코드의 insert/delete 등으로인하여데이터및 index 페이지의물리적인정열순서가엉키는것을새롭게정렬시켜주는 utility 이다. REORGCHK 의결과를이용하여필요시테이블또는인덱스를물리적으로재구성할수있습니다. 작업시에는현재데이터의약 2.5 배의공간이필요하므로충분한크기의임시테이블공간을이용하는것이좋습니다. table 에자주사용되는 index 가있을경우해당 index 를사용하여 reorg 를실행하면 index 순서와동일하게데이터가재구성됩니다. 2. REORGCHK REORGCHK unility 는데이터베이스를검사하여 REORG 를할필요가있는지를체크하는 utility 이다. 데이터페이지와 index 페이지의순서가서로맞지않으면성능의저하를유발하게되므로 REORGCHK 를정기적으로수행하여데이터베이스의물리적인상태를확인하는것이필요하다. $ db2 REORGCHK (UPDATE STATISTICS) ON TABLE test.test 옵션을지정하지않으면 update statistics 가 default 로지정되어자동적으로 runstats 가실행됩니다. $ db2 REORGCHK CURRENT STATISTICS on test.test ( 참고 ) 테이블명지정시스키마명. 테이블명으로지정해야합니다. $ db2 REORGCHK ON TABLE all 모든테이블을점검합니다. $ db2 REORGCHK ON SCHEMA shcema name 특정스키마명을가지는테이블만점검합니다. 3. REORG TABLE 3.1 use tablespace 재구성할테이블의복사본을지정할임시테이블스페이스를지정합니다. 설정하지않으면테이블이저장된테이블스페이스에복사본을저장합니다. $ db2 reorg table schem name.table name use temporary tablespace name 3.2 INDEX index name 특정인덱스의정렬순서를기준으로재구성합니다. 설정하지않을경우임의의순서대로재구성됩니다. $ db2 reorg table schem name.table name index schem name.index name 135

136 3.3 ALLOW NO ACCESS reorg 도중테이블을엑세스할수없습니다. $ db2 reorg table schem name.table name ALLOW NO ACCESS 3.4 ALLOW READ ACCESS reorg 도중읽기엑세스만가능합니다. 3.5 ALLOW WRITE ACCESS 읽기와쓰기모두허용됩니다. 온라인재구성에서만가능합니다. reorg 모니터링... online offline reorg 어느경우에사용 2g 이상 offline INPLACE 온라인모드로재구성, 재구성하는동안테이블의읽기와쓰기가가능합니다. $ db2 reorg table schem name.table name inplace allow read access start 3.7 NOTRUNCATE TABLE truncate 단계를수행하지않도록지정합니다. 4. REORG INDEX 인덱스의데이터가연속적으로저장될수있도록인덱스만재구성합니다. 4.1 ALLOW NO ACCESS reorg 도중테이블을엑세스할수없습니다. $ db2 reorg indexes all for table schem name.table name ALLOW NO ACCESS 4.2 ALLOW READ ACCESS reorg 도중읽기엑세스만가능합니다. 4.3 ALLOW WRITE ACCESS 읽기와쓰기모두허용됩니다. 온라인재구성에서만가능합니다. 136

137 4.4 CLEANUP ONLY ALL 완전히비어있는페이지는인덱스트리에서제거하고, 일부영역이비어있는페이지는인접페이지와병합하고빈페이지는제거합니다. default 값입니다. $ db2 reorg indexes all for table schem name.table name CLEANUP ONLY ALL 4.5 CLEANUP ONLY PAGES 한페이지의모든데이터가삭제되어완전히비어있는페이지를제거합니다. $ db2 reorg indexes all for table schem name.table name CLEANUP ONLY PAGES ( 참고 ) reorg index 에서는 USE option 을사용할수없습니다. 기본적으로인덱스가속한테이블스페이스를사용합니다. use option 을사용하기위해서는 table 절에서만사용가능합니다. 5. reorg 절차 5.1 reorg 작업을위한 temporary tablespace 추가 $ db2 create temporary tablespace tempspace1 managed by system using (device PATH ) 5.2 database fullbackup $ db2 backup database DB 명 online to PATH 5.3 모든에이전트연결해제 $ db2 force application all 5.4 REORGCHK 수행 $ db2 reorgchk update statistics on table all 5.5 체크된 TABLE 들에대하여인덱스명으로 REORG 작업을수행함 $ db2 reorg table table name index index name use tablespace1 5.6 reorgchk 를수행하여 reorg 되었나확인 $ db2 reorgchk update statistics on table all 137

138 5.7 RUNSTATS 를실행 $ db2 runstats on table table name and indexes all 5.8 DB REBIND $ db2rbind database name l logfile name 5.9 temporary tablespace 삭제 $ db2 drop tablespace tablespace DB RESTART $ db2stop force 138

139 Monitor(MON) 139

140 MON001. Snapshot 1. SNAPSHOT 1.1 개요 DB2 DBMS 는인스턴스및데이터베이스활동을모니터링할수있도록명령어기반의 snapshot 모니터도구를제공합니다. 각영역들을모니터링하도록옵션을제공하여인스턴스, 데이터베이스, 어플리케이션에관련된많은정보들을수집할수있습니다. 1.2 명령구조확인 db2 터미널에서명령구조를확인하고자할때사용합니다. $db2? < 명령어 > 예 ) $db2? get snapshot 1.3 snapshot 을수행하기위한전제 snapshot 을이용하여활동정보를추출하기위해서는영역별모니터링스위치혹은모니터링관련파라메터가활성화되어있어야합니다. 모니터링파라메터를활성화하도록설정해놓으면해당인스턴스가기동할때자동으로관련한모니터링스위치가자동으로켜집니다. 그리고모니터링스위치가켜져있어야실질적으로 snapshot 을이용하여정보조회가가능합니다. 모니터링파라메터의활성화설정으로모니터링스위치가켜져있든지비활성화설정으로모니터링스위치가꺼져있든지에상관없이모니터링스위치는온라인으로켜고끄기를수행할수있습니다. 즉, 모니터링파라메터를설정하는것은인스턴스기동시에관련모니터스위치가자동으로켜져있도록설정하는데그의미가있습니다 모니터링파라메터확인하기 $db2 ʺget dbm cfgʺ grep i ʺdft_mon_ʺ 모니터링파라메터설정활성화 / 비활성화하기 모니터링파라메터는 ( 버퍼풀 / 잠금 / 정렬 / 명령문 / 테이블 / 시간소인 / 명령단위 ) 의 7 개가있으며각각의파라메터를 on / off 함으로써활성 / 비활성설정이이루어집니다. 모니터링파라메터의값을변경하면인스턴스가재기동되어야변경된설정이적용됩니다. 가. 버퍼풀 버퍼풀모니터링파라메터설정활성화하기 140

141 $db2 ʺupdate dbm cfg using dft_mon_bufpool onʺ 버퍼풀모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_bufpool offʺ 나. 잠금 잠금모니터링파라메터설정활성화하기 $db2 ʺupdate dbm cfg using dft_mon_lock onʺ 잠금모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_lock offʺ 다. 정렬 정렬모니터링파라메터설정활성화하기 $db2 ʺupdate dbm cfg using dft_mon_sort onʺ 정렬모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_sort offʺ 라. 명령문 명령문모니터링파라메터설정활성화하기 $db2 ʺupdate dbm cfg using dft_mon_stmt onʺ 명령문모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_stmt offʺ 마. 테이블 테이블모니터링파라메터설정활성화하기 $db2 ʺupdate dbm cfg using dft_mon_table onʺ 테이블모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_table offʺ 바. 시간소인 테이블모니터링파라메터설정활성화하기 $db2 ʺupdate dbm cfg using dft_mon_timestamp onʺ 테이블모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_timestamp offʺ 사. UOW UOW 모니터링파라메터설정활성화하기 141

142 $db2 ʺupdate dbm cfg using dft_mon_uow onʺ UOW 모니터링파라메터설정비활성화하기 $db2 ʺupdate dbm cfg using dft_mon_uow offʺ 모니터링스위치확인하기 $db2 ʺget moitor switchesʺ 모니터링스위치켜고끄기 가. 버퍼풀 버퍼풀모니터링스위치활성화하기 $db2 ʺupdate monitor switches using bufferpool onʺ 버퍼풀모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using bufferpool offʺ 나. 잠금 잠금모니터링스위치활성화하기 $db2 ʺupdate monitor switches using lock onʺ 잠금모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using lock offʺ 다. 정렬 정렬모니터링스위치활성화하기 $db2 ʺupdate monitor switches using sort onʺ 정렬모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using sort offʺ 라. 명령문 명령문모니터링스위치활성화하기 $db2 ʺupdate monitor switches using statement onʺ 명령문모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using statement offʺ 마. 테이블 테이블모니터링스위치활성화하기 $db2 ʺupdate monitor switches using table onʺ 142

143 테이블모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using table offʺ 바. 시간소인 테이블모니터링스위치활성화하기 $db2 ʺupdate monitor switches using timestamp onʺ 테이블모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using timestamp offʺ 사. UOW UOW 모니터링스위치활성화하기 $db2 ʺupdate monitor switches using uow onʺ UOW 모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using uow offʺ 아. 여러개의모니터링스위치활성화하기 여러개의모니터링스위치를활성화 / 비활성화하기위해서는단지필요한모니터스위치를나열함으로써그렇게할수있습니다. 물론 on 과 off 를섞어서지정하여어떤것을활성화하고어떤것은비활성화할수도있습니다. 특정모니터링스위치활성화하기 $db2 ʺupdate monitor switches using < 모니터스위치 1> on < 모니터스위치 2> on < 모니터스위치 N>ʺ 예 ) $db2 update monitor switches using table on lock on statement on 특정모니터링스위치비활성화하기 $db2 ʺupdate monitor switches using < 모니터스위치 1> off < 모니터스위치 2> off < 모니터스위치 N>ʺ 예 ) $db2 update monitor switches using table off lock off statement off 특정모니터링스위치활성화 / 비활성화하기 $db2 ʺupdate monitor switches using < 모니터스위치 1> <on or off> < 모니터스위치 2> <on or off> < 모니터스위치 N> <on or off>ʺ 예 ) $db2 update monitor switches using bufferpool on lock off table off statement on 1.4 Snapshot 이지원하는영역별명령유형 ( 파티션환경에관련한부분은제외합니다.) 인스턴스활동정보수집 143

144 $db2 ʺget snapshot for database managerʺ 현재인스턴스에속한모든데이터베이스활동 $db2 ʺget snapshot for all databasesʺ 현재인스턴스에속한모든데이터베이스에활동중인어플리케이션 (dcs 제외 ) $db2 ʺget snapshot for all applicationsʺ 현재인스턴스에속한모든데이터베이스에활동중인어플리케이션 (dcs 만조회 ) $db2 ʺget snapshot for all dcs applicationsʺ 현재인스턴스에속한모든데이터베이스에작성된버퍼풀 $db2 ʺget snapshot for all bufferpoolsʺ 어플리케이션아이디값기준으로조회한특정어플리케이션에대한활동정보 (dcs 제외 ) $db2 ʺget snapshot for application applid < 어플리케이션아이디값 >ʺ 예 ) $db2 ʺget snapshot for application applid *LOCAL.DB ʺ 어플리케이션핸들값기준으로조회한특정어플리케이션에대한활동정보 (dcs 제외 ) $db2 ʺget snapshot for application agentid < 어플리케이션핸들값 >ʺ 예 ) $db2 ʺget snapshot for application agentid 94ʺ 어플리케이션아이디값기준으로조회한특정어플리케이션에대한활동정보 (dcs 만조회 ) $db2 ʺget snapshot for dcs application applid < 어플리케이션아이디값 >ʺ 예 ) $db2 ʺget snapshot for dcs application applid *LOCAL.DB ʺ 어플리케이션핸들값기준으로조회한특정어플리케이션에대한활동정보 (dcs 만조회 ) $db2 ʺget snapshot for dcs application agentid < 어플리케이션핸들값 >ʺ 예 ) $db2 ʺget snapshot for dcs application agentid 94ʺ 파티션간통신에관련된 FCM 활동정보조회 $db2 ʺget snapshot for fcm for all dbpartitionnumsʺ 어플리케이션아이디값기준으로조회한특정어플리케이션에대한잠금활동정보 144

145 $db2 ʺget snapshot for locks for application applid < 어플리케이션아이디값 >ʺ 예 ) $db2 ʺget snapshot for locks for application applid *LOCAL.DB ʺ 어플리케이션핸들값기준으로조회한특정어플리케이션에대한잠금활동정보 $db2 ʺget snapshot for locks for application agentid < 어플리케이션핸들값 >ʺ 예 ) $db2 ʺget snapshot for locks for application agentid on 94ʺ 카탈로그된모든원격데이터베이스에대한활동정보 $db2 ʺget snapshot for all remote_databasesʺ 모든원격어플리케이션활동정보 $db2 ʺget snapshot for all remote_applicationsʺ 특정데이터베이스에대한데이터베이스활동및어플리케이션활동정보 $db2 ʺget snapshot for all on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for all on sampleʺ 특정데이터베이스에대한데이터베이스에대한활동정보만조회 (dcs 데이터베이스제외 ) $db2 ʺget snapshot for database on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for database on sampleʺ 특정데이터베이스에대한데이터베이스에대한활동정보만조회 (dcs 데이터베이스 ) $db2 ʺget snapshot for dcs database on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for dcs database on sampleʺ 특정데이터베이스에접속한어플리케이션활동정보 (dcs 어플리케이션제외 ) $db2 ʺget snapshot for applicatins on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for applications on sampleʺ 특정데이터베이스에접속한어플리케이션활동정보 (dcs 어플리케이션 ) $db2 ʺget snapshot for dcs applicatins on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for dcs applicatins on sampleʺ 특정데이터베이스에속한테이블정보조회 $db2 ʺget snapshot for tables on < 데이터베이스 Alias>ʺ 145

146 예 ) $db2 ʺget snapshot for tables on sampleʺ 특정데이터베이스가보유한잠금정보조회 $db2 ʺget snapshot for locks on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for locks on sampleʺ 특정데이터베이스에작성된버퍼풀활동정보조회 $db2 ʺget snapshot for bufferpools on < 데이터베이스 Alias>ʺ 예 ) $db2 ʺget snapshot for bufferpools on sampleʺ 특정데이터베이스에활동중인 DYNAMIC SQL 정보저장 $db2 ʺget snapshot for dynamic sql on < 데이터베이스 Alias>ʺ > dynsql.out 예 ) $db2 ʺget snapshot for dynamic sql on sampleʺ > dynsql.out 1.5 Snapshot 과구성파라미터를이용한 DB 점검 (Unix/Linux 기준 ) 데이터스토리지 인스턴스 Home Directory 사용률 $ db2 get dbm cfg grep DFTDBPATH 위경로의사용량이 80% 를넘어가지않도록해야합니다 테이블스페이스사용률 $ db2 get snapshot for tablespaces on sample(db 명 ) 각테이블스페이스 (DMS) 마다의사용률을구해 80% 가넘지않도록합니다. Used Space( 사용된페이지수 )/Total Space( 총페이지수 ) * 100%< 80% 트랜잭션로그 가끔트랜잭션로그를일반로그로착각해삭제하는경우가있는데아래경로의파일은절대삭제하시면안됩니다 로그방식 (Circular / Retain) $ db2 get db cfg for sample(db 명 ) grep LOGRETAIN 값이 No 인경우는 Circular 방식이고, Recovery 인경우는 Retain 즉 Archive 방식입니다 Archive 디렉토리 $ db2 get db cfg for sample(db 명 ) grep LOGARCHMETH1 Archive 방식인경우값이 LOGRETAIN 인경우는아카이브된파일이로그디렉토리에저장이됩니다. 값이 Disk: 디렉토리위치 또는 TSM... 인경우는해당디렉토리에저장이됩니다. 146

147 로그디렉토리위치 $ db2 get db cfg for sample(db 명 ) grep Path to log files( 로그파일에대한경로 ) Active Log 디렉토리위치를나타냅니다 로그개수및크기 $ db2 get db cfg for sample(db 명 ) egrep LOGPRIMARY LOGSECOND LOGFILSIZ 로그파일시스템사용률이해당로그파일시스템사이즈보단작아야합니다. (LOGPRIMARY 값 + LOGSECOND 값 ) * LOGFILSIZ 값 <= Log File System 최대할당된 2 차로그공간 $ db2 get snapshot for db on sample(db 명 ) grep Secondary logs allocated currently( 현재할당된 2 차로그수 ) 이값이 2 이상이면 LOGBUFSZ 의값을증가시켜야합니다 애플리케이션동시성 Lock List 사용률 $ db2 get snapshot for db on sample(db 명 ) grep Lock List Memory In Use( 사용중인잠금목록메모리 ) $ db2 get db cfg for sample(db 명 ) grep LOCKLIST (Lock List Memory In Use) / (LOCKLIST) 의비율이높다면 LOCKLIST 의값을증가시켜야합니다 Lock Escalation 수 $ db2 get snapshot for db on sample(db 명 ) grep Lock escalations( 잠금에스컬레이션수 ) Row 레벨에서 Table 레벨의 Lock 으로자동업그레이드된횟수로이횟수는 0 에근접해야만합니다 Lock Timeout 수 $ db2 get snapshot for db on sample(db 명 ) grep Lock Timeouts( 잠금시간종료수 ) 오브젝트에대한 Lock 요청이 Timeout 된횟수로값이높다면 LOCKTIMEOUT (db cfg 값 ) 값을증가시켜야합니다 Lock Wait Time $ db2 get snapshot for db on sample(db 명 ) grep Lock waits( 잠금대기수 ) $ db2 get snapshot for db on sample(db 명 ) grep = Time Database Waited On Locks( 데이터베이스의잠금대기시간 ) 147

148 Lock 에대한평균 Wait Time 으로값이적어야하지만높다면 LOCKTIMEOUT (db cfg 값 ) 값을감소시켜야합니다. Lock 에대한평균 Wait Time = Time Database Waited On Locks(ms) / Lock Waits Deadlock 감지수 $ db2 get snapshot for db on sample(db 명 ) grep Deadlocks detected( 검출된교착상태수 ) Deadlock 발생횟수로값이적어야합니다 버퍼풀과 I/O 활동 버퍼풀적중률 $ db2 get snapshot for bufferpools on sample(db 명 ) 데이터에대한버퍼풀적중률 = (Buffer pool data logical reads Buffer pool data physical reads)/( Buffer pool data physical reads) 인덱스에대한버퍼풀적중률 = (Buffer pool index logical reads Buffer pool index physical reads)/( Buffer pool index physical reads) 각각의버퍼풀에대한데이터 / 인덱스에대한버퍼풀적중률이 95% 가넘는데좋으나적어도 80% 이상이될수있게버퍼풀의사이즈를조정해야합니다 Page Cleaner 에의한 Write 비율 $ db2 get snapshot for bufferpools on sample(db 명 ) Page Cleaner 에의한 Write 비율 ( 데이터 ) = (Asynchronous pool data page writes) / (Buffer pool data writes) Page Cleaner 에의한 Write 비율 ( 인덱스 ) = (Asynchronous pool index page writes) / (Buffer pool index writes) 각각의버퍼풀에대한데이터 / 인덱스에대한 Page Cleaner 에의한 Write 비율값이 90% 이상이면 NUM_IOCLEANERS (db cfg 값 ) 의값을줄이고 90% 이하면값을증가시킵니다 Dirty Page Steal Cleaner 횟수 $ db2 get snapshot for db on sample(db 명 ) grep Dirty page steal cleaner triggers 읽기전에 Dirty Page 가 Clean 되어야할횟수로값이매우적어야합니다. 만약 10 이상이면 CHNGPGS_THRESH (db cfg 값 ) 의값을줄여줍니다 Dirty Page Threshold Cleaner 횟수 $ db2 get snapshot for db on sample(db 명 ) grep Dirty page threshold cleaner triggers Page Cleaner 가너무많은 Dirty Page 로인해트리거된횟수로값이적어야합니다. 만약값이크면 CHNGPGS_THRESH (db cfg 값 ) 의값을줄여줍니다. 148

149 LSN Gap Cleaner 횟수 $ db2 get snapshot for db on sample(db 명 ) grep LSN Gap cleaner triggers Page Cleaner 가너무오래된 Dirty Page 로인해트리거된횟수로값이적어야합니다. 만약값이크면 SOFTMAX (db cfg 값 ) 의값을줄여줍니다 Direct Read/Write 횟수 > AIO $ db2 get snapshot for bufferpools on sample(db 명 ) Direct Reads / Direct Writes 는버퍼풀을사용하지 (Long/LOBs, 백업, 카탈로그오브젝트등 ) 않는 I/O 횟수로이값이크다면파일 I/O 로발생시키는횟수가많아지기에가급적이런원인이발생하지않도록원인을찾아수정합니다 Close 된 Database File 개수 $ db2 get snapshot for db on sample(db 명 ) grep Database files closed 파일핸들부족으로 Close/Reopen 된횟수로값이적어야합니다. 만약값이크다면 0 이될때까지 MAXFILOP (db cfg 값 ) 의값을증가시킨다 Prefetch 비율 $ db2 get snapshot for bufferpools on sample(db 명 ) Prefetch 비율 ( 데이터 ) = (Asynchronous pool data page reads) / (Buffer pool data physical reads) Prefetch 비율 ( 인덱스 ) = (Asynchronous pool index page reads) / (Buffer pool index physical reads) 복잡한쿼리워크로드에서 Prefetcher 에의해읽혀진 Physical Read 확률로각각의버퍼풀에대한데이터 / 인덱스에대한 Prefetch 비율값이높아야만합니다 Cache Package Cache 적중률 $ db2 get snapshot for db on sample(db 명 ) grep Package cache lookups $ db2 get snapshot for db on sample(db 명 ) grep Package cache inserts 패키지캐쉬에서패키지를찾는확률 = (Package cache inserts) / (Package cache lookups) 이확률이 90% 보다낮다면 PCKCACHESZ (db cfg 값 ) 의값을증가시킨다 Catalog Cache 적중률 $ db2 get snapshot for db on sample(db 명 ) grep Catalog cache lookups $ db2 get snapshot for db on sample(db 명 ) grep Catalog cache inserts 카탈로그캐쉬에서참조할확률 = (Catalog cache inserts) / (Catalog cache lookups) 149

150 이확률이 90% 보다낮다면 CATALOGCACHE_SZ (db cfg 값 ) 의값을증가시킨다 Sort Sort Overflow 비율 $ db2 get snapshot for db on sample(db 명 ) grep Sort overflows( 정렬오버플로우수 ) $ db2 get snapshot for db on sample(db 명 ) grep Total sorts( 총정렬수 ) Sort Heap ( SORTHEAP db cfg 값 ) 이부족하여디스크공간에저장할 Sort 의비율 = (Sort Overflows) / (Total Sorts) 이값이 DSS 가아닌 Workload 에서는 0 또는거의 0 이되게 SORTHEAP (db cfg 값 ) 의값을조정합니다 평균 Sort 시간 $ db2 get snapshot for db on sample(db 명 ) grep Total sort time( 총정렬시간 ) $ db2 get snapshot for db on sample(db 명 ) grep Total sorts( 총정렬수 ) Sort 당평균 Elapsed Time = (Total Sort Time) / (Total Sorts) Sort 당평균 Elapsed Time 이 원하는명령문응답 시간보다작게 SORTHEAP (db cfg 값 ) 의값을조정합니다 Post Threshold Sort 횟수 $ db2 get snapshot for dbm grep Post threshold sorts Sort Heap 임계값에도달한후 Heap 을요청한 Sort 개수로 DSS 가아닌 Workload 에서는 0 또는거의 0 이되게 SORTHEAP (db cfg 값 ) 의값을조정합니다.. 150

151 MON002. Utility Monitoring 1. Utility Monitoring 1.1 개요 데이터베이스에서유틸리티범주로분류되어모니터링및관리되는작업 ( backup, restore, load, runstats ) 들을모니터링할수있도록제공된명령어및유틸리티의사용법을설명합니다. 1.2 list Utility 명령을이용한 Monitoring( 상세정보포함 ) $db2 list utilities show detail Backup 모니터링실행결과예 ID = 2 Type = BACKUP Database Name = SAMPLE Partition Number = 0 Description = offline db Start Time = 10/30/ :55: State = Executing Throttling: Priority = 0 Progress Monitoring: Phase Number [CURRENT] = 1 Description = Work Metric = BYTES Total Work Units = Completed Work Units = Start Time = 10/30/ :55: Runstats 모니터링실행결과예 ID = 1 Type = RUNSTATS Database Name = PROD Partition Number = 0 Description = krrose.some_table Start Time = 12/19/ :54: State = Executing Invocation Type = User Throttling: Priority =

152 모니터링실행결과항목설명 항목 ID 설명유틸리티가수행될때부여받은아이디번호 ( 이번호를이용해서 Throttle 기능인 priority 를조정할때사용합니다. Priority value 가 0 이거나 100 일때는 Throttle 기능을사용하지않게되고 Priority value 가 1 에서부터높을수록시스템으로부터 resource 를자주할당받을수있게됩니다. ) Priority 변경명령구조 ) db2 set util_impact_priority for <id> to <priority value> 예 ) db2 set util_impact_priority for 2 to 10 Type 유틸리티의유형을의미합니다. ( backup, restore, runstats ) Database 유틸리티가실행중인데이터베이스이름입니다. Name Partition 데이터베이스가위치한파티션번호입니다. DPF 환경이아니라면 0 입니다. Number Description 유틸리티의설명 (comment) 입니다. Start Time 유틸리티가시작한시간입니다. State 유틸리티의실행상태입니다. Throttling Throttling 을사용하는지사용하는지 (1 99) 안하는지 (0, or 100) 한다면 Priority 항목값이얼마인지보여줍니다. Progress Monitoring Runstats 를제외한 backup, restore, load 유틸리티가구동중일때에는진행사항에대한좀더자세한내용을보여줍니다. 진행단계나전체수행예상량및현재진행량등을확인할수있습니다. 1.3 db2pd 유틸리티를이용한 Monitoring $db2pd db < 데이터베이스이름 > utilities 예 ) $db2pd db sample utilities Backup 모니터링실행결과예 Option utilities is an instance scope option. The database option has been ignored. Database Partition 0 Active Up 0 days 04:03:34 Utilities: Address ID Type State Invoker Priority StartTime DBName NumPhases CurPhase Description 0x7A904FA0 3 BACKUP Tue Jan 22 15:24:33 SAMPLE 1 1 online db Progress: Address ID PhaseNum CompletedWork TotalWork 152

153 StartTime Description 0x7A bytes bytes Tue Jan 22 15:24:33 n/a Runstats 모니터링실행결과예 Option utilities is an instance scope option. The database option has been ignored. Database Partition 0 Active Up 0 days 04:04:41 Utilities: Address ID Type State Invoker Priority StartTime DBName NumPhases CurPhase Description 0x7A904FA0 4 RUNSTATS Tue Jan 22 15:25:42 SAMPLE 1 1 ROBIN.EMPLOYEE Progress: Address ID PhaseNum CompletedWork TotalWork StartTime Description 모니터링실행결과항목설명 대분류항목소분류항목설명 Utilities ( 기본적인진행정보입니다.) Progress ( 진행상세정보를나타냅니다.) Address 프로세스가사용하는메모리주소입니다. ID 부여받은아이디번호입니다. Type 유틸리티유형정보입니다. State 진행상태플래그입니다. Invoker 유틸리티가 invoking 된타입입니다. 크게두가지로표현되며, 유저가실행한경우와 DB2 시스템이실행한경우로표현됩니다. Priority Priority 값입니다. StartTime 시작한시간입니다. DBName 실행되는데이터베이스이름입니다. NumPhases 전체실행단계정보입니다. 예를들어 Load 의경우두 단계로수행됩니다. CurPhase 현재수행되고있는단계값입니다. 실행이몇단계까지수행되고있는지확인할수있습니다. Description 설명 (comment) 입니다. Address ( Utilities 섹션의 Address 와동일합니다. ) ID ( Utilities 섹션의 ID 와동일합니다. ) PhaseNum ( Utilities 섹션의 CurPhase 값과동일합니다. ) CompletedWork 현재까지수행한데이터량 (byte) 입니다. TotalWork 전체수행해야할데이터량 (byte) 입니다. 153

154 StartTime 현재단계가시작한시간입니다. Description 설명 (comment) 입니다. 154

155 MON003. db2pd,db2top 1. db2pd 1.1 특징 DB2 데이터베이스와인스턴스의모니터링트랜잭션, 테이블스페이스, 테이블통계, 동적 SQL, 설정등을지속적으로트래킹일정주기로실행가능 1.2 사용 sysadmin 권한을가져야함, Unix or Linux 에서는인스턴스 owner 여야함 실행파일 : $INSTANCE_HOME/sqllib/adm/db2pd 도움말 : 에서 db2pd 검색 인스턴스 / 데이터베이스레벨로사용가능 범위한개인스턴스특정데이터베이스전체데이터베이스특정파티션전체파티션 옵션 inst or ins database n or db n alldatabase or alldb dbpartitionnum n or dbp n alldbpartitionnums or alldbp 1.3 사용법 인스턴스레벨 옵션 설명 Scope agents 에이전트 인스턴스 dbmcfg database manager 구성파라미터 인스턴스 fcm fast communication manager 인스턴스 version DB2 버전및레벨 인스턴스 sysplex 전체혹은특정데이터베이스 alias 인스턴스 osinfo 운영체제 인스턴스 mempools 메모리풀 Both memsets 메모리셋, 인스턴스공유메모리영역에남아 Both 있는미사용메모리의양을모니터 memblock 메모리사용현황 Both help 도움말 NA 데이터베이스레벨 옵션설명 Scope applications 현재및마지막 Anch ID 와동적 SQL 문의데이터베이스명령문고유 ID 를보고. 이를통해응용프로그램에서동적 SQL 문으로직접맵핑할 155

156 수있다. bufferpools 버퍼풀 데이터베이스 Catalogcache 카탈로그캐시 데이터베이스 dbcfg 데이터베이스파라미터 데이터베이스 dynamic dynamic SQL 데이터베이스 logs 로그파일명, 크기, 현재로그 데이터베이스 locks locks 데이터베이스 recovery 복구가진행중인지검증할때사용, 현재로그 데이터베이스 및현재 LSN 에서는로그위치, CompletedWork 는현재복구량 reopt reopt 옵션을사용한 Cached SQL 데이터베이스 reorg 테이블 reorg 데이터베이스 static execution of static SQL and packages 데이터베이스 tablespace 테이블스페이스 데이터베이스 tcbstats 테이블, 인덱스, 테이블정보, insert/update/delete 확인 데이터베이스 transactions 잠금수, 첫번째 LSN, 마지막 LSN, 사용된 데이터베이스 로그스페이스및예약된공간 사용예 # db2pd <interactive mode 에서실행 > # db2pd agent < 현재 agent 정보 > # db2pd command file.out <file.ot 으로출력파일설정 > # db2pd db sample # db2pd memsets file=m.txt repeat 2 5 <m.txt 에결과저장, 2 초간격으로 5 번반복 > # db2pd version osinfo <DB2 레벨 OS 정보 > # db2pd db sample reorg <reorg 상태파악 > # db2pd memb pid= < 개인메모리에대한메모리블록 > # db2pd db sample hadr <hadr 상태확인 > Lock 을잡고있는사용자찾기 # db2pd database sample locks transactions agents # db2pd db sample loc tra age ( 약자사용 ) IX 를가진 6 번 id 로사용자가 db2inst1 이라는것을알아냄 156

157 1.3.5 Isolation # db2pd db sample dynamic * AnchID : hashed anchor identifier 157

158 1.3.6 Table space # db2pd db sample tablespace 2. db2top 2.1 다운로드및설치 에서받고플랫폼별로실행파일하나로구성 2.1 실행 # su db2inst1 # db2top < 도움말이나옴 > # db2stop d sample i 2 (sample db, refresh 간격을 2 초 ) SQL 표시 위의처음화면에서 D 를입력하면 Dynamic SQL 표시됨 158

159 2.1.2 SQL 확인 D (Dynamic SQL) 화면에서 L 을누른후 HashValue 입력하면 SQL 전체를확인가능 Memory Pool 별 Memory 확인 159

160 2.1.4 Statement 현재가장많이 Read/Write 되는테이블확인 (T) RowsRead/s : 테이블의현재초당 Read 양 (page 수 ) RowsWritten/s : 테이블의현재초당 Write 양 (page 수 ) Data Pages : 현재테이블의크기 (page 수 ) Bufferpool(b) IBMDEFAULTBP 예 l_reads/s (logical reads) : 6985*4k=27M/s p_reads/s(physical reads) : 6743*4k=26M/s t(tablespace) 160

161 2.1.8 U(Lock) d(database) l(session) 세션화면에서 a 를눌러 agent id 를입력하면상세정보를볼수있는화면으로들어간다 161

162 162

163 MON004. Event Monitor 1.Database 1.1 설정 # db2 ʺcreate event monitor ev_database for database write to table manualstartʺ * 마지막 application 이종료되는시점에기록 * 3 개의테이블이생성됨 select varchar(evmonname,20) evmonname,case when target_type=ʹfʹ then ʹFileʹ when target_type=ʹtʹ then ʹTableʹ end TargetType,autostart,monscope,case when event_mon_state(evmonname) =0 then ʹStoppedʹ when event_mon_state(evmonname) =1 then ʹStartedʹ end state from syscat.eventmonitors # SET EVENT MONITOR ev_database STATE 조회 select evmon_activates,rows_deleted ʺDeletedʺ,rows_inserted ʺInsertedʺ,rows_read ʺReadʺ,rows_selected ʺSelectedʺ,rows_updated ʺUpdateʺ,log_writes,pool_data_l_reads ʺL_Readsʺ,pool_data_p_reads ʺP_Readsʺ,pool_data_writes ʺP_Writesʺ from DB_EV_DATABASE 163

164 1.3 삭제 # db2 ʺset event monitor ev_database state 0ʺ # db2 ʺdrop event monitor ev_databaseʺ 2. Tables 2.1 생성 # db2 ʺcreate event monitor ev_table for tables write to table manualstartʺ # db2 ʺset event monitor ev_tables state 1ʺ 2.2 조회 select varchar(a.table_schema,10) schema,varchar(a.table_name,15) table_name,a.rows_read ʺReadʺ,a.ROWS_WRITTEN ʺWriteʺ,a.Data_Object_Pages ʺPagesʺ,a.TABLE_TYPE ʺTypeʺ,varchar(b.TBSPACE,15) tablespace_name,event_time from TABLE_EV_TABLE a, syscat.tablespaces b where a.tablespace_id=b.tbspaceid and table_schema!=ʹsysibmʹ * pages : table 의전체페이지 2.3 삭제 # db2 ʺset event monitor ev_table state 0ʺ # db2 ʺdrop event monitor ev_tableʺ # db2 ʺdrop table CONTROL_EV_TABLEʺ ( 삭제하지않으면나중에동일이름을 event 로생성시뒤에 2 가붙은이름으로테이블이생성됨 ) # db2 ʺdrop table TABLE_EV_TABLEʺ 164

165 3. Statements 3.1 생성 # db2 ʺcreate event monitor ev_statements for statements where appl_name = ʹdb2bp.exeʹ write to table manualstartʺ * 연결된 app 가 db2bp.exe # db2 ʺset event monitor ev_statements state 1ʺ 3.2 조회 select agent_id id,rows_read,rows_written,start_time,system_cpu_time,total_sort_time,total_sorts,user_cpu_time,stmt_text from STMT_EV_STATEMENTs where rows_read>0 3.3 조회 # db2 ʺset event monitor ev_statements state 0ʺ # db2 ʺdrop table CONTROL_EV_STATEMENTSʺ # db2 ʺdrop table CONNHEADER_EV_STATEMENTSʺ # db2 ʺdrop table STMT_EV_STATEMENTSʺ 165

166 MON005. Health Center 1. 구성 $ db2hc 혹은제어센터에서 Health Center 시작 166

167 1.1 인스턴스설정값 마우스오른쪽 > 편집 임계값을초과한경우 DB2 스크립트나 OS 명령스크립트사용가능. 167

168 1.2 전역설정값 168

169 1.3 오브젝트설정값 2. 사용예 2.1 테이블스페이스가임계치를넘은예 아래는테이블스페이스 USRD 의사용률이 100% 에도달했음을표시. 169

170 170