Microsoft PowerPoint - DB2 9 vs Oracle 10g Admin.Technology( ).ppt

Transcription

1 IBM Software Group Admin. Technology IBM DB2 Open Forum 3 rd Version 2.0 Aug. 20, 2006 Burt Vialpando & Vikram Khatri 2008 IBM Corporation

2 Agenda DB2 Information Management Topics DB2 소개및개요 DB2 인스턴스및 CLP DB2 데이터베이스 DB2 백업및복구 DB2 HADR(High Availability Disaster Recovery) DB2 동시성 2008 IBM Corporation 2

3 IBM Software Group DB2 소개및개요 IBM DB2 Open Forum 3 rd 9 slides 2008 IBM Corporation

4 제품패키징비교 DB2 Information Management Oracle Oracle Lite Windows CE, Palm, EPOC, Windows 95/98/NT Oracle Personal Edition Windows Oracle Standard Edition Windows CE, LINUX (2 dist.), AIX, HP-UX, HP Compaq, Solaris, Tru64 Oracle Enterprise Edition Windows CE, LINUX (2 dist.), AIX, HP-UX, HP Compaq, Solaris, Tru64 RAC is an optional purchase feature DB2 DB2 Everyplace 9 Windows CE, Palm, EPOC, Windows XP/Tablet PC, QNX Neutrino, Symbian, elinux, Linux on PC DB2 Express 9 Windows, Linux (200 dist.) DB2 Workgroup 9 Windows, Linux (200 dist.), AIX, HP-UX, Solaris DB2 Enterprise 9 Windows, Linux (200 dist.), AIX, HP-UX, Solaris, Linux/390 Database Partitioning Feature optional purchase DPF Try DPF or extenders free with licensing update ability! 2008 IBM Corporation 4

5 DB2 Express-C 9 무료라이센스제공 DB2 Express-C 9 is: Production Ready Innovative Technology Developer Friendly Free to develop, deploy and redistribute* No database size limit No limit on number of instances per server No user limit * Free registration required Same superior data server technology and programming support as DB2 Express Edition in a smaller package DB2 Edition Comparisons Dedicated team for DB2 community participation & development Free online community support Skills & applications applicable to all editions of DB2 Express 9/Express-C 9 Workgroup 9 Enterprise 9 Linux & Windows, 32 and 64-bit platforms 1-2 CPU Server 4GB Memory Linux, UNIX & Windows, 32 and 64-bit platforms 1-4 CPU Server 16GB Memory Linux, UNIX & Windows, 32 and 64-bit platforms No limits Partitioning feature 2008 IBM Corporation 5

6 동일한코드기반 100% Common Code Base Pros & Cons A 100% common code base would Allow for faster distribution of releases & fixes Give a higher likelihood of identical functionality Give similar installation procedure for all platforms A 100% common code base would not Benefit from performance & functionality strengths inherent to specific platforms Oracle Solution Oracle develops on a few OS s, then ports to others IBM DB2 Solution DB2 data server has about a 90% common code base for LUW Varying by about 10% for operating system & hardware differences All supported OS versions for LUW released simultaneously IBM develops on the platforms it supports to optimize the code for them Porting is not done DB2 z-os, iseries and VM/VSE products are a different code base Which take advantage of these platforms DB2 end users & DBAs see little difference externally Mostly for OS considerations Using DPF on LUW does not require a new code install 2008 IBM Corporation 6

7 툴및제품관리 & 개발툴 & 제품 Oracle IBM Description of Usage Oracle 10g Enterprise Edition Oracle Enterprise Manager Enterprise Login Assistant Database Configuration Assistant Administration Assistant Oracle SQL Plus Worksheet isql Oracle SQL Plus Oracle Net Config. Assistant Oracle SQL Analyze ADDM Oracle Forms Developer Oracle Progress Monitor Oracle Procedure Developer Oracle Spatial & Intermedia Oracle Time Series COM Cartridge DB2 Enterprise 9 DB2 Control Center DB2 Web Query Tool DB2 Recovery Expert DB2 High Performance Unload DB2 Performance Expert DB2 Command Editor DB2 Command Line Processor DB2 Configuration Assistant DB2 Visual Explain db2exfmt, db2expln DB2 Event Monitor / Analyzer DB2 Table Editor Developer Suite DB2 Query Patroller(9.5 Workload Management) DB2 Developer Workbench DB2 Relational Extenders IBM Content Manager (and family of products which are many) Relational Database Management System GUI database master console, control, management, auto login, recovery, etc. GUI level line oriented & script saving database access OS level line oriented database access Node cataloging & connecting, networking Query analysis & visual explains Database event info. collection & analysis Forms creation, development, usage Query & session monitoring Stored procedure & other object development Image searching, content management 2008 IBM Corporation 7

8 툴및제품통신 & 연결툴 & 제품 Oracle Files Online (ifs 8i) Oracle Transparent Gateway Oracle Access Managers for CICS, IMS, DB2 Advanced Replication Message Broker Oracle Streams Internet Commerce Server Internet Application Server IBM Data Links Manager DB2 Discovery Link DB2 Connect, DB2 Relational Connect WebSphere Information Integrator MQ Series DB2 Q Replication DB2 SQL Replication WebSphere Portal Studio IBM Enterprise Information Portal Description of Usage File system for Internet use Federation Replication, message queuing Internet servicing, connection 2008 IBM Corporation 8

9 툴및제품인터넷, 자바, 데이터웨어하우징 & OLAP 툴 & 제품 Oracle JDeveloper Oracle Designer Oracle Portal (WebDB) Oracle Objects Oracle Data Mart Suite Warehouse Builder Oracle Pure*Integrate One Meaning Oracle Express Server Oracle Express Objects Oracle Discoverer Oracle Reports Oracle Data Mining Suite Oracle Darwin IBM Visual Age Family WebSphere Studio Net.Data IBM Content Manager DB2 Data Warehouse Manager DB2 Data Propagator IBM Data Refresher WebSphere Inf. Integrator Vality Integrity Information Catalog DB2 OLAP Server Data Warehouse Center QMF DB2 Web Query Tool for Multiplatforms DB2 Intelligent Miner DB2 OLAP Server Miner Description of Usage Java & web development & object modeling Data Warehouse building, data movement, management Data cleansing Metadata sourcing OLAP, data marts, cubes Reporting in all its forms Data mining 2008 IBM Corporation 9

10 툴및제품 DB2 GUI 로드맵 Configuration Assistant (db2ca) DB2 Desktop Folder x DB2 Command Window (db2cmd) DB2 Command Line Processor (db2) Command Editor (db2ce) Alert Center Visual Explain Replication Center (db2rc) Task Center (db2tc) Smart Wizards Guides or Wizards Activity Monitor (db2am) Control Center (db2cc) Health Center (db2hc) Journal Development Center (db2dc) Satellite Administration Center SQL Assist Tool Settings License Center Data Warehouse Center First Steps (db2steps) x Information Folder (db2ic) Tools that can be started from a command prompt show the command in parenthesis Most others are accessed through the Control Center & Command Center 2008 IBM Corporation 10

11 툴및제품 타벤더예 Quest Software Support of DB2 Toad for DB2 Toad for DB2 simplifies database development and allows database developers to be more productive by providing an intuitive graphical user interface to DB2. With Toad, developers can use a single, industry-proven tool for both development and DB2 object management. Quest Central for DB2 Quest Central for DB2 is an integrated suite of tools for managing DB2 on Linux, Unix, Windows, and z/os. Quest Central provides complete database administration, SQL tuning, space management, and performance diagnostics across all platforms from a single console. Quest SQL Optimizer for DB2 SQL Optimizer for DB2 UDB offers complete SQL performance optimization by proactively detecting inefficient SQL and automatically rewriting and selecting best alternatives to maximize performance Knowledge Xpert for DB2 Knowledge Xpert for DB2 is a comprehensive, Windows-based technical resource designed to give you quick access to expert advice to solve common and complex management issues for DB2 on z/os and UDB for NT and Unix. Benchmark Factory Benchmark Factory is a load testing solution that prevents unplanned downtime and slow performance by testing your system's limits before you go live. DataFactory DataFactory populates development, test and QA databases with millions of rows of meaningful, syntactically correct test data, providing you with a realistic view of application performance before going live. Foglight Cartridge for Databases - DB2/UDB Foglight is a powerful console that monitors every tier in your critical application technology stacks, including databases, networks, application servers, process schedulers and Web servers, alerting you to problems before they impact end users. This cartridge provides 24x7 monitoring for complex DB2/UDB database environments. Spotlight on DB2 Spotlight on DB2 diagnoses DB2 UDB and z/os performance problems in real time by graphically displaying all database activity in an intuitive user interface, enabling you to respond quickly to issues that need attention IBM Corporation 11

12 DB2 개발연구소 Development 3,000+ Sales / Mktg. 3,000+ Partners 16,000+ Rochester Toronto Boeblingen Menlo Park, Oakland, Portland, Silicon Valley Almaden Research Denver Austin Somers Lenexa Hawthorne Research Yamato Over 6,000 Strong WW Data Management Research Strong Partnering 2008 IBM Corporation 12

13 DB2 History On Linux, Unix and Windows (LUW) V1.x 1993 OS/2 and AIX platform Low-end OLTP V2.x 1995 Multiple platforms Capacity and performance JDBC UDTs, UDFs, LOBs Low-end OLTP V5.x 1997 SMP and MPP parallelism Data Links Manager SQLJ Initial OLAP SQL V DB2 for Linux DB2 Satellite Edition Ease of Use Ease of Application Development Performance V7.x 2000 Integration Warehousing Integrated OLAP Federated databases Native OLE DB Client Spatial extender XML extender Abstract Data Types SQL stored procedures TPC-H Leadership V , V Multidimensional Clustering SMART technology Expanded Linux support Online utilities Advanced BI, CubeViews TPC-C leadership Information Integration SAP Optimization V , V Self Tuning Memory Automation automatically Advanced SQL Data Compression 2008 IBM Corporation 13

14 IBM Software Group DB2 인스턴스및 CLP IBM DB2 Open Forum 3 rd 25 slides 2008 IBM Corporation

15 DB2 인스턴스개요 인스턴스 = Database Manager 인스턴스가하는일 : 데이터베이스메모리사용을위한디폴트값 - 예 : Utility impact limits Sort, hash, Java and other heap limits and defaults 모니터링 Health Snapshot 감사기능 진단및 & 통지내용로깅 연결관리 User authentication Communication protocols Agents & pooling management Transaction management (aka sessions in Oracle-speak) 연합데이터베이스기능제어 분산트랜잭션관리 2008 IBM Corporation 15

16 서버프로세스아키텍쳐 The Oracle SID Oracle SID Parameter file Control files Password file Archived log file Data filesdata files Database proddb1 Redo log files An Oracle SID (or unique instance name) can 하나의데이터베이스만허용 인스턴스 = 물리적인구조를액세스하기위한논리적인메모리구조 데이터베이스 = 물리구조 인스턴스와데이터베이스용어가동일하게사용 그러나기술적으로다른의미 SET ORACLE_SID = inst1 SQLPLUS> connect internal SQLPLUS> startup 2008 IBM Corporation 16

17 서버프로세스아키텍처 The DB2 Instance Catalog Log DB2 Instance Database proddb1 DBconfig Buffer Pools Catalog Log Database proddb2 DBconfig Buffer Pools DBMconfig A DB2 instance can 하나이상의데이터베이스허용 대부분운영시스템은하나의데이터베이스사용 A DB2 instance is a logical access to The databases logical structures Then to the databases physical structures In a DB2 instance 프로세스는데이터베이스들간공유 인스턴스구성변수 (dbm cfg) 가모든데이터베이스에공유 각각의데이터베이스는각자의데이터베이스구성변수 (db cfg) 를소유 DB2 terms "instance" & "database" have distinct meanings & purposes SET db2instance = inst1 db2start 2008 IBM Corporation 17

18 서버프로세스아키텍처 Oracle Processes List (Not Exhaustive) P1 P2 P3 P4 Level N/A Process Server LGWR DBWn * PMON SMON MMAN MMOM MMNL CKPT RECO ARCn * SNPn * LCKn * Dnnn * & Snnn* RVWR CTWR ASM QMNn * Purpose Server processes - Either dedicated or shared, performs requests for the client Log Writer - Performs sequential writes from the redo log buffer in the SGA to the redo log file(s) Database writer - Writes the dirty buffers from the database buffer cache to the data file(s) Process monitor - Cleans up failed user processes and frees of resources of these processes Space monitor - Space maintenance & crash recovery agent Memory Manager - SGA memory broker Memory Monitor AWR problem detection and self tuning Memory Monitor Light - Transfer statistics AWR collects to disk Check point Flushes modified data from the buffer cache to the disk and updates the control file and data files by doing a header update (DBWn actually does the write) Resolve for distributed databases - (DISTRIBUTED_TRANSACTIONS mode) Archive log monitor - Writes logs to archive areas (in ARCHIVE LOG mode) Snapshot & job scheduler Inter-instance locking - for multiple instances for one database (RAC mode) Dispatchers - for shared server, & Shared Sever processes Recovery Writer - Flashback support Change Tracking Writer RMAN support Automatic Storage Management - related processes are ASMB, ARB, RBAL Queue monitor for advanced queuing can be many 2008 IBM Corporation 18

19 서버프로세스아키텍처 DB2 Processes List (Not Exhaustive) P1 P2 P3 P4 Level Process Purpose Like? Instance db2sysc System controller - exists for the database server to function db2gds Global daemon spawner - UNIX systems only db2wdog Watchdog - UNIX systems only, handles abnormal termination PMON & SMON db2resyn Re-sync agent - scans the global re-sync list db2cart Invokes user exits to archive or retrieve log files (Archive Log) db2dart Pre-allocates log files for database with log retain set on (Archive Log) db2ipccm Inter-process listener - handles local client programs (DPF) Server db2fcmdm Fast communications manager daemon - handles inter nodal communication (DPF) db2pbc Parallel system controller - handles parallel requests from remote nodes (DPF) db2tcpcm, & others * TCP/IP communication listener, & other listeners (depending upon which protocols are specified in the DB2COMM variable) db2fmp Health Monitor process MMML Database db2loggr Logger - manipulates log files to handle transaction processing & recovery LGWR db2dlock Deadlock detection db2uext2 Archive log monitor ARCn db2pfch0 * Pre-fetcher db2pclnr1 Page & buffer pool cleaner DBWn & MMML Application db2agent * Either a process or thread, depending upon the OS Server * can be many 2008 IBM Corporation 19

20 서버프로세스아키텍처 DB2 Process Model P1 P2 P3 P4 1. 클라이언트프로그램은리스너를통해 Coordinator agent 를할당받음 2. 연결요청은 coordinator agent 를통해이루어짐 3. db2fmp 는 fenced SPs / UDFs 실행 2008 IBM Corporation 20

21 메모리구조 Oracle PGA Stack Space Data Buffers SGA Large Pool (optional) Shared Pool Library Cache Fixed SGA Variables Java Pool (optional) Dictionary Cache Parameters Events Session Info Enqueues Fixed Tables Control Structures Sort Area Redo Buffers State Objects Statistics Latches System Global Area (SGA) - 서버프로세스간통신, statement cache, redo log buffers, data buffer cache Program Global Area (PGA) - 응용프로그램과데이터베이스간통신 User Global Area (UGA) - user context & row-cached cursors 2008 IBM Corporation 21

22 메모리구조 DB2 Database Manager Shared Memory Monitor Heap mon_heap_sz Audit Buffer audit_buf_sz Database Shared Memory Utility Heap util_heap_sz backbufsz restbufsz buffer pool Locklists Buffer Pools buffpage buffer pool buffer pool buffer pool locklist Extended Memory Cache estore_seq_sz Shared Sort Memory dbheap logbufsz Catalog catalog_cache_sz pckcachesz Local Client Application Global Memory app_ctl_heap_sz Remote Client Agent & App Shared Memory aslheapsz agent_stack_sz query_heap_sz Agent Private Memory stat_heap_sz applheapsz sortheap drda_heap_sz udf_mem_sz stmtheap rqrioblk Application Shared Memory rqrioblk Database Manager Shared Memory- 인스턴스공유메모리 Database Shared Memory - 데이터베이스공유메모리 Application Shared Memory - 응용프로그램공유메모리 Agent Private Memory - 에이전트정보 : sort heaps, cursor info & session contexts 2008 IBM Corporation 22

23 중요파일들 Oracle Manually Handled Key Oracle files Oracle has a control file(s) which Oracle 복구에필수파일 Control Parameter 하나이상의복사본을관리 file files 별도백업필요 Painstakingly maintained (e.g. ALTER DATABASE BACKUP CONTROL FILE TO...) Oracle has parameter files which 인스턴스시작을위한필수파일 OS버전에따라하나이상존재가능 혼란가능성별도백업필요 / painstakingly maintained 재생성가능하나번거로움 Oracle 9i does have a new "server parameter file" (SPFILE) option... are you using it? Still requires a PFILE for database creation Oracle has a security (password) file which 리모트데이터베이스관리를위한파일 OS에따라하나이상의버전이존재가능 혼란가능성별도백업필요 / painstakingly maintained 재생성가능 Security file 2008 IBM Corporation 23

24 중요파일들 DB2 Automatically Handled 각데이터베이스당 Control 및 parameter 파일들이존재함 데이터베이스의스스로자동다중관리 : SQLBP.1 & 2 (buffer pool info) SQLSPCS.1 & 2 (table space info) SQLOG* (log control files) SQLDBCON & F (db configuration) db2rhist.asc &.bak (recovery history file) Backup 명령에의해자동백업됨. Restore / Recover 명령에의해자동복구됨 별도의백업가능 dbm cfg 파일 ( db2system ) (or the entire subdirectory it is in) Registry 파일 (db2set) DB2 가정 - 설치및관리자 기본적으로쉬운유지관리를하도록 기술력과 OS 에대한지식이그다지높지않아도가능하게 나중에원하는바대로조정하고보다복잡하게구성하도록 2008 IBM Corporation 24

25 Oracle 디렉토리구조 Windows Example (Using OFA) \oracle ORACLE_BASE \product Product directory \ ORACLE_HOME \admin \mydb1 Database \bdump \cdump \create \pfile \db_1 \udump \network \bin \admin sqlplus.exe sqlldr.exe rman.exe, etc listener.ora tnsnames.ora sqlnet.ora, etc IBM Corporation 25

26 DB2 디렉토리구조 Windows Example \DB2 Instance Name \node0000 Partition number (note: in ESE non DPF databases are a single node implementation) \sql00001 Database ID \SQLOGDIR \SQLT \SQLT \SQLT Default LOG directory SYSCATSPACE table space (always created) TEMPSPACE1 table space (always created) USERSPACE1 table space (always created) \program files \mysmsts \mydmsts.dat SQLTAG.NAM. This file marks the subdirectory as being in use SQL*.BKM (contains block allocation information if it is an MDC table) SQL*.LF (contains LONG VARCHAR or LONG VARGRAPHIC data) SQL*.LB (contains BLOB, CLOB, or DBCLOB data) SQL*.LBA (contains allocation and free space information about SQL*.LB files) SQL*.INX (contains index table data) SQL*.IN1 (contains index table data), etc. \IBM \sqllib db2start.exe db2stop.exe db2cmd.exe, etc IBM Corporation 26

27 환경 / 레지스트리변수 Oracle 환경변수 Variable ora_cwd ora_sid_pfile oracle_home oracle_sid oracle_base nls_lang rdbms_archive rdbms_control sqlpath Function Current Oracle directory Path to init.ora file Environment where products run Default Instance (SID) Root of the Oracle directory tree Language, character & territory set Location of backup database files Location of backup database control files Location of SQL scripts DB2 환경변수 Use of the DB2 profile registry allows for changes without a system reboot Use db2set or use the Configuration Assistant DB2 environment variables (OS controlled) are: db2path, db2instance Variable db2adminserver db2comm db2include db2instance (e) db2instdef db2owner db2slogon db2path (e) db2system Function Specifies which instance runs the admin. server Started communications manager Path to include in SQL searches Current instance Default instance Instance owning machine Enables secure logon Directory where product is installed Server name id 2008 IBM Corporation 27

28 INIT.ora 와 DB2 파라미터 DB2 는 50 개이상의변수가변경즉시반영됨! Oracle 10g init.ora parm control_files db_nk_cache_size db_block_buffers db_block_checking db_block_checksum db_create_online_log_dest_n db_block_size db_files db_keep_cache_size db_recycle_cache_size db_name db_writer_processes DB2 9 Equivalent no equivalent parm needed no equivalent parm needed no equivalent parm needed see: buffpage no equivalent parm needed no equivalent parm needed newlogpathn pagesize maxtotfilop no equivalent parm needed no equivalent parm needed no equivalent parm needed num_iocleaners Comments SQLBP.1, SQLSPCS.1, SQLOG*, SQLDBCON, db2rhist.asc. Multiple files in DB2 perform this functionality. Create as many buffer pools as you want of any page size Same as above. If no buffer pools are created then use buffpage db cfg parm. DB2 uses consistency bits to maintain page level integrity. This performs better then checksum page checking. Cannot be turned off. Same as above. Specifies where to change the log files location. db cfg parm. Defined when database is created. Maximum open files. dbm cfg parm. To have data pages kept in memory, create a buffer pool specifically for tables you want to pin. DB2's buffer pool algorithm is more advanced (not just LRU) so you don't need to specify recycle pools. With DB2 the database name is defined with create database command and not by a config parm. You can see the name (and provide aliases for that name) with the db2 catalog command and/or the db2 list database directory command. Number of asynchronous page cleaners. db cfg parm IBM Corporation 28

29 INIT.ora vs. DB2 Parameters (Continued) dml_locks Oracle 10g init.ora parm java_pool_size local_listener log_archive_dest log_buffer log_checkpoint_interval optimizer_* parallel_max_server sessions shared_pool_size sort_area_size hash_area_size user_dump_dest DB2 9 Equivalent no exact equivalent - see: locklist & maxlocks java_heap_sz svcename db2comm logarchmeth1 logbufsz softmax no exact equivalent - see: dft_queryopt dft_degree maxcagents no exact equivalent - see: dbheap no exact equivalent - see: sortheap diagpath Comments DB2 locks are held in memory & are a set of db cfg parms for sizing this lock information. dbm cfg parm. Specifies the port DB2 will listen on. dbm cfg parm. You must also specify the protocol as a registry variable. db cfg parm. Specifies the size of the log buffer. db cfg parm. Checkpointing. db cfg parm. The DB2 optimizer is fully cost based so there are no system level modes. The one exception is the dft_queryopt db cfg parm which indicates how much optimization DB2 should do. Specifies the degree of parallelism to use if you want to override DB2s autonomics. db cfg parm. Maximum number of concurrently connected applications. dbm cfg parm. No shared pool in db2. The closest parm would be the dbheap db cfg parm. DB2 has a global size for all sorts and hash joins using sortheap db cfg parm. Path to diagnostic dump data. dbm cfg parm IBM Corporation 29

30 CLP 모드 DB2 Information Management CLP 는 3 가지모드지원 Mode Command Batch Interactive Noted By OS prompt, prefaced with a db2 before each command OS prompt, db2 and an -f option to designate the input file CLP prompt, default of which is db2 => The command mode Is invoked from an OS shell window with db2cmd [return] db2cmd.exe is an extension of the Windows OS shell Invokes OS commands directly Invokes DB2 commands with db2 db2-command The batch mode Is like running Command Window commands en-masse The interactive mode Is invoked from the Command Window with db2 [return] Requires a! to invoke OS commands 2008 IBM Corporation 30

31 CLP 도움말 C:\ db2? db2 [option...] [db2-command sql-statement [? [phrase message sqlstate class-code]]] option: -a, -c, -e{c s}, -finfile, -lhistfile, -o, -n, -p, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile. db2-command: ACTIVATE DATABASE GET/UPDATE DB CFG RECONCILE ADD CONTACT GET/UPDATE DBM CFG RECOVER ADD CONTACTGROUP GET DBM MONITOR SWITCHES REDISTRIBUTE DB PARTITION etc., GET CONTACTGROUPS QUIT GET CONTACTS REBIND UPDATE MONITOR SWITCHES Note: Some commands are operating system specific and may not be available. For further help:? db2-command - help for specified command? OPTIONS - help for all command options? HELP - help for reading help screens The preceding three options can be run as db2 <option> from an OS prompt.!db2ic - DB2 Information Center (Windows only) This command can also be run as db2ic from an OS prompt. C:\ db2? sql0100 [this is an example of an SQL message help request] Tip: db2? help helps you read and use help screens db2 help launches the Information Center 2008 IBM Corporation 31

32 CLP 옵션 DB2 Information Management db2 => list command options Command Line Processor Option Settings Backend process wait time (seconds) (DB2BQTIME) = 1 No. of retries to connect to backend (DB2BQTRY) = 60 Request queue wait time (seconds) (DB2RQTIME) = 5 Input queue wait time (seconds) (DB2IQTIME) = 5 Command options (DB2OPTIONS) = Option Description Current Setting a Display SQLCA OFF -c Auto-Commit ON -e Display SQLCODE/SQLSTATE OFF -f Read from input file OFF -l Log commands in history file OFF -n Remove new line character OFF -o Display output ON -p Display interactive input prompt ON -r Save output to report file OFF -s Stop execution on command error OFF -t Set statement termination character OFF -v Echo current command OFF -w Display FETCH/SELECT warning msgs ON -x Suppress printing of column headings OFF -z Save all output to output file OFF db2 => update command options using c OFF [turns this off] db2 => set DB2OPTIONS=+c [Windows alternative for the same thing] db2 => export DB2OPTIONS= +c [UNIX alternative for the same thing] Tip: Leaving the interactive mode turns off your db2option settings! ? options is different from list command options Many db2option switches can be temporarily overridden in a batch mode 2008 IBM Corporation 32

33 CLP History 및 runcmd 명령 Command history [reverse] [n] displays the last commands cached 20 ascending is default display and 20 is the default cached Set db2_clp_histsize registry variable to change cache size runcmd runs the numbered command from the history cache These ONLY work in interactive mode db2 => history db2 => history 1 connect to sample 2 list command options 3 select * from sysibm.sysdummy1 20 select * from customer db2 => db2 => runcmd 3 db2 => runcmd 3 IBMREQD Y 1 record(s) selected db2 => 2008 IBM Corporation 33

34 CLP Command 창배치모드사용예 Batch or shell files can execute saved DB2 command files These can produce yet other output files for various purposes Here is a Generate RUNSTATS commands example x to suppress column headings td to set the end of command delimiter f to indicate input file name z to indicate output file name CLPxmp01.bat db2 connect to sample db2 -x -td@ -f CLPxmp01.db2 -z CLPxmp01OUT.txt db2 commit work db2 connect reset CLPxmp01.db2 select 'RUNSTATS ON TABLE' rtrim(tabschema) '.' tabname ON ALL COLUMNS ALLOW WRITE ACCESS;' from sysstat.tables where card = -1@ CLPxmp01OUT.txt RUNSTATS ON TABLE SYSIBM.SYSVIEWSON ALL COLUMNS ALLOW WRITE ACCESS; RUNSTATS ON TABLE SYSIBM.SYSVIEWDEPON ALL COLUMNS ALLOW WRITE ACCESS; etc., etc IBM Corporation 34

35 IBM Software Group DB2 데이터베이스 IBM DB2 Open Forum 3 rd 14 slides 2008 IBM Corporation

36 Database 관리유사한함수및용어 주요관리함수및용어 기본적으로동일 Different enough to miss when new to DB2 Oracle 함수 / 용어 Alert Log Archive Redo Log Autoextend Data Dictionary Data Cache Dbms_repair Dump datafile #block# (ALTER SYSTEM) Enable Restricted & Quiesce init.ora * Materialized View (Snapshot) Statement Cache DB2 함수 / 용어 DB2Diag Log Administration Notification Log Archive Log Autoresize System Catalog Buffer pool Inspect db2dart Quiesce DBM config (instance) DB config (database) Materialized Query Table Package Cache * Oracle 9i supports a "server parameter file" maintained by the Oracle engine 2008 IBM Corporation 36

37 Database 관리 Dictionary 와 Catalog Oracle Dictionary 와 DB2 System Catalog 내용 Oracle Data Dictionary 내용 SYS.xxxx$ - Base Tables SELECT table_name FROM user_tables SELECT view_name FROM user_views SYS.GV_$ or GV$ SYS.V_$ or V$ ALL_% DBA_% USER_% -System Views DB2 System Catalog 내용 SYSIBM Base tables SYSCAT - Read-only views - Defined for catalog base tables SELECT name FROM sysibm.systables WHERE TYPE="T" and CREATOR=user SELECT name FROM sysibm.systables WHERE TYPE="V" and CREATOR=user SYSSTAT - Updateable views - For access path manipulation 2008 IBM Corporation 37

38 물리데이터베이스저장모델스토리지구조 Oracle 과 DB2 는물리데이터베이스구조가거의비슷함 Below shows the slight differences in concept & semantics Oracle 스토리지구조 DB2 스토리지구조 Database Database Table space Datafile Table space Container Segment Object Extent Extent Oracle Block OS Page DB2 Page OS Page Logical Physical Logical Physical 2008 IBM Corporation 38

39 물리데이터베이스저장모델테이블공간타입 Oracle 테이블공간타입 System Non-System Temporary Dictionary Managed Locally Managed Permanent Dictionary Managed Locally Managed (default) Transportable Undo DB2 테이블공간타입 Database Managed (DMS) Regular Temporary (system & user) Large System Managed (SMS) Regular Temporary (system & user) Oracle Table Space Storage 설정 INITIAL NEXT MINEXTENTS MAXEXTENTS PCTINCREASE FREELIST/GROUPS DB2 Table Space Storage 설정 PAGESIZE EXTENTSIZE PREFETCHSIZE OVERHEAD TRANSFERRATE BUFFERPOOL 2008 IBM Corporation 39

40 물리데이터베이스저장모델 DB2 테이블공간생성 DB2 Table Space Creation Only SYSADM & SYSCTRL authorities can perform this Only created & active buffer pools may be used in a CREATE TABLESPACE command Buffer pools are dynamic & take immediate affect when added, dropped or altered DFT_PREFETCH_SZ can be set to let DB2 calculate optimal prefetching for your environment CREATE [ REGULAR / LARGE / TEMPORARY ] TABLESPACE tablespacename PAGESIZE integer MANAGED BY [ AUTOMATIC STORAGE][ SYSTEM / DATABASE ] [ system-container / database-container ] EXTENTSIZE [ number-of-pages / integer ] PREFETCHSIZE [ number-of-pages / integer ] BUFFERPOOL bufferpool-name OVERHEAD number-of-miliseconds TRANSFERRATE number-of-milliseconds AUTORESIZE To review information on these use LIST TABLESPACES [ SHOW DETAIL ] LIST TABLESPACE CONTAINERS FOR X [ SHOW DETAIL ] Or use Control Center GUI, or SYSCAT.TABLESPACES catalog view 2008 IBM Corporation 40

41 물리데이터베이스저장모델 DB2 Container Types For DMS & SMS DMS - Database Managed SMS - System Managed File Device Directory /database/tablespace/prod1 DMS is for database managed table spaces Basically what all Oracle table spaces translate to Recommended for larger more often used table spaces Supports automatic resizing and automatic storage similar to Oracle SMS is for system managed table spaces Only DB2 has the option to use this type of table space Recommended for smaller, less used tables paces For ease of maintenance Operating system controlled No database alterations possible to the container(s) More containers can be added to it with a redirected restore 2008 IBM Corporation 41

42 물리데이터베이스저장모델 DB2 Striping DB2 Table Space ~~~~~~~~~ ~~~~~~~~~~~~~ ~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~ ~~~~~~ ~~~~~~~~~ ~~~~~~ alter to add new Container Stripe Set 0 Container Stripe Set 1 DB2 does automatic striping of data among the containers in a table space You just make sure containers fall on different mount points You set PREFETCHSIZE for a table space setting to maximize striped reads & writes Or use block based buffer pools DB2 does automatic rebalancing of the data between containers when New DMS containers are added to a table space Rebalancing can be avoided during table space alter by Use of ALTER TABLESPACE... BEGIN NEW STRIPE SET With "policy" regulation to control impact on performance 2008 IBM Corporation 42

43 물리데이터베이스저장모델 DB2 행압축 Dictionary 기반 - symbol table for compressing/decompressing data records Lempel-Ziv (LZ) based algorithm (static dictionary based) Dictionary per table stored within the permanent table object 데이터는페이지에압축저장 On table space disk I/O 비용절감 In buffer pools 메모리절감 Insert/Update/Import/Load 시항시압축됨 Does not compress rows where no storage saving is realized for that row 약간의 CPU 비용추가 Rows must be decompressed before being processed for evaluation Name Dept Salary City ST Zip Burt smpo Dallas TX Vik smpo Dallas TX Burt smpo Dallas TX Vik smpo Dallas TX Etc Dictionary (01) smpo (02) Dallas, TX, Burt (01) (02) Vik (01) (02) Etc Etc 2008 IBM Corporation 43

44 기타관리상차이 DB2 Information Management DB2 Dynamic bitmap indexing 엔진스스로필요한시기에메모리에자동으로생성 Buffer pool per table space 테이블공간당각자의버퍼풀할당가능동일한테이블공간이라하더라도다른버퍼풀사용가능 Optimizer 는다양한정보에의해판단 Query optimization class Subcommands OPTIMIZE FOR n ROWS FETCH FIRST n ROWS ONLY Registry variables Volatile table Informational constraints Selectivity clause Optimization profiles Oracle Static bitmap indexing DBA 가만들고, 일반인덱스와동일하게관리 Cache (buffer pool) per block size block size 별로 Cache 할달가능 Optimizer 가한정된정보에의해판단 Hints Environment variables Version 7 부터비용기반지원 10g 에서는 Rules based 가지원되지않음 Version1 부터비용기반의 Optimizer 2008 IBM Corporation 44

45 기타관리상차이 ( 계속 ) DB2 True schema support 사용자와무관하게 Schema 가존재가능. 데이터베이스내또는데이터베이스간손쉽게이동가능 Packaged code Static access path 를위한 Pacakge 정보를카탈로그테이블에보관됨. Unrestricted parallel DML 모든 Query, Utility, RI Cascade, Triggers, LOB 처리시병렬처리지원 Optimizer aware of extenders.net 과 Spatial 정보에대한최적화지원 Federated Object 최적화지원 True row compression Load/Import/Insert 되는모든행이압축됨행레벨및칼럼레벨압축 Oracle User schema support Schema 를생성하기위해서는 User 가먼저생성되어야함 No packaged code Oracle 은 Static Access Path 를보관하지않음 Restricted parallel DML RI cascase, Triggers, LOB 처리시제약사항이있으며, DML 의병렬처리는 partitioned table 일때만지원 Optimizer not aware of extenders 최적화미지원 Initial column only compression Oracle 은 direct path loader 에만지원. 변경된행에대해서는후에 ALTER MOVE 명령으로재압축필요. 칼럼레벨압축 2008 IBM Corporation 45

46 IBM Software Group DB2 백업및복구 IBM DB2 Open Forum 3 rd 28 slides 2008 IBM Corporation

47 아키텍처비교컴포넌트 Oracle DB2 Data Files (Data Blocks) Control Files, List Backup Data Files (Data Pages) Recovery History File RMAN Backup / Restore Redo Logs Parameter Files Undo Segments Recovery Logs 2008 IBM Corporation 47

48 아키텍처비교 DB2 제어센터로깅구성 Wizards 효과적인학습툴 GUI 도구에서로깅설정을위한명령어를자동생성하여실행 Screens walk you through logging setup DB2 로깅을위해추가적인파라미터구성불필요 2008 IBM Corporation 48

49 아키텍처비교 DB2 제어센터백업, 리스토어및롤포어드 Wizards 효과적인학습도구 백업및리스토어, 롤포어드에대한모든구성내용제공 GUI 도구에서명령을자동생성하여실행 제어세터에서 3 개의화면실행 2008 IBM Corporation 49

50 아키텍처비교로깅옵션 Oracle DB2 Oracle 로깅옵션및컴포넌트 Redo logs는인스턴스마다정의 NoArchive 모드 - Full DB "crash" recovery - Limited roll-forward available Archive Mode - Recover DB, TS, DF - Dual logging & mirrored logging - 3 redo log states 1 - Active 2 - Inactive 3 - Archived DB2 로깅옵션및컴포넌트데이터베이스마다별도정의 Circular 모드 - Full "crash" recovery, - No roll-forward recovery Archive Log (aka Log Retain) - Recover DB, TS - Dual logging & mirrored logging - 3 recovery log states 1 - Active 2 - Online archived 3 - Off-line archived 2008 IBM Corporation 50

51 아키텍처비교 Archive 로깅 Oracle Archive Log Instance Level SGA DB2 Archive Log Data Base Level DB Shared Memory Database Buffer Cache Redo Log Buffer Large & Shared Pools, etc. Buffer Pool Log Buffer Package Cache, DB Heap, etc. dbwr ckpt lgwr arch db2pgclnr [softmax] db2logger db2uext2 Data Files Control Files Redo Log Files Archive Log Files Data Files Log Control File Recovery Log Files Archive Log Files Active Log Non-Committed or Non-Externalized Transactions Inactive Log Committed & Externalized Transactions Archived Log Files moved from active log subdirectory to another media Active Log Non-Committed or Non-Externalized Transactions Online Archive Log Committed & Externalized Transactions Off-line Archive Log Files moved from active log subdirectory to another media 2008 IBM Corporation 51

52 아키텍처비교 Recovery History 정보 Oracle For Online Backup RMAN LIST BACKUP V$BACKUP_FILES DB2 Recovery History File updated during Database backup, restore, roll-forward or recovery Table space backup, restore or rollforward Table space is created, dropped, altered, renamed or quiesced Table is loaded, dropped or reorganized Log file is archived New log file is written to list history backup all for sample select * from table(sysproc.admin_list_hist()) as history prune history Format YYYYMM 2008 IBM Corporation 52

53 오프라인백업 (aka Cold Backup) 가장단순한백업 Oracle ARCHIVELOG 모드 Shutdown in normal 수동복사 : data files control files redo logs (Use OS cpio, tar, backup manager, etc.) DB2 Circular logging 모드 (except to make a base backup for an archive logged database) 모든사용자연결해제 (or quiesce or stop) Backup 명령어사용 데이터베이스또는테이블공간레벨 SYSADM, SYSCTRL or SYSMAINT authority needed backup database sample to c:\db2backup 수동복사불필요하나필요시가능 2008 IBM Corporation 53

54 오프라인리스토어 Oracle Shutdown Normal 백업이미지와동일한이름사용 Data file, control files, redo logs, undo logs 파일들을수동으로복사 수동복사중에 datafile 를변경가능 alter database rename filename <oldpath> to <newpath> 새로운데이터베이스로생성가능하나매우복잡 DB2 데이터베이스 Stop 또는 quiesce History file에서필요한백업본에대한 TIMESTAMP를가져옴 ( 사용편의성 ) Restore 명령사용 restore database sample from "c:\db2backup" taken at without prompting Restore 중에 container 를변경가능 ( redirected restore ) DB2 는이작업의편의성을위해 script 를생성해주기도함 새로운데이터베이스로손쉽게생성가능 Restore db into newdb 2008 IBM Corporation 54

55 온라인백업 (aka Hot Backup) Archive 로깅구성필요 Oracle init.ora 변경 LOG_BUFFER LOG_ARCHIVE_DEST LOG_ARCHIVE_DUPLEX_DEST LOG_ARCHIVE_CONFIG Other optional shutdown startup mount alter database archivelog shutdown immediate 데이터베이스백업수행 alter database open alter database start archive log list archive log all DB2 로깅변수변경 LOGFILSZ LOGBUFSZ LOGPRIMARY LOGSECOND 추가변경 ( 선택적 ) NEWLOGPATH MIRRORLOGPATH BLK_LOG_DSK_FULL OVERFLOWLOGPATH LOGARCHMETH1 설정 오프라인백업수행필요 Wizard를통해구성가능 로그백업수행 2008 IBM Corporation 55

56 아키텍처비교 DB2 로깅파라미터 Function Parameter Usage Default Archive Retry Delay archretrydelay Wait in second between archive log failure attempts. 0 means no retry. 20 Block on Log Disk Full blk_log_dsk_ful Prevent disk full errors from being generated. Failover Archive Path failarchpath Alternate directory for the archive log files if the log archive method specified fails. NULL Log Archive Methods 1 & 2 logarchmeth1 logarchmeth2 These parameters cause the database manager to archive log files to locations that are not the active log path. Values are OFF, RETAIN, USEREXIT, DISK, TSM, VENDOR OFF Log Archive Options 1 & 2 logarchopt1 logarchopt2 Works with logarchmeth1 & 2 for vendor options if using TSM or VENDOR values. NULL Log Buffer logbufsz Amount of memory to use as a buffer for log records in 4-KB pages. 8 Log File Size logfilsiz The size of each configured log, in number of 4-KB pages Max Logs per Transaction max_log Percentage of primary log space that can be consumed by one transaction. 0 means no limit. 0 Mirror Log Path mirrorlogpath Location where an identical copy of the logs will be kept. NULL New Log Path newlogpath Location to change for log path away from default SQLOGDIR. NULL Number of Commits to Group mincommits Allows you to delay the writing of log records to disk until a minimum number of commits have been performed. 1 Number of Archive Retries numarchretry Number of attempts that will be made to archive log files using the specified log archive method before they are archived to the path specified by the failarchpath. 5 Number of Log Span num_log_span Number of active log files that an active transaction can span. 0 means no limit. 0 Overflow Log Path overflowlogpath Multi-function parameter used for rollforward and archived log retrieval operations. NULL Primary Logs logprimary Number of primary logs. Also determines number of logs in active log path. 3 Secondary Logs logsecond Number of secondary logs allocated if needed. -1 is unlimited active logs IBM Corporation 56

57 온라인백업작동및범위 Oracle RMAN 사용 Table space 레벨, one at a time (system 및 rollback 테이블공간은특별한주의필요 ) Alter table space begin backup 모드 변경된내용들을모두디스크에반영 SCN markers are frozen Log full images of changed blocks data files 과 control files 복사 (all data blocks, changed & unchanged) Alter table space end backup 모드 백업동안에최소한의 activity 권장 sysdba 권한필요 DB2 BACKUP command 사용 Database 또는테이블공간레벨 로그에확약된트랜잭션을위해변경된페이지정보를보유 Very first full backup done offline 백업이미지에포함되는것 변경된데이터페이지들 변경되지않는데이터페이지들 시스템파일들 History 파일들 Control 파일들 로그파일포함가능 우선순위조절가능 그래도온라인백업동안에는최소한의 activity 권장 sysadmin, sysctrl, sysmaint 권한필요 2008 IBM Corporation 57

58 온라인백업 DB2 예 DB2 Online Database backup backup database sample online to c:\db2backup Online table space backup including logs backup database sample tablespace (userspace1) online to c:\db2backup include logs Online database backup 압축및 Throttled backup database sample online to c:\db2backup Compress util impact priority IBM Corporation 58

59 온라인리스토어 Database 고려사항 Oracle Restore 와 recover 전략이무엇이유실되었느냐에따라다름 (Examples, see paper on Oracle Recovery Scenarios ) Restore 오프라인상태에서, 백업받은이미지복사 Recover roll-forward를위해redo log 반영 Database 레벨복구 데이터베이스 Mount 모든데이터파일을 Online 시킴 유실된데이터파일을복사 Redo log 적용 (inactive and archived) Recover database는 2가지타입가능 End of logs Point in time DB2 데이터베이스 Restore 시에는오프라인상태에서온라인백업이미지를이용하여 restore 실행 전체 Restore는모든주요시스템파일들을자동으로복원 손쉽게복구하고서비스시작가능 Restore with roll-forward for more control recover db sample to end of logs or restore database sample from c:\db2backup taken at without prompting rollforward database sample to end of logs and complete Roll-forward 는 2가지타입제공 End of logs Point in time 2008 IBM Corporation 59

60 온라인리스토어테이블공간고려사항 Oracle 테이블공간레벨복구 테이블공간 Offline 시킴 유실된데이터파일복사 Redo log 반영 (online and archived) 복구시나리오 Media failure의심각도및백업전략에의존적이며, 일관성있는상태로복구하는것이힘겨운때가종종있음. 예제 Oracle Recovery Scenarios DB2 테이블공간복구를온라인상태에서실행 restore database sample tablespace(userspace1) online from c:\db2backup taken at without prompting rollforward database sample to end of logs and complete tablespace(userspace1) online 롤포어드는 2 가지타입제공 End of logs Point in time 테이블공간백업이미지들을이용하여전체데이터베이스를생성가능 2008 IBM Corporation 60

61 Incremental 백업비교 Oracle Cumulative Incremental Backup Level 0 is a baseline Similar Level 1은가장마지막 Level 0 백업이래변경된모든 block들을백업 DB2 Incremental Backup 마지막 full backup 받은이래변경된모든페이지들을백업 Differential Incremental Backup (default) Similar Level 0 is a baseline Level 1 은가장마지막 incremental backup이래변경된모든 block들을백업 Delta Backup 마지막 full, incremental, delta 이래로변경된모든페이지들을백업 2008 IBM Corporation 61

62 Incremental Backup Oracle Cumulative and Differential Incremental Backups * Cumulative Incremental Backup Differential Incremental Backup (default) * Excerpt from Oracle documentation 2008 IBM Corporation 62

63 Incremental Backup DB2 Incremental & Delta DB2 Incremental & Delta Backups 2008 IBM Corporation 63

64 Incremental Backup Using Oracle 부분백업을위해 RMAN 사용 RMAN 은마지막백업이래변경된 Block 들을 Tracking 하는파일을이용 Tracking Enable alter database enable block change tracking using file '/bkups/change.log Tracking Disable alter database disable block change tracking DB2 Tracking 을위해 db cfg 에 TRACKMOD 를 로변경 update db cfg for sample using trackmod yes Full 백업을받은후, 이후에 Incremental 백업수행 backup database sample to c:\db2backup backup database sample online incremental to c:\db2backup backup database sample online incremental delta to c:\db2backup 2008 IBM Corporation 64

65 Incremental Restore Using Oracle Incremental restore 를위해 RMAL 사용 RMAN 은 Incremental 백업이미지로부터 Incremental Merge 시킬수있음 DB2 데이터의손실없이 restore incremental 및 delta 백업을통한자동복구를위해 Automatic 키워드사용 Roll forward ( 예제 : 다음장 ) Restore wizard 사용시매우편리 2008 IBM Corporation 65

66 Incremental Restore DB2 예제 DB2 Incremental Restore 단계 Restore using automatic keyword to apply incremental or delta backups restore database sample incremental automatic from c:\db2backup taken at without prompting 자동 restore 를위해, 가장최근의 incremental 및 delta 백업이미지의 timestamp Roll-forward rollforward database sample to end of logs and complete 2008 IBM Corporation 66

67 삭제된테이블복구과정비교 Oracle FLASHBACK 옵션이있으면손쉬움 : FLASHBACK TABLE RECYCLETEST TO BEFORE DROP DB2 삭제된테이블 history 가보관됨 (DROPPED TABLE RECOVERY가 On) list history dropped table all for sample 영구적으로테이블삭제를하려면 : DROP TABLE RECYCLETEST PURGE 영구적으로삭제한테이블을복구하는일은매우어려움 ( 다음장 ) 이전의백업이미지로부터테이블공간을 Restore restore db sample tablespace(userspace1) from c:\db2backup taken at without rolling forward without prompting 2008 IBM Corporation 67

68 삭제된테이블복구과정비교 ( 계속 ) Oracle 영구적으로삭제된테이블을복구하는법 Database를다른서버에restore 테이블을삭제된바로이전의시점까지 point-in-time 복구실행 삭제된테이블을 Export 원래의데이터베이스에 Import DB2 삭제된테이블의데이터를 Unload rollforward db sample to end of logs and complete recover dropped table d to c:\db2backup Recovery history 파일로부터 DDL문추출하여테이블재생성 데이터로드 load from c:\db2backup\node0000\data of del method p(1) insert into recoverme 2008 IBM Corporation 68

69 백업및복구자동튜닝백업지원 자동튜닝기능이 BACKUP 유틸리티에도지원 성능상장점 최악의시나리오에서도 4배의성능향상 자동설정 : Buffer size Number of buffers Parallelism 2008 IBM Corporation 69

70 백업및복구지원유틸리티 db2ckbkp ( 백업이미지점검 ) 백업이미지의무결성점검 ( 로그포함여부확인가능 ) db2ckrst (incremental restore 이미지점검 ) Helps manage log chains to make sure they are complete for any given incremental restore db2adutl (TSM 오브젝트관리 ) Allows users to query, extract, verify, and delete backup images, logs, and load copy images saved using Tivoli Storage Manager. db2pd ( 문제해결 ) -LOGS 옵션 모든로그에대한현재정보 2008 IBM Corporation 70

71 백업및복구교육 Oracle 백업및복구가배우고사용하기가쉽지않음. Oracle은 Backup / Recovery 를위해3일교육과정제공 DB2 백업및복구가배우고사용하기가매우간단 DB2 ADMIN교육과정이 4일과정이며, 백업및복구를위한 topic이포함 습득위해 1일이하소요 2008 IBM Corporation 71

72 백업및복구결론 두데이터베이스모두제공 Offline or online backup of database and table spaces Table space online backup and restore, relocation of table space with compression Roll-forward to a point-in-time or to the end of logs Incremental and differential backup for data warehouse environment Effective mechanisms and capabilities for securing data from loss due to media corruption or human error. 그러나, 백업및복구기술은 Oracle 보다 DB2 가매우간단하며손쉬움 2008 IBM Corporation 72

73 IBM Software Group Primary DB2 High Availability Disaster Recovery (HADR) clone Standby IBM DB2 Open Forum 3 rd 29 slides 2008 IBM Corporation

74 DB2 HADR 의목표 장애시간의최소화 (Ultra-fast failover) 관리의용이함및비용의최소화 (Easy administration) 이중화에대한성능보장 (Negligible impact on performance 업무연속성제공 (H/W,S/W upgrades without interruption) Fail-Over의용이함 (Client Reroute) Transparent failover & failback for applications 2008 IBM Corporation 74

75 DB2 HADR - 가장빠른장애복구솔루션 데이터베이스를이중관리함으로써사이트장애또는 Storage 장애에대비 무중단으로버전업그레이드가능 최근변경내용을대기서버의메모리 ( 버퍼풀 ) 에저장 장애시대기서버의가동시작시간을앞당김 15초이내장애복구 실재로 600명의 SAP 사용자를서비스하는시스템에서 11초만에데이터베이스서비스를재시작했음 대기서버에서도장애시주서버와동일한성능 (100%) 으로서비스가능 Automatic Client Reroute Client application 은자동으로대기서버에서재시작됨 tx tx Network Connection HADR 두서버간 Sync 유지 tx tx TSA for server monitoring Monitors the primary and initiates the takeover. HACMP, heartbeat, TSA, MSCS, etc 1 차서버 2 차서버 2008 IBM Corporation 75

76 HADR 구성방안 (Local HA/Remote DR) HADR 구성으로 Local Standby Database 구성 Remote DR 을위해 HADR Standby 데이터베이스에연결 ( 주서버의네트웍부하고려 ) HADR Cluster Primary DB 서버장애감지 Local Standby Database로자동서비스이전 서울지역전원장애등재해발생 Remote DR 서버로서비스이전 Automatic client reroute HADR DB1 Primary Database DB1 Disk Mirroring Local standby Database DB1 Remote Disaster Recovery 2008 IBM Corporation 76

77 HADR 의구현및관리 손쉽게구성하고관리 한장의설명으로충분 Primary Setup (1)db2 backup db hadr_db to backup_dir Standby Setup (2) db2 restore db hadr_db from backup_dir (3)db2 update db cfg for hadr_db using HADR_LOCAL_HOST host_a HADR_REMOTE_HOST host_b HADR_LOCAL_SVC svc_a HADR_REMOTE_SVC svc_b HADR_REMOTE_INST inst_b HADR_TIMEOUT 120 HADR_SYNCMODE ASYNC (6) db2 start hadr on database hadr_db as primary (4) db2 update db cfg for hadr_db using HADR_LOCAL_HOST host_b HADR_REMOTE_HOST host_a HADR_LOCAL_SVC svc_b HADR_REMOTE_SVC svc_a HADR_REMOTE_INST inst_a HADR_TIMEOUT 120 HADR_SYNCMODE ASYNC (5) db2 start hadr on database hadr_db as standby 서비스이관 : db2 takeover hadr on db hadr_db 2008 IBM Corporation 77

78 HADR Setup 클라이언트리라우트 Client reroute 구성 Before Client re-route After Client re-route Active Standby Loss Active Database Primary HADR Database Standby Failover Cluster manager executes db2 TAKEOVER HADR ON DATABASE HADRDB BY FORCE on standby Database loss Database Primary Primary : db2 UPDATE ALTERNATE SERVER FOR DATABASE HADRDB USING Host1.ibm.com PORT Standby : db2 UPDATE ALTERNATE SERVER FOR DATABASE HADRDB USING Host2.ibm.com PORT IBM Corporation 78

79 RAC 대비 HADR 의우수성 HADR 솔루션은 RAC 에비해고가용성은물론이고성능, 관리성, 비용면에서모두탁월한장점을가진솔루션입니다. 구분 성능 구현및관리 비교 HADR > RAC HADR > RAC 세부내용 DB2 는한대의 SMP 기계에서 OLTP 업무최고의성능 (tpc-c) 을내고있습니다. 가장최근자료 (2006/12) 에의하면, 동일한기계사양에서 Oracle 에비해 3.4 배의성능차이를보였습니다. 한대의 SMP로관리하는것이 Cluster 시스템을운영하는거에비해관리하기가더쉽습니다. 이로인해관리비용이 RAC가더많이소요됩니다. 또한 RAC는구축시 Application의디자인고려등구현하기가어렵지만, HADR은구성하기가매우쉽습니다. 고가용성 HADR > RAC 한대의노드가장애시 HADR 은 RAC 와비교했을때최소한비슷하거나또는보다빠른재가동시간을확보할수있습니다. 또한 DB2 는고가용성을위한다양한기능들을제공하고있습니다. 비용 HADR > RAC H/W 비용은 RAC 솔루션이보다저렴하나, Oracle 및 RAC 를위한라이센스비용으로인해, HADR 솔루션이전체적인비용을절감시킵니다. 이에따라 S/W 유지보수비용도 RAC 가더비싸게책정될수있습니다. RAC 3 노드를가정했을때, 동일한용량을가지는 HADR 솔루션이 24% 더저렴합니다 IBM Corporation 79

80 Oracle RAC 장애복구 Data Block Remastering 한노드가실패하면실패한노드의블럭들을다른노드에서재분산되어야함. 이과정동안전노드의 GCS 가중단되며, 데이터베이스서비스도중단됩니다. Instance 1 Instance 2 Instance 3 Global Resource Directory Master for block Global Resource Directory Master for block Global Resource Directory Master for block IBM Corporation 80

81 초기도입비용비교 총업무량처리를위해 12 장의 CPU 가필요하다고가정했을때 Oracle RAC 는장애시한대의서버에서가동되어야하므로, 업무대비한 CPU 개수에대한추가적인고려가필요하나, 이를감안하지않더라도도입비용면에서 DB2 HADR 이보다저렴합니다. HADR 은 DB2 의고가용을지원하는기능이며, DR 기능도포함합니다. $256,721 절감 (24%) Assumes that RAC scales linearly p way p way Server Storage DB2 ESE (Enterprise Server Edition) 9 HADR (High Availability Disaster Recovery) Total *$33,125 per CPU for DB2 ESE $211,740 $151,168 *$430,625 $0 $793,533 Storage Total p570 4-way p570 4-way p570 4-way Server(8822.5) Oracle EE 10g RAC(Real Application Cluster) **$48,000 per CPU for Oracle EE ***$24,400 per CPU for Oracle RAC $105,870 $75,584 **$576,000 ***$292,800 $1,050,254 초기도입시 Disk 추가비용소요 HADR을위한추가라이센스없음 Standby장비:1 CPU 라이센스구매 RAC 추가라이센스구매 두개장비모두 CPU 기반라이센스구매 라이센스구매비용 = 유지보수비용 2008 IBM Corporation 81

82 장애복구시간비교 장애발생시다시정상적으로서비스가제공되기까지의시간 (Fail-over Time) 을비교하였습니다. Fail-over 동작 Oracle 단일서버 Fail-over Oracle RAC Fail-over DB2 HADR/HA Fail-over 1) Cluster 서버그룹재구성 필요없음 15 초 필요없음 2) 분산 Lock 재구성 필요없음 5 초 필요없음 3) 디스크볼륨절체 최대 20 분 필요없음 필요없음 4) 데이터베이스서버재기동 최대 5 분 필요없음 필요없음 5) 데이터베이스복구 / 롤포워드 20 초 20 초 20 초 총소요시간 - Failover Time 25 분이상 60 초미만 20 초미만 DB2 의 HADR 솔루션이 Oracle RAC 에비해 2 배이상빠른 Fail-Over 시간제공! 2008 IBM Corporation 82

83 HADR vs RAC 가용성 구분 DB2 HADR Oracle 10g RAC 소프트웨어장애대비 Server 장애대비디스크장애대비사이트장애대비 Rolling Upgrade 지원수분대장애복구장애복구이후서비스가동지역적인분산 Express/Workgroup에서도지원구성및모니터가간단응용프로그램변경이불필요 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No No No Yes Yes No No / Limited No No 2008 IBM Corporation 83

84 IBM Software Group DB2 동시성 분리레벨및잠금 IBM DB2 Open Forum 3 rd 18 slides 2008 IBM Corporation

85 동시성개요 DB2 Information Management 동시성 : 다중사용자및응용프로그램들이자원을공유하는것을허용 데이터일관성유지 2가지메커니즘 분리레벨 잠금 동시성이슈 Lost updates 2개의 Transaction이동일한데이터를변경하려고시도하여, 첫번째변경된데이터는두번째변경된데이터에의해사라지는현상 Phantom Reads Transaction이두번째결과집합을생성할때, 처음에없던새로운행들이추가되는현상 Nonrepeatable Reads Transaction 이두번째결과집합을생성할때, 변경된행이존재하거나또는삭제되어나타나지않는행이있는현상. Uncommitted Reads 다른 transaction에의해변경되었으나, 아직확약되지않은데이터를읽는현상 Old Data Reads Transaction이읽어온데이터가이미다른 transaction에의해변경되어, 구데이터가되는현상 IBM Corporation 85

86 동시성 ANSI 분리레벨개요 ANSI 분리레벨 Repeatable Read Read Stability Cursor Stability Uncommitted Read 잠금영향 결과집합에영향을미치는모든행들에대해트랜잭션이마칠때까지잠금을보유 (Any row that is referenced in any manner to produce a result set is locked until the transaction ends) 실제적으로검색된모든행들에대해트랜잭션이마칠때까지잠금을보유 (Any row that is actually retrieved to produce the result set is locked until the transaction ends) 결과집합중현재참조되는행에대해서만 cursor 가이동하거나또는트랜잭션이마칠때까지잠금을보유 (Only the row that is currently being referenced in that result set is locked until that cursor is repositioned or the transaction ends) 트랜잭션이실행되는동안잠금을확보하지않음 (No locking is done on any records at any time during a transaction (with the exception of a dropped or altered table which is not transactional anyway)) 2008 IBM Corporation 86

87 동시성 ANSI 분리레벨 동시성이슈해결 ANSI Isolation Level J2EE Isolation Level * Old Data Reads? Lost Updates? Uncommitted Reads? Nonrepeatable Reads? Phantom Reads? Repeatable Read Serialization No No No No No Read Stability Repeatable Read No No No No Yes Cursor Stability Read Committed No No No Yes Yes Uncommitted Read Read Uncommitted No No Yes Yes Yes * From Oracle s documentation on J2EE isolation levels: Phantom Reads Concurrency Problems... Lost Updates Uncommitted Reads Nonrepeatable Reads 2008 IBM Corporation 87

88 동시성 ANSI 분리레벨튜닝 : 보다많은동시성확보 DB2_EVALUNCOMMITTED 레지스트리변수 RS나 CS분리레벨에서, 조건을만족시키는행이있을때까지, 행잠금을연기함. 조건을만족시키지않는행들은통과 CS 나 RS 트랜잭션은미확약데이터에대해서도조건만족여부를검사 DB2_SKIPDELETED 레지스트리변수 CS 나 RS 트랜잭션은삭제된행 ( 아직 commit 되지않음 ) 에대해조건검사를 Skip. DB2_SKIPINSERTED 레지스트리변수 CS 나 RS 트랜잭션은추가된행 ( 아직 commit 되지않음 ) 에대해조건검사를 Skip. Other DB2 Concurrency Improvement Suggestions 자주 commit을하도록보다작은 UOW를생성 가능한한빨리 Cursor를 Close Table 잠금이필요한경우 (Load나 utility작업을하는경우 ) 에는 SHARE모드에서함 2008 IBM Corporation 88

89 동시성 DB2 분리레벨설정 Statement 레벨 : SELECT (INTO), DELETE, UPDATE... WITH {RR, RS, CS, UR} 현재세션에서 dynamic SQL: SET CURRENT ISOLATION = {RR, RS, CS, UR} or SET ISOLATION {RR, RS, CS, UR} precompile 이나 bind 시 : ISOLATION {RR, RS, CS, UR} option of the command line processor PREP or BIND commands DB2 Call Level Interface (DB2 CLI): For DB2 CLI, change the isolation level as part of the DB2 CLI configuration (db2cli.ini). This can be done either by: UPDATE CLI CFG FOR SECTION sectionname USING TXNISOLATION {1, 2, 4, 8} where: 1 = SQL_TXN_READ_UNCOMMITTED - Read Uncommitted (Uncommitted Read) 2 = SQL_TXN_READ_COMMITTED (default) - Read Committed (Cursor Stability) 4 = SQL_TXN_REPEATABLE_READ - Repeatable Read (Read Stability) 8 = SQL_TXN_SERIALIZABLE - Serializable (Repeatable Read) Editing db2cli.ini and setting appltxnisolation = {1, 2, 4, 8} JDBC 또는 SQLJ : Use the settransactionisolation method in the java.sql interface connection, where: TRANSACTION_SERIALIZABLE = Repeatable read TRANSACTION_REPEATABLE_READ = Read stability TRANSACTION_READ_COMMITTED = Cursor stability TRANSACTION_READ_UNCOMMITTED = Uncommitted read Command Line Processor (CLP) : CHANGE ISOLATION TO {RR, RS, CS, UR, NC} where NC stands for No Commit REXX: CHANGE SQLISL TO {RR CS UR RS NC} {RR, RS, CS, UR, NC} 2008 IBM Corporation 89

90 동시성잠금모드 DB2 Information Management Locking Mode IN (Intent None) IS (Intent Share) NS (Next Key Share) S (Share) IX (Intent Exclusive) SIX (Share with Intent Exclusive) U (Update) NW (Next Key Weak Exclusive) X (Exclusive) W (Weak Exclusive) Z (Super Exclusive) Applicable Object Table spaces, blocks, tables Table spaces, blocks, tables Rows Rows, blocks, tables Table spaces, blocks, tables Table spaces, blocks Rows, blocks, tables Rows Rows, blocks, tables, buffer pools Rows Table spaces, tables 2008 IBM Corporation 90

91 동시성잠금타입호환성 STATE OF HELD RESOURCE STATE REQUESTED None IN IS NS S IX SIX U NX X Z NW W None IN IS NS S IX SIX U NX X Z NW W Legend: - grant lock requested immediately - wait for held lock to be released or timeout to occur 2008 IBM Corporation 91

92 동시성 DB2 잠금튜닝 DB2 Information Management LOCKLIST 잠금정보를저장하는메모리 LOCKTIMEOUT 응용프로그램이잠금을획득하기위해기다리는시간 ( 초 ) MAXLOCKS Lock escalation 이일어나는 LOCKLIST 중의비율 DLCHKTIME Deadlock 발생점검주기 SET CURRENT LOCK TIMEOUT [ WAIT T WAIT NULL WAIT integer ] 트랜잭션내에서 Lock timeout 값등록 ALTER TABLE LOCKSIZE BLOCKINSERT Insert 잠금을최소화하는 MDC 테이블옵션 2008 IBM Corporation 92

93 동시성 TPC-C: OLTP 벤치마크 벤치마크는 DB2 동시성이효율적임을증명 DB2 는비슷한 H/W 구성에서 Oracle 보다성능상우수 2008 IBM Corporation 93

94 동시성 SAP 벤치마크 가장큰 OLTP 벤더 벤치마크는 DB2 동시성이효율적임을증명 SAP 테스트에서 DB2 는최고의성능을내는데이터베이스 2008 IBM Corporation 94

95 MVRC 와 ANSI 분리레벨 Oracle 분리레벨 Oracle 은 Multi Version Read Consistency (MVRC) 분리레벨사용 Query 에서보는데이터는시작시점의존재데이터 Any statement seeing an update "rolls back" to get an older version of the data - 잠금비용을줄이는경우도있으나, I/O 비용을항상증가시킴 - 모든변경될가능성있는 data block 들은물리적인복사본 (undo tablespace 에기록 ) 이항상필요하기때문 - 디스크요구량증가 - Cache 사용량증가 - Log 사용량증가 일기만하는 Transaction 은 writer 를기다리지않으나, 읽어온데이터가구데이터일가능성이있음 - 이이슈는 MVRC 에만존재 두개의 WITH UPDATE 문장은서로를기다려야함 더구나 Oracle 은벤치마크테스트시에는 serializable 분리레벨을사용 (See Oracle TPC- C benchmark to see it widely uses serialized isolation.) 2008 IBM Corporation 95

96 MVRC 와 ANSI 분리레벨 Oracle 분리레벨문제점 Oracle MVRC는여러가지문제유발 다른 RDBMS들은이분리레벨을사용하지않음 ANSI 기반의 J2EE 분리모델과매치되지않음 확약된트랜잭션도 Update문이전에시작되었으면구데이터를읽어감. 보관된구데이터를찾기위해추가적인 CPU 자원이필요 동일한 Block의데이터가여러버전 Cache되어, cache의효율성이떨어짐 Undo Tablespace(9i이전 : Rollback segments) 가필요 RBS는복구를위해 redo log에저장됨 RBS는 Oracle DBA에게는문제가많음 구데이터보관을위해추가적은디스크필요 Segment의크기및숫자관리필요 Transaction의크기나기간을알필요가있음 Sync가이루어지지않으면, Oracle은트랜잭션을취소시킴 ORA 에러발생 (snapshot old) 시해결방법 DBA가 RBS의크기를늘려주거나숫자를늘려해결 개발자가자주 Commit을자주찍지않도록수정하여해결 2008 IBM Corporation 96

97 MVRC 와 ANSI 분리레벨실상황예제 첫번째예제 : MVRC 두번째예제 : MVRC force serializing DB2 와유사 Transaction 1 Transaction 2 Transaction 1 Transaction 2 Begin Transaction. Begin Transaction. Begin Transaction Begin Transaction SELECT available seats from flight ABC111. See seat 23F is last available. Reserve seat. SELECT available seats from flight ABC111 using FOR UPDATE clause. See seat 23F is last available. Reserve seat. SELECT available seats on flight ABC111. SELECT available seats on flight ABC111 using FOR UPDATE. Also see 23F available as Oracle goes to rollback segment to get the old version of that block. Blocks wait on lock from first transaction. Commit Transaction. Commit Transaction. Reserve seat. Commit Transaction SELECT returns with no seats available. Successful Commit with lost update. Book on another flight. Flight is oversold! 2008 IBM Corporation 97

98 MVRC 와 ANSI 분리레벨벤치마크테스트에서의프로그램코딩 TPC-C 벤치마크발표보고서에의하면 질문 : 왜 Oracle 은 MVRC 를사용하지않는가? 답변 : 최고도의트랜잭션시스템에서는 Overhead 가너무많기때문! 2008 IBM Corporation 98

99 MVRC 와 ANSI 분리레벨 DB2 분리레벨 DB2는 ANSI 표준분리레벨을사용 RR 모든참조되는행을잠금 RS - 모든검색된행에대해잠금 CS 현재처리되고있는행에대해서만잠금 UR 어떤행도잠그지않음 현재의확약된데이터만을읽도록트랜잭션을나열하는게기본 UR 분리레벨은 Dirty Read를허용하여, Reader가 Writer를기다리지않게함 DB2는 Undo(rollback) 오버헤드를가지지않음 DB2는행수준잠금은진정한행수준잠금이며, 메모리에저장 Oracle의 SCN은물리데이터블록의헤더에저장 Oracle의 SCN은동일한페이지의행을필요로하는다른트랜잭션에의해변경될수있음. 자주 commit을하지않도록짜여진 Oracle응용프로그램을 DB2로전환시에는많은 Locking을유발하게됨. 그러나반대로, 자주 commit을하도록짜여진 DB2 응용프로그램을 Oracle에전환시에는많은 ORA 에러가발생될수있다 MVRC 가벤치마크에서어떻게사용되었는가? TPC-H (warehousing), SAP, PeopleSoft, Baan (OLTP) 테스트에서 DB2 가선두! TPC-C 테스트에서, ORACLE 은 ƒ MVRC 와 ORA 의에러핸들링을 Skip 하며코딩했는가? ƒ 왜결과를얻기위해 serializable 분리레벨을사용했는가? ƒ 그럼에도불구하고왜여전히 DB2 에비해처리량이낮은가? 2008 IBM Corporation 99

100 잠금 Oracle 행수준잠금 Oracle 잠금접근 해당데이터페이지에 lock 정보를저장 모든 data 및인덱스페이지에는동시에접근하는트랜잭션수에따라가변적으로늘어나는 Transaction layer 가있다. 모든트랜잭션은해당페이지내에 ITL(Interested Transaction List) 에하나의엔트리를추가한다. 동일한페이지를액세스하는트랜잭션이많아질수록, transaction layer의크기는커짐 비용 디스크공간 Overhead 디스크속도 Overhead MAXTRANS는 ITL의수를제한 DBA는 hot 테이블에대한 disk overhead와 MAXTRANS에대해고려필요 MAXTRANS에도달하면, Transaction들은 sleep 됨 장점 메모리불필요 2008 IBM Corporation 100