BC535 Database Administration DB2 UDB on UNIX/NT BC535
Release 46D 05.02.2002
BC535 Database Administration DB2 UDB on UNIX/NT ................................................................................... 0-1 Copyright ........................................................................................................................................................... 0-2 SAP Basis Administration Training 4.6 ............................................................................................................. 1-1 SAP„s Knowledge Transfer Model .................................................................................................................... 2-1 Course Overview................................................................................................................................................ 3-1 Course Prerequisites - Audience - Duration ................................................................................................... 3-2 Course Objectives .......................................................................................................................................... 3-3 Course Contents ............................................................................................................................................. 3-4 Course Introduction........................................................................................................................................ 3-5 The Required Administration Knowledge ..................................................................................................... 3-6 DB2 Database Fundamentals ............................................................................................................................. 4-1 DB2 Database Fundamentals Part 1 ............................................................................................................... 4-2 DB2 Database Software Components ............................................................................................................ 4-3 DB2 Instances and R/3 Instances ................................................................................................................... 4-4 R/3 and DB2 Supported Combinations .......................................................................................................... 4-5 DB2 FixPaks .................................................................................................................................................. 4-6 DB2 Database Fundamentals Part 2 ............................................................................................................... 4-7 The DB2 Process Model for Unix Database Servers ..................................................................................... 4-8 The DB2 Process Model for Unix Database Servers ..................................................................................... 4-9 The DB2 Process Model for Unix Database Servers ................................................................................... 4-10 The DB2 Thread Model for NT Database Servers ....................................................................................... 4-11 Startup of DB2 ............................................................................................................................................. 4-12 Shutdown of DB2......................................................................................................................................... 4-13 DB2 TCP/IP Connection Setup .................................................................................................................... 4-14 DB2 IPC Connection Setup ......................................................................................................................... 4-15 SQL Statement Processing (1) ..................................................................................................................... 4-16 SQL Statement Processing (2) ..................................................................................................................... 4-17 Parallel I/O Servers and I/O Writers ............................................................................................................ 4-18 DB2 Database Fundamentals Part 3 ............................................................................................................. 4-19 Database Logging (1) ................................................................................................................................... 4-20 Database Logging (2): Log File Handling ................................................................................................... 4-21 Database Logging (3) ................................................................................................................................... 4-22 Database Logging (4) ................................................................................................................................... 4-23 Creation and Use of DB2 Log Files ............................................................................................................. 4-24 Circular Logging .......................................................................................................................................... 4-25 Log Retention Mode .................................................................................................................................... 4-26 Backup Pending Mode ................................................................................................................................. 4-27 DB2 Database Fundamentals Part 4 ............................................................................................................. 4-28 R/3 & DB2 Structures and Conventions on NT .......................................................................................... 4-29 R/3 & DB2 Structures and Conventions on UNIX ...................................................................................... 4-30 R/3 Naming Conventions: Review ............................................................................................................... 4-31
OS/Database Users and Authorities ............................................................................................................. 4-32 Roles of DB2 Database Administrative Users ............................................................................................. 4-33 Data Security: DB Access Passwords .......................................................................................................... 4-34 Unit Summary .............................................................................................................................................. 4-35 Unit Actions ................................................................................................................................................. 4-36 DB2 UDB Database Fundamentals: Exercises ............................................................................................ 4-37 DB2 UDB Database Fundamentals: Solutions ............................................................................................. 4-38 DB2 Cost-Based Optimizer................................................................................................................................ 5-1 DB2 Cost-Based Optimizer............................................................................................................................ 5-2 Cost-Based Optimizer: Introduction .............................................................................................................. 5-3 Facts Not Observed by the CBO .................................................................................................................... 5-4 DB2 UDB Cost-Based Optimizer .................................................................................................................. 5-5 DB2 UDB: Execution Plan ............................................................................................................................ 5-6 DB2 CBO: SQL Explain ................................................................................................................................ 5-7 Understanding DB2 Explain Output .............................................................................................................. 5-8 Understanding DB2 Details Output ............................................................................................................... 5-9 DB2 CBO: Details (1) .................................................................................................................................. 5-10 DB2 CBO: Details (2) .................................................................................................................................. 5-11 DB2 CBO: Details (3) .................................................................................................................................. 5-12 DB2 CBO: Details (4) .................................................................................................................................. 5-13 SQL Enhancements Affecting Query Execution .......................................................................................... 5-14 SELECT SINGLE Execution Plans ............................................................................................................. 5-15 UP TO
ROWS Execution Plans ........................................................................................................... 5-16 DB2 Operators ............................................................................................................................................. 5-17 DB2 JOIN Concepts: MERGE..................................................................................................................... 5-18 DB2 JOIN Concepts: NESTED LOOP ........................................................................................................ 5-19 DB2 JOIN Concepts: HASH ........................................................................................................................ 5-20 DB2 Configuration for CBO ........................................................................................................................ 5-21 R/3 Tools for Updating Statistics: dmdb6srp ............................................................................................... 5-22 Scheduling Object Statistics for All Objects ................................................................................................ 5-23 Scheduling Daily Statistic Updates .............................................................................................................. 5-24 SAP‟s dmdb6srp Stores DB2 REORGCHK Results ................................................................................... 5-25 SAP Two-Phase Strategy ............................................................................................................................. 5-26 Refreshing Daily Object Statistics: Phase I .................................................................................................. 5-27 Refreshing Daily Object Statistics: Phase II ................................................................................................ 5-28 Modifying the Algorithm to Update the Statistics ....................................................................................... 5-29 Summary ...................................................................................................................................................... 5-30 Unit Actions ................................................................................................................................................. 5-31 DB2 UDB Cost-Based Optimizer: Exercises ............................................................................................... 5-32 DB2 UDB Cost-Based Optimizer: Solutions ............................................................................................... 5-33 DB2 UDB Tool Environment ............................................................................................................................ 6-1 DB2 UDB Tool Environment ........................................................................................................................ 6-2
Tool Infrastructure of DB2 UDB Databases .................................................................................................. 6-3 Administration Database: ADM .......................................................................................................... 6-4 Administration Database: Maintenance ......................................................................................................... 6-5 Installing the Admin Database and Server Tools ........................................................................................... 6-6 DB2 Graphical Administration Tools ............................................................................................................ 6-7 Technical Architecture: Background Daemons ............................................................................................. 6-8 Control Center: Installation ............................................................................................................................ 6-9 Control Center: SAP DB2 Admin Extensions ............................................................................................. 6-10 Control Center: Handling ............................................................................................................................. 6-11 Control Center: Cataloging of Databases ..................................................................................................... 6-12 DB2 Command Line Processor .................................................................................................................... 6-13 DB2 CLP: Important Commands ................................................................................................................. 6-14 DB2 CLP: Architecture ................................................................................................................................ 6-15 Data Security: DB Access Passwords .......................................................................................................... 6-16 DB2 Tools to Extract Statistics: db2look ..................................................................................................... 6-17 DB2 Utilities: db2look ................................................................................................................................. 6-18 DB2 Utilities: db2dart .................................................................................................................................. 6-19 Unit Summary .............................................................................................................................................. 6-20 Unit Actions ................................................................................................................................................. 6-21 DB2 Tool Environment: Exercises .............................................................................................................. 6-22 DB2 Tool Environment: Solutions ............................................................................................................... 6-23 Backup and Log File Management .................................................................................................................... 7-1 Backup and Log File Management ................................................................................................................ 7-2 Defining Your Backup Requirements ............................................................................................................ 7-3 Aspects of Data Safety: Separating File Systems .......................................................................................... 7-4 Aspects of Data Safety: Redundancy ............................................................................................................. 7-5 Data Backup Objects ...................................................................................................................................... 7-6 DB2 Backup Architecture .............................................................................................................................. 7-7 DB2 Recovery History File ............................................................................................................................ 7-8 DB2 Backup Types: Offline Backup ............................................................................................................. 7-9 DB2 Backup Types: Online Backup ............................................................................................................ 7-10 Backup Strategies: Tablespace Level Backups ............................................................................................ 7-11 R/3 and DB2 Database Backup Tools .......................................................................................................... 7-12 R/3 and DB2 Database Backup Tools .......................................................................................................... 7-13 R/3 and DB2 Database Backup Tools .......................................................................................................... 7-14 ADM Backup Tools ................................................................................................................... 7-15 Backup Cycle: DB2 Database Backups ....................................................................................................... 7-16 Backup Frequency Considerations ............................................................................................................... 7-17 DB2 Backup Process Architecture ............................................................................................................... 7-18 Using DB2 Backup Parallelism ................................................................................................................... 7-19 DB2 Backup Performance Tips.................................................................................................................... 7-20 Backup Device Configuration Tips .............................................................................................................. 7-21
Block Size Limits of Selected Backup Devices ........................................................................................... 7-22 Tivoli Storage Manger Administration ........................................................................................................ 7-23 Advanced Backup Scenarios: Split Mirror Backup...................................................................................... 7-24 Advanced Backup Scenarios: Standby Database ......................................................................................... 7-25 Importance of Offline Log Files................................................................................................................... 7-26 2-Step Log File Management ....................................................................................................................... 7-27 Native BRARCHIVE Tape Management .................................................................................................... 7-28 Tools for Offline Log File Management ...................................................................................................... 7-29 Tools for Offline Log File Management ...................................................................................................... 7-30 Tools for Offline Log File Management ...................................................................................................... 7-31 Tape Management Recommendations ......................................................................................................... 7-32 Backup Cycle ............................................................................................................................................... 7-33 1-Step Log File Management ....................................................................................................................... 7-34 Data Backup Objects .................................................................................................................................... 7-35 Unit Summary .............................................................................................................................................. 7-36 Unit Actions ................................................................................................................................................. 7-37 Backup and Log File Management: Exercises ............................................................................................. 7-38 Backup and Log File Management: Solutions ............................................................................................. 7-39 Restore and Recovery ........................................................................................................................................ 8-1 Restore and Recovery .................................................................................................................................... 8-2 Roll Forward Vs. Restart Recovery ............................................................................................................... 8-3 Restart Recovery: Background ...................................................................................................................... 8-4 Restart Recovery ............................................................................................................................................ 8-5 Roll Forward Recovery .................................................................................................................................. 8-6 Roll Forward Recovery Scenarios.................................................................................................................. 8-7 Database Recovery: Work Flow .................................................................................................................... 8-8 Restore and Recovery with Logical Failures ................................................................................................. 8-9 Disk Crash: Lost Containers ........................................................................................................................ 8-10 DB2 Restore Performance Tips.................................................................................................................... 8-11 DB2‟s Restore Tools .................................................................................................................................... 8-12 DB2 Restore Tools: DB2 Control Center .................................................................................................... 8-13 DB2 Restore Tools: DB2 Control Center - Tablespaces .............................................................................. 8-14 DB2 Restore Tools: DB2 Control Center - Containers ................................................................................ 8-15 DB2 Restore Tools: DB2 Control Center - Roll Forward ............................................................................ 8-16 DB2 Restore Tools: DB2 Control Center - Options ..................................................................................... 8-17 DB2 Restore Tools: DB2 CLP Commands .................................................................................................. 8-18 Log File Life Cycle for 2-Step Log File Management ................................................................................. 8-19 Log File Management: DB2 Control Center ................................................................................................ 8-20 Log File Management: BRRESTORE ......................................................................................................... 8-21 Restoring the Administration Database ........................................................................................................ 8-22 Why Perform a Redirected Restore? ............................................................................................................ 8-23 SAP Redirected Restore Tool: brdb6brt ....................................................................................................... 8-24
Restarting the DB2 CLP Script .................................................................................................................... 8-25 Reducing Tablespaces With Redirected Restore .......................................................................................... 8-26 Reducing the High Watermark With db2dart............................................................................................... 8-27 Restore: Preventing Backup Errors .............................................................................................................. 8-28 Classification of Database Recoveries ......................................................................................................... 8-29 Database Restoring Tips .............................................................................................................................. 8-30 Unit Summary .............................................................................................................................................. 8-31 Unit Actions ................................................................................................................................................. 8-32 Restore and Recovery: Exercises ................................................................................................................. 8-33 Restore and Recovery: Solutions ................................................................................................................. 8-34 Storage Management .......................................................................................................................................... 9-1 Storage Management ...................................................................................................................................... 9-2 Database Storage Concepts ............................................................................................................................ 9-3 Database Objects: Tables and Indexes ........................................................................................................... 9-4 Table Storage Details ..................................................................................................................................... 9-5 Index Storage Details ..................................................................................................................................... 9-6 Tablespace Details ......................................................................................................................................... 9-7 Data Distribution Using DB2 Containers ....................................................................................................... 9-8 System Managed Vs. Database Managed Space ............................................................................................ 9-9 Temporary Tablespaces: Recommendations ................................................................................................ 9-10 Database Object Growth: Extent Allocation ................................................................................................ 9-11 Database Object Growth: Out of Space Situation ........................................................................................ 9-12 Monitoring Tablespace Growth: History ..................................................................................................... 9-13 Monitoring Tablespace Storage Allocation .................................................................................................. 9-14 Monitoring Container Placement ................................................................................................................. 9-15 Extending Database Storage Using DB2 CLP ............................................................................................. 9-16 Automated Rebalancing DB2 Extents .......................................................................................................... 9-17 Monitoring Rebalancing Progress ................................................................................................................ 9-18 DB2 Rebalancing Strategy ........................................................................................................................... 9-19 DB2 Recovery and Rebalancing: Possible Problems ................................................................................... 9-20 DB2 Redirected Restore: Redefinition of Storage ....................................................................................... 9-21 Table Reorganization Check in CCMS ........................................................................................................ 9-22 Freespace Monitoring of Tables ................................................................................................................... 9-23 Monitoring Fragmentation of Tables in R/3................................................................................................. 9-24 Overflow Records ........................................................................................................................................ 9-25 Index Cluster Properties ............................................................................................................................... 9-26 DB2 Table Reorganization in Same Tablespace .......................................................................................... 9-27 Reorganizing Tables Using Tablespace TEMP............................................................................................ 9-28 Reorganization Tools in CCMS ................................................................................................................... 9-29 Reorganizing Large Tables .......................................................................................................................... 9-30 Online Index Reorganization ....................................................................................................................... 9-31 Creating Additional Tablespaces ................................................................................................................. 9-32
Unit Summary .............................................................................................................................................. 9-33 Unit Actions ................................................................................................................................................. 9-34 Storage Management: Exercises .................................................................................................................. 9-35 Storage Management: Solutions................................................................................................................... 9-36 Performance Monitoring .................................................................................................................................. 10-1 Performance Monitoring .............................................................................................................................. 10-2 Database-Related Performance Issues ......................................................................................................... 10-3 Memory Configuration................................................................................................................................. 10-4 DB2 Memory Configuration 1 ..................................................................................................................... 10-5 Database Memory Configuration 2 .............................................................................................................. 10-6 Memory Configuration 3.............................................................................................................................. 10-7 Memory Configuration 4.............................................................................................................................. 10-8 Buffer Pool Considerations .......................................................................................................................... 10-9 Assessing the Efficiency of the Buffer Pool .............................................................................................. 10-10 Increasing the Size of the Buffer Pool ....................................................................................................... 10-11 Package and Catalog Cache Considerations .............................................................................................. 10-12 Sorting Area Considerations ...................................................................................................................... 10-13 Understanding Overflow Sorts ................................................................................................................... 10-14 Lock List Considerations ........................................................................................................................... 10-15 Inefficient Application Design ................................................................................................................... 10-16 When a Lock Wait Situations Occurs ........................................................................................................ 10-17 Identifying a Lock Wait Situation with R/3 ............................................................................................... 10-18 Reducing Exclusive Lockwaits .................................................................................................................. 10-19 Application Problems: Unnecessary Statements ........................................................................................ 10-20 Identifying Unnecessary SQL Statements .................................................................................................. 10-21 Eliminating Unnecessary SQL Statements................................................................................................. 10-22 Application Problems: Poorly Qualified Statements .................................................................................. 10-23 Analyzing a Poorly Qualified Statement .................................................................................................... 10-24 Performing an Explain Plan: Details .......................................................................................................... 10-25 Identifying Missing Indexes in Database ................................................................................................... 10-26 Dynamic SQL Cache Analysis ................................................................................................................... 10-27 Analyzing the Dynamic SQL Cache .......................................................................................................... 10-28 Analyzing SQL Sorts ................................................................................................................................. 10-29 Understanding DB2's FETCH Statements ................................................................................................. 10-30 Cost-Based Optimizer ................................................................................................................................ 10-31 Refreshing the Object Statistics ................................................................................................................. 10-32 Database Job Monitor (Transaction DB24)................................................................................................ 10-33 Modifying the Algorithm to Update the Statistics ..................................................................................... 10-34 Reasons for Performance Problems ........................................................................................................... 10-35 Physical and Logical Layout ...................................................................................................................... 10-36 Physical I/O Contention ............................................................................................................................. 10-37 Identifying I/O Contention in the Database ............................................................................................... 10-38
Solving I/O Contention .............................................................................................................................. 10-39 Optimize I/O on RAID Devices ................................................................................................................. 10-40 Solving Performance Problems .................................................................................................................. 10-41 Unit Summary ............................................................................................................................................ 10-42 Unit Actions ............................................................................................................................................... 10-43 Performance Monitoring: Exercises ........................................................................................................... 10-44 Performance Monitoring: Solutions ........................................................................................................... 10-45 Troubleshooting ............................................................................................................................................... 11-1 Troubleshooting ........................................................................................................................................... 11-2 Begin Your Daily Work with Monitoring .................................................................................................... 11-3 DB Monitoring: Freespace in Tablespaces .................................................................................................. 11-4 Daily DB Monitoring: Tablespace Growth .................................................................................................. 11-5 DB Monitoring: Last Backup and Archive Results ...................................................................................... 11-6 DB Monitoring: Backup Log ....................................................................................................................... 11-7 DB Monitoring: Freespace in Directory log_dir .......................................................................................... 11-8 Resolving Problems with DB2 Logging User Exit ...................................................................................... 11-9 Solving Problems with DB2 Logging User Exit ........................................................................................ 11-10 Solving Problems with DB2 Logging User Exit ........................................................................................ 11-11 DB Monitoring: Missing Indexes ............................................................................................................... 11-12 DB Monitoring: Runstats Activity ............................................................................................................. 11-13 Database Job Monitor: Runstats Activity ................................................................................................... 11-14 DB Monitoring: Mirroring the Administration Database ........................................................................... 11-15 Review db2diag.log ................................................................................................................................... 11-16 Monitoring Tools: Overview...................................................................................................................... 11-17 SAP System Administration Assistant (SSAA) ......................................................................................... 11-18 DB2 Diagnostic Files ................................................................................................................................. 11-19 Interpreting the db2diag.log file ................................................................................................................. 11-20 Decoding TechTalk in db2diag.log ............................................................................................................ 11-21 Tracing Database Malfunctions ................................................................................................................. 11-22 Preventative Maintenance .......................................................................................................................... 11-23 Database Utilities ....................................................................................................................................... 11-24 Roadmap for Start Problems ...................................................................................................................... 11-25 Roadmap Stop Problems ............................................................................................................................ 11-26 Files Needed for SAP DB2 Database Hotline ............................................................................................ 11-27 Unit Summary ............................................................................................................................................ 11-28 Unit Actions ............................................................................................................................................... 11-29 Troubleshooting: Exercises ........................................................................................................................ 11-30 Troubleshooting: Solutions ........................................................................................................................ 11-31
BC535 Database Administration DB2 UDB on UNIX/NT
Database Administration DB2 UDB on UNIX/NT
BC535
R/3 Release 4.6B R/3 Release 4.6B
SAP AG 1999 50038148 50038148
Copyright
Copyright 2001 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. All rights reserved.
SAP AG 2001
Trademarks:
Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of Microsoft Corporation. IBM®, DB2®, OS/2®, DB2/6000®, Parallel Sysplex®, MVS/ESA®, RS/6000®, AIX®, S/390®, AS/400®, OS/390®, and OS/400® are registered trademarks of IBM Corporation. ORACLE® is a registered trademark of ORACLE Corporation. INFORMIX®-OnLine for SAP and INFORMIX® Dynamic ServerTM are registered trademarks of Informix Software Incorporated. UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group. HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. JAVA® is a registered trademark of Sun Microsystems, Inc. JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. SAP, SAP Logo, R/2, RIVA, R/3, ABAP, SAP ArchiveLink, SAP Business Workflow, WebFlow, SAP EarlyWatch, BAPI, SAPPHIRE, Management Cockpit, mySAP.com Logo and mySAP.com are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other products mentioned are trademarks or registered trademarks of their respective companies.
SAP Basis Administration Training 4.6
Core Competence
MBC30 2 days R/3-Technical Implementation and Operation Management BCtcc* Technical Core Competence BC325 5 days BC305 3 days Advanced R/3 System Administration BC315 3 days
Expert Competence
BC505 3 days Database Administration Oracle BC511 5 days Database Administration Informix BC515 2 days Database Administration SAP DB BC520 3 days Database Administration MS SQL Server BC525 3 days Database Administration DB2/400 BC530 5 days Database Administration DB2/390 BC535 3 days Database Administration DB2 UDB BC555
Corresponding R/3 Basis Knowledge Product and/or SAP Expert Knowledge Book
3 days
Software Logistics
MY301 2 days
Workload Analysis
3 days
BC350**
Workplace
TCC Workplace
*Technical Core Competence Versions BC310 BC312 BC314 BC317 BC360 BC361 BC362 BC370 BC390 **BC350 Windows NT / Oracle Windows NT / SAP DB Windows NT MS SQL Server (Windows NT / UNIX) / DB2 UNIX / Oracle UNIX / Informix UNIX / SAP DB IBM AS400 IBM /390 Workplace (combined with MY301)
2 days
LiveCache Administration
Database Administration Training
SAP AG 1999
(C) SAP AG
BC535
1-1
(C) SAP AG
BC535
2-1
Course Overview
SAP AG 1999
(C) SAP AG
BC535
3-1
Course Prerequisites - Audience - Duration
Prerequisites:
SAP 50 R/3 Basis Technology BC317 Technical Core Competence (DB2 on UNIX/Windows NT) Basic knowledge of the UNIX or Windows NT operating systems
Audience:
Project team members responsible for the administration of SAP R/3 DB2 UDB database servers Technical consultants responsible for the installation, troubleshooting, and performance tuning of DB2 UDB database servers
Duration:
3 days
SAP AG 1999
(C) SAP AG
BC535
3-2
Course Objectives
This course will enable you to: Create a safe and secure operating environment for DB2 UDB database servers in your SAP R/3 implementation Operate the DB2 servers on a daily basis Analyze and adapt your DB2 server performance to requirements in your SAP R/3 implementation Use the SAP DB2 Admin Extensions Maintain the highest possible availability through preventive maintenance
SAP AG 1999
(C) SAP AG
BC535
3-3
Course Contents
Preface Introduction Unit 1 Unit 2 Unit 3 Unit 4 DB2 UDB Fundamentals DB2 UDB Cost-Based Optimizer DB2 UDB Tool Environment Backup & Log File Management Conclusion Appendix Unit 5 Unit 6 Unit 7 Unit 8 Restore & Recovery Storage Management Performance Monitoring Troubleshooting
SAP AG 1999
(C) SAP AG
BC535
3-4
Course Introduction
R/3 on DB2 Universal Database Version 6 enables the use of IBM‟s DB2 UDB database architecture on hardware systems ranging from Intel-based Windows NT servers, small UNIX servers, to high end SMP UNIX systems This course provides all the information necessary to safely operate the R/3 System on DB2 UDB servers in these environments This course is geared towards technical staff in R/3 implementation teams who are responsible for DB2 database servers
SAP AG 1999
(C) SAP AG
BC535
3-5
The Required Administration Knowledge
“Imagine a thunderstorm. Although the dynamics of meteorology are well understood in our times, we still cannot predict when and where the lightning will strike. However, we can safeguard ourselves through proper behavior based on knowledge. Think about your database. Again, though proper knowledge you will be able to decrease risks. Always be prepared!”
SAP Technical Core Competence DB2 Center of Expertise
SAP AG 1999
(C) SAP AG
BC535
3-6
(C) SAP AG
BC535
4-1
DB2 Database Fundamentals Part 1
Contents
DB2 database software components DB2 instances and R/3 instances R/3- and DB2-supported combinations DB2 FixPaks
Objectives
At the end of this section, you will be able to: Identify the DB2 database software components Identify the DB2 instance and database of an R/3 System Access the current information regarding the supported combinations of R/3 and DB2 UDB versions Access the relevant information about SAP supported FixPaks
SAP AG 1999
(C) SAP AG
BC535
4-2
DB2 Database Software Components
Host A
R/3 central instance IPC
(Local)
Host B
R/3 instance Network DB2 UDB administration client
TCP/IP
(Remote)
DB Server DB2 UDB EE
DBA workstation
DB2 UDB administration client DB2 Control Center SAP DB2admin tools
SAP AG 1999
For each R/3 System, there is one database server. The database server has core database software installed: DB2 Universal Database Enterprise Edition. If there is only one application server running, and this application server is installed on the database server, there is no need to install further software on the system. In this case, the communication between database server and application server is based on inter process communication (IPC). For all remote application servers, you must install the remote client access software. In DB2, it is called the DB2 Client Application Enabler. For each of the workstations of the database administrators, the WIN32 version of the DB2 Client Application Enabler must be installed. It contains the DB2 Control Center that hosts the R/3 DB2ADMIN software. The R/3 DB2ADMIN software can be installed by executing program db2ainst.exe on the workstation.
(C) SAP AG
BC535
4-3
DB2 Instances and R/3 Instances
R/3 System
R/3 instance DVEBMGS00 R/3 instance DV00 R/3 Instance DV00
What is a DB2 instance?
DB2 instance DB2
R/3 DB
Admin DB
(R/3 >= 4.0)
ADM
DB2 administration server
Instance DB2DAS00 for NT Instance DB2A for Unix
SAP AG 1999
An R/3 System, identified by , consists of: One or more R/3 instances The DB2 database instance DB2, which contains: - The R/3 database - The administration database, called ADM, which is needed for administrative purposes and is replicated in the R/3 database
Each R/3 System has its own DB2 database instance DB2. On UNIX, the DB2 instance is started together with the R/3 System. On Windows NT, the DB2 instance runs as a service. To retrieve configuration parameters for the DB2 instance, use command db2 get dbm cfg. To obtain parameters for database within the instance, use command db2 get db cfg for . In addition to the DB2 instance DB2, a second database instance DB2DAS00 (NT) / DB2AS (Unix) is installed on the database server to allow remote administration of the database server.
(C) SAP AG
BC535
4-4
R/3 and DB2 Supported Combinations
DB2 versions
V5
V6.1
R/3 Releases
3.0D...3.1H 3.1I 4.0A 4.0B 4.5A 4.5B 4.6A 4.6B
For further information, refer to http://sapnet.sap-ag.de/dbosplatforms
SAP AG 1999
For current information regarding supported combinations of R/3 and DB2 UDB versions, refer to SAPNet http://sapnet.sap-ag.de/dbosplatforms. Please pay special attention to upgrade restrictions that apply to particular combinations. You may encounter names other than DB2 Universal Database used as synonyms in SAP documentation (such as in the SAP Notes). This is because of the history of frequently changed names of DB2: DB2/6000 DB2 for AIX DB2 Common Server (or DB2 CS for short) DB2 Universal Database (or DB2 UDB for short)
Therefore, the internal abbreviation for the DB2 Universal Database on Unix/NT is DB6. Do not use DB2 when searching for information in SAPNet because this is the abbreviation used for DB2 on OS/390.
(C) SAP AG
BC535
4-5
DB2 FixPaks
SAP performs special integration tests with IBM
supplied FixPaks
SAP Note 101809 supplies information about the
supported FixPaks
SAP Note 166481 supplies information about
available RDBMS CD-ROMs, including FixPaks
STOP
Only use SAP supplied FixPaks
SAP AG 1999
When IBM releases a new DB2 version or FixPak, SAP performs special tests on them to ensure compatibility with R/3. Every FixPak that was successfully tested is documented in SAP Note 101809, and can be installed by customers. For information about how to retrieve the appropriate RDBMS CD-ROM, see SAP Note 166481. Ensure you only use RDBMS software (including FixPaks) that was shipped by SAP.
(C) SAP AG
BC535
4-6
DB2 Database Fundamentals Part 2
Contents
DB2 process model (Unix database servers) DB2 thread model (Windows NT database servers) Startup of DB2 Shutdown of DB2 DB2 TCP/IP connection setup DB2 IPC connection setup SQL statement processing
Objectives
At the end of this section, you will be able to: Identify the differences between DB2 for Unix and Windows NT Start the DB2 database Shut down the DB2 database Describe the DB2 TCP/IP connection setup
SAP AG 1999
(C) SAP AG
BC535
4-7
The DB2 Process Model for Unix Database Servers
Watch dog
Per instance
System control Global daemon spawner IPC communication manager TCP/IP communication manager
Per database
Prefetcher/ I/O server
Page cleaner/ I/O cleaner
Logger
Per connection
Agent
SAP AG 1999
One of the differences with the implementation of DB2 on UNIX and Windows NT is that the architecture on Unix is based on processes and the architecture on Windows NT is based on threads. DB2‟s processes/threads are divided into three groups: The per instance processes/threads are started to enable the database manager system to run properly The per database processes/threads are started for each database running within the database manager The per connection processes/threads are started for each application program (or work process) connecting to the database
The DB2 processes/threads can be monitored using: Unix: ps -ef (on Solaris use: /usr/ucb/ps -awwx) NT: qslice
(C) SAP AG
BC535
4-8
The per instance processes are: The listener processes - An inter-process communications listener for local client programs (db2ipccm) - One listener for each configured communication protocol, which is only TCP/IP in a R/3 system (db2tcpcm) The database manager process or system control process (db2sysc) The watch dog process (db2wdog), which is responsible for the cleanup of system resources (this is the only process that runs under root authority, all other processes are run with the instance owner db2). The global daemon spawner (db2gds), which forks the database processes (exception: db2 agent processes for remote connections).
(C) SAP AG
BC535
4-9
Each DB2 database has a group of I/O processes: One or more prefetchers (db2pfchr), used for asynchronous retrieval of data from disk into the buffer pool (db2 prefetchers are also called I/O servers) One or more page cleaners (db2pclnr), which write changed pages from buffer pool to disk asynchronously (db2 page cleaners are also called I/O cleaners) One logger process (db2loggr) for manipulation of database log files
Each work process (local + remote) is connected to a corresponding agent process (db2agent). If intra-query parallelism is enabled, this process is called the coordinator agent. DB2 then assigns a pool of sub-agents to a coordinator agent to work on parts of the same task. This is called intra-query parallelism, which reduces processing time on symmetric multiprocessor (SMP) systems. This setting is useful for SAP Business Warehouse database servers.
(C) SAP AG
BC535
4-10
The DB2 Thread Model for NT Database Servers
System control
Per instance threads
IPC communication manager
TCP/IP communication manager
Per database threads
Prefetcher/ I/O server
Page cleaner/ I/O cleaner
Logger
Per connection threads
SAP AG 1999
Agent
DB2 implementation on Windows NT is multi-threaded. As with UNIX, there are three distinct functional groups, but they are implemented as threads. All threads run within the Windows NT program db2sysc.exe. Use the Windows NT resource tool kit program qslice to view the threads in a program. With this program, you can monitor the growing number of threads in program db2sysc during R/3 startup. More information can be accessed using the NT Performance Monitor perfmon.exe, which is located in the administrative program group, as described in section Performance Monitoring. Other than the above mentioned differences, the implementation of DB2 on UNIX and Windows NT are very similar. Threads and processes that are managed by the DB2 database server engine are also called EDUs (engine dispatchable units).
(C) SAP AG
BC535
4-11
Startup of DB2
Explicit start of DB2 instance: command db2start
Explicit start of DB: command db2 activate
Buffer pool(s), etc.
or Implicit start of DB: at first connection
R/3 disp+work
SAP AG 1999
During R/3 startup, the DB2 instance is started with command db2start (if it is not already started). All the per-instance processes are started. There are two different ways to activate a DB2 database: Implicitly, through the activation of the database by first connection request Explicitly, executing command db2 activate database or db2 connect to
During activation, the per-database processes are started, the database buffers are initialized, and log files are created if needed. A DB2 agent (process db2agent) is created to service the connection request. Note: The DB2 database is not active after executing command db2start. The database is only started if a database connection has been established. Therefore, the first connection request to a DB2 database may take a while, until all buffers and files are initialized. On Unix, the DB2 watch dog process is a process without a controlling terminal. These processes are typically called daemon processes. This means the program will not stop when the user who started the program logs off. On Windows NT, the DB2 instance runs as a service (defined for automatic startup in NT registry).
(C) SAP AG
BC535
4-12
Shutdown of DB2
Explicit stop of DB2 instance: command db2stop
Explicit stop of DB: command db2 deactivate
Buffer pool(s), etc.
or Implicit stop of DB: with end of last connection
R/3 disp+work
SAP AG 1999
During shutdown, the DB2 I/O servers flush the buffer pool(s) to disk and then stop the per-database processes. There are two ways to shut down a DB2 database: Explicitly, by executing the command db2 deactivate database Implicitly, when the last connection has been reset, the database shuts down automatically. Note: If the database was activated using the command db2 activate, it can only be stopped with command db2 deactivate.
The DB2 instance can be stopped with command db2stop. As long as applications are still running, the database instance cannot be stopped. To check the status of the applications, use command db2 list applications [show detail]. To perform an emergency shutdown of DB2, use command db2stop force. All applications are disconnected, running transactions are rolled back, and the buffers of the database are consistently written to disk.
(C) SAP AG
BC535
4-13
DB2 TCP/IP Connection Setup
disp+work db2tcpcm db2agent R/3 disp+work 1 1 Arbitrary port A A sapdb2 B B fork/exec
3 3
Instance processes
db2tcpcm 2 2
C C
Arbitrary port
Connection processes
db2agent
SAP AG 1999
This example shows the DB2 processes that are used during remote database connection setup. For the connection setup (red numbering), the following scenario applies: An application program connects to the TCP/IP listener (db2tcpcm) on a predefined TCP/IP port. This port is defined in the DBM CFG parameter SVCENAME. (1) The TCP/IP listener establishes a new socket on another arbitrary port and forks itself into what becomes a DB2 agent. (2) The DB2 agent can now communicate directly with the application. The db2tcpcm is free to service additional requests. (3)
For the network communication (green lettering), the following scenario applies: The application sends a connection request from an arbitrary socket to the database server (port name sapdb2). (A) A new socket is created on an arbitrary port. On UNIX systems, the DB2 agent is created by forking the db2tcpcm. On Windows NT, a thread that acts as a DB2 agent is created. (B) The DB2 agent is responding on the new socket and the communication is established. First, the maximum requested I/O block size is negotiated by the client (RQRIOBLK). For each blocking cursor there is always a buffer of RQRIOBLK size on client side. This can allocate a substantial amount of memory per work process (check for blocked cursors in the Application Snapshot using transaction ST04 Detailed analysis menu). (C)
(C) SAP AG
BC535
4-14
This example shows the DB2 processes that are used during local DB2 database connection setup. Here, the communication method is called inter process communication (IPC). For connection setup, the following scenario applies: Since the database is cataloged locally, the application program connects to the local listener program (db2ipccm) through a message queue owned by db2ipccm. Information about the IPC is returned to the application program. (1) The DB2 global demon spawner (db2gds) is instructed to handle the application request. (2) The db2gds forks a DB2 agent on the database server on behalf of the application program. On Windows NT, a DB2 agent thread is created. The IPC information is known to the DB2 agent since it is forked. (3) Local connections are established through the application support layer (ASL) heap, which acts as the main IPC component between the application and the DB2 agent. (4)
Local connections are faster than connections made through TCP/IP.
(C) SAP AG
BC535
4-15
SQL Statement Processing (1)
SQL statement
R/3 work process
SELECT author, devclass, obj_name FROM sapr3.tadir WHERE 2 2 author=‟JOHN DOE‟
1/6 1/6
Execution plan
SELECT STATEMENT | (Estimated Costs = 661 [timerons] ) --RETURN | --FETCH TADIR | ------IXSCAN TADIR~2 #key columns: 1
Per instance
Buffer pool
Agent pool
db2agent
db2agent
5 5
Per connection
3 3
4 4 Disks
db2pfchr
Per database
SAP AG 1999
All work processes connect to the database as user sapr3. R/3 System users do not have a database system user ID. A work process is only active for one user during the dialog step, between screen input and screen output. Before screen output, the work process issues a commit to the database. This means different users use the same work process and database connection consecutively. The work process does not connect to and disconnect from the database between the dialog steps. This example shows the processes used for handling SQL statements: An SQL statement is sent from the R/3 work process to its associated agent. (1) The DB2 Optimizer in the DB2 agent transforms the SQL statement into an access plan on data. This process is called preparation. The plan may also be cached already. (2) In many cases, the agent forwards information about the pages that need to be transferred from data containers into the buffer pool to the DB2 I/O servers. (3) Several I/O servers work on the requests, performing I/O in parallel. (4) The agent performs additional tasks, as defined in the DB2 access plan. In this example, the execution plan is an index scan with one search argument. If work can be divided into subtasks and intra parallelism is used, the agent uses agents out of the agent pool to enable parallel processing. (5) The data is sent to the application using a FETCH SQL command. (6)
(C) SAP AG
BC535
4-16
SQL Statement Processing (2)
SQL statement
R/3 work process 1 1
UPDATE sapr3.tadir SET devclass = “ZXX” WHERE author=‟JOHN DOE‟
Log buffer
Log records
3 3
db2agent
Buffer pool
Per connection
2 2
db2agent
Per database
db2loggr
db2pclnr
4 4
log_dir log_dir
DB files
SAP AG 1999
This example shows the processes that handle database logging using a processing example: UPDATE/INSERT SQL statements are sent from application to the associated DB2 agent. (1) Data is manipulated in the buffer pool but not transferred to disk. The db2agent process writes information about the changes in the buffer as log records in the log buffer. (2) The db2loggr process is responsible for writing the contents of the log buffer to the current active log file. (3) This is done: - When an application ends the transaction by issuing a COMMIT/ROLLBACK SQL statement - Every second - When the log buffer is full I/O cleaners, also known as page cleaners, copy updated pages onto disk when triggered. (4) I/O cleaners get triggered when: - The maximum amount of log space that should be read during crash recovery has been reached (DB CFG parameter SOFTMAX) - The maximum percentage of changed pages has been reached (DB CFG parameter CHNGPGS_THRESH) - No pages are available during insert/update. In this case, “victim page cleaning” is started. - Note: I/O cleaners can be configured in a wide range.
(C) SAP AG
BC535
4-17
Parallel I/O Servers and I/O Writers
Buffer pool R/3 work process
db2agent
db2agent
REA D
&W RITE
db2clnr 1
n
db2pfchr n 1
Disk 1
WRITE
R
D EA
&
READ
T RI W
E
Disk 2
Disk n
SAP AG 1999
To achive optimal utilization of the I/O devices (physical disks), the number of I/O processes can be configured: - With disks containing database containers, you should configure the number of I/O servers slightly higher than . This way, there is always an I/O server available until the number of disks has been reached. In the unlikely situation that all of the I/O servers are busy, there is no reason to introduce more since they are all waiting for I/O on the disks. More I/O servers would only put higher load on the disk drives and increase the asynchronous read times. - As a rule of thumb, the number of I/O servers should not be higher than 25 on very fast SMP database servers and not higher than 12 on very fast uni-processor database servers. - If Transaction ST04 displays a higher value on the average asynchronous read time of I/O servers, you should decrease the number of I/O servers.
LONG VARCHAR data is not buffered by DB2's buffer pool(s). In this case, synchronous I/O is performed by the DB2 agents. Most of the tables that use unbuffered data types are buffered in the application servers. To display the list of tables that contain unbuffered data, issue the following SQL statement: db2 SELECT name, tbname, length FROM sysibm.syscolumns WHERE coltype ='LONGVAR'
If any performance bottlenecks occur, these unbuffered data objects should be analyzed.
(C) SAP AG
BC535
4-18
DB2 Database Fundamentals Part 3
Contents
Logging concept Database log file handling Logging modes
Objectives
At the end of this section, you will be able to: Identify online and offline log files Deactivate circular logging and activate log retention mode
SAP AG 1999
(C) SAP AG
BC535
4-19
Database Logging (1)
R/3 work process
Log file header (SQLOGCTL.LFH) Recovery range
Synchronous write on COMMIT/ROLLBACK
Log files Log buffer Buffer pool
db2agent
Per connection
Asynchronous write when triggered
db2agent
Per database
SAP AG 1999
db2loggr
db2pclnr
log_dir
DB files
When an operation is committed or rolled back, all the contents of the log buffer are transferred to the current active log file. COMMIT/ROLLBACK statements are synchronous, that is, they are returned when the write operation is finished. If there is not enough space for writing the log records, database error message SQL0964 (transaction log for the database full) will occur. Since the log files are mandatory for recovery and the write operation is synchronous, the log files must reside on mirrored and fast disks. A good configuration is RAID 1. The copying of changed pages from buffer pool(s) to container files happens asynchronously. RAID 5 storage for the containers may be appropriate, although RAID 1 is faster. When the changed pages have been written to disk by the I/O servers, DB2 will not need to re-create those pages during crash recovery. Because of this, the log file header is moved “forward” to the first log record which contains data about pages that are changed and not on disk. If the LFH moves out of a log file, this file is not required for a crash recovery. Note: File sqlogctl.lfh contains the log file header. If this file is not readable, crash recovery is not possible. In this case, the database must be restored. Therefore, the home directory of the DB2 instance owner db2 should be located on a mirrored device as well.
(C) SAP AG
BC535
4-20
This example shows how log file are handled: Pages are changed in the buffer pool by the db2agent process/thread. (1) Information about these changes are written as log records in the log buffer. (2) Log records are written from the log buffer to log files in directory log_dir by the db2loggr process/thread. (3) This is done: - When an application ends the transaction by issuing a COMMIT/ROLLBACK SQL statement - Every second - When the log buffer is full The status of this log file is online active. When a log file is filled with records, the db2 user exit (program db2uext2.exe) is called by the database and copies the log file into directory log_archive. The copied log file is called the offline log. When all referenced pages have been written to disk (from the buffer pool) the online active log file is deleted in the log_dir directory, but a copy of it remains as an offline log in the log_archive directory. (4)
(C) SAP AG
BC535
4-21
All online active log files reside in directory log_dir. Active log files contain committed and uncommitted transaction data that refer to changed pages in the buffer pool, which have not been written to disk yet. They are needed for ROLLBACK operations and crash recovery. Active log files are deleted only when they are no longer needed by DB2 because all referenced transactions are committed and all changed pages have been written down to disk. Also the DB2 user exit must have been successful in copying the active logs into the log_archive directory. Note: DB2 is configured to run the DB2 restart utility if an incorrect database shutdown is detected. DB2 then reads the online active log files to recreate the contents of the buffer pool at the time of the system crash and rolls back all unfinished transactions.
(C) SAP AG
BC535
4-22
The log files that have been copied by the DB2 user exit reside in the directory log_archive. They are called offline because they are no longer needed by the database for rollback operations or a crash recovery. However, they have to be copied back to directory log_dir in case of a roll forward recovery. When directory log_archive fills with offline logs, the DB2 database administrator must archive log files using the CCMS or the DB2 Control Center. The state of log files on tape or ADSM is archived. Archiving is important since the database cannot continue to run if there is no space available. To avoid the situation of having no space available, the log files must be archived regularly.
(C) SAP AG
BC535
4-23
At database startup, DB2 searches for log files in directory log_dir. If no log files exist, all primary log files are created by DB2. The total number of these log files is defined in the database configuration parameter LOGPRIMARY. The size of the log files (in 4KB pages) is defined in the database configuration parameter LOGFILSIZ. All log files are created during database operation Restore into new. In most cases, you may not see the full number of log files. Secondary log files are created on demand during database operations. The total number of secondary log files is defined in the database configuration parameter LOGSECOND. If the primary log files are all in use, secondary log files are created.
(C) SAP AG
BC535
4-24
Circular Logging
LOGRETAIN = OFF log_dir
X
BC535
Active
Inactive
SAP AG 1999
A DB2 database is configured in circular logging mode if the database configuration parameters LOGRETAIN and USEREXIT are set to OFF. This is the initial setting of the database after it is created, but it should not be the default setting for an R/3 database. Circular logging recycles log files that are no longer used, such as files that are not active. Since log files are overwritten, you cannot perform a roll forward recovery in the circular logging mode. Do not run a database for the R/3 System in circular logging mode. If a disk error occurs, you may not be able to recover your database.
(C) SAP AG
4-25
Log Retention Mode
First active log file
LOGRETAIN = RECOVERY log_dir USEREXIT = ON log_archive
When full, db2uext2 is called
SAP AG 1999
SAP recommends running the database for your R/3 System in log retention mode. To do this, set the database configuration parameters USEREXIT to ON and LOGRETAIN to RECOVERY (in DB2 V2 and V5.x the parameter LOGRETAIN has to be set to ON). The total log space available for a database running in log retention mode is the same as for circular logging mode: LOGFILSIZ * (LOGPRIMARY+LOGSECOND). The database manager configuration parameter USEREXIT enables unattended transfers of log files from directory log_dir to directory log_archive. When a log file is filled with records, DB2 calls the logging user exit program db2uext2, which is integrated into the R/3 System. Note: Do not use the sample programs that are provided in the samples directory of the DB2 instance. In certain cases, you may have to move log files from the online log directory to the offline log directory manually. If this occurs, do not use the operating system command copy. Instead, manually call the DB2 logging user exit. For more information about the DB2 logging user exit, see the documentation of the sample programs or the log file db2uext2.log. The logging user exit program writes log information for each execution into the administration database.
(C) SAP AG
BC535
4-26
Backup Pending Mode
Active
LOGRETAIN = OFF log_dir
Logging mode after installation
Inactive
Change parameters and restart DB
Database is backup pending
Perform full offline backup
LOGRETAIN = RECOVERY
Production logging mode
log_dir
USEREXIT = ON log_archive
SAP AG 1999
The initial setting of parameters LOGRETAIN and USEREXIT have to be changed after the installation in order to enable roll forward recovery. When these parameters are set and become effective (for example after restarting the database), DB2 goes into backup pending state. You must then perform a complete offline database backup. This backup is used as the starting point for recovery. You cannot connect to the database until this offline database backup has been successfully completed.
(C) SAP AG
BC535
4-27
DB2 Database Fundamentals Part 4
Contents
Naming conventions Users and groups Data security
Objectives
At the end of this section, you will be able to: Identify the structures and naming conventions for DB2 and R/3 in a UNIX and NT environment Describe the DB2 users and system administrators for R/3 in a UNIX and NT environment Understand the importance of data security
SAP AG 1999
(C) SAP AG
BC535
4-28
R/3 & DB2 Structures and Conventions on NT
Directory
Meaning DB2 software (specified at SW installation) DB2 instance directory DB2 offline log directory DB2 temp. storage of log files during roll forward CD1 copy during install DB2 online log directory Database diagnostic path Data containers / index containers Owner of the objects created on the database R/3 system administrator R/3 service owner for system R/3 database instance owner and administrator DB2ADMIN instance owner
:\ :\db2 :\db2\\log_archive :\db2\\log_retrieve :\db2\\sapreorg :\db2\\log_dir :\db2\\db2dump :\db2\\sapdata1...N
Windows NT users : sapr3 adm sapse db2 db2admin
SAP AG 1999
In the R/3 database server environment, directory and file names are standardized on Windows NT. Directory \usr\sap\\SYS\exe\run contains the R/3 executable programs, which are also programs that are used for database administration, such as: brdb6rst brdb6bck brarchive
During the installation of the R/3 System on Windows NT, the user is prompted for the drive locations of the above directories. Note: Windows NT does not offer a single directory tree with mounted file systems. The diagnostics of the DB2 database are written to :\db2\\db2dump. Log files recording the archiving of log files reside in :\db2\saparch. Log files recording the restoring of archived log files are written to :\db2\saprest.
(C) SAP AG
BC535
4-29
R/3 & DB2 Structures and Conventions on UNIX
Directory
Meaning DB2 software (specified during DB2 installation) DB2 instance directory DB2 binaries, ADSM control files, documentation DB2 directories for containers (usually mount-point) DB2 online log directory DB2 offline log directory DB2 temp. storage of log files during roll forward Owner of the objects created on the database R/3 administrator R/3 database instance owner, database administrator DB2ADMIN instance owner
/$INSTDIR/db2_06_01/ /db2//db2 /db2//sqllib/bin,adsm,doc,... /db2//sapdata1..N /db2//log_dir /db2//log_archive /db2//log_retrieve
UNIX users: sapr3 adm db2 db2as
SAP AG 1999
On UNIX systems, the files that reside on Windows NT in \sqllib are located in directories defined by the hardware vendor/UNIX flavor. For example: On SOLARIS, it is located in directory /opt/IBMdb2/V6.1. On AIX, it is located in directory /usr/lpp/db2_06_01/sqllib. On Linux, it is located in directory /usr/IBMdb2/V6.1.
The diagnostics of the DB2 database are written to directory /db2//sqllib/db2dump. Log files recording the archiving of log files reside in directory /db2//saparch. Log files recording the restoring of archived log files are written to directory /db2//saprest.
(C) SAP AG
BC535
4-30
R/3 Naming Conventions: Review
Tablespace name Directory Path Container name
PSAP. container001 PSAP. container002
Prefix PSAP
Abbreviation
Extension D (data) or I (index)
PSAP PSAPBTABD
Logical layer Physical Physical layer layer
Prefix Tablespace name Ext. Meaning
PSAP PSAP PSAP PSAP PSAP PSAP PSAP PSAP PSAP PSAP PSAP PSAP PSAP SYSCATSPACE TEMP USER1 EL ES LOAD SOURCE DDIC PROT CLU POOL STAB BTAB DOCU D or I D or I D or I D or I D or I D or I D or I D or I D or I D or I D or I D or I DB2 Data Dictionary Sort Processes, temp tables, reorg Default tablespace Development environment loads Development environment sources Screen and report loads (ABAP) Screen and report sources (ABAP) ABAP Dictionary Log-like tables (for example, spool) Cluster tables Pool tables (for example, ATAB) Master data and transparent tables Transaction data, transparent tables Documentation, SAPscript, SAPfind
Used by
DB2 RDBMS DB2 RDBMS DB2 RDBMS R/3 Basis R/3 Basis R/3 Basis R/3 Basis R/3 Basis R/3 Applications R/3 Applications R/3 Applications R/3 Applications R/3 Applications R/3 Applications
SAP AG 1999
The DB2 database uses tablespaces. From a logical point of view, a tablespace holds database objects, such as tables and indexes. On disk, a tablespace consists of one or more data files, called containers. The capacity of a tablespace can be increased by adding containers to it. The R/3 naming convention for tablespace names is PSAP. The abbreviations in the tablespace name are part of the container name. Containers are numbered starting with 000. A full path specification for a DB2 container for R/3 System C11 would be: /db2/C11/sapdata1/PSAPBTABD.container002 (UNIX) h:\db2\C11\sapdata1\PSAPBTABD.container002 (Windows NT)
Note: The R/3 System and SAP tools (such as SAP DB2admin tools) require that the naming conventions be observed. Following the R/3 naming convention ensures fast and efficient support by SAP.
(C) SAP AG
BC535
4-31
OS/Database Users and Authorities
OS/DB users sapr3 adm db2
Description Owner of database tables and indexes used by the R/3 applications. Has no privileges to perform database administration. R/3 instance owner. Can perform database administration such as startup, shutdown, and backup. Has no privileges to access database tables. Database instance owner and database creator. This is the DB2 superuser. Has all privileges for database administration and connect. Description Database owner. Can perform database administration. Has privileges to access all database tables. Can perform database administration. Has no privileges to access data in the databases (highest level of system control authority). Can perform database administration. Has no privileges to access data in the databases (second level of system control authority). Description Connection can be made by users of the SYSCTRL group. Connection using user name and password.
Database authority SYSADM SYSCTRL SYSMAINT
Connect request Implicit Explicit
SAP AG 1999
The following database authorizations are important for performing database administration tasks in the R/3 environment. With DB2, they are implemented as operating system groups. SYSADM is the highest level of administrative authority. Users with SYSADM authorization can run utilities, issue database and database manager commands, and access the data in any table in any database within the database manager instance. This authorization can control all database objects in the instance, including databases, tables, views, indexes, table spaces, node groups, buffer pools, event monitors, and more. SYSADM is assigned to the operating system group specified by the configuration parameter SYSADM_GROUP. Only a user with SYSADM authorization can perform functions such as Change database manager configuration or Grant DBADM authority. Note: User db2 has SYSADM authorization. SYSCTRL is the highest level of system control authority. Users with SYSCTRL authorization can perform maintenance and utility operations against the database manager instance and its databases. These operations can affect system resources, but they do not allow direct access to data in the databases. System control authorization is designed for users administering a database manager instance containing sensitive data. SYSCTRL authorization is assigned to the group specified by the configuration parameter SYSCTRL_GROUP. Only a user with SYSCTRL authorization (or higher) can force users off the system, create or drop a database, create/drop/alter a table space, or restore a new database. Users with SYSCTRL authorization also have the implicit privilege to connect to a database. Note: User adm has SYSCTRL authorization. SYSMAINT is the second level of system control authority. However, SYSMAINT is not used in the R/3 environment.
(C) SAP AG
BC535
4-32
Roles of DB2 Database Administrative Users
Privileges required for SAP DB2admin actions such as backup and log archival:
Restricted database administration through SYSCTRL group membership
DB2 users for R/3 in a UNIX environment
OS user db2 adm OS group SYSADM SYSCTRL Database privileges Full R/3 database administration Restricted database administration
SAP AG 1999
To protect data and resources, DB2 uses a combination of external security services and internal access control information. During the first step, called authentication, the user must identify herself/himself. This is done by issuing an operating system user and the appropriate password when connecting to the database. DB2 verifies the given combination using operating system services. During the second step, called authorization, the database manager decides if the user is allowed to perform the requested action or to access the requested data. There are two types of permissions recorded by DB2: privileges and authority levels. Authority levels provide a method of grouping privileges and control maintenance and utility operations. Both users db2 and adm have sufficient authorization to perform DB2 administration in an R/3 System. The database administration tool SAP DB2 Admin Extensions requires the restricted database administration privileges available in the group SYSCTRL. SAP DB2 Admin Extensions only have access to the tables required for performing R/3 database administration in the background and to tables that reside in the administration database ADM. These privileges are assigned during R/3 installation or upgrade.
(C) SAP AG
BC535
4-33
Data Security: DB Access Passwords
Database server
R/3 application server
De-crypt using KEY
R/3 work process
Connect as sapr3 using de-crypted password
DB2
DB2DB6EKEY =XXXXXXX
Read dscdb6.conf
Authenticate user at the operating system level
dscdb6.conf
SAP AG 1999
An R/3 work process connects to the database by retrieving an encrypted password from file dscdb6.conf. The R/3 work processes, which are started by user adm, use this password to connect to the database as user sapr3. If the password at the operating system level is changed, file dscdb6.conf will not be synchronized, and R/3 will not start. To check for this error, enter command R3trans -d as user adm. To change the password of user sapr3 and user adm, you can perform the following: For UNIX: As user adm, use dscdb6up adm dscdb6up sapr3 For NT: As user adm, use dscdb6up.exe adm dscdb6up.exe sapr3 For BOTH: Use the SAP DB2 Admin Password management extension.
(C) SAP AG
BC535
4-34
Unit Summary
Now you are able to: Explain the basic concepts of the DB2 UDB database server Name the important database directories and their content Start and shutdown the DB2 UDB database Identify the operating system users and their DB2 UDB authorizations
SAP AG 1999
(C) SAP AG
BC535
4-35
Unit Actions
?
Do the exercises
Solutions for the exercises
SAP AG 1999
(C) SAP AG
BC535
4-36
DB2 UDB Database Fundamentals: Exercises
No. 1 2 3 4 5 6 Exercise Start your test database using the DB2 command line processor. Which two methods can you use to start the database? Stop your test database using the DB2 command line processor. Use R/3 to display detailed information about DB2 logging. Find the names of the online log files Which online log files have already been copied by the DB2 logging user exit? Check if your test database is running in LOG RETENTION mode using the DB2 command line processor. Switch on LOG RETENTION mode. Note: For the test databases, we only change the DB2 parameter LOGRETAIN. For your actual R/3 database, you must also change the DB2 parameter USEREXIT. 7 8 9 Check the mode of your test database after changing the parameter LOGRETAIN. Check if your test database is running in LOG RETENTION mode using the DB2 Control Center. Switch off LOG RETENTION mode. How can parameter SYSADM_GROUP grant database administrator privileges to the DBA user db2? Which group on the operation system is configured as DB2's SYSADM_GROUP? Which one as DB2's SYSCTRL_GROUP? 10 11 12 13 14 Check if the passwords of sapr3 and adm are in sync between the file dscdb6.conf and the operating system. List the authorizations for user adm. Get the current DB2 version and find out the applied FixPak. How many I/O servers are configured in your test database? How many I/O cleaner are configured in your test database?
(C) SAP AG
BC535
4-37
DB2 UDB Database Fundamentals: Solutions
No. 1 Solution To start the test database manager (DB2 instance), use command db2start. To start the database with the first connect, use command db2 connect to . To start the database without any database connections, use command db2 activate db . 2 To disconnect and shut down the database, use command db2 terminate. To stop the database without any database connections, use command db2 deactivate db . Note: The command db2 list applications shows the connections to all databases in one DB2 instance. To shutdown the database manager, use command db2stop. To shut down the database manager, even if an application is still connected to the database, use command db2stop force. To kill all databases processes, use command db2stop –kill. 3 Log on to the R/3 System. From the main R/3 menu choose Tools CCMS Control/Monitoring Performance menu Database Activity (or use transaction ST04). Then choose Detail Analysis menu Logging Overview. The names of the online log files are displayed in the lower part of the list. The directory log_archive is displayed in the middle part of the list. To retrieve the database configuration, use command db2 get db cfg for . Check for the value of parameter LOGRETAIN. To change the value, enter command db2 update db cfg for using LOGRETAIN RECOVERY. 7 To check the mode of the database, use command db2 connect to . Check the output of DB2 for the keywords backup pending mode. 8 To check the mode of the database, run the DB2 Control Center using command db2cc, and expand the DB2 database db2 icon. Right click on the database icon and choose Configure. Use the tab Logs to display the relevant parameters. To disable LOG RETENTION, search for the parameter description ‘Retain log files for roll-forward recovery and/or Capture’ or the DB2 parameter LOGRETAIN and click on it, choose the value No, and select OK. 9 The database manager configuration contains the parameter SYSADM_GROUP. This parameter contains the related operation system group. This can be checked by entering the command db2 get dbm cfg
BC535 4-38
4 5 6
(C) SAP AG
| grep –i group (use the grep command to filter the output) or by using the DB2 Control Center. Right click on the instance db2 icon and select the tab Administration. Search for the parameter description ‘System administration authority group’ and ‘System control authority group’. The related DB2 parameters are SYSADM_GROUP and SYSCTRL_GROUP. 10 Change to user : su – adm. To check the passwords for sapr3, use command R3trans –d. Check if the R3trans returns successfully. To check the passwords for adm. use command dmdb6snp –n -o DBASE_BUFFERPOOL. Check if the dmdb6snp returns successfully. 11 Change to user : su – tcdadm. Since you are logged on with this user ID, enter command db2 get authorizations Note: Direct authorizations must be set by database "grant commands". Indirect authorizations are set by user memberships to DB2’s administration groups. 12 To get the current DB2 version, use command db2level. (The last token displays the FixPak identifier) On Linux, you can also use command rpm –q –a |grep db2 to query the installed file sets. 13 14 To display the number of I/O severs, use command db2 get db cfg for | grep –i num_ioserver. To display the number of I/O cleaners, use command db2 get db cfg for | grep –i num_iocleaner.
(C) SAP AG
BC535
4-39
(C) SAP AG
BC535
5-1
DB2 Cost-Based Optimizer
Contents
DB2 UDB Cost-Based Optimizer Calling Explain Explain output Database configuration for CBO Runstats with DMDB6SRP SAP tools for updating statistics
Objectives
At the end of this section, you will be able to: Efficiently use the DB2 Cost-Based Optimizer Perform the necessary system administration tasks to ensure CBO performance Recognize poor SQL statements Update the statistics
SAP AG 1999
(C) SAP AG
BC535
5-2
Cost-Based Optimizer: Introduction
Performance problems Performance problems due to due to
1 1
3 3
Hardware Hardware configuration
2 2
Database configuration Database configuration
Application problems Application problems
...
Buffer pools Buffer pools Lock storage Lock storage Isolation level Isolation level
SAP AG 1999
...
Expensive Expensive statements statements
Poor database performance is normally caused by poor database configuration, application coding problems, or hardware capacity. To identify and correct performance problems in the first two categories, perform normal tuning in the database monitors as follows: Check the database configuration (1) - Check whether the buffer pool(s) or lock list are sized correctly, or if I/O operations are concentrated on one or more disks which then become overloaded. Use the database tools to verify the efficiency of application coding (2) - Check for lock contention or inefficient SQL coding - Check if SQL statements are being executed more frequently than necessary. - Check statements for any of the following high-cost factors: Poor WHERE clauses Missing indexes Fragmentation of data/ indexes on disk - Internal R/3 components might not be using the best possible isolation level. For example, you can use optimistic cluster reads to avoid locks resulting from standard cluster reads. Consult with your hardware partner if performance problems are due to hardware capacity. (3)
(C) SAP AG
BC535
5-3
Facts Not Observed by the CBO
Non-CBO issues
Static behavior Static behavior Dynamic behavior Dynamic behavior
Disk Disk
RAM RAM Paging Paging
...
Thrashing Thrashing
Layout Layout
SAP AG 1999
Striping Striping
This graphic shows some of the facts that the CBO does not observe during SQL statement execution. Static behavior is the term used for system behavior based on properties which exist regardless of the existing load on the database server: Disk behavior - Layout of database containers on disks. Are containers of a tablespace distributed over several disks, all having the same I/O characteristics? The DB2 CBO cannot perform selective I/O based on the container access properties. You can only define characteristics on a per-tablespace level. - Striping properties of the disk subsystem. Read and write operations are not equally expensive in all RAID configuration levels. For example, transfer of data TO a RAID-5 device is more costly than to other RAID levels. The CBO does not know which data has to be written to a RAID-5 device. You should configure other RAID levels for data writes from buffer pools. RAM - Is all the installed RAM available to the DB2 processes running on the database server? Since DB2 maintains its own buffering with the buffer pools, you should check whether a certain amount of RAM is used unnecessarily on the operating system level for buffering file system I/O,.
Dynamic behavior is the term used for system behavior based on properties that become apparent during high load on the RAM, I/O, and CPU resources of the database server. For example, if the system is paging heavily, the CBO may waste system resources by using a large buffer pool.
BC535 5-4
(C) SAP AG
For each SQL statement, the DB2 CBO tries to determine the most effective strategy for retrieving or manipulating database data. The access strategy used depends on many factors, such as: Queried table data content (or tables, for a VIEW or JOIN) Fields specified in the WHERE clause of the SQL statement Indexes defined for the tables queried Database statistics
The CBO computes the cost of data access strategies, and chooses the least costly, according to the cost-model defined. For this, the optimizer requires up-to-date statistical information about the tables and indexes of the database. Statistical information for a table or index is stored in the DB2 data dictionary. To collect the statistical information, the DB2 command runstats is executed in a special R/3 executable. Note: The runstats command is expensive, since it performs I/O. Schedule this job at times of low system load. Table and index sizes, and value distributions can change. If the current number of rows of a table differs greatly from the values determined by the last runstats run, the optimizer may choose an ineffective strategy and the database access time becomes longer. You should refresh the optimizer statistics at least once a week.
(C) SAP AG
BC535
5-5
The access path for a statement results into an execution plan. This execution plan can be displayed in a readable format. Once an execution plan is created, DB2 caches it to be able to reuse it and to save the create time for an execution plan (also called prepare time). DB2‟s CBO estimates the most efficient way to retrieve data by: Considering data volume and distribution, which are subject to continuos changes Finding the right balance between CPU-based and I/O-based operations Searching through the possible execution plans to find the least expensive one
DB2‟s CBO considers how much time it takes to get the plan. The Query Graph Model provides a formalism for transforming incoming SQL into a mathematically sound representation that can then be manipulated using well-defined optimization techniques while preserving the semantics of the original SQL.
(C) SAP AG
BC535
5-6
DB2 CBO: SQL Explain
SQL statement text
Execution plan
SAP AG 1999
The execution plan contains all the necessary data to be able to reproduce (follow) DB2‟s access path. If in doubt, you can start with the analysis of the execution plan to determine if the access path is adequate. You can run the R/3 command Explain using one of the following methods: Transaction ST05 Transaction ST04 Detailed analysis DB2 applications Transaction ST04 Detailed analysis DB2 SQL Cache Report RSDB6TTE (cumulative DBSL trace)
Standalone tools, like DB2 Visual Explain, are also available. However, it is not easy to retrieve the statements executed by the R/3 System using these tools. The statement in this example has a very costly execution plan. You can prevent the CBO from using table scans by using indexes and well qualified WHERE clauses. The above SQL Explain Function in R/3 is available as of R/3 Release 3.1H.
(C) SAP AG
BC535
5-7
Understanding DB2 Explain Output
Optimization level budgets query plan search
Timerons are a relative cost unit
SAP AG 1999
Read the Explain output from the upper right hand corner
The optimizer search in DB2 UDB is budgeted by the optimization level selected for a query plan determination. The DB2 CBO cost model tries to be accurate but relies on certain assumptions. With optimizer level 0, the optimizer does not exploit statistical information to calculate the access plan. Greedy join enumeration is performed with nested loop joins and index scans only. There is only minimal query rewrites and no consideration of non-uniform distribution in statistics. List prefetch and index ANDing are disabled. Optimizer level 5 is the default setting in the R/3 environment. This enables dynamic programming with optimization effort. Heuristics are used in concert with self-adjusted throttling techniques to selectively apply greedy join enumeration in order to manage the tradeoff between optimization time and plan execution time. The optimizer considers all available statistics, all query rewrite rules, and most access paths. Materialized views and hash join access methods are used with an optimizer level greater than or equal to 5. Timerons are “virtual timing units” that are used as a relative measure of time on the same system. Provided you have two systems that are exactly the same, with the same database configuration parameters, then you may be able to compare the execution cost of the statements between the systems. The DB2 tool db2look is the only way to model the same environment on another system. Estimated costs are accumulated from input (disk), through operations (sorts, joins), to output (fetch).
(C) SAP AG
BC535
5-8
Understanding DB2 Details Output
The Details output contains additional cost information for each operator
SAP AG 1999
For each operator used in the Explain output, DB2 provides additional information. To display this information, mark an operator and choose Details. To receive a detailed explanation of the inner working model of the CBO, choose Details in an ST05 Explain output. For each Explain output, the details are generated in different parts.
(C) SAP AG
BC535
5-9
DB2 CBO: Details (1)
-----------------------------------------------------------------------------------D E T A I L S -----------------------------------------------------------------------------------EXPLAIN INSTANCE -----------------------------------------------------------------------------------DB2_VERSION: | 06.01.0 SOURCE_NAME: | SQLL38N5 SOURCE_SCHEMA: | NULLID EXPALIN_TIME: | 1999-01-14 18:35:15.467001 EXPALIN_REQUESTER: | SAPR3 -----------------------------------------------------------------------------------Database Context -----------------------------------------------------------------------------------Parallelism: CPU speed [msec/instruciton]: Comm speed: Buffer pool size [4KB]: Sort heap size [4KB]: Database heap size [4KB]: Lock list size [4KB]: Maximum lock list: Average applications: Locks available: | | | | | | | | | | No parallelism 2.4679E-06 0.0000E+00 10.000 512 8.000 3.200 100 1 361.600
SAP AG 1999
This example shows the parameters displayed in the Details screen. EXPLAIN INSTANCE: DB2_VERSION displays the current database release. EXPLAIN_TIME shows the time when the Explain was performed. EXPLAIN_REQUESTER shows the user ID.
Database Context: Parallelism shows if parallel operations are allowed. Parallel operations are not necessary for standard R/3 Systems, but are good for SAP Business Warehouse Systems. CPU Speed is the speed of the system determined at installation time of the DB2 instance by executing SPEC-INT style routines. Buffer pool size is the size of RAM storage for data and index pages. Lock list size is the size of RAM storage holding lock information. The size of a lock on an object is 64 bytes for the initial lock and 32 bytes for each additional lock on the same object.
(C) SAP AG
BC535
5-10
DB2 CBO: Details (2)
------------------------------------------------------------------------------------
Package Context
-----------------------------------------------------------------------------------SQL type: | Dynamic SQL Optimization level: | 5 Blocking: | Block all cursors Isolation level: | Uncommitted read ------------------------------------------------------------------------------------
STATEMENT SECTION
1 397
| | | | | | 264 Select No No 1
-----------------------------------------------------------------------------------QUERYNO: QUERYTAG: Statement type: Updatable: Deletable: Query degree:
SAP AG 1999
This example shows other parameters that might be displayed in the Details screen. The Package Context displays the optimization level of the package, the blocking type of the cursors, and the isolation level. If a cursor is blocked, more than one row is transferred to the client side communication area to support efficient communication. For each blocked cursor, a memory area is allocated (RQRIOBLKSZ). Since R/3 uses dynamic SQL, each DB2 package has only one section that contains the properties of the statement executed, which are: Statement type Updatable Deletable Query degree
The Query degree describes the parallelism that has been selected for the statement. For example, if a degree of N is specified, DB2 tries to distribute the execution of the statement onto N agents, all trying to work in parallel. However, with a typical R/3 System workload, this intra-query parallelism is not needed.
(C) SAP AG
BC535
5-11
DB2 CBO: Details (3)
------------------------------------------------------------------------------------
Original Statement:
-----------------------------------------------------------------------------------SELECT TABNAME, TABFORM, REFNAME FROM DDNTT WHERE NOT ((TABFORM = „J‟) AND (TABTYPE = „J‟)) ORDER BY TABNAME ------------------------------------------------------------------------------------
Optimized Statement:
-----------------------------------------------------------------------------------SELECT Q1:TABNAME AS “TABNAME”; Q1.TABFORM AS “TABFORM”, Q1.REFNAME AS “REFNAME” FROM SAPR3.DDNTT AS Q1 WHERE ((Q1.TABFORM <> „J‟) OR (Q1.TABTYPE <> „J‟)) ORDER BY Q1.TABNAME
SAP AG 1999
Another part of the Details screen displays the original SQL statement and the result of the query rewrite process, which is performed by the CBO. Query Rewrite applies the common SQL optimization techniques that would be observed by very experienced SQL programmers, and resolves any referenced views. In this example, the De-Morgan theorem is applied: A AND B = NOT(NOT(A) OR NOT (B))
(C) SAP AG
BC535
5-12
DB2 CBO: Details (4)
--------------------------------------------------------------------------------Access Plan: --------------------------------------------------------------------------------Total cost: | 7.3872E+03 [timerons] Query degree: | 1 ... --------------------------------------------------------------------------------4 ) TBSCAN -------------------------------------------------------------------------------... -------------------------------------------------------------------------------Arguments: -------------------------------------------------------------------------------TABLOCK ROWLOCK PREFETCH SCANDIR MAXPAGES BUFFERS | | | | | | INTENT NONE NONE SEQUENTIAL FORWARD ALL 1720
SAP AG 1999
An important indicator for the performance of a query is the data access method used. In this example, an INTENT NONE table lock ensures that the table is not dropped by other programs during the execution of the statement, and there are no row-level locks involved. The I/O servers are enabled to efficiently read FORWARD on the containers to retrieve an estimated total of 1720 4 KB pages. Note: The complete output of the analysis is located in the exercises for this section. As a general rule, the cost of operations is “pushed” upwards and accumulated in the Explain tree.
(C) SAP AG
BC535
5-13
SQL Enhancements Affecting Query Execution
SQL statement text
SELECT **FROM MARA SELECT FROM MARA WHERE mandt = „001‟ WHERE mandt = „001‟ AND matnr = „1000‟ AND matnr = „1000‟ AND bismt = „0001‟ AND bismt = „0001‟
Access path
TBSCAN TBSCAN
+
OPTIMIZE FOR OPTIMIZE FOR ROWS ROWS
Execution plan
Select
+
FETCH FIRST FETCH FIRST ROWS ONLY ROWS ONLY
Fetch TBScan
SET CURRENT QUERY SET CURRENT QUERY OPTIMIZATION OPTIMIZATION
SAP AG 1999
For more information, refer to SAP Note 150037
OPTIMIZE FOR N ROWS (OFNR) Optimizer choose the quickest way to return a result with n rows Does NOT limit the result to n rows, but returns all records which match the query Changes in the access plan can be: Nested Loop Joins instead of mass join techniques or get sorted records by using an appropriate index
FETCH FIRST N ROWS ONLY (FFNR) Limits the result to the first n returned rows does not affect CBO decisions Is not available as a single ABAP hint because it does not affect the decision of the CBO
SET CURRENT QUERY OPTIMIZATION Changes DB2„s search strategy to find the result set.
As of R/3 Release 4.6A, you can pass hints to the optimizer from ABAP to DB2 UDB using:
SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100„ %_HINTS DB6 'OPT_FOR_ROWS 15„. SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100„ %_HINTS DB6 'USE_OPTLEVEL 0'.
Or both
SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100„ %_HINTS DB6 'USE_OPTLEVEL 0„ DB6 'OPT_FOR_ROWS 15„.
(C) SAP AG
BC535
5-14
SELECT SINGLE Execution Plans
SELECT SINGLE * FROM ...
Tran
e sferr
d
SELECT * FROM ... FETCH FIRST ... OPTIMIZE FOR ...
SAP AG 1999
SAP‟s open SQL statement SELECT SINGLE * FROM ... is internally transfered to a DB2 SQL statement using DB2‟s clauses: FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS.
(C) SAP AG
BC535
5-15
UP TO ROWS Execution Plans
... UP TO ROWS ...
d ferre rans T
SELECT * FROM ... FETCH FIRST ... OPTIMIZE FOR ...
SAP AG 1999
SAP‟s open SQL statement ... UP TO ROWS ... is internally transferred to a DB2 SQL statement using DB2‟s clauses: FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS
(C) SAP AG
BC535
5-16
DB2 Operators
DELETE FETCH FILTER GENROW GRPBY HSJOIN INSERT IXAND IXSCAN MSJOIN NLJOIN RETURN RIDSCN SORT TBSCAN TEMP TQ UNION UNIQUE UPDATE
Delete Fetch Filter rows Generate row Group by Hash join Insert Index ANDing Index scan Merge scan join Nested loop join Result Row identifier (RID) scan Sort Table scan Temporary table construction Table queue Union Duplicate elimination Update
SAP AG 1999
For more information about the operators of a plan, see Appendix D in IBM DB2 Universal Database, Administration Guide Performance Version 6.
(C) SAP AG
BC535
5-17
DB2 JOIN Concepts: MERGE
MERGE SCAN JOIN
Still sorted T1, Column 2 A A B C A A B B
T1.C2 = T2.C1
T2, Column 1 A B B
1. Read T1, C2 and scan T2, C1 until same value is found. Then join the rows. 2. Read T2 until the value changes, always joining the rows with the row in T1. 3. Then read next value in T1 and start at the row in T2 which was visited last. 4. If the value matches, join the rows. Otherwise search until T2, C1 matches T1, C2. 5. Continue this process until the end of T1.
SORT 1 2 3 1
SORT 2
Table 1
SAP AG 1999
Table 2
Merge join (also known as merge scan join or sort merge join) requires that the columns which are to be merged are sorted. This input may come from data which is received through an index, or which is sorted through other means (temp table). Merge join requires an equality join predicate, such as the predicate “=”. The merge join provides good performance if T2 (above) is large and sorted. This is the case if the rows of T2 are read from an index. Then, the T2 data does not have to fully fit into the buffer pool at once. It can be read through prefetching and does not have to be re-used in the same operation.
(C) SAP AG
BC535
5-18
DB2 JOIN Concepts: NESTED LOOP
NESTED LOOP JOIN
expr(T1.C2) < T2.C1
T1, Column 2 B A B C B A A B
T2, Column 1 A B A D Read T1.C2 until end of table T1 Read T2.C1 until end of table T2 if expr(T1.C2) reloop T2.C1 is true join the rows endif end read T2 end read T1
1
2
3
1
2
Table 1
SAP AG 1999
Table 2
The nested loop join can be used by the CBO when a relational operator (=, <, >, <=, >=) is used. It can also be used with expressions based on columns in the left (outer) table. The nested loop join does not need sorted data as input and will not provide a sorted result set. Therefore, it is very efficient. But for large tables, T2 (inner) table must be in the buffer pool to allow efficient operation, or as with DB2 the inner table must be sorted before being used.
(C) SAP AG
BC535
5-19
DB2 JOIN Concepts: HASH
Hash Join
T1.C2 = T2.C1
T1, Column 2 A C B C A C C B B C C
T2, Column 1 C B A D C B B C A D
1. Read T2.C1, create entries for all rows into partitions based on the hash code generated from C1. 2. Read T1.C2 and calculate its hash code. 3. Use this code to access the partitions. 4. Within the partitions the columns are compared. If equal, the actual rows are joined. 5. Continue at (2) until the end of T1 is reached.
1
2
3
1
2
Table 1
SAP AG 1999
Table 2
Hash join is a highly efficient join method. It can only be applied with the equality predicate “=” and if the column data types are the same. The Hash Join method uses memory from the sort heap. If the space is not sufficient, it will create temporary tables to hold further hash partitions. These temporary tables reside in the buffer pool, and might spill over into the PSAPTEMP tablespace if they become too large. This is the reason why Hash Joins are not good for very large tables. The CBO will detect this and will use another join method.
(C) SAP AG
BC535
5-20
The following major database configuration settings are observed by the DB2 CBO: BUFFPAGE (determines the size of the buffer pool) SHEAPTHRES (maximum soft limit for all sortheaps within a database instance) LOCKLIST (determines the size of the lock list) CPUSPEED (determines the speed of the CPU during installation) DFT_DEGREE (determines the default degree for query operations) AVG_APPLS (determines the share of the buffer pool per application)
For each table and index accessed by the execution plan, the CBO uses statistics collected in the system catalog. For each of the disks used for the containers of accessed tables and indexes, the CBO uses data defined for the tablespaces.
(C) SAP AG
BC535
5-21
R/3 Tools for Updating Statistics: dmdb6srp
Index_1_Table_1
Table_1
Index_2_Table_1
R/3 dmdb6srp
(REORGCHK+RUNSTATS) (REORGCHK+RUNSTATS)
Table_2
Index_1_Table_2
SAP AG 1999
DB2 RUNSTATS updates the DB2 catalog with statistics information. During RUNSTATS, information about cardinality, number of pages, and record length is collected. If desired, various properties of the indexes are collected as well. The DB2 command REORGCHK is discussed in a later unit. The R/3 implementation of RUNSTATS is called dmdb6srp, and contains an extended set of analysis tools which build on the DB2 RUNSTATS API. dmdb6srp updates R/3 tables DB6IREORG and DB6TREORG, and the DB2 catalog. dmdb6srp uses optimization techniques common to R/3 Systems (DBSTATC), as discussed in a later unit. The DB2 native RUNSTATS does not correctly display the size of tables with long varchar fields. Since R/3 has many tables with columns with this type of field, dmdb6srp calculates the size of these fields as well. For very large tables, you can specify an execution-time limit. After this limit has been reached, the program stops the calculation of the size of the table and estimates the size based on the cardinality and other available data. You can also specify the total execution time of dmdb6srp, after which the program will terminate. It will "know" the tables which have been visited and will start "oldest statistics" first at next invocation. Certain tables must not receive new statistics (for example, VB tables) and are not updated during a RUNSTATS called by dmdb6srp. However, the DB2 native RUNSTATS destroys the statistical configuration of these tables. For this reason, do not run DB2 RUNSTATS with R/3.
(C) SAP AG
BC535
5-22
Full database update statistics should be scheduled as need arises. The update of the statistics is performed by the R/3 DB2 CCMS, which uses the DB2 runstats API. Do not enter this command directly. Use the DBA Planning Calendar (transaction DB13) to schedule the statistics update of all tables. You may limit the runtime of the command. Use the CCMS CBO menu (transaction DB20) to run unscheduled updates immediately.
(C) SAP AG
BC535
5-23
(C) SAP AG
BC535
5-24
Using R/3 to update the statistics ensures that not only the DB2 catalog is updated, but also internal R/3 tables that maintain additional information on the database state. The executable dmdb6srp is the interface between the DB2 command runstats and DB2specific R/3 tables. The executable dmdb6srp stores output information from the DB2 commands runstats and reorgchk in R/3 tables. This data is displayed when transaction DB02 is called.
(C) SAP AG
BC535
5-25
Only up-to-date statistical information can ensure that DB2‟s Cost-Based Optimizer chooses the optimal access path. However, gathering optimizer statistics is expensive and reduces system performance. To ensure up-to-date statistics for all tables and optimal analysis runtime, SAP has implemented a two-phase strategy: In the first phase, SAP tools determine which tables require a statistical update. The data collector CCMS_DB6_CT_TUNE determines which database objects have exceeded an update threshold, and modifies the control table DBSTATC accordingly. In the second phase, the statistics of the tables marked TODO in the control table DBSTATC are refreshed. To do this, use the SAP CCMS DB-Administration and schedule the job: Update Statistics + Reorgcheck (DBSTATC) or run dmdb6srp at the command line.
In phase 1, the CCMS data collector CCMS_DB6_CT_TUNE runs on a daily basis and is triggered by the SAP Alert Monitor background activity jobs. To schedule CCMS_DB6_CT_TUNE on a daily basis, SAP‟s Alert Monitor needs to be activated. To do this, call transaction RZ21 and choose Technical Infrastructure Method execution Activate background dispatching.
(C) SAP AG
BC535
5-26
Refreshing Daily Object Statistics: Phase I
SAP CCMS Admin Workplace (Database)
Data collection method CCMS_DB6_CT_TUNE
g Trig ers Cal ls
Function module DB6_ALM_TUNE
da Up tes
Table DBSTATC
Table DB6GSDTBS
SAP AG 1999
To get to the SAP Alert Monitor, choose Administration CCMS Control/Monitoring Alert Monitoring (or call transaction RZ20). Expand the CCMS Monitor Sets and choose SAP CCMS Monitor Templates Database. Expand the following nodes to get to the update threshold leaf: Database Performance Optimizer. According to the properties of the MTE class of update threshold exceeded, you find the data collection method CCMS_DB6_CT_TUNE. This method is triggered on a regular basis to determine the tables for a daily runstats. The data collection method CCMS_DB6_CT_TUNE calls the function module DB6_ALM_TUNE. This function module finds tables that have received an update or write threshold and therefore the table statistics should be updated by the daily runstats job. The DB2 snapshot for tables is used as basis for the daily analysis. Table DB6GSDTBS holds the information about the update and write activity of the tables. Based on the difference between the current number of update or writes and the numbers stored in table DB6GSDTBS, function module DB6_ALM_TUNE is able to determine if the threshold is exceeded or not. The current number of update and write activities are added to the numbers already stored in table DB6GSDTBS to be the basis of the next run of DB6_ALM_TUNE. Table DBSTATC contains all the tables that should not receive statistics updates. In addition, the function module DB6_ALM_TUNE inserts table names and flags those entries for an update statistics. This is based on information in table DB6GSTBS.
(C) SAP AG
BC535
5-27
The second phase of the two-phase strategy analyzes those objects that need a refresh of statistical information according to DBSTATC. This is normally a very small subset of all the R/3 tables. This ensures a minimal runtime of the second phase. Only statistics of tables that are marked with new statistics needed in the control table (DBSTATC) are refreshed. The refresh of the DB2 statistics is done by The executable dmdb6srp using the information defined in control table (DBSTATC). After the refresh, the entries in control table (DBSTATC) that were marked as TODO are deleted by dmdb6srp. Control table DBSTATC does not contain only DB6 entries, it also contains entries for other database vendors. Note: With the R/3 default settings, there are are no statistics created for pool and cluster tables.
(C) SAP AG
BC535
5-28
Modifying the Algorithm to Update the Statistics
SAP AG 1999
To modify the standard procedure used by SAP for refreshing the optimizer statistics, you can use the CBO control panel (transaction DB21). Set the TODO flag so the next time the statistics are refreshed, new statistics for the newly defined entries will be created.
(C) SAP AG
BC535
5-29
Summary
Now you are able to: Describe the basic concepts of SQL optimization Use the SAP tools that access the DB2 CBO Execute the DB2 Explain command from transaction ST05 Analyze execution plans generated by the DB2 CBO Explain why the R/3 RUNSTATS replacement dmdb6srp must be used instead of DB2 tools
SAP AG 1999
(C) SAP AG
BC535
5-30
Unit Actions
?
Do the exercises
Solutions for the exercises
SAP AG 1999
(C) SAP AG
BC535
5-31
DB2 UDB Cost-Based Optimizer: Exercises
No. 1 2 3 Exercise Logon to R/3 and enable the tracing of your own transactions. Open another mode and call transaction ST04. Stop tracing your own transactions. Call DB2 Explain on an arbitrary SQL statement from within the SQL Trace you have listed. Explain the following single SQL statement using the SQL Trace: SELECT * FROM MARA What is DB2’s estimated cost for executing this statement? 4 5 6 7 You are only interested in the column MATNR of the statement. How would you change the SQL statement? Call DB2 Explain on the new statement. What is the difference between the old and the new execution plan? What is the name of the DB2 utility used to update the DB2 catalog with new statistics? Explain why the standard DB2 tool should not be used in an R/3 environment. What is the name of the SAP utility used to perform the same actions? Call transaction DB20 to update the statistics of table MONI, but only if it is necessary. List the tables that will be treated as “special” by SAP’s CBO tools (either because they should receive daily updates or because they are excluded from RUNSTATS). What is the ACTIVE flag setting of VBDATA?
8 9
(C) SAP AG
BC535
5-32
DB2 UDB Cost-Based Optimizer: Solutions
No. 1 Solution To enable the tracing of your own transactions, log on and call transaction ST05. To enable the SQL Trace, choose Trace On. Call transaction ST04 by entering /oST04. To stop the trace, choose Trace Off. 2 3 Select an SQL statement and choose Explain. It is not possible to explain all rows. Operations such as “CLOSE” are not explainable. Call transaction ST05 and select Explain one SQL Request. Enter the SQL statement and choose Save. In the execution graph, you will see the total calculated cost as a number in exponential format. 4 5 6 7 Change the statement to the following: SELECT MATNR FROM MARA WHERE MANDT=’001’ MATNR is part of the index MARA~0 (primary key). The access to the data is now index-only. RUNSTATS is used to update the DB2 catalog with new statistics. SAP’s dmdb6srp program is used to call the DB2 API for RUNSTATS and REORGCHECK. It should be used because: It is more accurate for calculating the size of tables with long fields. It will terminate after a specified runtime. It will make sure that statistics for tables, which might be very large, will not be reduced only because there is currently no data in the table. It will update information in the tables DB6TREOG and DB6IREOG. These tables are used for CCMS reorganization analysis. 8 To update the statistics, call transaction DB20 and enter MONI. Press Count Total number of rows to display the current cardinality. If there is a difference, choose Refresh statistics. To list the tables, call transaction DB21. Choose Position and enter VBDATA. The values should be N, or R, but never A. With values N or R, the table will not receive updates though SAP’s runstats.
9
(C) SAP AG
BC535
5-33
(C) SAP AG
BC535
6-1
DB2 UDB Tool Environment
Contents:
Tools for managing DB2 UDB databases
Objectives:
At the end of this unit you will be able to:
Use the DB2 Control Center Install SAP‟s DB2Admin Extensions Use the DB2 command line processor (CLP) Utilize the most common DB2 CLP commands Describe important DB2 tools
SAP AG 1999
(C) SAP AG
BC535
6-2
The administration infrastructure of R/3 Systems in the DB2 environment contains: the DB2 administration server, the SAP DB2 instance, further SAP and DB2 standalone executables, and the graphical DB2 Control Center. The DB2 Control Center - and its SAP extensions - provides a graphical interface for the administration of the DB2 databases and instances. The DB2 Command Center and DB2 Script Center provide easy-to-use interfaces to schedule and run tasks on your database. The DB2 Command Line Processor (DB2 CLP) provides a text interface for the administration of DB2 databases and instances. SAP tools and DB2 tools are also available as standalone utilities. These programs are either started by SAP Systems, or are started manually as the need arises. For example, the program used to check DB2 databases for integrity is called db2dart (DB2 database analysis and repair tool). Most of the administrative tasks on a database server are started by the DB2 administration server, also known as the DB2 administration instance. It allows the remote and local control of the DB2 instances installed on a server system. This means, without the administration server, DBAs would not be able to remotely start and stop the SAP DB2 instance.
(C) SAP AG
BC535
6-3
The administration database is available for all R/3 Releases on DB2 UDB. While using a second database sounds complicated, there are compelling reasons why it is used: • All client systems in the network can connect to the centrally kept administrative information, without the need to access remote file systems of the database server. • Authentication is available through DB2. • The administration database is in the same DB2 instance as the R/3 database. Data can easily be exchanged between both databases. • If the R/3 database is down for maintenance or if an R/3 database recovery is necessary, the administration database, which contains vital recovery data, is still available. • The administration database can easily be recovered, since a backup of the administration database is included on each log file archive tape.
(C) SAP AG
BC535
6-4
After installing the R/3 System, data is copied from the administration database to the SAP database by scheduling a copying process using the executable sddb6mir. You should schedule the following command on an hourly basis: sddb6mir –m
The administration database is backed up as soon as the log files are archived to tape. Each backup of the archived log file contains a current backup of the administration database. If required, you can restore the administration database using either of the following: Restore the admin db image from tape using the option brrestore -ex Restore the admin db from the R/3 database using the option sddb6mir -r Option 1 (brrestore -ex ) can always be performed. Option 2 (sddb6mir -r ) can only be used if the R/3 database is available, and only if the DBA has enabled the copy process. Since this process only runs on an hourly basis, the administration data in the SAP database is not always up-to-date and should therefore not be used if option 1 is available.
(C) SAP AG
BC535
6-5
Installing the Admin Database and Server Tools
The following objects must exist in the SAP DB2 Admin environment:
Admin database ADM Admin tables DB6... Stored procedure sddb6das on the server SAP tools on the server, such as BRARCHIVE, BRRESTORE, and db2uext2
The administration database and all the administration tools on the server are automatically installed during R/3 installation To manually install the SAP‟s R/3 DB2 extensions, use sddb6ins
SAP AG 1999
To install the SAP DB2admin objects on the server, run program sddb6ins -h. This program is usually run during SAP software installation. The sddb6ins program requires you to enter the SAP Release of the R/3 System to be administered. If you are going to administer multiple R/3 Releases, you must repeat this procedure for each R/3 Release. Program sddb6ins -h. is also used during an R/3 Release upgrade. During the upgrade, you must specify the start and the target release of the upgrade. It will remove old programs, install new executables, and adapt database structures to the new SAP Release. The administration database is also created during the installation of the extensions.
(C) SAP AG
BC535
6-6
For graphical administration of the DB2 databases, you must install the DB2 UDB administrative tools. These tools are contained in various DB2 software packages, such as the DB2 UDB Personal Edition Package. After installation, you may start the DB2 Control Center using the command db2cc. On NT, you can also start the program using Start Programs DB2 for Windows NT Control Center. There is always exactly one database administration server (DAS) installed per physical server system. The DAS allows the remote-control of tasks, such as: Remote configuration of DB2 databases and instances Planning and scheduling of jobs operating on DB2 databases Remote operations on DB2 databases and instances
The administration database is used to integrate SAP-related data into the DB2 Control Center using SAP‟s DB2 Admin Control Center Extensions. To retrieve configuration parameters for the DB2 administration server, use command db2 get admin cfg. To obtain the name of the DAS on your UNIX server, use command dasilist. To obtain the name of the DAS on your NT server, use the command db2admin.
(C) SAP AG
BC535
6-7
DB2 Version 6 offers a JAVA-based GUI for database administration. It requires JDBC support on the database server. Either directly, or through these programs, the GUI talks to the DB2 administration server with administrative requests. To start the JDBC daemon on UNIX, you can either start the daemons directly (issue db2jd) or use command db2jstrt, which returns after forking the daemons. To do this, enter: db2jstrt 6789 (6789 is the default port number, which is used if db2jstrt is started without a port number) - for the JDBC network driver db2jstrt 6790 - for the JDBC Control Center Driver Add the script rc.db2 to the inittab to start the JDBC daemons during reboot
On Windows NT, these services are integrated into the NT service manager with the DB2 JDBC Applet Server and the DB2 JDBC Applet Server - Control Center services. The DB2 administration server always listens to requests from the DB2 Java daemons using TCP/IP port 523. You can run the Control Center as a Java application or as a Java applet through a Java enabled web browser. In both cases, you need a supported Java Virtual Machine (JVM) installed on your workstation to run the Control Center. SAP‟s DB2 admin extensions are not available to run as Java applets. This is, because the SAP extensions are running using DB2‟s JDBC Application Driver out of performance reasons.
(C) SAP AG
BC535
6-8
Control Center: Installation
To install the administrative client software on a PC, use the software provided on the R/3 RDBMS CD Run the R/3 installation program db2setup on the PC To install SAP‟s R/3 extensions, unpack the package DB6CCEXT.CAR (DB6CCEXT.SAR), then run:
NT: Setup UNIX: Install
SAP AG 1999
The DB2 Control Center is part of DB2 UDB for all released platforms. Make sure that your Java Run Time Environment (JRE) is compatible with the DB2 CC. You can install the Control Center software on all operating systems : On Win32, DB2 installs a compatible JRE for you On Linux, ensure you have JRE 1.1.8.2 or higher installed (older JREs will cause some problems) On all other UNIX platforms, JRE 1.1.7 and higher is acceptable
To install the SAP extensions to the DB2 Control Center, switch to a temporary directory and use the command car to uncompress the file db6ccext.car. As of R/3 Release 4.6C, SAP compression/archiving is provided with SAP‟s new SAR tool. It's invocation is compatible with the old CAR tool. The reason for shipping CAR or SAR files is based on the 8.3 file name convention for SAP‟s CDs. Since files for the installation may have longer file names, they cannot be directly put onto the CDs, they have to be shipped in CAR or SAP archives.
(C) SAP AG
BC535
6-9
Control Center: SAP DB2 Admin Extensions
SAP AG 1999
The SAP DB2 Admin Extensions are: At the instance level - Password management: Choose this item to change the password of the user sapr3 or adm on a system-wide basis. - Option management: Choose this item to modify the configuration database and permanently save settings of the R/3 DB2 admin tool. Up to R/3 Release 4.0, some of these tool parameters were defined in the file /db2//dbs/init.sap. - All values entered using Option management are entered into the administration database. At the database level - Tape management: Choose this item to define tapes, device classes, devices, and locations. - Log file management: Choose this item to control log files, archive, restore, and user exit.
(C) SAP AG
BC535
6-10
Control Center: Handling
1 1 2 2 3 3
4 4
5 5
SAP AG 1999
Use the DB2 Control Center to manage systems, DB2 instances, and database objects. All DB2 databases must be cataloged before they appear in the DB2 Control Center. The DB2 Control Center consists of five main elements: Menu bar: Selecting a menu from the menu bar allows you to perform all functions. (1) Admin GUI tools in the Control Center toolbar. (2) Object tree pane: Displays icons for all the database servers and objects that you can administer from the Control Center. (3) Contents pane: This pane shows all the objects that are contained in the selected object in the object tree pane. (4) Contents pane toolbar: Allows you to tailor the information in the contents pane. (5)
(C) SAP AG
BC535
6-11
Control Center: Cataloging of Databases
1 1 System
2 2
Instance
3 3
Database
SAP AG 1999
To catalog a database, you can use the DB2 Control Center or the DB2 command line processor (DB2 CLP). With the DB2 Control Center: Add a system - provide system name as used for DB2 and the UNIX/NT host name. If the refresh option is used, the Control Center will broadcast into the network and wait for DB2 admin servers on the network to answer and advertise their data. Because of this time delay, the retrieve-option will be faster if the hostname is known. Add an instance - provide DB2 instance information of the database you want to connect to. This information can be provided by the DB2 admin server. Add a database - provide DB2 database information manually, or, as retrieved from the DB2 admin server.
With the DB2 CLP, the same steps can be issued as commands: Add a system: CATALOG ADMIN TCPIP NODE REMOTE REMOTE_INSTANCE SYSTEM OSTYPE NT Add an instance: CATALOG LOCAL NODE INSTANCE SYSTEM IBMCC50 OSTYPE NT Add a database:CATALOG DATABASE AS ON
(C) SAP AG
BC535
6-12
DB2 Command Line Processor
C:\SQLLIB\BIN>db2 (c) Copyright IBM Corporation 1993,1997 Command Line Processor for DB2 SDK 6.1.0 You can prompt. db2 db2 issue database manager commands and SQL statements from the command For example: => connect to sample => bind sample.bnd
For general help, type: ?. For command help, type: ? command, where command can be the first few keywords of a database manager command. For example: ? CATALOG DATABASE for help on the CATALOG DATABASE command ? CATALOG for help on all of the CATALOG commands. To exit db2 interactive mode, type QUIT at the command prompt. Outside interactive mode, all commands must be prefixed with 'db2'. To list the current command option settings, type LIST COMMAND OPTIONS. For more detailed help, refer to the Online Reference Manual. db2 =>
SAP AG 1999
The DB2 command line processor is used to access DB2 without the need to use graphical tools. Experienced administrators can use the DB2 CLP to interface their administration scripts to DB2. The command db2 starts the DB2 CLP. The command quit stops it. The resources associated with the DB2 CLP will not be released until you enter command terminate. You can also use the DB2 CLP to run SQL commands or statements interactively from a shell. For example, db2 “select * from sapr3.svers” can be issued from adm‟s c-shell. However, you should be aware that the shells will perform special operations on wild-card characters. Whenever a wild-card character is used, it has to be in quotation marks. On NT platforms, the command window running the DB2 CLP needs special environment settings. These settings are provided using command db2cmd.exe . Help on major DB2 commands is provided using the help parameter “?”. To display all DB2 commands, enter db2 “?”. To display help on specific commands, enter the command after the “?”. For example, enter db2 “? backup”.
(C) SAP AG
BC535
6-13
DB2 CLP: Important Commands
2 2 1 1
C:/> db2stop C:/> db2 (c) Copyright IBM Corporation 1993,1997 Command Line Processor for DB2 SDK 6.1.0 db2=> ? Backup database ..... db2=> reorg table sapr3.svers
2 2
C:/> db2stop
SAP AG 1999
CLP commands are used to control the system interactively. The commands below are grouped by function: Database manager control: get dbm cfg, update dbm cfg, get admin cfg Database control: create db, drop db, activate db, get db cfg, update db cfg Database directory management: catalog db, list db directory Node directory management: catalog tcp/ip node, list node directory
System commands require a complete DB2 software installation and one DB2 instance. These commands are not executed by the DB2 CLP: db2start... Start DB2 instance db2stop... Stop DB2 instance db2ilist... List instances on the system db2set... Access DB2 profile registry
(C) SAP AG
BC535
6-14
DB2 CLP: Architecture
C:\>db2 (c) Copyright IBM Corporation 1993,1997 Command Line Processor for DB2 SDK 6.1.0
DB2 database
DB2 CLP
DB2 backend process
DB2 connection Inter-process communication
DB2 agent
SAP AG 1999
The DB2 CLP uses a DB2 backend process as the interface DB2. On UNIX systems, this process is called DB2BP in the process overview list. The DB2 CLP does not always communicate with a DB2 agent directly. A DB2 backend process is always used to issue the commands to the database server on behalf of the DB2 CLP. Using this architecture, DB2 connections can also be maintained within UNIX shells or NT command windows. Connect to the database using the command db2 connect to user using . After this command returns, you can execute the shell commands. Also, you can retrieve data using the connection that has been established, such as db2 “select * from sapr3.svers”. With this command, data is retrieved by the DB2 backend process that has been established by the DB2 CLP with the command connect. Remember to terminate your DB2 CLP sessions. Only using the quit command will leave the DB2 backend process up and running. As a worst case, you will not able to shut down your database while these processes are connected. Then, you must force the database instance shutdown using command db2stop force.
(C) SAP AG
BC535
6-15
Data Security: DB Access Passwords
SAP application server
DB2 database server
db2 connect to user sapr3 using YYYY R/3 work process DB2 database
Read dscdb6.conf De-crypt password using DB2DB6EKEY Connect as sapr3 using password
Authenticate with user ID sapr3 at the OS level
dscdb6.conf Read file
/sapmnt//global
DB2DB6EKEY=XXXX
SAP AG 1999
An R/3 work process connects to the database by retrieving an encrypted password from file dscdb6.conf. The R/3 work processes, which are started by user adm, use this password to connect to the database as user sapr3. If the password at the operating system level is changed, file dscdb6.conf is out of sync with the information contained in the file, and R/3 will not start. To check for this error, enter command R3trans -d as user adm. To change the password of user sapr3 and user adm, perform the following: For UNIX: As user adm, use dscdb6up adm or dscdb6up sapr3 For NT: As user adm, use dscdb6up.exe adm or dscdb6up.exe sapr3 For BOTH: Use the R/3 DB2ADMIN Change password option for users sapr3 and adm. As of R/3 Release 4.6C, the executable dscdb6up is shipped with enhanced error messaging functions. You can also use the new implementation on older SAP Releases. To change the password inside the file dscdb6.conf only, use command db6util. In order to write a new dscdb6.conf file, you must rename the old dscdb6.conf file. db6util -c <adm>
(C) SAP AG
BC535
6-16
DB2 Tools to Extract Statistics: db2look
Use: • When predefined statistics have been destroyed (APQD, ...) • When CBO must be tricked into specific behavior
db2look
(extract DDL & stats)
CREATE TABLE "SAPR3 "."APQD" ( "QID" VARCHAR(20) NOT NULL WITH DEFAULT ' ', ...... ) Tables and Indexes: • Number of rows • Number of index levels • Number of distinctive values UPDATE SYSCAT.TABLES SET CARD=-1, NPAGES=-1, FPAGES=-1, OVERFLOW=-1 WHERE TABNAME = 'APQD' AND TABSCHEMA = 'SAPR3 ';
R/3 Database
SAP AG 1999
Program db2look is used to extract data definition language statements (DDLs) and statistics of a DB2 database. db2look is installed by the DB2 software installation routines, and can be used at the command line by user db2. Using db2look, you can extract DB2 DDLs and statistics for tablespaces, tables, and indexes without exporting the database content. Since the output - even for a single table - contains all necessary database DDLs, the file contains a lot of data. For proper execution, you must edit the file and remove unnecessary statements.
(C) SAP AG
BC535
6-17
DB2 Utilities: db2look
Program db2look
Is used in the process of moving tables and indexes to another tablespace Saves structures of tables and indexes on the file system level Creates SQL commands for generating statistics entries in the database catalog
SAP AG 1999
Program db2look generates DDL scripts for copying structures of tables and indexes. Execute the following command to get a script containing all the statements for creating all tables, indexes, primary keys, and views (Note: In the R/3 environment, this returns a lot of information): db2look –d -u SAPR3 –e –o
If you only want to view specific tables, us the–t option (you can specify up to 30 single tables): db2look –d -u SAPR3 –t ... –e –o
The tables that are copied with db2look are empty, the data must be copied separately using the db2 export utility from the source system and then using the db2 IMPORT/LOAD utility into the target system. Alternatively, data can be selected and inserted using SQL statements. You can use db2look to get the database statistics of a table using: db2look –d -u SAPR3 –t [-r] –m –o
To create a script to copy statistic entries to tables with the same structure (statistic patches), use option – r to deactivate the RUNSTATS command on the table specified. For detailed information about this program, see SAP Note 102200.
(C) SAP AG
BC535
6-18
DB2 Utilities: db2dart
Physical database consistency check
1 1 2 2 3 3
db2stop db2dart [Option] [Option] ... Check .RPT and DART.ERR
SAP AG 1999
The service tool db2dart is used perform consistency checks of the DB2 database or to research a database error situation, which is reported in directory db2diag.log. This tool must be started by user db2. When you call db2dart, an overview of all options is displayed. If you run db2dart the important messages are written to two log files: .PRT, which contains the result of the examination DART.ERR, which contains the return codes
If db2dart reports no errors, you will find a success message at the end of .PRT The important db2dart options are: /DB Inspects entire database (default option) /T Checks a single table /TS Inspects a single tablespace and the tables within
For further information about the db2dart service tool, see SAP Note 98524.
(C) SAP AG
BC535
6-19
Unit Summary
Now you are able to: Administer your DB2 UDB databases using the DB2 Control Center and the DB2 command line processor Install the DB2Admin Extensions Catalog remote database instances and databases Use the db2look and db2dart tools
SAP AG 1999
(C) SAP AG
BC535
6-20
Unit Actions
?
Do the exercises
Solutions for the exercises
SAP AG 1999
(C) SAP AG
BC535
6-21
DB2 Tool Environment: Exercises
No. 1 2 3 4 5 Exercise Start the DB2 Control Center and select the DB2Admin Extensions. How many Database Administration Servers (DAS) are installed on the server? How many JDBC daemons are running? Check if the installed Java version is at least 1.1.8 Which minor version of the Java runtime environment is installed? Which command must be scheduled to mirror the administration database into the SAP database? Which command can be used to rebuild the administration database out of the SAP database? Check if a mirror job was scheduled. 6 7a Determine the values of the following database manager parameters: NUMDB and INTRA_PARALLEL. Change the following database parameters in your test database: DBHEAP = 4000 BUFFPAGE = 2500 7b Change the following database manager parameter in your test database using the DB2 Control Center: NUMDB = 10 8 9 10 11 Assume you received the database error SQL01072N, how could you get more information on this? Which DB2 profile registry values are set on an instance level? How do you update the database statistics for a table in an R/3 environment? Update the R/3 table SVERS. Create a file that contains all database statistics for R/3 table MONI.
(C) SAP AG
BC535
6-22
DB2 Tool Environment: Solutions
No. 1 Solution To start the DB2 Control Center, use command db2cc. To get to the R/3 Option Management and R/3 Password Management, right click on the db2 instance icon. To get to the R/3 Tape Management and R/3 Log File Management, right click on the database icon. 2 To display the amount of servers, use the following commands: dasilist (on UNIX) db2admin (on NT) 3 To check the daemons, use command ps –ef | grep db2jd. Note: You can start the JDBC daemons either by using command db2jd or by using command db2jstrt. 4 To check the installed Java version, use command jre –de. To check the minor version, use command rpm –q –a |grep –i java. 5 To mirror the administration database into the SAP database, use command sddb6mir –m . To rebuild the database after a crash, use command sddb6mir –r ADM . To check if a mirror job was scheduled, log on to R/3 and call transaction SM37, use a job name MIRROR_ADMIN_DB. 6 To determine the values of the database manager parameters, you can either Use command db2 get dbm cfg, or Log on to the R/3 System, call transaction DB03 and choose Active Parameters 7a To change the database parameters, use the DB2 command line processor, and issue command db2 “update db cfg for using dbheap 4000 buffpage 2500”. To change the database manager parameter using the DB2 Control Center, enter db2cc. Select the database instance icon db2. Right click on the icon and choose Configure, select tab Environment. Search for the parameter ‘Maximum number of concurrently active databases’ or the DB2 parameter NUMDB. Enter the value 10, and choose OK to save your settings. To get more information, use the DB2 command line processor, and enter command db2 “? SQL01072N”. To check the profile registry values, use command db2set –i. To update the database statistics for table SVERS, log on as user adm su - adm, and issue command dmdb6srp –n -t
BC535 6-23
7b
8 9 10
(C) SAP AG
sapr3.svers. 11 To create this file, log on as adm: su - adm and issue command db2look –d -u SAPR3 –t MONI –m –o .
(C) SAP AG
BC535
6-24
(C) SAP AG
BC535
7-1
Backup and Log File Management
Contents:
Backup strategies for databases and log files Different aspects of data safety DB2 backup architecture R/3 and DB2 backup tools Online and offline backups
Objectives:
At the end of this unit, you will be able to:
Define your backup requirements Understand the importance of file system management and
database redundancy
Know which tools to use and which objects require backing up Describe online and offline database backups as well as offline log
file archiving
SAP AG 1999
(C) SAP AG
BC535
7-2
Defining Your Backup Requirements
Expected data volume
Consequences of data loss
Maximum downtime service level agreements
Size of backup window
Protection against hardware failure
Integration with enterprise backup policies
Organization, test and escalation plans
Protection against logical errors
SAP AG 1999
External factors, physical errors, and logical errors can cause system downtime and may lead to data loss if you do not have a suitable backup strategy. Your backup strategy must be designed according to the needs of your company. A self-assessment regarding the criteria mentioned above should be performed prior to implementation considerations. To ensure the availability of your R/3 System, the backup strategy developed by your database administrator must be carefully tested before your R/3 System goes live, and validated after any changes to your backup strategy or infrastructure. As a basis for successful problem handling, you should create documents containing organizational descriptions of procedures and an escalation plan. These documents must be available and understood by the persons in charge of database restore and roll forward. Since most enterprises have developed backup and restore policies, the SAP and DB2 UDB backup strategy has to comply with both the existing infrastructure and regulations.
(C) SAP AG
BC535
7-3
Aspects of Data Safety: Separating File Systems
DB2 container
Online log files
Operating system swap/page files
/db2//sapdata1 ...
/db2//log_dir
/db2//sapdata
/db2//log_archive
Offline log files
SAP AG 1999
A well-designed disk layout ensures minimal impact of data loss due to hardware errors as well as well-performing system operation. The following rules should be obeyed for reasons of performance (P) and safety (S). Separate the operating system directories (especially the paging or swap files/devices) from all database directories. (P) Since the database logging requires intensive and synchronous I/O whenever the database log buffer is flushed, it is recommended to have the online log directory on isolated disks. (P) If the online log directory is lost, a restore and roll forward will occur. In this case, it is crucial that as many offline log files as possible are still present to prevent unnecessary data loss. This goal can be achieved by separating online and offline log files onto different physical devices. (S) If database containers are lost, a restore and (in most cases) a subsequent roll forward are necessary. In order to be able to reapply all transactions, both online and offline log directories should be separated from the container devices. (S) Further aspects of a well-performing disk layout is discussed in the Storage Management unit.
(C) SAP AG
BC535
7-4
Aspects of Data Safety: Redundancy
Reducing the risk of a restore or data loss by mirroring
Database Containers sapdata1, ..., n Online log files log_dir Configuration files instance directory Offline log files log_archive Should be mirrored using hardware or operating system to reduce the risk of a restore Must be mirrored by hardware or operating system Must be mirrored by hardware or operating system Should be mirrored by hardware or operating system to reduce the risk of data loss
SAP AG 1999
As part of your backup strategy, you must consider the consequences of losing specific database components and you must take appropriate measures to reduce these risks: If database containers are lost, the database (or at least the tablespaces affected) have to be restored. To minimize the risk of losing a container, you should mirror the directories containing the DB2 containers (For UNIX: /db2//sapdata, for NT: \db2\\sapdata). If an online (active) log file is lost, severe database problems will occur, and you will eventually have to restore the database. Since all the transactions cannot be reapplied after such a restore, data loss is unavoidable. Therefore, you must provide redundancy for the online log directory. (UNIX: /db2//log_dir, NT: \db2\\log_dir). The same problems occur if database configuration files are lost. Again, redundancy of the instance directory/file system is advised. (UNIX: /db2//db2, NT: \db2). If an offline log file is lost, the ongoing database operation is not directly affected. However, in case of a restore, the database cannot be rolled forward to its latest consistent state. (And an immediate database backup is mandatory if a loss of an offline log file is detected). To prevent this data loss, you should mirror the offline log files.
Since there are no reliable mirroring techniques provided by DB2, mirroring should be performed either by operating system means (such as by logical volume manager capabilities) or hardware components (such as RAID1 or RAID5).
(C) SAP AG
BC535
7-5
Data Backup Objects
Database backup DB2 configuration files DB2 BACKUP DB2 container(s)
Offline log file(s)
BRARCHIVE
Online log file(s)
R/3 data
... R/3 archiving ... Interfaces ... SAP executables
SAP AG 1999
Computing center data
???
Operating system information
To correctly implement your database backup strategy, you must know which objects are backed up and which tools are used: If you use the DB2 backup utility, the databases are backed up under control of the DB2 instance. DB2 backup processes create backup images that contain both the database‟s data and the contents of the database configuration files. Please note that this backup image does not consist of a copy of container and configuration files, but rather their contents. To back up the offline log files from the log_archive directory/file system, we use a non-DB2 tool called BRARCHIVE. BRARCHIVE has been implemented jointly by IBM and SAP, and reflects the SAP-specific environment. As you can see, online log files are not backed up (by DB2 BACKUP or BRARCHIVE). Filled log files are immediately copied by the DB2 logging user exit to the log_archive directory/file system and can be backed up at this point in time. (Note that this concept relies very much on mirrored devices for the online log directory.) R/3 data is not backed up with DB2 tools or BRARCHIVE. You have to use operating system tools to do this.
(C) SAP AG
BC535
7-6
It is important to understand that DB2 database backups are performed under the control of the DB2 instance. Because of this architecture, the DB2 backup utility will only read the contents of the objects that need to be backed up (rather than backing up files on the operating system level). Both the contents of the containers for the specified tablespaces and the configuration information (such as the container location, sizes, and database configuration values) are read by the utility and written into a DB2-specific backup image. The backup image may reside on disk devices, tape drives, or may use advanced storage solutions like Tivoli Storage Manager or Legato Networker. Each backup image created is identified by a unique timestamp. A corresponding entry is logged into DB2‟s backup and recovery history file.
(C) SAP AG
BC535
7-7
DB2 Recovery History File
Recovery history file:
Database or tablespace backup Database or tablespace restore Database or tablespace roll forward Tablespace alteration Tablespace quiesce Table load Table reorganization Table statistics update
System System Database, DB server Database, DB server Day, Time Day, Time Operation type: Operation type: First log file: First log file: Last log file: Last log file: No. of Tablespaces: No. of Tablespaces: Tablespaces: Tablespaces:
Location: Location:
BUD BUD DB6 is0005 DB6 is0005 21.02.2000 20:30:08 21.02.2000 20:30:08 ONLINE ONLINE S0000903.LOG S0000903.LOG S0000906.LOG S0000906.LOG 21 21 SYSCATSPACE SYSCATSPACE PSAPBTABD PSAPBTABD ... ... PSAPUSER1I PSAPUSER1I /db2/BUD/sqllib/lib\ /db2/BUD/sqllib/lib\ /libxbsa.a /libxbsa.a
DB2 Backup Image
Backup information
Recovery history
Data
• • •
Log file information
SAP AG 1999
The summarized backup information in the recovery history file can be used to recover all or part of the database to a given point in time. The information in the file includes: The part of the database that was backed up and how The time the backup was made The location of the backup data (the device information and the logical way to access the data) The last time a restore was performed
Every backup operation (tablespace and full database) includes a copy of the recovery history file. The recovery history file is linked to the database. When a database is dropped, the recovery history file is deleted. Restoring a database to a new location restores the recovery history file. When a database is restored, the existing history recovery file is not overwritten. If the current database is unusable or not available and the associated recovery history file is damaged or deleted, an option on the restore command allows only the recovery history file to be restored. The recovery history file can then be reviewed to provide information about which (earlier) backup to use to restore the database.
(C) SAP AG
BC535
7-8
DB2 Backup Types: Offline Backup
SAP GUI .... .... ______________________ ______________________ ______________________ ______________________ ....
R/3 instance
SPO WP BTC WP BTC WP DIA WP DIA WP Dia WP
Offline backup
Performed by DB2 instance
DB2 instance
-- Database is not available + Backup image is consistent + (No logs are required for recovery)
SAP AG 1999
The first type of DB2 backups is the offline backup. To be able to perform this backup, no application can be connected to the database. The DB2 backup utility creates the backup image and does not allow any new connections during the course of the backup. Offline backups are always consistent because the backup images are read from a consistent database state with no ongoing transactions. To preserve the buffer qualities of R/3 application server buffers, the R/3 System may remain running during the time of the backup. However, all work processes are disconnected from the database and remain in a reconnecting state. That is, they are not operational to end users during the time of the backup. After the backup is complete and the database is available again, these processes reconnect to the database if they are triggered by user or system action.
(C) SAP AG
BC535
7-9
DB2 Backup Types: Online Backup
SAP GUI .... .... ______________________ ______________________ ______________________ ______________________ ....
R/3 instance
SPO WP BTC WP BTC WP DIA WP DIA WP Dia WP
Online backup
Performed by DB2 instance
DB2 instance
+ Database is available + -- Backup image is inconsistent (Logs are required for recovery)
SAP AG 1999
The second type of DB2 backups is the online backup, which is performed by the DB2 backup utility in parallel to ongoing activities of database applications, such as R/3 work processes. Since transactions are executed during the runtime of an online backup, the backup image created cannot reflect a consistent state of the database. In case of a restore of an online backup, all changes that occurred during the time of the online backup have to be reapplied or rolled back (depending on the commit or rollback of the corresponding transaction). Therefore, it is absolutely necessary to be able to provide all the log files that were written during an online backup in order to obtain a consistent state using a roll forward operation. Since R/3 operation continues during online backups, system availability is increased while high application server buffer qualities are maintained. Note: DB2 can only perform online backups if your database is operating in LOG RETENTION mode.
(C) SAP AG
BC535
7-10
Backup Strategies: Tablespace Level Backups
...
DB2 tablespaces DB2 configuration files
PSAPBTABD PSAPUSER1D ... PSAPUSER1I PSAPSTABI
DB2 backup utility
DB2 backup on tablespace level: + + + +
Reduces backup time A complete logical database backup can be performed incrementally Recovery window may be an issue Restore activities become more complex
Backup image
---
SAP AG 1999
The DB2 backup utility supports backups on a tablespace level. This feature can be used, for example, after tablespace extensions. Problems might arise if you perform partial backups of a database by backing up the first group of tablespaces during one night and the second group of remaining tablespaces during the second night since you may not be able to restore the complete database within the maximum time frame. However, with the DB2 backup utility you can selectively restore a group of tablespaces from a full database backup image. This feature will be discussed in detail in the Restore and Recovery unit.
(C) SAP AG
BC535
7-11
R/3 and DB2 Database Backup Tools
Option 1: DB2 command line processor (CLP)
1a. On demand
C:\SQLLIB\BIN>db2 backup database TC1 online to \ d:\backups, e:\backups, f:\backups, g:\backups, h:\backups\ with 6 buffers parallelism 3 Backup successful. The timestamp for this backup image is: 200002191542209
1b. Scheduled regularly using scripts UNIX: /db2//batch/brdb6bucron.ksh
Stops R/3, performs DB2 offline backup, then restarts R/3
SAP AG 1999
There are three ways in which you can back up a DB2 UDB database. The first option uses the command line interface DB2 CLP. There are two ways to use the command line processor: As shown in example 1a., you can directly issue a DB2 backup command out of a CLP session. If you are not too familiar with the syntax of the utility, you can obtain help by issuing command db2 “? backup” As shown in example 1b., you can use crontab (available on UNIX platforms only) to schedule offline database backups using the script /db2//batch/brdb6bucron.ksh: - First, the script will stop the R/3 System. - Then the DB2 backup utility will be invoked to perform an offline backup of your database. - Finally, the R/3 System is restarted. This utility is quite useful for scheduling offline backups, for example, during the weekend.
(C) SAP AG
BC535
7-12
R/3 and DB2 Database Backup Tools
Option 2: DB2 Control Center
SAP AG 1999
The DB2 UDB database can also be backed up using the graphical user interface, DB2 Control Center: If you use the DB2 Control Center, backups can be started immediately or scheduled using the script center.
(C) SAP AG
BC535
7-13
R/3 and DB2 Database Backup Tools
Option 3: R/3 DBA Planning Calendar (DB13)
SAP AG 1999
The R/3 System provides the third way to perform DB2 database backups, which is suitable for online backups: If you use the CCMS DBA Planning Calendar (transaction DB13), you can conveniently schedule periodically executed online database backups.
(C) SAP AG
BC535
7-14
ADM Backup Tools
R/3 database
Containers
Admin database ADM
Database configuration Online log files
Mirroring with sddb6mir -m
1. 1.
Implicit
Transfers table entries created after last execution of sddb6mir into R/3 tables
2. 2.
Explicit
Each log file archival by brarchive will backup the contents of ADM
SAP AG 1999
There are two ways you can back up the contents of the admin database ADM: Implicitly Explicitly
Implicit backup: As part of the post-processing of each installation you are required to schedule an R/3 job using transaction sm36 that runs the executable sddb6mir -m on an hourly basis on your database server. Each invocation will extract the previously unsaved data and insert those records of ADM into corresponding tables in the R/3 database . Invoking sddb6mir -r allows you to restore the ADM database from the R/3 database. Explicit backup: Whenever offline log files are archived, the contents of ADM are also backed up to the same target media.
(C) SAP AG
BC535
7-15
Backup Cycle: DB2 Database Backups
DB2 database backups of
Online Offline
... Daily full online backup Weekly full offline backup
Online
28 days
...
Online
...
Offline
SAP AG 1999
The standard database backup cycle for DB2 includes performing: An offline backup of the complete R/3 database once a week, and A full online backup of once a day.
(C) SAP AG
BC535
7-16
Backup Frequency Considerations
The more frequently backups are performed, the less time is needed in case of a database recovery Trade-off between backup costs and recovery downtime
Recovery Cost
200000 180000 160000
Too few backups can become very expensive Keep roll forward times in mind
Cost per recovery
140000 120000 100000 80000 60000 40000 20000 0 0 10 20 30 40 50 60 70 80 90
Number of backups per year SAP AG 1999
The calculations shown here clearly show the importance of backups. When you create your backup strategy, you must consider the following: Having consistent readable backups is the only way to ensure a database can be recovered. Ensure that you use high-quality disks and/or redundancy to minimize the need for recovery. Fast recovery is crucial. Test your system‟s throughput. Do not rely on estimates. For critical systems, consider advanced solutions, which will be discussed later.
Remember, database administrators are always asked about backup costs, but never about the cost of downtime.
(C) SAP AG
BC535
7-17
The db2agent process communicates with and controls the operation of the db2med and db2bm processes. The db2med processes are of type media controller. They transfer data on a FIFO basis from backup buffers that are full. The number of db2med processes depends on the number of backup devices or sessions. The db2bm processes are of type buffer manipulator. Their task is to move data from a tablespace to backup buffers (not buffer pools). The buffer manipulators are set in parameter PARALLELISM. Since only one buffer manipulator can work on a tablespace at a time, you should not set the value for PARALLELISM higher than the number of tablespaces. The db2bm processes use prefetchers to improve parallelism to access the tablespaces. If you use multiple tapes or PARALLELISM with value larger than 1, the number of buffers you should have must be large enough to keep the media controllers and their attached devices busy. The size of the backup buffer should be 1024, or preferably 2048 4 KB pages (4 MB, 8MB). Example: You have 2 DLT tape drives and 5 independent RAID1 mirrored disks for the containers. Provided that you have correct striping of the containers, you will keep all disks busy when backing up 5 tablespaces at the same time (PARALLELISM=5). To have a free buffer when the last one is full, use 10 buffers. Since you are performing online backups, the number of I/O servers should be at least 5, since the R/3 System will also do prefetching. 2 media controllers are used.
(C) SAP AG
BC535
7-18
Using DB2 Backup Parallelism
WRITE
WRITE
db2 backup db online to /dev/rmt0, /dev/rmt1 with 6 buffers parallelism 3
6 backup buffers
db2med db2agent
2x db2med
WRITE
db2bm
3x db2bm
db2pfchr 1.... m
SAP AG 1999
Disk 1
READ
Disk 2
Disk n
The degree of parallelism that is used for your database backups can be set using the DB2 CLP: In parameter PARALLELISM, specify how many buffer manipulator processes are started.
You can specify how many memory areas are used to store data before it is written to the target media in parameter BUFFERS. The number of sessions is determined by the number of target devices (which must equal the number of media control processes). When determining your optimal backup strategy, include a variation of all three parameters with subsequent measurements.
(C) SAP AG
BC535
7-19
DB2 Backup Performance Tips
Use more than one process to exploit parallelism Provide a sufficient number of backup buffers:
Number of backup buffers = (2 x the number of db2med) + a few extra
Provide enough I/O server processes
Number of I/O server >= the number of max. disks for a tablespace + 2
SAP AG 1999
The DB2 UDB database utilizes backup buffers efficiently, switching fast between buffers. For optimal performance, you should use more than one process to exploit parallelism. To do this, you must have at least one backup buffer available for each of the following: Each db2med process writing to a medium Each db2bm process receiving data from tablespaces
In live systems, backup buffers are transferred to media much more slowly than they are filled by the db2bm processes. You should therefore have less filled buffers waiting for db2med processes than there are db2bm processes. For optimal performance, the number of backup buffers should be twice the number of devices or sessions plus a few extra. During online backups, backup buffers compete for RAM with other programs and buffers (buffer pool). You must take this into account when defining your number of buffers. During online backups, the db2bm processes utilize DB2 I/O servers (prefetchers) for I/O. It is therefore good practice to configure a slightly higher number of NUM_IOSERVERS.
(C) SAP AG
BC535
7-20
Backup Device Configuration Tips
Fixed block size: 512 1024 2048 4096 bytes
Variable block size: 0 bytes
Not
r
me ecom
nded
Buffer size is limited
WRITE
Backup buffers
Any buffer size
DB2 backup image
Header 4 KB
SAP AG 1999
When you use fixed block sizes for devices, only 512, 1024, 2048, and 4096 bytes are supported as block sizes. This is because DB2 BACKUP writes a 4 KB header onto the tape device. Note: Backup buffer sizes are not restricted with fixed device block sizes. If the block size is set to 0, variable block size will be used. The use of variable block sizes is not recommended. For more information, see SAP Note 199665. However, if you must use variable block sizes to increase the performance of the backups, the following condition must be met: The buffer size specified in the backup command must be less than or equal to the maximum variable block size supported by the hardware/OS vendor.
If the backup buffer size and tape block size do not have the correct combination, there is a possibility that the backup will complete successfully but it may not be possible to perform a restore from that backup image. Note: If you are not backing up to tape, larger backup buffers are better. For example, you can use larger backup buffers when you perform backups to disk and hierarchical storage systems, such as Legato Networker or ADSM.
(C) SAP AG
BC535
7-21
Block Size Limits of Selected Backup Devices
Device Attachment 8mm scsi 3420 s370 3480 s370 3490 s370 3490E s370 3490 s390 3490E s390 7332(4mm)* scsi 3490e scsi 3590** scsi 3570 Magstar MP
Block size limit 131072 65536 65536 65536 65536 65536 65536 262144 262144 2097152 262144
DB2 buffer size limit (4KB pages) 32 16 16 16 16 16 16 64 64 512 64
(*) The 7332 device does not implement a block size limit. 256 KB is only a suggested value. Block size limit is imposed by the parent adapter. (**) While the 3590 device does support a 2 MB block size, you might experiment with lower values (such as 256 KB), provided performance is adequate.
SAP AG 1999
The best backup and recovery strategy is the one that has been tested. Before going live, you must test a restore of the production database to make sure that block and buffer sizes are not only correct for backups, but also for a restore. Consult with your hardware vendor about the block size limit of the tape device and, if applicable, the parent adapter. Note that maximum block sizes sometimes vary between tape devices of the same type. Therefore, restore tests are mandatory.
(C) SAP AG
BC535
7-22
Tivoli Storage Manger Administration
To manage database backups and archived offline log files in the Tivoli Storage Manager, use the following executables:
db2adutl queries, selects, extracts, and marks backups as inactive brachive archives offline log files brdb6qry searches for archived log files brdbsdsd deletes archived log files brrestore retrieves archived log files
SAP AG 1999
SAP delivers several executables to manage database backups and log files archived offline: db2adutl is used to administrate the Tivoli Storage Manager (formerly known as the ADSM ADSTAR Distributed Storage Manager), and is described in SAP Note 67789. - If used with option Query, backup images are selected in the ADSM. - If used with option Extract, backups from the ADSM are extracted to disk. - If used with option Delete, database backups in the ADSM are marked as inactive, and deletion will take place according to retention policy. brachive is used to store offline retained log files to the ADSM. brdb6qry is used to search for offline archived log files in the ADSM. brdbsdsd is used to delete offline archived log files from the ADSM. brrestore is used to restore offline archived log files from the ADSM.
Note: The ADSM client program on INTEL platforms is currently not reentrant. This means that you cannot run multiple sessions to back up a DB2 database server on Windows NT. This limitation will, however, be removed in the future.
(C) SAP AG
BC535
7-23
Advanced Backup Scenarios: Split Mirror Backup
Procedure 1. Split the third mirror 2. Backup the contents of the third mirror 3. Resynchronize mirrors 1, 2, and 3
Logical level Production DB Split
Physical level
Mirror 1 Sync
Mirror 2 Sync
Mirror 3
“Backup”
SAP AG 1999
Instead of backing up the production database as discussed up to now, you can consider using an advanced backup concept, such as using the features of the underlying disk subsystem. In this example, production data is stored redundantly on three different physical devices, all in control and normally synchronized by the disk subsystem. As a first step, the third mirror is split apart and its contents are transferred as if it was a backup. Because the database was in continual operation, these changes that were recorded on disk mirrors 1 and 2 would have to be reapplied to mirror 3 by the disk subsystem. The benefit of this technique is to minimize the impact the backup has in the production environment.
(C) SAP AG
BC535
7-24
In this second advanced backup scenario, a second database server is used, which has initially been set up by a restore of the production database. This second (standby) database is permanently in the state roll forward pending. That is, it cannot be accessed by applications by “waits” for the application of transaction log files. Whenever a log file is filled on the production system, a special version of the DB2 logging user exit will copy this file to its standard target device (such as directory log_archive). In addition, it will copy it to a common directory that the production and standby database servers share, using UNIX or NT shares. On the standby database server, the logging user exit will again be used to retrieve the log files copied by this procedure. Note that these log files are not applied immediately, but after a configured amount of time. This provides the chance to treat logical errors. That is, the corresponding log records of these logical errors must not be reapplied. Instead, the standby system is opened shortly before the logical error occurred and substitutes the former production server.
(C) SAP AG
BC535
7-25
Importance of Offline Log Files
The database cannot be rolled forward past this point in time Point in time of the database error
Sequence of offline log files:
Lost information
X
Lost offline log file Intact but unusable offline log files
Time
If one offline log file is lost, none of the following files can be used in case of a recovery
SAP AG 1999
Although the database operation is not immediately affected if an offline log file is lost, these files are crucial in case of a restore and roll forward recovery. If a database backup image is restored, the subsequent roll forward cannot be continued past the missing file. This reveals the fact that losing an offline log file is more harmful than losing a database backup. If a loss of an offline log file is detected, an immediate full database backup is absolutely mandatory.
(C) SAP AG
BC535
7-26
2-Step Log File Management
ADM Logfile Copy-RC Archive-RC log_dir
1 1
DB2 logging user exit
0911 0912 0913
0 0 0
0 0 -
log_archive
2 2
BRARCHIVE
Tape / Storage management system
SAP AG 1999
When an online log file is filled with log records, the database invokes the DB2 logging user exit, which copies the file to the log_archive directory and update logging tables in administration database ADM. BRARCHIVE is used to archive offline log files to a constant storage media, such as to tape or a storage management system (like ADSM or Legato). BRARCHIVE retrieves the range of log files that are to be archived out of the administration database ADM. If you use a storage management system as BRARCHIVE‟s target device, tape management is done automatically by the storage management system. If you use tapes as the direct target media, you must manage the tapes manually.
(C) SAP AG
BC535
7-27
Native BRARCHIVE Tape Management
1. Configure a device class 2. Configure a device 3. Initialize tape
SAP AG 1999
Tape management relies on a hierarchy that has to be defined by the administrator: For each kind of tape device in the system, a device class has to be defined. The tape device has a number of attributes specific to a certain class of tape devices, such as capacity and tape handling commands. For each physical device, a tape device has to be defined. The name of that device should be the device name, for example, /dev/rmt0. Each tape that will be used for log file archiving has to be defined. This means the tape must be given a name and initialized.
To initialize a tape, from the DB2 Control Center choose R/3 Tape Management Tapes. Select the device class to which the tape to be initialized belongs. Select Tapes. Press the right mouse button and select Initialize Tape. In the Initialize Tape dialog box, enter the name of the new tape. Select the device where the tape is located. To initialize the tape, choose Init. All tape actions and their results can be seen in the Journal page. To remove a tape, select the tape in the tree, press the right mouse button, and select Delete.
(C) SAP AG
BC535
7-28
Tools for Offline Log File Management
Option 1: Direct invocation of brarchive
1a) On demand
C:\SQLLIB\BIN>brarchive -sd -n 10 -d ADSM -confirm
1b) Scheduled regularly using scripts Use crontab or at mechanisms
SAP AG 1999
There are three ways in which you can archive offline log files using BRARCHIVE. The first option uses the command line interface of BRARCHIVE. There are two ways to do this: As shown in example 1a., you can directly issue the brarchive command from the command prompt. As shown in example 1b., you can use crontab (available on UNIX platforms only) to schedule offline log file backups using a script.
(C) SAP AG
BC535
7-29
Tools for Offline Log File Management
Option 2: DB2 Control Center
T01
Archive
SAP AG 1999
A second interactive method to trigger BRARCHIVE is provided by SAP‟s enhancements to the DB2 Control Center. You should archive log files when you want to be able to keep them over a longer period of time. You can either archive them to tape or to ADSM. To archive log files, from the DB2 Control Center choose R/3 Log File Management Archive. Specify the node where you want to archive the log files. Specify the log file up to which you want to archive. If you do not want the log files to be deleted from DB2DB6_ARCHIVE_PATH after archiving, select the don„t delete log files in file system check box. Specify where you are going to archive to, that is, select either ADSM (left radio box) or Tape (right radio box). If you select ADSM, choose a management class from the ADSM management class box. If you select Tape, specify the name of first tape and its device. If you want to use two tapes, follow the same procedure for the name of the second tape and its device. To start the archive process, choose Archive.
(C) SAP AG
BC535
7-30
Tools for Offline Log File Management
Option 3: R/3 DBA Planning Calendar (DB13)
SAP AG 1999
The R/3 System provides the third way to start BRARCHIVE. If you use the CCMS DBA Planning Calendar (transaction DB13), you can conveniently schedule periodically executed log file archiving.
(C) SAP AG
BC535
7-31
Tape Management Recommendations
Incremental file system backup of log_archive in addition to BRARCHIVE can be useful if 2-step log file management is in place Do not backup online log files Keep all the log files needed to restore the oldest database backup in your backup cycle Schedule checks for freespace in log_archive to trigger BRARCHIVE as soon as required
SAP AG 1999
A good way to archive offline log files is to schedule an incremental backup of the log_archive directory using a hierarchical storage management system. Also, call BRARCHIVE on a regular basis. With this method, you will also have two copies of each log file available. The active log files in directory log_dir are locked by the database when in use. Therefore, there is no necessity to back up online log files. If you do back up this directory, you will encounter database runtime problems. You must ensure that you have all the log files available that are needed to recover the oldest backup in your backup cycle. Ensure your retention period for log files is 28 days. If you use cron (UNIX), you are able to call a shell script that checks the freespace in log_archive on a regular basis. When the freespace is below a certain level, the shell script will call BRARCHIVE automatically.
(C) SAP AG
BC535
7-32
Backup Cycle
DB2 database backups and log file archival
Online Offline Archives (2x) Online Archives (2x)
28 days
Online Archives (2x)
SAP AG 1999
You must back up the offline log files each workday, as well as after every online and offline backup. Ensure that you back up the offline log files twice, on separate tapes, before they are deleted. Remove the last verified full offline backup of each cycle from the tape pool, and keep this backup in long-term storage. Replace the tapes, and initialize new ones. To verify a backup, check the database for logical errors and the database backups for physical errors. Perform backup verification at least once in the backup cycle. Perform additional backups after each database structure modification or a system upgrade. Place these additional backups in long-term storage.
(C) SAP AG
BC535
7-33
1-Step Log File Management
log_dir
1 1
DB2 logging user exit
Storage management system
SAP AG 1999
As an alternative to the previously discussed 2-step log file management, you can set up the 1-step log file management: By a different configuration of the DB2 logging user exit, filled log files are transferred immediately to storage management systems. As a consequence, BRARCHIVE is not needed in this setup.
The benefit of this approach is that log files are stored on safe, external media as soon as possible. In case of a roll forward recovery, these log files can directly be retrieved by the DB2 logging user exit. There is no operator intervention required as it would be necessary to retrieve log files from tape in the 2-step scenario. To avoid file system full conditions in the log_dir directory, you must make sure that your storage management solution is highly available.
(C) SAP AG
BC535
7-34
Data Backup Objects
Database backup DB2 configuration files DB2 BACKUP DB2 container(s)
Offline log file(s)
BRARCHIVE
Online log file(s)
R/3 data
... R/3 Archiving interface ... Interfaces ... file systems SAP Executables
SAP AG 1999
Computing center data
R/3 &
???
Operating system system information
Operating
Remember that your backup strategy must include backing up the operating system data as well as R/3 file systems (such as transport directories, archive file, executables, and scripts). The tools to backup these components are highly specific to the underlying operating system. Apart from all planning and scheduled jobs, it is important that every backup strategy consists of training, knowhow, documentation, and regularly performed restore and disaster recovery tests
(C) SAP AG
BC535
7-35
Unit Summary
Now you are able to: Define an efficient backup strategy that uses:
DB2‟s Control Center for database backups CLP for database backups CCMS Planning Calendar for online backups BRACHIVE for DB2 for log file archiving
Estimate the amount of tapes needed for your backup strategy Understand DB2‟s 1-step and 2-step log file management
SAP AG 1999
(C) SAP AG
BC535
7-36
Unit Actions
?
Do the exercises
Solutions for the exercises
SAP AG 1999
(C) SAP AG
BC535
7-37
Backup and Log File Management: Exercises
No. 1 Exercise For these exercises, make the following preparations: Enter your -directory cd /db2/T Create a backup directory mkdir backup Enter the backup-directory cd backup 2 3 4a 4b 5 6 Change the logging mode of your test database into LOG RETENTION mode. Perform an offline backup of your test database. Write down the timestamp of your backup for further use. Perform an offline tablespace backup of the PSAPEL46BD and PSAPBTABD tablespaces. Perform an offline backup of your test database using the DB2 Control Center. Retrieve information about all backups that have been performed on your R/3 database server up to now, using R/3 monitoring transaction. Retrieve the same information about the backups using the DB2 Control Center. (Hint: Use the DB2 Journal). 7 8 9 10 11 Retrieve the DB2 recovery history information of your test database for all backups using the DB2 CLP. Retrieve the DB2 recovery history information of your test database for all backups made since yesterday, using the DB2 CLP. Use the scenario that your DB2 recovery file has been destroyed. Restore your recovery file from the latest backup using DB2 CLP. Perform the same operation using the DB2 Control Center. (Hint: Use the DB2 Journal) Which transaction has to be used to show information about the success of the mirroring of the administration database adm?
(C) SAP AG
BC535
7-38
Backup and Log File Management: Solutions
No. 1 Solution Enter your -directory cd /db2/T. Create a backup directory mkdir backup Enter the backup-directory cd backup. 2 3 To change the logging mode of the test database, use command db2 "update db cfg for using logretain recovery". To perform an offline backup of the test database, use command db2 "backup database to /db2/T/backup with 4 buffers buffer 1024 parallelism 2" The timestamp for this backup image is 20000327181524. 4a To perform an offline tablespace backup, use command db2 "backup database tablespace PSAPEL46BD, PSAPBTABD to /db2/db2t0/T/backup with 4 buffers buffer 1024 parallelism 2". To start the DB2 Control Center, use command db2cc. Right click on the test database icon and choose Backup Database. In the tab Backup, specify the backup directory /db2/db2t0/T/backup. In the tab Options, select Offline. To start the backup, select Backup now. 5 6 To retrieve the information about all the backups, use the Overview Backup Status Monitor (transaction DB12). To retrieve the information about all the backups using the DB2 Control Center, you must mark View and choose Journal. Choose the History tab, and select your database. To retrieve the DB2 recovery history information, use command db2 list history backup all for . To retrieve the DB2 recovery history information since yesterday, use command db2 list history backup since 20000327 for . To restore the recovery file from the latest backup, use command db2 restore db history file online from To restore the recovery file from the latest backup using the DB2 Control Center, choose Tools Journal. Choose the History tab, and select your database. Right click on the latest backup containing the history file and choose Restore Recovery History. To review the success of the mirroring job of the administration database, use the job overview in transaction SM37. Note: This transaction must be scheduled on an hourly basis.
4b
7 8
9
10
11
(C) SAP AG
BC535
7-39
(C) SAP AG
BC535
8-1
Restore and Recovery
Contents:
Overview of recovery options with DB2 UDB Restoring a database and single tablespaces Performance considerations in restore situations
Objectives:
At the end of this unit you will be able to:
Restore a DB2 database from a backup image Roll forward the database using the offline log files Define recovery procedures
SAP AG 1999
(C) SAP AG
BC535
8-2
Roll Forward Vs. Restart Recovery
Manual intervention
Automatic, no intervention needed
Database restart DB DB
Roll forward recovery: restore + roll forward
Restart recovery: connect
SAP AG 1999
DB2 offers two types of recovery: A roll forward recovery must be performed when all or parts of the persistent storage (disks) have been lost or corrupted. The process is lengthy and requires manual intervention. A restart recovery is performed by DB2 whenever data in the non-persistent memory (RAM) has been lost due to a system hardware failure or a power loss. - In this case, restarting the DB2 database initiates a crash recovery. DB2 reruns all transactions that were performed previously but have not been recorded in persistent memory. The DB2 log file header file contains the log sequence number, which must be used as a starting point for this type of recovery. If you lose this file, you are unable to restart your database and must perform rollforward recovery. - To ensure that DB2 automatically starts the restart recovery, the DB2 database parameter AUTORESTART must be set to On.
(C) SAP AG
BC535
8-3
Restart Recovery: Background
Buffer pool
DB2 agent 1 1 DB2 agent DB2 I/O cleaner
2 2 DB2 logger
3 3
tely edia Imm
4 4
d erre De f
Online log files Log records
Log sequence number Log rec. data
Tablespaces
n+1
01001010010 XXXXXX 01001010011 XXXXXX
n+2
01001010012 XXXXXX 01001010013 XXXXXX
Data not stored on tablespaces
...
SAP AG 1999
...
DB2 agents perform database transaction triggered by R/3 work processes. (1) All database changes are immediately logged to the log files by the DB2 logger. This is called write ahead logging. (2) Pages containing committed transaction data are moved to persistent storage by DB2 I/O cleaners and DB2 agents. (3) Those processes are triggered by soft check points. Soft check points define the interval for a buffer pool flush. Soft check points are defined by: DB2 parameter SOFTMAX DB2 parameter CHNGPGS_THRESH (change pages threshold)
However, DB2‟s buffer pool flushes do not write the entire buffer pool content to disk. There is always a difference between the committed data, stored in the log files and in the buffer pools, and the data stored on the persistent storage. If a DB2 database crashes because of power failure, the database is in an inconsistent state. To solve this inconsistent state, DB2 performs a restart recovery before it is up and running after a database crash. The restart recovery reapplies all data at the point in time of the crash, which was committed and logged to the log files but not yet moved to persistent storage. (4)
(C) SAP AG
BC535
8-4
Restart Recovery
Log file header holds recovery range
Online Log Files Log records
Log sequence number Log rec. data
lied app Re
Tablespaces
n+1
01001010010 XXXXXX 01001010011 XXXXXX
n+2
01001010012 XXXXXX 01001010013 XXXXXX
Data not stored on tablespaces
...
SAP AG 1999
...
The next database start after a crash starts DB2‟s restart recovery. The goal of the restart recovery is to get DB2 into a consistent state. The log file header holds the restart recovery range by pointing to the first log sequence number, which holds committed data that is not yet applied to the tablespaces. As long as the database performs a restart recovery, no connections are allowed. The db2diag.log contains entries that are required in the case of a restart recovery, which show the first and last log sequence number (LSN) used during crash recovery.
(C) SAP AG
BC535
8-5
Roll Forward Recovery
Performed with operator intervention:
Restore the offline/online database backup Restore the log files Roll forward using log files
Containers
Backup 1 1
DB2 RESTORE
3 3
Database configuration
Log files 2 2
Roll forward
Online log files BRRESTORE
SAP AG 1999
With a roll forward recovery, a backup image (online or offline) and all the subsequent log files created after the backup are used to recover the database to a consistent state. When you design your backup strategy, consider how much time you want to spend on a recovery. A current backup enables you to perform a faster recovery since there are fewer log files to redo.
(C) SAP AG
BC535
8-6
Roll Forward Recovery Scenarios
db2 rollforward database to Roll forward to a point in time
t t1
DB2 BACKUP
n
n+1
n+2
n+3
n+4
Offline log files
Roll forward to end of logs
t t1 tEnd
db2 rollforward database to end of logs
SAP AG 1999
There are two ways to perform a roll forward recovery: You can roll the database forward to a certain point in time You can roll the database forward to the end of the logs.
If you perform a point in time recovery, ensure that you are using coordinated universal time (CUT) – also known as Greenwich mean time – since the database server calculates the timestamps based on this time zone. Applications from different time zones may connect to the database, but internal processing is done in CUT. Note: The timestamp used for backups is based on the local time zone of the database server.
(C) SAP AG
BC535
8-7
Database Recovery: Work Flow
1 1
2 2
History
Restore
3 3
Database
Logs
5 5
4 4
Database
Roll forward
SAP AG 1999
You must perform a database recovery in several steps: Use the DB2 recovery history file to search for the timestamp of the most recent backup. If you performed full database backups, you can identify the backup image using this timestamp. If the size of your database mandated backups on tablespace level, you must search for multiple backups. (1) Restore the database using the DB2 Control Center or the DB2 command restore. (2) Retrieve the log files that are requested in the recovery history file. The file specifies the log files needed to maintain consistency of an online backup. If you want to recover all operations made to the database, you must retrieve all the log files that have been created. (3) In the roll forward phase, use the DB2 Control Center to roll forward the transactions using the log files that have been retrieved. (4) Finally, you must instruct the database to roll forward stop. The database will then be consistent. (5)
This example does not show the roll back phase, which is the last step in this process. During the roll forward process, all committed transactions are reapplied to the database. At the end of the roll forward, a list of open transactions exists. These transactions are still waiting for a commit. For data consistency reasons, open transactions are then rolled back to make sure that changes that are not committed are not applied to the database.
(C) SAP AG
BC535
8-8
Restore and Recovery with Logical Failures
Logical failures
No structural change Logical failure (such as a dropped table)
t
Roll forward to PIT
Restore database Restore database
with CUT P Us e 2 CL DB
11:15:00 MET = 10:15:00 CUT
SAP AG 1999
In this example, the database is returned to a previous given status and then rolled forward to another (later) specified time. The following steps outline the procedure for doing this: First, a restore is performed using the most recent backup. Then all required archive files are copied back from the archive tapes. The database is then rolled forward, up to the specific point in time when the logical failure occurred.
This type of recovery is called point-in-time (PIT) recovery. You should only use PIT recovery if the data that is not applied to the database can somehow be re-created (since it has been added in later transactions than the PIT). Time specifications for the PIT have to be made in CUT. Do not use your local time zone, unless you live in the GMT time zone. The UNIX command date helps you to retrieve local time and CUT. Note: If you use the DB2 Control Center for a PIT recovery, the timestamp you apply must be in local time. The DB2 Control Center performs the conversion for you (ensure you have the most recent FixPak applied).
(C) SAP AG
BC535
8-9
You can use the DB2 Control Center to solve simple recovery cases, such as after losing data files because of a defective hard disk. First, determine the cause of the defect (such as hard disk defects caused by vibrations). This may help you to avoid similar situations in the future. After a hard disk defect, however, you need a new hard disk. To perform a restore and recovery using DB2 Control Center, call DB2 CC on your control workstation: 1 Check the tablespaces that have been destroyed with the disk crash. 2 Insert the DB2 backup tape with the correct backup timestamp. 3 Restore the tablespace out of your full online of offline database backup. 4 Insert the tape with the needed volume label. 5 Retrieve the log files that are needed to roll forward to the end of logs from the tape pool. 6 Roll the database forward using the DB2 Control Center. The DB2 logging user exit moves all the log files that are needed for roll forward from the offline log directory to the online log directory.
After all the log files have been applied, the database is made available with the command db2 rollforward stop. Open transactions are then rolled back and the database is ready for use. Note: Never drop the whole database if partial recovery is possible. This will save you the time required to re-create the database container files and restore mostly correct data.
(C) SAP AG
BC535
8-10
The db2agent process communicates with and controls the operation of the db2med and db2bm processes, also during a restore. During restore operations, the media controllers transfer data from device to restore buffers. When a buffer is full, it is transferred to disk on FIFO basis. The number of db2med processes depends on the number of restore devices or sessions. The parameter PARALLELISM may be used during a restore, but if you are not using multiple tape devices or ADSM sessions, multiple db2bm processes are not used. It is always much faster to empty the buffers to disk than to fill them from tape or ADSM. For example, you should use PARALLELISM when reading the backup images from disk. The number of buffers should be large enough to keep the media controllers and their devices busy (more than 2). The size of the restore buffer should be 1024, or preferably 2048 4 KB pages (4 MB, 8MB). You may use a multiple of the backup buffer size. But if you achieve good backup performance with a certain backup buffer size, you can ensure excellent restore performance by using the same size for the restore buffers. Note: During a restore, DB2 uses page cleaners to transfer the buffer contents to disk.
(C) SAP AG
BC535
8-11
DB2‟s Restore Tools
DB2 Control Center
Restore and roll forward
Command line processor
SAP AG 1999
To issue the DB2 restore and roll forward commands, you can use: The DB2 Control Center, or The DB2 command line processor (CLP)
The DB2 Control Center is DB2‟s graphical user interface used for perform database administration tasks. DB2‟s command line processor allows you to perform database administration task using SQL commands.
(C) SAP AG
BC535
8-12
DB2 Restore Tools: DB2 Control Center
Select a backup from DB2‟s backup history list Check that the source of the backup is available
SAP AG 1999
To perform a restore using the DB2 Control Center, go to the main screen of the DB2 Control Center. Place your cursor on the database name (SAPSID), click the right mouse button, and choose Restore Database. To configure the restore command, the DB2 Control Center is divided into five tabs, allowing you to adapt the restore commands to your needs: Backup image Tablespaces Containers Roll forward Options
In the Backup image tab, specify which backup will be used for the restore. Ensure you choose a recent backup from DB2‟s backup history list and check that the source of this backup is available. To check if the source of the backup is available, check the List of directories or tapes.
(C) SAP AG
BC535
8-13
DB2 Restore Tools: DB2 Control Center Tablespaces
Use a full database backup to avoid inconsistency
SAP AG 1999
In the Tablespaces tab, select Use a full database backup to avoid inconsistency. If you select a single tablespace or group of tablespaces to perform a partial tablespace restore, you may create a database that holds information in different tablespaces that do not match, from an application point of view.
(C) SAP AG
BC535
8-14
DB2 Restore Tools: DB2 Control Center Containers
Only select this option if you want to perform a redirect restore
Select a table space you are going to restore redirect Use Add, Change, and Remove to change the container layout per tablespace
Tip: Use SAP‟s redirect restore tool
SAP AG 1999
In the Containers tab, option Redirect table containers enables you to change the container layout per tablespace. If you select this option, you need to mark and configure each tablespace that you want to restore redirect. If you are going to change a high number of tablespaces and containers this may take a long time. Also, there is no way of temporarily saving any results of your work. And if you have a large number of tablespaces and containers, you may not have an adequate overview of your redirected restore layout. Note: If you are going to change the layout of your database, use SAP‟s redirect restore tool.
(C) SAP AG
BC535
8-15
DB2 Restore Tools: DB2 Control Center - Roll Forward
Only select this option if you want to perform a roll forward directly after the restore. If selected, specify either a point in time or end of logs recovery. Select this option if you are going to perform a roll forward after a restore.
Use roll forward item after a restore to perform roll forward
SAP AG 1999
In the Roll forward tab, you can specify if you want to perform a roll forward right away, after a restore. If you select this option, you must define whether it will be a roll forward to a point in time (PIT) or a roll forward to the end of logs. If you do not want to perform a roll forward right away (after a restore), you can set a roll forward item in the DB2 Control Center. To do this, after you have performed a successful database restore, place your cursor on the database name (SAPSID) and click the right mouse button. Choose Rollforward... Again you can choose between a roll forward to a point in time (PIT) or a roll forward to the end of logs. If you have selected Leave in roll-forward pending state, you must issue a roll forward stop command to finish the roll forward procedure.
(C) SAP AG
BC535
8-16
DB2 Restore Tools: DB2 Control Center - Options
Select the option Offline to avoid inconsistency
Configure the number of buffers and the buffer size DB2 uses for read and write operations
Choose Show Command to display the corresponding DB2 command
SAP AG 1999
In the Options tab, select the option Offline to ensure consistency while you perform the restore. In the section Performance, you can specify the number of buffers and the buffer size DB2 will use to read from the backup media and write the information to disks.
(C) SAP AG
BC535
8-17
DB2 Restore Tools: DB2 CLP Commands
DB2 restore command
DB2 restore and roll forward command DB2 roll forward command
SAP AG 1999
To perform a restore using the DB2 command line processor, you must first have the syntax for each command. At the command prompt, enter: db2 “? restore” db2 “? rollforward”
Alternatively, you can use the DB2 Control Center to configure the restore and roll forward (as previously described), and then choose Show Command. The corresponding CLP command is displayed. Save this CLP command in a file, and modify it to meet your requirements. For detail information about the restore and roll forward CLP commands, refer to DB2‟s Command Reference.
(C) SAP AG
BC535
8-18
Log File Life Cycle for 2-Step Log File Management
log_dir DB2 logging user exit DB2 logging user exit
1 1
4 4
log_archive
log_retrieve ADM
2 2
BRARCHIVE
Tape / Storage Management Solution
SAP AG 1999
3 3
BRRESTORE
Log files can be managed in two-steps with the use of DB2‟s logging user exit, and the SAP tools BRARCHIVE AND BRRESTORE: The DB2 logging user exit copies log files from the online log directory (log_dir) to the offline log directory (log_archive). For each log file copied, an entry is created in the administration database (ADM). The logging user exit also copies log files from the log_retrieve directory to the online log directory (log_dir) in the event that a roll forward has to be performed. The SAP tool BRARCHIVE provides log file storage management functionality. For each log file that is copied or deleted to or from the offline log directory (log_archive), a corresponding entry in the administration database is made. The SAP tool BRRESTORE provides log file restore management functionality. For each log that is restored to directory log_retrieve, a corresponding entry in the administration database is made.
(C) SAP AG
BC535
8-19
Log File Management: DB2 Control Center
Choose the backup you want to use for the restore
S0000100.LOG.2000012053624.NODE0000 ADSM:TCCB2. S0000101.LOG.2000012063624.NODE0000 ADSM:TCCB2 S0000102.LOG.2000012073624.NODE0000 ADSM:TCCB2 S0000103.LOG.2000012083624.NODE0000 ADSM:TCCB2 S0000104.LOG.2000012093624.NODE0000 ADSM:TCCB2
The related log files are displayed
Restore
Restore the log files from the backup media
SAP AG 1999
Log files can also be managed using the DB2 Control Center: From the DB2 Control Center., place your cursor on the database name (SAPSID) and click the right mouse button. Choose R/3 Log File Management Restore. Specify the node where you want to restore log files. To determine which log files are to be restored, specify a backup date. All available log files for the selected backup are displayed (together with the medium on which they are stored). Specify the log file range to be restored. To start the restore procedure, choose Restore. A dialog box appears telling you that the restore process has been started. After the restoring has finished, another dialog box appears telling you it it was successful. All restore actions and their results can be seen in the Journal page.
(C) SAP AG
BC535
8-20
Log File Management: BRRESTORE
Restoring log files from tape
Command syntax Description brrestore -a 110-120 -d tape -v D4XA99 -out Tries to retrieve the log files with the numbers 110 to 120 from tape D4XA99 and prints out all messages.
Emergency log file restore from ADSM
Command syntax Description brrestore -ex "*" S000112* 19980612 –d adsm Stores log files available from ADSM to the current directory, provided they match the given file name pattern and were stored to ADSM on 12 June 1998 or later.
Emergency log file restore from tape
Command syntax Description brrestore -ex "*" –t /dev/rmt0.1 Stores ALL files available on the tape in device /dev/rmt0 to the current directory.
Query functions
Command syntax Description brrestore –q '*NODE0002' -out Displays information about the archiving work to be done. The names of all archived log files are under the node NODE0002.
Query functions
Command syntax Description brrestore –q adsmMC -out Displays the names of all known ADSM management classes.
SAP AG 1999
If you use the SAP tool BRRESTORE to manage log files, you can use the following commands in combination with brrestore