This book is your must-have companion for the administration of SAP MaxDB. It discusses useful principles, provides a step-by-step guide for typical administration tasks, and supports you in identifying and solving common problems.

The authors provide you with a highly detailed overview of the database, illustrating the different instance types, the structure, and the user concept. The interaction between SAP NetWeaver Application Server and SAP MaxDB is described in depth before you dive into the typical tasks within the lifecycle of a database instance — from installation and upgrade, to the configuration of the database as well as backup and recovery, to the deletion of the instance. Chapters with useful information on performance and troubleshooting tips for common problems serve to round out this comprehensive administration handbook.

The book is based on SAP MaxDB 7.7, but it can also be used for older releases.

Highlights

• Setup of a database instance
• Software installation and upgrade
• Generating, initializing, and configuring a database
• Backup and recovery
• Interaction with SAP NetWeaver Application Server
• Consistency checks
• Analysis tools
• Caches and indexes
• Diagnosis files

About the Author(s)

André Bögelsack, Stephan Gradl, and Manuel Mayer work at the SAP University Competence Center of the Technische Universität München in the SAP NetWeaver and SAP MaxDB area. Professor Helmut Krcmar holds the Chair of Information Management at the Technische Universität München.

Table of Contents

1 ... Introduction to SAP MaxDB ... 11
1.1 ... History ... 11
1.2 ... SAP MaxDB Features ... 12
... 1.2.1 ... General Features ... 12
... 1.2.2 ... Flexibility during Operation ... 14
... 1.2.3 ... SQL Modes and Interfaces ... 15
... 1.2.4 ... Areas of Use ... 16
1.3 ... Useful Internet Sources ... 16
... 1.3.1 ... Official SAP MaxDB Website ... 17
... 1.3.2 ... SAP MaxDB Wiki on the SAP Developer Network ... 17
... 1.3.3 ... SAP MaxDB FAQ ... 17
... 1.3.4 ... SAP MaxDB Forum ... 17
1.4 ... Structure of this Book ... 18

2 ... Overview of SAP MaxDB ... 21
2.1 ... SAP MaxDB — Instance Types ... 21
... 2.1.1 ... OLTP and OLAP ... 21
... 2.1.2 ... SAP liveCache ... 23
2.2 ... SAP MaxDB — Software ... 26
... 2.2.1 ... The X Server ... 27
... 2.2.2 ... Database Studio ... 27
... 2.2.3 ... Database Manager GUI ... 30
... 2.2.4 ... Database Manager CLI ... 34
... 2.2.5 ... SQL Studio ... 35
... 2.2.6 ... SQL CLI ... 36
... 2.2.7 ... Web SQL ... 37
... 2.2.8 ... Other Utilities ... 38
2.3 ... SAP MaxDB — User Concept ... 45
... 2.3.1 ... MaxDB Users ... 45
... 2.3.2 ... Operating System Users ... 52
... 2.3.3 ... Security Aspects ... 54
2.4 ... Database Concepts ... 55
... 2.4.1 ... Kernel Threads ... 56
... 2.4.2 ... Caches ... 63
... 2.4.3 ... Data and Log Volumes ... 70
... 2.4.4 ... Savepoints and Snapshots ... 76
... 2.4.5 ... Locking ... 78
... 2.4.6 ... Directory Structure ... 80
... 2.4.7 ... Operational States ... 83
... 2.4.8 ... Database Parameters ... 84
... 2.4.9 ... Configuration Files ... 86
2.5 ... Summary ... 87

3 ... SAP MaxDB and SAP ... 89
3.1 ... SAP Architectures ... 89
... 3.1.1 ... ABAP and Java Stack ... 90
... 3.1.2 ... Architecture Levels ... 91
3.2 ... Communication with SAP MaxDB ... 93
... 3.2.1 ... SAP MaxDB Interfaces ... 96
... 3.2.2 ... Communication with SAP Systems ... 99
3.3 ... Important Transactions ... 102
... 3.3.1 ... Transaction DB50 — Database Assistant ... 103
... 3.3.2 ... Transaction DB13 ... 109
... 3.3.3 ... Transaction RZ20 ... 112
3.4 ... Summary ... 114

4 ... Administration Tasks ... 115
4.1 ... Server Software Installation and Upgrade ... 115
... 4.1.1 ... SDBINST/SDBSETUP ... 116
... 4.1.2 ... SDBUPD ... 126
4.2 ... Creating and Initializing the Database ... 130
... 4.2.1 ... Planning the Database ... 130
... 4.2.2 ... Creating the Database via the GUI ... 132
4.2.3 ... Creating the Database via the dbmcli Tool ... 140
... 4.2.4 ... Interaction with SAPInst ... 144
4.3 ... Configuring the Database ... 146
... 4.3.1 ... Adding and Deleting Data/Log Volumes ... 146
... 4.3.2 ... Configuring Log Volumes and Log Mode ... 154
... 4.3.3 ... Updating the System Tables ... 158
... 4.3.4 ... Parameter Changes ... 159
4.4 ... Database Backup ... 163
... 4.4.1 ... Backup Concepts ... 163
... 4.4.2 ... Creating a Backup Medium ... 166
... 4.4.3 ... Incremental and Complete Backup ... 171
... 4.4.4 ... Log Backups ... 174
... 4.4.5 ... Snapshots ... 177
... 4.4.6 ... Checking Backups ... 182
4.5 ... Database Recovery ... 186
... 4.5.1 ... Recovery Types ... 186
... 4.5.2 ... Recovery Strategy ... 187
... 4.5.3 ... Recovery/Recovery with Initialization ... 188
... 4.5.4 ... Reintegrating Faulty Log Mirrors ... 192
... 4.5.5 ... Bad Indexes ... 193
4.6 ... Consistency Checks ... 196
... 4.6.1 ... General Description ... 196
... 4.6.2 ... Checking the Database Structure ... 197
4.7 ... Deleting the Database ... 200
... 4.7.1 ... Deleting the Database ... 200
... 4.7.2 ... Server Software Uninstallation ... 203
4.8 ... Summary ... 206

5 ... Performance Tuning ... 207
5.1 ... Performance Optimization ... 208
5.2 ... Indexes ... 208
... 5.2.1 ... B* Trees: Theory ... 209
... 5.2.2 ... Primary and Secondary Key ... 211
5.3 ... The Database Optimizer ... 218
... 5.3.1 ... Basic Principles ... 218
... 5.3.2 ... Criteria for Selecting Specific Access Strategies ... 223
5.4 ... Caches ... 226
... 5.4.1 ... Background ... 226
... 5.4.2 ... The Various Caches ... 227
... 5.4.3 ... The Appropriate Size of the Caches ... 230
... 5.4.4 ... The Most Important Information in Caches ... 230
... 5.4.5 ... Critical Region Statistics ... 235
5.5 ... Analysis Tools ... 237
... 5.5.1 ... Database Analyzer ... 238
... 5.5.2 ... Resource Monitor ... 249
... 5.5.3 ... Command Monitor ... 253
... 5.5.4 ... SQL Explain ... 260
5.6 ... Performance with SAP NetWeaver AS ... 263
... 5.6.1 ... SAP NetWeaver AS Performance Analysis ... 264
... 5.6.2 ... Load Analysis ... 266
... 5.6.3 ... Database Analysis in SAP NetWeaver AS ... 267
5.7 ... Summary ... 280

6 ... Problem Situations ... 281
6.1 ... Diagnostic Files ... 281
... 6.1.1 ... Dev Traces ... 282
... 6.1.2 ... SQL Trace ... 282
... 6.1.3 ... SQLDBC Trace ... 283
... 6.1.4 ... X Server Log: xserver_.prt ... 284
... 6.1.5 ... appldiag ... 285
... 6.1.6 ... dbm.prt ... 285
... 6.1.7 ... KnlMsg (knldiag ... 286
... 6.1.8 ... KnlMsgArchive (knldiag.err, dbm.utl ... 287
... 6.1.9 ... dbm.knl ... 288
... 6.1.10 ... dbm.ebp ... 289
... 6.1.11 ... dbm.ebl ... 289
... 6.1.12 ... rtedump ... 290
... 6.1.13 ... knltrace ... 291
... 6.1.14 ... knldump ... 292
6.2 ... Error Types and Analysis ... 293
... 6.2.1 ... Installation Problems ... 293
... 6.2.2 ... Connection Problems ... 294
... 6.2.3 ... Log Full/Data Full ... 295
... 6.2.4 ... System Crash/System Error ... 299
... 6.2.5 ... System Blockade ... 300
... 6.2.6 ... Backup/Recovery Error ... 302
... 6.2.7 ... Hardware Error ... 303
6.3 ... Summary ... 304

7 ... Summary and Outlook ... 305

A ... Command Reference dbmcli ... 309

B ... The Authors ... 319