adm535_db2_unix_n_nt

7/31/2019 ADM535_DB2_unix_n_nt

http://slidepdf.com/reader/full/adm535db2unixnnt 1/372

© SAP AG ADM535 0-1

SAP AG 2004

<leave this slide blank>

ADM535 Database Administration DB2 UDB

Diese Seite wird von Andrea für euch noch erstell!

FS310 Inkasso/Exkasso

© SAP AG 2005

Database Administration DB2 UDB for Unix and

Windows

SAP Web AS 6.40

2005/Q4

50074144




SAP AG 2004

Copyright 2005 SAP AG. All rights reserved.

No part of this publication may be reproduced or transmitted inany form or for any purpose without the express permission of

SAP AG. The information contained herein may be changed

without prior notice.

Copyright

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

other software vendors.

Microsoft, Windows, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400,

iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, and Informix are

trademarks or registered trademarks of IBM Corporation in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or

registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web

Consortium, Massachusetts Institute of Technology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and

implemented by Netscape. MaxDB is a trademark of MySQL AB, Sweden. SAP, R/3, mySAP, mySAP.com, xApps, xApp, and other SAP products and services mentioned herein as well as

their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective

companies. Data contained in this document serves informational purposes only. National product specifications

may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated

companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and

SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP

Group products and services are those that are set forth in the express warranty statements accompanying such

products and services, if any. Nothing herein should be construed as constituting an additional warranty.




SAP AG 2003

This course is intended for the following audiences:

Database administrators

SAP system administrators

Project team members

Duration: 3 Days

Target Audience

User notes

These training materials are not a teach-yourself program. They complement the explanations provided by your course instructor. Space is provided on each page for you to note down additional

information.

There may not be sufficient time during the course to complete all the exercises. The exercises provide

additional examples that are covered during the course. You can also work through these examples in

your own time to increase your understanding of the topics.




SAP AG 2003

Required Knowledge:

SAPTEC (mySAP Technology Fundamentals)

Working knowledge of the DB2 UDB database for Unix and

Windows operating systems

Recommended Knowledge:

ADM100 (mySAP Technology Administration)

Course Prerequisites




SAP AG 2005

Definitions

28

Tools for DB2

Database Servers

1 Definitions

4 Log File Management

5 Managing Backups

7 Storage Management

9

11 Problem Analysis

10 Ongoing Operations

6 Database Recovery 12 Appendix

3 PartitioningDB2 Performance

Monitoring and Tuning

Database Architecture




SAP AG 2005

DB2 in Brief

DB2 Processes

DB2 Environment

User Management

1 Definitions

Storage Structures

Definitions




SAP AG 2004

DB2 UDB Version 8 Packaging

DB2 UDB Enterprise Server Edition

Contains single-partition and multi-partition features

SAP requires that you use the partitioning feature for SAP

products that have been approved for use of this feature

The ESE product provides the ability to create and manage multiple database partitions. SAP customers

with DB2 licenses from SAP have the appropriate license to use the database partitioning feature.

SAP installations with SAPinst always install a database instance that is enabled for multiple partitions,however OLTP systems must be set up with a single partition.

As an additional consideration, the decision must be made to create multiple database partitions on a

single SMP server or to create multiple database partitions across more than one physical server (that is,

a clustered hardware configuration).




SAP AG 2004

DB2 UDB Version 8

SAP technology releases

DB2 UDB version

For further information, refer to:

http://service.sap.com/platforms

http://www.ibm.com

3.1I 4.0B 4.5B 4.6C 6.10 6.20 6.30 6.40 7.00

SAP Solutions and DB2 Releases

For current information regarding supported combinations of SAP software and the IBM DB2 Universal

Database, refer to SAP Service Marketplace at http://service.sap.com.

End-of-Service is declared by IBM for DB2 databases in IBM Document 1039854. To find it, search onhttp://www.ibm.com for this number.

There is a SAP Process describing support for DB2 Versions which have end-of-service (SAP Note

677786).

Pay special attention to upgrade restrictions that apply to particular combinations.

You may encounter names other than the IBM DB2 Universal Database (UDB) used as synonyms in

SAP documentation (such as the SAP Notes):

DB2 Common Server (or DB2 CS for short)

DB2 UDB

DB6 is the internal abbreviation for DB2 Universal Database technology on UNIX and Windows. Donot use DB2 when searching for information in SAPNet because this abbreviation is used for DB2 on

z/OS.




SAP AG 2005

DB2 Platforms

Hardware OS

Intel

pSeries

AMD64

HP

SUN

Linux

Windows

AIX

HP-UX

Solaris




SAP AG 2004

Technical Overview

F i l e L e v e l

DB2 Instance

Configuration

DB2 Database

Configuration

DB2 Log files DB2 Containers

db2gdsdb2gds

db2wdogdb2wdog

db2syscdb2sysc

db2ipccmdb2ipccm

db2tcpcmdb2tcpcm

Buffer Pool(s)

Package Cache

db2loggwdb2loggw

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr db2pclnr db2pclnr

db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

D B 2 I n s t a n c e a n d D a t a b a s e L a y e r

Database Heap

Utility Heap

db2agentdb2agentdb2agentdb2agent

…

…

…

WorkWork processprocess WorkWork processprocess …

Memory areas Processes/Threads

…

The above picture is a simplified representation of DB2’s architecture. In general, a “DB2

Universal Database for Unix and Windows” installation consists of a DB2 instance and at leastone DB2 database.

The most important memory areas that reside in the database memory are the buffer pool(s),

the database heap, lock list and the package and catalog caches.

DB2 agents include coordinator agents and subagents, and are the most common type of DB2

processes that carry out the bulk of SQL processing on behalf of applications (for example, SAPwork processes). DB2 assigns a coordinator agent with an application, and this agent

coordinates the communication and processing for this application.

In general, there is a 1:1 relationship between SAP work process and the DB2 (coordinator)

agent.

In contrast to Unix, the DB2 implementation on Windows is multi-threaded, meaning that we

will only see one process (named db2syscs). All other processes, that we see on Unix, run asthreads within the program db2syscs.exe.




SAP AG 2004

DB2-Instance

db2<dbsid2>

SAP and DB2 Instances: Overview

Server

Database

<DBSID2>

DB2-Instance

db2<dbsid1>

Database

<DBSID1>

SAP-System

<SAPSID1>SAP-System

<SAPSID2>

A DB2 instance is an execution environment for DB2 commands, databases, and tools.

It is also referred to as a DB2 database manager.

More than one instance can be defined on a server machine and each of them can be managedindependently.

There is a one-to-one relationship between a DB2 instance db2<dbsid> and a SAP system <SAPSID>.




SAP AG 2004

DB2 Instance

db2<dbsid1>

Database

<DBSID1>

Default Database Connection

Connect user and password:

Connect user is defined by environment variable

'dbs_db6_schema' und SAP profile parameter

'dbs/db6/schema'

Password is retrieved from DB6 password file:

UNIX: /usr/sap/<SAPSID>/SYS/global/dscdb6.conf

Windows:

\\%DSCDB6HOME%\sapmnt\<SAPSID>\SYS\global\dscdb6.conf

environment variable 'DB2DB6EKEY' is used for password

decryption

Password file 'dscdb6.conf':

Maintenance of passwords with program 'dscdb6up':dscdb6up <username> <password>

dscdb6up -create <connect_user password>

<sidadm password>

Connect user is owner of all SAP tables

Database name / instance:

DB2INSTANCE

DB2DBDFT

DB2DBDFT=<DBSID>

SAP System <SAPSID>

SAPSYSTEMNAME=<SAPSID>

DB2INSTANCE=db2<dbsid>

dbs_db6_schema=sap<dbsid>

DB2DB6EKEY=<key>

DSCDB6HOME (Windows only)

This slide shows the important parts of database connection:

Environment settings

Users

Password Encryption:

The dscdb6up must be executed by <sapsid>adm.




SAP AG 2004

DB2 DatabasesDB2 Databases

Database Objects

DB2 Instance

DB2 Databases

DB2 TablespacesDB2 TablespacesDB2 Tablespaces

IndexesIndexes

TablesTables

C11#STAB

SMS-managed data

DB files grow and shrink

Only file systems

C11#BTABD and C11#BTABI

DB2-managed storageSpace in containers preallocated

File systems and raw devices

There are logical and physical database objects.

You can create more than one instances on one server, and more than one database on one instance.

Databases contains bufferpools, tablespaces, and temporary tablespaces.

Tablespaces contains tables, indexes and views.

If you use DMS, Tablespaces are stored on filesystem level in containers. If you use SMS than

tablespaces are saved in path, and for each object the database will create one file in this path.

DMS ist the default tablespace storage type for data and index tablespaces. SMS ist the default

tablespace storage type for temporary tablespaces.




SAP AG 2005

DB2 Logging Overview

db2loggr

History file

db2logmgr

db2tapemgr

log_dir

mirror

log_dir

failarch

path

TSM

Vendor

Disk

overflow

logpath

Tape

db2loggw

db2agent

L o g B u f f e r

This slide shows the concept of the log file management of DB2 Version 8.2 including the two new

components, the DB2 log manager (db2logmgr) and the DB2 tape manager (db2tapemgr).

The DB2 log manager is the central component for managing log files. It is part of the DB2 engine and isresponsible for archiving and retrieving log files.

The DB2 log manager supports two archiving locations (so that is possible that a log file can be stored

on two different locations), for example, TSM, disk or vendor products.




SAP AG 2004

DB2 in Brief

DB2 Processes

DB2 Environment

User Management

1 Definitions

Storage Structures

User Management




SAP AG 2005

DB2 Users and Groups

Database System Administrator Group:

db<dbsid>adm (SYSADM_GROUP)

Users: db2<dbsid>

Database System Control Group:

db<dbsid>ctl (SYSCTRL_GROUP)

Users: <sapsid>adm

Database Maintenance Group:

db<dbsid>mnt (SYSMAINT_GROUP)

Users: sap<sapsid>

The operating system user db2<dbsid> is the DB2 instance owner and the SAP database administrator.

This user belongs to the operating system group db<dbsid>adm.

This operating system user <sapsid>adm is authorized to start and stop the SAP system and the DB2Database Manager (instance). <sapsid>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 db<dbsid>ctl.

Various connect users are possible, depending on your SAP release and setup. These operating system

users – sap<sapsid1>, sap<sapsid2>, sap<sapsid3>, and so forth, and sapr3, – 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 user IDs. These users belong to the operating system group

db<dbsid>mnt and are created only on SAP systems that have the SAP system database installed (not

on remote application servers). These users have the following database authorizations: CREATETAB,

BINDADD, CONNECT, and IMPLICIT_SCHEMA.




SAP AG 2005

Database authority Description

SYSADM Users with this authority can perform

database administration and have

privileges to access all database tables.

SYSCTRL Users with this authority can perform

database administration, but have noprivileges to access data in the

databases (highest level of system

control authority).

Implicit connections to the database –

requiring no password – can be made

by users having SYSCTRL authority.

SYSMAINT User with this authority can perform

maintenance operations on all

databases in the instance.

DB2 Database Authorizations

SYSADM is the highest level of administrative authority. Users with SYSADM authorization can run

utilities, issue database and database manager commands, and access the data in any table in any

database within the database manager instance. This authorization can control all database objects in the

instance, including databases, tables, views, indexes, table spaces, node groups, buffer pools, eventmonitors, and more. Only a user with SYSADM authorization can perform functions such as Change

database manager configuration or Grant DBADM authority.

SYSCTRL is the highest level of system control authority. Users with SYSCTRL authorization can

perform maintenance and utility operations against the database manager instance and its databases.

These operations can affect system resources, but they do not allow direct access to data in the databases.

SYSCTRL authorization is assigned to the group specified by the configuration parameter

SYSCTRL_GROUP. Only a user with SYSCTRL authorization (or higher) can force users off the

system, create or drop a database, create/drop/alter a table space, or restore a new database. Users with

SYSCTRL authorization also have the implicit privilege to connect to a database.

SYSMAINT is the second level of system control authority. It does not allow direct access to data. This

authority allows to update database configuration files, to back up a database or a tablespace, to restorean existing database and to monitor a database.




SAP AG 2005

DB2 in Brief

DB2 Processes

DB2 Environment

User Management

1 Definitions

Storage Structures

Storage Structures




SAP AG 2004

Storage Structures in Windows

<drive1>:\sqllib – DB2 software

<drive2>:\db2<dbsid> – DB2 instance directory

<drive3>:\db2\<DBSID>\log_dir – Online log files

<drive5>:\db2\<DBSID>\log_archive – Archived logs

<drive4>:\db2\<DBSID>\log_retrieve – For log file retrieval during recovery

<drive6>:\db2\<DBSID>\db2dump – DB2 diagnostic log and service information

<driveN>:\db2\<SAPSID>\sapdata1...n – SAP database files

In the SAP DB2 database server environment, directory and file names are standardized on Windows.

During the installation of the SAP system on Microsoft Windows, the user is prompted for the drive

locations of the above directories. Note: Windows does not offer a single directory tree with mountedfile systems.

The following list explains the usage of the directories. You can put many of the directories on the same

drive, depending on the system usage:

\sqllib – location for the DB2 executable programs

\db2<dbsid> - DB2 instance directory containing instance configuration data

\db2\<DBSID>\log_dir – directory where DB2 log files are created and used by the database

\db2\<DBSID>\log_archive – directory containing old log files, which are not used by the database

\db2\<DBSID>\log_retrieve – directory for storage of the log files during recovery

\db2\<DBSID>\db2dump – directory with diagnostic information

\db2\<SAPSID>\sapdata1\[NODE0000]... – directories that are used to store DB2 data




SAP AG 2004

Storage Structures in UNIX and Linux

/

/db2/db2<dbsid> - Home directory of user db2<dbsid>

/db2/<DBSID>

/db2/<DBSID>/db2<dbsid> - DB2 instance directory

/db2/<DBSID>/log_dir - Online log files

/db2/<DBSID>/log_archive – Archived logs (used by old log file

management)

/db2/< DBSID>/log_retrieve – For log file retrieval during recovery

/db2/<SAPSID>/sapdata1..n – SAP database files

/opt/IBM/db2/V8.1 – DB2 software (HP-UX, Solaris, Linux)

/usr/opt/db2_08_01 – DB2 software (AIX)

/db2/< DBSID>/db2dump – database diagnostic path

On UNIX systems, the DB2 V8.1 software is installed in /opt/IBM/db2/V8.1.

The DB2 Instance Data is stored in the directory /db2/db2<dbsid>.

The Instance Database Directory is located in /db2/<DBSID>/db2<dbsid>.

The files of the SAP Database are stored in the directories /db2/<sapsid>/sapdata1/[NODE0000] ...

/db2/<sapsid>/sapdataN/[NODE0000], and /db2/<sapsid>/sapdatat or

/db2/<sapsid>/saptemp<x>/[NODE0000].

The DB2 log files will be written to /db2/<dbsid>/log_dir by the DB2 database software.

The DB2 log files will be copied to /db2/<dbsid>/log_archive when not used any more.

For DB2 recovery purposes, SAP installs a log_retrieve directory. The log files will be stored here

during a database recovery.




SAP AG 2005

Prefix Abbreviation Extension

<SAPSID># <TS_name> D (data) or

I (index)

Physical

layer

Logical

layer

Tablespace

name

Directory

path

Container

name

Prefix Tablespace name Ext. Meaning Used bySYSCATSPACE DB2 Data Dictionary DB2 RDBMS

PRD# TEMP Sort Processes, temp tables, reorg DB2 RDBMS

PRD# USER1 D or I Default tablespace DB2 RDBMS

PRD# EL<Release> D or I Development environment loads SAP Basis

PRD# ES<Release> D or I Development environment sources SAP Basis

PRD# LOAD D or I Screen and report loads (ABAP) SAP Basis

PRD# SOURCE D or I Screen and report sources (ABAP) SAP Basis

PRD# DDIC D or I ABAP Dictionary SAP Basis

PRD# PROT D or I Log-like tables (for example, spool) SAP Applications

PRD# CLU D or I Cluster tables SAP ApplicationsPRD# POOL D or I Pool tables (for example, ATAB) SAP Applications

PRD# STAB D or I Master data and transparent tables SAP Applications

PRD# BTAB D or I Transaction data, transparent tables SAP Applications

…

PRD# ODS D or I Operational Data Storage (BW) SAP BW

Physical

layer

Tablespace Naming Conventions

<SAPSID> = PRD

PRD#BTABD

PRD#BTABD.

container000

PRD#BTABD.

container001

The DB2 database uses tablespaces. From a logical point of view, a tablespace holds database objects,

such as tables and indexes. On disk, a tablespace consists of one or more data files, known as

containers. The capacity of a tablespace can be increased by adding containers to it.

With <SAPSID> being PRD, the SAP naming convention for tablespace names is

PRD#<tablespace_name><extension>.

The abbreviations in the tablespace name are part of the container name. Containers are numbered

starting with 000. A full path specification for a DB2 container for SAP system PRD would be:

/db2/PRD/sapdata1/NODE0000/PRD#BTABD.container000 (UNIX)

h:\db2\PRD\sapdata1\NODE0000\PRD#BTABD.container000 (Windows)

Note: The SAP system and SAP tools (such as SAP Storage Management in ST04) suggest that the

naming conventions be observed. Following the SAP naming convention ensures fast and efficient

support from SAP.




SAP AG 2005

DB2 in Brief

DB2 Processes

DB2 Environment

User Management

1 Definitions

Storage Structures

DB2 Processes




SAP AG 2004

Important Processes and Threads

db2agentdb2agent

Manages DB2 logs files

Writes from log buffer to log file

Manages preallocation of DB2 log files

Manages the deadlock detection in the database partition

Handles read-ahead of large quantities of data as soon as requested by DB2 to

buffer pool(s)

The DB2 system controller EDU

db2loggr db2loggr

db2wddb2wdoogg

db2loggwdb2loggw

db2tcpcmdb2tcpcm

db2ipccmdb2ipccm

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

db2syscdb2sysc

Services remote TCP/IP connection requests

Services local IPC connection requests

Monitors the process hierarchy on UNIX servers to handle abnormal process termination

The corresponding EDU to a application process

Picks dirty pages from buffer pool(s) for copy to DB2 containers

• This slide shows important DB2 EDUs.

• EDUs (Engine dispatchable units) are processes on UNIX or threads on Windows.




SAP AG 2005

Differences Between Windows and UNIX

W i n d o w s O p e r a t i n g S y s t e m

U N I X O p e r a t i n g S y s t e m

Process - based Thread-based

db2syscs.exe

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm


db2agentdb2agent

db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

db2tcpcmdb2tcpcm


db2agentdb2agent

db2loggwdb2loggw

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm

db2agentdb2agent


db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

db2tcpcmdb2tcpcm

db2agentdb2agentdb2agentdb2agentdb2agentdb2agent

db2loggwdb2loggw

The DB2 implementation on Windows is multi-threaded. As with UNIX, there are three distinct

functional groups, but they are implemented as threads. All threads run within the Windows program

db2syscs.exe.

Use the Windows resource tool kit program qslice to view the threads in a program. With this program,

you can monitor the growing number of threads in program db2syscs during SAP startup.

More information can be accessed using the Windows Performance Monitor perfmon.exe, which is

located in the administrative program group.

Except for the above mentioned differences, the implementations of DB2 on UNIX and Windows are

very similar.




SAP AG 2004

DB2 in Brief

DB2 Processes

DB2 Environment

User Management

1 Definitions

Storage Structures

DB2 Environment




SAP AG 2004

DB2 Profile Registry: db2set

Centralized control of the DB2 environment settings

Restart of system after changes of DB2 registry

Various levels:

Platform-specific environment variables

DB2 instance-level profile registry

Variables set at this level apply only to a specific instance.

DB2 global-level profile registry

Variables set at this level apply globally to all instances.

DB2 instance-node-level profile registry

Variables at this level apply to a specific partition in a multi-partitionenvironment.

DB2 instance profile registry

Contains a list of all instances in the system. The command db2ilist, whichlists all instances in a system, uses this registry as input.

DB2 profile registry is a centralized repository that stores most DB2 specific environment information.

Depending on the operating system platform where DB2 is installed, variables stored in the DB2 profile

registries may be different. List all DB2 registry variables currently set: db2set -all

Display global registry variables: db2set -g

Display instance registry variables: db2set -i <instname>

Set a DB2 registry variable: db2set <parameter>=<value>

- e.g. db2set DB2COMM=TCPIP

Please note: Restart the instance for new changes to take effect !




SAP AG 2004

DB2 Profile Registry: OS Dependencies

Windows

Control Panel -> System -> Environment Variables

HKEY_LOCAL_MACHINE->SOFTWARE->IBM->DB2

Set DB2INSTANCE=DEV

UNIX

.profile, .kshrc, or .cshrc is typically incorporated with

db2profile (Bourne or Korn shell) or db2cshrc (C shell)

The instance owner's home directory

Export DB2INSTANCE=DEV

Environment variables are set at the operating system level.

Most environment variables are set automatically during or after DB2 installation




SAP AG 2004

db2level: DB2 FixPak Information

What's the current DB2 installation level?

command: db2level

DB21085I Instance "DB2" uses "32" bits and DB2 code release

"SQL08010" with level identifier "01010106".

Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak

"0". Product is installed at “/opt/IBM/db2/v8.1".

32 or 64 bit

installation directory

FixPak

level




SAP AG 2004

DB2_Workload

1. Call ST04 – Choose

Configuration - CLPCommands.

2. Select “DB2 Profile Registry” (theselection is transfered into SQL).

3. Use the scroll bar on the right side, copy and paste

the output below the header line into check table

“db2set output,” and save the check.

Since DB2 Version 8.2.2 there is a new profile registry variable DB2_Workload. In SAP environment

you should set this variable to SAP (SAP Note 825392). This variable activates all settings that are

important for an SAP system in an implicit way.

In emergency case it is possible to overwrite DB2 registry variables which are activated implictly.




SAP AG 2004

Definitions: Unit Summary

You should now be able to:

Use the basis knowledge of DB2 UDB

Describe the different environment levels of DB2

Set the recommended registry profile settings

Describe the user management




SAP AG 2004

Do the exercises

?

Check solutions to the exercises

Unit Actions




SAP AG 2004


1 Definitions

8

Tools for DB2

Database Servers2

4 Log File Mangement

5 Managing Backups


9

11 Problem Analysis









SAP AG 2004


Contents:

Database Process Model

DB2 in Action: Select and Update/Insert/Delete

Objectives:

At the end of this unit, you will be able to:

Describe the operational model of your DB2 Universal Database

Describe the packaging and installation options of DB2 in SAP

environments

Perform software maintenance for your database server

Describe the working model of your database server

Explain the authentication concept of your SAP DB2 database




SAP AG 2003

Starting and Stopping

Processing

2 Architecture





SAP AG 2004

F i l e L e v e l

D

B 2 I n s t a n c e L a y e r

DB2 Instances in SAP Environments

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm

DB2 Instance

Configuration

db2tcpcmdb2tcpcm

startsap db

startdb

db2start

Engine Dispatchable Units (EDUs)

are processes in UNIX

or threads in Windows

SAP provides scripts to start the SAP server. The startsap script starts the database instance using the

startdb script, which will in turn starts the db2start DB2 command.

With db2start, the following per-instance Engine Dispatchable Units (EDUs) are started: db2wdog – The DB2 watchdog EDU. This EDU is used to monitor the process hierarchy on UNIX

servers to handle abnormal process termination

db2sysc – The DB2 system controller EDU

db2tcpcm – The EDU responsible for servicing remote TCP/IP connection requests

db2ipccm – The EDU servicing local IPC connection requests

The configuration for the DB2 instance is stored in the instance configuration file.

The content of this internal binary file can be displayed with the DB2 command:

DB2 GET DBM CFG

The file's content can be modified with the command:DB2 UPDATE DBM CFG USING <parameter> <value>




SAP AG 2004

F i l e L e v e l

D B 2 I n s t a n c e L a y e r

DB2 Databases in SAP Environments: EDUs

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm

DB2 Instance

Configuration

D B 2 D a t a b a s e L a y e r

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

db2tcpcmdb2tcpcm

startsap db

startdb

db2start

db2 activate db

DB2 Database

Configuration

DB2 Logfiles DB2 Containers

db2loggwdb2loggw

The database is activated with the following command:

db2 activate database <DBSID>

As soon as the database is activated, the following per-database EDUs are started:

db2loggr – Manages DB2 log files

db2loggw – Writes from log buffer to log file

db2fmtlg – Manages pre-allocation of DB2 log files

db2dlock – Manages the deadlock detection in the database partition

db2pfchr – Handles read-ahead of large quantities of data as soon as requested by DB2 (this is

prefetcher EDU)

db2pclnr – Responsible for picking dirty pages from buffer pool(s) for copy to DB2 containers (this is

page cleaner EDU)

The configuration of the database <DBSID> is stored in the DB2 Database Configuration File. The

contents of this binary file can be viewed with the DB2 command:

DB2 GET DB CFG FOR <DBSID>

Its contents can be manipulated with the following command:

DB2 UPDATE DB CFG FOR <DBSID> USING <PARAMETER> <VALUE>




SAP AG 2004

D B

2 C o n n e c t i o s

F i l e L e v e l


DB2 Connections in SAP Environments: Processes

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm

DB2 Instance

Configuration



db2agentdb2agent

db2tcpcmdb2tcpcm


db2agentdb2agent

startsap db

startdb

db2start

db2 activate db

startsap r3

sapstart

dispatcher

work processes

DB2 Database

Configuration

DB2 Logfiles DB2 Containers

db2loggwdb2loggw

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

If an SAP application needs to retrieve data from a DB2 database server, it will connect via the remote

listener if it is not running on the same logical server as the database partition. The remote listener will

ask the db2 system controller to start a db2agent EDU for direct connection with the SAP application.

When requesting a database connection on the same logical system as the database partition, the Inter-

Process Connection Manager starts the db2agent EDUs directly.

The following command can be used to display all active applications that are connected to the database

:

DB2 LIST APPLICATIONS FOR DATABASE <DBSID> [SHOW DETAIL]

A connection to the database can be established with the DB2 command:

DB2 CONNECT TO <DBSID>

The connection can be terminated with the DB2 command:

DB2 TERMINATE

The following command can be used to disconnect all applications :

DB2 FORCE APPLICATION ALL




SAP AG 2004

D B


F i l e L e v e l


DB2 Memory Allocation: Database Server

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm

DB2 Instance

Configuration



db2agentdb2agent

db2tcpcmdb2tcpcm


db2agentdb2agent

DB2 Database

Configuration


DB2 Memory

Agent Memory

WorkWork processprocess

Database manager

shared memory

Database shared

memorydb2loggwdb2loggw

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

When the DB2 instance is started, the per-instance memory, also known as database-manager shared

memory, is allocated. The maximum size of the memory can be controlled by the DB2 DBM parameter

'instance_memory'. By default, this parameter is set to Automatic, allowing for growth according to

consumption.

When the DB2 Database is activated or started, the per-database memory is allocated. The maximum

size of the per-database memory can be controlled by the DB2 DB parameter 'database_memory'. This

memory is used by all applications of the database concurrently.

The most important memory areas that reside in the database memory are the database heap and buffer

pool(s). The sizes of these memory areas are related to each other. Other memory areas are the lock list,

the package, and the catalog cache.

When an db2agent EDU is allocated to an application, the agent will allocate its agent private memory.

This memory contains memory heaps for sorting (sort heap), application processing (application heap),

and so on.

Because large database servers will run many db2agent EDUs (notice that the memory consumption is

directly related to the number of connected applications), the per-agent memory consumption should not

be underestimated.




SAP AG 2003

F i l e L e v e l


Shutdown of DB2 Databases

db2wddb2wdoogg

db2syscdb2sysc

db2gdsdb2gds

db2ipccmdb2ipccm

DB2 Instance

Configuration


db2tcpcmdb2tcpcm

db2stop

stopdb

db2 deactivate db

stopsap

DB2 Database

Configuration


1. Database buffers are flushed

2. DB2 log files and DB2 containers are closed

db2loggwdb2loggw

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr

db2pclnr db2pclnr

db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

db2pclnr db2pclnr

When SAP software is shut down in a controlled process, the stopsap db script is used to call stopdb,

which in turn executes the following DB2 commands:

db2 deactivate database <dbsid>db2stop

During the deactivation of the database, the database buffers are copied back to DB2 containers and all

files are closed. This includes the DB2 log files.

As soon as there are no active databases, the db2stop command stops the per-instance processes.




SAP AG 2003


Processing

2 Database Architecture

Processing




SAP AG 2004

The SAP Database Support Layer

&0 |1:1|0|DbSlConnectDB6|SQLAllocHandle |0|0|SQL_HANDLE_S ...

&0 |1:1|0|DbSlConnectDB6|SQLExtendedPrepare|0|0|CLI_STMT_ ...

&+0|1:1|0| values( current degree ) -- OPTLEVEL( 0 ) -- ...

&+0|1:1|0| cursor type=NO_HOLD, isolation=UR, ...

&0 |1:1|0|DbSlConnectDB6|SQLExecute |0|0|

&0 |1:1|0|DbSlConnectDB6|SQLExtendedBind |0|0|BindCol

&+0|1:1|0| 1 CT=CHAR T=VARCHAR ...

&0 |1:1|0|DbSlConnectDB6|SQLFetch |0|1| ...

&+0|1:1|0| row 1: 1 CHAR I=5 "1 "

&0 |1:1|0|DbSlConnectDB6|SQLFreeStmt |0|0|SQL_CLOSE ...

This excerpt from the SAP dev trace shows the interface between the

SAP Database Support Layer and the IBM DB2 Call Level Interface

DBSL DB2CLI

The Database Support Layer (DBSL) is a set of C routines, which call the DB2 Call Level Interface

(CLI).

The CLI is documented in the DB2 Call Level Interface Guide and Reference Version 8. In the example shown in the slide, the DBSL function DbSlConnectDB6 calls the following routines:

SQLAllocHandle – To allocate a DB2 statement handle

SQLExtendedPrepare – To prepare an SQL statement

SQLExecute – To execute the prepared SQL statement

SQLFetch – To retrieve the result set of the SQL statement (the additional information in the

example indicates that one result was found)

SQLFreeStmt – To free up the statement handle

The above excerpt from an SAP DBSL trace is shown for educational purposes only. In normal

operations of SAP systems, no knowledge about the trace is necessary.




SAP AG 2004

C o n n e c t i o n s

I n s t a n c e

CC

db2tcpcmWP db2agent

sapdb2<SAPSID>

Arbitrary

port

Arbitrary

port

DB2 TCP/IP Connection Setup


db2tcpcmdb2tcpcm db2syscdb2sysc

db2agentdb2agent

11

22

33

44

DD

BB

A A

This example shows the EDUs that are used during remote database connection setup.

For the connection setup (numbers), the following scenario applies:

1. The SAP work process, or any SAP application requesting a DB2 database connection, attaches to the TCP/IP

listener (db2tcpcm) on a predefined TCP/IP port. This port is defined in the DBM CFG parameter

SVCENAME.

2. The TCP/IP listener asks the DB2 system controller to start a db2agent EDU

3. A db2agent is started.

4. The db2agent can now communicate directly with the application. The db2tcpcm is free to service additional

requests.

For the network communication (letters), the following scenario applies:

A The application sends a connection request from an arbitrary socket to the database server (port name

sapdb2<dbsid>).

B A new socket is created on an arbitrary port for the db2agent.

C The db2agent responds on the new socket and communication is established. First, the maximum requested I/O

block size is negotiated by the client (RQRIOBLK). For each blocking cursor, there is always a buffer of

RQRIOBLK size on the client side. This can allocate a substantial amount of memory per work process (check

for blocked cursors in the application snapshot using transaction ST04).




SAP AG 2004

Statement is PREPARED in execution planSELECT STATEMENT

| (Estimated Costs = 661 [timerons] )

--- RETURN

|

--- FETCH TADIR

|

------IXSCAN TADIR~2 #key columns: 1

SQL statementSELECT

author, devclass, obj_name

FROM

sapprd.tadir

WHERE

author=’JOHN DOE’

Buffer pool


db2agentdb2agent

db2pfchr db2pfchr

Statement is EXECUTED

Rows are FETCHED

11

22

33

44

11

22

33

44

DB2containers

SQL Statement Processing

This example shows the processing that happens during execution of read-only SQL statements:

1. An SQL statement is sent from the SAP work process to its associated db2agent.

2. The DB2 Optimizer in the DB2 agent transforms the SQL statement into an execution plan on data.This process is called preparation. The plan may also be cached already in the DB2 Package Cache

so preparation may not be needed.

3. The prepared statement will then be executed, and a result set will be formed. Several I/O servers – or

prefetchers – may work on the requests, performing I/O in parallel, if the amount of data to be read is

large. For normal operations, with very selective SQL statements, prefetching is not be needed.

4. The work process retrieves the result set to the application using a FETCH SQL command.

A work process is active for one user only during the dialog step between screen input and screen output.

Before screen output, the work process issues a commit to the database. This means different users use

the same work process and database connection consecutively. The work process does not connect to

and disconnect from the database between the dialog steps.




SAP AG 2004

SQL statementUPDATE sapr3.tadir

SET

devclass = “ZXX”

WHERE

author=’JOHN DOE’

Buffer pool


db2agentdb2agent

db2pclnr db2pclnr

11

22

33 44

Log buffer

Log file db2loggwdb2loggwDB2

containers

SQL Statement Processing (2)

This example shows the processing when an SQL statement is executed that changes data:

1. Statements that will alter data – such as UPDATE/INSERT/DELETE SQL statements – are sent from

the SAP application to the associated DB2 agent.2. The DB2 page holding the data is altered in the buffer pool. Much later, it will be transferred to disk.

At the same time, the DB2 agent process writes information about the changes to the data to the log

buffer. DB2 uses the log records for this write operation.

3. The db2loggw process is responsible for writing the contents of the log buffer to the current active log

file. This is done when an application ends the transaction by issuing a COMMIT SQL statement,

every second, and when the log buffer is full.

4. I/O cleaners or page cleaners copy updated pages to disk when triggered. I/O cleaners get triggered

when:

- The maximum amount of log space that should be read during crash recovery has been reached (DB

CFG parameter SOFTMAX)

- The maximum percentage of changed pages has been reached (DB CFG parameter

CHNGPGS_THRESH)

- No pages are available during insert/update. In this case, victim page cleaning is started.




SAP AG 2004

DB2 UDB Multi-Partition Query Processing

DB2 Database Partition 0 DB2 Database Partition 2DB2 Database Partition 1

Table 1Table 2


db2agentdb2agent

11

db2agentdb2agent db2agentdb2agent

22

33

44

55

The Agent of the Partition, to which the client is connected, prepares the SQL statement (2) and

distributes the access plan parts to all other partitions involved in answering the query(3).

Each partition computes its results. Maybe data has to be shipped between partitions in that process. Finally, the local result is sent back to the coordinating agent, summarized there and then returned to the

client.




SAP AG 2004

Database Architecture: Unit Summary


Describe the operational model of your

DB2 Universal Database Describe the packaging and installation options of DB2

in SAP environments

Perform software maintenance for your database server

Describe the working model of your database server

Explain the authentication concept of your

SAP DB2 database




SAP AG 2003

Do the exercises

?


Unit Actions




Exercises

Unit: Database Architecture

At the conclusion of this exercise, you will be able to:

• Start and stop a DB2 database

1-1 Database Operations: Starting and Stopping

1-1-1 Start your DB2 training instance <db2txx>.

1-1-2 Start your training database <Txx> manually.

1-1-3 How many connections are made to the database?

1-1-4 Connect to the database.

1-1-5 Stop your training database and then stop the DB2 training instance.

1-2 DB2 EDUs

1-2-1 How can the DB2 EDUs be viewed in the training system?

1-2-2 With the DB2 training instance <db2txx> being stopped, how many DB2 EDUs

do you see?

1-2-3 Start the DB2 training instance. Which are the processes that are now started?

1-2-4 Activate the training database <Txx>. What are the per-database processes?




Solutions

Unit: Database Architecture

1-1 Database Operations: Starting and Stopping1-1-1 In order to start your DB2 training instance <db2txx>, enter the command

db2start.

1-1-2 To start your training database <Txx>, you can activate the database using

db2 activate db <Txx>.

1-1-3 To see the connections which are made to the database, enter

db2 list applications. The following response should be expected:

SQL1611W No data was returned by Database System Monitor.SQLSTATE=00000

You will see that no data is returned, since you have just started the DB2instance.

1-1-4 To connect to the database, enter db2 connect to <Txx>. The database will respond to you with:

Database Connection Information

Database server = DB2/LINUX 8.1.0SQL authorization ID = DB2Txx

Local database alias = Txx

1-1-5 To stop the DB2 database and instance, you need to disconnect first, then stopthe DB2 database and instance.

db2 terminatedb2 deactivate db <txx>

db2stop

1-2 DB2 EDUs.

1-2-1 Since the training system is a Linux server, EDUs are implemented as processes. To view the EDUs, you can use ps, or pstree: Here is an example of

pstree:

-db2sysc---db2sysc-+-13*[db2sysc]

`-db2sysc---db2sysc

1-2-2 The answer to this question depends on the number of other DB2 instances and

databases that are running on your server . But even with all other training

databases shut down, you will see that the SAP training system DEV is runningits own databases and DB2 instance.




1-2-3 After starting your DB2 training instance with db2start,use the Unix commands

ps –ef | grep –i <Txx> to see that the per-instance processes are started (db2sysc, db2ipccm, and soon).

1-2-4 After activating the training database with db2 activate db <Txx>

you can see that the per-database processes are started (db2pfchr, db2pclnr, and

so on).




SAP AG 2005

Partitioning

1 Definitions

8

Tools for DB2

Database Servers

3 Partitioning

4 Log File Mangement

5 Managing Backups


9

11 Problem Analysis



2

DB2 Performance






SAP AG 2004

Contents:

Aspects of Single and Multi-Partitioning

Partitioning Layout

Objectives:


Show differences between single and multi partitioning

Add and delete partitions

Implement an appropriate database partition layout

Partitioning




SAP AG 2005

General

Add and Delete a Partition

Single Partitioning

3 Partitioning

Multi Partitioning

General




SAP AG 2004

DB2 Partitioning and SAP

• Single Partitioning:

•OLTP (online transactional processing):

•SAP Web Application Server (Web AS)

•Customer Relationship Management (CRM)

•Supply Chain Management (SCM)

•Enterprise Resource Planning (ERP)

•Multi-Partitioning:

•OLAP (online analytical processing)

•Business Information Warehouse (SAP BW)

•Advanced Planning and Optimization (APO)

In DB2 UDB there is single and multi partitioning.

In SAP environment there is a relationship between the kind of partitioning and kind of processing.

Multi partitioning is only used by OLAP systems like SAP BW or APO. Multi partitioning in OLTPsystems has not been used by SAP until now.




SAP AG 2004

Logical Partitions – Database Partitions on One Server

Physical Partitions – Database Partitions on Different Servers

D B 2 D a t a

b a s e

D B 2 D a t a b a s e

P a r t i t i o

n 0

Data S e r v e r 1


P a r t i t i o

n 1

Data

D B 2 D

a t a b a s e

D B 2 D

a t a b a s e

P a r t i t i o n 0

Data S e r

v e r 1

D B 2 D

a t a b a s e

P a r t i t i o n 1

Data S e r

v e r 2

Logical and Physical Partitioning

Physical or logical Partitioning is transparent to the database. It is determined by the file db2nodes.cfg

Logical Partitioning:

- Several logical partitions can be on the same machine.

Physical Partitioning:

- Several logical partitions can be on different machines.

Each partition has its own Buffer Pools, Sort Areas and Logging.

Definition of database partition groups to determine which partitions are used for tablespaces.

All tables of a partitioned Tablespace distributed on the assigned partitions.




SAP AG 2004

Partition Groups: Tablespace Layout


D B 2 D a t a b a s e P a r t i t i o n 0

PARTITON GROUP 1

PARTITON GROUP 2


Tablespace1

Tablespace3

With IBM DB2 Universal Database Enterprise Server Edition V8.1, SAP delivers all DB2 database

servers are to be multi-partition enabled. Still, for OLTP systems, there must be one database partition

only.

In multi-partitioned databases, the partitions are grouped into database partition groups to form subsets

of the distributed database for storage allocation purposes.

Each multi-partitioned database contains at least three predefined database partition groups:

IBMCATGROUP – the partition group with the tablespace containing the system catalogs

IBMTEMPGROUP – the default partition group for system temporary tablespaces

IBMDEFAULTGROUP – the default partition group for the user defined tablespaces




SAP AG 2004

Partitioning Key

Number Name First Name

123456789 John Test

123456790 Max Try

Hash Value 1 3 4 5 6 7 8 9 10 …

Partition 0 0 1 1 2 2 3 1 2 3

2

D

B 2 D a t a b a s e

P a r t i t i o n 0

Data D

B 2 D a t a b a s e

P a r t i t i o n 1

Data D

B 2 D a t a b a s e

P a r t i t i o n 2

Data D

B 2 D a t a b a s e

P a r t i t i o n 3

Data

Hash value

assigned to

Partition number

Hash function

determines data

distribution todatabase partitions

The partitioning key for the partitioned Persistent Staging Area (PSA), Operational Data Store (ODS)

and fact tables are chosen automatically by BW when the tables are created during activation of PSA,

ODS and info cube objects. No special user interaction is required.

Partition key on fact table includes all dimension columns with exception of Package Dimension.

Data distributed with hash function.

Partitioning Key of a table is used to determine a partition on which the data row is stored.

Partitioning key is a key that is part of table definition.

Automatically generated Partition Keys on PSA, ODS, Fact Tables leads to uniformly distributed data

on the participating partitions.

- Partition Keys (PK)on

- Fact Tables: All dimension key columns without request dimension

PK = Dim1, Dim2, Dim3, …., Dim-Time, Dim-Unit- ODS Tables:

- Activation Queue Tables (/BIC/A..40): PK = Record column

- Change Log Tables (/BIC/A..00): PK = Record column

- Active Tables (/BIC/A..50): PK = key fields of ODS object

- PSA Tables: PK = Record column




SAP AG 2005


SELECT ... FROM ...

DISTRIBUTED TABLES

Interparallelism and Intraparallelism

SELECT ... FROM ... SELECT ... FROM ...

D B 2 D a t a b a s e P a r t i t i o n 0 INTRAPARALLELISM INTRAPARALLELISM

INTERPARALLELISM

Parallel Processing

Two types of Parallelism

Intra-partition parallelism

=> parallel processing within

one partition

Inter-partition parallelism

=> operations are executed

in parallel on each

database partition

SQL Query performance proportional to number of partitions (BW environment)

Parallel processed SQL statements: UPDATE, DELETE, INSERT, JOINS, GROUP BY,INDEX/TABLE SCANS, SORT

DB2 UDB Tools processed in parallel: INDEX Creation, Backup and Restore, Table Reorganization




SAP AG 2005

General


Single Partitioning

3 Partitioning

Multi Partitioning

Single Partitioning




SAP AG 2004

Tablespace Layout on Single-Partition Systems



NGRP_DIM_<SAPSID>

NGRP_FACT_<SAPSID>

Basis Standard Tablespaces

<SAPSID>#FACTD, <SAPSID>#FACTI

NGRP_ODS_<SAPSID>

PSAPTEMP

PSAPTEMP16

Temporary Tablespaces (PSAPTEMP and PSAPTEMP16) are created as SMS tablespace. SMS

tablespaces are growing and shrinking dynamically. Therefore, PSAPTEMP and PSAPTEMP16 should

be located in the same file system.

Following BW-specific tablespaces and database partition groups are created per default:

The PSA or ODS database partition group NGRP_ODS_<SAPSID> that contains all partitions

The PSA or ODS tablespaces <SAPSID>#ODSD and <SAPSID>#ODSI that are both in the

NGRP_ODS_<SAPSID> database partition group. These tablespaces correspond to the data

class DODS.

The default dimension database partition group NGRP_DIM_<SAPSID> that contains partition 0

The default dimension tablespaces <SAPSID>#DIMD and <SAPSID>#DIMI that are both in the

NGRP_DIM_<SAPSID> database partition group. These tablespaces correspond to the data class

DDIM.

The default fact database partition group NGRP_FACT_<SAPSID> that contains all partitions

The default fact tablespaces <SAPSID>#FACTD and <SAPSID>#FACTI that are both in the

database partition group NGRP_FACT_<SAPSID>. These tablespaces correspond to data class

DFACT.




SAP AG 2004

DB2 Database Partition 0

Bufferpools on single Partition Systems

16 kb Basis Tablespaces

4kb Bufferpool

16 kb Bufferpool

4kb Basis Tablespaces

Bufferpools

Tablespaces

On a single partition System at least one bufferpool should exist for each page. For performance reasons

sometimes it is necessary to create more than one bufferpool of a pagesize. Then there should be a

relationship between tablespace and bufferpool. For this please use the following command:db2 „alter tablespace <tablespacename> bufferpool <bufferpoolname>“




SAP AG 2005

General


Single Partitioning

3 Partitioning

Multi Partitioning

Multi Partitioning




SAP AG 2004

Persistent Staging Area (PSA)large tables, large row size,

flat table structure

Data Warehouse and

Operational Data Store (ODS)large tables, large row size,


Master Datain general small tables, small row size,


Multi-Dimensional ModelsSAP BW InfoCubes and

SAP BW aggregatesuse SAP BW extended star schema

Fact tables: large tables, large row size

Dimension tables: small tables, small row size

Multi Partitioning: Different Data Types (SAP BW)

Different types of data need different types of data processing and data distribution.

For example, BW contains the following different data types:

FACT

DIM (Dimension Tablespaces)

ODS (Operational Data Store)

PSA (Persisting Staging Area)

The next slides will show, how DB2 handles these data types.




SAP AG 2004


Buffer Pools on Multi-Partition Systems (SAP BW)

Dimension 1

ODS Activation Queue

ODS Active Table, Change Log

Dimension 2

Aggregates 2

Aggregates 1

Aggregate buffer pool (shared)

Fact, ODS, PSA buffer pool (shared)

Fact Fables 1

InfoCube fact 2

PSA data

DB2 Database Partition n

IBMDEFAULTBP

DIMENSION Bufferpool

(shared)

Basis Tables

Buffer pools

Tablespaces

Use different Bufferpools for different kind of data, for example BW-data (Dimension, Aggregates,

PSA / ODS /Fact data). This increases the Query Performance, because buffer pool hit ratio is different

for each data type.

Large Tablespaces and assigned Bufferpools are distributed on several partitions.

If many database partitions are defined (more than 6), it could make sense to store PSA / ODS /Fact

tablespaces not on Partition 0 in order to reduce workload on partition 0 and to increase database

backup / restore performance.

There are three levels of bufferpool size definition:

Db cfg (single partition) parameter buffpage: db2 get db cfg for <dbsid>

Table syscat.bufferpools (single partition)

db2 “select * from syscat.bufferpools”

Syscat.bufferpoolnodes (multi partition)db2 “select * from syscat.bufferpoolnodes”

The higher level will overwrite the lower level, if there is a value <> -1 in the npages column.




SAP AG 2004

Connection Between Application and DB Server



P a r t i t i o n 0

S e r v e r 1


P a r t i t i o n 1


P a r t i t i o n 2

S e r v e r 2


P a r t i t i o n 3

S A P A

p p l i c a t i o n S e r v e r

S A P

A p p l i c a t i o n S e r v e r

S A P A


S A P A


Fast Communication

Application Servers are connected to the first DB Partition of a DB Server

Advantage to connect to DB Part.0 is Fast Access to Basis, Master Data and Dimension Tables

SMP Machine: All BW Application are Servers connected to DB Part.0

With Clustered Hardware, make sure, that CPU work load on DB Part.0 < 90%. Alternatives to reduce

CPU work load is to Connect less BW Appl. Servers to DB Server with DB Part. 0 or to locate PSA,

ODS, Fact and Aggregate data not on DB Part. 0

If many database partitions are defined (more than 6), it could make sense to store PSA / ODS /Fact

tablespaces not on Partition 0 in order to reduce workload on partition 0 and to increase database backup

/ restore performance.




SAP AG 2005

General


Single Partitioning

3 Partitioning

Multi Partitioning





SAP AG 2004

Adding New Database Partitions (General)

D B

2 D a t a b a s e

D B

2 D a t a b a s e

P a r t i t i o n 0

Data S e r v e r 1

D B

2 D a t a b a s e

P a r t i t i o n 1

Data

1. Create necessary file systems

2. Create the additional partition

3. Update the database configuration on the new partition

4. Create the containers for the system temporary tablespaces on the newpartition

5. Add the new partition to the two database partition groups

6. Add containers for the tablespaces belonging to the two databasepartition groups on the new partition

7. Redistribute the data

For performance reasons and storage capability reasons it is sometimes necessary to add new partitions

on a existing database. This figure shows the general steps have to be performed.




SAP AG 2004

Adding New Database Partitions (Detail)



P a r t i t i o n 0

S e r v e r 0


P a r t i t i o n 1

S e r v e r 1



P a r t i t i

o n 0

S e r v e

r 1


P a r t i t i o n 1

New Phsical Partition

New Logical Partition

Tasks to add an additional DB partition:

Create necessary local file systems e.g. /db2/<DBSID>/sapdata1/NODE<NNNN>

Create the additional partition with command:

db2start add dbpartitionnum hostname <hostname>

db2nodes.cfg file will be updated

Update the database configuration on the new partition:

db2 update db cfg for <sid> using <parameter> <value>

Create the containers for the system temporary tablespaces on the new partition

Add the new partition to the relevant database partition groups

db2 alter database partition group <db partition group> adddbpartitionnum ..

Add containers for the tablespaces belonging to the database partition groups on the new partition:

db2 alter tablespace <tablespacename> add <container_clause> …

Redistribute the data (on the next slides)

Additional steps are necessary for adding a db partition on a new DB server (mount shared file systems

(db2 instance), install DB software, run SAPinst)




SAP AG 2004

Partition Wizard

The Partition Wizard will help during steps after the creation of new partitions on filesystem level.

Bevor the partition wizard is used, the following object must exist:

- Filesystems

- Mounts (if needed)

- New partition definition:

db2start add dbpartitionnum hostname <hostname> ….




SAP AG 2004

Distribution of Data: Redistribute


Tablespace A

Table A


Tablespace A

Table A


After the physical addition of a partition it is necessary, to distribute the specific data towards the

partitions.

The first possiblity is the DB2 Tool redistribute:db2 „redistribute db partition group <db-partition-group> {{uniform

| using delete <filename>} | using

targetmap <filename> |continue | rollback}“

Note: During the redistribute DB2 performs a consistency check!




SAP AG 2004

Distribution of Data: db6conv

Another possiblity for distribution is a SAP Tool, called db6conv.

DB6CONV executes the following SQLstatement:

Db2 „INSERT INTO target-table

SELECT * FROM source-table

(NOT LOGGED INITIALLY)“

And additional post processing

DB2‘s rebalancing mechanism is avoided, which results in faster redistribution of data.

Since NOT LOGGED INITIALLY is used, a database backup is required after relocation of the data.

SAP OSS Notes: 648432 DB6 New function modules for hash partitioning

362325 DB6 Table conversion with DB6CONV




SAP AG 2004

Delete a Partition

D B 2 D

a t a b a s e

D B 2 D a t a b

a s e P a r t i t i o n 0

S e

r v e r 1


D

B 2 D a t a b a s e

S e r v e r 1

D B 2 D

a t a b a s e P a r t i t i o n 0

To delete a partition, the following steps must be performed in sequence:

Backup of database

Change environment to the partitionnumber, which should be deleted:set DB2NODE=<partitionnumber>

Check Nodes in use: db2 drop node verify

Check which nodegroups must be redistributed: db2 list nodegroups show detail

Check needed space to delete a partition (is there enough freespace?):

db2 list tablespaces show detail

Redistribution of specific nodegroups (repeat for all needed partitiongroups):

db2 „redistribute nodegroup <partitiongroupname> uniform drop node

(<partitionnumber>)“

Delete partition (if empty): db2stop drop nodenum <partitionnumber>

Backup of database




SAP AG 2004

Partitioning: Unit Summary


Show differences between single and multi-partitioning

Add and delete partitions

Implement an appropriate database partition layout




Partitioning Exercises

Unit: Partitioning


• Maintain Partitions

1-1 Bufferpools

1-1-1 List the different levels of bufferpools

1-1-2 Which value is valid?

1-1-3 Verify your solution.




Partitioning Solutions

Unit: Partitioning

1-1 Bufferpools

1-1-1 There are three levels:

Enter the command: db2 get db cfg for <Txx> and check the buffpage

parameter

Enter the command: db2 “select * from syscat.bufferpools” and check the

npages value

Enter the command: db2 “select * from syscat.bufferpoolnodes” and check

the npages value.




SAP AG 2005

Log File Management

28

Tools for DB2

Database Servers


1 Definitions

5 Managing Backups


9

11 Problem Analysis









SAP AG 2005

Log File Management

Contents:

Log File Management

DB2 Logging Concepts and Architecture

The Log File Life Cycle

Tools for Log File Management

Objectives:


Describe the DB2 logging architecture

Use the SAP log file management tools

Select and implement the appropriate archiving method for your system




SAP AG 2005

Log File Fundamentals

Log File Handling

Legacy Log File Management


Log File Management V8.2





SAP AG 2004

DataLog Information

Log Buffer Buffer Pool

Logging Overview

container log file

db2loggwdb2loggw

db2pfchr db2pfchr db2pclnr db2pclnr

db2agentdb2agent

Mirror log file

In the above figure, you see how DB2 uses two different methods to write changes to disk:

Using the buffer pool, pages are changed in the buffer pool by the DB2 Agent (db2agent) engine

dispatchable units (EDUs). Information about these changes is written to the log buffer by the db2agentusing log records. The path of the log information is as follows:

• Log records are written from the log buffer to log files in directory log_dir by the db2 log writer

EDU. This is done (1) when an application performs a COMMIT SQL statement, (2) every second,

and (3) when the log buffer is full – whatever comes first.

• During a rollback, the db2agent reads the log records from the log file.

• The pages are updated in the buffer pool(s).

Page cleaners copy updated pages to disk when triggered. Page cleaners get triggered when (1) the

maximum amount of log space that should be read during crash recovery has been reached (DB CFG

parameter SOFTMAX), (2) the maximum percentage of changed pages has been reached (DB CFG

parameter CHNGPGS_THRESH), and (3) no pages are available during insert or update. In this case,“victim page cleaning” is started.




SAP AG 2005


Log File Handling








SAP AG 2005

log_dir

Log File Life Cycle

Indirect Archiving

Direct Archiving

log_archive

log_retrieve Storage

d b 2 u e x t 2

d b 2 u e x t 2

B R AR C H I V E

B R R E S T

O R E

log_dir db2uext2

db2uext2

Storage

With two-step log file management (Indirect Archiving), the DB2 logging user exit (db2uext2) and the

SAP tools brarchive and brrestore are used:

The DB2 logging user exit copies log files from the online log directory (log_dir) to the offline logdirectory, log_archive. For each log file copied, an entry is created in the administration database,

ADM<DBSID>. The logging user exit also copies log files from the log_retrieve directory to the

online log directory (log_dir) when a rollforward command is issued.

The SAP tool brarchive provides log file backup functionality. For each log file that is copied to or

deleted from the offline log directory (log_archive), a corresponding entry in the administration

database is made.

The SAP tool brrestore provides log file restore functionality. For each log that is restored to

directory log_retrieve, a corresponding entry in the administration database is made.

The tables of the administration database ADM<DBSID> will be updated during log file management

operations.

With one-step log file management (Direct Archiving), the following occurs:

Log files are transferred immediately to storage. As a consequence, brarchive or brrestore are not

needed in this scenario.

Log files are stored on safe, external media as soon as possible. If a roll-forward recovery occurs,

these log files can be retrieved directly by the DB2 logging user exit. No operator intervention is

required. (For the two-step scenario, log files have to be retrieved from storage.)




SAP AG 2005

Indirect Archiving

log buffer

archived log files

online log files

db2agentdb2agent

db2loggwdb2loggw

db2uext2 (Step1)db2uext2 (Step1)

brarchivebrarchive (Step2)(Step2)

Storage

log_archive

log_dir

database global memory

This slide shows how log files are handled during Indirect Archiving:

• When a log file is filled with records, the DB2 logging user exit is called by the database to copy

the log file into the directory log_archive. The copied log file is the offline log. When allreferenced pages have been written from the buffer pool(s) to disk, the online log file is ready for

renaming in the log_dir directory. A copy remains as an offline log in the log_archive directory.

• Log files that reside in the log_archive directory can be archived to storage with brarchive.

• For this setup, you should install the SAP DB2 Admin Tools with an administraion database.

To get protocol information about DB2 logging user exit executions, call transaction ST04, choose

Backup and Recovery -> Backup Overview -> section User exit . Section brarchive provides similar

information about brarchive executions.




SAP AG 2004

brarchive Examples

For more information, please refer to the

SAP DB2 database administration guide

Initialization of tapes

brarchive -d tape -i force -v L6CA01 -t /dev/nst1 –out

Indirect archiving of log files to tape with store and deletebrarchive -sd -d tape -v L6CA01 -t /dev/nst1 -out -n 5 -sid L6C

Indirect archiving to TSM

brarchive -s -d adsm –out

Use of vendor libraries

brarchive -s -d adsm -t /dev/nst1 -out -n 5 -sid L6C

-vendor /<path>/vendorlib

brarchive is used to archive offline log files to storage, such as to tape or to a storage management

solution. brarchive retrieves the range of log files that are to be archived from the administration

database.

If you use a storage management solution as brarchive’s target device, tape management is performed

automatically by the storage management system.

Important: You should initialize tapes for a whole backup cycle.

The environment file init<DBSID>.db6 is the primary source for setting the admin tool configuration.

We strongly recommend using this file to configure the Admin Tool-specific environment variables.

This file was not available before SAP R/3 4.6A. For more information, refer to the SAP DB2 database

administration guide, Database Administration Guide for SAP on IBM DB2 Universal Database for

UNIX and Windows.




SAP AG 2004

brarchive: Option 1

pcibm14:db2l6c > brarchive -sd -d adsm -out -sid L6C

Operating System

1a) On demand

1b) Scheduled regularly using scripts

pcibm14:root > more /etc/crontab

1 0 * * * db2l6c brarchive -s -d tape -v L6CA02 -t /dev/nst0 -out -sid L6C

You can use the command line interface of brarchive in two ways:

As shown in example 1a, you can issue the brarchive command directly from the command prompt.

As shown in example 1b, you can use a scheduler to schedule backups of offline log files using ascript.




SAP AG 2005

Direct Archiving

log buffer

online log files

db2agentdb2agent

db2loggwdb2loggw

db2uext2db2uext2

Storage Management Solution

log_dir

database global memory

You can set up one-step log file management by changing the configuration of the DB2 logging user

exit. Here log files are transferred immediately to storage. Consequently, brarchive and the Admin

Database are not needed.

The benefit of this approach is that log files are stored on safe, external media as soon as possible. If a

rollforward recovery is necessary, these log files can be retrieved directly by the DB2 logging user exit.

No operator intervention is required.

To avoid file system full conditions in the log_dir directory, make sure that your storage management

solution is highly available.

Set the DB2DB6_UEXIT_DIRECT variable in the environment file init<DBSID>.db6 to indicate direct

archiving. Use the following syntax:

For TSM: DB2DB6_UEXIT_DIRECT = TSM: [<Management Class>]

For a vendor's user exit program: DB2DB6_UEXIT_DIRECT = VENDOR and

DB2DB6_VENDOR_UEXIT = <vendor user exit program>

For more information, refer to the SAP DB2 database administration guide.

For this setup, you should install the admin tools without the Admin Database.

In Version 8, you can use infinite logging. This means it is possible to support environments with large

jobs that require more log space than you would normally allocate to the primary logs. For this, the

database configuration parameter LOGSECOND must be set to -1.




SAP AG 2004

Restoring log files from tape

brrestore -a 110-120 -d tape -v D4XA99 –out

Emergency log file restore from TSM

brrestore -ex "*" S000112* 20040912 –d adsm -out

Emergency log file restore from tape

brrestore -ex "*" –t /dev/rmt0.1 -out

Query functions

brrestore -q '*NODE0002' -out

Query functions

brrestore -q adsmMC -out

brrestore Examples

For more information, refer to the

SAP DB2 database administration guide

The brrestore command can be used to retrieve any log file archived using brarchive.




SAP AG 2005


Log File Handling








SAP AG 2005

Overview

db2loggr

History file

db2logmgr

db2tapemgr

log_dir

mirror

log_dir

failarch

path

TSM

Vendor

Disk

overflow

logpath

Tape

db2loggw

db2agent

L o g

B u f f e r

The slide shows the new log file management of DB2 Version 8.2.

Since DB2 Version 8.2 there are two new components, the DB2 log manager (db2logmgr) and the

DB2 tape manager (db2tapemgr).

The DB2 log manager is part of the DB2 engine and is responsible for archiving and retrieving log files.

The DB2 tape manager can be used to archive DB2 log files to tape. This is an executable, which you

can call from the command line. The DB2 log manager cannot directly handle the log files that are stored

on tape. You have to call the DB2 tape manager explicitly from the command line.




SAP AG 2004

db2logmgr: DB2 Log Manager

History file

db2logmgr

log_dir

mirror

log_dir

failarch

path

TSM

Vendor

Disk

The db2logmgr is part of the DB2 engine

The Log Manager stores Log file location information in the history file.

The db2 log manager provides the following functionality:

Storing/retrieving log files from log dir from/to log directory

Back-end support: disk, TSM, vendor, old user exit

Failover path

Log file chain support

Two archive locations

The configuration of the db2logmgr is possible with : db2 “update db cfg for <dbsid> using <parameter> <value>”

If log files are retrieved, the DB2 log manager directly retrieves them from the backend and puts them inthe DB2 transaction log directory (log_dir). From there the DB2 engine can read them to perform a

database recovery or rollforward operation.




SAP AG 2004

Log File Chains

Log file chains are created if the

database is restored to a point in time.

recover db to T1

00 10 20 40 50 60 70B1 8030

R1T1 00 10 20 4030 B251 61 71 81 91 101 111 121 131 141

R2T3 81 91 101 112 122 132 142

R1T2 00 10 20 4030 51 61 73 83 93

Bn backup n

Lc log file L of chain c

(rollforward processing)

log file L of chain c

(normal processing)Lc

RnTm

recover db using

backup n to timestamp m

recover db to T2

recover db to T3

11 22

33

44

55

66

88

77

99

The DB2 transaction log files have consecutive names from S0000000.LOG to S99999999.LOG.

If a log file is full, DB2 creates a new log file with the next number.

In some special cases DB2 can create log files with the same name but different contents.

An offline backup B1 is created.

Transactional work on the database creates log files 0 - 8 that belong to log file chain 0.

A database recovery to point-in time T1 is performed. This is done by using backup image 1 and

applying log files 0 - 4.

Transactional work on the database creates log files 5 -14, which belong to log file chain 1.

An offline backup B2 is created.


applying log files 8 - 10 of log file chain 1.

Transactional work on the database creates log files 11 - 14, which belong to log file chain 2.


applying log files 0 - 4 of log file chain 0 and log files 5 - 6 of log file chain 1.

Transactional work on the database creates log files 7 - 9, which belong to log file chain 3.




SAP AG 2005

Configuration Variables

Number of seconds to wait after a failed archive attemptbefore trying to archive the log file again.

ARCHRETRYDELAY

Number of retries to archive a log file to the primary or

secondary archive directory before trying to archive log

files to failover directory.

NUMARCHRETRY

If DB2 unable to archive log to both the primary and

secondary archive destination due to a media problem,

then DB2 will try to archive log files to this path. This path

must be a disk.

FAILARCHPATH

Tape expiration in days.DB2_TAPEMGR_TAPE_EXPIRATION

(registry)

e.g. for TSM –fromnode <node> –fromowner <owner>LOGARCHOPTS1

LOGARCHOPTS2

DISK:<path>

TSM:<TSM management class>

VENDOR:<vendor lib>

USEREXIT

LOGRETAIN

LOGARCHMETH1

LOGARCHMETH2

Description of VariableVariable Name

The DB2 log manager supports the following archiving media:

USEREXIT: The DB2 log manager will execute the user exit for log file management like today.

TSM: The DB2 log manager has built-in support for accessing the Tivoli Storage Manager (TSM).

DISK: The DB2 log manager can use disk locations for archiving log files.

VENDOR: DB2 provides a vendor API for the log file management.

LOGARCHMETH1/2: The DB2 log manager supports two different locations (so that a log file can be

stored on two different locations), for example, TSM, disk or vendor products.

If log files cannot be archived to the destination, you can specify a FAILARCHPATH- directory that is

used as temporary location for the log files. If the archive process is possible again, db2logmgr will

move them to the archiving destination.




SAP AG 2004

History File

List History File for NL4

Number of matching file entries = 2

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID-- --- ------------------ ---- --- ------------ ------------ --------------

X D 20050321170957 1 D S0000000.LOG C0000000----------------------------------------------------------------------------

----------------------------------------------------------------------------Comment:

Start Time: 20050321170957End Time: 20050321170957Status: A

----------------------------------------------------------------------------EID: 2 Location: /db2/NL4/log_archive/NL4/NODE0000/C0000000/S0000000.LOG

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

-- --- ------------------ ---- --- ------------ ------------ --------------X D 20050321170957 N S0000000.LOG----------------------------------------------------------------------------

----------------------------------------------------------------------------Comment: ARCHIVE LOG

Start Time: 20050321170957End Time: 20050321170957

Status: A----------------------------------------------------------------------------EID: 3

The location of the log files is recorded in the DB2 history file. DB2 creets Log file entries, if a new log

file is used by the database during normal operation or if a log file is applied during a database

rollforward.

To list the log file information about the command line, you can use the DB2 command list

history:

db2 “list history archive log all for <dbsid>”




SAP AG 2005


Log File Handling




Log File Handling




SAP AG 2004


P a r t i t i o n

0


P a r t i t i o n

1


P a r t i t i o n

2

Log Files in Multi-Partitioned Environments

Tape or Storage

Management System

log_archive log_retrieve

Logfile1Logfile1 Logfile1Logfile1Logfile1Logfile1 Logfile1.Part0Logfile1.Part0

Logfile1.Part1Logfile1.Part1





When using multiple partitions, the database will produce log files on a per-partition basis. The DB2

logging user exit will copy the log files from each log_dir directory (local to each partition) to the shared

log_archive directory.

Note: The node number is part of the log file name, after it has been copied from the partition to

log_archive.

DB2 tape manager is used to archive log files and to restore log files.

In legagy systems, by using the global directory log_archive, the SAP tool brarchive is used to store all

the log files of the partitions to tape or a storage management system.




SAP AG 2005

Creation and Use of DB2 Log Files

LOGPRIMARY LOGSECOND

LOGFILSIZ

n PRIMARY log files are

preallocated

m SECONDARY log files are

created if the primary log

files are all in use

The size of a log file is defined in 4KB pages

S000001.LOG S00000n.LOG.... ....S00000n+m.LOG log_dir

BLK_LOG_DSK_FUL

At database startup, DB2 searches for log files in directory log_dir. If no log files exist, all primary log

files are created by DB2 before the database is made available.

The number of these log files is defined in the DB CFG parameter LOGPRIMARY. The size of the log files (in 4 KB pages) is defined in the DB CFG parameter LOGFILSIZ.

All log files are created during database operation Restore into new. In most cases, you may not see the

full number of log files.

Secondary log files are created on demand during database operations. The total number of secondary

log files is defined with the DB CFG parameter LOGSECOND.

If the primary log files are all in use, secondary log files are created.

If the log_dir is full, the DB CFG parameter BLK_LOG_DSK_FUL=ON will ensure that no transactions

are rolled back. DB2 will wait until log_dir contains free space again.




SAP AG 2004

On-Demand Log Archival

DB2 'ARCHIVE LOG.....''

Close the log file

and call the DB2

logging user exit!

time

DB2 supports the manual closing of log files. If the user exit is enabled, the log files will be archived.

With this option, you can get a complete set of log files up to a known point.

To initiate on-demand log archiving, enter the following command:

db2 archive log for database <dbsid>

This command is used automatically at the end of an online backup. This is necessary, because the db2

backup command contains the “include logs” option.




SAP AG 2004

Cleanup of Log Files

From log_dir

With dropping of database

After point-in-time recovery

With the prune command

From log_archive

brarchive

From storage products

Indirect archiving with tape: initialization of tape

Indirect archiving with TSM: brrestore -delete

Direct archiving with TSM: db6adutl

Direct archiving with other storage management products:tools that are available with this product

The strategy of deleting log files depends on the backup frequency, the number of log files generated per

day, and the size of the available storage. Log files are located in the log directory or the log archive

directory.

log_dir – Normally there is no need to delete files from this directory. Caution: Do not delete files

that are still needed by the database.

log_archive – Log files are stored in the log archive directory only if you use indirect archiving. You

can delete log files with one of these commands

brarchive –sd

brarchive -ssd

During this action, log files will be saved and deleted. If only -s or -ss are used, the delete stored

action, -ds, must be run subsequently.




SAP AG 2005

log_archive

log_dir

LOGRETAIN = OFFActive

Inactive

LOGRETAIN = RECOVERY

USEREXIT = ONProduction

logging mode

Change parameters

and restart DBDatabase

is

backup

pendingPerform full

offline backup

Backup Pending Mode

log_dir

Logging mode

after installation

The initial setting of the DB CFG parameters LOGRETAIN and USEREXIT has to be changed after the

installation to enable roll-forward recovery. To view the logging configuration of your database, call

transaction ST04, choose Backup and Recovery -> Logging Parameters.

When these parameters are set and become effective (for example, after restarting the database), DB2

goes into backup pending state. You must then perform a complete offline database backup. This backup

is used as the starting point for recovery.

You cannot connect to the database until this offline database backup has been successfully completed.




SAP AG 2004

Managing Database Log Files: Unit Summary


Describe the DB2 logging architecture

Use the SAP log file management tools

Select and implement the appropriate archiving method

for your system




SAP AG 2005

Do the exercises

?

Check the solutions to the exercises

Unit Actions




Exercises

Unit: Log File Management


• Monitor and manage the log files of a DB2 database

• Set up a logging configuration that minimizes the risk of losing data

Preparation: To access your training system and use your training database <Txx> during the

following exercises, please start your Web browser. Use the server name and port

number provided by your instructor and enter the following URL:

http://<servername>:<portnumber >. A graphical desktop will appear in your browser window. Please enter the password sapdb2 to access the desktop. From now on, you

can use the desktop to open multiple windows.

You will also be able to reconnect to your desktop in case you close your Web browser window.

1-1 Repeat the log file management. Which parameters reflect the current primary

environment?

1-2 What is the maximum space needed for all (possible) log files?

1-3 Truncate the current log file of your training database <Txx>.




Solutions

Unit: Log File Management

1-1 Execute the following command: db2 get db cfg for <Txx> | moreand check the parameters LOGARCHMETH1, LOGARCHOPT1 andFAILARCHPATH.

1-2 Use Size of Log Files (logfilsiz), Number of Primary Log Files (logprimary) and

Number of Secondary Log Files (logsecond) in section Logging Configuration in order

to calculate the space requirement with the following formula:space = (logprimary + logsecond) * logfilsiz.

1-3 Execute the following command: db2 “archive log for db <Txx>”

With this, the current log file is truncated. Since USEREXIT is not switched on, you

will see that the log file is not archived, as it would work in a standard SAPconfiguration.




SAP AG 2004

Managing Backups

28

Tools for DB2

Database Servers

5

1 Definitions



9

11 Problem Analysis



3 Partitioning

Managing Backups

DB2 Perfomance Monitoring

and Tuning





SAP AG 2004

Prevention of Data Loss

Backup Check

Advanced Backup Technologies

Backup Architecture

Backup Tips

4 Managing Backups

Simple Backups





SAP AG 2004

Why Should You Back Up Data?

Murphy's Law also applies to your system!

To avoid possible failures

Storage failure

CPU failure

Application failure




SAP AG 2004

Prevention: Redundancy

Reducing the risk of a restore or data loss by mirroring

Should be mirrored using

hardware or operating

system to reduce the risk

of a restore

Should be mirrored by

hardware or operating

system to reduce the risk of

data loss

sapdata1, ..., n

log_dir

instance directory

log_archive

Database containers

Online log files

Configuration files

Offline log files

Must be mirrored by

hardware, operating system,

or database

Must be mirrored by hardware

or operating system

As part of your backup strategy, you must consider the consequences of losing specific database files

and you must take appropriate measures to reduce these risks:

If database containers are lost, the database (or at least the tablespaces affected) has to be restored.To minimize the risk of losing a container, you should mirror the directories containing the DB2

containers: /db2/<DBSID>/sapdata<x> for UNIX; <drive:>\db2\<DBSID>\sapdata<y> for

Windows.

If an online log file is lost, the database server will perform an emergency shutdown, and a subsequent

restart recovery is not possible. Therefore, you have to restore the database. Because transaction

information is lost with the log file, data loss is unavoidable. Therefore, SAP recommends providing

redundancy for the online log directory by using DUAL LOGGING with the DB CFG parameter

MIRRORLOGPATH .

The same problems occur if database configuration files are lost. Again, redundancy of the instance

directory/file system is advised: /db2/<DBSID>/db2<dbsid> for UNIX; <drive:>\db2<dbsid> for

Windows.

If an offline log file is lost, the ongoing database operation is not directly affected. However, in case of

a restore, the database cannot be rolled forward to its latest consistent state. (An immediate database

backup is mandatory if loss of an offline log file is detected). To prevent this data loss, you should

mirror the offline log files.




SAP AG 2004

Prevention of data loss

Backup Check


Backup Architecture

Backup Tips

4 Managing Backups

Simple Backups

Backup Architecture




SAP AG 2005

Database Backup Objects

Computing center dataR/3 data

... R/3 archiving

... Interfaces

... SAP executables

Operating system

information

DB2 BACKUP

BRARCHIVE

Database backup

Offline log file(s)

Online log file(s)

DB2 configuration files

DB2 container(s)

???

USEREXIT

To implement your database backup strategy, you must know which objects are backed up and which

tools are used:

If you use the DB2 backup utility, the databases are backed up under control of the DB2 instance.DB2 backup processes create backup images that contain both the database’s data and the contents of

the database configuration files. This backup image does not consist of a copy of container and

configuration files, instead it includes their contents.

To back up the offline log files from the log_archive directory/file system, we recommend using the

SAP tool brarchive. (up to DB2 8.2). Starting with DB2 Version 8.2 you should use the new log file

management.

SAP data residing on operation system level (outside of the database) is not backed up with DB2 tools

or brarchive. You have to use operating system tools to do this.




SAP AG 2005

Backup

buffers

Processes:

DB2 buffer manipulators

DB2 media controllers

DB2 prefetchers

DB2 agents

DB2 Backup Model

db2agentdb2agent

db2meddb2meddb2meddb2med

db2bmdb2bmdb2bmdb2bmdb2bmdb2bm

db2pfchr db2pfchr db2pfchr db2pfchr

db2pfchr db2pfchr

Connection L ayer Database Layer

R E A D

W R

I T E

R E A D

R E A D

Tablespace 1 Tablespace 2 Tablespace 3

W R

I T E

The db2agent EDU (engine dispatchable unit) communicates with and controls the operation of two

special processes, db2med and db2bm, which are started with the backup.

The db2med processes are media controllers. They transfer data from backup buffers that are full. Thenumber of db2med processes depends on the number of backup devices or sessions.

The db2bm processes are buffer manipulators. Their task is to move data from tablespace to backup

buffer (not buffer pools). The number of buffer manipulators is set in parameter PARALLELISM.

Because one buffer manipulator will work on a tablespace at a time, you should not set the value for

PARALLELISM higher than the number of tablespaces. The db2bm processes use prefetchers to

improve parallelism to access the tablespaces.

The number of buffers must be high enough to keep the output device(s) busy.

The size of the backup buffer should be a multiple of the largest extent size, for example, 1024 or 2048

of 4 KB pages (4 MB, 8 MB).

Example: You have two DLT tape drives and five RAID1 mirrored disks for the containers. If you havecorrect striping of the containers, you will keep all disks busy when backing up five tablespaces at the

same time (PARALLELISM=5). With the given number of output devices (2), the degree of parallelism

(5), and two spare backup buffers, configure nine backup buffers. The quantity of prefetchers used by the

backup in this example is 5. Because the SAP system may also do prefetching, you should take this into

account when configuring the number of prefetchers in your database (DB CFG parameter

NUM_IOSERVERS).




SAP AG 2004

db2agentdb2agent




db2pfchr db2pfchr

Connection Layer Database Layer

Using DB2 Backup Parallelism

7 backupbuffers

db2 backup db PRD online

to /dev/nst0, /dev/nst1

with 7 buffers

parallelism 3

2x db2med

3x db2bm

R E A D

R E A D

R E A D


W R

I T E

W R I T E

In the BUFFERS parameter, you can specify how many memory areas are used to store data before it is

written to the target media.

The number of sessions is determined by the number of target devices (which must equal the number of media control processes).

When determining your optimal backup configuration, test a variation of the settings with subsequent

measurements.

V8.2: If not specified by user DB2 will automatically set the buffer size, the number of buffers and the

parallelism settings for both backup and restore operations.




SAP AG 2004


Backup Check


Backup Architecture

Backup Tips

4 Managing Backups

Simple Backups

Simple Backups




SAP AG 2004

DB2 Backup Types: Online Backup

Online backup

++

!!

Performed by DB2 instance

Database is available

DB2 8.2: Backup image is consistent

by using “include logs” option

DB2

Instance

Using the DB2 online backup, the DB2 backup utility runs in parallel to ongoing activities of database

applications, such as SAP applications.

Because transactions are executed during an online backup, the online backup image created cannotreflect a consistent state of the database. If a restore of an online backup is necessary, all changes that

occurred during online backup have to be applied again.

Therefore, it is absolutely necessary to provide all the log files that were written during an online backup

to obtain a consistent state of the database.

With DB2 Version 8.2 it is possible to use the INCLUDE LOGS parameter. This means, the online

backup image will contain the log information created during the online backup.

Because SAP operations continue during online backups, system availability is increased while high

application server buffer qualities are maintained.

Note: DB2 can perform online backups only if your database is operating in Log Retention mode.

The current log file will be archived when the online backup is complete.




SAP AG 2005

02:00

SAP and DB2 Database Backup Tools: Option 2

Performance

Space

Backup and Recovery

Configuration

Jobs

DBA Planing Calendar

DBA Log

Alerts

Database Administration Tasks

L61System

Category

DBA Planning Calendar

Calendar ID

Planning Mode

DBA Actions

Local

Action Pad

Archive Inactive Log Files to Device

Archive Inactive Log Files to TSMAutomatic Reorg

Check Table for Statistics Update

Full Backup Into TSMFull Backup to Device

Full Backup with vendor LibraryInitialize Tape In Device

Pattern Setup

04:0005:00

06:00

Tuesday, 24 Wednesday, 25 Thursday, 26 Friday, 27

Backup Mode

Action Details Edit Delete Add

Monday, 23

Full Backup Into TSM

Action Description

Planned Start 25.02.2005

Status

12:00:00

Action Parameters Recurrence

Backup Mode

1Number of I/O Sessions

Online

Offline

2Number of Buffers

1.024Buffer Size

1Parallelism

Pages

SAP DBA Planning Calendar

The SAP system provides the second method for performing DB2 database backups.

You can schedule periodic database backups if you use the DBA Planning Calendar (from transaction

ST04, choose Jobs → DBA Planning Calendar (or transaction DB13)).




SAP AG 2004


Backup Check


Backup Architecture

Backup Tips

4 Managing Backups

Simple Backups

Backup Check




SAP AG 2004

Backup Check: DB2 Recovery History File

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

-----------------------------------------------------------------------------------------------------------

B D 20050131220019001 N L S0000099.LOG S0000107.LOG

-------------------------------------------------------------------------------------------------Contains 26 tablespace(s):

00001 SYSCATSPACE

.

.

.

00025 PSAPUSER1D

00026 PSAPUSER1I

----------------------------------------------------------------------------

Comment: DB2 BACKUP L6C ONLINE

Start Time: 20050131220019

End Time: 20050131223144

----------------------------------------------------------------------------

00010 Location: /dev/nst1

db2 “list history backup all for L6C“

Recovery history file

• Range of relevant

log files

• Object

• Event

• Start and end time

• Location

The summarized backup information in the recovery history file can be used to recover all or part of the

database to a given point in time. The file includes the following information:

The part of the database that was backed up and how it was backed up The time when the backup was made

The location of the backup image (the device information)

The last time a restore was performed

Every backup operation (tablespace and full database) includes a copy of the recovery history file. The

recovery history file is linked to the database. When a database is dropped, the recovery history file is

deleted. Restoring a database to a new location restores the recovery history file. When a database image

is restored to the original database, the existing history recovery file is not overwritten.

To get an updated history file, create an empty tablespace and back up this tablespace on a regular basis

to get regular backups of the history file with a small overhead. If you archive log files to tape, you will

find a relatively new version of the history file on the latest tape.




SAP AG 2004


Backup Check


Backup Architecture

Backup Tips

4 Managing Backups

Simple Backups





SAP AG 2004

1. The following command places the

database in roll-forward pending

state

2. Now database is usable as standby

database

- Copy offline log files from primary

system to standby system

- Roll forward with copied log files

Split-Mirror Backup: Standby Database

Database instance

db2inidb <dbsid> as standby

Production Server

Database

Backup Server

With this advanced backup scenario, a second (standby) database is permanently in the state rollforward

pending . It cannot be accessed by applications, instead it waits for more transaction log files.

Follow these steps for using the split mirror as a standby database:1. Start the standby DB2 instance with the command:

db2start

2. Establish the standby database with the command:

db2inidb <dbsid> as standby

Whenever a log file is released on the production system, db2uext2 copies it to the log files's standard

target device (for example, log_archive). In addition, db2uext2 can create a second copy in a common

directory that is shared by the production and standby database servers.

On the standby database server, db2uext2 is used to retrieve the log files during rollforward.

These log files might not have to be applied immediately, but after a configured period of time. Thismakes it possible to prevent logical errors from being applied to the standby database.

A backup taken after db2inidb and before the rollforward phase can be restored on the production

system. A subsequent rollforward is possible.




SAP AG 2004

Production Server Backup Server

Split-Mirror Backup: Mirror as Backup Image

Database instance Database instance

111

222

1. Restore of file systems

from the backup server to

the production server

2. The following command isexecuted on the

production system, this

command performs the

redo phase of a restart

recovery and leaves the

database in roll-forward

pending state:

3. Roll-forward through log

files of production system

db2inidb333

DatabaseDatabase

db2inidb <dbsid> as mirror

The third way to split the mirror is to copy the database files without log files back to the source

machine.

To copy the database files back to the source machine without the log files, enter the followingcommands:

1. Start the production DB2 instance with the command:

db2start

2. Reestablish the mirrored copy with the statement:

db2inidb <dbsid> as mirror

3. Replay all work with the statement:

db2 'rollforward db <dbsid> to end of logs and stop‘

The log files of the primary system will be used for this action.




SAP AG 2004

1. The following commandperforms the redo and undo

phase of a restart recovery:

Split-Mirror Backup: Cloning a Database

Database instance

db2inidb <dbsid> as snapshot

Database

Backup Server

This method can be used to create a

homogeneous system copy.

On this slide, you see one way to use this database copy.

1. Start the standby DB2 instance with the command:

db2start

2. Use the following command to perform the redo phase and undo phase of a restart recovery:

db2inidb <dbsid> as snapshot

Note: Backups taken from this snapshot cannot be used to roll forward the primary system.




SAP AG 2004


Backup Check


Backup Architecture

Backup Tips

4 Managing Backups

Simple Backups

Backup Tips




SAP AG 2004

Backup Scenarios

Monday Tuesday WednesdayThursday Friday SundaySaturday

Online

Backup

Online

Backup

Online

Backup

Online

Backup

Online

Backup

Online

Backup

Offline1

Backup

Increm.

Backup

Increm.

Backup

Increm.

Backup

Increm.

Backup

Increm.

Backup

Increm.

Backup

Online

Backup

Split

Mirror

Split

Mirror

Split

Mirror

Split

Mirror

Split

Mirror

Split

Mirror Online Backup

of Split Mirror

Online

Backup

Split mirror + Hot standby database + monthly database consistency

check with inspect

The above figure shows SAP's recommendations for use of the different backup options. The T-shirt size

model (S, M, L, XL, and XXL) can be used for the database size and system availability.

There is no technical reason to force customers to create an offline backup with DB2 database servers.You could have your own implementation-specific requirements that might lead to the conclusion that

offline backups are necessary although downtime will be introduced. In this case, SAP will endorse your

decision as well.




SAP AG 2004

DB2 Backup Performance Tips

Use more than one process to exploit parallelism

Provide a sufficient number of backup buffers:

# backup buffers = # output devices + parallelism + 2

Provide enough prefetchers for online backup

# prefetchers = # physical disks + 2 + parallelism

# prefetchers <= 20

DB2 8.2: Self-tuning backup and restore operations

If not specified, the command will automatically choose:

WITH num-buffers BUFFERS

PARALLELISM n

BUFFER buffer-size

For optimal performance, you should use more than one process to exploit parallelism. To do this, you

must have at least one backup buffer available for each of the following:

Each db2med process writing to a medium Each db2bm process transferring data from tablespaces to backup buffers

In live systems, backup buffers are transferred to media much slower than they are filled by the db2bm

processes. You should therefore always have filled buffers waiting for db2med processes.

During online backups, backup buffers reside in random access memory (RAM) together with other

programs and buffers (buffer pool). You must take this into account when defining your memory sizing.

Because the utility heap size (DB CFG parameter UTIL_HEAP_SZ) of SAP systems is normally rather

small, you should manually increase the heap to accommodate a larger number of backup buffers or

larger backup buffer sizes.

V8.2: If not specified by user DB2 will now set the number of buffers, the buffer size and the

parallelism settings for both backup and restore operations by itself. The values chosen are

based on the number of processors, the amount of memory, and the database configuration.




SAP AG 2004

Legacy ADM<DBSID> Backup Tools

Containers

Online log files

Database configuration

SAP database <DBSID>Admin database ADM<DBSID>

RECOMMENDATION

SAP 4.XX: Admin tools release >= 20

SAP 6.XX: Admin tools release >= 9

Note:

- To restore the admin database, you should use sddb6ins –r.

- With SAP 4.0B and 4.5B, sddb6mir is needed to mirror tables from

ADM<DBSID> to <DBSID> to be displayed in transaction DB12.

brarchive

We strongly recommend using the most current Admin Tool release.

The contents of the admin database ADM<DBSID> are backed up during each brarchive run.

The restore of the Admin database can be performed with:

sddb6ins -r

For more information, refer to the Database Administration Guide for SAP on IBM DB2 Universal

Database for Unix and Windows and the "Tools for DB2 Database Servers" unit.




SAP AG 2005

Do the exercises

?


Unit Actions




SAP AG 2005

Database Recovery

2 8Tools for DB2

Database Servers

6 Database Recovery


1 Definitions 7 Storage Management

9

11 Problem Analysis


5 Managing Backups

12 Appendix







SAP AG 2005

Database Recovery

Contents:

Preparing for Disaster Recovery

DB2 Database Recovery Techniques

Executing Recovery Procedures

Objectives:


Describe Restart Recovery and Rollforward Recovery

Decide on the most appropriate recovery method for your

system

Implement a viable recovery strategy, including

documentation




SAP AG 2005

Organization of Recovery

Restore Procedure

6 Database Recovery

Restore Detail

Organization of Recovery




SAP AG 2005

Disaster Recovery Scenario

Although many restart, restore, and recovery procedures had been

tested, a complete disaster recovery plan did not exist.

The documentation provided did not include essential elements,

such as problem assignment and follow-up, off-site storage of

disaster recovery procedures, clearly defined roles and

responsibilities, consistent terminology referring to vital business

processes, and documented recovery sites with addresses and

directions.

An incomplete disaster recovery plan could result in failure to

recover vital internal systems in the event of a disaster and could

lead to loss of data...

The above self-assessment was made by an SAP customer. It highlights the importance of these key

recovery issues:

- Disaster recovery plan (including recovery sites)- Documentation (storage and availability)

- Training for recovery

- Role descriptions

Although loss of data does not sound dangerous, you should understand that if there is a loss of

production data, your business processes are exposed. In the worst case, you are risking the financial

health of your company.




SAP AG 2005

Backup Frequency versus Rollforward Time

Having only a few backups per year

means long rollforward timesThe cost of backups is proportional

to the number of backups made

Backup Frequency

Cost

Recovery Cost Backup Cost

Backup Frequency

Cost

Restore and Rollforward will take time! Make sure you

know your system's rollforward performance

Recovery cost is the estimated cost of downtime. Downtime is dependent on the time to recover the

database. Restoring a backup and rolling forward the database will be more time consuming with more

log files to process (when fewer backups are made). With more frequent backups, your downtime will be

shorter because fewer log files have to be applied after restore.

Backup cost includes tape cartridges used, tape unit and tape robot down payment, and wear and tear. It

also includes system performance impact through added I/O. The cost will increase with more backups

made.




SAP AG 2005

0

500

1000

1500

2000

2500

Find the optimal number of backups

Expensive Area

Less Expensive

Area

Recovery Cost Backup Cost Total Cost

Backup Frequency

There is an optimum number of backups per year

The calculations shown here clearly show the importance of finding the optimal backup frequency.

The total cost for the strategy is a sum of the backup cost and the recovery cost. When you create your

backup strategy, you must consider the following: Having consistent readable backups is the only way to ensure a database can be recovered.

Ensure that you use high-quality disks and/or redundancy to minimize the need for recovery.

Fast recovery is crucial. Test your system’s throughput. Do not rely on estimates.

For critical systems, consider advanced solutions based on DB2 WRITE SUSPEND/RESUME.

Remember, database administrators are always asked about backup costs, but never about the cost of

downtime.

Check if incremental or delta backup improves your situation.




SAP AG 2005

Recovery Organization

Recovery Procedure

6 Database Recovery

Restore Details

Recovery Procedure




SAP AG 2005

Rollforward recovery:

restore + rollforward or

recover

Manualintervention

DB

DBDatabase restart

Automatic, nointervention

needed

Restart recovery:

At connect time (AUTORESTART)

DB2 Recovery Techniques

DB

DB2 offers two types of recovery:

A rollforward recovery must be performed when all or parts of the disks have been lost or corrupted.

The process is lengthy and requires manual intervention. A restart recovery is performed by DB2 whenever the database server has not been properly shut

down. In this case, the contents of the buffer pool(s) are lost. This can be caused by a system hardware

failure or power loss.

- Restarting the DB2 database initiates a crash recovery. DB2 reruns all transactions that were

performed previously but were not recorded on disk. The DB2 log file header file

(/db2/<DBSID>/db2<dbsid>/NODE0000/SQL00001/SQLOGCTL.LFH) contains the log sequence

number, which will be used as a starting point for this type of recovery. If you lose this file, you

will not be able to restart your database and rollforward recovery must be performed.

- To ensure that DB2 automatically starts the restart recovery, the DB2 database parameter

AUTORESTART must be set to On, the default setting in SAP systems.




SAP AG 2005

Restart Recovery: Insights (1)

t

TXN 1

TXN 2

TXN 3

STOPSoft Checkpoint

Databases that have not been properly closed or shut down willhave to be restarted.

During Restart

Transaction 1 will be re-applied to the database

Transaction 2 is already applied to the database, no need to re-apply

Transaction 3 will be rolled back (undone)

Watch out for time required for restart recovery!

Restart recovery involves re-applying changes to the database that have not been written to disks.

During Restart, the database server performs the same workload that was already performed during

normal processing since the last soft checkpoint. Therefore, you should look closely at restart performance by adjusting the SOFTMAX parameter

according to your system availability requirements:

With small SOFTMAX, restart takes less time but there are more frequent soft checkpoints.

With larger SOFTMAX, restart potentially processes more log records because there are fewer

frequent soft checkpoints. In addition, there will be less disk activity during normal processing.




SAP AG 2005

Restart Recovery: Insights (2)

2003-01-07-16.16.21.860310 Instance:db2lx9 Node:000

PID:3355(db2agent (LX9)) TID:1024 Appid:*LOCAL.db2lx9.010B97211621

base sys utilities sqledint Probe:30

Crash Recovery is needed.

2003-01-07-16.16.22.178019 Instance:db2lx9 Node:000PID:3355(db2agent (LX9)) TID:1024 Appid:*LOCAL.db2lx9.010B97211621

recovery manager sqlpresr Probe:410 Database:LX9

Crash recovery started. LowtranLSN 000000026895000C MinbuffLSN 000000026895000C



recovery manager sqlprecm Probe:125 Database:LX9

Using parallel recovery with 9 agents 26 QSets 208 queues and 64 chunks



recovery manager sqlprecm Probe:400 Database:LX9

DIA2051W Forward phase of crash recovery has completed. Next LSN is

"00000002689534DB".



recovery manager sqlpresr Probe:3170 Database:LX9

Crash recovery completed. Next LSN is 00000002689534DB

DB not

properly

shut down

LowtranLSN

MinbuffLSN

Next LSN

At database restart, db2diag.log will show:

Restart recovery involves the following steps:

Detection for proper shutdown of the database. If the database was stopped improperly, restart

recovery will be needed. Re-applying the changes to the database (REDO phase).

Removing uncommitted changes in the database (UNDO phase).

LowtranLSN – LSN of the first log record written by the oldest uncommitted transaction is read from

LFH. With multiple uncommitted transactions, this is the oldest log record (sorted by write time).

MinbuffLSN – LSN of the oldest change to unwritten data pages. Because these pages have not been

written (checkpoints or other events), the changes to pages, starting from this page and sorted by LSN,

have probably been lost.

NextLSN – LSN of the next log record that will be written. It is higher than all the LSNs used in the

system.

DB2 adjusts the number of DB2 agents used in the restart recovery based on the number of CPUs in the

database server. The multiple DB2 agents re-apply the transactions in parallel. This can be seen in the

slide (Using parallel recovery…).




SAP AG 2005

Performed with operator intervention:

Restore the offline/online database backup

Restore the log files (when using indirect archiving)

Roll forward using log files (retrieved on demand)

Containers

Online log files

Database configuration

Backup

DB2 RESTORE

111

Log files

- with indirect archiving -

BRRESTORE

222

333

Roll forward

Rollforward Recovery

With a rollforward recovery, a backup image (online or offline) and all the subsequent log files created

after the backup are used to recover the database to a consistent state.

When you design your backup strategy, consider how much time you want to spend on a recovery. Acurrent backup enables you to perform a faster recovery because there are fewer log files to re-apply.




SAP AG 2005

Database

(rollforward

pending)

list historylist history restore

rollforward

111 222

333

Database Recovery: Work Flow Details

444

Log Files

555

rollforward stop

brrestore

Database

(available)

You must perform a database recovery in several steps:

Use the DB2 recovery history file to search for the timestamp of the most recent backup. If you

performed full database backups, you can identify the backup image using this timestamp. If the sizeof your database mandated backups on tablespace level, you must search for multiple backups.

Restore the database using the DB2 command restore.

Retrieve the log files that are requested in the recovery history file. The file specifies the log files

needed to make an online backup available, for example, the log files created during the online

backup. If you want to recover all operations made to the database, you must retrieve all the log files

that have been created since the online backup was started.

In the rollforward phase, use the DB2 rollforward command to re-apply the transactions using the log

files that have been retrieved.

Finally, you must instruct the database to stop the rollforward. The database will then be made

available.

This example does not show the rollback phase, which is the last step in this process. During the

rollforward process, all committed transactions are reapplied to the database. At the end of the

rollforward, a list of open transactions exists. These transactions are still waiting for a commit. For data

consistency reasons, open transactions are then rolled back to make sure that changes that are not

committed are not applied to the database.




SAP AG 2005

Restore Command

sapdb2:db2l62>db2 “restore database L62 from /dev/rmt0”

The RESTORE DATABASE command completed successfully.

DB2 command line processor (CLP)

You can issue a DB2 restore command directly from a CLP session. If you are not familiar with the

syntax of the utility, you can obtain help by issuing the following command:

db2 '? Restore' The Restore procedure in a multi-partitioned environment is even more complex, because you had to

perform this procedure for each database partition. In addition, you first had to perform the restore of the

database partition where the system catalog resides.




SAP AG 2005

Recover Command

sapdb2:db2l62>db2 “recover db L62 taken at 20050304204148 ”

The RECOVER DATABASE command completed successfully.

DB2 command line processor (CLP)

The Recover command, which is introduced with DB2 Version 8.2, selects a suitable backup image and

the log files required to recover the database to a specific point in time or to end of logs. The recover

command performs the restore and the rollforward operation together. This also applies in a multi-

partitioned environment with one command call.

This approach always requires an up-to-date history file because, all information about backup images

and log files is retrieved from there.

The database recovery with the recover command is performed in two phases:

In the first phase the recover command selects the backup from the history file and restores the

backup image. This is done for all partitions.

During the second phase the recover command performs the database rollforward to the specified

point-in time.

Note: DB2 use the recover command only if your database is operating in Log Retention mode and the

V8.2 based log file management is used.




SAP AG 2005

Incremental and Delta Restore

+ + +

Backup

Restore

Full Incremental Delta Logs

Sunday Monday Tuesday Wednesday Thursday Friday SundaySaturday

Incremental backups can be restored by restoring the full backup image and the incremental backup

image (only possible when database configuration parameter TRACKMOD is set to ON ).




SAP AG 2005

Restore Example with Incremental Backups

sapdb2:db2l61>db2 “restore database L61 incrementalautomatic from /backups taken at 20050304204148”

The RESTORE DATABASE command completed successfully.

Use ONE command to perform this operation

The text "taken at” ensures that only a specific backup is accepted

Utility will search for backup images

DB2 incremental backup images can be retrieved by specifying the newest incremental backup

timestamp and the keyword incremental automatic. In this case, DB2 selects the appropriate full

backup image (if possible) initially. Then, DB2 applies the incremental changes that have been stored in

the specified backup image.

The contents of the /backups directory will be as follows:

-rw-r----- 1 db2l61 dbl61adm 12615680 Mar 4 20:34

/tmp/SAMPLE.0.db2l61.NODE0000.CATN0000.20050304203434.001








SAP AG 2005

DB2 Restore Performance Tips

Restorebuffers

Processes:

DB2 buffer manipulators

DB2 media controllers

DB2 page cleaner

DB2 agents

db2agentdb2agent




db2pclnr db2pclnr

Connection Layer Database Layer

W R I T E

R E A D

W R I T E

R

E A D

W R I T E

W R I T E

R E

A D

R

E A D

W R I T E

W R I T E

W R I T E

W R I T E


The db2agent EDU (engine dispatchable unit) communicates with and controls the operation of the

db2med and db2bm processes, also during a restore.

During restore operations, the media controllers transfer data from device to restore buffers. When a buffer is full, it is transferred to disk on FIFO basis. The number of db2med processes depends on the

number of restore devices or sessions. The parameter PARALLELISM may be used during a restore, but

if you are not using multiple tape devices or TSM sessions, multiple db2bm processes are not used. It is

always much faster to empty the buffers to disk than to fill them from tape or TSM. For example, you

should use PARALLELISM when reading the backup images from disk.

The number of buffers should be large enough to keep the media controllers and their devices busy

(more than 2).

The size of the restore buffer must be equal to or a multiple of the backup buffer size that you specified

when the backup image was taken, for example, 1024 or 2048 of 4 KB pages (4 MB, 8MB). If you

achieve good backup performance with a certain backup buffer size, you can ensure excellent restore

performance by using the same size for the restore buffers.

Note: During a restore, DB2 uses page cleaners to transfer the buffer contents to disk.

V8.2: If not specified by user DB2 will now set the number of buffers, the buffer size and the parallelism

settings for both backup and restore operations.




SAP AG 2005

Database Recovery: Rules of Thumb (1)

Murphy‘s law also applies to your database server. Be prepared

for a disaster when you least expect one.

Check the database diagnostic log file db2diag.log after a crash.

Understand the reasons for the recovery.

In case of doubt:

Hands off! No recovery is better than an incorrect recovery.

1

2

3

When performing a database recovery, apply the rules of thumb listed above.

As long as you have adhered to a proper data backup strategy, it should always be possible to restore

your database to a complete and consistent status. Depending on the knowledge and experience of your administrator, you may need to call on external help.

The db2diag.log file most likely shows the reason for the database recovery, from the database software

perspective. You must be able to understand the reasons to ensure that they never happen again.

Ensure that your last backup is available either on tape or on a hierarchical storage management system

such as Legato Networker and TSM.

Using a tested recovery scenario allows you to minimize the number of errors during this process. You

should practice recovery using the tools with sample databases.

Operation of SAP software is not possible while the database is being restored.




SAP AG 2005

Database Recovery: Rules of Thumb (2)

Perform the recovery using the DB2 Command Line Processor.

Check that your last data backup is available.

Make sure you have tested the recovery procedure to reduce the

chance for errors during the time-consuming restore/rollforward

process.

4

5

6

If you encounter problems during recovery, you should request help from SAP. As soon as you have

resolved the problems with the help of SAP, continue with the recovery.

Using direct DB2 commands to perform recovery is not an easy task and should be performed by veryexperienced DB2 database administrators only.




SAP AG 2005

Restoring the Administration Database

Use the most current archive

sddb6ins -r

ADMIN

DB

sddb6ins will read the tape archives

Only on Legacy Log

File Management

An export of essential information about that database is archived. The brarchive command performs

this automatically. The backup image is a compressed archived file (CAR file) consisting of individual

table exports. This image does not contain action or action detail information such as that shown on

SAP-DB2admin's journal pages. This data is deleted after the administration database is recreated using

this image.

With up-to-date Admin Tools, you will be able to restore the administration database using

sddb6ins -r.

For details of the new sddb6ins functionality that replaces the sddb6mir -r option, please refer to SAP

Note 533979.

Note: Only necessary by using legacy log file management.




SAP AG 2005

Organization of Restore

Restore Procedure

6 Database Recovery

Restore Details

Restore Specials




SAP AG 2005

If your recovery procedure fails,do not hesitate to open a customer message

Database Restore Tips

DB2 Version 8: Use the LOCALTIME clause to prevent

miscalculations.

Ensure that all your offline log files are in a safe location.

Before starting Restore, copy the online log files to a safelocation and, if possible, backup all database and DB2 Instance

files using OS tools.

Schedule an online backup of the database as soon as possible

after recovery.

Ensure you know how to perform a restore using DB2 CLP.

Recovery situations can be extremely complicated. Rash actions can result in permanent loss of data,

which makes a complete recovery impossible.

Remember to make a backup copy of all online log files first. Please test your recovery scenario.

Please test it again.

In addition, schedule regular tests. Regular tests are necessary because they will be your life insurance.




SAP AG 2005

Database Recovery: Unit Summary

Now you are able to:

Describe Restart Recovery and Rollforward Recovery

Decide on the most appropriate recovery methodfor your system

Implement a viable recovery strategy, including

documentation




SAP AG 2005

Do the exercises

?


Unit Actions




Exercises

Unit: Database Recovery


• Recover a database

• Perform precise rollforward operations


following exercises, please start your Web browser. Use the server name and portnumber provided by your instructor and enter the following URL:

http://<servername>:<portnumber >. A graphical desktop will appear in your browser window. Please enter the password sapdb2 to access the desktop. From now on, youcan use the desktop to open multiple windows.

You will also be able to reconnect to your desktop, in case you close your Web

browser window.

1-1 Restore your training database <Txx>

1-1-1 Delete all containers belonging to PSAPUSER1D. To determine the location of the containers, use db2 “list tablespace containers for <tablespace id>”. To get

the tablespace id, use db2 “list tablespaces” . Does the deletion of containers

result in error messages in the db2diag.log or changes in the tablespace status?

1-1-2 Restore the database from the backup that was made during the exercises in the Managing Backups Unit.

1-1-3 What is the status of the database after the restore has completed?

1-2 Rollforward Operations

1-2-1 When is the use of DB2 ROLLFORWARD during a recovery operation

optional, and when is it mandatory?

1-2-2 Make sure that your training database <T xx> is running in Log Retention Modeand truncate the DB2 log file several times. What can you see in log_dir?

1-3 Is there another possibility to rebuild your database? What is the difference?




SAP AG 2005

Storage Management

Contents:

Tablespace Types and Storage Locations

Space Allocation in DB2 Tablespaces

Adding and Distributing Containers

Reorganization Strategies

Objectives:


List the different DB2 tablespace types

Describe space allocation and management within thedatabase

Decide on the best reorganization strategy




SAP AG 2005

Insights

Table and Data Management

Reorganization

Space Management


Tablespace Layout

Insights




SAP AG 2004

Examples of DMS and SMS

Tablespace C11#BTABD is a DMS tablespace because it is created on

containers

/db2/C11/sapdata1/NODE0000/C11#BTABD.container000



Tablespace C11#STAB resides in directories sapdata1 and sapdata2,

therefore, it is SMS-based

/db2/C11/sapdata1/NODE0000 SQL00001.INX

SQL00001.DAT

SQL00001.LFSQL00001.LB

SQL00001.LBA

/db2/C11/sapdata2/NODE0000 SQL00001.INX

For database-managed space (DMS), the containers provide storage by being implemented as files or

being implemented as raw devices. This space is pre-allocated and initialized during container allocation.

For system-managed space (SMS), data is stored by DB2 in many different files: Each table is stored in a file (DAT)

Each index is stored in a file (INX)

Each LONG column is stored in a file (LF).

Long Field column data and allocation information are stored in a file (LB, LBA).

To check the types of the tablespaces in your SAP system, use transaction ST04, choose Space ->

Tablespaces.

Alternatively, you can use the command:

DB2 ‘LIST TABLESPACES’




SAP AG 2005

Database Objects: Tables and Indexes

TableTablen:1

DB2DB2data pagedata page

(4 KB, 8 KB,(4 KB, 8 KB,

16 KB, 32 KB)16 KB, 32 KB)

Logical view Physical view

RecordsRecords

IndexIndex

NodeNode

DB2DB2

index pageindex page

(4 KB, 8 KB,(4 KB, 8 KB,16 KB, 32 KB)16 KB, 32 KB)

1:1

Tables contain records of data. One or more records of a table are stored in a DB2 data page. Pages are

the minimum I/O unit of DB2. A DB2 data page can contain one or more records. But a record must fit

in a data page.

If a record of a table does not fit in a 4096 byte (4 KB) page, the table must be defined in a tablespace

that is based on a larger page size. DB2 also provides page sizes of 8192 bytes (8 KB), 16384 bytes (16

KB), and 32768 bytes (32 KB).

For improved access to data, indexes are predefined by SAP developers. Indexes can also be defined

later as further need arises. An index is defined using a few columns of a table, which are accessed

frequently. Index data structures allow for efficient retrieval of small quantities of data. Indexes are

based on nodes, their storage is maintained within DB2 index pages. Because the pointers pointing to

other index nodes are also stored in the index pages, storage of indexes is not as compact as the storage

of data records.




SAP AG 2004

Hard Limits for DB2 Tablespaces

...

Tablespace Partition 1

Partition 2

Partition N

Total tablespace size max =N x partition tablespace size max

Example:

N=8 (partitions)

512 GBmax (32KB page size)

4 TB Tablespace sizemax

The maximum size of a DB2 tablespace is based on the following values:

The maximum per-partition size of a tablespace varies with page size:

- with 4 KB page size, the maximum per-partition tablespace size is 64 GB




Having a maximum number of 1000 partitions, the actual hard physical limit for a DB2 tablespace is

finally approximately

- 64 TB (4 KB)

- 128 TB (8 KB)

- 256 TB (16 KB)- 512TB (32 KB)

SMS tablespaces have different limits on a per-object level.




SAP AG 2004

Row Storage Details

TableDB2 data page

Each row can be located by the record identifier (RID).

The RID describes the detailed location of the row in the

tablespace.

Header

Data pages are structured into three parts:

Page header – Contains information such as last change transaction LSN (log sequence number). This

information is important for recovery purposes and for the backup utilities. Slot directory – Contains pointers to the actual storage location of the data record in the page. These

pointers are offsets into the page.

Space – Allows the storage of the DB2 records.

The maximum size of the records stored in a page varies with the page size. On a 4 KB page, the

maximum length of a row is 4005 bytes. If more data is to be stored in a row, one or more columns of

the table have to be stored in LOB or LONG fields. Alternatively, the table has to be stored in a

tablespace with a larger page size. With 8 KB pages, the maximum length can be 8101, and so on. More

details can be found in the IBM DB2 manual, Administration Guide: Planning , in the "Physical

Database Design – Space Requirements for User Table Data" section.

The maximum size of a row can be calculated based on information from the IBM DB2 manual, SQL Reference Volume 2, in the "CREATE TABLE" section.




SAP AG 2005

DB2 Data Pages

Page

Header Slot Directory (255 max)

Row location is a tuple (page number, slot number)

Note: Rows may not be larger than a page

RID

Each record in DB2 can be located by the record identifier (RID), which consists of the data page

number (3 bytes) and the entry number in the slot directory (1 byte). The slot entry contains the byte-

offset to the data page, which must be used as the start location for the data record.

Deleted space is marked in the slot directory, and can be used again for new records of the same table.

The slot directory contains a maximum of 255 entries. For tables with small records compared to the

page size, there might be unused space on each of the pages.

Artificial Example: With 4 KB pages and a table defined with a single CHAR(1) field, only 255

records can be stored on each data page. The inaccessible free space for each of the pages of the table

is more than 3 KB.

Note: The above problem may only exist in SAP systems with page sizes of 8 KB, 16 KB or 32 KB.




SAP AG 2005

DB2 Index Trees and Index Pages

1 12 17

15 175 12

Root Node

Intermediate Nodes

Leaf Nodes

Index Pages

Data Pages

Level 1

Level 2

Level 35RID

10RID

11RID

12RID

1

Indexes are structured in B+ trees, which consist of the following nodes:

A single root node

Many leaf nodes, which do not point to further nodes

Intermediate nodes, which are in-between

The DB2 Index Manager looks up a key value in the index as follows:

The DB2 Index Manager looks up the root node and searches for a key that is equal to or larger than

the value.

Then, the DB2 Index Manager looks up the page number stored with this key, loads the respective

page, and searches the page for a key that is equal or larger.

This process continues until the DB2 Index Manager finds a leaf page containing the key value

searched for.

With the RID stored with the value, DB2 looks up the page and addresses the record if necessary.

The administrative storage overhead in each index page is 100 bytes. The rest of the page is free for

index values, page numbers, or RIDs. Free space on index pages is provided by DB2 during page

allocation to ensure that key values can be inserted later.




SAP AG 2004

Extent Allocation: Inside DB2 DMS Tablespaces

Header

Object Table

SMP

EMP T1

DATA T1

EMP T2

DATA T2

SMP

Object Table

Tablespace

Extent #

1

2

3

4

5

N

N+1

Extent types within tablespaces:

Object table extent

Space map pages

Extent map pages

Data / index extents

Extents are a series of contiguous pages:

Extent size is fixed and predefined per tablespace

Space is allocated by extent

The Object Table Extent provides the directory of objects in the tablespace.

DB2's SMPs (Space Map Pages) provide a bitmap describing the layout of the next

1000 Extents (4 KB page size)




Extent Map Pages describe the extents that are used for an object. They contain the page numbers of the

first data page in the extents of the object. The tablespace parameter EXTENTSIZE is used to define this

storage unit.




SAP AG 2004

Details on Extent Layout in Tablespaces

Output from DB2DART:

Object Table Space Map Extent Map Data Extent

Extents belonging to table object 9

Header

Administrative extents (header, object table, SMP)

DART (V8.1.0) Report:

Highwater Mark: 1472 pages, 92 extents (extents #0 - 91)

[0000] 65534 0x0e [0001]65534 0x0e [0002] 65535 0x00 [0003] 4 0x41*[0004] 4 0x01* [0005] 5 0x41* [0006] 5 0x01* [0007] 6 0x41*[0008] 6 0x01* [0009] 7 0x41* [0010] 7 0x01* [0011] 7 0x01[0012] 8 0x41* [0013] 8 0x01* [0014] 9 0x41* [0015] 9 0x01*[0016] 9 0x01 [0017] 9 0x01 [0018] 9 0x01 [0019] 9 0x01

[0020] 9 0x01 [0021] 9 0x01 [0022] 9 0x01 [0023] 9 0x01[0024] 9 0x01 [0025] 9 0x01 [0026] 9 0x01 [0027] 9 0x01[0028] 9 0x01 [0029] 9 0x01 [0030] 9 0x01 [0031] 9 0x01

[0032] 9 0x01 [0033] 9 0x01 [0034] 9 0x01 [0035] 9 0x01[0036] 9 0x01 [0037] 9 0x01 [0038] 9 0x01 [0039] 9 0x01

[0040] 9 0x01 [0041] 9 0x01 [0042] 9 0x01 [0043] 9 0x01[0044] 9 0x01 [0045] 9 0x01 [0046] 9 0x01 [0047] 9 0x01[0048] 9 0x01 [0049] 9 0x01 [0050] 9 0x01 [0051] 9 0x01

[0052] 9 0x01 [0053] 9 0x01 [0054] 9 0x01 [0055] 9 0x01[0056] 9 0x01 [0057] 9 0x01 [0058] 9 0x01 [0059] 9 0x01[0060] 9 0x01 [0061] 9 0x01 [0062] 9 0x01 [0063] 9 0x01

[0064] 9 0x01 [0065] 9 0x01 [0066] 9 0x01 [0067] 9 0x01[0068] 9 0x01 [0069] 9 0x01 [0070] 9 0x01 [0071] 9 0x01

[0072] 9 0x01 [0073] 9 0x01 [0074] 10 0x41* [0075] 10 0x01*[0076] 11 0x41* [0077] 11 0x01* [0078] 12 0x41* [0079] 12 0x01*

[0080] 13 0x41* [0081] 13 0x01* [0082] 14 0x41* [0083] 14 0x01*[0084] 14 0x01 [0085] 9 0x01 [0086] 14 0x01 [0087] 9 0x01[0088] 14 0x01 [0089] 9 0x01 [0090] 9 0x01 [0091] 6 0x01

The extents shown in the table include the following:

0000 and 0001 – the tablespace header and the tablespace object table

0002 – the first space map page (SMP)

0003 – the first object‘s extent map page (EMP), the object ID is 4

0004 – the data page for object ID 4

Object 9 occupies many extents (starting with 0014) and is fragmented within the tablespace because it

also occupies extents 0085, 0087, and 0089 to 0090.

To get the above listing of a tablespace, use the following command:

db2dart <dbsid> /DHWM




SAP AG 2004

Extent Allocation Within Containers

Tablespace

Container0 Container1

Extent

with extent size 2

Page

DB2 Extents are allocated by “round robin”

The allocation of new space for DMS tablespaces takes place on a per-extent basis.

Each tablespace is defined based on an extent size, which is a multiple of the page size of the tablespace.

Each new extent is allocated in the next DB2 container until all containers have been used. Then, thefirst container is used again.

This process ensures distribution of data to the available containers.

To get detailed information about the tablespaces in your SAP system (for example page size, extent

size, allocated pages, used pages or number of containers), use transaction ST04, choose Space ->

Tablespaces.

Alternatively, you can use the following command:

DB2 ‘LIST TABLESPACES SHOW DETAIL’




SAP AG 2005

Insights


Reorganization

Space Management


Tablespace Layout

Space Management




SAP AG 2005

Managing Tablespaces with ALTER TABLESPACE

Command or Statement ?

New space is added to tablespaces

with the

ALTER TABLESPACE ...

DB2 SQL statement

New space must be added manually or

via the SAP system

There are important differences between DB2 commands and DB2 SQL statements. Most notably,

statements are integrated into the transactional concept of the database server. You may COMMIT a

statement or ROLLBACK the statement.

Transactions are stored in DB2 log files by their nature because they must be re-played during recovery

operations. Because DB2 storage management commands are statements, they are stored in log files as

well, and reapplied during rollforward.

An example of adding a container (size 100000 pages) to a tablespace using the DB2 SQL statement is

shown here:

ALTER TABLESPACE C11#BTABD ADD (FILE´/db2/C11/sapdata1/NODE0000/C11#BTABD.container007´ 100000)

SAP recommends to use transaction ST04. Choose Space -> Tablespaces, then mark the appropriate

tablespace and choose Change.




SAP AG 2004

Levels of DMS Storage Management

Manual storage management

Manual creation of containers

Manual resize of container

Semi-automatic storage management - Autoresize

Manual creation of containers

Automatic resize of containers

Automatic storage management – Autostorage

Automatic creation of containers

Automatic resize of containers

There are three levels of DMS storage management: automatic, semi-automatic and manual.




SAP AG 2005

Rebalancing Extents Involves Copying all Data

“Additional I/O"

Before

After


1 3 5 2 4 6

7 9 11 8 10 12

Container0 Container1 Container2

1 4 7 2 5 8 3 6 9

10 11 12

Unused Extents

SQL0289C

Database has a

tablespace full situation

Therefore, 'Container2'is added

When running out of free extents within a tablespace, at the least you will have to add more space to the

tablespace. This can be done by adding a new container.

The example in the figure shows the reassignment of extents within containers. Because a new container is available, the extents will be redistributed by DB2.

This will result in balanced I/O workload, however, because almost all of the extents will be moved, the

input/output effort involved with extending a tablespace is considerable.

Because runtimes on a very large DB2 database may be very long, you should try to schedule the

operation during periods of low system activity such as weekends.

To monitor the growth of the tablespaces in your SAP system, use transaction ST04, choose Space ->

History -> Database and Tablespaces. You can get the statistics for the last days, weeks or months.

SQL0289C also occures, when the maximum number of DB2 extents has been reached.




SAP AG 2004

Tablespace

Stripe Sets: Implicit Use


Range 0

Range 1

With containers of different size, multiple

ranges are used.

Rebalancing is performed on a per-range basis

(bottom up).

With different container sizes:

Stripe Set 0

With different container sizes, DB2 automatically defines multiple ranges, based on striping. Although

the initial striped allocation of extents is possible in both containers in the example, the containers will

be fully allocated at a certain moment. Then, more extents can be allocated only in Container2. This will

define a new range (range 1).




SAP AG 2004

Multiple Stripe Sets: Implicit Use

ALTER TABLESPACE ... RESIZE (file ‘<path>/Container0’ <size>)

Only range 1 will be rebalanced!


Range 0

Range 1

Stripe Set 0

Because Container0 in Stripe Set 0 is resized, the Stripe Set needs to be rebalanced.

Because Range 0 is already balanced, only Range 1 has to be rebalanced.




SAP AG 2004

Multiple Stripe Sets: Explicit Use

ALTER TABLESPACE ... BEGIN NEW STRIPE SET (file ‘<path>/Container2’ <size>)

Because Container2 is added to a new stripe set, no

rebalancing will occur!


Stripe Set 0 Stripe Set 1

Container2

Tablespace

You can also explicitly add a container to a new stripe set, to avoid rebalancing.

In the example in the figure, Container2 is added in a new stripe set and no rebalancing is made. This is

in contrast to traditional DB2 behavior where rebalancing was mandatory. A tablespace map for a tablespace with two containers is shown below (initial configuration of the above

example):

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[ 0] [ 0] 0 735 11775 0 735 0 2 (0,1)




SAP AG 2004

Semi-Automatic Storage Management

/db2/<DBSID>/<path1>/<Tablespace1>.container1



.

.

.

Creation of tablespace containers:

User Auto-resize

Tablespace

Containers

AutomaticResizing

Semi-automatic Storage Management, also called auto-resizing, contains the possibility to switch from

manual to the automatic resizing of tablespace containers. For this, it is necessary to reconfigure the

tablespaces with the following command: db2 „alter tablespace <tablespacename> autoresize yes“

If auto-resize is enabled but INCREASESIZE is not specified, DB2 will determine the appropriate rate

of growth.




SAP AG 2004

Automatic Storage Management

/db2/<DBSID>/<path1>/

/db2/<DBSID>/<path2>/

Creation of autostorage

paths:

User Autostorage

Automatic tablespace

container creation and resizing

To Use the Automatic Storage Management it is necessary to create the database or the tablespace with

the automatic storage parameter:

db2 „create db <dbsid> automatic storage yes on /db2/<dbsid>,/db2/<dbsid>/<additional Storage Path> using codeset ISO8859-1territory en_US collate using IDENTITY dft_extent_sz 8 with ´SAPdatabase <dbsid>´“

db2 „create tablespace <tablespacename> managed by automatic storageinitialsize <size> increasesize <size> maxsize <size>“

Means a minimum of administrative overhead by administrator.

To add new storage path it is necessary to use the command db2 alter database addstorage on <path_list>




SAP AG 2005

Insights


Reorganization

Space Managemant


Tablespace Layout

Tablespace Layout




SAP AG 2005

Insights


Reorganization

Space Management


Tablespace Layout





SAP AG 2004

Buffered and Unbuffered Data Types

D B


F i l e L e v e l

db2agentdb2agent

DB2 containers


DB2 Memory

Buffer pools

Buffered Access

Unbuffered dccess

Buffered data types: char, varchar, int, ...

Unbuffered data types: long varchar, LOB,...

The database server stores all data, except for LOB and LONG data, in its buffer pools for caching

purposes.

The unbuffered data is not stored in the buffer pool(s) for performance and size reasons. LOB fields can be several MB in size, which could easily displace important data in the buffer pool(s).

SAP has made a conscious decision to store large amounts of data, that is buffered by other means, in

LONG or LOB fields. Still some tables, that contain unbuffered fields, are not buffered. APQD and

VBDATA are examples of such tables.




SAP AG 2004

How to Prevent Unbuffered Data Access

APQD long varchar APQD varchar

Data page

Data long

field page

16KB page

size

4KB page

size

APQD record does

NOT fit into page size FIT into page size

It is possible to move a table with LONG fields to tablespaces with larger page sizes.

When doing this, the SAP data dictionary will query the underlying DB2 tablespaces for page size

infomation. If possible, the table in the new tablespace is created without a LONG field. The data is thencopied from old to new tablespace and the tables are renamed.

The size of the long field has to be considered when choosing the page size of the new tablespace: The

SAP DB2 Database Interface (DBSL) selects LONG fields on 4 KB pages whenever a field is longer

than 3800 bytes. For 8 KB pages, the maximum field size is 7800 bytes, and so forth.




SAP AG 2004

Moving Tables Between Tablespaces

Tablespace ATablespace B

Table A

Table B

Table C

Table B

Page Size 4 KB Page Size 4KB, 8KB, 16KB, 32KB

Not Useable

Caution:

Short row lengths

and large page

sizes may lead to

unused space in

pages (255 slots)!

DB6CONV

When the total allocated space for a tablespace reaches the limit of 64 GB (with 4 KB pages) and

REORG does not yield additional space, you must move tables to other, probably new, tablespaces to

allow for further growth of the tables. This limit varies with the page size and can be 64 GB, 128 GB,

256 GB, 512 GB).

To move a table from one tablespace to another, use SAP utilities such as DB6CONV. This allows for

larger table sizes when copied to a tablespace with larger page size.

A table is copied based on primary index (almost like REORG).

For details, refer to SAP Note 362325.

For SAP Release 3.1I, DB6CONV is not supported. SAP provides a manual description in these cases.




SAP AG 2004

Data

Data

DB2 data page

DataOVL Data

Overflow Records: F1

If record cannot be extended in place, index

RID information is not changed.

An overflow record containing the new RID is

written.

Overflow records occur with tables defined using one or more VARCHAR data fields. If an in-place

extension of these fields is not possible, DB2 places an overflow record in the original location of the

row. It will contain the RID of the new location holding the extended record.

Overflow accesses involve two read operations. They may be in-page, in-extent, but in the worst case,

they may be out-of extent. This means the data is not in proximity of the original location and another

page must be read.

Overflow accesses might reduce I/O performance, however, only careful analysis of the situation will

show you if a table has to be reorganized. Two examples:

Table CDHDR contains 67 overflow records and the size is 4 MB. Even with heavy use of all of the

rows of the table, this table may easily fit into the buffer pool. There will not be any I/O impact.

Table ACCTID contains 10% overflow records and the size is 5 GB. It will not fit into the buffer pool

and large scans are made to the table. With heavy read activity on the table, the table should be

reorganized twice a year.




SAP AG 2004

Redirected restore

Extent 0

Tablespace

Reduce TBS size

HWM

No

space

Reducing Tablespaces with Redirected Restore

DB

When reducing the tablespace size using redirected restore, you must ensure that the total size of the

tablespace is not reduced below the high watermark of the tablespace.

If this is not the case, DB2 RESTORE will try to write an extent that does not exist any more on disk.




SAP AG 2004

Checking for HWM with DB2DART

DART (V8.1.0) Report:

Wed Jan 29 20:31:09 2003 GMT+1

Database Name: L62

Report name: L62.RPT

Database Subdirectory: /db2/L62/db2l62/NODE0000/SQL00001

Operational Mode: Database Inspection Only (INSPECT)

Action option: DHWM

Tablespace-ID: 27

...

Highwater Mark: 1472 pages, 92 extents (extents #0 - 91)

...

Dump highwater mark processing - phase start.

Number of free extents below highwater mark: 0

Number of used extents below highwater mark: 92

Object holding highwater mark:Object ID: 6

Type: Index Extent

Dump highwater mark processing - phase end.

DB2DART /DHWM provides valuable information about the

HWM state:

When calling DB2DART /DHWM, processing will focus on details that are helpful for reduction of the

high watermark (HWM). In this example, the Object ID 6 holds the HWM. By analyzing the details of

the DB2DART output, you can assess the possibility for lowering the HWM.




SAP AG 2005

Insights


Reorganization

Space Management


Tablespace Layout

Reorganization




SAP AG 2004

Table Reorganization with Temporary Tablespace

Temporary tablespaceRegular tablespace

TableTableCopy

Temporary tablespaceRegular tablespace

Copy

Table is copied to

the temporary

tablespace.

Table is copied to the

original tablespace

and object map pointer

is changed.

11

22

Using temporary tablespace, DB2 REORG copies the table contents to a temporary table and then copies

the table back to the original location.

This method allows for REORG for large tables. This is because the temporary tablespace can beincreased temporarily and then reduced in size again.

You will notice that REORG will consume two or even three times the space of the table object during

processing. The actual space consumption depends on the index definitions and sizes.




SAP AG 2004

Table Reorganization in Tablespace

Regular Tablespace

TableTableCopy

Regular Tablespace

Table extents are copied

to free extents (lowest

extents used first).11

You can also reorganize tables with the traditional REORG command without using a temporary

tablespace.

In this case, the table will be exclusively locked by DB2 and copied to a new location in the tablespace.




SAP AG 2005

Do the exercises

?


Unit Actions




Exercises

Unit: Storage Management


• Monitor and manage the space consumption of DB2 tablespaces

• Monitor and decide on the necessity to reorganize tables

• Implement appropriate infrastructure for tablespaces

• Describe DB2’s internal storage allocation mechanisms

Preparation:

To access your training system and use your training database <Txx> during thefollowing exercises, please start your Web browser. Use the server name and port





1-1 Monitoring the space consumption of tablespaces.

1-1-1 Check if there are tablespaces that need to be extended in the SAP trainingsystem DEV.

1-1-2 Which information, beside the current allocation, should be taken into account?

1-1-3 How could you perform the extension of those tablespaces?

1-1-4 Should PSAPTEMP also be extended? Explain your answer.

1-2 Extending tablespace PSAPBTABD in your training database <Txx>.

1-2-1 Add a second container (size 256 pages) to tablespace PSAPBTABD using theDB2 CLP.

1-2-2 How can you validate the above action?

1-2-3 Now extend tablespace PSAPBTABD by a total of 128 pages without adding a

new container.

1-2-4 Explain the advantages/disadvantages of the different options used to extend the

tablespace.




Solutions

Unit: Storage Management

1-1 Monitoring the space consumption of tablespaces.

1-1-1 In transaction ST04, choose Space -> Tablespaces, and sort the table by column

Percent Used . Look for tablespaces where Percent Used > 90.

1-1-2 In transaction ST04, choose Space -> History -> Database and Tablespaces.

Set Statistics = Week and Object Selection = Tablespaces. Then double-click onthe critical tablespaces and check the growth of the last weeks. If the tablespace

size was rather static, it might not be necessary to increase the size.

1-1-3 To extend tablespaces, go to transaction ST04, choose Space -> Tablespaces,

select the appropriate tablespace, and choose Change. Alternatively, use the

DB2 CLP to execute the ALTER TABLESPACE command.1-1-4 In 1-1-1, you have seen that Percent Used is 100 for PSAPTEMP. But because

PSAPTEMP is a SMS tablespace, that does not mean that it needs to be

extended. Tablespaces of this type allocate space dynamically when needed.

1-2 Extending tablespace PSAPBTABD in your training database <Txx>.

1-2-1 Use the following command:

db2 “alter tablespace psapbtabd add (file

‘/db2/<Txx>/sapdata3/PSAPBTABD.container002’ 256)”


db2 “list tablespaces show detail”

The number of containers of tablespace PSAPBTABD is now 2 and the totalnumber of pages is 512.


db2 “alter tablespace PSAPBTABD extend (all 64)”

1-2-4 When adding a new container, the rebalancing process is started and theadditional space will be available when the rebalancer has finished. If the

second option is used, extending all containers by the same number of pages, norebalancing takes place.




1-3 REORGCHK results.

1-3-1 In transaction ST04, choose Space -> Tables and Indexes. In the selection

dialog box, use flag Display flagged tables only.

1-3-2 Overflow records occur if tables with VARCHAR data fields are updated and

an in-place extension of these fields is not possible. An overflow record is then placed in the original location of the row, which contains the RID of the new

location holding the extended record. Overflow records might have a negative

impact, because they involve two read operations and, in the worst case, theymay be out-of extent.

1-4 Table reorganization.

1-4-1 You should consider using INPLACE reorganizations, because they are online.

1-4-2 INPLACE reorganization can only be performed for tables with Type 2

indexes.




SAP AG 2005

Tools for DB2 Database Servers

Contents:

SAP Standalone Executable Programs for DB2 Universal

Database

DB2 Standalone Executable Programs

Objectives:


Use the SAP and DB2 tools in a server environment

Decide on the proper use of the tools in daily database

operation

Use special tools as need arises




SAP AG 2005

Things to Know About Tools

SAP software with DB2 UDB is shipped with tools from

SAP – These tools are developed by SAP to help with the use

of DB2 Universal Database servers

IBM – These tools are part of the DB2 Universal Databasesoftware package

These tools complement each other and are subject to

decisions made by SAP AG or IBM Corporation

New tools may become available while others disappear

SAP will provide support for both groups of tools through the

SAP Service Marketplace

The tools that are shipped with SAP software on the IBM DB2 Universal Database are grouped into two

sets:

SAP tools for DB2 such as brachive, brrestore, dmdb6srp, and dscdb6up DB2 tools such as db2adutl and db2relocatedb




SAP AG 2005

Tools Environment

Database Layout Tools

Security and Safety Tools

SAP Tools for ST04

Monitoring Tools

8 Tools for DB2 Databases

Log File Management Tools





SAP AG 2004

brarchive: the Log File Archive Tool (1)

>>--- brarchive ---------------------------------------------------> >------ -h(elp) --------------------------------------------------->

+-- -V -----------------------------------------------------¦

+-- -do ----------------------------------------------------¦

+-- -dt volume ---------------------------------------------¦

¦ +- show -+ ¦

+-- -i(nitialize) --+---------+----¦ tape options +---------¦

¦ +- set -¦ ¦

¦ +- force -+ ¦

¦ ¦

+-- -s ----¦ device options +-----------------------------¦

+-- -sd --¦ +- -n(umber) number -+ ¦

+-- -ss --¦ ¦

+-- -ssd --+ ¦

+-- -ds ----------------------------------------------------¦

¦ +- -n(umber) number -+ ¦

¦ ¦

+-- -q(uery)---- backups -----------------------------------¦

¦ +- check --¦ tape options +--+ ¦¦ +--- + --------+ ¦

+---- -GAL ------- add ------------------------------------+

+- -DMP -+ +- remove -¦

+- revert -+ Legacy

Stores log files on tape or storage management system

Using the Log File Archive tool, brarchive, log files can be archived to tape or storage management

system with the following options:

-i – Initializes using one of the following options: show – Displays the volume label, which will be created (default action)

set – Re-initializes the volume label on previously used tapes. This allows for recycling of tapes that

have been released for further use. The log files on these tapes are not necessary for the defined

backup cycle.

force – Initializes a new tape or re-initialize a tape, which contains ACTIVE log files. Use the FORCE

action with caution because you could overwrite a log file that might be necessary for a recovery

activity. Only use this option if you have a newer backup or if the log files to be deleted have been

backed up on other tapes or media as well.

-s – Stores log files (without deleting)

-sd – Stores and deletes log files

-ss – Stores log files on two tape units in parallel. Log files are not deleted.

-ssd - Stores log files on two tape units in parallel and deletes the stored log files from disk

-ds - Deletes those log files that have been stored on disk

The -q (query) option provides information about all known backups in the Admin Database (backups)or information about the tape which is in use (check).

Because brarchive is normally run via CCMS, it does not provide output to the command line unless it is

called with the -out option (used in subsequent slide, "brarchive, the Log File Archive Tool (3)").




SAP AG 2004

brarchive: the Log File Archive Tool (3)

+- -node NODE0000 -+

>--+------------------+-------------------------------------------------->

+- -node NODExxxx -¦ +- -sid DBSID -+ +- -vndlib lib -+

+- -node ALL -+

>------------------------------------------------------------------------><

+- -out -+ +-- -DEB -----------------+

+-- level --+

device options:

+-- -d(evice) ----- adsm ------------------------------------------------¦

¦ +- tsm -+ +- Mgmt Class -----------------¦ ¦

¦ + Mgmt Class 1 + Mgmt Class 2 + ¦

+--- tape -----¦ tape options +------------------------¦

+--- script -------------------------------------------+

tape options:

+-- -v(olume) name -------------- -t device -----------------------------¦

+- name2 -+ +- device2 -+ +- -one -+

Legacy

Options for brarchive

Additional options for brarchive include the following:

Node specification in partitioned databases (NODE0000 is the default node and is used when -node is

omitted) Vendor library name and path

Debug level information (it is good practice to use init<dbsid>.db6 to set the debug level instead of

using -DEB)

Script invocation of brarchive/brrestore backend solutions that are site-specific (please refer to SAP

Note 533979) – use 'script' in statement




SAP AG 2004

sddb6ins: the Installation Program for Admin Tools

>>--sddb6ins---[-h]----------------------------------------->

+ -V --------------------------------------¦

+ -i -------------------[<target release>]-¦+ -u --<start release>--[<target release>]-¦

+ -r --[adminDB backup image]--------------¦

+ -checkonly ------------[<target release>]-+

>--[-s <SAPSID>]--[-NOADMDB]--[-DEB [<level>]]------------->

>--[-db2dbnamepwd <pwd>]---------[-sidadmpwd <pwd>]-------->

>--[-db2dbnamedomain <domain>]---[-sidadmdomain <domain>]-><

Legacy

Installation tool for SAP DB2 Admin Tools

The installation program for SAP DB2 Admin tools, sddb6ins, is used to install or upgrade the Admin

Tools on a database server.

If you want to install the tool on UNIX servers, you must use the following trick: log on as db2<dbsid>user. Then enter su to root, without the hyphen. After this, you will have the rights of root, but including

the environment of the db2<dbsid> user (besides other features).

Using the parameters gives you the following options:

-i – Use this parameter to install the Admin Tools by specifying the <target release>.

-u – Use this parameter to upgrade from <start release> to <target release>.

-checkonly – Use this parameter to verify the correct installation of the Admin Tools.

-r – Use this parameter to install a backup copy of the Admin DB that was retrieved from an archive

using brrestore.

Note: For Windows-based installation, you must provide the password for the DB2 databaseadministrative user with the -db2dbnamepwd option.




SAP AG 2005

sddb6mir, the Admin DB Creation and Mirror Tool

>> --- sddb6mir --- -c ADM<DBSID>-------------------><| -m <dbsid> ------------ || -r ADM<DBSID> -p <path> |

Do not use the -r option ! Use sddb6ins -r instead !

SAP Database

Admin DB

Legacy

For SAP releases earlier than Basis 6.10:

This tool is used to transfer administrative data to the SAP

database (must be scheduled as background job)

The Admin DB Creation and Mirror tool, sddb6mir, available in SAP releases earlier than Basis 6.10, is

used to do the following depending on the option:

-c – Create an empty Admin Database -m – Mirror the Admin Database into the SAP database

-r – Restore the Admin Database from the tables that have been used in the SAP database. Here you

need the -p option for the temporary files.

Note: For SAP Basis releases 6.10 and later, SAP does not use sddb6mir because data will be retrieved

from the Admin DB and a backup of the Admin DB is made whenever brarchive is used.

For details of sddb6ins functionality that replaces the -r option, refer to SAP Note 533979.




SAP AG 2005

Tools Environment



SAP Tools for ST04

Monitoring Tools







SAP AG 2005

brdb6brt, the Redirected Restore Tool

>> --- brdb6brt -- -bm ---BACKUP ---- -s SourceDB -- > |- RETRIEVE-----------|

|- RETRIEVE_RELOCATE--||- BOTH---------------|

>----------------------------------------------------><|- -p protocolpath-| |- -ip ScriptPath -|

>----------------------------------------------------><|- -nb numBuf -||- -bs bufsiz -||- -es -||- -ol -|

>----------------------------------------------------><|- -ts timestamp -||- -nn nodenum |

>----------------------------------------------------><|- -bpt ---- device ------------|

| ^ |directory -------|||| |--------------------|||- ADSM numsess---------||- VENDOR lib ----------|

|- numsess-|>----------------------------------------------------><|- -tbs -tablespace -||- -user name | |-using pass -|

^------------|

Tool for DB2 Redirected Restore; used for homogeneous system

copy via backup/restore (see also db2relocatedb)

DB2's Redirected Restore tool, brdb6brt, provides mechanisms to redefine most storage properties of a

database through a simple backup/restore cycle. These storage properties include the DB2 container

locations, sizes, number of containers of a tablespace, and database name. One notable exception is the

page size of the tablespaces. The page size is defined during the creation of a tablespace and cannot be

changed later.

Although you can use brdb6brt for backup, this tool is typically used to create a script, which can be

used to restore a backup image.

The tool also supports the creation of an input script for the database cloning tool db2relocatedb and the

database initialization tool db2inidb. For this, use the option RETRIEVE_RELOCATE.




SAP AG 2005

brdb6brt – Check Mode

>> --- brdb6brt -- -bm ---CHECK ---- -ip ScriptName - >

-------------------------------------------------------><|- -nn nodenum | |- -user Username| |- -using Password |

.... sapdata<N>sapdata1 sapdata2

CHECK

F i l e

L e

v e l

Simulation of a restore operation

The check mode of brdb6brt reviews if a restore will be possible with a given restore script.

The output of the check mode run is written to a protocol file in the current working directory.

The check mode checks the filenames, paths, and space in the database server. It also checks if files can be created. It does not check if the backup image is accessible or if the hierarchical storage management

system allows retrieval of the backup image.




SAP AG 2004

db2inidb: the Database Initialization Tool

>>-- db2inidb ---database alias ---- AS ---->

>----- SNAPSHOT --------------------------->

+-- MIRROR --¦

+-- STANDBY --+

>-----------------------------------------><

+- RELOCATE USING -- config file -+

Set the state of (a copy of) the database at restart time to be

one of the following:

An exact copy (snapshot)

A starting point for hot standby A backup source

The database initialization tool db2inidb allows administrators to initialize DB2 databases in several

ways. This is always related with the process of copying the database files under /db2/<DBSID> to

another directory or probably another server.

When copying to another server, you will not need to use the RELOCATE option. This option is

explained in the next slide.

In any case, the copy will be able to be used as a

SNAPSHOT – A clone of the original database, consistent and crash-recovered

MIRROR - A copy of the original database, but in rollforward-pending mode. This database will be

able to consume the log files from the original database to be available as a hot standby database.

STANDBY – A database, not crash-recovered, not consistent, but accepting DB2 BACKUP

connections. This database can be used to create an online backup of the original database.




SAP AG 2004

db2relocatedb: the Database Cloning Tool

>>-- db2relocatedb - -f RelocationFile ----><

Example: Copy database PRD to QAS

# File for use with db2relocatedb

INSTANCE=db2prd,db2qasDB_NAME=PRD,QASDB_PATH=/db2/PRD,/db2/QAS

LOG_DIR=/db2/PRD/log_dir/NODE0000, /db2/QAS/log_dir/NODE0000CONT_PATH=/db2/PRD/sapdata1/PRD#BTABD.container001,/db2/QAS/sapdata1/QAS#BTABD.container001

CONT_PATH=/db2/PRD/sapdata1/PRD#BTABD.container002,/db2/QAS/sapdata1/QAS#BTABD.container002

...

Change database setup information, which is burned into DB2's

binary configuration files

Database path

Location of one or more containers

Database instance and database name

Log path

For databases relocated with this tool, ROLLFORWARD PENDING status can be switched on with

DB2 tool DB2RFPEN

The tool brdb6brt provides options to generate an input file to db2relocatedb.




SAP AG 2005

Tools Environment



SAP Tools for ST04

Monitoring Tools







SAP AG 2005

Security-Related Executables and Files

Database Security Component – DB6 – Update Password

dscdb6up

Database Security Component – DB6 – Configuration

/sapmnt/<SAPSID>/global/dscdb6.conf

DB6 – utility (the kitchen sink for many functions needed by

SAP); called via ST04 or automatically during SAP system

upgrade

db6util

Use the update password tool, dscdb6up, to set the passwords for the SAP Database Connect User and

the <sapsid>adm user on operating system level (using operating system APIs such as passwd()) and in

the password configuration file dscdb6.conf.

The password configuration file dscdb6.conf must exist, otherwise, you should re-create the file with the

SAP utility db6util.

The SAP utility db6util is used mainly during SAP system upgrades. It provides functions for reviewing

free space in tablespaces, setting passwords, and reviewing lock situations.




SAP AG 2005

DB2 will

authenticate user

at the OS level

Data Security: DB Access Passwords

SAP Database Server SAP Application Server

DB2DB6EKEY=XXXXXXX

Read dscdb6.conf

Connect as <user>

using <password> De-crypt using KEY

DB2

Any SAP work process that connects to the database retrieves encrypted passwords from the file

dscdb6.conf. The work processes, which are started by user <sapsid>adm, use this password to connect

to the database as user sap<sapsid> or sapr3, depending on the SAP system configuration and the SAP

release. This user is the connect user.

If the password at the operating system level is changed, the dscdb6.conf file will not be synchronized

and the system will not start. To check for this error, execute command R3trans -x from the operating

system level as user <sapsid>adm (which will perform only a connect) or R3trans –d (which will

connect and retrieve some data).

To change the password of the connect user and user <sapsid>adm, you can perform the following:

For Unix: As user <sapsid>adm, use

dscdb6up <sapsid>adm <password>

dscdb6up <connect_user> <password>

For Windows: As user <sapsid>adm, use

dscdb6up.exe <sapsid>adm <password>

dscdb6up.exe <connect_user> <password>

For both: Alternatively you can use the SAP DB2 Admin Password management extension in the DB2

Control Center.




SAP AG 2005

db2ckbkp, the Backup Verification Tool

>> --- db2ckbkp ---- -a ------- <filename> ---><^ | -c | | ^--- , ------|

| | -d | || | -h | || | -H | || | -l | || | -n | || | -o | ||-----, ---|

You should verify your backups!

Used to test the integrity of DB2 backups

You can test the integrity of your DB2 backups with the DB2 utility for backup verification, db2ckbkp.

When using multiple backup images, such as multiple tapes in multiple tape drives or multiple backup

files, you must make sure that the first device or file that was written to by backup is also specified firstas the filename argument. This is because the first tape or file contains the media header.

The following arguments have the following effects:

-a – Shows all available information

-c – Shows the checksums

-d – Displays DMS tablespace data headers

-h – Displays the media header information, including the data which is expected by the DB2

RESTORE utility

-H – Displays the media header only (4 K), but does not read the backup for verification

-l – Displays log file header on the backup




SAP AG 2005

Tools Environment



SAP Tools for ST04

Monitoring Tools



Monitoring Tools




SAP AG 2005

db2pd – DB2 Problem Determination

DB2-Instance db2<dbsid1>

Database <DBSID1>

Agents, dbm cfg, osinfo....

applications, db cfg, Bufferpools,

Logs, locks, tablespaces,....

This utility shipped with the DB2 engine starting with the release DB2 8.2.

Executed from the command line with optional interactive mode.

Runs very quickly as it is not needing any locks or latches. This executable runs outside of the enginesresources (for example works on a hung DB2 engine).

Can be used by customers to monitor and troubleshoot.

Gives the user a closer view into the DB2 engine.

To use this tool it is necessary to have SYSADM authority and on Unix/Linux you must be instance

owner.




SAP AG 2005

Tools for DB2 Database Servers: Unit Summary


Use the SAP and DB2 tools in a server environment

Decide on the proper use of the tools in

daily database operation

Use special tools as need arises




SAP AG 2005

Do the exercises

?


Unit Actions




Exercises

Unit: Tools for DB2 Database Servers


• Move a tablespace container to another location without performing aredirected restore


following exercises, please start your Web browser. Use the server name and port





1-1 Please move (relocate) container001 of tablespace PSAPPOOLD of your training

database <Txx> into another sapdata directory. Do not use a redirected restore!

Note: Before you begin, please set the right environment with:

Export DB2DBDFT=<Txx>




Solutions

Unit: Tools for DB2 Database Servers

1-1 Please use the following procedure:

Make sure, that you are in directory /db2/db2<txx>!

Use brdb6brt to generate an appropriate script: ./brdb6brt –s <Txx> -bm

RETRIEVE_RELOCATE

Stop your DB2 training instance <db2txx> with the following command: db2stop

Change the following entry in the relocation script <Txx>_NODE0000.scr

Source:

CONT_PATH= /db2/<Txx>/sapdata6/PSAPPOOLD.container001,/db2/<Txx>/sapdata6/PSAPPOOLD.container001

Target:

CONT_PATH= /db2/<Txx>/sapdata6/PSAPPOOLD.container001,

/db2/<Txx>/sapdata3/PSAPPOOLD.container001

Move the container PSAPPOOLD.container001 to /db2/<Txx>/sapdata3:

mv /db2/<Txx>/sapdata6/PSAPPOOLD.container001 /db2/<Txx>/sapdata3

Now start your DB2 training instance <db2txx> with the following command:

db2start

Finally use the following command to correct the container path into the database

configuration: db2relocatedb -f <Txx>_NODE0000.scr




SAP AG 2005

DB2 Performance Monitoring and Tuning

28

Tools for DB2

Database Servers

9



5 Managing Backups 11 Problem Analysis



3 PartitioningDB2 PerformanceMonitoring and Tuning





SAP AG 2004

DB2 Performance Monitoring and Tuning

Contents:

Database Memory Configuration

Application Design

DB2 Universal Database Cost-Based Optimizer

Database Layout

Objectives:


Monitor key performance indicators for DB2 UniversalDatabase

Recognize poorly qualified and unnecessary SQL statements Use the DB2 Dynamic SQL cache analysis

Identify I/O contention in the database




SAP AG 2004

Memory

Statistics

Indexes

Cost-Based Optimizer

Layout

DB2 Performance Monitoring and Tuning9

Tips

Statement Tuning

Memory




SAP AG 2004

Performance problems

due to

Performance problemsPerformance problems

due todue to

Memory configurationMemory configurationMemory configurationApplication designApplication designApplication design

Database layoutDatabase layoutDatabase layout

Normal tuning path for SAP systems

Cost-based optimizer CostCost--basedbased optimizer optimizer

Database-Related Performance Issues

Database performance problems normally result from poor configuration of the database memory

configuration, application design problems, missing or outdated statistics for the Cost-Based Optimizer,

or incorrect database layout.

This unit focuses on the steps you should take to identify and correct performance problems within your

SAP system. For normal tuning, consider the following areas:

• Check the database configuration.

• Use the SAP database tools to verify the efficiency of the application design.

• Check that the update statistics strategy is sufficient.

• Verify the database layout.




SAP AG 2005

DB2 Memory Overview (1)

Monitor Heap(mon_heap_sz)

Audit Buffer (audit_buf_sz)

Buffer pool(s)

Extended Memory Cache

Lock List(locklist)

Package Cache(pckcachesz)

Application Control Heap(app_ctl_heap_sz)

Database Manager SharedMemory (including FCM)

Database Global Memory

Application Global Memory

Backup Buffer (backbufsz)

Restore Buffer (restbufsz)

Utility Heap(util_heap_sz)

Database Heap(dbheap)

Log Buffer (logbufsz)

Catalog Cache(catalogcache_sz)

DB2 allocates memory in the following steps:

Database Manager Shared Memory is allocated when the database manager is started using the

db2start command, and remains allocated until the database manager is stopped using db2stop. Database Global Memory (also known as Database Shared Memory) is allocated for each database

when the database is activated using the ACTIVATE DATABASE command or the first application

connects to the database. The Database Global Memory remains allocated until the database is

deactivated using the DEACTIVATE DATABASE command or the last application disconnects from

the database. This memory is used across all applications that might connect to the database.

Application Global Memory (also known as Application Shared Memory) is allocated when an

application connects to the database. This allocation occurs only in a partitioned database environment

or if parameter INTRA_PARALLEL is enabled. This memory is used by DB2 agents to share data

and coordinate activities.

Most memory heaps are only allocated as required. The following heaps are permanently attached:

Buffer pool(s), lock list, package cache, database heap, utility heap

As a rule of thumb, use this formula to determine the memory that is allocated as a minimum:

Minimum DB2 memory = Buffer pool(s) + Package cache (PCKCACHESZ 4 KB) + Database heap

(DPHEAP 4 KB) + Lock list (LOCKLIST 4 KB) + Utility heap (UTIL_HEAP_SZ 4 KB) + (Number

of DB2 agents * 10 MB).




SAP AG 2005

DB2 Memory Overview (2)

DRDA Heap(drda_heap_sz)

Statistics Heap(stat_heap_sz)

UDF Memory

Sort Heap for PrivateSort

(sortheap)

Application Heap(applheapsz)

Statement Heap(stmtheap)

Agent Stack(agent_stack_sz)

Client I/O Block(rqrioblk)

Agent Private Memory

Query Heap(query_heap_sz)

For RemoteClients

Application Support Layer Heap (aslheapsz)

Client I/O Block (rqrioblk)

Agent/Local Application Shared Memory

For Local

Clients

Agent Private Memory is allocated for an agent when that agent is assigned to work for a particular

application. The Agent Private Memory contains memory areas, such as sort heaps and application

heaps, which will be used only by this specific agent. This memory remains allocated even after the DB2

agent completes tasks for the application and goes into idle state.

The Agent/Local Application Shared Memory is used for SQL request and response communications

between an agent and its client application.




SAP AG 2005

SYSCATSPACE

Database

RDBMSDatabase global

memory

Buffer pool(s)Buffer pool(s)

...

SELECT *FROM MARAWHERE ... Logical

reads

Physical reads

Catalog cache

Database heap

Memoryarea

SAP workprocess

DB2agents

Memory Configuration (1)

Package cache

PSAPBTABI PSAPBTABD

The buffer pool functions as a cache for the database

The package cache caches parsed SQL statements

The catalog cache caches data dictionary information from the database

Buffer pools provide the storage for data and index pages. Buffer pools function as an optimized cache

for the database to improve database system performance. Because this cache optimizes its strategy for

database use, it is better to use the buffer pools than to use a large file system cache. The goal is to

minimize disk accesses (physical reads) and to maximize buffer pool access (logical reads). The size of

the buffer pool(s) is controlled by either the DB2 parameter BUFFPAGE or with the CREATE

BUFFERPOOL or ALTER BUFFERPOOL statement. There is at least one buffer pool per database.

Depending on your performance analysis, you can also define buffer pools at tablespace level.

After a dynamic SQL statement is compiled and used by a DB2 agent, its access plan (package) is

cached in the package cache. Other DB2 agents executing the same statement again use the version

already compiled in the package cache, thus avoiding the cost of compilation. This process is also known

as preparation.

The catalog cache stores binary and compressed descriptors for tables, views, and aliases. Each time a

dynamic SQL statement is compiled, the system reads queries from system tables to gather information

about all tables, views, and aliases. Descriptors are held in the catalog cache to avoid reading from disk.




SAP AG 2005

Buffer Pool Considerations

Buffer poolsummary

Buffer poolsnapshot

>96

>95

>98

Transaction ST04 provides important monitoring information.

To get an overview of the overall buffer pool usage, go to transaction ST04, choose Performance →

Database → section Buffer Pool.

Buffer Pools Number: Number of buffer pools configured in this system.

Buffer Pools Total Size: The total size of all configured buffer pools in KB. If more than one buffer

pool is used, choose Performance→ Buffer Pools to get the size and buffer quality for every single

buffer pool.

Overall buffer quality: This represents the ratio of physical reads to logical reads of all buffer pools.

Data hit ratio: In addition to overall buffer quality, you can use the data hit ratio to monitor the

database: (data logical reads - data physical reads) / (data logical reads) * 100%

Index hit ratio: In addition to overall buffer quality, you can use the index hit ratio to monitor the

database: (index logical reads - index physical reads) / (index logical reads) * 100%.

Data or Index logical reads: The total number of read requests for data or index pages that went

through the buffer pool.

Data or Index physical reads: The total number of read requests that required I/O to place data or index

pages in the buffer pool.

Data synchronous reads or writes: Read or write requests performed by db2agents.




SAP AG 2005

Database

Database globalmemory

RDBMS

Lock list

SAP workprocess

DB2agents

Memoryarea

Sort heap

Agent privatememory

Memory Configuration (2)

Buffer pool(s)Buffer pool(s)

PSAPTEMP PSAPBTABD ...

Lock list contains the locks held by all applications

Sort heap is used for sorting

Lock list: Locking is the mechanism used by the database manager to control concurrent access to data.

Both rows and tables can be locked. There is one lock list per database, which contains the locks held by

all applications. Its maximum size is 256 MB (for 32-bit systems) and 2 GB (for 64-bit systems).

Sort heap: The sort heap is used for sorting. Sorting is required for a query, for example, if the order by

clause is used in the SQL statement. If the configuration of the sort heap is not adequate for a query, a

temporary table in PSAPTEMP is used to handle the sort request.




SAP AG 2005

Sorting Area Considerations

Database Snapshot: Sort information

< 1% of Total Sorts

To monitor the sorting behaviour of your database, go to transaction ST04, choose Performance→

Database → section Sorts.

Total sort heap size: The maximum size for the sort heap used to sort data indexes or data pages.

Sort heap allocated: The total number of allocated pages of sort heap space for all sorts at the level

chosen and at the time the snapshot was taken.

Sort overflows: The total number of sorts ran out of sort heap and may have required disk space for

temporary storage.

Monitor the relation between Total sorts and Sort overflows to find out if the sort heap is too small for

your system. Sort overflows should not exceed 1% of total sorts.

Use the Database Manager Snapshot to monitor the piped sorts and the post threshold sorts. To do this,

use the command db2 get snapshot for database manager (in transaction ST04, choose Configuration

→ CLP Commands → Function Database Manager Snapshot ). Monitor the following entries:

Post threshold sorts: Number of sorts that have required heaps after the sort heap threshold has been

reached.

Piped sorts requested: Number of piped sorts that have been requested.

Piped sorts accepted: Number of piped sorts that have been accepted.




SAP AG 2005

Buffer pools(s)Buffer pools(s)

SAP workprocess

DB2 UDB

DB2agent

Agent privatememory

Sort heapDB2 instance

Agent privatememory

Sort heap

Agent privatememory

Sort heap

Agent privatememory

Sort heap

Agent privatememory

Sort heap

Sort heap threshold

Understanding Overflow Sorts

Database

SELECT *FROM MARAWHERE MANDT=001

AND MTART LIKE ....ORDER BY STOFF

PSAPTEMP

If the information being sorted is larger than the sort heap that is allocated within the agent private

memory, a sort overflow occurs and a temporary table is created. With small buffer pools, this table

might even spill over to disk storage of the PSAPTEMP tablespace.

If the result of a sort cannot return directly, a temporary table is used to store the final sorted data (non

piped sort).

The maximum size of the sort heap is configured in the DB2 parameter SORTHEAP.

The total amount of memory for sorting available in the DB2 instance is configured in the DB2 instance

parameter SHEAPTHRES (sort heap threshold). This parameter represents a soft limit; that is, if this

parameter is reached, new sort heap requests will receive only small amounts of memory.




SAP AG 2005

Lock List Considerations

= 0

Database Snapshot: Locking information

To monitor the current lock situation, call transaction ST04, choose Performance→ Database → section

Locks and Deadlocks.

Lock list size: This indicates the amount of storage allocated to the lock list. One lock list exists on eachdatabase and contains the locks held by all applications concurrently connected to that database.

Lock list in use: The total amount of lock list memory that is in use.

Lock escalations: The number of times that locks have been escalated from multiple row locks to a table

lock.

Excl. lock escalation: The number of times that locks have been escalated from multiple exclusive row

locks to an exclusive table lock.

DB CFG parameter MAXLOCKS defines a percentage of the lock list held by an application that must

be filled before the database manager performs escalation. In order to prevent the whole lock list being

filled by one single application, MAXLOCKS should be set to a value lower than 100 (e.g. 90). Lock

escalation also occurs if the lock list runs out of space.

Deadlocks detected: The total number of deadlocks that have occurred.

To monitor the previous lock situations, go to transaction ST04 and choose Performance→ History →

Database. Check the Deadlocks, Lock escalations and X Lock Escalations rows.

Note: It is extremely important that your SAP system runs without lock escalations!




SAP AG 2005

1. Workprocess

2. Workprocess

3. Workprocess

4. Workprocess

AcquiresMARA Lock

A long periodof processing

CommitUpdateMARA

Working...WAITING!UpdateMARA

RequestsMARA lock

AcquiresMARA lock

Commit

Working...WAITING!Update

MARARequests

MARA lockAcquires

MARA lock

WAITING ...UpdateMARA

RequestsMARA lock

lockedby

WP 1 WP 2 WP 3

When a Lock Wait Situation Occurs

MARA

MARA

A lock wait situation occurs when a work process requests a lock on an object that is already locked by

another work process. For the database to maintain transactional consistency, the object is locked by the

process that requests it first.

If a user starts a logical unit of work and updates an important object, for example, the most popular

material number of the company, all other users who want to update the same material must wait until

the first user has committed the changes before they can get the record. The duration of such update

transactions is typically very short.

A user holding a lock occupies an SAP work process. Other users trying to apply the same lock have to

wait and at the same time they occupy their own SAP work process. As the number of lock waits

increases, fewer and fewer SAP user requests can be processed by available SAP work processes. In the

worst case (lock holders and waiters = number of SAP work processes), a small number of users can

cause the entire SAP system to freeze.

With DB2 database servers, several hundred thousands of row locks are transformed into a single table

lock if a lock escalation occurs. During update, row level x-locks are set. After the lock escalation, thedatabase will have a single table x-lock (exclusive lock). The concurrency of the SAP system is heavily

affected.




SAP AG 2005

Identifying a Lock Wait Situation

Lock Snapshot

Graph Layout

Table Layout

Processwaiting for lock

Processholding lock

In this example, the lock wait situation was created manually using the DB2 CLP.

To identify lock wait situations, use the Lock Wait Monitor (from transaction ST04, choose

Performance → Lock Waits or use transaction DB01).

The Lock Wait Monitor provides the following information:

Process IDs of lock holder and waiting processes

SQL statements involved

Lock objects

Lock mode




SAP AG 2004

Reducing Exclusive Lock Waits

Redesign the application to reduce the locking period by:

Increasing the commit frequency in the application

Not allowing a single process to hold a lock for a long period

Locking the object as late as possible

Adjust the job scheduling cycle so that lock situations do notoccur.

Exclusive lock waits usually occur because of one of the following situations:

A user holds the lock too long.

- For example, a user could be processing large amounts of data in the background. Only explicitlycommitting the changes on the updated records would enable update access for other users. The

solution here would be to analyze the application and determine whether more commits can be

safely built into the application.

Many users want the same record in high-volume processing.

- For example, in mass loading of FI documents, users could all want to update the same general

ledger account. Though each individual lock wait may not take long, the sum of all lock waits can

significantly reduce the speed of the mass load. Here, a possible solution would be to sort the data

to mix up the accesses to GL accounts.

Use Type-2 indexes, if the database has been set up on earlier DB2 releases




SAP AG 2004

Memory

Statistics

Indexes


Layout


Tips

Statement Tuning





SAP AG 2004

DB2 Configurations That Affect the DB2 Optimizer

dft_degree

dft_queryopt

avg_appls

sortheap

locklist

maxlockscpuspeed

stmtheap

max_querydegree

comm_bandwidth

DB CFG

Size of

buffer pools

DBM CFG

…

The above configuration parameters or factors affect the access plan chosen by the DB2 optimizer.

When a configuration parameter is changed dynamically, the optimizer might not read the changed

parameter values immediately because of older access plans in the package cache. To reset the packagecache, execute the FLUSH PACKAGE CACHE command.

Other important factors that influence the optimizer decision are the DB2 profile registry variables and,

of course, the statistics.




SAP AG 2004

OPTIMIZEROPTIMIZER

Which is the optimal access path?Which is the optimal access path?

Index AIndex A

Fulltablescan

Fulltablescan

Index BIndex B

TableMARA

SELECT * FROM MARAWHERE mandt = "100"

AND matnr = "1000"AND bismt = "0001"

$$

$$$$

$$$$$$

Possible access pathsPossible access paths

CostsCosts

DB2 UDB Cost-Based Optimizer

MARA

During preparation, the DB2 Cost-Based Optimizer tries to determine the most effective strategy for

retrieving or manipulating database data. The access strategy used depends on many factors such as the

following:

Queried table data content (or tables, for a VIEW or JOIN)

Fields specified in the WHERE clause of the SQL statement

Indexes defined for the tables queried

Database statistics

DB2 configuration, etc.

The Cost-Based Optimizer generates many alternative execution plans, estimates the execution costs of

each alternative plan using, among other information, the statistics for tables, indexes and columns, and

chooses the plan with the smallest execution cost. For this, the optimizer requires appropriate statistical

information about the tables and indexes of the database.

Statistical information for a table or index is stored in the DB2 data dictionary. To collect the statistical

information, the DB2 command runstats is executed in a special SAP program.

Table and index sizes, and value distributions can change. If the current number of rows of a table differs

greatly from the values determined by the last runstats run, the optimizer could choose an ineffective

strategy and the database access time becomes longer.




SAP AG 2005

SQL Query Optimization (1)

Index A

SELECT * FROM MARAWHERE mandt = ‘001’AND matnr = ‘1000’AND bismt = ‘0001’

Access pathAccess path

Execution planExecution plan

Select

Fetch

IXScan

SQL statement textSQL statement text

Query Graph ModelQuery Graph Model

The access path for a statement results in an execution plan. This execution plan can be displayed in a

readable format.

Once an execution plan is created, DB2 caches it to be able to reuse it and to save the create time for anexecution plan (also known as prepare time).

DB2’s Cost-Based Optimizer estimates the most efficient way to retrieve data by doing the following:

Considering data volume and distribution, which are subject to continuous changes

Finding the right balance between CPU-based and I/O-based operations

Searching through the possible execution plans to find the least expensive one

DB2’s Cost-Based Optimizer also considers how much time it takes to get the plan.




SAP AG 2005

SQL Query Optimization (2)

Query Graph ModelQuery Graph Model

SELECT * FROM MARAWHERE mandt = ‘001’AND matnr = ‘1000’AND bismt = ‘0001’

SQL statement textSQL statement text

Execution planExecution plan

Select

Fetch

IXScan

Index A

Access pathAccess path

OptimizeAccess Plan

GenerateExecutable Code

Rewrite Query

Check Semantics

Parse Query

The Query Graph Model is an internal, in-memory database that represents the query as it is processed

in the steps described below:

Parse Query: The Query text is analyzed to validate the syntax. When parsing is completed, an internalrepresentation of the query is created and stored in the Query Graph Model.

Check Semantics: Possible inconsistencies among parts of the statement are detected (for example, the

data type of columns specified for scalar functions are checked).

Rewrite Query: The global semantics stored in the Query Graph Model are used to transform the query

into a form that can be optimized more easily and the result is stored in the query graph model.

Optimize Access Plan: Many alternative execution plans are generated. To estimate the execution

costs of each alternative plan, the optimizer uses the statistics for tables, indexes, columns, functions,

the values of some configuration parameters, and so forth. Then the plan with the smallest estimated

execution cost is chosen. All relevant information is stored in the DB2 Explain Tables.

Generate Executable Code: The access plan and the Query Graph Model are used to create anexecutable access plan, or section, for the query.




SAP AG 2004

Memory

Statistics

Indexes


Layout


Tips

Statement Tuning

Statement Tuning




SAP AG 2004

“Expensive” SQL Statements

F i l e L e v e l

DB2 InstanceConfiguration

DB2 DatabaseConfiguration


db2gdsdb2gds

db2wdogdb2wdog

db2syscdb2sysc

db2ipccmdb2ipccm

db2tcpcmdb2tcpcm

Buffer Pool(s)

Package Cache

db2loggwdb2loggw

db2pfchr db2pfchr

db2pclnr db2pclnr

db2pfchr db2pfchr


db2loggr db2loggr

db2fmtlgdb2fmtlg

db2dlockdb2dlock

db2pfchr db2pfchr

D B 2 I n s t a n c e a n d

D a t a b a s e L a y e r

Database Heap

Utility Heap


…

…

…

WorkWork processprocess WorkWork processprocess …

Memory areas Processes/Threads

Physical I/Oload

CPUConsumption

MemoryConsumption

To process an SQL statement, the database needs different kinds of resources:

CPU resources: all processes (or threads) involved in the execution of the SQL statement, especially

the DB2 agents, consume CPU time Physical memory: in the DB2 agent private memory certain memory areas like statement heap, query

heap, sort heap, etc. are allocated and pages in the buffer pool(s) are used

Physical disk storage (storage subsystem / disks): all data necessary to process the statement that is not

already available in the buffer pool(s) need to be retrieved from storage

The amount of resources needed to process a statement depend on

The size and number of the objects (tables, indexes) involved

The complexity of the statement

The access chosen by the optimizer (= the way how the data is retrieved and evaluated)

…

We call statements that consume a high amount of database resources “expensive” statements. Normally

this also means that it takes a long time to execute this kind of statements. Since DB2 only provides very

limited information on the resource consumption of SQL statements, the execution time is used to

estimate the resource consumption.




SAP AG 2004

SQL Cache in ST04

In ST04, choose Performance SQL Cache.

Date of last SQL cacheoutput stored in SAP system

Get new SQL cacheoutput

Display SQL cacheoutput stored in database

Make sure that the system isalready running for a while

before the analysis isstarted!

The SQL Cache Snapshot in transaction ST04 contains information about the runtime of the dynamic

SQL statements in an SAP system.

To perform an analysis, call transaction ST04 and choose Performance→ SQL Cache. Before youdisplay the SQL Cache consider the following options:

Set Selection Criteria: Displays the output of the SQL cache, which is stored in the SAP system. This

selection does not get the latest SQL cache from DB2.

Refresh: Displays the latest SQL cache from DB2 and stores this in an SAP table.

Field Last snapshot: Shows the timestamp of the SQL cache output, which is stored in the SAP system.

It is important to make sure, that an SQL cache analysis is always performed in a „warm“ system. That

means that already a significant number of statements have been executed in order to see representative

content of the SQL cache. A rule of thumb is, that at least 1.000.000 SELECTs have been executed in

the system, before an analysis makes sense. In ST04 ->Performance -> Database, section „Calls“ this

information can be found.




SAP AG 2004

How to Find Expensive Statements (1)

Choose differentapproaches to display

SQL cache

The Selection Criteria screen provides the possibility to analyze the SQL cache from different angles:

Specifying Executions >= 1 without other criterias, is the most general approach. It displays the top

300 statements with regard to the Total execution time (ms) (which we before defined as an estimationfor how expensive a statement is).

The default of at least 100 executions is changed in order to see also those statements that are not

executed very often but nevertheless consume a lot of system resources and are therefore very

expensive.

If additionally Rows Read >= 1000000 is specified, the focus shifts more to those statements that

consume a large amount of buffer pool resources.

It is also possible to limit the display to those statements that access a certain table or view by entering

the object‘s name in the SQL text selection field. It is important to note that this selection criteria is

case sensitive. Generally if a statement comes from ABAP coding, the object name is in upper case!




SAP AG 2004


Start to analyze SQLstatements that have

greater than 5% share onthe total response.

Use “Avg. Execution Time(ms)” to determine if theaverage single statement

execution time is sufficient.

Sort column “TotalExecution Time (ms)”

descending.1 2 3

When using the general approach, the SQL cache content should be sorted by column Total Execution

Time (ms) descending (which implicitly means that it is also sorted by column Total Execution Time

(%)), in order to focus during the analysis on those statements that have a great share on the total

response time.

Use the values for Avg. Execution Time (ms) together with the number of Executions to determine if the

statement has a large share of the Total Execution Time for the following reasons:

The single statement execution is sufficient and the large number of executions are the reason why the

share on Total Execution Time is high.

The single execution is not sufficient (and together with the number of executions), this is the reason

why the share on Total Execution Time is high. And especially when the number of executions is very

high, these statements should be well-tuned.




SAP AG 2004


Sort column“SQL Sorts”descending.

Start to analyze SQL statements thathave high number of SQL sorts.Check if a proper index could avoid sortoperation.

1 2

Additionally, check for statements that have a high number of sorts. Therefore sort the SQL Sorts

column in descending order.

A high number of sorts can be an indication that an appropriate index is not available or not used. For SELECT statements, the value in column Rows written shows the number of rows inserted, updated,

and deleted in temporary tables. A high number here, points to large sorts that should be analyzed in

detail.




SAP AG 2004


Sort column“Rows Read”descending.

1Calculate the average

number of rows per execution.

2Start to analyze SQL statements

that read a high number of rowsto create the result set.

3

When the second approach (specifying Rows Read >= 1000000 in the Selection Criteria screen) is used,

then the focus is on analyzing those statements that consume a lot of resources in the buffer pool (but

probably also other memory, CPU and storage resources). This approach is especially important if the

buffer pool quality is not sufficient.

The counter Rows Read describes the number of touched rows (either directly in buffer pool or first

retrieved from disk and then processed in the buffer pool). It is NOT the number of rows in the result set

of the query!

After sorting the SQL cache contents by column Rows Read , the average number of rows read per

execution should be calculated for the top statements.

Only those statements where the average number of rows read is high, need to be further analyzed. Often

a high value indicates that table scans or index scans with little or no selectivity are occurring.

Unfortunately the SQL cache does not contain information on the size of the result set (this information

is only available in the SQL trace). A comparison between the Rows Read and the size of the result set

would be helpful for the analysis.

The identified top statements with regard to Rows Read will often be also the top statements regarding

the Execution Time. But even if that is not the case for some statements, they should be analyzed

nevertheless, because they consume a lot of database resources which impacts the performance of other

SQL statements.




SAP AG 2004

Reasons for Expensive Statements

Full table scan Index scan

SELECT * FROM MARA WHERE MATERIAL = 10001

MARA MARA_0

Common reasons:

No suitable index available, or suitable index available but not used

If an Explain shows a table scan or an inefficient index access, the most common reasons are

there is no suitable index for the where condition available

a suitable index is available but not used

In some cases, it can also happen, that the Explain shows an efficient index access, but runtime is

nevertheless inappropriate




SAP AG 2004

SQL Cache: Functionality

Explain the statement youare going to analyze.

Show ABAP source of SQLstatement.

Select the statement you aregoing to explain.

First mark the statement that you are going to analyze, then

Use the Explain button to display the chosen execution/access plan and all associated information.

Use the Source button to display the ABAP source code where the SQL statement comes from.

For some statements the Source button does not work. In most cases these are statements with access to

tables like DDNTT, DDNTF, REPOTEXT, etc. which are issued by the SAP kernel. Because these

statements do not come from ABAP coding, it is obviously not possible to display the ABAP source

code.




SAP AG 2005

Identifying Unnecessary SQL Statements

ST05 – SQL Trace

This example shows SQL Trace output when the same SELECT statement was executed repeatedly.

The return code 100 ( SQL0100W ) specifies that no data is found.

The first rows of the result set are transferred to the communication buffer with the OPEN operation.




SAP AG 2004

BUFFER TABLE USR01 in

SAP buffer by allowing

buffering for USR01 table

WHILE..

Select * from USR01 where ...

ENDWHILE

Select * from USR01 where ...

Store output into InternalMemory Structure

WHILEPROCESSING

ENDWHILE

Eliminating Unnecessary SQL Statements

1

2

There are two basic ways to eliminate unnecessary SQL statements:

Change the application program to keep track of the information it has already read in its own program

memory (that is, build internal tables) Use the SAP mechanisms for buffering tables, but you must consider the following:

- Table size: Only small tables can be buffered.

- Technical settings: Check whether the technical settings allow buffering (use transaction SE12).

- Update frequency: Only tables with a small number of updates can be buffered.




SAP AG 2005

DB2 Explain Output

Timerons are a relative cost unit

Optimization level budgets query plan search

Type of parallelism used for the query

Some details for each

operationcan be displayed

Number of key columnsused

The optimizer search in DB2 UDB is budgeted by the optimization level selected for a query plan determination.

The DB2 Cost-Based Optimizer cost model tries to be accurate but relies on certain assumptions.

With optimizer level 0, the optimizer does not exploit statistical information to calculate the access plan. Greedy

join enumeration is performed with nested loop joins and index scans only. There are only minimal query

rewrites and no consideration of non-uniform distribution in statistics. List prefetch and index ANDing aredisabled.

Optimizer level 5 is the default setting in the SAP environment. This enables dynamic programming with

optimization effort. Heuristics are used in concert with self-adjusted throttling techniques to selectively apply

greedy join enumeration in order to manage the tradeoff between optimization time and plan execution time. The

optimizer considers all available statistics, all query rewrite rules, and most access paths. Materialized views and

hash join access methods are used with an optimizer level greater than or equal to 5.

Timerons are virtual timing units that are used as a relative measure of time on the same system. If you have two

systems that are exactly the same with the same database configuration parameters, you could compare the

execution cost of the statements between the systems.

If a statement is executed in parallel, the necessary work is divided into several portions, which are then executed

simultaneously, either by subagents in the same partition (intra-partition parallelism ) or in different partitions

(inter-partition parallelism). Parallelism is generally used for complex queries in OLAP systems, whereas in

OLTP systems no parallelism is used. The following values for Parallelism are possible in an access plan:

None – no parallelism used

Intra-partition parallelism – only possible if database manager configuration parameter

INTRA_PARALLEL was set to YES

Inter-partition parallelism – only possible in multi-partition environments

BP = both – intra-partition parallelism and inter-partition parallelism used

The More/Less Tree Info button provides the possibility to display information about the estimated number of rows

and the calculated total costs and I/O costs for each oprerator.




SAP AG 2004

Explain: Object Information

Double-click on object nameshows DB2 catalog information

For each object in the access plan, you can get detailed information by double-clicking on the object

name or mark the object and use button System Catalog Information.

Available information includes table and index definitions, timestamps and optimizer relevant statistical

data like e.g. cardinality for tables or distinct values, high2keys, low2keys for columns or

first[x]keycardinality, clusterratio and sizing information (nleaf, nlevels) for indexes.

Additionally it provides the possibility to update statistics for the chosen table.




SAP AG 2004

Explain: Operator Information

The Details output contains detailedinformation for each operator – especially about the predicates.

For each operator used in the access plan, DB2 provides additional information. To display this

information, mark an operator and choose Details.

An important part of the provided information is the information about the predicates. A predicate is thelogical condition in the WHERE clause that must be satisfied in order for the row to be included in the

result set.

The details output shows which predicates are used and how: You will find the start and stop conditions

for the range-delimiting predicates as well as the information which sargable predicates are used.




SAP AG 2005

SQL Enhancements Affecting Query Execution (1)

ABAP report

…SELECT

*FROM

“MARA”WHERE

“MANDT” = ? AND “MATNR” = ?FETCH FIRST 1 ROWS ONLY OPTIMIZE FOR 1 ROWS

…

...

SELECT SINGLE * FROM maraINTO t_mara

WHERE mandt = „100“and matnr = „1000“

...

SQL statement

DBSL

SELECT SINGLE statements

SAP’s open SQL statement SELECT SINGLE * FROM ... is internally transformed to a DB2 SQL

statement using DB2’s clauses:

FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS. OPTIMIZE FOR N ROWS (OFNR)

Optimizer chooses the quickest way to return a result with n rows.

Optimizer does not limit the result to n rows, but returns all records that match the query.

Changes in the access plan can be nested loop joins instead of mass join techniques. You can get

sorted records by using an appropriate index.

FETCH FIRST N ROWS ONLY (FFNR)

Limits the result to the first n returned rows does not affect Cost-Based Optimizer decisions.

This is not available as a single ABAP hint because it does not affect the decision of the Cost-Based

Optimizer.

For more information please refer SAP Note 150037.




SAP AG 2005

SQL Enhancements Affecting Query Execution (2)

…SELECT

*FROM

“MARA”WHERE

“MANDT” = ? AND “MATNR” = ?FETCH FIRST 15 ROWS ONLY OPTIMIZE FOR 15 ROWS

…

...

SELECT * FROM maraINTO TABLE t_mara

UP TO 15 ROWSWHERE mandt = „100“and matnr = „1000“

...

DBSL

ABAP report

SQL statement

SELECT UP TO <N> ROWS statements

SAP’s open SQL statement ... UP TO <N> ROWS ... is internally transformed to a DB2 SQL statement

using DB2’s clauses:

FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS




SAP AG 2004

Memory

Statistics

Indexes


Layout


Statement Tuning

Statistics




SAP AG 2004

Database Statistics

Database statistics:

Describe the physical characteristics of a table and theassociated indexes

These characteristics include:

Number of records

Number of pages

Degree of clustering of the table to any of its indexes

Average record length

Highest and lowest values in the index(es)

Number of distinct values in indexed columns

The DB2 optimizer uses these statistics when determiningaccess paths to the data.

Database Statistics contain important information about the data within tables and indexes, like e.g.

number of rows, etc and the objects themselves like e.g. the size of the tables and indexes.

The statistical data contributes largely to the cost calculations that the cost-based optimizer performsduring preparation and therefore the accuracy of the information is crucial for the optimizer to choose an

optimal access path.




SAP AG 2004

How are Statistics Collected?

Do not use RUNSTATS from command line!

The DB2 utility RUNSTATS is used to gather statistics.

In SAP environments, a specific infrastructure is providedto implement an efficient statistics strategy:

Control table DBSTATC

For Basis < 6.40: ABAP report RSDB6RSTATS and programdmdb6srp

For Basis 6.40: ABAP report RSDB6RSTATS

For Basis >= 7.00: DB2 Autorunstats

IBM provides the DB2 utility RUNSTATS for updating statistics.

In SAP environments this utility should not be used directly from command line, but via SAP provided

programs. These programs can be scheduled from transaction DB13 (DBA Planning Calendar) and provide certain control mechanisms to make sure that only the „right“ objects get new statistics.




SAP AG 2004

SAP Two-Phase Strategy

Control table DBSTATC

DB object TOBDO …

AUFK x …

EKPO …

KNVK x …

LIPS …

MKPF …

VBUK x …

RESB x …

The statistics for thedatabase objects thatare marked in table

DBSTATC are updated.

Phase 2

Database objects requiring anupdate of optimizer statisticsare determined and marked in

table DBSTATC.

Phase 1

Check tables for statistics update

RUNSTATS and REORGCHCK(DBSTATC)

In order to support customers in updating statistics only for those tables which really need it, SAP has

implemented a two-phase strategy:

In the first phase, SAP tools determine which tables require a statistical update and the control tableDBSTATC is then modified accordingly. To do this, in basis releases < 6.40, the job Check Tables for

Statistics Update needs to be scheduled in the DBA Planning Calendar.

In the second phase, the statistics of the tables marked with ‘X’ in column TOBDO in the control table

DBSTATC are refreshed. To do this, in basis releases < 6.40, the job RUNSTATS and REORGCHK

(DBSTATC) needs to be scheduled in the DBA Planning calendar or SAP’s executable dmdb6srp can

be scheduled at the operating system level.

In basis releases = 6.40, the two-phase strategy still exists, but is implemented in a single job,

RUNSTATS and REORGCHK (DBSTATC), which needs to be scheduled from the DBA Planning

Calendar.




SAP AG 2004

2. Performs an update statistic run on all flagged tables in DBSTATC

1. Performs a check run on all tables (no longer needed in Basis releases >= 6.40)

Add

Implementing SAP’s Statistics Strategy

Schedule jobs ona daily basis!

In ST04, choose Jobs DBA Planning Calendar .

Use the DBA Planning Calendar (go to transaction ST04 and choose Jobs → DBA Planning Calendar )

to schedule the check job (Action Check Tables for Statistics Update – only in basis releases < 6.40 ),

followed by the statistics update of flagged tables job (Action RUNSTATS and REORGCHK

(DBSTATC)) on a daily basis.




SAP AG 2004

Performs an update statistic run on all tables

Add

Optional Update Statistics Job for All Objects

Schedule job on aweekly basis!

Action RUNSTATS and REORGCHK for all Tables can be used to schedule a refresh of the statistical

data of all tables. Whereas ‘all tables’ means, that those tables that are excluded in control table

DBSTATC will not get new statistics.

If the daily jobs are implemented correctly, generally this job is not necessary. But if there is a time

window with low activity during the weekend, then optionally the customer can schedule the job.

SAP does not recommend to schedule this job on a daily basis!




SAP AG 2004

Modifying the Algorithm to Update the Statistics (1)

Tables can beexcluded by

modifying the Activeflag

basis release < 6.40

To modify the standard procedure used by SAP for refreshing the optimizer statistics, you can use the

Cost-Based Optimizer control panel (transaction DB21). The following fields are relevant:

Database object = <table name> Object type = 01 (table)

Database type = DB6

Active flag = A – Table will be processed.

Active flag = N – Table will never be processed by SAP tools, generally used for tables of unstable

size; the volatile flag is set for these tables.

Active flag = R or I – Table will not be processed by the regular statistics jobs, only when explicitly

required by the user.

During the analysis of an expensive SQL statement, it might become necessary to exclude a table from

the standard procedure.

Example: The optimizer chooses a sub-optimal access path and can only be motivated to choose a better

one by modifying table and/or index statistics manually. With the next update statistics run, these

changes would be overwritten. This can be prevented by excluding the object in DBSTATC. The

downside of this procedure is, that not only the statistical data for the optimizer are frozen, but also the

information about size of the table and its indexes is no longer updated.




SAP AG 2004


The Analysis method fieldcan be modified to collect,e.g., distribution statistics

for certain tables.

DB6B

Basis release < 6.40

Another example where a modification of the standard procedure might become necessary is in case an

expensive statement can only be improved when the objects involved have distribution statistics. In order

to have an automated statistics collection for these objects, field Analysis method in DBSTATC would

be set to „DB6A“.

Possible values for Analysis method include:

DB6B – Basic table and basic index statistics

DB6T – Basis statistics for tables and no index statistics

DB6I – Basis statistics for indexes and no table statistics

DB6A – Distributed table and extended index statistics

DB6D – Distributed table and no index statistics

DB6X – Extended index and no table statistics

DB6E – Distributed table and basic index statistics

DB6Y - Basic table and extended index statistics




SAP AG 2004


In ST04, choose Space Tables and Indexes

RUNSTATS Control tab.

Basis release = 6.40

The Analysis

method can bemodified or

objects can beexcluded from

statistics update.

Another example where a modification of the standard procedure might become necessary is in case an

expensive statement can only be improved when the objects involved have distribution statistics. In order

to have an automated statistics collection for these objects, field Analysis method in DBSTATC would

be set to „DB6A“.

Possible values for Analysis method include:

DB6B – Basic table and basic index statistics

DB6T – Basis statistics for tables and no index statistics

DB6I – Basis statistics for indexes and no table statistics

DB6A – Distributed table and extended index statistics

DB6D – Distributed table and no index statistics

DB6X – Extended index and no table statistics

DB6E – Distributed table and basic index statistics

DB6Y - Basic table and extended index statistics




SAP AG 2004

DB2 Autorunstats

Database configuration for Database L62

Automatic maintenance (AUTO_MAINT) = ONAutomatic database backup (AUTO_DB_BACKUP) = OFFAutomatic table maintenance (AUTO_TBL_MAINT) = ON

Automatic runstats (AUTO_RUNSTATS) = ONAutomatic statistics profiling (AUTO_STATS_PROF) = OFF

Automatic profile updates (AUTO_PROF_UPD) = OFFAutomatic reorganization (AUTO_REORG) = OFF

DB2 Autorunstats activSAP Runstats inactiv

Basis release = 7.00

With DB2 Version 8.2.2 the autorunstats functionality is recommended.

DB2 autorunstats will be activ, if the db cfg parameters auto_maint, auto_tbl_maint and

auto_runstats are set to on.To use the functionality by SAP it is necessary to plan the REORGCHK for all tables job.

DB2 runstats will decide by itself to use the optimal method to update the statistics.

In this case the SAP Runstats will be set to inaktiv and it is not possible to plan the SAP Jobs Check

Tables for Statistics Update and RUNSTATS and REORGCHK (DBSTATC)




SAP AG 2004

Reasons for Performance Problems

SYSSTAT

Systemtables

Remember Use only SAP tools to refresh the statistics of the SAP tables to

avoid performance problemsor

Use DB2 Autorunstats

Old statistical information

Incorrect cost-basedoptimizer assumptions

Severe performance problems can also be caused by the following:

Incorrect statistical information

Incorrect assumptions about the data distribution within the object

Performance problems regarding incorrect optimizer assumptions are normally fixed by adding

additional indexes or changing index definition or SAP access methods to the data (rsdb/...).

Use only SAP tools to refresh the statistics of the SAP tables. SAP tools ensure that the update is done

using the method and option defined for this object in the control table (DBSTATC). Every update of the

statistics not done by the SAP tools can create severe performance problems.

If incorrect Cost-Based Optimizer assumptions are made, open a customer message in SAPNet.




SAP AG 2004

Memory

Statistics

Indexes


Layout


Tips

Statement Tuning

Indexes




SAP AG 2004

Why Indexes?

Indexes can be used to

Ensure uniqueness

Avoid table scans where possible

Provide ordering

Facilitate clustering for more efficient access

Speed up table joins

Eliminate sorts

The reasons why indexes are created can be divided into two categories:

Application related: an index can be used to enforce the uniqueness constraints on one or more

columns because the application logic depends on it. Performance related: an index provides an efficient access to data in underlying tables when only a

subset of the data is required, or when it is faster to use the index than scanning the entire table.




SAP AG 2004

Clustering Index

Rows indata pages

Index

100% clustered n% clusteredn < 100

versus

A clustering index attempts to maintain a particular order of data.

The primary key is, in most cases, the clustering index.

Defining a clustering index has the following advantages:

Helps keeping newly inserted rows clustered according to this index.

Clustering can significantly improve the performance of operations such as prefetch and rangescans.

Reduces significantly the need for reorganizing the table.

Only one clustering index is allowed per table. In SAP environments this is generally the primary key.

As indicators how well the table data is clustered in regard to a specific index, use the

CLUSTERRATIO or CLUSTERFACTOR statistics collected by utility RUNSTATS. These numbers

are displayed in ST04 -> Space -> Tables and Indexes -> Indexes tab: fields Cluster Ratio and Cluster

Factor or on the Explain screen -> double click on relevant index name, use values for clusterratio and

clusterfactor .

If your analysis of an expensive statements shows that the access would benefit from a better clusterratio

of the scanned index and this index is not the primary key, then a reorganization of the table with the

index specified in the CLUSTER option of the reorg command can be used (making this index the

clustering index of the table).

Remember: In general, only one index can be one hundred percent clustered, except in those cases

where the keys are a superset of the keys of the clustering index or where there is de facto correlation

between the key columns of the two indexes.




SAP AG 2004

Index Reorganization

For Type-1 indexes, fragmentation is not a commonproblem.

If Type-2 indexes are used, then REORGCHK formulas F7and F8 should be monitored.

A REORG INDEXES command was introduced with DB2 8,but it is not supported by the DBA Planning Calendar.

Up to DB2 7, the REORG TABLE command needs to beused (or equivalent action in DBA Planning Calendar).

The cluster ratio can only be improved by reorganizing thetable!

During standard processing, normally Type-1 indexes do rarely need a reorganization. This can be

different under certain circumstances, e.g. massive INSERT, UPDATE, DELETE activities.

Due to the index design for Type-2 indexes, index fragmentation is more likely to happen than on Type-1 indexes .

With DB2 Version 8, the following options are available for index reorganization:

Reorganize all indexes of a table with the REORG INDEX command (unfortunately not yet

supported via DBA Planning Calendar). Therefore it is necessary to execute the command from the

command line.

Drop and re-create the index (supported via SE11 -> Indexes -> Utilities -> Database Utility)

Reorganize the table with all associated indexes (supported via ST04 -> Jobs -> DBA Planning

Calendar -> Action REORG and RUNSTATS for single table)

If the clusterratio of a certain index needs to be improved, it is not sufficient to reorganize the index!

Because the clusterratio describes the order of table rows in relation to a certain index, it is necessary to

reorganize the table (perhaps with specifying a new clustering index) to improve the clusterratio.




SAP AG 2004

Identifying Missing Indexes

In ST04, choose Diagnostics Missing Tables and Indexes.

Poorly qualified SQL statements can be caused when an index is defined in the SAP Data Dictionary but

is missing in the database. Missing database indexes can be discovered using transaction ST04, choose

Diagnostics → Missing Tables and Indexes.

Common reasons for missing indexes are as follows:

Indexes that are defined in the Data Dictionary but not activated

Indexes that are manually dropped for tests

The report on missing indexes is created as part of the Performance Collector background job. The

check on missing indexes should be performed once a week.

If necessary, you can also check for the index by querying the DB2 Data Dictionary using the DB2 CLP.

Indexes can be created, redefined, or dropped.




SAP AG 2005

Database Layout

Database layoutDatabase layoutDatabase layout I/O contentionI/O contentionI/O contention

This section describes how to identify an I/O contention problem caused by the layout of the database.




SAP AG 2004

DB2 agentDB2 agent

DB2 agent

DB2 agentDB2 agent

READ

I/Ocleaner I/O

cleaner

I/O

cleaner

I/O

Server

I/O

Server I/OServer

I/O Contention

READ/WRITE WRITE

X85#BTABD

X85#BTABI

X85#USER1D

X85#STABD

X85#STABI

X85#POOLD

DiskDisk Disk

Numerous DB2 EDUs access the same disk at the same time

I/O contention means high I/O wait times for processes accessing the database.

I/O contention occurs under the following circumstances:

Inefficient application design such as expensive, unnecessary, and poorly qualified statements

Data unevenly distributed across many disk cylinders

Disk not fast enough to handle high I/O activity

Heavily accessed tables or indexes are not distributed or striped across many disks

Incorrect hardware configuration such as many disks on few controllers

When numerous DB2 engine dispatchable units (EDUs) access the same disks, I/O contention is likely to

occur.

Note: Often I/O contention is caused by application design problems that must be checked first.




SAP AG 2004

Check for high number of physical reads opposed

to logical reads

Check for high number of physical reads opposed

to logical reads

Identifying I/O Contention

Check averageread time

Check averagewrite time

Check the average write andread time per tablespace for

peaks

Check the average write andread time per tablespace for

peaks

I/O per tablespace

Sorted by

To check for I/O contention, use transaction ST04 and choose Performance→ Tablespaces. Select Reset

and, after a short period, Since Reset . Sort by Data / Index Physical reads.

Use the Change Layout button to include the Avg. Phys. Read Time (ms) and Average Phys. Write Time

(ms) columns in the display.

Check the Avg. Phys. Read Time (ms) and the Average Phys. Write Time (ms) per tablespace to

determine if the average number for a tablespace are significantly higher than for the others. Average

numbers could indicate an I/O bottleneck but might not always point to the bottleneck.

Further analyze the tablespaces with high read or write times. To do this, choose Space → Containers in

ST04.

Note: Due to different hardware configurations and disk speeds, the values might vary significantly from

system to system. Your hardware vendor can provide more specific numbers.

Use operating system-specific disk monitoring tools to determine if all file systems and disks that are in

use by the most active tablespaces can cope with the workload.




SAP AG 2004

Solving I/O Contention

In ST04, choose Space Containers.

Once you have identified I/O contention, use the following methods to solve the problem:

Distribute I/O (for example, containers) evenly over the available disks.

Use faster disks.

Move hotspot tables or indexes into own tablespaces on own disks.

Create dedicated buffer pools for specific tablespaces.

Note: Different hardware platforms could have bottlenecks in disk controller ports, motherboards, and

backplanes. Refer to your hardware vendor for I/O distribution guidelines.

SAP’s monitors are limited to check the distribution of the container on the actual available disks. Use

operating system commands for further analysis (for example, with AIX, use lspv; on Windows, use

Disk Administrator).




SAP AG 2004


db2pfchr db2pfchr

Optimize I/O on RAID Devices

Buffer poolBuffer pool

db2pfchr db2pfchr

...

RAIDdevice

ExtentExtent SizeSize::

n * RAIDn * RAID StripeStripe SizeSize

PrefetchPrefetch SizeSize::

RAIDRAID StripeStripe SizeSize **

number number of of disksdisks

andand

n *n * ExtentExtent SizeSize

CREATE TABLESPACE ...

EXTENTSIZE x

PREFETCHSIZE y

...

When data is placed on redundant array of independent disks (RAID) devices, specific tasks must be

performed to optimize performance. Perform the following for each tablespace that uses a RAID device:

Make the EXTENTSIZE of the table space equal to (or a multiple of) the RAID stripe size. Ensure that the PREFETCHSIZE of the tablespace is:

- The RAID stripe size multiplied by the number of RAID parallel disk drives (or a whole multiple of

this product)

- A multiple of the EXTENTSIZE




SAP AG 2004

DB2 Performance Monitoring and Tuning:Unit Summary


Monitor key performance indicators for

DB2 Universal Database Recognize poorly qualified and unnecessary

SQL statements

Use the DB2 Dynamic SQL cache analysis

Identify I/O contention in the database




SAP AG 2005

Do the exercises

?


Unit Actions




Exercises

Unit: DB2 Performance Monitoring and Tuning


• Monitor performance indicators and memory areas using SAPmonitoring transactions

• Use the SQL trace facility and the SQL Cache to identify and analyzeexpensive SQL statements

• Verify the correct setup of the update statistics strategy

1-1 Log on to the SAP training system DEV and review the following:

1-1-1 What is the overall buffer quality of the training system?

1-1-2 What is the buffer quality of the individual buffer pools?

1-2 Check the locking status of the SAP training system DEV:

1-2-1 What is the current size of the lock list?

1-2-2 Check whether (exclusive) lock escalations have occurred since the startup of

the database.

1-2-3 Check whether (exclusive) lock escalations have occurred recently.

1-2-4 How can you influence the impact of a lock escalation on the locking behavior of other applications?

1-3 Enable the tracing of your own transactions. Open another mode and call transaction

ST04. Stop tracing your own transactions.

Note: If you cannot start the trace because another group already activated it for their

transactions, skip exercises 1-3 and 1-4, and proceed with exercise 1-5. Try to perform

exercise 1-3 and 1-4 at a later point in time.

1-4 Call DB2 Explain on an arbitrary SQL statement from within the SQL trace you

created in exercise 1-3.

1-5 Explain the following single SQL statement:

SELECT * FROM USR01

1-5-1 What is DB2’s estimated cost for executing this statement?

1-5-2 What is the access path chosen by the DB2 optimizer?

1-5-3 Which indexes exist for table USR01?




1-6 You are only interested in column BNAME of table USR01. How would you change

the statement in 1-5? Call DB2 Explain on the changed statement.

1-7 What is the difference between the old and the new access plans?

1-8 Use the Dynamic SQL Cache to find out:

1-8-1 Which statement has the largest share in the total DB response time?

1-8-2 Which statement has the highest average execution time?

1-9 Verify the access plans of the statements in 1-8.

1-10 What do the original statements in the ABAP source code look like?

1-11 Which tablespace has the highest average physical read time, and how is the data inthis tablespace distributed?




Solutions

Unit: DB2 Performance Monitoring and Tuning

1-1 Go to transaction ST04.

1-1-1 Look for the Overall Buffer Quality in section Performance -> Database -->

Buffer Pool .

1-1-2 Look at the values in column Buffer Quality (%) in section Performance --->

Buffer Pools.

1-2 Go to transaction ST04.

1-2-1 Look for the Lock List Size in section Performance -> Database -> Locks and

Deadlocks.

1-2-2 To check the (exclusive) lock escalation situation, look at the Escalations

information in section Locks and Deadlocks.

1-2-3 To check if (exclusive) lock escalations have occurred recently, check columns

Lock Escalations and X Lock Escalations in Performance -> History ->

Database.

1-2-4 Setting DB CFG Parameter MAXLOCKS to a value lower than 100 (e.g. 90)

forces a lock escalation to occur before the lock list is completely filled by the

locks of a single application, thus leaving free space for other applications toregister their locks.

1-3 Call transaction ST05. To enable the SQL trace, choose Activate Trace.

To open another mode and call transaction ST04, enter /oST04.To stop the trace, choose Deactivate Trace.

1-4 Choose Display Trace. Select an SQL statement and choose Explain. It is not possibleto explain all rows. Operations such as FETCH or CLOSE are not explainable.

1-5 In transaction ST05, choose Enter SQL Statement . Enter the SQL statement and choose Explain.

1-5-1 In the access plan display, you will see the total calculated cost as a number inexponential format and unit “timerons.”

1-5-2 In the access plan display, you will see TBSCAN as the chosen access path.

1-5-3 Double-click on the table name in the access plan display.

1-6 Change the statement to the following:SELECT BNAME FROM USR01




1-7 BNAME is part of index USR01~0. The access is now index-only.

1-8 In order to start the Dynamic SQL Cache Analysis, call transaction ST04 and choose

Performance -> SQL Cache.

Note: Only use the Refresh button if no statements are displayed.

1-8-1 Sort by column Total Execution Time(ms).

1-8-2 Sort by column Avg. Execution Time(ms).

1-9 To verify the access plans of the statements in 1-8, mark the appropriate statement andchoose Explain.

1-10 To display the ABAP source code of the above statements, mark the appropriatestatement and choose Source.

1-11 Call transaction ST04 and go to section Performance -> Tablespaces.

Add column Avg. Phys. Read Time (ms) to the display (use button Change Layout to do

this) and sort by the added column.

Then go to section Space -> Containers and check how many containers exist for the

relevant tablespace and in which directories/file systems they reside.




SAP AG 2005

Ongoing Operations

2 8Tools for DB2

Database Servers

10

1 Definitions

5 Managing Backups


9DB2 Performance


11 Problem Analysis



3 Partitioning

Ongoing Operations





SAP AG 2005

Ongoing Operations

Contents:

Regular DBA Tasks

Objectives:


Perform the regular tasks of database supervision

Implement preventive measures to improve systemavailability

Check and interpret the protocols provided by the SAPsystem

Monitor the administrative elements of your DB2

database servers




SAP AG 2005

Regular Admin Steps

At the start of each day, you should obtain an overview of the condition of your database in order to:

Recognize current problems or error conditions and resolve them

Solve problems before they become critical

Include the following areas in your daily monitoring:

Tablespaces: Check if there is enough freespace in each tablespace. Consider the growth of the objects

(tables and indexes) in the tablespaces.

Backup and archive: Check if the last backup and archive of offline log files were successful.

Directory log_archive: Check if there is enough freespace in the log_archive directory for offline logs

until the next planned archive run.

Performance: Check the settings of performance-related parameters of the database. Good database

performance ensures faster response times of the SAP system.




SAP AG 2005

System Check

Monitor daily

The System Check overview (from transaction ST04, choose Diagnostics → Single System check)

shows the status of your system.

This screen displays the date of the last backup and the state of user exit, log directory, automatic storageand the tablespace with the lowest freespace is displayed.




SAP AG 2005

DBA Log

Monitor daily

To get the DBA log information from transaction ST04, choose Jobs → DBA Log (or transaction DB24).

This is the complete log of all tool activities.




SAP AG 2005

Backup Protocol

Monitor daily

To view backup information, from transaction ST04, choose Backup and Recovery → Backup Overview

(transaction DB12). This should be checked daily.

To check your backup, use the db2chkbkp tool (refer to the "Tools for DB2 Database Servers" unit).

Note: Do not forget to back up all your operating system files!

To get backup information when the database is down, enter the following command:

db2 "list backup history all for database <dbsid>"




SAP AG 2005

DB2 Logging User Exit Protocol

PerformanceSpaceBackup and Recovery

Backup OverviewLogging Parameters

ConfigurationJobsAlerts


L62System

Backup and Recovery: Overview

Database Backup

Log File Name

Backup Overview

0000Partition 30Display Days

Userexit Brarchive

Move Date Move Time Return Code Target

C0000000 S0000222.LOG 10.12.2004 22:32:58 0 DISK

C0000000 S0000221.LOG 10.12.2004 15:08:13 0 DISKC0000000 S0000220.LOG 09.12.2004 22:32:09 0 DISK

C0000000 S0000219.LOG 06.12.2004 22:32:12 0 DISKC0000000 S0000218.LOG 05.12.2004 22:32:10 0 DISK

Monitor daily

Chain

To get the DB2 Logging User Exit Protocol information, from transaction ST04, choose Backup and

Recovery → Backup Overview (transaction DB12).




SAP AG 2005

Check DB2 Statistics Collection

Action Details

Monday, 23

02:00

PerformanceSpaceBackup and RecoveryConfiguration

Jobs

DBA LogAlerts


L61System

Category


Calendar ID

Planning Mode

DBA Actions

Local

Action Pad

Archive Inactive Log Files to DeviceArchive Inactive Log Files to TSMAutomatic Reorg

Check Tables for Statistics Update

Full Backup Into TSMFull Backup to Device

Full Backup with vendor LibraryRunstats and Reorgchk (DBSTATC)

Pattern Setup

04:0005:00

06:00

Tuesday, 24 Wednesday, 25 Thursday, 26 Friday, 27

Edit Delete AddAction Description

Action Parameters Job Log

Runstatsand Reorgchk (DBSTATC)

Planned Start 11.2.2005 12:01:10

Planned Start 11. 2.2005 12:01:12

Date MessageTime Msg ID Msg No

11.2.2005 10:00:52 00 516 Job started

11.2.2005 10:00:52 00 550 Step 001 started (program RSDBAJOB,

11.2.2005 10:01:00 00 517 Job finished11.2.2005 10:01:00 00 516 Job started11.2.2005 10:01:00 00 550 Step 001 started (program RSDB6RSTATS,

11.2.2005 10:01:02 DB6PM 000 Stats_Check: started by RSDB6RSTATS

Planned Start

Finished successfully

11.2.2005

Status

12:00:00


Monitor daily

To check the log of the ‘Check Tables for Statistics Update’ and ‘Runstats and Reorgchk (DBSTATC)’

actions, from transaction ST04, choose Jobs → DBA Planning Calendar (transaction DB13).




SAP AG 2005

brarchive Protocol

PerformanceSpace

Backup and RecoveryBackup OverviewLogging Parameters

ConfigurationJobsAlerts


L62System

Backup and Recovery: Overview

Database Backup

Log File Name

Backup Overview

0000Partition 30Display Days

Userexit Brarchive

Move D ate Move Time Return Code Target

S0000222.LOG 11.2.2005 09:32:25 0 TAPES0000221.LOG 11.2.2005 09:32:20 0 TAPES0000220.LOG 11.2.2005 09:32:15 0 TAPE


S0000216.LOG 11.2.2005 09:32:00 0 TAPES0000215.LOG 11.2.2005 09:31:56 0 TAPE


S0000211.LOG 11.2.2005 09:31:41 0 TAPES0000210.LOG 11.2.2005 09:31:38 0 TAPE

Monitor daily

Legacy

To get the brarchive log information, from transaction ST04, choose Backup and Recovery → Backup

Overview (transaction DB12).



© SAP AG ADM535 10-10

SAP AG 2005

Monitoring Tablespaces

222

Monitor weekly

111

It is in the nature of a database that it will grow over time. New extents are allocated in tablespaces and

sooner or later DB2 will not be able to find free space. To avoid downtime as a result of a tablespace full

condition, you should monitor freespace on a weekly basis or when performing actions that are expected

to change the space allocation in the database (for example, client copy and batch input). To get the

appropriate information, go to transaction ST04, choose Space -> Tablespaces.

You should (1) check the growth of tablespaces and, if necessary, (2) increase their size.

If you use auto-storage or auto-resize, than it is only necessary to verify the freespace on the Basis

filesystem!

Note: this screen should be used, to monitor tablespaces regarding their maximum size limits.s



© SAP AG ADM535 10-11

SAP AG 2005

Check Tablespace Growth

PerformanceSpace

TablespacesContainers

Tables and Indexes

HistoryOverview

Tables and IndexesBackup and Recovery

ConfigurationJobs

Alerts


L62System

Container Configuration

Statistics

0000Partition

Week 15.09.2004

Object Selection Tablespace 06.10.2004

From

To

Date Delta (KB Total)KB Total %UsedTablespace Name KB UsedL62#BTABD 06.10.2004 900.000 0 863.460 95,94

29.09.2004 900.000 0 727.740 80,86

22.09.2004 900.000 0 640.440 71,16

15.09.2004 900.000 0 450.270 50,03

Database and Tablespaces

First you should check the growth of the tablespace. To do this, call transaction ST04, choose Space ->

History -> Database and Tablespaces.

Based on this growth analysis, you are able to decide how many pages (or KB) to add to the tablespace.



© SAP AG ADM535 10-12

SAP AG 2005

Changing Tablespace Sizes

Performance

Space

ContainersTables and Indexes

HistoryBackup and Recovery

ConfigurationJobs

Alerts


L62System

Name L62#BTABDTablespaces

0000Partition

NodeGroup SAPNODEGRP_L62

Total

Space

Used 95,94

Free 36540

900.000 KB

KB

%

Tablespace Maintenance

TechnicalSettings Containers

Container Name

/db2/L62/sapdata4/NODE0000/L62#BTABD.container000

KB Total

900.000

Resize all containers to 1.000.000 KB

ALTER TABLESPACE "L62#BTABD"

RESIZE (ALL CONTAINERS 1000000 K

)ON NODE ( 0 )

;COMMIT WORK

;

The second step is to extend the tablespace. To do this in transaction ST04, choose Space →

Tablespaces.

SAP recommends increasing the size of all containers in one operation to avoid different container sizes.



© SAP AG ADM535 10-13

SAP AG 2005

Renaming db2diag.log

2004-11-12-18.34.44.249646 Instance:db2l62 Node:000PID:29625(db2star2) TID:1024 Appid:none

base sys utilities startdbm Probe:911

ADM7513W Database manager has started.

2004-11-12-18.41.09.735535 Instance:db2l62 Node:000

PID:30609(db2stop2) TID:1024 Appid:none

base sys utilities stopdbm Probe:48




ADM7514W Database manager has stopped.


PID:30656(R3trans) TID:1024 Appid:none

oper system services sqlo_access_listen_queue Probe:1

sapdb2:/db2/L62/db2dump>ls –ltr

-rw-rw-rw- 1 db2l62 dbl62adm 4365120 Dec 10 02:07 db2diag.log

db2diag_10122004.old

Monitor monthly

The db2diag.log file will receive a lot of information. This file might grow very fast. To avoid long

access times, it is useful to rename this file on a monthly basis.

For more information on analysing db2diag.log, refer to the "Problem Analysis" unit.



© SAP AG ADM535 10-14

SAP AG 2005

Check the Administration Notification Log File

ADM7513W Database manager has started.


PID:4052(db2agent (L62)) TID:1024 Appid:*LOCAL.db2l62.013D71090113

recovery manager sqlpresr Probe:410 Database:L62

ADM1530E Crash recovery has been initiated.


PID:4052(db2agent (L62)) TID:1024 Appid:*LOCAL.db2l62.013D71090113

recovery manager sqlpresr Probe:3170 Database:L62

ADM1531E Crash recovery has completed successfully.




ADM7514W Database manager has stopped.Monitor daily

The Notification Log contains information about events such as db2start, deadlocks, or crash recovery.

The DBM CFG parameter NOTIFYLEVEL determines the level of detail. Valid values for this

parameter are between 0 and 4. Default value is 3.



© SAP AG ADM535 10-15

SAP AG 2005

Ongoing Operations: Unit Summary


Perform the regular chores of database supervision

Implement preventive measures to improve system

availability

Check and interpret the protocols provided by the

SAP system

Monitor the administrative elements of your

DB2 database servers



© SAP AG ADM535 10-17

Exercises

Unit: Ongoing Operations


• Perform the regular monitoring of administrative tasks

1-1 Which are the daily monitoring tasks for an SAP system on DB2 as discussed in thischapter?

1-1-1 Please execute all necessary steps in the SAP training system DEV.

1-2 Monitor the growth of tablespaces.

1-2-1 Where can you find information about the growth of the tablespaces in the SAP

training system DEV?

1-2-2 Which options do you have to increase the size of a tablespace?

1-2-3 How you can prevent the manual steps of 1-2-2? Is there something tomonitor?



© SAP AG ADM535 10-18

Solutions

Unit: Ongoing Operations

1-1 Log on to the SAP training system DEV and go to transaction ST04.

1-1-1 In transaction ST04, the following daily monitoring tasks should be performed:

- System Check (State of Backup, User exit, Log directory, Log ArchiveDirectory is displayed):

ST04 -> Diagnostics -> Single System check .

- DBA Log information:

ST04 -> Jobs -> DBA Log .

- Backup, DB2 logging user exit and brarchive protocols:

ST04 -> Backup and Recovery -> Backup Overview.

1-1-2 You do not need the brarchive protocol information.

1-2 Monitor the growth of tablespaces.

1-2-1 To find information about the current size, go to transaction ST04, choose

Space -> Tablespaces. To view the growth of the tablespaces, in transaction

ST04, go to Space -> History -> Database and Tablespaces.

1-2-2 It is possible to increase the size of a tablespace in transaction ST04 -> Space -

> Tablespaces, or you can use alter tablespace on command line.

1-2-3 Usage of Auto-resize.




SAP AG 2005

Problem Analysis

2 8Tools for DB2

Database Servers

11 Problem Analysis



9

5 Managing Backups









SAP AG 2005

Problem Analysis

Contents:

Database Software Support

Database Software Maintenance

DB2 diagnostic information and diagnostic tools

Objectives:


Describe the support and maintenance processes for

DB2 database servers in SAP environments

Provide support information for service engineers

Collect support information from your database server




SAP AG 2005

SAP IBM Support Process

SAPIBM

Active Global SupportSAP DB2 UDBDevelopment

IBM DB2 AdvancedSupport

24 hours per day

7 days per week

365 days per year

24 hours per day

7 days per week

365 days per year

Customer IBM Labs

OSS Message #

PMR

SAP and IBM maintain a layered support structure for

customer systems using IBM's DB2 Universal Database

This figure illustrates the SAP IBM support cooperation.

The availability is based on severity levels. Critical SAP DB2 system situations (production down, very

high) are handled 24 hours by 365 days. Problems up to severity high are handled during normal business hours.




SAP AG 2004

DB2 Universal Database: Software Maintenance

OSS – Message Number

PMR - Problem ManagementRecord

APAR – Authorized Problem

Analysis Record

PTF – Program Temporary FIXFixpak – A Package of PTFs

SAPIBM

Each SAP Customer messages sent to SAP receives a message number.

When a customer case is recognized to be a problem that cannot be solved by SAP Active Global

Support, it is transferred to DB2 Support. Here a Problem Management Record (PMR) is created in IBM's problem tracking system.

If a problem is recognized as being a result of a defect, an Authorized Problem Analysis Record

(APAR) is created within IBM's problem tracking system. This record then triggers DB2 development to

provide a fix to the problem.

A Program Temporary Fix (PTF) is a fix to a generally released IBM software product. By installing a

matching PTF on the system, a customer with a DB2 problem will be able to solve the reported problem.

A FixPak is a set of PTFs. These sets are cumulations of PTFs up to a predefined level. Applying the

latest Fixpak level will help you to further improve the performance and stability of your DB2 database

server.




SAP AG 2005

SAP performs special integration tests with IBM

supplied FixPaks

SAP Note 101809 supplies information about thesupported FixPaks

SAP Note 166481 supplies information about

available RDBMS CD-ROMs, including FixPaks

Use only SAP supplied FixPaks

DB2 FixPaks

STOP

When IBM releases a new DB2 version or FixPak, SAP performs special tests to ensure compatibility

with SAP.

Every FixPak that was successfully tested is documented in SAP Note 101809 and can be installed bycustomers.

For information about how to retrieve the appropriate RDBMS CD-ROM, see SAP Note 166481.

Ensure you use only RDBMS software (including FixPaks) that was shipped by SAP.

To show the current DB2 version and Fixpak level, use the following command:db2level




SAP AG 2005

Fixpak Download

DB2 fixpaks for download on SAP service market place:

http://service.sap.com/swcenter-3pmain




SAP AG 2005

Checking the DB2 Diagnostic Log File

2003-01-28-02.07.24.550689 Instance:db2l62 Node:000PID:16391(db2agent (L62)) TID:1024 Appid:*LOCAL.db2l62.043EF8030132database utilities sqlubcka Probe:130 Database:L62

Backup Complete.

2003-01-29-02.01.46.094131 Instance:db2l62 Node:000PID:22415(db2stop2) TID:1024 Appid:none


ADM7514W Database manager has stopped.

File db2diag.log contains RAS information for your

database. RAS stands for

Reliability

Availability Serviceability

You can read db2diag.log with standard tools:

The DB2 diagnostic log file contains information in ASCII text about problems encountered within DB2.

Each entry follows a predefined format. In error situations, hexadecimal error codes are displayed.

It is helpful to decode the hexadecimal codes to understand the error situation. Because Intel systems andRISC systems differ in the endian-ness, you may have to rearrange the hexadecimal codes to represent a

negative number. Values of type 0xFFFFnnnn represent a negative number, values of type 0xnnnnFFFF

have to be byte-reversed. For example, 0x0AE6FFFF will become 0xFFFFE60A. To translate this

hexadecimal code into a number, you must create the 2s-complement (0x019F6) and translate it to

decimal 6646. This SQL code can be looked up in the error messages manual or the DB2 support Web

site.

To analyse the db2diag.log file you can use the command db2diag. It filters and formats the diagnostic

file.




SAP AG 2005

DB2DART for Database Maintenance and Analysis

DB2 V810 DB2DART HELPSyntax:

DB2DART <DB Alias> <option> ...

Help:/H Help. This help text.

Inspect Action:/DB (default) Inspects entire database./T Inspects a single table. (See notes 1, 3)/TSF Inspects only the tablespace files and containers./TSC Inspects a tablespace's constructs (but not its tables)./TS Inspects a single tablespace and its tables.

(/TSC and /TS require a tablespace id. See notes 1, 2)/ATSC Inspect constructs of all tablespaces (but not their tables).

Data Format Action:........

DB2DART provides a superset of the functions provided by

the DB2 INSPECT command

For options, call DB2DART on command line:

DB2DART is run from the command line

It can be used while the database is up and running, but the results may be unpredictable. Therefore, for

best accuracy and performance, use DB2DART while the database is stopped. A subset of the DB2DART functionality is available with the DB2 command INSPECT, which can be

called during normal operations.




SAP AG 2005

DB2SUPPORT – For Capturing Information

monster:db2l62 58> db2support . -d L62 -cOutput file is "/db2/db2l62/db2support.zip"Time and date of this collection: "Wed 16 Dec 2002 09:55:07 PM GMT+1 GMT+1"...Creating final output archive

"db2support.html""db2_sqllib_directory.txt""db2supp_system.zip""JDKlevel.cfg"

...db2support is now complete. An archive file has been produced: "db2support.zip„

monster:db2l62 58>

DB2SUPPORT can be called from the command line. It

captures all information that support analysts might need

Call the tool when instructed by SAP support engineers.

The result is a zip file, which can be attached to the

customer message

Example invocation:

Details on this tool can be viewed in SAP Note 83819. For Windows systems, refer to SAP Note 88190.



© SAP AG ADM535 11-10

SAP AG 2005

Db2pd – Monitor and Troubleshoot DB2

Example: db2pd –database <dbsid> -bufferpools

BufferPools:

First Active Pool ID 1

Max Bufferpool ID 1

Max Bufferpool ID on Disk 1

Num Bufferpools 3

Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp CurrentSz

1 IBMDEFAULTBP 4096 1000 0 0 3 1000

2 MYBUFFERPOOL 4096 10000 0 0 9 10000

3 BUFFERPOOL3 8096 5000 0 0 4 5000

Db2pd retrieves information from the DB2 memory sets.

Executed from the command line with optional interactive mode.

Runs very quickly as it is not needing any locks or latches. This executable runs outside of the enginesresources (for example works on a hung DB2 engine).

Can be used by customers to monitor and troubleshoot.

Gives the user a closer view into the DB2 engine.

To use this tool it is necessary to have SYSADM authority and on Unix/Linux you must be instance

owner.



© SAP AG ADM535 11-12

SAP AG 2005

Problem Analysis: Unit Summary


Describe the support and maintenance processes for

DB2 database servers in SAP environments Provide support information for service engineers

Collect support information from your database server

If you have any suggestions regarding this training or

SAP DB2 products, please send e-mail to

[email protected]

adm535_db2_unix_n_nt

Documents