SAP Web as 6.20 DB2 UDB v7 Administration Guide

Database Administration Guide

®

SAP on IBM DB2 Universal Universal Database for UNIX and Windows Document Version 1.00 - August 15, 2003

SAP AG Neurottstraße 16 69190 Walldorf Germany T +49/18 05/34 34 24 F +49/18 05/34 34 20 www.sap.com

© Copyright 2003 SAP AG. All rights reserved.

JAVA® is a registered trademark of Sun Microsystems, Inc.

No part of this publication may be reproduced or transmitted in any

J2EE™ is a registered trademark of Sun Microsystems, Inc.

form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior

JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc.,

notice.

used under license for technology invented and implemented by Netscape.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.

Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of Microsoft Corporation.

SAP, SAP Logo, R/2, R/3, mySAP, mySAP.com, xApps, mySAP Business Suite, and other SAP products and services mentioned herein

®

®

®

®

IBM , DB2 , DB2 Universal Database, OS/2 , Parallel Sysplex ,

as well as their respective logos are trademarks or registered

MVS/ESA, AIX®, S/390®, AS/400®, OS/390®, OS/400®, iSeries,

trademarks of SAP AG in Germany and in several other countries all ®

pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere , TM

Netfinity®, Tivoli®, Informix and Informix® Dynamic Server

are

over the world. All other product and service names mentioned are the trademarks of their respective companies.

trademarks of IBM Corp. in USA and/or other countries. ORACLE® is a registered trademark of ORACLE Corporation.

Disclaimer Some components of this product are based on Java™. Any code

®

®

®

®

UNIX , X/Open , OSF/1 , and Motif are registered trademarks of

change in these components may cause unpredictable and severe

the Open Group.

malfunctions and is therefore expressively prohibited, as is any decompilation of these components.

LINUX is a registered trademark of Linus Torvalds and others. Any Java™ Source Code delivered with this product is only to be used Citrix®, the Citrix logo, ICA®, Program Neighborhood ®, MetaFrame®, ®

®

®

WinFrame , VideoFrame , MultiWin and other Citrix product names

by SAP’s Support Services and may not be modified or altered in any way.

referenced herein are trademarks of Citrix Systems, Inc. HTML, DHTML, XML, XHTML are trademarks or registered ®

Documentation in the SAP Service Marketplace

trademarks of W3C , World Wide Web Consortium, Massachusetts

You can find this documentation at the following Internet address:

Institute of Technology.

service.sap.com/instguides

Typographic Conventions

Icons

Type Style

Represents

Icon

Meaning

Example Text

Words or characters that appear on the screen. These include field names, screen titles, pushbuttons as well as menu names, paths and options.

Caution

Cross-references to other documentation

Recommendation

Example text

Emphasized words or phrases in body text, titles of graphics and tables

Syntax

EXAMPLE TEXT

Names of elements in the system. These include report names, program names, transaction codes, table names, and individual key words of a programming language, when surrounded by body text, for example, SELECT and INCLUDE.

Example text

Screen output. This includes file and directory names and their paths, messages, names of variables and parameters, source code as well as names of installation, upgrade and database tools.

Example text

Exact user entry. These are words or characters that you enter in the system exactly as they appear in the documentation.

Variable user entry. Pointed brackets indicate that you replace these words and characters with appropriate entries.

EXAMPLE TEXT

Keys on the keyboard, for example, function keys (such as F2) or the Enter key.

Example Note

SAP Database Administration Guide: IBM DB2 Universal Databas

Contents SAP Database Administration Guide: IBM DB2 Universal Database for UNIX and Windows 1 Introduction......................................................................................... 8 1.1 About this Documentation.....................................................................8 1.1.1 Who Should Use this Documentation ...........................................................................8 1.1.2 How this Documentation is Structured ..........................................................................9

1.2 Naming Conventions............................................................................10 1.3 Admin Tools: Enhancements and Changes.......................................11 1.3.1 New Features ..............................................................................................................13 1.3.2 Differences Between 4.6D and 6.NN Admin Tools .....................................................16

1.4 DB2 UDB for UNIX and Windows Version 7: Enhancements............17 1.5 DB2 UDB for UNIX and Windows Version 8: Enhancements............19

2 Basic Concepts ................................................................................ 21 2.1 Introduction...........................................................................................21 2.1.1 The SAP DB2 Admin Tools.........................................................................................21 2.1.2 User Interfaces ............................................................................................................22

2.2 SAP Log File Management...................................................................23 2.2.1 Recovery - The Need for Log Files .............................................................................23 2.2.2 Recovering Log Files ..................................................................................................25 2.2.3 Log File Life Cycle (Archive and Restore) ..................................................................27 2.2.4 Log File Deletion .........................................................................................................30 2.2.5 Protocols .....................................................................................................................31 2.2.6 Raw Device Log Files .................................................................................................32 2.2.7 Log File States ............................................................................................................32 2.2.8 Special Cases - Non-Standard Behavior of Database Systems.................................33

2.3 SAP Security Concept..........................................................................34 2.4 Multi-Partitioned Systems....................................................................35

3 Installation and Setup ...................................................................... 36 3.1 Introduction...........................................................................................36 3.2 SAP System Environment....................................................................38 3.2.1 SAP System Users and Groups..................................................................................38 3.2.2 User Environment........................................................................................................40 3.2.3 Access Authorizations for Admin Tool-Related Directories ........................................41

3.3 Installing the Admin Tools Manually ..................................................42 3.3.1 Prerequisites for the Admin Tools Installation.............................................................43 3.3.2 General Information ....................................................................................................45 3.3.3 Upgrading the Admin Tools.........................................................................................45 3.3.4 Installing the Latest Admin Tools ................................................................................46 3.3.5 Checking the Admin Tools Environment.....................................................................47 3.3.6 Actions Performed by the sddb6ins Program .............................................................48 3.3.7 Special Configurations ................................................................................................51 3.3.8 Enabling the Database for Rollforward Recovery .......................................................52 3.3.8.1 Enabling Rollforward Recovery for a Single-Partitioned Database.......................53 3.3.8.2 Enabling Rollforward Recovery for a Multi-Partitioned Database .........................54

4

August 2003


3.4 Installation and Setup of the DB2 Control Center Extensions .........55 3.4.1 Installation of the DB2 Control Center ........................................................................55 3.4.2 Installing the DB2 Control Center Extensions.............................................................55 3.4.3 Setting Up the DB2 Control Center .............................................................................56 3.4.4 Using the DB2 Control Center Extensions ..................................................................63

4 Configuration .................................................................................... 64 4.1 Changing the Admin Tools Configuration..........................................64 4.1.1 Parameters Affecting Log File Management Storage .................................................64 4.1.2 Configuring the Admin Tools Using Environment Files ..............................................67

4.2 Configuring the System for Archiving Log Files ...............................71 4.2.1 Archiving Directly to TSM............................................................................................73 4.2.2 Archiving Directly to Disk ............................................................................................74 4.2.3 Archiving to an Alternative Storage Management Product .........................................75 4.2.4 Indirect Archiving to Tape ...........................................................................................75 4.2.5 Indirect Archiving to TSM ............................................................................................81 4.2.6 Indirect Archiving Using a Customer Script ................................................................81

4.3 Configuring TSM (Tivoli Storage Management).................................83 4.3.1 Configuring TSM Files dsm.opt and dsm.sys .............................................................84 4.3.2 Configuring TSM Environment Variables ....................................................................85 4.3.3 Configuration Considerations......................................................................................87

4.4 DB2 Configuration Parameters ...........................................................88 4.5 Disk Space Considerations for Running the Admin Tools ...............89

5 Regular Administration.................................................................... 91 5.1 DB2 Log File Management...................................................................91 5.1.1 Archiving Log Files from Disk to Backend ..................................................................91 5.1.2 Deleting Log Files .......................................................................................................94

5.2 Storage Management ...........................................................................96 5.2.1 Checking the Space Available in a File System..........................................................97 5.2.2 Checking the Space Available in a Tablespace..........................................................97 5.2.3 Checking the Size of Tables and Indexes...................................................................99

5.3 Performance Monitoring ......................................................................99 5.3.1 Monitoring Database Performance ...........................................................................100 5.3.2 Monitoring Dynamic SQL Statements .......................................................................101 5.3.3 Updating Statistics for Database Tables and Checking for Reorganization .............102 5.3.4 Job Monitoring...........................................................................................................103

5.4 Database Backup................................................................................103 5.4.1 Backup Considerations .............................................................................................103 5.4.2 Performing the Backup..............................................................................................104 5.4.3 Integrity of Backups...................................................................................................105 5.4.4 Frequency of Backups and Time Required...............................................................106 5.4.5 Advanced Backup Techniques..................................................................................106

6 Advanced Tasks ............................................................................. 107 6.1 The db6util Tool..................................................................................107 6.2 Setting and Updating Passwords......................................................108 6.3 Using the Journal in the DB2 Control Center Extensions...............108 6.4 Advanced Log File Management Tasks............................................109 6.4.1 Deleting Special Log Files.........................................................................................110 6.4.2 Deleting Tapes from the Admin DB...........................................................................112

6.5 Reorganization of Tables and Tablespaces .....................................112 6.6 Redirected Restore.............................................................................114

August 2003

5


6.6.1 Usage of Tool brdb6brt .............................................................................................115

6.7 Command Line Tasks Using db6clp .................................................118 6.8 Monitoring Database Alerts ...............................................................120 6.9 Monitoring Lock Waits and Deadlocks .............................................120 6.10 Managing Backups and Logs Archived on TSM............................121 6.11 The db2inidb Tool.............................................................................122 6.11.1 db2inidb Option: as mirror.......................................................................................123 6.11.2 db2inidb Option: as snapshot..................................................................................126 6.11.3 db2inidb Option: as standby for Backups ...............................................................127 6.11.4 db2inidb Option: as standby for Hot-Standby Database.........................................129

6.12 Dual Logging.....................................................................................135 6.13 Adjusting the Content of the Admin DB .........................................135

7 Emergency Tasks ........................................................................... 137 7.1 SAP Database Recovery ....................................................................137 7.2 Log File Restore of the SAP Database..............................................138 7.3 Recovery of the Admin DB ................................................................143 7.3.1 General Information ..................................................................................................143 7.3.2 Recovery Process .....................................................................................................144

Appendix A: Tool Command Line Parameters ............................... 147 brarchive - Log File Archive Tool............................................................147 brdb6brt - Redirected Restore Tool ........................................................150 brrestore - Log File Retrieval Tool ..........................................................153 db2adutl - DB2 TSM Management Tool ..................................................156 db2ckbkp - DB2 Check Backup Utility....................................................158 db2trc - DB2 Trace....................................................................................159 db2uext2 - DB2 User Exit.........................................................................162 db6adutl - SAP TSM Management Tool ..................................................163 db6util - Tool to Assist Database Administration..................................164 dmdb6bkp - Database Backup Tool........................................................165 dmdb6srp - Update Statistics Tool .........................................................166 dmdb6rts - Table Reorganization Tool ...................................................167 sddb6ins - Admin Tools Installation Program .......................................168 sddb6mir - Admin DB Creation and Mirror Tool Utility .........................170

Appendix B: Troubleshooting.......................................................... 171 DB2 Traces................................................................................................171 DB2 Trace Facility db2trc ...................................................................................................171 DB2 CLI Trace....................................................................................................................172

Admin Tools Traces .................................................................................173 Downloading Admin Tool Patches from the SAP Service Marketplace.............................174 General Troubleshooting Comments .................................................................................176 Installation (sddb6ins) Troubleshooting .............................................................................177 User Exit Troubleshooting ..................................................................................................178 brarchive/brrestore Troubleshooting ..................................................................................181 DB2 Control Center Extensions Troubleshooting ..............................................................196 Collecting Diagnostic Information for SAP Support ...........................................................197

6

August 2003


Appendix C: Implementation Details............................................... 198 Admin DB Structure .................................................................................198 brarchive and brrestore backend Library Architecture .......................................................202

DB2 Control Center Extensions Architecture ........................................203

Appendix D: Example of File init.db6............................... 204 File init.db6 on UNIX .................................................................204 File init.db6 on Windows ..........................................................206

Appendix E: Example of Customer Script db6sctsm.smp ............ 208 Appendix F: References ................................................................... 210 DB2 UDB for UNIX and Windows V7 - Additional Documentation .......210 DB2 UDB for UNIX and Windows V8 - Additional Documentation .......212

Glossary and Index ........................................................................... 214

August 2003

7

1 Introduction

1 Introduction 1.1 About this Documentation This documentation supplies information specific to the SAP environment and describes daily administration tasks using both product tools and the SAP-specific administration tools. The documentation is written based on DB2 UDB Version 7 as the underlying database release. The concepts described in this documentation are also expected to apply to other DB2 for UNIX and Windows database versions. The information in this documentation has been collected from a variety of sources such as previous administration documentations, SAP Notes and customer experience and gives the administrator a single point of reference. The information is presented in a task-oriented way, separating information frequently needed from that which the administrator only requires, for example, in an emergency. It also includes a new troubleshooting section giving advice about solving problems before contacting SAP, or what information to send to SAP if no solution can be found.

The information provided in this documentation applies to all SAP kernel releases starting from 3.1I and higher as well as IBM DB2 Universal Database for UNIX and Windows Version 7 and higher. If you would like to give feedback on this documentation, we would ask you to use the following email address: [email protected]

1.1.1 Who Should Use this Documentation This documentation is intended primarily for database administrators and SAP system administrators who need to install and maintain a SAP system running on DB2 UDB for UNIX and Windows. It is structured to be of use to both people new to database administration and experienced SAP administrators alike, including those with experience with other database products. It will also be of use to a range of customer support functions when planning, installing, and maintaining systems, or assisting in these tasks.

8

August 2003

1 Introduction

1.1.2 How this Documentation is Structured This documentation is divided into the following sections:

Chapters ...

1.

Introduction Provides general information about the guide, its targeted audience, interfaces, naming conventions and new features.

2.

Basic Concepts Describes administration and Admin Tools and the related concepts, including DB2 UDB-specific information on installing and configuring servers and clients for the SAP system.

3. Installation and Setup Explains how to install Admin Tool patches and deal with upgrades. It also covers manual (re)installation of the tools if required. 4. Configuration Describes the available options and steps required to configure the Admin Tools. 5. Regular Administration Lists and describes tasks that system administrators need to perform on a regular basis such as storage administration, backup and log file management. 6. Advanced Tasks Covers one-time or rarely needed tasks not related to configuration. Some tasks may be prerequisites for other day-to-day tasks, which must be performed before log files can be stored to tape. Also includes monitoring and performance considerations. 7. Emergency Tasks Tasks that administrators may need to perform after a database failure.

Appendices A. Tool Command Line Parameters Contains detailed command line syntax for each command. B. Troubleshooting Describes what to do when tools do not work as expected. C. Implementation Details Contains internal tool information, especially for support personnel. D. Example of the File init.db6 E. Example of Customer Script db6sctsm.smp F. References G Glossary and Index

August 2003

9

1 Introduction

1.2 Naming Conventions Terminology For information on terminology used in this documentation, see Glossary and Index [Page 214].

Product Names In this documentation the following naming conventions apply: SAP Web Application Server / SAP system SAP Web Application Server is referred to as SAP Web AS or SAP system. Additionally the term SAP system also refers to other components of mySAP.com. IBM DB2 Universal Database for UNIX and Windows IBM DB2 Universal Database for UNIX and Windows is referred to as DB2 UDB for UNIX and Windows or DB2. IBM DB2 Universal Database Enterprise Server Edition for UNIX and Windows IBM DB2 Universal Database Enterprise Server Edition for UNIX and Windows is referred to as DB2 UDB ESE for UNIX and Windows. single-partitioned system / EE system The term single-partitioned system replaces the term EE system. multi-partitioned system / EEE system The term multi-partitioned system replaces the term EEE system. 6.NN Admin Tools / 6.10 Admin Tools The term 6.NN Admin Tools replaces the term 6.10 Admin Tools, as these Admin Tools can be used for any SAP system release starting from 6.10 and higher. Tivoli Storage Management (TSM) / ADSM The IBM storage product ADSM has been renamed to TSM (Tivoli Storage Manager) in the most recent versions. The names are often used interchangeably, but in this documentation the product is referred to as TSM. The term ADSM is normally only used in this guide to indicate program parameters or configuration parameter names.

Tivoli (IBM) no longer supports the product ADSM. Customers still using ADSM are strongly advised to upgrade to TSM Client Version 4.1 or higher. Serious problems have occurred when using the Admin Tools with TSM Version 3.7. This version is also out of service now, so an upgrade is also strongly advised. Windows The term Windows refers to Windows 95, Windows 98, Windows NT, or Windows 2000. Servers can only be installed on Windows NT or Windows 2000. Clients can be installed on any Windows platform.

10

August 2003

1 Introduction

1.3 Admin Tools: Enhancements and Changes The following sections summarize recent changes to the Admin Tools.

Within these sections, we refer to the Admin Tools release and not the SAP system release, which do not need to be the same. For example, the 4.6D Admin Tools should be used on all SAP 3.1I to 4.6D systems where DB2 UDB for UNIX and Windows Version 7 or higher is installed. The 6.10 Admin Tools or higher should be used with all SAP system releases above and including 6.10. The following changes are informational or not patch-specific: •

Installation Program sddb6ins The command line syntax of sddb6ins has been simplified by extracting information from the environment. The sddb6ins program now also contains the remaining Admin Tools, which it installs. sddb6ins therefore no longer requires a separate DBATOOLS.SAR file, and the shared library path environment variable must no longer be set before starting it. The Admin Tools installation program sddb6ins now checks many aspects of the environment to locate the majority of problems experienced by customers. In the past a difficulty with the Admin Tools has been the runtime environment. Admin Tools files and directories may have been unintentionally removed or were given the wrong ownership or authorizations; environment variables may have been set to non-existent directories, and so on. For more information, see Installing the Admin Tools Manually [Page 42].

•

Admin Tools in an MCOD Environment The Admin Tools have been modified to support this SAP feature. They are only installed with the first SAP system, that is, only once per database. As log files are shared by all the SAP systems using the same single database, user db2 and all adm users can perform log file management tasks. It is necessary to differentiate between the SAP system ID and the SAP system database name , as these are no longer necessarily the same. Additionally, user IDs and directory names may be affected. If and have the same value, as is often the case for the first SAP system installed on a database, the old terminology still applies as described in the table below. However, there is a limitation in SAP 4.6x releases, that the initial SAP system must have the same ID as the SAP database. Further SAP systems have different IDs. The database connect user for the first SAP system in a 4.6D system environment is still sapr3. If an MCOD system is removed, you must make sure that the db2 user environment still points to another valid MCOD system using the same database.

August 2003

11

1 Introduction

The following terminology conversions must be used: SAP System and Database Name Descriptions ID

Old ID

New ID

SAP system ID

,

,

SAP database name

,

,

User

Old ID

New ID

Database instance owner

db2

db2

SAP database administrator

adm

adm

Database connect user

sapr3

sap

User ID Descriptions

sapr3 is still used for the first SAP system in a 4.6D system environment.

Directory Names Descriptions Directory

Old Name

New Name

SAP system related directories

db2 home directory and subdirectories

The following are examples of how the descriptions of directory names have changed on UNIX systems compared to earlier versions of this documentation on. If and are the same, the real directory name after substitution remains unchanged: Old New /usr/sap//sys/exe/run

/usr/sap//sys/exe/run

/db//log_dir

/db//log_dir

If the original SAP system is removed, certain Admin Tools are no longer fully functional. This restriction only applies to 4.6D SAP systems. From SAP system Release 6.10 and higher, it is only necessary to verify that the db2 user is set up correctly. Use sddb6ins –checkonly to verify this. This user may require changes to his environment.

12

August 2003

1 Introduction

For more information on released platforms and availability, see the SAP Service Marketplace at the Internet address service.sap.com/platforms and choose → Multiple Components in One Database (MCOD). •

64-bit Support The Admin Tools have been modified to work with 64-bit platforms.

Some changes were specifically implemented for the 4.6D and 6.NN Admin Tools and delivered as patches. For more detailed information, see New Features [Page 13]. For information on the main differences between the 4.6D and 6.NN Admin Tools, see Differences Between 4.6D and 6.NN Admin Tools [Page 16].

1.3.1 New Features This section provides information on new features that have been specifically implemented for the 4.6D and 6.NN Admin Tools.

6.NN stands as a placeholder for all Admin Tools releases starting from 6.10 and higher.

New Features of 4.6D Patch 20 and 6.NN Patch 9 •

brarchive/brrestore customer script interface You can now write your own individual backend interface. For more information, see Archiving Log Files from Disk to Backend [Page 91].

•

Option brarchive –GAL/-DMP You use this option to adjust the Admin DB to a file system. For more information, see Adjusting the Content of the Admin DB [Page 135].

•

Option sddb6ins –r You use this option to recreate the Admin DB from a SAR file. For more information, see Recovery of the Admin DB [Page 143].

•

Support of two TSM management classes The User Exit and brarchive can now archive log files to two TSM management classes. For more information, see Archiving Directly to TSM [Page 73].

•

Restore of log files excluding the Admin DB The User Exit restores log files from log_archive or log_retrieve without any information on those log files in the Admin DB. You activate this feature by setting configuration variable DB2DB6_UEXIT_RETRIEVE_WO_DB in file init.db6.

•

Admin Tools and DB2 Version 8 The Admin Tools are now supported for DB2 UDB for UNIX and Windows Version 8.

For more detailed information on 4.6D patch 22 and 6.NN patch 11, see SAP Note 533979.

August 2003

13

1 Introduction


Option brarchive –dt You use this option to delete tapes using the command line. For more information, see Deleting Log Files [Page 94].

•

Option brarchive –do You use this option to delete obsolete log files using the command line. For more information, see Advanced Log File Management Tasks [Page 109].

•

Support of TSM LANFREE The Admin Tools have been modified to support the TSM LANFREE feature. You activate this feature by setting configuration variable DB2DB6_TSM_MULTITHREADED in file init.db6. For more information, see Configuring TSM (Tivoli Storage Management) [Page 83].



Indirect archiving without accessing the Admin DB If you are using indirect archiving, the User Exit is now able to archive without accessing the Admin DB. The necessary tasks to update the Admin DB content are moved from the User Exit to brarchive. You activate or deactivate this feature of the User Exit by setting configuration variable DB2DB6_UEXIT_ARCHIVE_WO_DB to ON or OFF. Program sddb6ins automatically sets variable DB2DB6_UEXIT_ARCHIVE_WO_DB to ON if during the installation of the Admin Tools the new version of transaction DB12 is recognized by the Admin Tools.

•

Archiving directly to disk If you are using direct archiving, the User Exit is now able to archive log files directly to disk. For more information, see Configuring the System for Archiving Log Files [Page 71].



Indirect archiving without accessing the Admin DB is now the default for all releases. Before mirroring, the sddb6mir tool also adds missing log files. This updates the information in the old version of transaction DB12. Archiving without the Admin DB causes less problems. For this reason, this is now the default for all SAP system releases. To turn archiving without the Admin DB off, set DB2DB6_UEXIT_ARCHIVE_WO_DB to OFF in the init file.

14

August 2003

1 Introduction

New Features of Other Administrative Tools •

Backup restore tool brdb6brt patch 5

New comments starting with SET IGNORE ROLLFORWARD The script created by brdb6brt patch 2 or higher contains new comments starting with SET IGNORE ROLLFORWARD. You might have to use this feature if the log files contain information on container change operations and the operations cannot be applied during the rollforward recovery. This may occur in a redirected restore scenario because the container layout is then different to the one when the backup was made. You activate this feature by removing the two dashes (−−) at the beginning of the corresponding comment line.

Support of striped containers Striped containers are created if you are using DB2 Version 7 and instance registry variable DB2_STRIPED_CONTAINERS is set to ON. For DB2 Version 8, this variable is set to ON by default.

Backup information in brdb6brt brdb6brt automatically contains the information from the latest full backup. It retrieves the information from the backup history file.

Option –bm RETRIEVE RELOCATE You use this option to create scripts that can be used for database relocation. These scripts can be used as a template in db2relocatedb with command relocate DB or in db2inidb with command RELOCATE USING.

Option –replace You use this option to modify the generated scripts for redirected restore and relocation. For example, you can replace the database name .

For more detailed information on patch 5 of brdb6brt, see SAP Note 599121. •

DB2 Control Center Extensions patch 3:

You can now use the DB2 Control Center Extensions for the 4.6D and 6.NN Admin Tools.

The DB2 Control Center Extensions have been modified to support the customer script option of brarchive.

The DB2 Control Center Extensions are supported for DB2 UDB for UNIX and Windows Version 8.

For more detailed information on patch 3 of the DB2 Control Center Extensions, see SAP Note 599123.

August 2003

15

1 Introduction

1.3.2 Differences Between 4.6D and 6.NN Admin Tools Since the implementation of the 6.NN Admin Tools, the main differences to the 4.6D Admin Tools are: •

ADM database mirroring no longer required In the past, the Admin DB ADM was mirrored to the SAP database to reduce the risk of data loss. This has proven to be unnecessary. Thus, the program sdd6mir, which was used to perform this action, is no longer required. When SAP system log files are stored directly by the User Exit to TSM, the Admin DB is not required and so no backup (mirror) of this database is needed. If log files are stored using brarchive, a backup copy of the Admin DB is stored to the same medium as the log file(s) at the end of each brarchive run. For more information on how to recover the Admin DB, see Recovery of the Admin DB [Page 143]. This implies that the 6.NN Admin Tools delivery does not contain program sddb6mir any more.

•

New location for Admin Tools executables To reduce the dependencies between the almost release-independent Admin Tools and the release-dependent SAP products, the Admin Tools, their configuration, protocol and trace files have been moved. This means that, for example, unlike Admin Tool versions up to 4.6D, the 6.10 Admin Tools no longer need to be re-installed after an SAP system upgrade. For more information, see Installing the Admin Tools Manually [Page 42]. The following table shows the location of the Admin Tools files and variables for 4.6D and 6.NN:

Executables and libraries: brarchive/ brrestore, db6adutl, libdb6* Protocol files

Trace files

4.6D

6.NN

UNIX: /usr/sap/SYS/ exe/run

UNIX: /admintools/ bin

Windows: :\usr\sp\\SYS\ exe\run

Windows: \admintools\ bin

UNIX: /db2//saparch /db2//saprest

UNIX: /admintools/ protocols

Windows: :\db2\db2\ saparch :\db2\db2\ saprest

Windows: \admintools\ protocols

UNIX: /tmp/TraceFiles

UNIX: /admintools/ TraceFiles

Windows: \ TraceFiles

16

Windows: \admintools\ TraceFiles

August 2003

1 Introduction

TSM environment variables

UNIX: in.dbenv_.csh/sh Windows: user environment

UNIX: /admintools/ bin/dbaenv_.csh/ sh Windows: user environment

1.4 DB2 UDB for UNIX and Windows Version 7: Enhancements The following are the major new features in Version 7.1 and Version 7.2, which are relevant to the Admin Tools:

Version 7.1 •

Closing the Active Log File after Backup After an online backup is complete, DB2 forces the currently active log file to be closed and as a result it will be archived. This ensures that an online backup has a complete set of archived log files available for recovery. For more information, see the IBM documentation DB2 Administration Guide: Implementation

•

On Demand Log Archive Support You can now force the currently active log file closed, which is then automatically archived by DB2 if the User Exit is enabled. This feature gives database administrators more granular control over their backup and restore strategy. For more information, see the IBM documentation DB2 Administration Guide: Implementation.

•

Log Limit Increased to 32 GB The maximum size of (the sum of) all log files has been increased from 4 GB to 32 GB. This enables applications to perform extremely large amounts of work within a single transaction. For more information, see the IBM documentation DB2 Administration Guide: Implementation.

•

Renaming a Tablespace When creating a tablespace, you specify its name. In previous versions, you were not allowed to change that name again during the lifetime of the tablespace object. Now, an administrator can rename a tablespace with the RENAME TABLESPACE statement. For more information, see the IBM documentation DB2 Administration Guide: Implementation.

August 2003

17

1 Introduction

Version 7.2 •

Block Transactions When Log Directory is Full DB2 Version 7.2 provides a new registry variable DB2_BLOCK_ON_LOG_DISK_FULL, which is used to prevent disk full errors from being generated when DB2 cannot create a new log file in the active log directory. DB2 attempts to create the log file every five minutes and writes a message to file db2diag.log after each attempt. This file indicates if your application is hanging. For more information on the DB2 instance variable DB2_BLOCK_ON_LOG_DISK_FULL , see the DB2 Release Notes.

•

Suspended I/O Supports continuous system availability, while allowing online split mirror handling of the database. By momentarily suspending I/O to disk, DB2 will ensure that the split mirror copy maintains its integrity. A new tool db2inidb has been created that operates on split mirror copies.

If you want to use the db2inidb tool, we strongly recommend that you read The db2inidb Tool [Page 122] before you start using it. •

Dual Logging DB2 now has the capability to mirror the active log files to protect databases from accidental deletion of an active log and data corruption caused by a hardware failure. A new registry variable DB2_NEWLOGPATH2 instructs the database to write an identical copy of the log files to a different path on a physically separate disk.

•

Parallel Recovery DB2 now uses multiple agents to perform both crash recovery and database rollforward recovery. This may improve performance on SMP machines.

•

Incremental and Delta Backups There are two backup types supported:

Incremental backup: Only contains pages that have been modified since the last full database or tablespace backup.

Delta backup: Only contains data that has changed since the last tablespace backup (full, incremental, or delta backup).

18

August 2003

1 Introduction

1.5 DB2 UDB for UNIX and Windows Version 8: Enhancements All new features of DB2 UDB for UNIX and Windows Version 8 are described in the IBM documentation IBM DB2 Universal Database Version 8 - What’s New. This section briefly summarizes new features that affect log file management and database administration in an SAP environment.

Logging Enhancements •

Dual Logging Restrictions of this feature, which was introduced in DB2 Version 7.2, have been removed. This feature is now supported on Windows as well. You can choose the mirror log directory by setting database configuration parameter mirrorlogpath.

•

Infinite Logging It allows an active unit of work to span the primary logs and archive logs, effectively allowing a transaction to use an infinite number of log files. Without infinite active log enabled, the log records for a unit of work must fit in the primary log space. Infinite active log is enabled by setting logsecond to -1. Infinite active logging can be used to support environments with large jobs that require more log space than you would normally allocate to the primary logs.

Be aware that if you activate this feature, rollbacks may take a long time if the log files containing the rollback information need to be retrieved by the User Exit. We strongly recommend that you use this feature only if you are using direct archiving. If you use indirect archiving, it is possible that the User Exit cannot retrieve required log files for a rollback, because they have already been archived and deleted from log_archive by brarchive. In addition, you should set the new database configuration parameters MAX_LOG and NUM_LOG_SPAN to avoid that transactions run indefinitely. •

Block Transactions when Log Directory is Full The block on log disk full function, which was introduced in DB2 Version 7, is now set using database configuration parameter blk_log_dsk_ful.

•

Increased Log Space The maximum amount of log space, which can be defined, has increased from 32 GB to 256 GB.

Backup and Recovery Enhancements •

XBSA Support Backup can now interface with solutions from storage vendors that have implemented the XBSA industry standard interface.

•

Point in Time Rollforward Recovery to Local Time When using point in time (PIT) rollforward recovery, you can now specify the time as local time. This makes it easier to roll forward to a specific point in time and eliminates potential errors due to the translation of local to GMT time.

August 2003

19

1 Introduction

•

Database Maintenance Mode: QUIESCE Using the new QUIESCE command, you can force all users off an instance or a database and put it into a quiesced mode for database maintenance activities. The quiesce mode of the database is ideal to create an offline backup without the need to stop the SAP application servers.

Availability Enhancements •

Online Table Reorganization Online table reorganization allows applications to access the table during the reorganization. In addition, online table reorganization can be paused and resumed later by anyone with the appropriate authorization by using the schema and table name. Online table reorganization is allowed only on tables with type-2 indexes and without extended indexes.

•

Online Index Reorganization A table and its existing indexes can be read and updated during an index reorganization using the new REORG INDEXES command.

•

Online Configuration Parameters Over 50 configuration parameters can now be set online. Changes to these online configuration parameters take immediate effect without the need to stop and start the instance, or deactivate and activate the database. You no longer have to disconnect users when you fine tune your system, which gives you more flexibility to decide when to change the configuration.

•

DMS Container Operations If you use database-managed tablespaces, DB2 Version 8 will allow you to drop a container from a tablespace, reduce the size of existing containers, and add new containers to a tablespace in a way that a rebalance does not occur.

Usability Enhancements DB2 Control Center and 64-bit Instances The DB2 Control Center can now be used to administer 32-bit and 64-bit instances.

20

August 2003

2 Basic Concepts

2 Basic Concepts 2.1 Introduction This chapter describes the concepts relating to the Admin Tools, and the terminology used throughout this documentation. Much of this terminology is described in Glossary and Index [Page 214].

This documentation includes new and updated information that was not available in previous versions.

2.1.1 The SAP DB2 Admin Tools The SAP DB2 Admin Tools are a collection of tools specific to the SAP environment, which supplement standard DB2 tools. They can be divided into the following categories: •

Log file management tools

•

Database backup and restore support tools

•

Performance related tools

In addition to the tools themselves, a graphical interface, the DB2 Control Center Extensions, has been created that simplifies access to several of the Admin Tools. These are extra menu options and windows that are available in the DB2 Control Center after the extensions are installed.

August 2003

21

2 Basic Concepts

SAP System

SAP database

Monit o + Exe r cute

+

SAP processes

M o Ex nit ec or ut e Admin Tools

Proto c + Qu o l ery

cute Ex e DB2 Control Center + SAP Extensions

Configure + Query

Admin database ADM

The following graphic shows the relationship between the Admin Tools and the DB2 Control Center Extensions plus the SAP system and database.

2.1.2 User Interfaces System administrators can perform the DBA tasks using the following interfaces: •

DB2 Command Line Processor (CLP) DB2 tool used to execute database utilities, SQL statements and get online help. It offers a variety of command options, and can be started in interactive input mode, command mode and batch mode. For more information, see the DB2 Command Reference.

•

DB2 Control Center (DB2CC) DB2 tool offering a graphical user interface that simplifies administrative tasks such as configuring the database, backup and recovery, scheduling jobs, and managing media. To start the DB2 Control Center, use the db2cc command on the command line, or use Start panels on Windows platforms. The DB2 Control Center can be extended with the SAP Admin Tools Control Center extensions to support SAP-specific tasks related to log file management. You can also access tools such as DB2 Visual Explain and DB2 Performance Monitor from the DB2 Control Center. For introductory information, choose Help → Getting Started.

22

August 2003

2 Basic Concepts

•

Operating System Command Line This can be used to perform the majority of tasks described in this documentation. Generally this is the fastest and most flexible method, but may require user knowledge of complex command syntax. The system administrator can also use this interface to automate tasks, for example, by writing scripts or batch files.

•

SAP Transactions These provide an additional graphical interface for starting specific tasks. The number of available tasks is limited, but they are often neither platform nor database-specific and can therefore be used in a mixed environment.

2.2 SAP Log File Management This section explains why log file management is an essential part of system administration, and how the Admin Tools assist in this task. It covers the following topics: •

Recovery − The Need for Log Files [Page 23]

•

Recovering Log Files [Page 25]

•

Log File Life Cycle (Archive and Restore) [Page 27]

•

Log File Deletion [Page 30]

•

Protocols [Page 31]

•

Raw Device Log Files [Page 32]

•

Log File States [Page 32]

•

Special Cases – Non-Standard Behavior of Database Systems [Page 33]

2.2.1 Recovery - The Need for Log Files Once a database is up and running, one of the most essential tasks for a database administrator is the management of database backups and log files to ensure that databases can be recovered after a serious failure. The DB2 Database Manager generates log files to keep track of changes made to the database. These log files are required for the following purposes: •

Explicit and implicit rollback When a database transaction is rolled back all changes are undone.

Explicit rollback An application indicates that changes that it started to make to the database are invalid.

Implicit rollback A program making changes to the database does not commit changes before exiting, either through incorrect programming or a crash.

In both cases the database needs log file information to return the database to the state it was in before the changes were made.

August 2003

23

2 Basic Concepts

•

Database recovery The DB2 Database Manager may be forced to restart without applications being able to close all open transactions. The log files containing the information about changes made by these transactions are required to perform database recovery when the database manager is started again. This involves rolling back (undoing) all open transactions for all applications.

•

Database restore A database can be recreated by restoring a previous backup. A backup represents the state of a database at the time the backup was performed, but does not contain information about transactions completed after this time. Therefore, after a backup image is used to restore a database, log file data generated after the backup was taken must be added to the database again, to bring the database completely up-to-date. This process is known as rollforward recovery. The command to add the log file information is as follows: DB2 ROLLFORWARD.

Restoring from an offline backup image Such a database is immediately usable after the ROLLFORWARD STOP subcommand. Alternatively, log files can be first applied to bring the database up to date (to end of logs).

Restoring from an online backup image In the case of an online backup, the log files generated while the backup operation was running are also required, as transactions may have modified the database contents during the time of the backup. Without this additional data the restored database cannot be activated. The database must be rolled forward to at least the point in time when the backup operation completed to ensure that the data is consistent throughout the database.

Rollback and database recovery do not normally require user intervention. The DB2 Database Manager retains all log files as long as they contain information from open transactions. User intervention is only required if the directory where the database manager stores log files is damaged or lost. This directory is known both as the LOGPATH and log directory. In case of loss or damage, you can recover at least those files that have been successfully archived with the tools described below.

If the log directory is changed and then the subdirectory or any required log files in that subdirectory are removed, DB2 will look for log files in the default log directory (SQLOGDIR) when the database is activated. If the log files are not found, the database will enter a backup pending state. In connection with rollforward operations, DB2 version 7 supports a log overflow directory. The rollforward operation first checks for logs in the location specified by the LOGPATH database configuration parameter. If you have moved any logs from the location specified by the log path parameter, use the ROLLFORWARD overflow directory parameter to specify the path to the logs that you moved. This is especially important in connection with recovery when logging to raw devices.

24

August 2003

2 Basic Concepts

2.2.2 Recovering Log Files The tools db2uext2 (the User Exit), brarchive and brrestore were developed as the log file management component of the Admin Tools. Their main purposes are: •

To archive log files.

•

To assist database administrators performing database rollforward operations after a database restore.

The Admin Tools are automatically installed as part of an SAP system installation with R3SETUP. If you install your SAP system with SAPinst, the Admin Tools are no longer automatically installed. You can manually (re)install, upgrade or patch the Admin Tools using the sddb6ins program. For more information, see Installing the Admin Tools Manually [Page 42]. Rollforward recovery is not enabled by default. Unless otherwise configured, the DB2 Database Manager reuses a fixed number of log files in a circular fashion, for example, 0,1,2,3,0,1,2,3,0 and so on. This behavior can be modified so that the database manager uses log files in an ascending order, for example, 0,1,2,3,4,…,9999999. By default, the database manager also does not start any application (User Exit) to independently archive full log files for later recovery. Database administrators are strongly recommended to set the following database parameters in the SAP environment to ON: •

LOGRETAIN Rollforward recovery is enabled by turning on this parameter. That is, circular logging is turned off and each new log file has a higher number.

Each time the LOGRETAIN parameter is turned off and on again, the database manager resets log file numbers and starts from zero again. This can result in several log files having the same log file number on a given node. The log files management tools only directly support recovery of the most recent version of log files. Recovery of older versions must be done manually. •

USEREXIT The User Exit mechanism is enabled by turning on this parameter. The User Exit is automatically started by the DB2 engine each time a log file becomes full.

A backup is required after setting the LOGRETAIN parameter (or both), after which the database can be started again.

To perform online backups, the database must be operated in rollforward recovery mode. To restore the database from an online backup, all log files in use during the online backup operation must be available to complete the restore operation.

August 2003

25

2 Basic Concepts

Once these parameters have been turned on and a backup has been performed, the Admin Tools can be used to archive log files. The User Exit (db2uext2) is then automatically started by the database each time a log file is filled. Depending on how it is configured, the User Exit either archives log files directly to a storage management product, or it copies the log files to the directory referred to as archive directory (as defined by the DB2DB6_ARCHIVE_PATH environment variable). Groups of files in the archive directory can then be archived to tape, TSM or with an individual script using brarchive later. The database can be recovered later using the DB2 commands RESTORE and ROLLFORWARD. Dependent on the configuration, the User Exit either retrieves log files directly from the chosen repository, or from the archive directory and/or retrieve directories. The latter is the directory (defined by the DB2DB6_RETRIEVE_PATH environment variable) to which log files are restored using brrestore. To complete a rollforward operation (other than stop), brrestore and ROLLFORWARD operations may need to be performed repeatedly until all required log files have been retrieved and rolled into the database.

If the database is not rolled forward to the end of all logs when restarted, the Database Manager will create new log files starting with the number immediately following the last log file that was rolled into the database. This can result in several log files having the same log file number on a given node. The log file management tools only directly support recovery of the most recent version of log files. Recovery of older versions must be done manually.

Running the User Exit, brarchive and brrestore The User Exit does not need to be started by a user – it is automatically called by DB2 if configured correctly. Both brarchive and brrestore can be called from within the DB2 Control Center or can be started from the command line. brarchive can also be scheduled to be (regularly) started using the DBA Planning Calendar (SAP transaction DB13), or otherwise automated using scripts or batch files.

26

August 2003

2 Basic Concepts

2.2.3 Log File Life Cycle (Archive and Restore) The following section covers the standard scenarios known as indirect and direct archiving. There is a third scenario, which is a variation of direct archiving, where the SAP User Exit is configured to call a vendor User Exit. The log file cycle is similar to direct archiving with the log files being copied elsewhere but the target and exact behavior depend on the vendor User Exit. The graphic describes the possible life cycle of a log file when using the log file management tools. …/log_archive/ S0000003.LOG (A) LOGPATH DB Parameter S0000004.LOG (B) …/log_dir . User Exit offline retained brarchive S0000005.LOG (D) S0000006.LOG S0000007.LOG brrestore online active User Exit restored retained online retained S0000000.LOG (C) (D) S0000001.LOG DB2DB6_RETRIEVE_PATH …/log_retrieve/ Indirect

Direct LOGPATH DB Parameter …/log_dir . S0000005.LOG S0000006.LOG S0000007.LOG

.

TSM, TAPE or SCRIPT S0000002.LOG archived retained

online active

TSM, DISK or VENDOR S0000000.LOG S0000001.LOG S0000002.LOG S0000003.LOG S0000004.LOG

online retained

archived retained

(A) User Exit (D)

Archive Restore

When the USEREXIT database configuration parameter is set, the database engine automatically starts the User Exit program to archive each log file as soon as it is full or it is closed. The location the file is moved to depends on the configuration of the User Exit. By default, log files are archived indirectly by first copying the file to the archive directory. The User Exit can be reconfigured to archive directly using TSM, archive to disk or via an alternative User Exit.

The Admin Tools can only administer the latest version of a log file with a given number per node, that is, you cannot administer two log files with the number 25 on node 0.

August 2003

27

2 Basic Concepts

Indirect Archiving and Restore (Default) Single log files are copied into the archive directory by the User Exit (A). brarchive is then used to archive groups of offline log files into an archive repository (also known as backend repository) (B). File archiving using the archive directory is referred to as indirect archiving. brrestore retrieves archived log files from the repository if they are no longer in the archive directory, and places them in the retrieve directory (C) where the User Exit can access them. When requested by a ROLLFORWARD operation in connection with a database restore action, the User Exit retrieves log files from the archive or the retrieve directory (D).

Only indirect archiving supports archiving to tape without a vendor storage management product. However, before you decide to use this solution you should take the following disadvantages into consideration: • Log files are not immediately archived to long-term storage. Therefore, log files copied by the User Exit but not yet archived with brarchive can be lost if the disk containing the archive directory fails. • The complexity of the solution, including additional storage directories and an extra Admin DB slightly increases the risk of failure.

Direct Archiving and Restore Direct archiving is the recommended method for log file archiving, as it is the simplest and therefore most reliable method. Log files are archived immediately and directly to long-term storage. However, either TSM or another storage management product with its own User Exit is required. You can use these products not only to archive log files but also to archive backups. The User Exit can be configured to archive directly to TSM, archive to disk or to call a vendor User Exit for an alternative storage product. The latter provides support for any products which provide a User Exit, whilst ensuring that SAP tables containing log file information are also updated. A later ROLLFORWARD operation, after restoring the database, results in the files being automatically restored from the same source. Log files are not renamed during direct archiving or restore.

We recommend that database administrators use direct archiving as it performs well and requires minimal maintenance. ROLLFORWARD operations after a database restore normally require no user interaction when using direct archiving. For more information on how to configure the User Exit and the Admin Tools in general, see Changing the Admin Tools Configuration [Page 64].

Log File Naming Convention The log file names in the graphic above are simplified for readability depending on the way they are archived: •

Direct archiving Files are stored to the backend without their names being modified except if you are using direct archiving to disk. In this case, the node number NODEXXXX is appended to avoid problems in a multi-partitioned environment.

28

August 2003

2 Basic Concepts

•

Indirect archiving When the User Exit copies log files from the log directory to the archive directory, the files are renamed by appending the file-closure timestamp (14 digits) and the node number (NODExxxx). In ROLLFORWARD mode, when the User Exit moves files from the archive or retrieve directory back to the log directory, the original name is restored. Otherwise, the files will not be recognized by DB2. For example, during archiving, the copy of the file S0000001.LOG put in the archive directory might be renamed to S0000001.LOG.20001130140536.NODE0000. A file restored by brrestore (in the LOG RETRIEVE directory) still has the extended file name. The name is first truncated back to its original form in the copy returned to the log directory by the User Exit when it is called to retrieve files for a ROLLFORWARD operation.

DB2 log file names are of the format Snnnnnnn.LOG, where nnnnnnn is a seven digit number ranging from 0000000 to 9999999. When a database is first created, log files starting from S0000000.LOG are created. DB2 restarts the log file numbering sequence at S0000000.LOG under the following conditions: •

When the database configuration file is changed to enable archive logging

•

When the database configuration file is changed to disable archive logging

•

When the log file sequence wraps, that is after log S9999999.LOG is used

The states a log file passes through, which appear in the above graphic, for example, online active, are described in Log File States [Page 32].

Admin Tool Log File Management Model – Indirect Archiving Only Whenever a log file is archived, the Admin Tools also associate it with a database backup timestamp (that is, the date and time of the most recent SAP system database backup operation – whichever was performed most recently). The Admin Tools are then able to tell the database administrator which log files are needed to roll forward a database after a specific backup image has been restored. For more information, see Log File Restore of the SAP Database [Page 138]. In combination with the log file close timestamp (another log file attribute), the backup timestamp allows the Admin Tools to keep track of the restorable backup or log file combinations. The Admin Tools GUI (DB2 Control Center Extensions) also supports this.

Normally, only the Admin Tools should be used to manage log files. The files should not be moved or deleted manually as this will lead to data inconsistencies. The only exception to this is when log files that are no longer supported by the tools (see Invalid in the section Log File States [Page 32]), are restored. Those files must have the timestamp and node extension removed from their names when they are moved back to the log directory.

August 2003

29

2 Basic Concepts

2.2.4 Log File Deletion Log Files on Disk Log files in the DB2 log directory should not be deleted. The database manager automatically deletes or reuses these files after they have been successfully archived by the User Exit. Depending on the archiving method you choose, the following happens: •

If you chose direct archiving, no log file copies are made on disk and no deletion is needed.

•

If you chose indirect archiving, log files copied to the archive directory by the User Exit and later successfully archived using brarchive will only be deleted from the archive directory if this is explicitly requested. The same applies to log files restored to the retrieve directory by brrestore.

In the latter cases deletion is requested by: •

Indicating deletion as part of a brarchive/brrestore operation (options -sd and – ssd).

•

Explicitly deleting log files in a later call to an Admin Tool (options -ds and –dr). Log files no longer required on the backend should also be deleted using these tools.

Be aware that the available disk space will eventually be exhausted if the User Exit is unable to successfully archive log files over a long period of time. The same will happen if log files archived using brarchive and restored using brrestore are not deleted using the same tools. Tapes can be reused, but other backend storage should be cleaned up regularly using the Admin Tools. Storage management products often supply their own tools for doing this or can be configured to automatically remove old files. However, it is essential that you use the Admin Tools to delete files when using indirect archiving, as the Admin DB is then also updated.

Log Files in Long-Term Storage •

Tape If you use a tape as a long-term storage, the Admin DB should always reflect the content of the tapes (in the Admin DB, there is an entry for each log file that is stored on tape). If you reuse a tape that already contains log files, all entries in the Admin DB will automatically be removed before the tape is overwritten with new log files. Therefore the Admin DB does not grow. If you lose a tape or if a tape becomes unreadable, you should delete the tape from the Admin DB to reflect this lost or unreadable tape in the Admin DB. For more information, see Deleting Tapes from the Admin DB [Page 112].

•

TSM If TSM is configured correctly and if you use an archive copy group for storing log files, the log files will automatically be deleted from TSM. If you use indirect archiving, the Admin DB does not recognize the automatic deletion and runs out of sync. To synchronize the Admin DB, you should use brrestore –delete. For more information, see Deleting Log Files [Page 94].

30

August 2003

2 Basic Concepts

If you use a backup copy group, you need to delete the log files manually. If you use indirect archiving, use brrestore –delete. For more information, see Deleting Log Files [Page 94]. If you use direct archiving, you may use db6adutl to delete the log files. •

Customer script If you use a customer script, you should use brrestore –delete to delete log files. For more information, see Deleting Log Files [Page 94].

2.2.5 Protocols The User Exit (db2uext2), brarchive and brrestore generate protocol information to record the actions that have been performed. Protocol information is also written into the Admin DB by the log file management tools. This information can be used by the DB2 Control Center Extensions. The Admin DB protocols and the protocol files of brarchive and brrestore are eventually deleted by brarchive. The frequency can be configured by setting configuration parameter delete_protocols using the DB2 Control Center Extensions.

Protocol files should only be deleted by the database system administrator. The protocol files have the following form and are written to the following directories: Tool

Protocol File(s)

Location

User Exit

db2uext2.log.NODExxxx db2uext2.err.NODExxxx

Directory defined by the DIAGPATH configuration parameter (db2 get dbm cfg …)

(Files are appended) brarchive

brarchive..lfa

•

For 4.6 D Admin Tools: /saparch

•

For 6.NN Admin Tools: /admintools/ protocols

•

For 4.6D Admin Tools /saprest

•

For 6.NN Admin Tools: /admintools/ protocols

(One file per run)

brrestore

brrestore..lfr (One file per run)

These tools can also write more detailed traces, normally only for use by support and development. For more information, see Appendix B: Troubleshooting [Page 171].

August 2003

31

2 Basic Concepts

2.2.6 Raw Device Log Files To achieve maximum performance, we recommend that you use raw devices for storing log data. This has the effect of bypassing a possible file system management overhead and allows DB2 to manage the log data with optimum efficiency. However, it is generally easier to work with log files in file systems, as these can be maintained and manipulated using standard operating system tools. The Admin Tools fully support raw logging. The differences between file system-based logging and raw device logging are hidden by the User Exit. The User Exit extracts raw data into individual files that then behave identically to non-raw log files. If you decide to use raw device logging, the following is necessary to enable rollforward during database recovery. If logging information cannot be obtained from the log extents available on the raw device, DB2 expects the logs to be in the log overflow directory. To allow the User Exit to put missing logs in the log overflow directory, the ROLLFORWARD utility must specify the overflow log path parameter.

2.2.7 Log File States This section is mainly written for system administrators and support personnel only for use if problems involving the Admin DB occur. Within an SAP environment, in order for the tools to know what operations have been performed on a log file, the log file is defined to pass though the following states during its life cycle. This state information is stored in the Admin DB, so it is only relevant to systems using the (default) indirect archiving method. This information is consequently not stored on systems performing direct archiving. •

Online active The log file is currently being used to log transactions. The location of the log file is defined in the LOGPATH database configuration parameter. This can be either a file system path or a raw device.

•

Online retained The log file is no longer being written to by the database, but may contain information about transactions that the database engine has not yet written from the buffer pool to disk. The log file is located in the log directory, and will also be in the archive directory after the User Exit has run.

•

Offline retained The log file is no longer being used and does not contain transactions with unwritten data pages. The log file is in the archive directory.

The original log file will also remain in LOGPATH until the database is stopped or the DB2 Database Manager reuses the file. However, the tools have no influence on this and so assume that the file is no longer there.

32

August 2003

2 Basic Concepts

•

Archived retained The log file has been archived using brarchive. The location of the archived log file is a storage management product, for example, TSM (Tivoli Storage Manager) or tape. If not explicitly deleted, the original log file is also still in the archive directory and no restore is possible (or necessary) as long as this original log file has not been deleted using brrestore.

•

Restored retained The log file has been restored using brrestore. The location of the restored log file is the retrieve directory. If not explicitly deleted by using the -delete option in a subsequent operation, the archived log file is also still available on the backend.

•

INVALID The Admin Tools can only administer the latest version of a log file with a given number per node. If the database generates a second log file with a number already used, the User Exit marks previous versions as INVALID. This can occur after a database restore if the ROLLFORWARD operation does not use all log files, or by turning the LOGRETAIN database configuration parameter off and on again. Older log files that have been archived can still be manually retrieved from the backend using system or storage product tools, but the Admin Tools only support the retrieval of the newest version.

•

Gone This state is only used internally to indicate that a log file could not be found in the location where it was expected, for example, if a customer uses MOVE, RENAME or DELETE. This state can also occur when storage products, such as TSM, automatically delete files after a certain period of time. A delete operation will delete such entries.

2.2.8 Special Cases - Non-Standard Behavior of Database Systems Certain systems are set up in a slightly non-standard way, for example: •

As split-mirror

•

As homogeneous system copy

In both cases the Admin Tools are either restricted or must be configured differently.

Split-Mirror for Backup Purposes DB2 has introduced a new concept known as split-mirror using the db2inidb tool. With this tool, you can rapidly create a mirror copy of a production system database without the need to shut down the system. This copy can then be used to perform an offline backup without affecting the performance of the original system, which continues processing as usual.

For this feature, we strongly recommend customers to only use direct archiving. This requires either TSM or a vendor product that provides its own User Exit. For more information, see The db2inidb Tool [Page 122].

August 2003

33

2 Basic Concepts

Homogenous System Copy You can create new SAP systems from existing system backups. In this case, the Admin Tools must be newly installed on the new system as the system name has changed.

Do not attempt to copy Admin DB information from the source system to the new target system. Although DB2 does not force a backup in this situation, as LOGRETAIN is normally already active, it is essential that you take a new backup before you use the Admin Tools on the new SAP system. Sometimes a system copy is used to overwrite another system or a previous system copy where the Admin Tools were already installed. In this case, the old Admin DB should be dropped before the Admin Tools are reinstalled. Additionally, the backup history file must be pruned to remove all previous backup information before a new backup is taken and before the Admin Tools are used.

2.3 SAP Security Concept General Information In an SAP environment, DB2 runs with authentication set to the value server_encrypt. This means that the user ID and password provided to connect to databases or attach to database instances are verified by DB2 using operating system services from within the database server. The user IDs and passwords for SAP system users are maintained and stored in the file: UNIX: /usr/sap//SYS/global/dscdb6.conf Windows: \\%DSCDB6HOME%\sapmnt\\SYS\global\dscdb6.conf

In an exclusively Windows environment, environment variable DSCDB6HOME contains the name of the database server. In a system environment where the database server operates under an operating system other than Windows, DSCDB6HOME should contain the name of the server where file dscdb6.conf can be accessed with the path listed above. File dscdb6.conf must be accessible from all application and database servers using NFS (UNIX) or Windows shares. Passwords are stored in encrypted form. This file is protected from unauthorized access using file system access authorizations. For all the dscdb6.conf accesses described in this documentation, environment variable DB2DB6EKEY is used to encrypt or decrypt the requested password.

34

August 2003

2 Basic Concepts

The value of DB2DB6EKEY must be identical on all application servers, the central instance, and on the SAP system database servers (all systems with the same ). DB2DB6EKEY is requested and set during SAP system installations. The default value is , but will need modifying for all but the central database server. The administrator can change this value at any time, but this requires that the value be updated on all other related systems, and the passwords in the file dscdb6.conf be changed (see below). UNIX: The DB2DB6EKEY variable is set in the SAP profiles dbenv_.csh and .dbenv_.sh, which are read when adm or db2 logs on. Windows: The DB2DB6EKEY variable is set in the system environment.

Managing Passwords for Database Standard Users DB2 uses operating system users to maintain system security. The Admin Tools need access to the user passwords to access the database. These are stored in encrypted form in a special file and must be updated using the dscdb6up tool.

Do not change user passwords only using operating system methods. Tools that read password file dscdb6.conf will no longer be able to connect to the database until tool dscdb6up has been used. For information on how to set and update passwords for SAP system users, see Setting and Updating Passwords [Page 108].

2.4 Multi-Partitioned Systems The Admin Tools are also enabled to work with multi-partitioned systems. A multi-partitioned database can be configured to consist of more than one database node. In this case, in the SAP implementation, certain directories found in the single-partitioned environment are extended to become node-specific in a multi-partitioned environment and other directories are moved.

On UNIX, the home directory of the db2 user changes from /db2/ (single-partitioned system) to /db2/db2 (multipartitioned system). Also tools such as brarchive and brrestore should be used with the additional parameter –node. For more information on the syntax of brarchive and brrestore, see brarchive - Log File Archive Tool [Page 147] and brrestore - Log File Retrieval Tool [Page 153].

August 2003

35

3 Installation and Setup

3 Installation and Setup 3.1 Introduction This chapter is intended for administrators who want to check their environment, or need to (re)install or upgrade the Admin Tools manually. The installation of the Admin Tools depends on the SAP installation tool. You can install the SAP system using: •

R3SETUP The Admin Tools are automatically installed as part of the SAP system installation. R3SETUP creates the directories, user, groups and user environment that are required to successfully install the Admin Tools using the sddb6ins program.

•

SAPinst The Admin Tools are not automatically installed during the SAP system installation with SAPinst. However, you can install the Admin Tools after installing the SAP system. SAPinst creates the users and groups that are required to successfully install the Admin Tools using the sddb6ins program. Version 6.10 of sddb6ins and higher creates the directories and adds variables to the user environment that are required to run the Admin Tools.

The 6.NN Admin Tools must be used on SAP systems with release 6.10 or higher. They do not perform correctly on SAP systems with release 4.6D or lower, and the 4.6D Admin Tools do not perform correctly on SAP systems with release 6.10 or higher.

Installation Overview The following list provides an overview of the steps that you have to perform to install the Admin Tools and enable the SAP system database for recovery: ...

1. Check the SAP system environment [Page 38] to make sure that the SAP system was installed correctly. 2. Check the prerequisites for the installation of the Admin Tools [Page 43]. 3. Install or upgrade to the latest Admin Tools. For more information, see one of the following sections:

Upgrading the Admin Tools [Page 45] if you want to upgrade the Admin Tools to the latest version.

Installing the Latest Admin Tools [Page 46]if you want to install the latest version of the Admin Tools or apply the latest patch.

4. Check the Admin Tools environment [Page 47] after installation. 5. Install the DB2 Control Center on the SAP database server or one or more workstations. For more information, see Installation of the DB2 Control Center [Page 55]. You may have already performed this step as part of the database installation. Otherwise, this can be delayed until step 7 if rollforward recovery enabling (step 6) will be performed on the command line.

36

August 2003


6. Enable the database for rollforward recovery [Page 52]. 7. Catalog the databases to be administered. For more information, see Setting Up the DB2 Control Center [Page 56]. 8. Check that the DB2 Control Center Extensions are working correctly. For more information, see Using the DB2 Control Center Extensions [Page 63].

See also: •

For information on installing IBM DB2 Universal Database for UNIX and Windows with the SAP system, see the following documentation:

For SAP Web AS 6.10 and higher: SAP Web Application Server Installation on UNIX: IBM DB2 Universal Database for UNIX and Windows SAP Web Application Server Installation on Windows 2000: IBM DB2 Universal Database for UNIX and Windows You can find this documentation in the SAP Service Marketplace at the Internet address service.sap.com/instguides → SAP Web Application Server.

For SAP Basis Release 4.6D: SAP Basis Installation on UNIX: IBM DB2 Universal Database for UNIX and Windows SAP Basis Installation on Windows: IBM DB2 Universal Database for UNIX and Windows To find this documentation, see the SAP Service Marketplace at the Internet address service.sap.com/instguides → SAP Basis.

For SAP R/3 Release 4.6x and lower: To find the corresponding documentation, see the SAP Service Marketplace at the Internet address service.sap.com/instguides → SAP R/3.

•

For additional information on installing the Admin Tools, see the following SAP Notes:

SAP Note 410252 DB6: Installing the latest 4.6D DB2 Admin Tools

SAP Note 455506 DB6: Installing the latest 6.NN DB2 Admin Tools

To find the SAP Notes, see the SAP Service Marketplace at the Internet address service.sap.com/notes.

August 2003

37


3.2 SAP System Environment The following sections explain the SAP system environment that is a prerequisite for the successful installation of the Admin Tools.

3.2.1 SAP System Users and Groups The following tables list the users and groups that are automatically created by the installation tool during the SAP system installation. SAP System Users User

Description

db2

Database administrator This operating system user is the DB2 instance owner and the SAP database administrator.

adm

SAP system administrator This operating system user is authorized to start and stop the SAP system and the DB2 Database Manager (instance). adm has the DB2 authorizations DBADM and SYSCTRL. DB2-specific monitoring functions invoked by SAP application server functions require SYSCTRL authorization. This user belongs to the operating system group dbctl.

sapr3

Database user These operating system users own all SAP database objects (tables, indexes and views). All database connection and instance access operations for an SAP application server are performed using these users.

First installed SAP system with Release 4.6D or lower

sap SAP systems based on SAP Web AS kernel Release 6.10 or higher and additional MCOD 4.6D SAP systems

Windows only: sapse

These users belong to the operating system group SAPSYS and are only created on SAP systems on which the SAP system database has been installed (not on remote application servers). These users have the following database authorizations: CREATETAB, BINDADD, CONNECT, and IMPLICIT_SCHEMA SAP service account user This operating system user is a virtual user. In general, on Windows the SAP system is started with this user account, but there is no need to log on to the SAP system with it. This user account must have the local user authorizations to Log on as a service and has to be a member of the local administrator group. The name of this user must be sapse.

38

August 2003


SAP System Groups Groups

Description

dbadm

Database system administration group Each member of this group has SYSADM authorization for the DB2 Database Manager instance. This is the highest level of authorization within the database manager and controls all database objects.

dbctl

Database system control group Each member of this group has SYSCTRL authorization for the DB2 Database Manager instance. SYSCTRL has authorizations allowing operations affecting system resources, but not allowing direct access to data.

Windows only:

Domain-level SAP system administration group This group is used for grouping the SAP system administrators. The sole function of a global group is to gather users together at domain level so that they can be placed in the appropriate local groups. The members of this group are the domain users adm and sapse.

SAP__GlobalAdmin

The group SAP__GlobalAdmin is only used when the SAP System belongs to a Windows domain. The group SAP__GlobalAdmin is not required for a local installation. Windows only:

Local group on an application server

SAP__LocalAdmin

Only local groups are created and maintained on an application server. A local group can only be given authorizations to the system where it is located. If the system is part of the domain, the local group can contain users and global groups from the domain.

August 2003

39


3.2.2 User Environment In the following table, the Set In column shows the location where the variables are set and where they need to be changed. General Environment Variables Environment Variable

Value

Set In

DB2INSTANCE

DB2

UNIX: ~/.dbenv_ .csh and ~/.dbenv_ .sh Windows: User environment

DB2DBDFT

Same as relevant location for variable DB2INSTANCE as described in this table

DB2DB6EKEY

(default value)

Same as relevant location for variable DB2INSTANCE as described in this table

INSTHOME

UNIX: Home directory of user db2

UNIX: ~/.dbenv_ .csh

Windows: Default value for a singlepartitioned system: :\DB2 Default value for a multipartitioned system: \\%DSCDB6HOME%\db2

and

UNIX: ~/.sapenv_ .csh

SAPSYSTEMNAME

~/.dbenv_ .sh Windows: User environment

and ~/.sapenv_ .sh Windows: User environment

40

August 2003


dbs_db6_schema

Database schema:

Valid for all SAP systems with SAP Web AS 6.10 or higher and MCOD 4.6D SAP systems

sap or sapr3 depending on the SAP system release you have installed.

Same as relevant location for variable SAPSYSTEMNAME as described in this table

Additional Environment Variables for Windows Environment Variable

Value

DSCDB6HOME

Database server name

SAPMNT

:\usr\sap\

SAPEXE

:\usr\sap\\SYS\exe\run

3.2.3 Access Authorizations for Admin Tool-Related Directories Access Authorizations for Directories and Files under UNIX DB2 Directory or File

Access Privilege in Octal Form

Home directory of user

755

Owner

db2

Group

dbadm

db2 (/db2/ or /db2/db2) /db2//log_dir

750

db2

dbadm

/db2//log_retrieve

750

db2

dbadm

/db2//log_archive

750

db2

dbadm

/db2//db2dump

750

db2

dbadm

/usr/sap//SYS/exe

755

adm

sapsys

/usr/sap//SYS/ global

750

adm

dbadm

4750

root

dbctl

adm

dbadm

/usr/sap//SYS/exe/ run/dscdb6up /usr/sap//SYS/ global/dscdb6.conf

640

All authorizations can be checked using the –checkonly option of the sddb6ins program.

August 2003

41


Access Authorizations for Directories and Files under Windows Directory

Access Privileg e

Owner

For User or Group

:\db2

Full Control

Administrator

SAP__LocalAdmin, System

:\db2

Full Control

Administrator

Everyone

:\db2\\ log_dir

Full Control

Administrator

db2, System

:\db2\\ log_retrieve

Full Control

Administrator


:\db2\
Full Control

Administrator


:\db2\\ db2dump

Full Control

Administrator


3.3 Installing the Admin Tools Manually The 4.6D Admin Tools must be used on all SAP systems with release 3.1I − 4.6D where DB2 UDB for UNIX and Windows Version 7 or higher is installed. The Admin Tools are installed on the database server using program sddb6ins. During normal installation of an SAP system with R3SETUP, sddb6ins is called automatically. The administrator only needs to execute sddb6ins manually in the following cases:

42

•

If an SAP system was installed with SAPinst

•

If you want to use the 4.6D Admin Tools and your SAP system is between Release 3.1I and 4.6C

•

After completion of an SAP system upgrade and if you have not yet installed the latest Admin Tools

•

When applying a downward compatible kernel and if you have not yet installed the latest Admin Tools

•

When manually installing the Admin Tools or applying an Admin Tools patch

•

When checking the installation of the Admin Tools

August 2003


3.3.1 Prerequisites for the Admin Tools Installation Before you can install the Admin Tools, you must make sure that certain prerequisites are met. Some are general, some apply to a certain patch level of the sddb6ins program or depend on your operating system.

General Prerequisites •

Sufficient space Make sure that you have sufficient space (approx. 4 MB) in the directory containing the Admin Tools executables. sddb6ins installs the executables in the following directory:

For 6.NN Admin Tools: UNIX: $INSTHOME/admintools/bin Windows: %INSTHOME%\admintools\bin

For 4.6D Admin Tools UNIX: /usr/sap//SYS/exe/run Windows: :\usr\sap\\SYS\exe\run

If you are using indirect archiving (that is parameter -NODADMDB is not used during installation), the Admin DB requires additional space. The directory to which the database manager configuration parameter DFTDBPATH is pointing must be large enough to hold the Admin DB and its log files (approx. 4 MB). •

Availability of the database instance The SAP database instance DB2 must be running when installation program sddb6ins is executed.

•

Correct user environment Program sddb6ins must be executed in the correct user environment. Windows: To start sddb6ins.exe, log on to the database server as user adm.

When working with Microsoft Cluster, sddb6ins.exe must be executed on all cluster nodes. For this, set the environment variable DB2INSTANCE to db2 (instead of db2l) in the local command window. The database resource has to run on the actual cluster node. UNIX To run sddb6ins, you require root authorization with the adm user environment. Log on as adm and then change to user root using su.

Do not use su - to change to user root because the db2 user environment is then lost.

August 2003

43


UNIX only: For SAP Systems lower than 4.6x If you want to install the 4.6D Admin Tools for the first time on SAP systems lower than 4.6x, you have to perform the following additional steps as the group concept changed with Release 4.6A: ...

1. Create two new groups called dbctl and dbadm if they do not already exist. 2. Change the primary group of user db to dbadm and keep group sysadm as the secondary group. 3. Add group dbctl as the secondary group to user adm. 4. Update the database manager configuration. To do so, log on to the database server as user db2 and enter the following commands: db2 update dbm cfg using SYSADM_GROUP dbadm db2 update dbm cfg using SYSCTRL_GROUP dbctl 5. Perform an instance update to correct the file authorizations of executables, for example, db2start: a. Log on to the database server as user db2 and stop the DB2 instance. Enter the following command: db2stop force b. As user root, switch to the instance directory and update the DB2 instance. Enter the following commands: cd /instance ./db2iupdt db2 c. Log on again as user db2 and restart the DB2 instance. Enter the following command: db2start 6. Download the dscdb6up program from the SAP Service Marketplace at the Internet address service.sap.com/patches. 7. To extract dscdb6up using CAR, enter the following command: CAR -xvf dscdb6up.CAR 8. Copy the dscdb6up program to directory /usr/sap//SYS/exe/run. 9. Use chmod and chown to set the authorizations as follows: File /SYS/exe/run/dscdb6up

Permission 4750

Owner

Group

root

dbctl

/SYS/global

750

adm

dbadm

/SYS/global/dscd6.conf

640

adm

dbadm

acts as a place holder for /usr/sap/.

44

August 2003


3.3.2 General Information Return Codes of sddb6ins The sddb6ins program writes information about the steps it performs to standard output. The return code of the sddb6ins program depends on the success of the installation. The following table describes the possible return codes: Return Code

Description

0

No error detected

1

Severe errors detected, the installation is not complete. For more information, see Appendix B: Troubleshooting [Page 171].

2

Warning. Errors have occurred during the installation but the installation is complete. Check the output messages.

The values listed in the table above differ from other Admin Tools.

Abbreviation of sddb6ins Options The following options of the sddb6ins program are not listed in detail in the sections Upgrading the Admin Tools [Page 45], Installing the Latest Admin Tools [Page 46] and Checking the Admin Tool Environment [Page 47]. Instead, {common option} acts as a placeholder for the following three options: •

[-s ]

•

[-db2dbnamepwd ] [-db2dbnamedomain ]

•

[-sidadmpwd

•

[-NOADMDB]

•

[-DEB []]

] [-sidadmdomain

]

For a detailed description of these options, see Appendix A: Tool Command Line Parameters [Page 147].

3.3.3 Upgrading the Admin Tools Use You use the -u option of the sddb6ins program to upgrade the Admin Tools. The following conditions apply: •

If you have performed an upgrade to an SAP system with Release 4.6D or lower but you are still using a version of the Admin Tools from before 4.6D, an upgrade to the latest 4.6D Admin Tools is strongly recommended.

•

If you upgrade to an SAP system Release 6.10 or higher, an upgrade to the 6.NN Admin Tools is required.

August 2003

45


If you are using a SAP system with Release 4.6D or lower and you want to use the 6.NN Admin Tools, Admin Tools upgrades are not supported.

Procedure To upgrade the Admin Tools, enter the following command: sddb6ins -u [] {common options} is a placeholder for the release of the previously installed Admin Tools.

For example, to upgrade the Admin Tools from 4.6D to 6.10 using the 6.10 sddb6ins executable: UNIX: sddb6ins –u 46D Windows: sddb6ins –u 46D –db2dbnamepwd > If the -NOADMDB option is used, the Admin DB will not be installed or will be dropped if it already exists. In this case log file archiving is only performed directly by the User Exit program db2uext2 (for more information, see Archiving Directly to TSM [Page 73]). Otherwise, the Admin DB will be installed (if not already present) and brarchive and brrestore are then also available. See also: Actions Performed by the sddb6ins Program [Page 48]

3.3.4 Installing the Latest Admin Tools Use You use the -i option in the following cases: •

You have to install the latest Admin Tools manually after an SAP system installation of Release 6.10 or higher.

•

You want to apply an Admin Tools patch for the currently installed Admin Tools. However, you must make sure that you are using a patch that is valid for the same release of the currently installed Admin Tools.

Procedure To manually install the latest Admin Tools or to apply an Admin Tools patch, enter the following command: sddb6ins -i [] {common options}

For example, to install the current version of the Admin Tools, enter the following command: UNIX:

46

sddb6ins –i

August 2003


Windows: sddb6ins –i –db2dbnamepwd > If the -NOADMDB option is used, the Admin DB will not be installed or will be dropped if it already exists. In this case log file archiving is only performed directly by the User Exit program db2uext2 (for more information, see Archiving Directly to TSM [Page 73]). Otherwise, the Admin DB will be installed (if not already present) and brarchive and brrestore are then also available.

The -a option of sddb6ins, which was used to install the latest Admin Tools after the installation of a downward-compatible kernel (DCK), no longer exists. Use the -i option instead. See also: Actions Performed by the sddb6ins Program [Page 48]

3.3.5 Checking the Admin Tools Environment Use You use option –checkonly to verify the Admin Tools installation. The verification routine checks if: •

The environments for user db2 and adm are set correctly.

•

The necessary files and directories exist and have the correct authorizations.

•

The DB2 instance variable DB2ENVLIST is set correctly.

•

The database configuration parameters LOGRETAIN and USEREXIT are set to ON for the SAP database .

•

UNIX only: Users and groups are set up correctly.

•

Windows only: The required shares exist.

Procedure To check the environment of the Admin Tools, enter the following command: sddb6ins –checkonly [] {common options}

For example, to check the Admin Tools, enter the following command: UNIX: sddb6ins –checkonly Windows: sddb6ins –checkonly –db2dbnamepwd >

August 2003

47


With the sddb6ins version 6.10 or higher, this verification is also performed automatically at the end of an installation (–i) or upgrade (-u) operation. The output contains useful information about the environment in which the Admin Tools operate. The verification tool writes the following message types: Message Type

Description

[I]

Provides information on the current configuration.

[E]

Severe configuration errors have occurred during the installation.

[W]

Warnings if possible inconsistencies have been detected

3.3.6 Actions Performed by the sddb6ins Program The following section describes the actions performed by the sddb6ins program during the installation (option -i) or upgrade (option -u) of the Admin Tools:

Actions Performed During Installation (-i) The sddb6ins program performs the following actions: •

6.NN Admin Tools only: The following table lists the directories that are created by sddb6ins: Directory

•

Description

•

UNIX: $INSTHOME/admintools

•

Windows: %INSTHOME%\admintools

•

UNIX: $INSTHOME/admintools/bin

•

Windows: %INSTHOME%\admintools\bin

•

UNIX: $INSTHOME/admintools/TraceFiles

•

Windows: %INSTHOME%\admintools\TraceFiles

•

UNIX: $INSTHOME/admintools/protocols

Contains brarchive and brrestore protocol files.

•

Windows: %INSTHOME%\admintools\protocols

User Exit protocol files are in the DIAGPATH directory.

Admin Tools directory, also contains the environment file init.db6.

Contains the Admin Tools executables and libraries.

Contains the trace files created by all Admin Tools.

6.NN Admin Tools on Windows only: In a multi-partitioned system, sddb6ins creates the following shares on the node where hostname matches the contents of the variable DSCDB6HOME:

48

log_archive

log_retrieve

August 2003


•

Extracts the executables stored in sddb6ins to the following directory:

For 6.NN Admin Tools: /admintools/bin

For 4.6D Admin Tools: /usr/sap//SYS/exe/run

•

•

•

Copies the User Exit program db2uext2 to the DB2 directory:

UNIX: $INSTHOME/sqllib/adm

Windows: \sqllib\bin

Copies the stored procedure sddb6jds, which is required for the DB2 Control Center Extensions, to the DB2 directory.

UNIX: $INSTHOME/sqllib/function

Windows: \sqllib\function

6.NN Admin Tools only: Removes executables installed with previous versions, which are no longer required. The Admin Tools up to 4.6D were stored in the same directory as the standard SAP system executables and were therefore deleted during an SAP system upgrade.

•

Creates or updates the environment file init.db6 in the following directory:

For 6.NN Admin Tools: /admintools directory (see table above)

For 4.6D Admin Tools: /usr/sap//SYS/global

For an example of the environment file, see Appendix D: Example of File init.db6 [Page 204]. •

By default, sddb6ins creates the Admin DB ADM if it does not already exist. If the –NOADMDB flag is used and the Admin DB exists, it is dropped. If –NOADMDB is not used, sddb6ins performs the following actions:

Customizes a multi-partitioned system.

Grants the access authorizations to the Admin DB ADM.

If required, it creates the tables in the Admin DB and fills the configuration table with default values if this table is empty.

4.6D Admin Tools only: Creates mirror tables in the database.

•

4.6D Admin Tools only: Creates a user defined function (UDF) entry in the database.

•

6.NN Admin Tools only: Adds Admin Tools-specific environment variables, for example for TSM, to the environment of db2 and adm.

•

Sets the DB2 instance variable DB2ENVLIST, so that the stored procedure works properly.

•

UNIX only: Sets the access authorizations on the Admin Tools directories and files as follows:

August 2003

49


6.NN Admin Tools Directory or File

Access Privileg e in Octal Form

Owner

Group

$INSTHOME/admintools

0755

db2

dbadm

$INSTHOME/admintools/bin

0755

db2

dbadm

$INSTHOME/admintools/protocols

0755

db2

dbadm

$INSTHOME/admintools/TraceFiles

0755

db2

dbadm

$INSTHOME/admintools/init.db6

0644

db2

dbadm

$INSTHOME/admintools/bin/brarchive

6755

db2

dbadm

$INSTHOME/admintools/bin/brrestore

6755

root

dbadm

$INSTHOME/admintools/bin/libdb6tp.[o|so|sl]

0755

db2

dbadm

$INSTHOME/admintools/bin/libdb6ds.[o|so|sl]

0755

db2

dbadm

$INSTHOME/admintools/bin/libdb6sc.[o|so|sl]

0755

db2

dbadm

$INSTHOME/admintools/bin/libdb6sctsm.smp

0755

db2

dbadm

$INSTHOME/admintools/bin/db6adutl

6755

root

dbadm

$INSTHOME/sqllib/adm/db2uext2

0755

db2

dbadm

$INSTHOME/sqllib/function/sddb6jds

0755

db2

dbadm

4.6D Admin Tools Owner

Group

0755

db2

dbadm

/tmp/TraceFiles

0755

db2

dbadm

/usr/sap//SYS/global/ init.db6

0640

db2

dbadm

/brarchive

6755

db2

dbadm

/brrestore

6755

root

dbadm

/libdb6tp.[o|so|sl]

0755

db2

dbadm

/libdb6ds.[o|so|sl]

0755

db2

dbadm

/libdb6sc.[o|so|sl]

0755

db2

dbadm

/db6sctsm.smp

0755

db2

dbadm

/db6adutl

6755

root

dbadm

Directory or File

$INSTHOME/saparch

Access Privileg e in Octal Form

$INSTHOME/saprest

50

August 2003


$INSTHOME/sqllib/adm/db2uext2

0755

db2

dbadm

$INSTHOME/sqllib/function/sddb6jds

0755

db2

dbadm

is a placeholder for /usr/sap//SYS/exe/run. •

Inserts the Admin Tools release into the Admin DB.

•

6.10 Admin Tools only: Checks the installation of the Admin Tools using, for example, the –checkonly option, which is described in Appendix A: Tool Command Line Parameters [Page 147] and Appendix B: Troubleshooting [Page 171].

Actions Performed During Upgrade (-u) The actions performed during upgrade are similar to the actions performed during installation. However, some actions depend on the parameter. The sddb6ins program performs the following additional actions: •

If starting with 3.1I Admin Tools: The 3.1I init.sap configuration file content is moved to the Admin DB.

•

4.0B − 4.6A Admin Tools The log archive location must be changed. Any log files in the old location are moved and the Admin DB is updated.

•

3.1I − 4.6C Admin Tools The executables and libraries are exchanged.

3.3.7 Special Configurations This section provides information on special configurations that apply in the case of multiple SAP systems on one database (MCOD) or if you are using a multi-partitioned database system: •

MCOD

For SAP systems with Release 4.6D: The Admin Tools are only enabled for the main SAP system, that is the firstly installed SAP system. The Admin Tools do not support any additionally installed SAP system .

For SAP systems with Release 6.10 or higher: Call the sddb6ins program for all adm users to enable the Admin Tools environment for all SAP systems that are installed on one database.

•

Multi-partitioned database systems If you are using a multi-partitioned database system, which is the default for an SAP BW installation, and more than one database node is used, you must call sddb6ins on each host where a database node of the database exists.

August 2003

51


•

Multi-partitioned database systems on UNIX only: If you want to use indirect archiving, you have to split the database installation over several nodes (computers). The archive and retrieve directories must be mounted on all nodes using NFS. You must export the archive and retrieve directories on one node and mount these directories on all other nodes using NFS. You need to use the same path on all nodes for the archive and retrieve directory.

3.3.8 Enabling the Database for Rollforward Recovery As outlined in Basic Concepts [Page 21], SAP and IBM recommend that the database system be operated in rollforward recovery mode (LOGRETAIN) with the USEREXIT database parameter turned on. In this mode, the active log files, in which all database changes are recorded, are archived by the User Exit program db2uext2 when they are no longer needed for processing transactions. Log file numbering increases with each new log file (up to 9999999). Rollforward recovery is not enabled by default. You enable rollforward recovery and the User Exit mechanism by setting the database configuration parameters LOGRETAIN and USEREXIT to ON. An offline backup of the database must be performed after setting these parameters. The procedures differ depending whether you are using a single-partitioned or multi-partitioned system.

Unlike with an offline backup, if you want to perform online backups, the database must be in rollforward recovery mode, that is LOGRETAIN must be set to ON.

There are ways of forcing the database out of BACKUP PENDING mode after setting these parameters without performing a backup first. Do not do this, as the Admin Tools will not operate correctly until a backup has been performed. System copies created by restoring a database backup will also not force a new backup if the LOGRETAIN parameter was already ON for the original database. A new backup must be performed before the User Exit is called. See also: Enabling Rollforward Recovery for a Single-Partitioned Database [Page 53] Enabling Rollforward Recovery for a Multi-Partitioned Database [Page 54]

52

August 2003


3.3.8.1 Enabling Rollforward Recovery for a SinglePartitioned Database Updating Database Parameters You can perform these actions either using the command line or the DB2 Control Center.

DB2 Command Line on the Database server ...

1. Log on to the database server as user db2. 2. Windows only: Start the DB2 Command Window. 3. To update the database configuration parameters, enter the following commands: db2 update db cfg for using USEREXIT ON db2 update db cfg for using LOGRETAIN ON 4. Restart the database instance. ...

DB2 Control Center Make sure that you have installed and set up the DB2 Control Center before performing these steps. For more information, see Installation of the DB2 Control Center [Page 55] and Setting Up the DB2 Control Center [Page 56]. 1. Start the DB2 Control Center. 2. From the context menu of the database icon, choose Configure. 3. Choose Logs of the notebook. 4. Choose Retain log files for roll-forward recovery from the list. 5. In the value area, select the Yes radio button. 6. To confirm, choose OK to confirm. 7. Restart the database instance. ...

Backing Up the Database After having updated the database configuration parameters, back up your database. For more information, see Performing the Backup [Page 104].

August 2003

53


3.3.8.2 Enabling Rollforward Recovery for a Multi-Partitioned Database Updating Database Parameters You can perform these actions either using the command line or the DB2 Control Center.

DB2 Command Line on the Database server ...

1. Log on to the database server as user db2. 2. To update the database configuration parameters, enter the following commands: db2_all "db2 update db cfg for using LOGRETAIN ON" db2_all "db2 update db cfg for using USEREXIT ON" 3. Restart the database instance. ..

DB2 Control Center Make sure that you have installed and set up the DB2 Control Center before performing these steps. For more information, see Installation of the DB2 Control Center [Page 55] and Setting Up the DB2 Control Center [Page 56]. 1. Start the DB2 Control Center. 2. From the context menu of the database icon, choose Open → Database Partitions…. 3. In the table Database Partitions, Select all nodes. 4. From the menu that appears, select Configure…. 5. Choose Logs of the notebook. 6. Select Retain log files for roll-forward recovery from the list. 7. In the value area, choose the Yes radio button. 8. To confirm, choose OK. 9. Restart the database instance. ...

Backing Up the Database After having updated the database configuration parameters, back up your database. For more information, see Performing the Backup [Page 104].

54

August 2003


3.4 Installation and Setup of the DB2 Control Center Extensions The Admin Tools have a user interface that is integrated into the DB2 Control Center as a set of extensions. As it is Java-based, it is available on all platforms which support Java. It can be run remotely, such as on a PC. To be able to use this interface, you must install the DB2 Control Center first.

3.4.1 Installation of the DB2 Control Center The DB2 Control Center is part of the DB2 Client Software called the DB2 Administration Client, and also part of the DB2 server installation. To install it, follow the standard DB2 installation process and make sure that you select the option Control Center for installation. On a UNIX based operating system, you can start the DB2 Control Center by executing command db2cc. On Windows, there is also a menu entry in the DB2 group in the Start menu.

The DB2 Control Center of DB2 Version 7 or lower does not support 64-bit instances in general. In order to work with 64-bit systems, the DRDA protocol must be used. This is available with all single- and multi-partitioned systems. On all other systems, install DB2 Connect in addition.

3.4.2 Installing the DB2 Control Center Extensions The DB2 Control Center Extensions can only be installed after the DB2 Control Center has been installed.

If you use the direct archiving mode to archive the log files, the DB2 Control Center Extensions are not required,.

On Windows: ...

1. Log on to the machine where you want to install the Admin Tools as a user with local administration authorizations. 2. Insert the SAP kernel CD. 3. On the CD, change to the directory NT\I386\DBTOOLS. 4. Extract file DB6CCEXT.SAR to a temporary directory by using tool SAPCAR. 5. Change to the temporary directory. 6. Invoke setup.exe and follow the instructions.

August 2003

55


For the DB2 Control Center Extensions to perform correctly, the DB2 variable DB2INSTANCE must have the same value in the system environment and the environment of the user who starts the DB2 Control Center.

On UNIX: ...

1. Log on to the machine where you want to install the DB2 Control Center Extension as user root. 2. Insert the SAP kernel CD. 3. Mount the CD. 4. On the CD, change to the directory UNIX//DBTOOLS. 5. Extract file DB6CCEXT.SAR to a temporary directory by using SAPCAR. 6. Change to the temporary directory. 7. Invoke the shell script install and follow the instructions

3.4.3 Setting Up the DB2 Control Center The DB2 Control Center Extensions are available for SAP databases and instances. If the DB2 Control Center is not running on the same machine as the SAP database, it is necessary to catalog the remote computer, the SAP database and its instance. These tasks have to be performed by an user with administrative authorizations.

Cataloging Databases on 32-Bit DB2 Instances If communication error messages appear while performing actions in this section, the DB2 Administration instance (db2das) may not be running on the computer being cataloged or communication may not be set up correctly. Refer to the DB2 documentation for setting up and starting the DB2 Administration instance and the DB2 Messages Reference.

56

August 2003


Cataloging a Remote Computer: ...

1. In the navigation frame of the DB2 Control Center, choose Systems from the context menu. 2. Choose Add from the menu. The Add System dialog box appears.

3. To retrieve a list of DB2 systems located in the network, choose Refresh. 4. If available, choose the system on which the SAP database server is located. If the system is not listed, enter the name of the server in the System Name field. Fill out or modify the remaining fields. 5. To add this and further systems, choose Apply. Otherwise, confirm with OK.

August 2003

57


Cataloging a Database Manager Instance: ...

1. In the navigation frame of the DB2 Control Center, expand the node with the name of the system where the DB2 server is located. 2. From the context menu of the Instances subnode, choose Add. The Add Instance dialog box appears.

3. To retrieve a list of instances on the chosen system, choose Refresh. 4. If available, select the instance from the list in the Remote Instance field. If the instance is not listed, enter the name of the instance in the Remote Instance field and fill out the remaining fields. The service name should have the value sapdb2 and is defined in file /etc/services on the database server. 5. To add this and further instances, choose Apply. Otherwise, confirm with OK.

58

August 2003


Cataloging the Databases and ADM: ...

1. In the navigation frame of the DB2 Control Center, expand the node, then the instances node, and the node. 2. From the context menu of the Databases node, choose Add. The Add Database dialog box appears.

3. To retrieve the list of databases created on the instance, choose Refresh. 4. If available, choose the SAP database in the Database Name field. If the database is not listed, enter the name of the database in the Database Name field. The other fields are optional. 5. Confirm the entry by choosing Apply. 6. In the same way, select the Admin DB ADM in the Database Name field, or enter the name by overwriting the previous entry. 7. Confirm the entries with OK.

Cataloging Databases on 64-Bit DB2 Instances With DB2 Version 8, cataloging databases on 64-bit DB2 instances is the same process as on 32-bit DB2 instances. The DB2 Control Center of DB2 Version 8 provides the same functionality for both instance types as long as your database is DB2 Version 8.

If you want to access a DB2 Version 7 64-bit instance using DB2 Control Center Version 8, you must configure the DRDA communication protocol to enable communication. Be aware, however, that in this case only limited functionality is available. With DB2 Version 7, the DB2 Control Center does not provide all functions on 64-bit DB2 instances. To use the DB2 Control Center for DB2 Version 7 for the administration of 64-bit DB2 instances, you must use the communication protocol DRDA.

Cataloging Databases to Use Communication Protocol DRDA To be able to use communication protocol DRDA, the SAP database and Admin DB ADM must be cataloged in a special procedure. On Windows, you can use the Client Configuration Assistant or the command line processor. On UNIX, you can only catalog the databases using the command line.

August 2003

59


Using the DB2 Version 7 Client Configuration Assistant ...

1. Log on as a user with administrative authorizations. If the computer is a member of a domain, the user must have domain administrator authorizations. 2. Start the Client Configuration Environment by choosing Start → Programs → IBM DB2 → Client Configuration Assistant. The Client Configuration Assistant dialog box appears. 3. Choose Add. The Add Database Wizard - 1. Source screen appears. 4. Choose Manually configure a connection to a database. 5. To continue, choose Next. The Add Database Wizard - 2. Protocol screen appears. 6. Select TCP/IP. 7. To be able to connect to the server, select The database physically resides on a host or AS/400 system.

If this option does not appear, you have not installed DRDA support on the client. 8. Select Connect directly to the server. 9. To continue, choose Next. The Add Database Wizard - 3. TCP/IP screen appears. 10. In the Host Name field, enter the host name where the SAP database is running. 11. In the Port Number field, enter the port number of the database instance.

You can find the port number in the following file on the database host. The location depends on the host operating system: • Windows: \system32\drivers\etc\services • UNIX: /etc/services Look for the entry sapdb2. 12. To continue, choose Next. The Add Database Wizard - 4. Database screen appears. 13. Enter the name of the SAP database and choose Next twice. The Add Database Wizard - 6. Node Option screen appears. 14. In the System Name field, enter the name of the database host. 15. In the Instance Name field, enter the DB2 instance name.

60

August 2003


16. Choose the corresponding operating system. 17. Choose Finish. The Confirmation dialog box appears. To test the connection, choose Test Connection, enter the user ID and password, and choose OK. 18. To repeat the procedure for the Admin DB ADM, choose Add and repeat steps 13 to 17. Using the DB2 Version 8 Configuration Client Assistant 1. Log on as a user with administrator authorizations. If the computer is a member of a domain, the user must have domain administrator authorizations. 2. Start the client configuration environment by choosing Start → Programs → IBM DB2 → Set-up Tools → Configuration Assistant. The Client Configuration Assistant dialog box appears. 3. Choose Selected → Add Database Using Wizard… The Add Database Wizard – 1. Source screen appears. 4. Select Manually configure a connection to a database. 5. To continue, choose Next. The Add Database Wizard – 2. Protocol screen appears. 6. Select TCP/IP. 7. To be able to connect to the server, select The database physically resides on a host or AS/400 system.

If this option does not appear, you have not installed DRDA support on the client. 8. Select Connect directly to the server. 9. To continue, choose Next. The Add Database Wizard – 3. TCP/IP screen appears. 10. In the Host name field, enter the host name where the SAP database is running. 11. In the Port number field, enter the port number of the database instance.

You can find the port number on the database host in the following file: • UNIX: /etc/services • Windows: \system32\drivers\etc\servic es Look for the entry sapdb2. 12. To continue, choose Next. The Add Database Wizard – 4. Database screen appears. 13. Enter the name of the SAP database and choose Next. The Add Database Wizard – 5. Data Source screen appears.

August 2003

61


14. Deselect Register this database for ODBC and choose Next. The Add Database Wizard – 6. Node Option screen appears. 15. Choose the corresponding operating system. 16. In the Remote instance name field, enter the DB2 instance name and choose Next. The Add Database Wizard – 7. System Options screen appears. 17. To continue, choose Next. The Add Database Wizard – 8. Security Options screen appears. 18. Select User authentication value in server’s DBM configuration. 19. To continue, choose Next. The Add Database Wizard – 8 DCS Options screen appears. 20. Select Configure DCS options. 21. Select Disconnect if client generates an interrupt (INTERRUPT_ENABLED). 22. Choose Finish. The Test Connection dialog box appears. 23. Select CLI and enter the user ID and password. 24. Choose Test Connection. 25. To check for a successful test connection, choose the Results tab. In case of a success, the following message is displayed: CLI connection tested successfully 26. To add the Admin DB ADM, repeat steps 1 to 22. Using the Command Line ...

1. Log on as a user with SYSADM authorization, for example, db2. 2. Enter the following commands in the sequence given (use db2 as ) db2 catalog tcpip node remote server

You can find the port number on the database host in the following file: UNIX: /etc/services Windows: %SystemRoot%\system32\drivers\etc\services Look for the entry sapdb2. db2 catalog db at node authentication dcs db2 catalog dcs db parms ',,INTERRUPT_ENABLED,,,,,' db2 catalog db ADM at node authentication dcs db2 catalog dcs db ADM parms ',,INTERRUPT_ENABLED,,,,,'

62

August 2003


3.4.4 Using the DB2 Control Center Extensions After cataloging the databases, the DB2 Control Center Extensions can now be used. You can access the DB2 Control Center Extensions using new context menu entries, which have been added to the standard DB2 Control Center menus at the instance and database levels. The database context menu contains three entries: SAP Options Management, SAP Log File Management and SAP Tape Management. For more information on how to use the option SAP Option Management, see Parameters Affecting Log File Management Storage [Page 64]. For more information on how to use the option SAP Log File Management, see Advanced Log File Maintenance Tasks [Page 109].

August 2003

63

4 Configuration

4 Configuration 4.1 Changing the Admin Tools Configuration The following sections describes how you can change the configuration of the Admin Tools. There are two locations where parameters may need to be changed: •

In the DB2 Control Center Extensions (only necessary for indirect archiving). For more information, see Parameters Affecting Log File Management Storage [Page 64].

•

In the environment file init.db6 For more information, see Configuring the Admin Tools Using Environment Files [Page 67].

4.1.1 Parameters Affecting Log File Management Storage If you have decided to use the default behavior of archiving log files indirectly via disk, the Admin DB will have been created. The Admin DB keeps track of the location and states of all log files. Furthermore, it contains several configuration parameters for the log file management tools. To view or to change the parameters, proceed as follows: ...

1. In the navigation frame of the DB2 Control Center, choose the database using the context menu.

If you are using the DB2 Control Center Extensions with a patch level lower than 3, you choose the database instance db2 (containing the database) using context menu. 2. Choose SAP Option Management. The Options dialog box appears. The following is an example from an AIX system.

64

August 2003

4 Configuration

When you select a parameter, its description is displayed in the field at the bottom of the dialog box. 3. To change the value of a selected parameter, double-click on the Value field. 4. Enter the new values. The following parameters can be set in the Options dialog box: Parameter

Description

backup_dev_type

Default storage management product, if not given on the command line. Initially set to tape, but can be set to adsm for TSM. Do not set this value to tsm.

cpio_flags

Flags used to modify the cpio command when archiving files to tape. These may need modifying if a non-standard block size is used. For more information, see Indirect Archiving to Tape [Page 75] section Modifying the Tape Block Size.

cpio_in_flags

Flags used to modify the cpio command when restoring files from tape. These may need modifying if a non-standard block size is used. For more information, see Indirect Archiving to Tape [Page 75] section Modifying the Tape Block Size.

August 2003

65

4 Configuration

rewind

Command used to rewind a tape. The $ is replaced by the tape device name. This value should not normally be changed.

rewind_offline

Command used to rewind a tape and then place it offline (some devices eject the tape). The $ is replaced by the tape device name. This value should not normally be changed.

tape_pos_cmd

Command used to a spool a tape forwards. The first $ is replaced by the tape device name. The second $ is replaced by a number indicating the number of files to spool forward by. This value should not be changed.

tape_size

Indicates the default size of tapes. It is only used to suggest how much data might fit on a tape, but may be ignored. M indicates megabytes. This value is used to set the default value for all user-defined tape devices.

tape_address

Non-rewind address of the default tape device. Using this address ensures that the tape is not rewound at the end of each tape operation. For more information on non-rewind addresses, see brrestore - Log File Retrieval Tool [Page 153].

tape_address_rew

Standard address of the default tape device. Using this address results in the tape being rewound at the end of each tape operation.

expir_period

Number of weeks that must pass before a used tape can be re-used without the need to explicitly reinitialize it. The default is value 6.

tape_use_count

Number of times a tape can be re-initialized before warning messages appear requesting that the tape be replaced. The default value is 100. Do not use operating system tools to modify tapes or this information will be lost resulting in tapes being used too often, risking loss of data.

dd_flags

Flags used to modify the dd command when archiving files to tape. These may need modifying if a non-standard block size is used.

dd_in_flags

Flags used to modify the dd command when restoring files from tape. These may need modifying if a non-standard block size is used.

delete_protocols

Number of days to retain tool protocol entries in the Admin DB, before they are deleted. The default value is 30.

adsm_mc

Name of the default TSM management class.

5. To cancel changes, choose Escape. To confirm the changes, choose Enter. A dialog box appears asking for confirmation of the changes. 6. To confirm the changes, choose Yes.

66

August 2003

4 Configuration

4.1.2 Configuring the Admin Tools Using Environment Files The environment files init.db6 and init.db6 are the primary source for configuring the Admin Tools. The redirected restore tool brdb6brt and the CCMS tools (for example, dmdb6rdi, dmdb6rts, dmdb6srp, dmdb6snp and db6clp) can be configured in the init.db6 file. The Admin Tools (for example, db2uext2, brarchive, brrestore, db6adutl, sddb6mir) can be configured in the init.db6 file.

Location of the Environment Files •

The init.db6 file is located in the following directory:

For SAP system releases lower and including 4.6D

UNIX: /usr/sap//SYS/global

Windows: :\usr\sap\\SYS\global

For SAP Web AS 6.10 or higher

UNIX: /admintools

Windows: \admintools

Be aware that for SAP system releases lower and including 4.6D, equals and the global directory is used as the location for the environment files. Thus, all the variables are contained in the same file that is located in the global directory. •

The init.db6 file is located in the following directory:

UNIX: /usr/sap//SYS/global

Windows: :\usr\sap\\SYS\global

Format of File Content The following is a brief description of the format of the file content: •

A # introduces comments; the rest of the line is ignored.

•

Empty lines are ignored.

•

To set a variable, you have to specify a line like = , for example, DB2DB6_TEMP_DIR = c:\temp.

Activation of Variables All recognized variables are listed in the file and are inactive as long as they appear after the comment sign (#). You activate a variable by removing the comment sign (#) and by adapting the value of the variable according to your requirements.

We strongly recommend that you use only these files for configuring the Admin Tools-specific environment variables.

August 2003

67

4 Configuration

Admin Tools Configuration Variables The following table lists the configuration variables of the Admin Tools, which are listed in file init.db6 or init.db6 respectively, and their values.

Variable

Value

INSTHOME

UNIX:

(4.6D Admin Tools only)

Points to the db2 home directory. Windows: Points to :\db2\db.

DB2DB6_ARCHIVE_PATH

Points to the archive directory. UNIX: /db2//log_archive (default value) Windows: Default value for a single-partitioned database: :\db2\log_archive Default value for a multi-partitioned database: \\\log_archive

DB2DB6_RETRIEVE_PATH

Points to the retrieve directory. UNIX: /db2//log_retrieve (default value) Windows: Default value for a single-partitioned database: :\db2\\log_retrieve Default value for a multi-partitioned system: \\\log_retrieve

DB2DB6_TEMP_DIR

UNIX: /tmp (default value) Windows: Value of %TEMP% (default value)

DB2DB6_STANDBY_DIR

This variable is not set by default. If this variable is used, the User Exit creates an additional copy of the log file in the standby directory during the archiving operation. If you want to set up a hot-standby system, you can use this variable For more information, see The db2inidb Tool [Page 122].

DB2DB6_STANDBY_DIR_NOERR

By default, this variable is not set or set to OFF. If the variable is set to ON, operations that fail in the standby directory do not cause the executable, which accesses the standby directory, to abort with an error.

DB2DB6_STANDBY_DIR_ADMDBSAR

By default, this variable is not set or set to OFF. If the variable is set to ON, brarchive creates an additional copy of the Admin DB backup SAR file in standby directory DB2DB6_STANDBY_DIR.

68

August 2003

4 Configuration

DB2DB6_UEXIT_DIRECT

•

For indirect archiving: This variable is not set or set to OFF.

•

For TSM: To activate archiving to TSM, set variable as follows: DB2DB6_UEXIT_DIRECT=TSM:[+]

•

For direct archiving to an alternative storage product, set the variable as follows: DB2DB6_UEXIT_DIRECT=VENDOR. In addition, to activate direct archiving to an alternative storage product, you must configure variable DB2DB6_VENDOR_UEXIT.

•

For archiving directly to disk, set the variable as follows: DB2DB6_UEXIT_DIRECT=DISK.

For more information, see Configuring the System for Archiving Log Files [Page 71]. DB2DB6_VENDOR_UEXIT

By default, this variable is not set. If you have configured DB2DB6_UEXIT_DIRECT=VENDOR, you need to set the variable as follows: DB2DB6_VENDOR_UEXIT=. For more information, see Archiving to an Alternative Storage Management Product [Page 75].

DB2DB6_UEXIT_ARCHIVE_WO_DB

By default, this variable is not set or set to ON. If this variable is set to ON, the User Exit does not update the Admin DB during indirect archiving operations. brarchive updates the Admin DB only during the next archiving operation. For SAP system releases lower and including 4.6D where program sddb6mir is used, sddb6mir updates the Admin DB as well.

DB2DB6_UEXIT_RETRIEVE_WO_DB

By default, this variable is not set or set to OFF. If this variable is set to ON, the User Exit tries to retrieve log files from log_archive or log_retrieve even if the Admin DB does not exist or is not up-to-date.

DB2DB6_CORE

By default, this variable is not set or set to OFF. If this variable is set to ON, the Admin Tools executables write a core file in UNIX instead of trying to handle exceptions internally. We strongly recommend that you do not set this variable to ON unless you are asked to do so by SAP support.

DB2DB6_LIBDB6SC_SCRIPT

By default, this variable is not set. If you want to archive log files using a customer script with brarchive, the value of the variable must be set to the fully qualified path of the customer script. For more information, see Indirect Archiving Using a Customer Script [Page 81].

August 2003

69

4 Configuration

DB2DB6_TSM_MULTITHREADED

By default, this variable is not set or set to OFF. If you want to use the TSM LANFREE feature, you must set this variable to ON. For more information, see Configuring TSM (Tivoli Storage Management) [Page 83].

DB2DB6_TSM_SLIB

By default, this variable is not set or set to OFF. You only have to set this variable if the currently shown shared library version changes in newer versions of TSM. We strongly recommend that you do not set this variable to ON unless you are asked to do so by SAP Support.

DB2DB6_ADMDB_REORG_FREQUENCY

By default, this variable is not set. The default value of seven days applies. By setting this variable, you can specify how often the Admin DB is reorganized :

DB2DB6_TRC_

•

–1 = never

•

7 = default value

By default, this variable is not set or set to OFF. If you set this variable to ON, the tracing for is activated.

DB2DB6_TRC__LEVEL

Values between 1 and 9 are allowed and specify the level of detail: •

1 = less detailed

•

3 = default

•

9 = maximum

DB2DB6_TRC__PATH

Specifies the path where the trace files of format ..trc are created.

DB2DB6_TRC__FILE

Overrides DB2DB6_TRC__PATH. If this variable is used, the same trace is used for all runs of , which is set to the fully qualified path of the trace file.

70

August 2003

4 Configuration

Configuration Variables of Other Tools The following table lists the configuration variables of the redirected restore tool brdb6brt and the CCMS tools, which are contained in file init.db6, and their values. Variable

Default Value

Description

DB2DB6_DMDB6BKP_RETRIES

10

Backup retries in case of offline backups.

DB2DB6_DMDB6BkP_TAPE_PATTERN

\\

Pattern which distinguishes tapes from normal directories

DB2DB6_DMDB6BKP_BLOCK_SIZE

1024

Backup block size for tapes

DB2DB6_DBDM6SRP_UPDATE_THRESHOLD

15

Updates threshold in percent to flag tables for RUNSTATS.

DB2DB6_DMDB6SRP_TABLESIZE_THRESHOLD

1000

Minimum table size in rows to flag tables for RUNSTATS.

(Windows only)

See also: Appendix D: Example of File init.db6 [Page 204].

4.2 Configuring the System for Archiving Log Files As outlined in Basic Concepts [Page 21], log files can be archived directly or indirectly.

Direct Archiving The User Exit archives log files either directly to disk (DB2DB6_ARCHIVE_PATH), to TSM or to another vendor storage management product, that is log files are immediately copied from the system to a safe backend and do no longer reside on the local system. The User Exit program can be configured to support one of these options. Archiving log files directly to a storage vendor product means that the User Exit program uses the indicated storage management product to manage DB2 log files. In case of TSM the User Exit utilizes the TSM APIs to archive and retrieve log files. All other storage products (for example, Legato Networker from Legato System, Inc.) deliver their own implementation of the User Exit program.

August 2003

71

4 Configuration

Direct archiving offers the following advantages: •

System configuration is less complex as no Admin DB or DB2 Control Center Extensions are required.

•

Backups can also be archived with the storage management product.

•

Tape access and management using storage management systems are easy to handle and no tape space is wasted.

•

No extra space for an archive directory is needed when archiving to TSM or any other storage management product. You only need extra space if you archive directly to disk.

We recommend that database administrators use direct archiving as it performs well and requires minimal maintenance. ROLLFORWARD operations after a database restore normally require no user interaction when using direct archiving. However, when using direct archiving you have to take into considerations that some constraints apply. During a rollforward recovery of the database the storage management system must be available. If it is not available, problems will arise. You can avoid this situation by setting variable DB2DB6_STANDBY_DIR to keep local copies of log files until they are no longer required on the local system. Furthermore, an overflow of the log_directory can occur due to network outage or unavailability of the storage management system.

You can avoid an overflow of the log directory by increasing the space of the log directory using the space you saved by not needing an archive directory. In addition, setting DB2 registry variable DB2_BLOCK_ON_LOG_DISK_FULL=ON (DB2 Version 7.2) or the corresponding database configuration variable parameter BLK_LOG_DSK_FULL=YES (DB2 Version 8) also reduces the risk of database failure. You also may consider to use infinite logging.

Indirect Archiving This is the default configuration. The User Exit automatically copies log files to the directory specified in the DB2DB6_ARCHIVE_PATH variable. Then brarchive is used to archive the copied files. In this case, you can archive log files to TSM, tape or using a customer script. Indirect archiving offers the following advantages: •

Log files are stored on the local system until deleted by brarchive. In case your storage management system is unavailable, you are still able to restore and rollforward the database as long as all log files and backups are still available on the local system.

72

•

Simple tape support is provided by the Admin Tools.

•

Option brrestore –delete allows deletion of log files that were created before a specific timestamp. With direct archiving this function is not available.

•

The time of the archiving operation can freely be chosen. Thus, network traffic can be avoided during the day by archiving during the night.

August 2003

4 Configuration

However, when using indirect archiving, you have to take into consideration that there are a few disadvantages. For example, log files located in the archive directory are unsafe until stored to a safe backend by brarchive. The complexity of the solution, including additional storage directories and an extra Admin DB, slightly increases the risk of failure. Especially the existence of the Admin DB can cause problems in split-mirror scenarios and when performing a homogeneous system copy. See also: For detailed information on direct and indirect archiving, see the following sections: •

Archiving Directly to TSM [Page 73]

•

Archiving Directly to Disk [Page 74]

•

Archiving to an Alternative Storage Management Product [Page 75]

•

Indirect Archiving to Tape [Page 75]

•

Indirect Archiving to TSM [Page 81]

•

Indirect Archiving Using a Customer Script [Page 81]

4.2.1 Archiving Directly to TSM In order to use the Tivoli Storage Manager to manage DB2 log files and/or DB2 backups, the TSM client software must be installed on the DB2 database server and a TSM server must be available. For more information on how to set up the TSM client, see Configuring TSM (Tivoli Storage Management) [Page 83]. To indicate direct archiving to TSM, set the DB2DB6_UEXIT_DIRECT variable in the environment file init.db6 (make sure that it is not commented out). The following syntax is used to set variable DB2DB6_UEXIT_DIRECT: DB2DB6_UEXIT_DIRECT = TSM:[]

To use the TSM management class MLOG1: DB2DB6_UEXIT_DIRECT = TSM:MLOG1 If you do not want to create and use a specific TSM management class, specify either of the following: • DB2DB6_UEXIT_DIRECT = TSM • DB2DB6_UEXIT_DIRECT = TSM:DEFAULT

To switch the User Exit program to direct archiving of log files, either: • Call the installation program sddb6ins with the parameters: -i [] -NOADMDB The Admin DB is automatically removed. • Drop the ADM database manually. In both cases the necessary environment variable(s) must be set to indicate to the User Exit program that direct archiving to the data storage is required, for example, DB2DB6_UEXIT_DIRECT.

August 2003

73

4 Configuration

The following naming conventions on the TSM server apply: TSM Variable

Meaning

/

TSM file space name

DB2

TSM file space type

/NODEyyyy

High level file identifier, where yyyy is the node number

/Szzzzzzz.LOG

Low level file name, where zzzzzzz is the log file number

For the database BIN on node NODE0000, archiving log file S0000599.LOG would result in the following values: •

File space name: /BIN

•

File space type: DB2

•

Object name file space: /BIN

•

Object high-level name: /NODE0000

•

Object low-level name: /S0000599.LOG

For storing the log files, the archive copy group is used by default. If no archive copy group is defined, a backup copy group is used. We recommend that you define an archive copy group.

Archiving to Two TSM Management Classes For duplicated archiving of log files to TSM, you have to create two management classes in TSM. To do so, you specify both management classes, for example, MC1 and MC2, in the User Exit variable DB2DB6_UEXIT_DIRECT.

DB2DB6_UEXIT_DIRECT = TSM:MC1+MC2

Archiving Using TSM Option LANFREE To make sure that the Admin Tools work in a TSM LANFREE environment, you have to activate variable DB2DB6_TSM_MULTITHREADED in file init.db6.

4.2.2 Archiving Directly to Disk Since Admin Tools patch 23 (6.NN Admin Tools) or patch 12 (4.6D Admin Tools), the User exit can directly archive or retrieve log files to or from disk. To define the archive directory, you have to configure environment variable DB2DB6_ARCHIVE_PATH. During the archiving operation the User Exit appends the partition number to the log file to avoid that log files from other DB2 partitions will be overwritten. During the retrieve operation the log file is transferred back to the log directory and the partition number is removed automatically.

74

August 2003

4 Configuration

The log files in the archive directory need to be manually archived to disk using tools of your choice.

Be aware that you cannot use brarchive or brrestore to archive or restore these log files.

4.2.3 Archiving to an Alternative Storage Management Product In order to choose an alternative storage manager product to manage DB2 log files or DB2 backups, the specific client software must be installed and configured on the DB2 database server. The Admin Tools User Exit must also be installed.

Make sure that the vendor User Exit program is renamed and does not overwrite the User Exit program delivered by SAP. Set the DB2DB6_UEXIT_DIRECT and DB2DB6_VENDOR_UEXIT variables in the environment file init.db6 using the following syntax: DB2DB6_UEXIT_DIRECT = VENDOR DB2 DB6_VENDOR_UEXIT = If the vendor User Exit program is not in the path of the DB2 instance owner db2, specify the program with the fully qualified path. For setup and error handling relating to the vendor User Exit program, refer to the vendor’s documentation.

4.2.4 Indirect Archiving to Tape You can only archive log files to tape in indirect archiving mode. For this purpose, the Admin Tools include a tape management facility. All tapes and tape devices used for log file archiving must be registered here. Each tape and tape device is assigned to a tape device class. The basic steps in tape management include: •

Defining device classes.

•

Defining devices.

•

Initializing (naming) tapes.

August 2003

75

4 Configuration

Initially, a default device class is defined. This device class initially contains one defined device: Windows:

/dev/mt0

AIX:

/dev/rmt0

Solaris:

/dev/rmt/0

Linux:

/dev/st0

HP-UX:

/dev/rmt/0m

We recommend that you update or replace default entries according to your requirements. To define device classes, devices and tapes, read the following sections. If you have more than one tape device, replace the 0 with 1, 2 and so on to address them. For example, the second tape on Solaris is /dev/rmt/1.

Working with Tape Device Classes ...

1. In the navigation frame of the DB2 Control Center, expand the node until the database names are visible. 2. From the context menu of the SAP database icon, choose SAP Tape Management. 3. If required, log on as user db2. The Tape Management dialog box appears.

4. Choose Tapes. 5. To display a list of all known device classes, expand the node in the tree.

76

August 2003

4 Configuration

6. You can choose from the following actions:

To add a new device class, choose Add Device Class from the context menu of the node.

To change an existing device class definition, choose Edit from the context menu of the node.

To remove a device class definition, choose Delete from the context menu of the node.

All devices and tapes of that class will also be removed by the delete action. For the first two actions, the Add/Change Device Class dialog box appears.

7. Enter or change the fields as required. The fields are described in detail in the table following this procedure. 8. Choose Add or Change depending whether you want to add a new device class or change existing data. The fields in the Add/Change Device Class dialog box describe a tape device class. Most fields have a corresponding configuration parameter as described in the table under Parameters Affecting Log File Management Storage [Page 64].

Field

Description

Name

Name of the device class. Required but can be freely chosen.

Capacity

Overrides the tape_size configuration parameter.

Type

Describes the device type you can choose from the menu.

Description

Optional field that describes the device class.

Rewind

Overrides the rewind configuration parameter.

August 2003

77

4 Configuration

Rewind Offline

Overrides the rewind_offline configuration parameter.

Eject

Identical to Rewind offline. Set this field to the same value.

Positioning

Overrides the tape_pos_cmd configuration parameter.

Working with Tape Devices ...

1. In the navigation frame of the DB2 Control Center, choose SAP Tape Management from the context menu of the SAP database . 2. If required, log on as user db2. 3. Expand the node, and the node to which the device definition will be added. 4. Expand the Devices node. A list of known device classes is displayed. 5. You can choose from the following actions:

To add a new tape, choose Add Device… from the context menu of the node.

To change an existing Device definition, choose Edit from the context menu of the node.

To retrieve the label of a tape currently located in a tape drive, choose Retrieve Tape Label from the context menu of the node.

To remove a device definition, choose Delete from the context menu of the node.

For the first two actions the Add/Change Device dialog box appears.

6. Enter or change the fields as required. Description is optional. 7. Choose Add or Change depending on whether you want to add a new tape or change existing data.

78

August 2003

4 Configuration

Modifying the Tape Block Size To improve performance, it is sometimes necessary to modify the block size used to read and write tapes. The flags supplied to cpio and dd will need modifying if a non-standard block size is used. For cpio, the option ‘B‘ must be replaced with ‘C‘ followed by the block size divided by 512. For dd, the bs value must be changed to the block size in bytes.

Due to a limitation in the Admin DB design, such changes apply to all tape classes and tape devices.

Initializing Tapes Tapes must be initialized before files can be archived to them. This involves writing a header file to each tape, containing information such as volume name, SAP system ID and tape usage count. Only a single archive operation involving one or more log files is permitted on an initialized tape. Tapes must normally then be re-initialized before new log files can be archived to them. As described in Changing the Admin Tools Configuration [Page 64], tapes can be re-used if the log files go out of date as defined by the expir_period configuration parameter.

Initializing Tapes Using the DB2 Control Center ...

1. In the navigation frame of the DB2 Control Center, choose SAP Tape Management from the context menu of the SAP database . 2. If required, log on as user db2. 3. Expand the node, and the node to which the tape to be initialized belongs. The Tape Management dialog box appears.

4. From the context menu of the Tapes node, choose Initialize Tape…. The Initialize Tape dialog box appears 5. Enter the name of the new tape.

August 2003

79

4 Configuration

6. Choose the device where the tape is located.

If the tape has never been initialized before or was used to archive files less than expir_period weeks ago, the initialization is not permitted unless the Force initialization box is selected. You can change the value of the expir_period parameter in the SAP Option Management dialog box. To initialize a tape the first time, the Force initialization option must be selected. 7. To initialize the tape, choose Init.

All tape actions and their results can be seen by choosing Journal tab.

To initialize an already registered tape again, choose Re-initialize ….from the context menu of the tape icon. To remove a tape, choose Delete from the context menu of the tape icon.

Initializing Tapes with brarchive brarchive is the command that the DB2 Control Center uses to initialize tapes. Calling brarchive on the command line gives the administrator access to all command line options and may therefore be more flexible than the DB2 Control Center interface.

The following command initializes a tape with the name D4XA01 without checking whether the tape has already been used or initialized. Messages are only written to a protocol file. To initialize the tape, enter the following command: brarchive -i force -v D4XA01 –t/dev/rmt/0 The complete syntax is explained in Appendix A: Tool Command Line Parameters [Page 147].

80

August 2003

4 Configuration

4.2.5 Indirect Archiving to TSM For indirect archiving to TSM, no special tasks must be performed other than configuring TSM itself. You can find more general information about TSM configuration in Configuring TSM (Tivoli Storage Management) [Page 83]. The following naming conventions apply on the TSM server: TSM Variable

Description

/

TSM file space name

DB2

TSM file space type

/NODEyyyy

High level file identifier, where yyyy is the node number

/Szzzzzzz.LOG.YYYYMMDDHHMMSS.NODEyyyy

Low level file name, where •

zzzzzzz is the log file number

•

YYYYMMDDHHMMSS is the file that was closed by DB2

•

yyyy is the node number

For the database BIN on node NODE0000, archiving the log file S0000599.LOG would result in the following values: • File space name: /BIN • File space type: DB2 • High-level name: /NODE0000 • Low-level name: /S0000599.LOG.20010606103645.NODE0000 For storing the log files the archive copy group is used by default if no archive copy group is defined. Then a backup copy group is used.

We recommend that you define an archive copy group.

Archiving Using the TSM Option LANFREE If you want to use this option, you have to activate variable DB2DB6_TSM_MULTITHREADED in file init.db6 to enable the Admin Tools to work in a TSM-LANFREE environment.

4.2.6 Indirect Archiving Using a Customer Script brarchive und brrestore currently only support archiving to two backends: TSM and tape. By using your own script, you enable the Admin Tools to archive log files to any backend, which means any other storage product or storage device, according to your requirements. The script needs to be able to archive, retrieve or delete log files.

August 2003

81

4 Configuration

What Does the Script Need to Support? If you want to use your own script to archive log files to any other backend than TSM or tape, you have to make sure the script accepts the parameters and return codes provided by brarchive and brrestore. The following table lists the parameters that the script needs to support: Parameter

Description

action

You can set the parameter to perform the following actions:

directory

•

ARCHIVE

•

RETRIEVE

•

DELETE

•

If you are archiving log files, directory points to the directory where the log files to be archived are located.

•

If you are retrieving log files, directory points to the directory where the log files should be retrieved to.

•

If you are deleting log files, directory points to the directory where the log files were originally archived from.

Defines the log file that is to be used. The log file has the following format: Snnnnnnn.LOG.NODExxxx

file

The sequence of the parameters in the script must be the same as in the table. ...

The following table lists the return codes the script must issue for every action performed so that brarchive and brrestore can determine a success or failure: Return Code

Description

LFMVND_STATUS_OK=0

Indicates success of the action performed.

LFMVND_RC_ERROR=64

Indicates failure of the action performed.

LFMVND_COMMAND_NOT_SUPPORTED=71

Indicates that the action specified in the script is not supported.

You must make sure that you specify the correct return codes for every action performed − as described in the table. For example, if the script returns LFM_STATUS_OK (although the action has failed) brarchive and brrestore assume that the action was completed successfully, which is not the case. Thus, the content of the Admin DB becomes inconsistent and you risk to lose log files.

Writing Your Own Script You use the sample script db6sctsm.smp, which is part of the Admin Tools delivery, to write your own script. The shell script is designed for UNIX. If your system is running on Windows, you have to write a batch file or an executable. In the sample script the TSM client dsmc is used to archive, retrieve or delete log files.

82

August 2003

4 Configuration

We strongly recommend that you copy the sample script to avoid overwriting during the next Admin Tool installation. After having copied the script, you can enter your archive, retrieve or delete commands.

Activating the Script You activate the script by setting parameter DB2DB6_LIBDB6SC_SCRIPT= in file init.db6. For more information on how to set parameters in file initdb6, see Configuring the Admin Tools Using Environment Files [Page 67].

4.3 Configuring TSM (Tivoli Storage Management) Use When issuing DB2 BACKUP or RESTORE commands, you can specify Tivoli Storage Manager (TSM product, to manage the database or tablespace backup). You can also use TSM to manage archived DB2 log files.

We recommend that you use at least TSM Version 4.1.

Procedure Before the DB2 Database Manager db2 can use TSM, you have to perform the following steps: ...

1. Install the TSM client and API on the DB2 server. 2. Configure the TSM files dsm.opt (UNIX and Windows) and dsm.sys (UNIX only) as described in Configuring TSM Files dsm.opt and dsm.sys [Page 84]. 3. Configure the TSM environment variables DSM_DIR, DSMI_DIR, DSM_CONFIG, DSMI_CONFIG, DSM_LOG and DSMI_LOG as described in Configuring TSM Environment Variables [Page 85]. 4. To generate the password, run the TSM client dsmc once. 5. To make the environment variable visible to DB2, restart DB2 with db2start. For more detailed information on steps 2 and 3, see SAP Note 82029 ADSM/TSM Installation in DB2 Environment.

August 2003

83

4 Configuration

4.3.1 Configuring TSM Files dsm.opt and dsm.sys On UNIX systems, the TSM files dsm.opt and dsm.sys must be configured. On Windows systems, the TSM file dsm.opt must be configured. The files must contain the following parameters:

UNIX File dsm.opt Parameter

Value

SERVERNAME

File dsm.sys Parameter

Value

SERVERNAME

COMMETHOD

TCP/IP

TCPPORT

1500

TCPSERVERADDRESS

PASSWORDACCESS

Generate

PASSWORDDIR

/etc/adsm

The entry of SERVERNAME in the dsm.opt file must fit the value SERVERNAME in the dsm.sys file.

For parameter PASSWORDDIR: The users root and db2 must be able to access directory /etc/adsm. Make sure that this directory has the appropriate authorizations.

Windows File dsm.opt

84

Parameter

Value

COMMMETHOD

TCP/IP

TCPPORT

1500

NODENAME

TCPSERVERADDRESS

PASSWORDACCESS

Generate

August 2003

4 Configuration

4.3.2 Configuring TSM Environment Variables TSM environment variables DSM_DIR, DSMI_DIR, DSM_CONFIG, DSMI_CONFIG, DSM_LOG and DSMI_LOG are needed to tell TSM where TSM resides and on which TSM server the database backup and the log files should be stored. The following list provides additional information on these variables that you have to take into consideration depending on the Admin Tools version you are using: •

4.6D Admin Tools

UNIX During the installation of the SAP system with R3SETUP the files .dbenv_.csh and .dbenv_.sh are created in the home directory of db2 and adm. These files contain some default values for the TSM variables, which are not valid in most cases and which must be corrected.

Windows The above mentioned variables are not set during installation and must be added manually to the user environment of db2 and adm.

•

6.NN Admin Tools or higher

UNIX The sddb6ins program, which installs the Admin Tools, adds an entry to the profile, .cshrc and .login scripts in the home directories of db2 and adm, which call the dbaenv_.csh and dbaenv_.sh script. The shell script dbaenv_.csh and dbaenv_. sh add the TSM API-specific environment variables DSMI_DIR, DSMI_CONFIG and DSMI_LOG, as well as the TSM client-specific environment variables DSM_DIR, DSM_CONFIG and DSM_LOG. The values, which are set there, work on all standard TSM installations and generally do need not to be corrected.

Windows If the TSM software is installed on the database server before the sddb6ins program is called, sddb6ins detects this and sets the TSM API-specific environment variables DSMI_DIR, DSMI_CONFIG and DSMI_LOG to appropriate values in the user environment of users db2 and adm. If you install the TSM software afterwards, you can rerun sddb6ins with the –i option and sddb6ins will add these variables.

August 2003

85

4 Configuration

Environment Variables for TSM: The following table provides an overview of all environment variables you should check: Variable

Value

Set In

DSMI_DIR

Location of TSM API library with the following default:

Windows:

Windows: :\progra~1\Tivoli\ tsm\api UNIX: For 6.NN Admin Tools: •

dbaenv_.csh

•

dbaenv_.sh

For 4.6D Admin Tools:

DSMI_LOG

•

~/.dbenv_.csh

•

~/.dbenv_.sh

Log directory for TSM API with the following default:

User environment of db2 and adm UNIX: For 6.NN Admin Tools: •

$INSTHOME/admintools/bin/dbaenv_ .csh

•

dbaenv_.sh

For 4.6D Admin Tools: •

~/.dbenv_.csh

•

~/.dbenv_.sh

Same as for variable DSMI_DIR as described in this table.

Windows: :\progra~1\Tivoli \tsm\api UNIX: $INSTHOME/errors DSMI_CONFIG

Location of dsm.opt file with the following default: Windows: :\progra~1\Tivoli \tsm\baclient\dsm.opt

Same as relevant location for variable DSMI_DIR as described in this table

UNIX: $INSTHOME/sqllib/adsm /dsm.opt UNIX only: DSM_DIR

Location of TSM client with the following default: For 6.NN Admin Tools: •

dbaenv_.csh

•

dbaenv_.sh


86

UNIX only: For 6.NN Admin Tools: •

$INSTHOME/admintools/bin/dbaenv_ .csh

•

$INSTHOME/admintools/bin


•

~/.dbenv_.csh

•

~/.dbenv_.csh

•

~/.dbenv_.sh

•

~/.dbenv_.sh

August 2003

4 Configuration

UNIX only:

Log directory for TSM client.

UNIX only:

DSM_LOG

The default is $INSTHOME/errors

Same as for variable DSM_DIR as described in this table

UNIX only:

Location of dsm.opt file for TSM client. The default is:

UNIX only:

DSM_CONFIG

$INSTHOME/sqllib/adsm/ dsm.opt

Same as for variable DSM_DIR as described in this table

4.3.3 Configuration Considerations With TSM, you can use the same or different management classes for archiving backups and log files. The preferable solution, however, is that you use a management class for backups different from the one you use for log files. If you use the same management class, backups and log files might be archived to the same tape. In this case, the following disadvantages have to be taken into consideration: •

If you lose a tape, you not only lose a backup but also the log files. If you only lose a backup, you can use an older backup and still perform a rollforward recovery. With log files missing, this is not possible.

•

The performance during rollforward recovery is limited because log files are widely spread on the tape if stored on the same tape as backups.

•

Sometimes you might be forced to keep offline backups longer than log files. If log files are deleted from tape by TSM, you cannot reuse the whole tape because backups still reside on that tape and space reclamation drastically affects the system performance.

We recommend that for archiving log files you use a disk storage pool. With this pool, you can achieve a better system and rollforward performance if caching on the disk storage pool is switched on. For backups, however, we recommend that you do not use such a disk storage pool. If the DB2 backup image does not fit into this disk storage pool, TSM will fail. An extremely large disk storage pool would be necessary to avoid this problem.

August 2003

87

4 Configuration

4.4 DB2 Configuration Parameters DB2 variables are configured by the SAP installation to ensure that DB2 and the Admin Tools work. This section lists parameters, which influence the Admin Tools directly, with a brief description. If it is necessary to change them, refer to the DB2 documentation. Database Configuration Database Configuration Parameter

Description

LOGRETAIN

Must be set to ON that rollforward recovery is possible.

USEREXIT

Must be set to ON that the User Exit db2uext2 is called.

LOGPATH

Directory where DB2 writes log files to.

LOGFILSIZ

Defines the size of each log file. Default value in an SAP environment is 64 MB.

Database Manager Configuration Database Manager Configuration Parameter

Description

DIAGPATH

Location where DB2 writes the db2diag.log. The User Exit program db2uext2 writes the files db2uext2.NODEyyyy.log and db2uext2.NODEyyyy.err to this directory.

DB2 Instance Registry Variables Instance Registry Variable

Description

DB2ENVLIST

Default value: •

Windows: INSTHOME SAPSYSTEMNAME dbs_db6_schema

•

AIX: LIBPATH INSTHOME SAPSYSTEMNAME dbs_db6_schema

•

Linux: LD_LIBRARY_PATH INSTHOME SAPSYSTEMNAME dbs_db6_schema

•

Solaris: LD_LIBRARY_PATH INSTHOME SAPSYSTEMNAME dbs_db6_schema

•

HP-UX: SHLIB_PATH INSTHOME SAPSYSTEMNAME dbs_db6_schema

Setting these variables ensures that the User Exit program db2uext2 and the stored procedure sddb6jds get the appropriate environment. The sddb6ins installation program sets this value appropriately.

88

August 2003

4 Configuration

4.5 Disk Space Considerations for Running the Admin Tools The following section provides additional information on the directories that are needed if you want to install the Admin Tools. The given information refers to disk space considerations for: •

Log files

•

Protocols

•

Trace files

•

Admin DB

Log Files •

Direct archiving Make sure that the log directory provides sufficient space to avoid problems if your storage management system might be temporarily unavailable.

•

Indirect archiving The free space in the directory where DB2DB6_ARCHIVE_PATH is pointing to, needs to be dimensioned, depending on the log file size and the period of time between two brarchive calls and the amount of log files that accumulate between two brarchive calls. The free space in the directory where DB2DB6_TEMP_DIR is pointing to needs to be dimensioned, so that two exports of the Admin DB fit into it. Otherwise, brarchive fails to store the Admin DB.

Protocols •

User Exit protocols The User Exit writes its protocol files db2uext2.NODExxxx.log and dbuext2.NODExxxx.err to the directory where the database manager configuration parameter DIAGPATH points to.

•

brarchive/brrestore protocols These log files are initially generated in the directory where DB2DB6_TEMP_DIR points to. They are moved to the protocols directory at the end of the brarchive/brrestore run. The directories differ for the Admin Tools version as follows:

For 6.NN Admin Tools: /admintools/protocols

For 4.6D Admin Tools: brarchive: /saparch brrestore: /saprest

These directories need to be dimensioned to hold the small protocol files.

August 2003

89

4 Configuration

Trace Files Trace files are only generated if the user has explicitly enabled trace file generation in the init.db6 file. The trace files are only required to determine defects or by the SAP support to fix problems. The trace file directory can be configured in the init.db6 file for each executable separately. The default directories are: •

For 6.NN Admin Tools: /admintools/TraceFiles

•

For 4.6D Admin Tools: /TraceFiles

Admin DB (Indirect Archiving) The Admin DB is relatively small and its size increases slowly. All methods to keep the Admin DB small are described in Regular Administration [Page 91].

90

August 2003

5 Regular Administration

5 Regular Administration 5.1 DB2 Log File Management The concepts and terminology used in this chapter are described in Basic Concepts [Page 21]. For more detailed information on how to configure the log file management tools, see Configuration [Page 64]. If you are archiving directly, all archive processes will run automatically. You may need to delete old log files from the storage product occasionally. For indirect archiving (the default), the main daily action is archiving log files to tape or TSM after they have been processed by the User Exit. If files are not deleted from the disk during these archive operations, the files remaining on the disk will also need deleting regularly. The other less regular log file management action is deleting log files from the storage product.

5.1.1 Archiving Log Files from Disk to Backend The log files are copied to a temporary disk location by the User Exit, as specified by the environment variable DB2DB6_ARCHIVE_PATH. Log file management information is stored in the Admin DB ADM. The log files written to the DB2DB6_ARCHIVE_PATH location can by archived using the brarchive tool, either to tape, TSM or any other backend using a customer script. You can start brarchive on the command line or using the DB2 Control Center Extensions, or using the DBA Planning Calendar (transaction DB13).

Archiving with DB2 Control Center ...

1. In the navigation frame of the DB2 Control Center, choose your system and log on as a user with administrator authorizations. 2. On the subnode Database, from the context menu of the database , choose SAP Log File Management. 3. If required, log on as user adm or db2. The Log File Management dialog box appears. 4. Choose Archive. 5. In the Archive Log Files of Node field, choose the node from which log files should be archived. 6. In the Log Files to be Archived field, choose the log file up to which you want to archive. All older log files up to the one you have selected will be selected for archiving.

August 2003

91


If you do not want the log files to be deleted from the archive directory after archiving, select Keep Log Files in File System. Select TSM, Tape or Script:

TSM: In the TSM Management Class field, select a TSM management class. The possible entries for the management class are maintained using the SAP Option Management dialog box. The corresponding keyword is adsm_mc. Under Value, a list of management classes is specified, separated by commas. For more information, see Changing the Admin Tools Configuration [Page 64].

Tape: i.

Select Name of First Tape. The list of devices next to this box is then recalculated to only contain the matching devices for that tape.

ii.

Select a device. Alternatively, you can start by choosing a device name and afterwards choose an appropriate tape.

If you want to save to two tapes simultaneously, follow the same procedure for the Name of the Second Tape and its device. If these fields do not contain any entries, log files are saved to a single tape. Once you have selected an entry in one of the four boxes, the entries in the remaining boxes are limited to those entries that match the selection. iii.

To display the full list of possible entries, start the selection process again by choosing Reset Selection. If the tape is not initialized or the device you want to use is not displayed (not defined), go to the SAP Tape Management by choosing Tape. For more information, see Indirect Archiving to Tape [Page 75].

After archiving log files to a tape, the same tape cannot be used to archive additional log files in a later operation until the number of weeks specified in the expir_period configuration parameter has elapsed or the tape is initialized again. For more information, see Parameters Affecting Log File Management Storage [Page 64]. 7. To start the archive process, choose Archive. A dialog box appears indicating that the archiving process was just started. After the archiving has finished, another dialog box appears indicating if it was successful.

You can see all archiving actions and their results in the Journal page.

92

August 2003


Archiving with Command Line Tool brarchive brarchive is the command that the DB2 Control Center uses to archive log files. Calling brarchive from the command line gives the administrator access to all command line options and may therefore be more flexible than the DB2 Control Center interface. The following syntax describes the most common usage of brarchive.

Tapes must be initialized before brarchive can use them. For more information, see Indirect Archiving to Tape [Page 75]. Archiving Log Files to Single Tape Command syntax

brarchive -sd -d tape -v D4XA01 –t /dev/rmt/0 -out n 25

Description

Archives 25 log files to the tape D4XA01, and deletes them from the file system if processing completes successfully. Messages are written to the standard output and a protocol file.

Archiving Log Files to Two Tapes (Double Store) Command syntax

brarchive -ssd -d tape -v D4XA01 D4XA02 –t /dev/rmt/0 /dev/rmt/1 -out -n 10

Description

Archives the same ten log files to both the tapes D4XA01 and D4XA02, and deletes them from the file system if processing completes successfully. Messages are written to the standard output file and a protocol file.

Archiving Log Files to TSM Without Delete Command syntax

brarchive -s -d tsm –out

Description

Archives all available log files to the default management class in TSM, without deleting them from the file system. Messages are written to the standard output file and a protocol file. You might not want to delete log files immediately to allow them to be processed using other tools. These must be deleted later using the –ds option.

Archiving Log Files to Two TSM Management Classes Command syntax

brarchive –ssd –d tsm MC1+MC2 –out

Description

As of 4.6D Admin Tool patch 20 or 6.NN Admin Tool patch 9, you can archive log files to two different management classes. You have to create two management classes, for example, MC1 and MC2, in TSM before you call brarchive.

For more information on the barchive syntax, see brarchive - Log File Archive Tool [Page 147].

August 2003

93


Setting Parameter Default Values in the Admin DB To avoid the necessity to specify all command line options, you can set some values in the SAP Option Management using the DB2 Control Center Extensions. For more information, see Parameters Affecting Log File Management Storage [Page 64]. Setting the values for the following configuration variables will help you to minimize the required command line options: •

backup_dev_type Specify the value that you normally specify using the –d command line option.

•

adsm_mc Specify the TSM management class(es) that you want to use by default.

Archiving Using Customer Script To activate the script support, you have to set variable DB2DB6_LIBDB6SC_SCRIPT= in file init.db6. However, before you call brarchive and brrestore using the command line, you must make sure that option –d script is specified in the script as described in the following example.

•

If you want to archive all log files, you enter the following command: brarchive –s –d script –out • If you want to retrieve up to ten log files, you enter the following command: brrestore –a –d script –n 10 –out • If you want to delete log files for a specified backup timestamp, you enter the following command: brrestore –delete 20020107120000 –d script –out For more information, see Appendix E: Example of Customer Script db6sctsm.smp [Page 208].

5.1.2 Deleting Log Files Log files may need to be deleted regularly depending on how often backups are taken, the number of log files generated each day, and the size of available storage. Log files are stored in the following locations:

94

•

Log directory

•

Archive directory

•

Tape

•

Storage Products

•

Any other backend using a customer script

August 2003


Deleting Log Files from the Log Directory

Normally there is no need to delete files from this directory. Do not delete files that are still needed by the database. Log files will be deleted from the log directory if you: •

Drop a database. This action deletes all log files in the current log path directory. You may need to back up these log files beforehand, for future recovery purposes.

•

Recover the database to a point in time. When recovering the database to a point in time, log files chronologically past the point in time are re-used and therefore lost. For example, if a rollforward operation only needs log data up to the middle of log file 35 and you restart the database, the remainder of log file 35 and the whole of log files 36 and onwards will be re-used. If you need to restore again past the point in time used earlier, you cannot do so since the log files were re-used. Retrieve the original set of log files manually to a different location and copy them back for the restore action. You can identify the original log files easily by their timestamp if they have the following format: Snnnnnnn.LOG..NODExxxx.

To actively delete log files, use the PRUNE LOG FILE command. This DB2 command deletes all log files in the log directory created prior to a nominated log file. It will only do so from the active log path. If any log files have been moved to another path, they will not be considered by this command.

PRUNE LOGFILE PRIOR to S0000100.LOG This command will delete all log files in the active log path up to and including S0000099.LOG.

Deleting Log Files from the Archive Directory Log files are placed here by the User Exit in indirect mode (default). If brarchive is then used with the –sd or –ssd archive actions, the log files are removed again when the files have been successfully stored. If only –s and –ss are used, the delete stored action (-ds) must be run separately to delete these files from the disk.

Deleting Log Files from Tape It is neither possible nor necessary to explicitly delete log files from tapes. As soon as a tape is re-initialized or re-used, the files previously stored on the tape are removed from the Admin DB.

Deleting Unknown TSM files with brrestore The log file management tool brrestore now allows removal of files from TSM that are not known to the Admin DB. This is done by specifying a file pattern, either to match log files (*.LOG and *.LOG.*), or Admin DB safety files (*.SAR). The syntax is as follows: brrestore –delete [file pattern]

August 2003

95


Deleting Log Files from Storage Products You can delete log files from either: •

TSM Only use the Admin Tools to work with files stored indirectly to TSM. To delete old log files created with recent tools, enter the following command: brrestore –delete. For more information on the syntax of brrestore, see brrestore - Log File Retrieval Tool [Page 153]. Files stored directly to TSM or archived indirectly with older tools (for example, for SAP system Release 3.1I and 4.0A) can be deleted using the db6adutl tool or the TSM utility dsmc. In this case, refer to Archiving Directly to TSM [Page 73] for a description of where the files are stored.

•

Other storage management products Files directly archived using an additional User Exit must be managed with tools made available by that product. For more information, see the corresponding product documentation.

Deleting Log Files Archived with a Customer Script You delete log files, which were archived using a customer script, by entering the following command: brrestore –d script –delete. The section DELETE must be implemented in the script. For more information on the syntax of brrestore, see brrestore – Log File Retrieval Tool [Page 153].

5.2 Storage Management Approaching the limit of available storage can degrade overall performance. Running out of storage may cause the database or system to fail. Therefore, one essential task for system administrators is storage management, which consists of tasks such as: •

Checking the free space in file systems.

•

Checking the space available in tablespaces.

•

Checking the size of tables and indexes.

•

Managing (for example, adding or resizing,) these storage elements to deal with changing requirements.

The tasks described in the following sections can be performed either using the DB2 Control Center or the DBA Cockpit.

The information provided for the DBA Cockpit is valid for SAP Web AS 6.10 or higher. For SAP system Release 4.6x, you must make sure that you have installed the DBA Cockpit as described in SAP Note 300828.

96

August 2003


5.2.1 Checking the Space Available in a File System To check the available free space of the log directory and the archive directory, proceed as follows:

From the SAP Performance Monitor You can access the information by calling transaction DB6COCKPIT and choosing Configuration → File Systems in the navigation frame of the DBA Cockpit. The information displayed on this screen helps you to determine how much free space is available in your file systems. For more information, see the SAP Online Documentation in the SAP Library.

From the Operating System: Use the command df on UNIX systems and the program Explorer on Windows systems.

5.2.2 Checking the Space Available in a Tablespace To check the amount of space available in a DMS tablespace, proceed as follows:

From the SAP Performance Monitor You can access information on space for tablespaces by calling transaction DB02 and choosing Space → Tablespaces in the navigation frame of the DBA Cockpit. For more information, see the SAP Online Documentation in the SAP Library.

From the DB2 Control Center ...

1. Open the database tree. 2. Double-click on Tablespaces. In the contents pane a list of all tablespaces is displayed. 3. Scroll to columns Allocated size, Size used and Percentage used to see details related to the amount of space available in a tablespace. Space is measured in pages where one page is 4 KB.

Adding More Space to a Tablespace The capacity for a DMS tablespace is the total size of all containers allocated to this tablespace. When a DMS tablespace reaches the threshold capacity (depending on the usage of the tablespace, 90% is a possible threshold), you should add more space to it. From the SAP Performance Monitor (SAP Web AS 6.20 or higher) You can add more space to tablespaces by calling transaction DB02 and choosing Space → Tablespaces in the navigation frame of the DBA Cockpit and choosing Add.

August 2003

97


From DB2 CLP Using the following SQL commands you can: •

Add a new container to a DMS tablespace. The DB2 database manager will automatically rebalance the tables in the DMS tablespace across all available containers. During rebalancing, data in the tablespace remains accessible. To add a new container, enter the following SQL command: alter tablespace add ( file '' )

•

Increase the size of one or more containers in the DMS tablespace.

Extend one or more existing tablespace container by a specific size. To do so, enter the following SQL command: alter tablespace extend (all containers )

Resize one or more existing tablespace containers to a new size. Container sizes can only be increased. To do so, enter the following SQL command: alter tablespace resize (all containers )

The database manager will automatically rebalance the tables in the DMS tablespace across all available containers if necessary. During rebalancing, data in the tablespace remains accessible. •

DB2 Version 8 Increase the size of a DMS tablespace by adding a new stripe set. To do so, enter the following SQL command: alter tablespace begin new stripe set (file ‘’ ) This does not cause rebalancing. The space that you have added will be available immediately.

For more detailed information on the command syntax, see the IBM documentation DB2 Universal Database SQL Reference.

Reducing the Size of a Tablespace •

DB2 Version 7 You can reduce the size of tablespaces by reorganizing all the tables in the tablespace, backing up the tablespace, and then restoring it using a new physical layout.

•

DB2 Version 8

From the SAP Performance Monitor (SAP Web AS 6.20 or higher) You can reduce the space of a tablespace by calling transaction DB02 and choosing Space → Tablespaces in the navigation frame of the DBA Cockpit and choosing Delete.

From DB2 CLP You can reduce the size of a tablespace using the following SQL command: alter tablespace resize (all containers )

98

August 2003


5.2.3 Checking the Size of Tables and Indexes You can access information on space for tables and indexes by calling transaction DB02 and choosing Space → Tables and Indexes in the navigation frame of the DBA Cockpit. When you call this transaction for the first time, the following check on the system catalog tables is performed: •

Are there tables that do not yet have an entry in the CCMS control table DB6TREORG? (This table is always filled by CCMS programs after REORGCHK.)

•

Are there tables that have entries in the CCMS control table DB6TREORG and that are not up-to-date with the system catalog?

If any tables are found, a dialog box appears listing these tables that require reorganization.

Be aware that running a REORGCHK on the listed tables may take a long time and affect the database performance. To adjust the tables, choose REORGCHK. Otherwise, choose Continue. For more information, see the SAP Online Documentation in the SAP Library.

5.3 Performance Monitoring Performance monitoring consists of the following tasks: •

Monitoring database performance

•

Monitoring SQL statements

•

Updating statistics for database tables and checking for reorganization

•

Monitoring jobs

You can access all monitoring and database administration tasks using the DBA Cockpit. The DBA Cockpit is a navigation frame that holds a tree structure, which is divided at the top level into the main task areas of database administration, for example, performance monitoring.

The information provided for the DBA Cockpit is valid for SAP Web AS 6.10 or higher. For SAP system releases lower and including 4.6x, you must make sure that you have installed the DBA Cockpit as described in SAP Note 300828.

August 2003

99


5.3.1 Monitoring Database Performance DB2 monitors are used to collect detailed resource usage information. The monitor switches can be turned on and off at the instance level (DBM configuration) or at the application level (using the UPDATE MONITOR SWITCHES command).

We recommend that you keep the monitor switches turned on. SAP provides a graphical performance monitor for DB2 to analyze the database activity basis of the DB2 monitor switches.

Checking Buffer Pool Quality Buffer pools are database objects used to cache database data pages in memory. If an object’s data page is placed in a buffer pool, physical I/O access to disks is avoided. Buffer pools can be assigned to cache only data of a particular tablespace. This is assigned within the tablespace definition. Every DB2 database must have a buffer pool. For each new database created, DB2 defines the IBMDEFAULTBP buffer pool. This is the default buffer pool for the database.

The information provided in this paragraph is only valid for SAP system Release 4.6C and higher. You can access information on the buffer pool quality by calling transaction ST04 and choosing Performance →Database → Buffer Pools in the navigation frame of the DBA Cockpit. Buffer pool related information is displayed. The values of Overall Buffer Quality, Data Hit Ratio and Index Hit Ratio should be more the 95 percent. Otherwise, contact SAP remote services Early Watch to improve the values. For more information, see the SAP Online Documentation in the SAP Library.

Checking Locks and Deadlocks DB2 captures information for locks held by applications against a database and records each deadlock event (by means of deadlock event monitors).

The information provided in this paragraph is only valid for SAP system Release 4.6C and higher. You can access information on lock escalation, deadlocks and lock timeouts by calling transaction ST04 and choosing Performance → Database → Buffer Pools in the navigation frame of the DBA Cockpit. The value of Lock Escalations, Deadlocks Detected and Lock Timeouts should be almost zero. Otherwise, contact SAP remote services Early Watch to improve the values. For more information, see the SAP Online Documentation in the SAP Library.

Checking Sort Overflows The DB2 Snapshot Monitor provides cumulative information about the number of heaps used, overflows and the performance of sorts. These snapshots are used in the SAP performance monitor to display sort overflows.

100

August 2003


The information provided in this paragraph is only valid for SAP system Release 4.6C and higher. You can access information on sort overflows by calling transaction ST04 and choosing Performance → Database → Sorts in the navigation frame of the DBA Cockpit. The value of Sort Overflows divided by Total Sorts should result in less than 0,01. Otherwise, contact SAP remote services Early Watch to improve the values. For more information, see the SAP Online Documentation in the SAP Library.

Checking Cache Quality You can access information on catalog cache and package cache by calling transaction ST04 and choosing Performance → Database → Cache in the navigation frame of the DBA Cockpit. The values of Catalog Cache Quality and Package Cache Quality should be more the 95 percent. Otherwise, contact SAP remote services Early Watch to improve the values. For more information, see the SAP Online Documentation in the SAP Library.

5.3.2 Monitoring Dynamic SQL Statements Use The DB2 statement cache stores packages and statistics for frequently used SQL statements. By examining the contents of this cache, you can identify the dynamic SQL statements that are most frequently executed and the queries that consume the most resource. Using this information, you can examine the most commonly executed and most expensive SQL operations, to determine if SQL tuning could result in better database performance.

Procedure You can access information on the dynamic SQL cache by calling transaction ST04 and choosing Performance → SQL Cache in the navigation frame of the DBA Cockpit. You can retrieve the latest cached SQL statement by choosing Refresh. If you retrieve data for the first time, a Selection Criteria dialog box appears where you can limit the result set displayed. Depending on your system, the snapshot can give you a wide range of information, which might lead to a very large result set. If a snapshot already exists, that is, if you had already chosen Refresh at least once, you can display the last snapshot taken by choosing Set Selection Criteria. For more information, see the SAP Online Documentation in the SAP Library.

August 2003

101


Explain Function If a statement is displayed, choose Explain to list the access plan for the statement execution. The Explain function provides a detailed analysis of expensive SQL statements, for example, on how DB2 accesses the data to resolve the SQL statements. To display the access plan of a statement from the SAP performance monitor: ...

1. Follow the procedure as described above. 2. Select a statement. 3. Choose Explain. For more information, see the IBM documentation Administration Guide: Chapter 26, SQL Explain Facility.

5.3.3 Updating Statistics for Database Tables and Checking for Reorganization Regular updates of statistics on the physical characteristics of the database tables and indexes provide the necessary information for the DB2 optimizer to determine the access path to the data. In a partitioned database system, statistics are collected based on the table data that is located on the database partition where the command is executed. Global table statistics for an entire partitioned table are derived by multiplying the values obtained at a database partition by the number of database partitions on the node group over which the table is partitioned. The global statistics information is stored in the catalog tables. To update database tables statistics and to check whether tables need to be reorganized, you can use the jobs Update Statistics + Reorgchk all tables or Update Statistics and Reorgchk (DBSTATC) in the DBA Planning Calendar (transaction DB13). These jobs begin by updating the database statistics (using the DB2 RUNSTATS utility) and then check whether tables need to be reorganized (using the six formulas of the REORGCHK utility as described in the IBM documentation IBM DB2 Universal Database Command Reference). The REORGCHK formulas help to decide if tables and indexes require physical reorganization. These formulas are general recommendations that show the relationship between the allocated space and the space that is being used for the data in tables. Three formulas are used for tables, and three are used for indexes. The results are stored in SAP internal tables.

To reorganize tables we recommend that you use the jobs provided in the DBA Planning Calendar. These jobs automatically update the table and index statistics afterwards.

102

August 2003


5.3.4 Job Monitoring The information provided in this section is valid for SAP Web AS 6.10 and higher. For SAP system Release 4.6x, you must make sure that you have installed the DBA Cockpit as described in SAP Note 300828. You can schedule background jobs to display information on protocol records on all jobs that were executed by the SAP performance monitor by calling transaction DB6COCKPIT and choosing Jobs → DBA Log in the navigation frame of the DBA Cockpit. If you want to display details on a single protocol record, double-click it. For more information, see the SAP Online Documentation in the SAP Library.

5.4 Database Backup Backups are complete copies of the database. You must perform backups on a regular basis in order to be able to restore the database to a consistent state that is as up-to-date as possible. You can perform backups in online or offline mode: •

Online mode Access to the database is not blocked. The users can continue to work normally during the backup.

•

Offline mode The backup process connects to the database in exclusive mode. The database can be restored without log files.

In either case, the database can be restored completely and brought up-to-date by rolling in the log files generated after the backup was taken. To perform both online and offline backups of the database, you can either use the BACKUP DATABASE command, the DB2 Control Center or the DBA Planning Calendar within the SAP system.

5.4.1 Backup Considerations You cannot back up a database that is not in a usable state except for a database in the backup pending or the rollforward pending state. The following is necessary to perform a backup: •

You must have SYSADM, SYSCTRL, or SYSMAINT authorization to use the BACKUP DATABASE command.

•

You must start the database manager (db2start) before taking a database backup. If you want to use the DB2 Control Center, you do not need to explicitly start the DB2 Database Manager.

August 2003

103


•

In a partitioned database system, keep a copy of the db2nodes.cfg file with any backup copies you take. This copy is as a protection against possible damage to this file.

The database may be local or remote. The backup remains on the database server unless a storage management product, such as Tivoli Storage Manager (TSM), is used. You can back up a database to a specified disk, a tape, or a location managed by TSM or another vendor storage management product. After an online backup, DB2 forces the currently active log file to be closed and as a result it will be archived. This ensures that an online backup has a complete set of archived log files available for recovery.

5.4.2 Performing the Backup Backing Up a Single-Partitioned Database You can perform these actions either using the DB2 command line or the DB2 Control Center. DB2 Command Line 1. Log on to the database server as user db2. 2. Enter the following command: db2 backup db …

For the complete syntax of this command check the DB2 documentation. DB2 Control Center

Make sure that you have installed and set up the DB2 Control Center before performing these steps. Fore more information, see Installation of the DB2 Control Center [Page 55] and Setting Up the DB2 Control Center [Page 56]. 1. Start the DB2 Control Center 2. From the context menu of choose Backup → Database… . 3. In the dialog box that appears, enter the required values, including the correct backup target. 4. Choose Backup now. A job will be generated that can be monitored using the DB2 Control Center Journal.

104

August 2003


Backing Up a Multi-Partitioned Database You can perform these actions either using the DB2 command line or the DB2 Control Center. DB2 Command Line 1. Log on to the database server as user db2. 2. Enter the backup command for the catalog node: db2_all “<<+n< db2 backup db …”

For the complete syntax check the DB2 documentation and replace n wit the catalog node number (the default value is 0). 3. To back up all other nodes in parallel, enter the following command: db2_all "<<-n< db2 backup db ... " DB2 Control Center

Make sure that you have installed and set up the DB2 Control Center before performing these steps. Fore more information, see Installation of the DB2 Control Center [Page 55] and Setting Up the DB2 Control Center [Page 56]. ...

1. Start the DB2 Control Center 2. From the context menu of choose Open → Database Partitions…. 3. From the context menu of the node number where the system catalog resides (default: 0), choose Backup…. 4. In the dialog box that appears, enter the required values, including the correct backup target. 5. Choose Backup now. 6. Repeat steps 4 to 7 for all other nodes. A job will be generated that can be monitored using the DB2 Control Center Journal.

5.4.3 Integrity of Backups The db2ckbkp utility allows the user to test the integrity of a backup image on disk or tape and determine whether or not it can be restored. It can also be used to display the meta data stored in the backup header in order to determine information about a particular backup image. One or more parts of an image can be checked. You can use the utility as follows: If the full backup consists of multiple objects, the validation will only succeed if db2ckbkp is used to validate all of the objects at the same time. When checking multiple parts of an image, the first backup image object (.001) must be specified first.

db2ckbkp

August 2003

SAMPLE.0.krodger.NODE0000.CTN0000.19990817150714.*

105


If the backup resides on TSM, refer to the information on db2adutl and db6adutl provided in Managing Backups and Logs Archived on TSM [Page 121]. For more information on the db2ckbkp utility, see db2ckbkp - DB2 Check Backup Utility [Page 158].

5.4.4 Frequency of Backups and Time Required You should take full database backups regularly, regardless of how often log files are archived. A recent full backup means that there are fewer archived log files to apply to the database in case of recovery, which reduces the amount of time required by the ROLLFORWARD utility to recover the database. It also reduces the chance of a log file not being available (corruption or loss). To reduce the amount of time that the database is not available, consider using online backups.

You can only use an online backup for recovery if the database log files that are created during the online backup are available.

5.4.5 Advanced Backup Techniques The following list provides information on advanced backup techniques: •

Incremental or delta backups To reduce the backup and restore time, you can use incremental or delta backups. For more information, see the IBM documentation Data Recovery and High Availability Guide and Reference.

•

Backup by file system copy For information on using tool db2inidb as a mirror for a file system-based backup, see The db2inidb Tool [Page 122].

•

Standby database for backup purposes For information on using tool db2inidb as a standby database for a backup, see The db2inidb Tool [Page 122].

106

August 2003

6 Advanced Tasks

6 Advanced Tasks 6.1 The db6util Tool The db6util tool contains a collection of utility routines that are mainly used during the SAP system upgrade. The following db6util options are also useful for database administration and troubleshooting and can be entered using the command line. To generate a complete list of all db6util options, you can call db6util –h from the command line. The results or messages generated by all db6util commands may be redirected by the command options [ -o ] or [ -w ] .

Tablespace Free Space To generate a free space list for all tablespaces, enter the following command: db6util –f

DB2 RUNSTATS Options •

To perform RUNSTATS on a single table, enter the following command: db6util

•

-r

To perform RUNSTATS on all tables specified in the file, create a file containing a list of tables and enter the following command: db6util –rf

•

To perform RUNSTATS on all tables that were temporarily marked as VOLATILE in the database and to remove the VOLATILE attribute from the tables after RUNSTATS has run, enter the following command: db6util –rv Tables that are marked with an N in the ACTIVE column in table DBSTATC are not affected by this option.

Database Lock Overview db6util helps to analyze database lock wait situations by extracting all involved processes from a snapshot for applications and displaying their dependencies in the form of a syntax diagram. Detailed information about those processes, such as the last SQL statement that was executed or lock types, is displayed. •

To display processes that are only involved in a deadlock situation, enter the following command: db6util –sd

•

To display all processes that are involved in a lock wait situation, enter the following command: db6util –sl

August 2003

107

6 Advanced Tasks

Both commands can be executed with additional parameters to take snapshots periodically. Enter: db6util –sd [sleep time] [number of snapshots] db6util –sl [sleep time] [number of snapshots] For more information on the syntax of db6util, see db6util - Tool to Assist Database Administration [Page 164].

6.2 Setting and Updating Passwords You can set and update the password of the users adm and sap or sapr3 (for systems up to and including 4.6D) using the command line: ...

1. Log on as user adm. 2. Enter the following command: dscdb6up This command updates the contents of the dscdb6.conf file, which must not be modified manually, and the operating system password. On multi-partitioned systems, this must be done on all database nodes.

If you inadvertently delete or destroy file dscdb6.conf, you can recreate it by entering the following command: dscdb6up –create /sapr3 password> <adm password>

The operating system passwords are not changed when you use the -create option.

6.3 Using the Journal in the DB2 Control Center Extensions Most tasks in the DB2 Control Center Extensions are logged in the Admin DB. You can access these logs by choosing Journal. You can display completed tasks, such as archiving or restoring log files, initializing tapes and also display tasks still running. In addition, you can display the logs of the user exit. To access the journal: ...

1. In the Log File Management, Tape Management or Log File Maintenance dialog box, choose Journal. 2. Choose the radio button with the category of actions you want to browse. A list of actions is displayed with start and end time, return code (RC), category and return message. 3. If you want to see an action that took place more than seven days ago, you can use the Back button to go back in steps of seven days.

108

August 2003

6 Advanced Tasks

4. Choose an action from the list. The detailed output of that action is displayed in the Description group box.

To update the list of actions with the most current data, choose Refresh.

6.4 Advanced Log File Management Tasks As outlined in Basic Concepts [Page 21], you can archive log files either automatically using the User Exit or let the User Exit copy them to the archive directory. From this directory you have to archive them as described in Archiving Log Files from Disk to Tape or TSM [Page 91]. If you need archived log files for a rollforward recovery, you have to restore them into the retrieve directory as described in Log File Restore of the SAP Database [Page 138]. The Admin DB ADM keeps track of the current location of the log files. As outlined in Deleting Log Files [Page 94], you can delete log files from the log directory, archive directory, tape, storage management products and any other backend using a customer script.

August 2003

109

6 Advanced Tasks

However, there are two special cases that require advanced log file management skills: •

Log files can have special states that can be maintained in special dialog boxes. These particular log files need to be deleted manually from the Admin DB. For details on how to delete these log files, see Deleting Special Log Files [Page 110].

•

Tapes can become unreadable or can be lost. In this case, you must manually delete the entry for the tape from the Admin DB. For more information, see Deleting Tapes from the Admin DB [Page 112].

6.4.1 Deleting Special Log Files Use You use the following procedure to delete log files that can have a special status. The log files can be maintained in special dialogs. The status of these log files can be as follows: •

If you archive log files and choose at the same time to leave them in the archive directory (DB2DB6_ARCHIVE_PATH), they have two locations assigned.

•

If you retrieve log files, they have two locations assigned − the retrieve directory (DB2DB6_RETRIEVE_PATH) and the archive location (for example, on tape).

•

A log file has the status invalid. This can happen if you restore a database without rolling it forward to the end of the logs. In this case log files with the same sequence number are created again. In the Admin DB the old log files are marked as invalid.

Procedure You can delete (or maintain) these special log files using either the DB2 Control Center Extensions or the DB2 command line. Using the DB2 Control Center Extensions ...

1. In the DB2 Control Center select the SAP database . 2. From the context menu of choose SAP Logfile Management. 3. If required, enter user adm or db2 and the corresponding password. The Log File Management dialog box appears. 4. Choose Log File Maintenance… . The Logfile Maintenance dialog box appears.

110

August 2003

6 Advanced Tasks

5. Choose the node where you want to delete or maintain log files. 6. Choose one of the three radio buttons:

archived Displays log files previously archived and not deleted from the archive directory.

restored Displays log files previously restored.

from database Displays log files known to the Admin DB and which are marked as INVALID.

7. Select a range of log files you want to delete. 8. Choose Delete. Using the DB2 Command Line •

To delete log files from the archive directory that have already been archived but not removed from the archive directory, enter the following command: brarchive –ds –out

•

To delete log files from the retrieve directory that have been restored but not removed from the retrieve directory, enter the following command: brrestore –dr – out

•

To delete log files that are marked as INVALID, enter the following command: brarchive –do -out

August 2003

111

6 Advanced Tasks

6.4.2 Deleting Tapes from the Admin DB Use You use this procedure to delete tapes that have become unreadable or were lost from the Admin DB to keep the content of the Admin DB up-to-date.

Procedure Using the DB2 Control Center Extensions ...

1. In the DB2 Control Center choose the SAP database . 2. From the context menu of choose SAP Tape Management. 3. If required, enter user adm or db2. The Tape Management dialog box appears. 4. In the navigation tree of the Tape Management dialog box select the tape that you want to delete. 5. From the context menu choose Delete. The Attention dialog box appears. 6. Confirm the operation by choosing Yes. Using the DB2 Command Line To delete tapes from the Admin DB, enter the following command: brrestore –dt –v -out

To delete tape PRD01 from the Admin DB, enter the following command: brrestore –dt –v PRD01 -out

6.5 Reorganization of Tables and Tablespaces Reorganization of Tables A table can become fragmented as a result of large numbers of updates, deletes or inserts, causing performance to deteriorate. In general, newly inserted rows cannot be placed in the physical sequence corresponding to the logical sequence defined by an index. This means that the DB2 Database Manager has to perform additional read operations to access the data, because logically sequential data may be on different physical pages that are not sequential. Therefore, you should use the DB2 reorganization utility (REORG) to rearrange the data in the tables and indexes and to remove the free space that is inherent in fragmented data. The result will be faster accessing of data and, consequently, improvements in performance.

112

August 2003

6 Advanced Tasks

The DBA Planning Calendar (transaction DB13) provides the following jobs for the reorganization of tables and tablespaces: •

REORG and RUNSTATS of Flagged Tables You can use this job to reorganize tables, particularly those for which reorganization is recommended. Whether or not it would be beneficial to reorganize a table is calculated by the Update Statistics Job described in Updating Statistics for Database Tables and Checking for Reorganization [Page 102]. This job displays a maximum of 100 tables recommended for reorganization as well as additional data, such as delta table size and the duration of the last reorganization. You choose which of the proposed tables to reorganize. The selected tables are reorganized according to the order of the primary index using the DB6CLP tool. The table statistics are subsequently updated.

•

REORG of Tables in Tablespace(s) Sometimes, you may want to reorganize all the tables of a particular tablespace to remove fragmented data, thus increasing the free space. The DBA Planning Calendar (transaction DB13) provides a utility allowing you to do this. The job uses the external program dmdb6rts to reorganize all tables of a tablespace and to subsequently update the table statistics.

•

Automatic REORG This job can be used to do automatic reorganizations of tables according to REORGCHK recommendations. This job is additionally controlled by user-defined limitations such as, for example, table size or runtime.

•

REORG and RUNSTATS for Single Table Yon can use this job to reorganize one single table

In all jobs you can specify the temporary tablespace where the DB2 Database Manager can temporarily store the table that is being reconstructed. If no temporary tablespace is entered, the DB2 Database Manager stores a working copy of the table in the tablespace in which the table being reorganized resides. This temporary table will be at least as large as the original table, so you must make sure that the tablespace used for reorganization has at least sufficient free space to hold the original table.

Keep in mind that you may be reorganizing a table within a temporary tablespace that is using pages larger than 4 KB (for example, 8 KB, 16 KB or 32 KB). During the reorganization, the temporary tablespace, which is used during the reorganization, must have the same size pages as the base tablespace. For more information, see the SAP Online Documentation in the SAP Library.

August 2003

113

6 Advanced Tasks

6.6 Redirected Restore The redirected restore tool brdb6brt retrieves a database backup and creates a CLP script for the restore of this backup image. Before you run the script, you can modify it, for example, change the name and structure of the database. 2 Create

brdb6brt 1

Retrieve Data & Backup DB

Restore Script

Create 3 Edit 4

Backup Image Redirected Restore 9 for homgeneous system copy

Source DB

DB Copy

Customized Restore Script

User 5 Create

Redirected Restore 8

brdb6brt

Check 7

to change container layout

Create 6

Customized Restore Script

The graphic above illustrates the tasks relevant to a redirected restore process. First of all, back up the database and retrieve data on the container layout (1) using tool brdb6brt. The tool creates a backup of the database (3) and a CLP script for restoring this backup (2). This script corresponds to the container layout of the database at the time of the backup. The script can then be adapted to the needs of the database to be restored (4). This can involve changes to the number, size or location of the containers for a restore to the same database (6 + 8). Another possibility is to perform a homogeneous system copy (9) that normally requires major changes to the script to match the new machine setup (5). Additionally, brdb6brt can serve to check the script (7). It reports any failure the restore process might encounter. In this way, failures can be avoided before they occur.

114

August 2003

6 Advanced Tasks

6.6.1 Usage of Tool brdb6brt You can use the brdb6brt redirected restore tool to: •

Perform simple backups.

•

Retrieve an overview of the container layout.

•

Perform a redirected restore.

•

Change the container layout.

•

Perform a homogeneous system copy.

•

Create a script for restoring certain tablespaces only.

•

Check the restore script.

•

Move existing containers do other directories.

•

Change text during script generation if required.

The following sections provide example commands. For more information on the syntax of brdb6brt, see brdb6brt - Redirected Restore Tool [Page 150].

Performing a Simple Backup You want to make a backup of the entire database to TSM with two sessions. The source database is called SAMPLE. The backup is made online. Enter the following command: brdb6brt –s SAMPLE –bm BACKUP –bpt ADSM 2 –ol

Retrieving an Overview of the Container Layout If you want to have an overview of the layout of the database containers, you can create a restore script only. To do so for database SAMPLE as instance owner db2user with password PASS123, enter the following command: brdb6brt –s SAMPLE –bm RETRIEVE –user db2user –using PASS123 The script contains the current time as the backup timestamp. You can also use this script for a restore operation if you have taken a backup of the database separately. You will have to change the script to the timestamp of the backup image you created separately.

Performing a Redirected Restore After the script has been edited, it has to be executed from the command line. The DB2 Command Line Processor (DB2 CLP) provides an option allowing DB2 to read statements from a file. This is called background mode. Enter the following command: db2 –tvf <script file>

August 2003

115

6 Advanced Tasks

The parameters have the following meaning: Parameter

Meaning

-t

Forces the CLP to use a semicolon (;) as terminating character for an SQL statement. The use of this option is mandatory for the execution of the script.

-v

Forces the CLP to print each statement on the screen.

-f

Forces the CLP to read the statements from the specified file. By default, the brdb6brt tool creates a script file of the name scr, where is the name of the source database.

If a backup and restore script of the database SAMPLE was created, the script should now be executed. Enter the following command: db2 –tvf SAMPLE.scr

Changing the Container Layout You want to change the layout of the containers of your current database. This can comprise changing the number of containers of a tablespace, changing their sizes or their location in the file system. To do so for the database SDB, proceed as follows: ...

1. To create the backup and the restore script, enter the following command: brdb6brt –s SDB –bpt Y:\BACKUPS1 Y:\BACKUPS2 Y:\BACKUPS3 Since the database is rather large, the backup is divided into three pieces. 2. Edit the script SDB.scr and change the container’s location, size and number. 3. To change the container layout, restore the database using the following command: db2 –tvf SDB.scr

Performing a Homogeneous System Copy You want to copy your database to another machine. For this purpose the container locations have to be adapted. To do so for the database SDB, proceed as follows: ...

1. To create the backup and the restore script, enter the following command: brdb6brt –s SDB –bpt Y:\BACKUPS1 Y:\BACKUPS2 Y:\BACKUPS3 Since the database is rather large, the backup is divided into three pieces. 2. Make the backup images and the script available on the target machine. This can be accomplished by copying them to the machine via ftp. 3. Log on to the target machine and edit the script SDB.scr. Change the container’s locations. You also need to adapt the location of the backup image to the directory where the containers are available on the target machine. 4. Restore the database by entering: db2 –tvf SDB.scr

116

August 2003

6 Advanced Tasks

Creating a Script for Restoring Certain Tablespaces Only You want to back up one or more tablespaces rather than of the entire database. The tablespaces for backup have the names USERSPACE1, TBSPACE and TESTSP2. The backup is done to TSM (three sessions). The database name is SAMPLE. The restore script is created to restore only the specified tablespaces. Enter the following command: brdb6brt –s SAMPLE –bpt ADSM 3 –tbs USERSPACE1 TBSPACE TESTSP2

Checking the Restore Script After the script has been edited, the script can be checked whether it would succeed on that machine. The check allows scripts that perform full database or tablespace level restores. Also, restoring to a new or existing database is considered. The user running the check should be the user that will later run the script with DB2 CLP, in most cases the DB2 instance owner (db2). To run a check on the SAMPLE.scr script, enter the following command: brdb6brt –bm CHECK -ip SAMPLE.scr The output from the check run shows possible errors, warnings and information about the redirected restore operation that the script will perform. Possible errors start with [E], warnings with [W] and informational messages with [I]. The output is also saved to a file in the current directory. The file name is the script name replaced by the file ending .chk. In this example, the check output would be written to file SAMPLE.chk. With the content of the check output, which is mainly error and warning messages, the database administrator now should be able to find errors in the script or on the database server system, for example, duplicated file names, missing write authorizations, out of space situations. Additional information on the used file systems is provided, such as: •

List of tablespace containers

•

Information on used space

•

Information on free space

•

Information on missed space

•

Information on required space in the DB2 log directory

Especially on UNIX database servers you should be aware that new tablespace containers will be placed in the topmost existing directory and therefore in the underlying file system. So before running brdb6brt, the tablespace container layout (distribution of tablespace containers in the file system(s)) should be implemented by creating file systems, directories and links to get sound check output.

If you have a multi-partitioned database, you need to run brdb6brt for all partitions of your database. To do so, use the –nn parameter. The scripts created include the partition number, which prevents existing scripts from other database partitions from being overwritten.

August 2003

117

6 Advanced Tasks

Moving Existing Containers to Other Directories With brdb6brt patch 5 or higher, you can create relocate scripts to move existing containers to other directories using the db2relocatedb tool. Furthermore, you can use these scripts to initialize mirrored databases with a modified container layout using the db2inidb tool and its parameter RELOCATE USING. 1. To create the relocate script, enter the following command: brdb6brt –s SAMPLE –bm RETRIEVE_RELOCATE Script SAMPLE is generated. You need to modify it according to your requirements. 2. To move containers using the db2relocatedb tool, enter the following command: db2relocatedb –f SAMPLE scr 3. To initialize the mirrored database, for example, to create a database snapshot using the db2inidb tool, enter the following command: db2inidb SAMPLE as snapshot relocate using SAMPLE.scr

Changing Text During Script Generation brdb6brt creates the scripts that are used to perform a redirected restore and to relocate the database (relocate DB script). The script then needs to be adapted according to your requirements. With brdb6brt patch 5 or higher, a new parameter –replace was introduced. You can use this parameter to adjust the script output during its generation instead of adapting the output manually afterwards.

For example, you can use this parameter to change the destination database name from SAMPLE to SAM and the container location from /db2/sample to /db2/sam: brdb6brt –s SAMPLE –bm RETRIEVE –replace SAMPLE=SAM,/db2/sample=/db2/sam

6.7 Command Line Tasks Using db6clp DB2 UDB for UNIX and Windows provides the command line tool Command Line Processor (CLP) that allows you to administer your databases and instances as well as to access your databases via SQL. CLP accepts certain administrative commands like BACKUP DATABASE and SQL commands like SELECT. However, you cannot chain commands on the command line. On Windows you need a special environment to use CLP. The reason is that you can only access CLP on Windows by using a special command shell installed with DB2. Tool db6clp was developed specifically for this reason. It is actually a wrapper of the original CLP.

118

August 2003

6 Advanced Tasks

It allows you to chain commands by separating them with semicolons. Furthermore, it is not necessary to call it in the special DB2 command shell on Windows. The tool is defined as an external command in the SAP system and can be used on all platforms. There are other additional functions, which are presented in the following table. Option

Meaning

-a

Explicitly attaches to the database instance before the command execution. The instance is the one to which the database given with the –db switch belongs. If –db switch is omitted, is assumed as the database name. If S is specified, the attachment is made using user sap. If A is specified, user adm will be used. The password is retrieved using the password services of SAP systems for DB2. Thus the correct environment must exist for a successful run (DB2DB6EKEY must be set). This switch can only be used if the caller is one of the users db2 oradm; or sapse on Windows.

-c

Explicitly connects to the database given with the –db switch. If –db switch is omitted, is assumed as the database name. For specification of S or A the same comments as for the –a switch apply. This switch can only be used if the caller is one of the users db2 or adm; or sapse on Windows.

-l

Sets the environment DB2INSTANCE to this value before execution of the CLP command.

-db

If this switch is specified without one of the –a and –c switches, an explicit connection to the database is only made if it is cataloged remotely.

-p

Sets the partition of the database to which the command applies. This is applicable in multi-partitioned environments. The environment variable DB2NODE is set to the given value before the command execution.

-V

Prints out version and patch information of the tool.

The command to be executed by DB2 CLP. Multiple commands can be specified by separating them with semicolons.

August 2003

119

6 Advanced Tasks

The formal syntax of the command is as follows: db6clp -V | [-a S|A [-db ] | -c S|A [–db ] | -i | -db ] [-p ] See also: •

For more information on external commands, see the SAP Online Documentation in the SAP Library at SAP Web Application Server → Computing Center Management System → Background Processing.

•

For more information on DB2 CLP and administrative commands, see the IBM DB2 administration documentation Command Reference.

•

For more information on SQL, see the IBM DB2 documentation SQL Reference.

6.8 Monitoring Database Alerts For information on how to monitor database alerts, see the SAP Online Documentation in the SAP Library.

6.9 Monitoring Lock Waits and Deadlocks Monitoring Lock Waits A lock wait is a situation when one application updates or inserts a row without COMMIT and a second application tries to access this row. As a result, the second application gets the status lock wait. You can access information on lock waits by calling transaction ST04 and choosing Performance → Lock Waits in the navigation frame of the DBA Cockpit. If you want to display data for the first time, choose Refresh. You can choose between the display layouts by using the list box Layout. For more information, see the SAP Online Documentation in the SAP Library.

120

August 2003

6 Advanced Tasks

Monitoring Deadlocks Deadlocks are situations where two or more applications block each other, each waiting for the other(s) to release a lock, for example, application 1 and application 2 update or insert different rows without COMMIT. Then application 1 tries to access the rows that are locked by application 2, and application 2 tries to access rows that are locked by application 1. You can access the Deadlocks screen by calling transaction ST04 and choosing Performance → Deadlocks in the navigation frame of the DBA Cockpit. You can display all deadlocks that occurred in the past by choosing Show History. For more information, see the SAP Online Documentation in the SAP Library.

6.10 Managing Backups and Logs Archived on TSM TSM distinguishes between: •

TSM backup copy group A defined number of versions of each file is kept on the TSM server.

•

TSM archive copy group Each file is kept on the TSM server for a defined number of days.

The definition of kept versions and days must be performed on the TSM server and cannot be changed by the TSM client. The following table shows which type of TSM copy group is used by DB2 and the Admin Tools. DB2 Backup TSM Copy Group

TSM backup copy group

brarchive

User Exit in Direct Mode

Default: TSM archive copy group. If not present on the TSM server then TSM backup copy group

Default: TSM archive copy group. If not present on the TSM server then TSM backup copy group

To query, extract and delete backups and log files, you should use the following tools: DB Backups

Log Files saved with brarchive

Log Files saved using the User Exit in Direct Mode

TSM Backup Mode

db2adutl

brarchive

db6adutl

TSM Archive Mode

--

brarchive

db6adutl

August 2003

121

6 Advanced Tasks

To run the program db6adutl, use the instance owning user db2. db6adutl is part of the Admin Tools and is installed in the following directory: •

•

For 6.NN Admin Tools

UNIX: $INSTHOME/admintools/bin

Windows: %INSTHOME%\admintools/bin

For 4.6D Admin Tools

UNIX: /usr/sap//SYS/exe/run

Windows: :\usr\sap\\SYS\exe\run

The syntax of this command is provided in Appendix A: Tool Command Line Parameters [Page 147]. Additionally, db2adutl allows you to check backups, logs, and load copy images saved using TSM. This utility is installed in the following directory: UNIX: $INSTHOME/sqllib/misc Windows: \sqllib\misc The syntax of this command is shown in Appendix A: Tool Command Line Parameters [Page 147]. For more information on TSM, see the TSM documentation at: http://www.tivoli.com/support/storage_mgt/adsm/pubs/ For more information on where log files are archived to on the TSM server, see Indirect Archiving to TSM [Page 81] and Archiving Directly to TSM [Page 73].

6.11 The db2inidb Tool DB2 tool db2inidb and the write suspend feature are new features of DB2 UDB for UNIX and Windows Version 7.2. Tool db2inidb in combination with hardware technologies like EMC Timefinder® or IBM ESS® provides the basis for fast backups or database clone creation. With these technologies you can split up an entire file system very fast, that is create a split image of a file system. This split image can be mounted on a different machine, in a different directory on the same machine where the original file system is located or replace the current content of the original file system in case of failure. EMC Timefinder® or IBM ESS® also provide functions to archive a split image to storage devices, for example, tape. The db2inidb tool provides the following options: •

as mirror You use this option to create fast backups and fast database restores with nearly no system outage. For more information, see db2inidb Option: as mirror [Page 123].

•

as snapshot You use this option to create database clones for quality assurance or test systems. For more information, see db2inidb Option: as snapshot [Page 126]

122

August 2003

6 Advanced Tasks

•

as standby You use this option to:

Create normal DB2 backups with nearly no system outage. For more information, see db2inidb Option: as standby for Backups [Page 127].

Build up a hot-standby system that is synchronized over log files. For more information, see db2inidb Option: as standby for Hot-Standby Database [Page 129].

The procedures provided in the following sections are only intended for experienced DB2 database administrators. Be aware that when using these options, problems with the log file management tools might occur. For more detailed information on the db2inidb tool, see the IBM DB2 documentation Data Recovery and High Availability Guide and Reference.

6.11.1 db2inidb Option: as mirror Use You use the db2inidb tool option as mirror to: •

Perform fast backups or restores. The split image is created while the database is in write suspend mode. The creation of the split image is very fast if you are using hardware technologies such as EMC Timefinder or IBM ESS. This means you have nearly no system outage during the backup of the split image. To restore the database, you mount the split image and roll forward the database. The mount time of the split image is very fast compared to a normal database restore. This means you save the time for a normal database restore from a normal DB2 backup image.

•

Compress backups with the standard system command compress before archiving. For example, the split image can be compressed before you archive it to tape. Thus, tape space and I/O bandwidth are saved.

•

Store log files to any storage management system. Using this function you are not restricted to the destinations provided by the DB2 backup command. All storage management systems are able to archive files from the file systems. You use this function to store all important DB2 files and database containers.

With DB2, archiving and restoring the database files and database containers is only allowed in conjunction with the write suspend feature and the db2inidb tool for the regular DB2 backup and restore function. If you do not use these tools, you may cause irrevocable data loss or unexpected system behavior.

August 2003

123

6 Advanced Tasks

Procedure Performing a Backup Ll...

1. To switch the database mode to write suspend, log on to the database server as user db2 and enter the following command: db2 set write suspend for database The database only allows read access now. 2. Create the split image. The split image must include:

All database containers (sapdata*)

The database directory: UNIX: /db2//db Windows: :\db2\\db

Make sure that the image does not include log directory, archive directory and retrieve directory. This is important because, if the split image is mounted for a database restore, the current log files, which are contained in these directories, will be overwritten with the old log files contained in the split image. 3. To switch the database mode back to normal operation, enter the following command: db2 set write resume for database The database now allows full access again. 4. Archive the split image.

Performing a Restore 1. Log on to the database server as user db2. 2. To restore the split image using the storage management system tools, enter the following command: db2inidb as mirror The database is now in rollforward pending mode. 3. Make sure that the log files required for rollforward recovery are available:

For direct archiving: The User Exit retrieves log files as required during rollforward recovery directly from the storage management product.

For indirect archiving: You have to recreate the Admin DB and restore required log files. To do so, proceed as follows: i.

Delete the Admin DB using the following command: db2 drop db ADM

ii.

124

Restore the latest Admin DB backup SAR file using brrestore –ex or use the SAR file located in directory \adminDB_ using program sddb6ins.

August 2003

6 Advanced Tasks

To do so, enter the following command: sddb6ins –r iii.

Restore log files required for rollforward recovery using brrestore.

You can avoid the recreation of the Admin DB in this scenario if the Admin DB uses a file system that is not located in the default database path /db2/. Then the Admin DB is not contained in the split image and contains up-to-date information after the split image has been mounted. By default, the Admin DB is created in the path specified by the database manager configuration parameter DFTDBPATH. You can use the redirected restore tool to move an existing Admin DB. Alternatively, if the Admin DB has not existed so far, you can set the database manager configuration parameter DFTDBPATH to a different directory, for example, to /db2/ADM before calling sddb6ins –i. You should reset parameter DFTDBPATH after sddb6ins –i was called. 4. Perform a rollforward recovery to the end of logs. a. Start the rollforward recovery using the following command: db2 rollforward database to end of logs b. Check if the rollforward recovery is complete using the following command: db2 rollforward database query status If the rollforward recovery is not complete, you have to correct the problem, for example, by providing missing log files and repeating step a. c. Complete the rollforward recovery using the following command: db2 rollforward database to end of logs and complete

If the operation was successful, the database should now be in the most current state and can be accessed again.

Log File Management Considerations If you want to use indirect archiving, you have to consider the following: If no normal database backup (that is a regular online or offline backup) was performed on the system, the log files are assigned to the last backup made on the system. This affects, for example, the usability of the brrestore –delete option. The brrestore –delete option requires that you specify a backup timestamp. Since no normal backup is performed, the backup timestamp is not known to the Admin Tools. To avoid the log file deletion problem, brrestore option –force was introduced. If you use –force in conjunction with –delete , you can specify a timestamp that is not a backup timestamp.

As an example, the following command would delete all log files before 1 January 2003: brrestore –delete 20030101000000 –force

Since indirect archiving is very complex and thus very error prone in conjunction with db2inidb tool option as mirror, we strongly recommend that you use direct archiving.

August 2003

125

6 Advanced Tasks

6.11.2 db2inidb Option: as snapshot Use You use the db2inidb option as snapshot to create a database clone for test or quality assurance systems from your production system. In the past it was necessary to copy a SAP system using the homogeneous system copy. The homogeneous system copy requires a full database backup and a redirected restore on the target system. This procedure can be timeconsuming if your source system is very large (for example, several 100 MB). The db2inidb tool option as snapshot allows you to perform a homogeneous system copy for large databases very fast.

Procedure On the source system: ...



The database directory: UNIX: /db2//db Windows: :\db2\\db

Log directory log_dir The log files are required to roll back open transactions when db2inidb option as snapshot is called on the target system.

Make sure that the split image does not include the archive and retrieve directory. 3. To switch the database mode back to normal operation, enter the following command: db2 set write resume for database The database now allows full access again. On the target system: 1. Prepare a standard SAP system environment for SAP databases using homogeneous system copy methods. 2. Mount the split image using the storage system tools. 3. To initialize the database, enter the following command: db2inidb as snapshot Alternatively, if you want to change the container layout on the target system to distinguish it from the one on the source system, you can use the following command: db2inidb as snapshot using relocate

126

August 2003

6 Advanced Tasks

You can easily create the using the brdb6brt option –bm RETRIEVE_RELOCATE on the source system. All open transaction are rolled back and the target database can be accessed now. Before accessing the target database, however, you should perform the following tasks to clean up the target system. If the source system was configured for indirect archiving, you must delete the Admin DB on the target system. The reason is that the Admin DB, which uses circular logging (that is, LOGRETAIN is switched OFF), is included in the split image, but the db2inidb as snapshot command only supports databases where LOGRETAIN is switched ON.: a. To delete the Admin DB, enter the following command: db2 drop db ADM b. Prune the backup history file as the information included in that file is valid for the source system. To do so, enter the following command: db2 prune history force ..

Log File Management Considerations If you want to use the target system for quality assurance or test purposes, you should consider to set parameter LOGRETAIN to OFF and to use circular logging instead. Thus, you do not need to use the log file management tools.

Be aware that all databases using circular logging are subject to some restrictions, for example, online backups and rollforward recovery are not supported. If you require these features on the target system, you must reinstall and reconfigure the log file management tools on the target system.

6.11.3 db2inidb Option: as standby for Backups Use You use the db2inidb tool option as standby to perform fast backups with nearly no system outage, especially if you are using hardware technologies such as EMC Timefinder or IBM ESS. Backups and restores are simply performed with standard DB2 commands. In comparison to the db2inidb option as mirror, where split images are used to back up the database, normal DB2 backup images are created. The normal DB2 backup images offer the following advantages: •

The backup image can be used to restore the database on a different hardware platform.

•

The backup image can be used to perform a redirected restore.

•

The backup image can be used to restore the database into a newer DB2 version, for example to build up a quality assurance or test system on a new database version.

August 2003

127

6 Advanced Tasks

Procedure Performing a Backup On the source system: ...



The DB2 instance directory: UNIX: /db2//db Windows: :\db2\\db

3. To switch the database mode back to normal operation, enter the following command: db2 set write resume for database The database now allows full access again. 4. Archive the split image. On the target system: 1. To create a database instance using DB2 instance creation tool db2icrt, enter the following command: db2icrt 2. Log on as instance owner. 3. To start the database instance, enter the following command: db2start 4. Recreate the database from the split image: a. Mount the split image. b. Initialize the standby database using the following command: db2inidb as standby 5. To create a database online backup using the DB2 backup command, enter the following command: db2 backup db online to /tmp

Performing a Restore The backup images created with the backup command on the standby system can be used as normal backups for the source system. Thus, the restore procedure is equivalent.

The backup performed on the standby system is not listed in the backup history of the source system.

128

August 2003

6 Advanced Tasks

Log File Management Considerations If you want to use indirect archiving, you have to consider the following: If no normal database backup (that is a regular online or offline backup) was performed o the system, the log files are assigned to the last backup made on the source system. This affects, for example, the usability of the brrestore –delete option. The brrestore –delete option requires that you specify a backup timestamp. Since no normal backup is performed, the backup timestamp is not known to the Admin Tools. To avoid the log file deletion problem, brrestore option –force was introduced. If you use –force in conjunction with -delete , you can specify a timestamp that is not a backup timestamp.

As an example, the following command would delete all log files before 1 January 2003: brrestore –delete 20030101000000 -force

6.11.4 db2inidb Option: as standby for Hot-Standby Database Use You use the db2inidb tool option as standby to synchronize two databases using log files, that is you create a hot-standby database. Hot-standby database systems are used to: •

Avoid long restore operations after a hardware failure. The hot-standby database buffers are allocated and filled with the most recently changed data pages.

•

Allow a fast recovery from logical failures. In this case, the hot-standby system is kept in a state with a defined time delay compared to the source system. If a logical error is detected on the source system, you can switch to the state of the hot-standby system that may not have applied this logical error.

The setup can be as follows: The source database works normally. The hot-standby database is in rollforward pending mode. All log files are created on the source database and are copied to the hot-standby database using the ROLLFORWARD command as shown in the following graphic.

August 2003

129

6 Advanced Tasks

source system

hot-standby system

source database

hotstandby database

log_dir

log file copied on regular basis

log_dir

The following graphics show the differences for direct or indirect archiving. Overview of Standby Databases If Direct Archiving Is Used

source system

hot-standby system

source database

hotstandby database

log_dir

log_dir

db2uext2 ARCHIVE

130

Storage Management System

db2uext2 RETRIEVE

August 2003

6 Advanced Tasks

Overview of Standby Databases If Indirect Archiving Is Used

source system

hot-standby system

hotstandby database

source database

log_dir

Admin DB

db2uext2 ARCHIVE log_archive

standby_dir

Admin DB

log_dir

db2uext2 RETRIEVE without DB copy log file on regular basis

log_archive

brarchive

backend

Procedure The following procedures describe the end of log scenario. The other scenario, that is recovering from logical failures, works according to the end of log scenario, but you must use rollforward to instead of rollforward to end of logs to ensure the time delay for the hot-standby system.

Preparing the Source Database •

If you are using indirect archiving: Configure environment variable DB2DB6_STANDBY_DIR in file init.db6. The User Exit then writes a copy of the log file into the standby directory in addition to the one the User Exit writes into the archive directory.

•

If you are using direct archiving: The central point for the log files is the storage management system. No further action is required.

August 2003

131

6 Advanced Tasks

Creating an Initial Hot-Standby Database Version On the source system: Create a database snapshot of the source system that is used as the starting point for the hot-standby system: ...



The database instance directory: UNIX: /db2//db Windows: :\db2\\db

Make sure that the image does not include the log directory, the archive directory and the retrieve directory. 3. To switch the database mode back to normal operation, enter the following command: db2 set write resume for database The database now allows full access again. 4. Archive the split image. ...

On the hot-standby system: 1. Create the database by mounting the split image from the source system. 2. Initialize the hot-standby database using the following: db2inidb as standby The open transactions are not rolled back and the hot-standby database is now in rollforward pending mode.

Preparing the Hot-Standby Database If you want to use indirect archiving: 1. Drop the Admin DB using the following command: db2 drop db ADM The Admin DB of the hot-standby system is empty until the system failover. 2. Set environment variable DB2DB6_UEXIT_RETRIEVE_WO_DB in file init.db6 to ON. The Admin DB on the hot-standby system does not have any knowledge about log files created on the source database. To enable the User Exit to retrieve log files during rollforward recovery, you have to activate this flag. The User Exit then retrieves the latest log files with the log file number requested by the database engine during rollforward recovery.

132

August 2003

6 Advanced Tasks

If you want to use direct archiving: If you are using TSM, you have to configure the same NODENAME (TSM configuration parameter) on the hot-standby system as on the source database.

Keeping the Hot-Standby Database in Synchronization with the Source Database To keep the hot-standby database in synchronization with the source database, you have to perform the following tasks on a regular basis. If you want to use indirect archiving: •

In most cases, the standby directory (DB2DB6_STANDBY_DIR) of the source database is a shared file system pointing to the archive directory of the hot-standby database system (DB2DB6_ARCHIVE_PATH>/). If this is not the case, you have to transport the log files from the source database system to the hot-standby system.

•

Perform a rollforward recovery on the hot-standby system (without the stop/complete option) using the following command: db2 rollforward db to end of logs

•

Delete log files that have already been applied to the hot-standby database using rollforward recovery and that have already been archived on the source system using brarchive.

If you want to use direct archiving: You perform a rollforward recovery on the hot-standby system (without the stop/complete option) using the following command: db2 rollforward db to end of logs The User Exit then retrieves the required log files directly from the storage management system, where the source database had stored them.

Switching Over to the Hot-Standby Database System If you are using direct archiving, you perform a rollforward recovery to the end of log files on the hot-standby system. 1. To start the rollforward recovery, enter the following command: db2 rollforward db to end of logs 2. To check if the rollforward recovery is complete, enter the following command: db2 rollforward db query status If the rollforward recovery is not complete, you have to correct the error, for example, by providing missing log files and repeat step a. 3. To stop the rollforward recovery, enter the following command: db2 rollforward database and complete If the operation was successful, the hot-standby database is now in the most current state and can be accessed again.

August 2003

133

6 Advanced Tasks

If you are using indirect archiving, you perform the following tasks on the hot-standby system: ...

1. Recreate the content of the Admin DB on the hot-standby system using the latest Admin DB backup SAR file. You can find the latest Admin DB backup SAR file in one of the following locations:

In the standby directory (DB2DB6_STANDBY_DIR). The SAR file is only available in the standby directory if you have set configuration parameter DB2DB6_STANDBY_DIR_ADMDBSAR to ON in the init.db6 file.

In directory /adminDB_ that is located on the source system.

If the Admin DB backup SAR file is not in one of these locations or cannot be accessed, you have to perform an emergency restore using brrestore –ex. To recreate the content of the Admin DB, enter the following command: sddb6ins –r 2. Perform a rollforward recovery to end of log files. a. To start the rollforward recovery, enter the following command: db2 rollforward db to end of logs b. To check if the rollforward recovery is complete, enter the following command: db2 rollforward db query status If the rollforward recovery is not complete, you have to correct the error, for example, by providing missing log files and repeat step a. c. To complete the rollforward recovery, enter the following command: db2 rollforward database and complete If the operation was successful, the hot-standby database should now be in the most current state and can be accessed again.

Log File Management Considerations If you are using indirect archiving, the Admin DB of the hot-standby database does not contain any information on log files and database backups before the system failover. The system failover is relatively complex and error-prone. Thus, we recommend that you use direct archiving.

134

August 2003

6 Advanced Tasks

6.12 Dual Logging Dual logging is a new feature of DB2 Version 7.2. It provides the option to keep two copies of the log files, which are normally only in the log directory. To activate dual logging on DB2 V7: Set...

1. Set DB2 instance registry variable DB2_NEWLOGPATH2 using db2set. Enter the following command: db2set DB2_NEWLOGPATH2=ON DB2 creates the name of the second log directory from the first log directory by adding 2. 2. To activate this change, restart the instance. To activate dual logging on DB2 UDB for UNIX and Windows Version 8, proceed as follows: 1. Set DB configuration parameter MIRROR_LOGPATH. Enter: db2 update db cfg for using MIRROR_LOGPATH

2. To activate this change, restart the instance.

DB2 calls the User Exit just once for each log file regardless if dual logging is activated or not. This means that the log files are not archived twice. If you use indirect archiving, the Admin DB keeps track of only one log file. brarchive stores only one log file.

6.13 Adjusting the Content of the Admin DB Use You adjust the content of the Admin DB by using the brarchive option –GAL.

Before you enter the –GAL option of brarchive you should use the preview mode with the –DMP option to make sure that your changes to the Admin DB are correct.

Procedure To adjust the content of the Admin DB, you use the following brarchive options: •

-GAL add Log files that are not known to the Admin DB but located in the archive directory are added to the Admin DB.

•

-GAL remove Log files that are assumed to be in the archive directory but do not exist there are removed from the Admin DB.

August 2003

135

6 Advanced Tasks

•

-GAL revert With this option, you can revert the state of log files from ARCHRET (archive retained) to OFFLRET (offline retained) if the log files are still in the archive directory. The next time brarchive is run to archive log files, these log files will be treated like log files that have not yet been archived; they will then be archived again.

136

August 2003

7 Emergency Tasks

7 Emergency Tasks 7.1 SAP Database Recovery If you are using a full database backup as a basis, you can choose to have DB2 apply all the units of work for any or all of the tablespaces in the database, up to any point within the time period covered by the logs ROLLFORWARD recovery. As long as all the log files are available covering the time period from the time of the backup through to the current time, rollforward recovery re-applies the completed units of work recorded in the database logs to the restored database, tablespace or tablespaces. You can specify that rollforward recovery is done to the end of logs, or to a particular point in time. During the restore phase, a copy of the SAP database and SAP database logs is needed for recovery. The database administrator db2 or the SAP system administrator adm can restore or rollforward the database. In a partitioned database environment, the rollforward must be issued from the catalog node of the database. The restore command can be entered from the command line processor, DB2 Command Center or DB2 Control Center. A database must be restored successfully before it can be rolled forward; but a tablespace does not. A database rollforward runs offline. The database is not available for use until the rollforward completes either by reaching the end of the logs during a tablespace rollforward, or by specifying STOP on the ROLLFORWARD command. You can, however, perform an online rollforward of tablespaces as long as SYSCATSPACE is not included. When you perform an online rollforward operation on a tablespace, it is not available for use, but the other tablespaces in the database are. When the rollforward recovery method completes successfully, the last log that was used by rollforward is truncated, and logging begins with the next sequential log. The practical effect is that any log in the log directory with a sequence number greater than the last log used for rollforward recovery is re-used. You have to make a copy of the logs before executing the ROLLFORWARD command if not already done by the User Exit program. The DB2 Database Manager ensures that an incorrect log is not applied during rollforward recovery, but it cannot detect the location of the required log. If the active log file is not sufficient for a recovery, the db2uext2 interface will be called with the RETRIEVE request to get the missing log file.

Restoring Databases from an Offline Backup Without Rolling Forward You can only restore without rolling forward at the database level, and not at the tablespace level. To restore a database without rolling forward, you could either restore a nonrecoverable database, that is, a database that uses circular logging, or you could specify the WITHOUT ROLLING FORWARD parameter for the restore utility.

Restoring Databases and Tablespaces and Rolling Forward You do not have to recover your database with the latest backup copy of the database. You can start with any backup as long as you have the logs associated with and following that backup.

August 2003

137

7 Emergency Tasks

One type of problem that requires point-in-time rollforward recovery is the corruption of data that is caused by errant logic or incorrect input in an application. You can use rollforward recovery to recover the database to a point-in-time that is close to when the application began working with the database. A log uses a timestamp associated with the completion of a unit of work. The timestamp in the log uses the Coordinated Universal Time (CUT). CUT helps to avoid having the same timestamp associated with different logs, for example, because of a change in time associated with daylight savings time. The timestamp used on the backup is based on the local time that the backup started. As a result, when you call the ROLLFORWARD command, you must specify the time in CUT.

7.2 Log File Restore of the SAP Database As outlined in Basic Concepts [Page 21], you can restore log files either automatically using the User Exit program or you have to restore them from the retrieve directory. From there the User Exit is able to retrieve log files to the online log directory. If you choose to restore from storage into the retrieve directory, use one of the following procedures. You can restore log files from storage (tape, TSM or any other backend where you archived log files to using customer script) either with the brrestore tool or with the DB2 Control Center Extensions for the SAP system.

Restoring Log Files with the DB2 Control Center: To restore the log files, proceed as follows: ...

1. In the navigation frame of the DB2 Control Center, from the context menu of the SAP database, choose SAP Log File Management. 2. If required, enter user adm or db2. The Logfile Management dialog box appears.

138

August 2003

7 Emergency Tasks

3. Choose Restore. 4. Choose the node where you want to restore log files. 5. To determine which log files are to be restored, choose a backup date. All available log files for the selected backup are displayed together with the number of locations on which they are stored. 6. In the Restore logiles taken at group box select a log file range to be restored.

Due to a program limitation, the oldest log file must be at the start of the range.

August 2003

139

7 Emergency Tasks

7. To start the restore procedure, choose Restore. The Select Location dialog box appears.

8. Choose the location to be used for the restore process. The best location is the default value. All log files that can be restored from the chosen location are displayed. If you choose a tape location from where all matching devices are retrieved, you have to choose a tape device in the Select tape drive field. 9. To start restoring from the selected location, choose Start. A dialog box appears informing you that the restore process has been started. After the restore has finished, a check is made whether all selected log files were restored. If so, a dialog box appears displaying a success message. If there are still log files left to be restored, the Select Location dialog box reappears again and you have to repeat the process for all different locations where necessary. All restore actions and their results can be seen on the Journal screen.

Restoring Log Files with the Command Line Tool brrestore brrestore can be used to retrieve any log file archived using brarchive. Calling brrestore on the command line gives the administrator access to all command line options and may therefore be more flexible than using the DB2 Control Center. The following tables describes the most common usage of brrestore. If this fails, an emergency restore can be attempted, although we do not recommend this action.

The -a arguments (shown in the following tables) must be log file numbers and not log file names. For more information on the syntax of brrestore, see brrestore - Log File Retrieval Tool [Page 153].

140

August 2003

7 Emergency Tasks

Restoring Log Files From a Tape Command syntax

brrestore -a 110-120 -d tape -v D4XA99 –out –sid SAPSID

Description

This command tries to retrieve the log files with the numbers 110 to 120 from tape D4XA99. Messages are written to the standard output and a protocol file.

Restoring Log Files From TSM Command syntax

brrestore -a 110-120 -d tsm –out –sid SAPSID

Description

This command tries to retrieve the log files with the numbers 110 to 120 from TSM. Messages are written to the standard output file and a protocol file.

Restoring Log Files Archived Using Customer Script Command syntax

brrestore -a 110-120 -d script –out –sid SAPSID

Description

This command tries to retrieve the log files with the numbers 110 to 120, which where archived using your own script. Messages are written to the standard output file and a protocol file.

Emergency Restore of Log Files Using brrestore -ex Use this procedure either when both the Admin DB and the SAP database have been destroyed or normal log file retrieval methods fail. Do not use an emergency restore for normal management purposes, as the Admin DB is not updated.

We recommend that you first recover the Admin DB if necessary, and then use the standard restore command brrestore -a as described in the tables above.

Prerequisites •

The SAP database has been restored from a backup and left in ROLLFORWARD_PENDING state.

•

You have started the database ROLLFORWARD and determined that DB2 requests log files that cannot be retrieved directly from the active log or by the User Exit from the archive or retrieve directory.

Procedure You can perform the following procedures using either the DB2 Control Center or brrestore. DB2 Control Center ...

1. Locate the most recent backup of the Admin DB ADM on TSM or tape and recreate the database as described in Recovery of the Admin DB [Page 143]. 2. Use the DB2 Control Center Extensions to restore the log files from TSM or tape as described in “Restoring Log Files” in this section.

August 2003

141

7 Emergency Tasks

brrestore If you do not currently have access to the Admin Tools, you can restore the log files completely independently of the DB2 Control Center and the Admin DB. This procedure allows you to retrieve log files and Admin DB backups directly from tape or TSM. ...

1. After restoring the SAP database and starting the rollforward recovery, determine which log files are not yet available but will be required for the recovery process. That is, which log files were created after the backup was taken but are currently not in the log_dir, log_archive or log_retrieve directories. 2. To find out where these files are located, enter the following command: brrestore -q { -d tsm | -d tape -t | -d script } 3. To restore the log files from the archive, enter the following command: brrestore –ex [[]] { -d tsm | -d tape –t | -d script } is the non-rewind address of the tape for restoring from tape. 4. Put the files where DB2 or the User Exit expects to find them (log_dir, log_retrieve) and continue the rollforward recovery.

To restore all files from a tape on Windows, enter the following command: brrestore -d tape –ex "*" -t /dev/nmt0.1 To retrieve all log files in the number range 100 to 199 that were archived to TSM between November 1997 and the current day, enter: brrestore -d tsm -ex "S00001*" 199711 For more information on the syntax of brrestore and non-rewind addresses, see brrestore Log File Retrieval Tool [Page 153].

142

August 2003

7 Emergency Tasks

7.3 Recovery of the Admin DB

7.3.1 General Information The Admin DB contains information about objects such as log files and their locations. This information is required to process these log files, for example, to recover them. The Admin DB data itself may need to be recovered for the following reasons: •

The Admin DB is lost or damaged.

•

The SAP system is being moved or a duplicate SAP system is being set up on another machine. The SAPSID and DBSID must remain unchanged. Otherwise, you must create a new empty Admin DB.

You can deal with these situations as follows: •

You create a new Admin DB and take a backup of the SAP database. If you do not need the old log files, for example, on a test system, this is the fastest way of recovering or recreating the Admin DB. Otherwise, you must recover the Admin DB from a safety copy as described below.

If the Admi...

a. If the Admin DB still exists, drop it using the following command: DROP DB ADM b. To recreate an empty Admin DB, call sddb6ins –i.

It is not essential but useful to prune the DB2 backup history file (PRUNE…). c. Make sure that the SAP database parameters LOGRETAIN and USEREXIT are both set to ON. d. Perform a full backup of the SAP database. •

You recover the Admin DB from a safety copy. To make sure that the Admin DB ADM can be recovered, an export of essential information from that database is archived to the same target as the log files just archived. brarchive does this export and archiving automatically after successfully archiving one or more log files. This database safety copy is not a database backup but a compressed archive file (adminDB.SAR) made up of individual table exports. This database backup image does not contain job information such as the one shown on journal pages in the DB2 Control Center. This data is lost when ADM is recreated using this image.

August 2003

143

7 Emergency Tasks

7.3.2 Recovery Process The recovery process consists of the following steps: Re...

1. Retrieving the Admin DB backup image from archive. 2. Recreating the Admin DB using sddb6ins or manually.

Retrieving the Admin DB Backup Image from Archive (TSM, Customer Script or Tape) A copy of the latest Admin DB safety copy export archive file (adminDB..SAR) should be located in subdirectory /adminDB_. If this file is available, you do not need to recover the Admin DB backup image from storage and you can continue with “Recreating the Admin DB” described further below in this section. •

TSM: If the database log files were archived to TSM, Admin DB backup images are also stored there. To find the most recent file, it may be necessary to retrieve several or all SAR files from TSM. If you specify adminDB.*.SAR as the file pattern, all safety files will be retrieved. If you specify a part of the timestamp in the file pattern, the number of files is reduced. You can restore the SAR file from TSM using the following command: brrestore –d tsm –ex -out

•

Customer script: If the database logs were archived using a customer script, Admin DB backup images are also stored using a customer script. You need to restore the SAR file manually from where the customer script has stored it.

•

Tape: If database safety copies (and log files) are archived to tape, restore the safety file of the Admin DB from the current tape to the current working directory using the following command: brrestore –d tape –ex -t -out

is the non-rewind address of the tape for restoring from tape. For more information on non-rewind addresses, see brrestore – Log File Retrieval Tool [Page 153].

You can use the pattern adminDB.20000626*.SAR to retrieve all safety files of the Admin DB archived on August 26, 2000. This pattern reduces the number of log files that are to be retrieved. To retrieve the above mentioned safety file(s) to the current working directory, enter the following command: • For TSM: brrestore –d tsm –out –ex “adminDB.20000626*.SAR“ • For tape: brrestore –d tape –out –ex “adminDB.20000626*.SAR“

Recreating the Admin DB The Admin DB can be recreated either using program sddb6ins or manually.

144

August 2003

7 Emergency Tasks

Recreating the Admin DB Using Program sddb6ins 1. Log on to the database server: On UNIX: As user adm and switch to user root (su root). On Windows: As user adm. 2. Create the Admin DB using sddb6ins. To do so, enter the following command: sddb6ins –r

sddb6ins –r adminDB.20000626123504.SAR This command performs the following actions: • It creates the Admin DB. • It extracts SAR file. • It imports IXF files from SAR file. • It adds backups, which were made after the SAR file has been created, to the Admin DB. • It adds log files from log_archive that are not known to the Admin DB to the Admin DB. The reason is that these log files are moved to log_archive after the SAR file has been created.

Recreating the Admin DB Manually 1. To recreate the Admin DB step-by-step from the SAR file, enter the following command: On UNIX: a. Log on as user adm and switch to user root using the following command: su root b. As user root, enter the following command: sddb6ins –I On Windows: Log on to your database server as user adm and enter the following command: sddb6ins –i –db2dbnamepwd > 2. Extract the SAR file using the following command: SAPCAR –xvf adminDB..SAR 3. Import IXF files using the db2move tool. 4. To import the original data, call db2move as user adm from the directory where the archive was unpacked. db2move is located in the DB2 directory misc. Enter the following command: db2move ADM IMPORT –io INSERT_UPDATE

August 2003

145

7 Emergency Tasks

This command performs the following actions:

Backups that were made after the SAR file was created are added to the Admin DB (brarchive –q backups –out).

Log files that are not known to the Admin DB are added from log_archive to the Admin DB. The reason is that these log files are moved to log_archive after the SAR file has been created (brarchive –GAL add –out).

The output of the db2move command can be as follows: IMPORT: -Rows read: 2109; –Rows committed; 2109 Table: SAPR3.DB6IDENTIFIER IMPORT: -Rows read: 19; –Rows committed; 19 Table: SAPR3.DB6CONFIG IMPORT: -Rows read: 774; –Rows committed; 774 Table: SAPR3.DB6LOGFILE IMPORT: -Rows read: 1774; –Rows committed; 1774 Table: SAPR3.DB6LFSTATE All errors and warnings are recorded in the table-specific message files named tab?.msg. As a result, backups that are made after the SAR file has been created are added to the Admin DB using the following command: brarchive –q backups –out Tool db2move also adds log files that are not known to the Admin DB from log_archive to the Admin DB using the following command: brarchive –GAL add –out. The reason is that these log files are moved to log_archive after the SAR file has been created.

146

August 2003

Appendix A: Tool Command Line Parameters

Appendix A: Tool Command Line Parameters brarchive - Log File Archive Tool

August 2003

147


Command Parameter Actions Parameter

Action

-h

Displays the application command line syntax.

-help

-Version

Displays the application information and library version information if -d is specified.

-do

Deletes obsolete files.

-dt

Deletes tape with label volume from the Admin DB. For tape, also specify –v.

-V

You use this option if the tape is lost or is physically damaged. -i -initialize ...

can be replaced by one of the following: •

show Displays the tape volume name (default).

•

set Re-initializes the old archive tape.

•

force Initializes the new tape or re-initializes the old tape containing the active log files.

-s

Stores log file(s). For tape, also specify –v and –t. Files are not deleted on disk after they are archived.

-sd

Stores and deletes log file(s). For tape, also specify –v and –t. Successfully archived files are deleted from disk.

-ss

Stores each log file twice to backend. For tape, also specify –v and –t. Files are not deleted on disk after they are archived.

-ssd

Tape only: Same as –ss action but removes original copies from disk.

-ds

Deletes stored log file(s). Removes original copies from disk.

-q

can be replaced by one of the following:

-query

148

•

backups Displays the timestamps of all known backups.

•

check Displays detailed information about a tape. Similar to -i show

August 2003


Command Parameter Modifiers Modifier

Description

-d

Device to archive files to retrieve files from tape, tsm or any other backend using a customer script. -d tsm can be followed by a single TSM management class or by two TSM management classes separated by ‘+‘.

-device

-DEB []

Switches on trace. is a value between 1 (low detail) and 9 (high detail). However, the trace is normally turned on in the init.db6 file.

-one

No automatic tape eject after successful archive operation

-out

Displays the protocol on the command line (stdout).

Without this parameter no output is displayed except -h and -V. -n -number -node NODExxxx -node ALL

Maximum number of log files to process. The oldest files are processed first. The numerical argument does not refer to a log file number. Node on which to perform processing or all nodes on which to perform processing. The default value is NODE0000.

- node ALL cannot be used with all actions. This parameter is only required with multi-partitioned systems. -sid

SAP system ID to which the operation applies

-t []

Tape only:

-tape_device ... -v [] -volume ... -vndlib

Tape device(s), for example, /dev/rmt/0 on Solaris. Two arguments are required for -ss and –ssd. Tape only: Volume name(s) on tape(s). Two arguments are required for -ss and –ssd. File name (including path) of the vendor library to load

brarchive and brrestore only automatically display output with -h and -V. To display the output for other actions, -out must be specified.

August 2003

149


brdb6brt - Redirected Restore Tool brdb6brt runs in the following four modes: •

Backup/retrieve

•

Check

•

Tool information

The following sections provide syntax examples of each mode.

Backup/Retrieve Mode To create a backup or a restore script, use the following syntax:

Command Parameter: Parameter

Description

-bm BACKUP

Creates a backup of the specified database only.

-bm RETRIEVE

Creates the restore script for the specified database only.

-bm BOTH

Creates a backup of and the restore script for the specified database.

-bm RETRIEVE RELOCATE

Creates the relocate script for the specified database.

150

August 2003


-s

Name of the database for which the backup and/or restore script should be created

-pp

Directory where the protocol file for the brdb6brt run will be written to. The default value is the working directory. The protocol file is named .brp or _NODE.brp in a multipartitioned environment.

-i

Directory where the restore script will be written to. The default value is the working directory. The restore script is named .scr or _NODEscr in a multipartitioned environment.

-nb

Number of buffers reserved for the execution of the backup. The default value is 2.

-bs

Size of the buffer for the backup operation, in 4 KB allocation units. The default value is 1024.

-es

The restore script is created for experts, that is, only comments that are really needed are included.

-ol

Backup operation is done online.

-ts

Only used in retrieve mode. If specified, the timestamp in the restore script is set to this value which must be in the form YYYYMMDDhhmmss. The default value is the current date and time.

- replace

With this option you replace strings in the generated scripts for redirected restore and relocate. Parameter ReplaceDefinition must have the format =,=,…. This option only makes sense for –bm RETRIEVE, -bm BOTH and –bm RETRIEVE RELOCATE.

-parallelism

Allows you to specify the parallelism degree for backup and redirected restore operations.

-nn

In a multi-partitioned environment the backup operation is done against this node. The restore script is specific for this node and is named _NODE.scr.

-bpt

To back up the database to tape, specify a valid tape device. You can split the backup into multiple pieces by specifying multiple devices separated by blanks.

-bpt

To back up the database to a directory, specify a valid directory. Make sure it has sufficient space to hold the backup volume. It is possible to split the backup into multiple pieces by specifying multiple directories separated by blanks.

August 2003

151


-bpt TSM []

With this option you back up the database into TSM. Specify NumberOfSessions for the TSM connection.

-bpt XBSA []

With this option you back up the database into an XBSAcompliant storage management system. Specify NumberOfSessions for the XBSA connection. This feature can only be used on DB2 V8.

-bpt VENDOR

With this option you use a third party shared library to perform the backup operation. Specify the library as LibName and optionally the NumberOfSessions.

[] -tbs

If this option is not specified, a full database backup is performed. However, you may choose to back up only one or more tablespaces of the database. To do so, specify the tablespaces separated by blanks. The restore script is created only for the specified tablespaces.

-user

To perform the whole operation of brdb6brt as another user, you can specify the user name here.

-using

Password for the specified user

Check Mode Use this syntax to check whether a given restore script would succeed on this machine. The user who performs the check mode should be the instance owner of the database instance. The terminal output of the check run is written to a protocol file in the current directory. The name of the protocol file is .chk or _NODE.chk depending on the specified script name.

Command Parameters Parameter

Description

-bm CHECK

Checks whether a given restore script would succeed on this machine

-ip

Name of the restore script to be checked on this machine. By default, the restore script is named .scr or _NODE.scr in a multipartitioned environment.

-nn

In a multi-partitioned environment the check operation is done against this node.

-user

To perform the database dependent checking of the check mode as another user, you can specify the user name here.

-using

Password for the specified user

Tool Information Mode Use this syntax to retrieve version information about this tool.

152

August 2003


brrestore - Log File Retrieval Tool

Command Parameter Actions Parameter

Action

-h

Displays the application command line syntax.

-help -V

Displays application information and library version information if -d is specified.

-dr [|]

Deletes restored log file(s). Removes restored copies from disk.

August 2003

153


-a

Restores log file(s) from archive.

-a

Each parameter is a log file number and not a log file name, for example, -a 5 or -a 5-10 to restore log file 5 only or files 5 to 10.

-ex

Performs emergency restore of log files when the Admin DB is not available. Tape argument -t must refer to a non-rewind device. Nonrewind address examples are described under “Non-Rewind Tape Device Addresses” later in this section.

-delete []

Deletes all known log files in an archive (TSM only) older than the given backup timestamp ts. An additional (optional) file pattern can be specified to force all matching files older than the timestamp to be deleted, even if they are not known to the Admin DB. If you want to specify a timestamp that is not a backup timestamp, you need to specify the –force option in addition.

-q

can be replaced by one of the following:

-query

-force

•

adsmMC Displays known TSM management classes.

•

Requests a list of log files matching the given file pattern on the TSM server, for example, –q "*.LOG" (TSM only).

Currently only used in conjunction with –delete ts-.

Command Parameter Modifiers Modifier

Description

-d

Device to archive files to retrieve files from tape, TSM or any other backend using a customer script. -d adsm can be followed by an TSM management class

-device -DEB []

Switches on trace. level is a value between 1 (low detail) and 9 (high detail). However, the trace is normally turned on in the init.db6 file.

-one

No automatic tape eject after successful archive operation

-out

Displays the protocol on the command line (stdout).

Without this parameter no output is displayed except -h and -V. -n -number

154

Maximum number of log files to process. The oldest files are processed first. The numerical argument does not refer to a log file number.

August 2003


-node NODExxxx -node ALL

Node on which to perform processing. The default value is NODE0000 or ALL nodes.

-node ALL cannot be used with all actions. This parameter is only required with multi-partitioned systems. -sid

SAP system ID to which the operation applies

-t

Tape device, for example, /dev/rmt/0 on Solaris. brrestore can only access one tape a time.

-tape_device -v

Volume name on tape.

-volume

brrestore can only access one tape at a time.

-vndlib

File name (including path) of the vendor library to load

brarchive and brrestore only automatically display output with -h and -V. To display output for other actions, -out must be specified

Non-Rewind Tape Device Addresses This form of the addresses is only required for emergency restore (-ex). With the exception of AIX, it is formed by adding an "n" to the standard (rewind) address: Windows:

/dev/nmt0

AIX:

/dev/rmt0.1

Solaris:

/dev/rmt/0n

Linux:

/dev/nst0

HP-UX:

/dev/rmt/0mn

If you have more than one tape device to address, replace the “0” with “1”, “2” and so on as appropriate. For example, the non-rewind address of the second tape on Solaris is /dev/rmt/1n.

August 2003

155


db2adutl - DB2 TSM Management Tool

Parameter

Description

QUERY

Queries the TSM server for DB2 objects.

EXTRACT

Copies DB2 objects from the TSM server to the current directory on the local machine.

DELETE

Either deactivates backup objects or deletes log archives on the TSM server.

VERIFY

Performs consistency check on the backup copy that is on the server. This parameter causes the entire backup image to be transferred over the network.

156

August 2003


TABLESPACE

Includes only tablespace backup images.

FULL

Includes only full database backup images.

LOADCOPY

Includes only load copy images.

LOGS

Includes only log archive images.

BETWEEN AND

Specifies that the logs between log sequence number 1 and log sequence number 2 are to be used.

SHOW INACTIVE

Includes backup objects that have been deactivated.

TAKEN AT

Specifies a backup image by its timestamp.

KEEP n

Deactivates all objects of the specified type except for the most recent n by timestamp.

OLDER THAN

Specifies that objects with a timestamp earlier than timestamp or n days will be deactivated.

DATABASE

Considers only those objects associated with the specified database name.

NODE

Considers only those objects created by the specified node number.

PASSWORD

Specifies the TSM client password for this node, if required. If a database is specified and the password is not provided, the value specified for the TSM password database configuration parameter is passed to TSM. Otherwise, no password is used.

NODENAME

Considers only those images associated with a specific TSM node name.

WITHOUT PROMPTING

The user is not prompted for verification before objects are deleted.

OWNER

Considers only those objects created by the specified owner.

August 2003

157


db2ckbkp - DB2 Check Backup Utility


Description

-a

Displays all available information.

-c

Displays the results of check bits and check sums.

-d

Displays information from the headers of DMS tablespace data pages.

-h

Displays media header information, including the name or path of the image expected by the restore utility.

-H

Displays only the media header information. This option does not validate the image. Validation will be performed on the entire image if this option is not specified.

-l

Displays log file header data.

-o

Displays detailed information from the object headers.

The name of the backup can be checked at a time.

158

August 2003


db2trc - DB2 Trace Using this utility is described in DB2 Trace Facility db2trc [Page 171].

Parameters You must issue the db2trc command several times to turn tracing on, produce a dump file, format the dump file, and turn tracing off. The following table represents the order in which you use the parameters: Parameter

Description

on

Use this parameter to start the DB2 trace facility. See the next table for information about the options for this parameter.

dump

If you are tracing to memory, use this parameter to dump the trace information out to a file once you reproduce the error. The following command puts the information in the current directory in a file called db2trc.dmp: db2trc dump db2trc.dmp You must specify a file name with this parameter. The file is saved in the current directory unless you explicitly specify a path.

off

After you dump the trace to a file, turn the trace off by entering the following command: db2trc off

August 2003

159


flw | fmt

After you dump the trace to a binary file, confirm that the trace was successful by formatting it into an ASCII file. Use either the flw option (to sort by process or thread), or the fmt option (to list every event chronologically). For either option, you must specify the name of the dump file and should specify the name of an output file that will be generated. For example: db2trc flw db2trc.dmp db2trc.flw db2trc fmt db2trc.dmp db2trc.fmt These options are provided for you to verify the trace. In most cases, only the dump file itself should be sent to the DB2 Customer Service.

Starting a DB2 Trace To start the trace facility, enter db2trc on. The default trace option values are:

Trace Option

Description

-m *.*.*.*

Traces everything.

-e –1

Collects all errors.

-r 16000

Maximum record size is 16 KB.

-s

Trace destination to shared memory (instead of to a file)

-m mask

Specifies trace record types to focus the search on. The mask variable consists of four one-byte masks separated by periods. These masks correspond to products, event types, components, and functions, respectively, and act as a filter to accept or reject the trace record sent by DB2 for each event based on its ID. If the DB2 Customer Service requires mask values different from the default values, you will be instructed on which values to use.

-e max_sys_errors

Limits the number of DB2 internal system errors that the trace will hold to max_sys_errors. The default value is -1 (collect all errors).

-r max_record_size

Limits the size of trace records to max_record_size bytes. Longer trace records are truncated.

-s | -f filename

By default, the trace output is stored in shared memory (the s option). If the workstation is being suspended preventing you from accessing this output, you can trace to a file with the -f option. When using this output, you must specify a file where the trace output will be stored.

160

August 2003


l [ buffer_size] | -i [buffer_size]

This option specifies the size and behavior of the trace buffer.-l specifies that the last trace records are retained, that is, the first records are overwritten when the buffer is full. –i specifies that the initial trace records are retained, that is, no more records are written to the buffer once it is full. The buffer size can be specified in either bytes of megabytes (MB). To specify the buffer size in megabytes, add the character m to the buffer size. For example, to start db2trc with a 4 MB buffer, enter the following command: db2trc on -l 4m

The buffer size must be a power of 2. The default and maximum trace buffer sizes vary by platform. The minimum buffer size is 1 MB. -t

August 2003

Includes timestamps. Applicable to UNIX-based environments only, where the logging of timestamps severely affects performance.

161


db2uext2 - DB2 User Exit Wrong usage of this function may make database backups unusable. The user exit is normally only called by the database engine. The syntax description here is only for qualified SAP support personnel and administrators who have been explicitly instructed to perform an action involving the User Exit by SAP support.

Unlike the other programs described here, the User Exit parameters allow no spaces before their arguments. The -SP and -LS parameters are only used if a raw device is used for logging. DB2 Parameters Passed to the User Exit Program Parameter

Description

-OS

Platform on which the instance is running, for example, AIX, Windows, Solaris, HP-UX and Windows 98

-RL

DB2 release level

-RQ

Request type, for example, archive or retrieve

-DB

Database name

-NN

Local node number

-LP

Fully qualified path to the log files. The path must contain the trailing path separator.

-LN

File name of the log file

-AP

TSM password. It is passed to the User Exit if it is provided in the database configuration.

-SP

Log extent starts at this number in steps of 4 KB pages of the device.

-LS

Size of the log extent in 4 KB pages

162

August 2003


db6adutl - SAP TSM Management Tool

Parameter

Description

QUERY

Queries the TSM server for log files.

EXTRACT

Copies log files from the TSM server to the current directory on the local machine.

DELETE

Either deactivates log files or deletes log files on the TSM server.

ARCHIVE

Performs action for log files in TSM archive copy group.

BACKUP

Performs action for log files in TSM backup copy group.

BOTH

Performs action for log files in TSM archive and backup copy group.

BETWEEN AND

Specifies that the logs between log sequence number 1 and log sequence number 2 are to be used.

DB

Considers only those objects associated with the specified database name.

NODE

Considers only those objects created by the specified node number.

WITHOUT PROMPTING

The user is not prompted for verification before objects are deleted.

Archived Admin DB safety files (adminDB..SAR) should be deleted using brrestore before changing from indirect archiving to direct User Exit archiving.

August 2003

163


db6util - Tool to Assist Database Administration

Parameter

Description

-h

Prints help text.

-V

Prints version information.

-n

Database name. The default value is the value of environment variable DB2DBDFT.

-auth

User authentication. If this option is not specified, db6util tries to retrieve the adm password from the DB2 password service.

-o

Log file. The default value is stdout.

-w

Result file. The default value is stdout.

-r

RUNSTATS on single tables and all indexes

-rf

RUNSTATS on tables provided in file first

-rv

RUNSTATS on tables with VOLATILE attribute. Tables flagged in DBSTATC with ACTIVE = N are not affected. The VOLATILE attribute is removed after RUNSTATS.

-f

Gets tablespace free space information.

-dg

Gets database parameter.

-dm

Modifies database parameter.

-mg

Gets database manager parameter.

-mm

Modifies database manager parameter.

-sd

Displays overview over deadlocked processes in application snapshot.

-sl

Displays overview over deadlocked processes and processes in lock wait status in application snapshot.

164

August 2003


dmdb6bkp - Database Backup Tool


Description

Specifies the SAP database name .

-NODExxxx

Partition number, for example, NODE0002

-ONLINE | OFFLINE

Specifies online or offline backup mode.

-ADSM OPEN SESSIONS

Number of I/O sessions to be used with TSM

-TO

Lists directory or tape device names.

-LOAD OPEN SESSIONS

Name of shared library containing the vendor backup I/O functions and the number of I/O sessions to be used

-BUFFERS

Number of buffers to be used

-BUFFERSIZE

Size, in pages, of the buffer used when building the backup image. The default value is 1024.

-PARALLELISM

Number of buffer manipulators to be spawned during the backup process. The default value is 1.

August 2003

165


dmdb6srp - Update Statistics Tool


Description

-n


-tALL

Performs the operation on all database tables.

-tDBSTAT

Performs the operation on all selected DBSTATC tables.

-tCALL

Calculates REORGCHK information on all database tables with current statistics.

-t.

Table name, for example, SAPR3.MONI

-z

Maximum amount of time (in minutes) for the execution of the program. The default value is 1200 minutes.

-m

To use a statistical option, specify one of the following letters:

-l

166

•

b Both tables and index statistics are gathered. This is the default value.

•

t Table statistics only

•

I Indexes only

•

d Table with extended (distribution) statistics

•

x Extended statistics for indexes only

•

e Both table (with distribution statistics) and basic indexes

•

y Extended statistics for indexes and basic table statistics

•

a Extended statistics for indexes and table statistics with distribution

Maximum amount of time (in minutes) spent on the calculation of the size of long fields. The default value is 1200 minutes.

August 2003


dmdb6rts - Table Reorganization Tool Command Parameters Parameter

Description

-n


-t

Specifies the name of the tablespace that contains the tables to be reorganized.

-m

Specifies the name of the tablespace where the DB2 Database Manager can temporarily store the table being reconstructed. The page size must match the page size of the tablespace specified with the –t switch.

August 2003

167


sddb6ins - Admin Tools Installation Program On UNIX, sddb6ins must always be called with root authorization but with the user environment for user db2 as described in Installation and Setup [Page 36].


Description

-h

Prints the usage of sddb6ins.

-V

Prints the version of sddb6ins.

-i []

Installs the Admin Tools. The default value of is the release of sddb6ins.

-u []

Upgrades the Admin Tools. indicates the release of the already installed Admin Tools (for example, 46D). The default value of is the default release of sddb6ins.

-r []

Recreates the Admin DB using the Admin DB backup SAR file.

-checkonly []

Checks if the Admin Tools are installed correctly. The default value of is the release of sddb6ins.

[-db2dbnamepwd ] [-sidadmpwd ]

All of these parameters have useful default values, except -db2dbnamepwd . The following default values apply:

Windows only: [-db2dbnamedomain ] [-sidadmdomain ]

•

The value of the USERDOMAIN environment variable is the default value for .

•

The value for -sidadmpwd is read from the dscdb6up password file.

For the installation of the 6.NN Admin Tools on Windows, the -db2dbnamepwd is mandatory.

168

August 2003


-s

Specifies the SAP system ID. The value of the SAPSYSTEMNAME environment variable is the default value for .

-DEB []

Activates the tracing facility. The detail level can be specified using . can be between 1 and 9. The default value is 3. The value 9 means "very detailed".

-NOADMDB

The Admin DB is not needed because log files are archived directly to the vendor storage product by the User Exit program.

August 2003

169


sddb6mir - Admin DB Creation and Mirror Tool Utility

Command Parameters

170

Parameter

Description

-m

Mirrors the Admin DB ADM to the SAP database .

-DEB []

Activates the tracing facility. The detail level can be specified using . can be between 1 and 9. The default value is 3. The value 9 means “very detailed”.

August 2003

Appendix B: Troubleshooting

Appendix B: Troubleshooting DB2 Traces The DB2 software provides several trace facilities to track down a variety of problem categories that might occur when running or developing DB2 applications. The decision to run a trace must be carefully met. The following facts should be taken into consideration: •

In most cases, system performance will decrease.

•

It is a time-consuming task.

•

The amount of trace data can be enormously large. For this reason, it is essential to find the smallest possible scenario that reproduces the problem. It might be necessary to drill down, or even stop other activities while recreating the problem situation with the trace turned on.

•

Interpreting trace data properly requires detailed low-level knowledge of DB2. For this reason, traces are usually only run at the request of DB2 support.

•

Problem situations that have been observed might disappear during attempts to reproduce them with the trace turned on.

All DB2 traces are documented in the Troubleshooting Guide of the DB2 Online Documentation delivered with the software.

DB2 Trace Facility db2trc The DB2 trace, controlled by the command db2trc, traces events. It must be run by the DB2 instance owner on the database server. To get an overview of the available options, run the command with no options. When turned on, options for the location of trace data are: •

In shared memory For this, the amount of memory available for tracing is limited by the amount of available shared memory. In many cases, this reduces the duration of the trace to a few minutes. Once the problem has been traced, trace data first needs to be dumped before the trace is turned off.

•

In a file This severely affects performance. Given the number of activities in an SAP environment, DB2 will probably come to a standstill.

To get the trace into readable form, trace data needs to be formatted on the system the trace was taken on.

August 2003

171


Usually, the sequence of activities using this kind of trace is as follows: ...

1. To store trace data in a preallocated buffer of size in shared memory, turn the trace on by entering the following command: db2trc on –l -s 2. Reproduce the problem. 3. Dump the trace data to a file named, for example db2trc.dmp, by entering the following command: db2trc dmp db2trc.dmp 4. Turn off the trace by entering the following command: db2trc off 5. Format the trace into two files, db2trc.fmt and db2trc.flw by entering the following command: db2trc fmt db2trc.dmp db2trc.fmt db2trc flw db2trc.dmp db2trc.flw While formatting, the output is written to the screen. If the output mentions that the trace has wrapped, the amount of memory reserved for trace data was insufficient and the trace might not be useful. The trace needs to be repeated with a larger amount of memory. Typical values to start with are 20000000 or 40000000 (20 or 40 million bytes).

DB2 CLI Trace The DB2 CLI trace traces all activities of the Call Level Interface (CLI), that is, the SQL interface of DB2. It is run on the machine running the DB2 application program. It is activated for an application process when the trace is turned on or the process connects to the database. There is no option to activate the trace for a process while it is being connected to the database.

Procedure ...

1. To turn the DB2 CLI trace on or off, log on as DB2 instance owner. 2. To activate the trace, parameter TRACE in the CLI configuration needs to be set to 1. To do so for all databases in a DB2 instance, enter the following command: db2 update cli cfg for section common using trace 1 3. To de-activate the trace, enter the following command: db2 update cli cfg for section common using trace 0 4. To configure the trace, several parameters can be set. Enter the following command: db2 update cli cfg for section common using 5. To verify the current settings, enter the following command: db2 get cli cfg

172

August 2003


Examples of Available Parameters Parameter

Description

TRACEFILENAME

Path of the file containing all trace data

TRACEPATHNAME

Directory to store trace files, one per process

TRACEFLUSH 0|1

If this parameter is set to 1 it forces a write to disk for each entry.

Admin Tools Traces You can activate all tracing activities of most SAP DB2 kernel tools using variable settings in environment file init.db6. This environment file is located in the $INSTHOME/admintools directory (UNIX) or on the environment settings (Windows, also Registry settings). Each component of the Admin Tools can be traced separately.

The trace output is intended for experienced support personnel and the application developers. There are often several items that appear to be errors, but are acceptable under certain conditions. Environment Variables Environment Variable

Values

DB2DB6_TRC_

ON or OFF Default value: OFF For example: DB2DB6_TRC_DB2UEXT2=ON

DB2DB6_TRC__LEVEL

Default value: 2 1 Errors Error tracing 2 Footprint Function internal info 3 DEV: Development trace

DB2DB6_TRC__PATH

Any path where db2 has write access. For example: DB2DB6_TRC_DB2UEXT2_PATH/db2/PRD/admintools/TraceFiles

DB2DB6_TRC__FILE

A fixed name for the file to be used for all traces, which is overwritten by each new trace. This should normally be left unset to use the default behavior of creating one file per trace.

In the table above, is DB2UEXT2, BRARCHIVE, BRRESTORE, SDDB6JDS, or BRDB6BRT. You can control brarchive and brrestore tracing using the command line option –DEB, which overrides the environment, or an optional trace level and trace directory. The trace level can have a value between 0 and 9.

August 2003

173


Tracing of sddb6ins can only be activated using the command line option -DEB. You can neither use the environment nor the ini file. Default Trace File Names Admin Tools

File Names

DB2 administration server

sddb6jds..trc

DB2 User Exit

db2uext2..trc

Log file management

brarchive..trc brrestore..trc

Data collector

dmdb6rdi..trc

Update statistics

dmdb6srp..trc

Redirected restore

brdb6brt..trc

Backup tool

dmdb6bkp..trc

DB6CLP (command line processor)

db6clp..trc

The default trace file directories are: UNIX: $INSTHOME/admintools/TraceFiles Windows: %INSTHOME%\admintools\TraceFiles

Downloading Admin Tool Patches from the SAP Service Marketplace You can download the latest available versions of the Admin Tools from the SAP Service Marketplace at the Internet address service.sap.com/patches.

For 4.6D Admin Tools •

For 32 Bit Choose SAP R/3 → SAP R/3 4.6C → Binary Patches → SAP KERNEL 4.6D → → DB2 UDB.

•

For 64 Bit Choose SAP R/3 → SAP R/3 4.6C → Binary Patches → SAP KERNEL 4.6D 64-BIT → → DB2 UDB.

174

August 2003


For 6.NN Admin Tools •

For 32 Bit Choose SAP Web AS → SAP Web AS → Binary Patches → SAP KERNEL → → DB2 UDB.

•

For 64 Bit Choose SAP Web AS → SAP Web AS → Binary Patches → SAP KERNEL 64-BIT → → DB2 UDB.

The following table provides an overview of the files available: File

Description

SAPCAR.CAR

Contains the extraction tool SAPSAR(.exe).

DB6CCEXT_HW.SAR

Contains the SAP DB2 Control Center Extensions.

sddb6ins.CAR/SAR

Contains the installation program sddb6ins(.exe).

dscdb6up.CAR

UNIX only and only for SAP system Releases 4.6x

Keep in mind that HW stands for UNIX if the DB2 Control Center runs on UNIX, or I386 if it runs on Windows.

August 2003

175


Troubleshooting Admin Tools and Programs The following section provides information and advice on how to proceed when problems occur.

General Troubleshooting Comments Many recent customer problems were caused by problems with the runtime environment. An installation may not have completed successfully, or a user inadvertently changed the environment. For example, an environment variable was changed or unset, or file or directory authorizations were changed. You can now check the runtime environment for the Admin Tools by calling sddb6ins –checkonly. Password file problems are also common. Use dscdb6up to resynchronize passwords. For detailed information, see Setting and Updating Passwords [Page 108]. Also, check that the operating system user has not revoked users. Problems with devices are a frequent issue. Older versions of the Admin Tools, that is, SAP system Release 3.1I, are not compatible with TSM. ADSM must be still used in this case. Initializing tapes with non-default values, for example, with a different block size, will cause problems if these values conflict with the parameters in those in the Admin DB. For more information, see Changing the Admin Tools Configuration [Page 64]. If brrestore or brarchive can not terminate properly, a lock file may be left behind. Running the tool again will cause an LFA158 error that will contain the path of the lock file. Ensure that neither brarchive nor brrestore are running and delete the file. Often, defects that you find have already been found and addressed. Downloading and installing the latest patch from the SAP Service Marketplace will often resolve the problem. For more information, see Downloading Admin Tool Patches from the SAP Service Marketplace [Page 174] in this appendix. Customers occasionally run out of disk space. Make sure that the DB2 protocol file db2diag.log in the db2dump directory has not become too large. Running brarchive will free up space by removing log files from the archive directory. If the database cannot be started, it may be necessary to first temporarily move some log files out of the log directory to provide space. It is important to choose only the most recent inactive files as the user exit and brarchive will move older files first. Make sure that files are no longer active before moving them. As soon as enough space is available, when the User Exit has archived several files, brarchive should then be called and the log files in temporary storage moved back to the log directory.

Initial Troubleshooting Steps Implement the following steps until the problem is resolved. ...

1. Look in the protocol file for the error. 2. Look up the meaning of the error message in this appendix. 3. Perform the suggested action. 4. If there is an SQL return code in the error message, look it up in the DB2 Message Reference and perform the recommended action. 5. Search SAP Notes for solutions to previous occurrences of this error.

176

August 2003


6. Check the SAP Service Marketplace at the Internet address service.sap.com/patches for new patches. Download and install the latest patch version. 7. If not, run the installation tool with the -checkonly option and check the output. Make any appropriate adjustments to the runtime environment. 8. Switch on Admin Tool tracing. Run the action again. Create a message using SAPNet − R/3 Frontend. Upload the protocol and trace files, the protocol file init.db6, and any core file to the SAP server. To ensure efficient troubleshooting, go through the above steps and gather as much information as possible about the problem before contacting SAP support.

Installation (sddb6ins) Troubleshooting Installation Failure In general, if the installation or upgrade has not completed successfully, you can retry this operation without causing data loss. The following error can occur during the installation of the Admin Tools: Error Message

Error opening file with mode wb.

Reason

File cannot be replaced because the executable or shared library is currently in use.

Action Required

Retry installation or upgrade operation.

Common Installation Problem The password file is inconsistent with the operating system; variable DB2DB6EKEY changed. The passwords given when installing the SAP system or using the dscdb6up utility must match the operating system passwords for the same users. DB2DB6EKEY must have the same value on all systems. By default, the value is based on the machine name, which is often different on multi-node systems (DB2 UDB EEE V7 or DB2 UDB ESE V8 with multiple partitions).

August 2003

177


User Exit Troubleshooting The following errors might occur: •

The log directory (database parameter LOGPATH) becomes full.

•

No new log files appear in the archive directory.

•

Entries in diagnostic file db2diag.log and db2alert.log or db2uext2.err.NODEXXXX (located in the DIAGPATH database parameter) report a User Exit failure.

To check the reasons for these errors, you can use the following procedure that is valid for the ARCHIVE and RETRIEVE actions of the DB2 User Exit program: ...

1. Look at the db2uext2 protocol and error files. These files are located in the DIAGPATH database parameter and are written for each database partition. The file names are as follows:

Protocol file: db2uext2.log.NODEXXXX

Error file: db2uext2.err.NODEXXXX

2. Look at the journal page for User Exit entries. See the following table for details. For the User Exit error message, the return codes have the following description: User Exit Error Messages Return Code

Description

0

Successful

1

DB2 shared library not found. For detailed analysis, activate the tracing facility. On UNIX: Verify if environment variable INSTHOME is set correctly.

4

Temporary error encountered (system memory). Retry. If the problem persists, contact your system administrator and SAP support.

5

TSM shared lib could not be loaded. Check if the specified shared library is in the path of the DB2 instance owner: UNIX: libApiDS (for 64bit libApiDS64) Windows: ADSM32.dll If files are being archived directly to TSM by the User Exit, check environment variable DB2DB6_VENDOR_UEXIT.

6

Function module in the shared library could not be loaded. Check previous error messages.

8

The User Exit returns this value to the DB2 Database Manager for most failures. The DB2 Database Manager automatically retries archive operations again after five minutes. If processing of the same log file fails repeatedly, check the error file db2uext2.err.NODExxxx for more detailed information.

12

Hardware error

16

Error with the User Exit program or system function used by the program, for example, signal handling). Retry. If the problem persists, contact SAP support.

178

August 2003


20

Error with one or more of the parameters passed to the User Exit program. Check whether you are using a version of db2uext2 that is compatible with the database product release.

23

Admin DB access error. Contact SAP support.

24

The User Exit program was not found. This return code is recorded in the db2diag.log. Check if program db2uext2 is available in the following directories: UNIX: $INSTHOME/sqllib/adm/ Windows: :\sqllib\bin\

28

I/O fail

30

File I/O error. Accompanying message should contain the system error number defined in file errno.h. •

35

Archive operations: If the source is the log file to be archived by the DB2 User Exit, this is just a warning. The log file has probably been moved out of the online log directory. Make sure that the log file is still available. If not, you may have problems if a database recovery becomes necessary.

•

Retrieve operations (ROLLFORWARD): If the DB2 User Exit was called to retrieve log files, the log file could not be found in either the archive or the retrieve directory. Attempt to retrieve it using brrestore. If the file does not exist (and never has existed) you are finished and can perform ROLLFORWARD STOP. This is a safety feature to ensure that the ROLLFORWARD is not accidentally stopped when log files still exist that could be recovered using brarchive. This is deliberately different to the default (simplified) User Exits provided as source code together with DB2.

36

Opening file failed. Log file does not exist. Check error file db2uext2.err.NODExxxx for more detailed information. Retrieve operations (ROLLFORWARD): This is not necessarily an error. Ensure that the file has never existed and then perform ROLLFORWARD STOP.

37

Opening file failed. Check the messages and access authorizations. Check error file db2uext2.err.NODExxxx for more detailed information.

38

Reading from file failed. Wait to see if db2uext2 succeeds at a future attempt. If not, check at the file system level. Check error file db2uext2.err.NODExxxx for more detailed information.

39

Writing to file failed: The log file could not be copied to the archive directory. There could be a free space problem. Check error file db2uext2.err.NODExxxx for more detailed information.

40

Closing of log file failed.

August 2003

179


41

Creating directory failed. Check the file system access authorizations. Check error file db2uext2.err.NODExxxx for more detailed information. It may be necessary to modify the environment variables DB2DB6_ARCHIVE_PATH and DB2DB6_RETRIEVE_PATH.

43

Unable to read environment variable. Check error file db2uext2.err.NODExxxx for more detailed information.

44

Error when accessing file. Check the file system access authorizations. Check error file db2uext2.err.NODExxxx for more detailed information (for example, errno).

45

RC_AUDIT_IO_ERROR: Problem writing to db2uext2.err.NODExxxx or db2uext2.log.NODExxxx

46

File I/O seek error. Check error file db2uext2.err.NODExxxx for more detailed information (for example, errno).

48

SQL error occurred. Check error file db2uext2.err.NODExxxx for more detailed information. Check the SQL code in DB2 Message Reference.

49

SQL warning occurred. Check error file db2uext2.err.NODExxxx for more detailed information. Check the SQL code in DB2 Message Reference.

90

Windows only: Environment variable DSCDB6HOME not set. Check your environment.

91

Environment variable DB2DB6EKEY not set. Check your environment.

92

Password could not be retrieved from password file dscdb6.conf. On UNIX Check the file authorizations of /sapmnt//global (permission 700, owner adm, group dbadm) and /sapmnt//global/dscdb6.conf (permission 600, owner adm, group sapsys). On Windows Check if share \\DSCDB6HOME\sapmnt is available and if the DB2 service has been started by user db2.

93

Environment variable DB2DBDFT not set. Check your environment.

101

Database management (DBM) parameter DFTDBPATH is not set or could not be retrieved. Try to retrieve this parameter on the command line. Check the database environment.

102

Database management (DBM) parameter DIAGPATH is not set or could not be retrieved. Try to retrieve this parameter on the command line. Check the database environment.

104

Log file ID cannot be obtained. Any log file to be archived by the DB2 User Exit is assigned a unique ID. It is generated in the Admin DB. If this error occurs, check the ADM database of your managed instance and contact SAP support.

110

Recording information failed: An action step cannot be protocolled in the Admin DB. Processing continues. Check error file db2uext2.err.NODExxxx for more detailed information.

180

August 2003


111

Protocol database connect error. Check from the DB2 command line (CLP) whether a connection to ADM can be established. If not, contact SAP support.

112

ADM object creation failed. No log file entry can be made in the Admin DB. Processing cannot continue. Check error file db2uext2.err.NODExxxx for more detailed information.

115

Last backup timestamp could not be retrieved from the database recovery history file and could not be inserted in the Admin DB. Check error file db2uext2.err.NODExxxx for more detailed information.

116

The vendor user exit program could not be started. Check error file db2uext2.err.NODExxxx for more detailed information.

117

Vendor user exit program error. For detailed information, refer to the vendor user exit documentation.

brarchive/brrestore Troubleshooting Checking the protocol output and corresponding corrective actions can solve most problems involving brarchive and brrestore.

However, when called directly on the command line the protocol is by default only written to file and no output appears on the command line. To override this, option -out must be included in the program call. If an application does not perform correctly, make sure that you are using the most recent patch available in the SAP Service Marketplace at the Internet address service.sap.com/patches. The version and patch level of each program can be seen near the top of each protocol. As mentioned above, in addition to the standard environment tracing switches, brarchive and brrestore also allow activation of tracing on the command line using parameter -DEB , for example, 1…9.

brarchive and brrestore Messages The following tables list brarchive and brrestore message IDs, their texts, and suggested corrective actions. •

brarchive and brrestore information messages have no numbers - only warnings and errors.

•

LFA000 to LFA099 are brarchive and brrestore warning messages.

•

LFA100 to LFA999 are brarchive and brrestore error messages.

•

VND000 to VND999 are back end library error messages.

August 2003

181


brarchive and brrestore Warning Messages ID

Message

Comment

LFA000

Action ended with warnings.

Main action completed successfully but warnings were issued. See other messages for more information.

LFA001

syntax supported but archaic. Use new syntax.

Although the program still supports this old command line option it may not in future releases. It is better to use the new syntax. Use option –help to see the syntax.

LFA002

syntax archaic and ignored. Use new syntax.

A command line option was ignored because it is no longer valid syntax. Use the new syntax. Use option – help to see the syntax.

LFA005

option supported but archaic. Use .


LFA006

-s with two devices and volumes supported but archaic. Use ss.


LFA007

-sd with two devices and volumes supported but archaic. Use ssd.


LFA009

Option
cannot be written to.

Only a problem if other messages reflect this.

LFA070

Tape has SID and not as expected!

Tape belongs to another system. If you are certain this tape is not needed, the tape header must be overwritten using operating system commands, for example, tar, cpio, or dd.

184

August 2003


LFA071

Tape # has been reused # times (>#) and should be replaced.

This tape must be replaced as soon as possible. It is no longer reliable.

LFA073

Bad environment value ? =
The environment variable value may be invalid. Check this variable in the user environment.

brarchive and brrestore Error Messages ID

Message

Comment

LFA101

Parameter

SAP Web as 6.20 DB2 UDB v7 Administration Guide

Recommend Documents