SAP DB User Manual
User Manual: SAP DB
SAP AG November 2002
Copyright
© Copyright 2002 SAP AG.
Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.1 or any later version published by the Free
Software Foundation.
For more information on the GNU Free Documentaton License see
http://www.gnu.org/copyleft/fdl.html#SEC4.
User Manual: SAP DB 2
SAP AG November 2002
Icons
Icon Meaning
Caution
Example
Note
Recommendation
Syntax
Typographic Conventions
Type Style Description
Example text
Example text
Words or characters that appear on the screen. These include field
names, screen titles, pushbuttons as well as menu names, paths and
options.
Cross-references to other documentation.
Emphasized words or phrases in body text, titles of graphics and tables.
EXAMPLE TEXT Names of elements in the system. These include report names,
program names, transaction codes, table names, and individual key
words of a programming language, when surrounded by body text, for
example, SELECT and INCLUDE.
Example text
EXAMPLE TEXT
Example text
<Example text>
Screen output. This includes file and directory names and their paths,
messages, source code, names of variables and parameters as well as
names of installation, upgrade and database tools.
Keys on the keyboard, for example, function keys (such as F2) or the
ENTER key.
Exact user entry. These are words or characters that you enter in the
system exactly as they appear in the documentation.
Variable user entry. Pointed brackets indicate that you replace these
words and characters with appropriate entries.
User Manual: SAP DB 3
SAP AG November 2002
User Manual: SAP DB ............................................................................................................. 13
Architecture of the Database System .................................................................................. 13
Database Instance ........................................................................................................... 14
Thread........................................................................................................................... 14
User Kernel Thread (UKT) ........................................................................................ 14
Data Writer ............................................................................................................ 15
Log Writer .............................................................................................................. 15
Server Tasks ......................................................................................................... 16
Timer Task............................................................................................................. 16
Trace Writer Task .................................................................................................. 16
User Task .............................................................................................................. 16
Utility Task ............................................................................................................. 16
Special Thread .......................................................................................................... 17
Coordinator............................................................................................................ 17
Dev Thread............................................................................................................ 17
Requester .............................................................................................................. 18
Temporary Dev Thread ......................................................................................... 18
Timer...................................................................................................................... 18
Operating-System-Dependent Special Threads ....................................................... 18
Clock Thread ......................................................................................................... 18
Console Thread ..................................................................................................... 18
Cache............................................................................................................................ 19
Catalog Cache .......................................................................................................... 19
I/O Buffer Cache ....................................................................................................... 19
Converter............................................................................................................... 19
Data Cache............................................................................................................ 20
Log Queue ................................................................................................................ 20
Volume.......................................................................................................................... 21
Data Volume ............................................................................................................. 21
Log Volume ............................................................................................................... 22
Database Instance Type .................................................................................................. 23
SAP DB OLTP .............................................................................................................. 23
liveCache ...................................................................................................................... 23
SAP DB Document Server............................................................................................ 23
SAP DB OLAP .............................................................................................................. 24
SAP DB E-Catalog........................................................................................................ 24
SAP DB Versions and Database Instance Types ............................................................ 24
Operating System Platforms ............................................................................................ 25
Multiprocessor Configuration............................................................................................ 25
User Manual: SAP DB 4
SAP AG November 2002
User Concept ....................................................................................................................... 25
SAP DB User Classes...................................................................................................... 26
Database Manager Operator (DBM Operator) ............................................................. 26
Authorizations ........................................................................................................... 27
User Authorizations ............................................................................................... 27
Default Authorizations for the First DBM Operator ............................................ 28
Operating System User Authorizations ................................................................. 28
Database User.............................................................................................................. 28
Database User Classes ............................................................................................ 28
SYSDBA ................................................................................................................ 29
DBA ....................................................................................................................... 29
DOMAIN ................................................................................................................ 29
RESOURCE .......................................................................................................... 30
STANDARD........................................................................................................... 30
User Groups.............................................................................................................. 30
The Role Concept ............................................................................................................ 30
User Data as Options ....................................................................................................... 31
Options (C/C++ Precompiler) ....................................................................................... 32
Required Options ............................................................................................................. 34
User Data and XUSER..................................................................................................... 34
Using XUSER................................................................................................................... 35
XUSER Data................................................................................................................. 35
Generating XUSER Data in the Background................................................................ 36
Security Concepts................................................................................................................ 37
Availability......................................................................................................................... 38
Security Requirements ................................................................................................. 38
Restartability..................................................................................................................... 38
Backup Strategy ............................................................................................................... 39
Backup .......................................................................................................................... 40
Data Backup.............................................................................................................. 40
Complete Data Backup.......................................................................................... 40
Incremental Data Backup ...................................................................................... 41
Parallel Backup...................................................................................................... 41
Saving Data Backups ............................................................................................ 41
Log Backup ............................................................................................................... 41
Automatic Log Backup........................................................................................... 42
Interactive Log Backup.......................................................................................... 42
Saving Log Backups.............................................................................................. 43
External Backup Tool.................................................................................................... 43
Log Concept......................................................................................................................... 43
User Manual: SAP DB 5
SAP AG November 2002
Log Entry .......................................................................................................................... 44
Redo Log Entry............................................................................................................. 44
Undo Log Entry............................................................................................................. 44
Online Logging ................................................................................................................. 44
Redo Log Management ................................................................................................ 45
Log Queue ................................................................................................................ 45
Log Page................................................................................................................... 46
Log Writer.................................................................................................................. 46
Log Area.................................................................................................................... 47
Undo Log Management ................................................................................................ 47
Undo Log File............................................................................................................ 48
History Management..................................................................................................... 48
History File ................................................................................................................ 48
History List ................................................................................................................ 49
Garbage Collector ..................................................................................................... 49
Restart or Recovery ......................................................................................................... 49
Redo Log Manager ....................................................................................................... 49
Log Reader ................................................................................................................... 50
Redo Log File ............................................................................................................... 50
Redo List....................................................................................................................... 51
Redo Task..................................................................................................................... 51
Savepoint on Restart .................................................................................................... 52
Example: Restart .......................................................................................................... 52
Database Tools.................................................................................................................... 53
Architecture of the SAP DB Tools .................................................................................... 53
Architecture of the Database Manager......................................................................... 54
Architecture of the SAP DB Loader .............................................................................. 55
SQL Studio Architecture ............................................................................................... 56
Architecture of the SAP DB Web Tools ........................................................................ 57
X Server ........................................................................................................................ 58
DBM Server .................................................................................................................. 58
Loader........................................................................................................................... 59
Web Server................................................................................................................... 59
Database Manager........................................................................................................... 59
Database Manager GUI................................................................................................ 60
Options (DBMGUI) .................................................................................................... 60
Database Manager CLI................................................................................................. 61
Options (DBMCLI)..................................................................................................... 61
DBM Commands....................................................................................................... 62
Web DBM...................................................................................................................... 62
User Manual: SAP DB 6
SAP AG November 2002
SAP DB Loader ................................................................................................................ 63
Options (LOADERCLI).................................................................................................. 64
Loader Commands ....................................................................................................... 64
SQL Studio: Introduction .................................................................................................. 65
SQL Studio.................................................................................................................... 65
Options (SQL Studio) ................................................................................................ 66
Web SQL ...................................................................................................................... 66
Directory Structure of the Database for SAP Systems ........................................................ 67
Variables........................................................................................................................... 68
Distribution of the SAP DB Directories on the Hard Disk................................................. 68
Security Requirements ................................................................................................. 68
Performance Requirements.......................................................................................... 69
Example Configuration ................................................................................................. 69
Various Database Systems .......................................................................................... 70
SAP DB Directories ...................................................................................................... 70
Instance Data ............................................................................................................ 71
Programs that Are Independent of the Database Software Version......................... 71
Libraries for the Client Run-time Environment.......................................................... 72
Programs that Are Dependent on the Database Software Version .......................... 72
Client Tools ............................................................................................................... 73
Example: SAP DB Directory Structure...................................................................... 73
Display SAP DB Directories ...................................................................................... 73
Define SAP DB Directories ....................................................................................... 74
Directory Structure of the Database System for Open Source............................................ 74
Variables........................................................................................................................... 75
Distribution of the SAP DB Directories on the Hard Disk................................................. 75
Security Requirements ................................................................................................. 75
Performance Requirements.......................................................................................... 76
Example Configuration ................................................................................................. 77
Various Database Systems .......................................................................................... 77
SAP DB Directories ...................................................................................................... 77
Display SAP DB Directories ...................................................................................... 78
Define SAP DB Directories ....................................................................................... 79
Database Parameters .......................................................................................................... 79
General Database Parameters......................................................................................... 79
Special Database Parameters (Extended)....................................................................... 80
Support Database Parameters......................................................................................... 80
BACKUP_BLOCK_CNT ................................................................................................... 81
CACHE_SIZE................................................................................................................... 81
CAT_CACHE_SUPPLY ................................................................................................... 81
User Manual: SAP DB 7
SAP AG November 2002
DATE_TIME_FORMAT .................................................................................................... 81
DEADLOCK_DETECTION............................................................................................... 81
DEFAULT_CODE............................................................................................................. 81
DEVNO_BIT_COUNT ...................................................................................................... 81
INSTANCE_TYPE............................................................................................................ 82
JOIN_MAXTAB_LEVEL9 ................................................................................................. 82
JOIN_MAXTAB_LEVEL4 ................................................................................................. 82
JOIN_SEARCH_LEVEL ................................................................................................... 83
KERNELDIAGSIZE .......................................................................................................... 83
KERNELVERSION........................................................................................................... 83
LOG_BACKUP_TO_PIPE................................................................................................ 83
LOG_IO_QUEUE ............................................................................................................. 84
LOG_MODE ..................................................................................................................... 84
LOG_SEGMENT_SIZE .................................................................................................... 84
LRU_FOR_SCAN............................................................................................................. 84
MAXARCHIVELOGS........................................................................................................ 84
MAXBACKUPDEVS ......................................................................................................... 85
MAXCPU .......................................................................................................................... 85
MAXDATADEVSPACES .................................................................................................. 85
MAXDATAVOLUMES ...................................................................................................... 85
MAXLOCKS...................................................................................................................... 85
MAXLOGVOLUMES ........................................................................................................ 86
MAXRGN_REQUEST ...................................................................................................... 86
MAXSERVERTASKS ....................................................................................................... 86
MAXUSERTASKS............................................................................................................ 86
MP_RGN_LOOP .............................................................................................................. 86
OPTIM_BUILD_RESLT.................................................................................................... 86
OPTIM_FETCH_RESLT .................................................................................................. 87
OPTIM_KEY_INV_RATE ................................................................................................. 87
OPTIM_MAX_MERGE ..................................................................................................... 87
OPTIM_ORDERBY_IDX .................................................................................................. 87
OPTIM_OR_DISTINCT .................................................................................................... 88
REQUEST_TIMEOUT...................................................................................................... 88
RESTART_SHUTDOWN ................................................................................................. 88
RUNDIRECTORY ............................................................................................................ 88
SEQUENCE_CACHE....................................................................................................... 89
SESSION_TIMEOUT ....................................................................................................... 89
UTILITY_PROT_SIZE ...................................................................................................... 89
_DATA_CACHE_RGNS................................................................................................... 89
_EVENT_ALIVE_CYCLE ................................................................................................. 89
User Manual: SAP DB 8
SAP AG November 2002
_MAXEVENTS ................................................................................................................. 89
_MAX_MESSAGE_FILES................................................................................................ 89
_ROW_RGNS .................................................................................................................. 90
_TAB_RGNS .................................................................................................................... 90
_TRANS_RGNS............................................................................................................... 90
_TREE_RGNS.................................................................................................................. 90
_UNICODE....................................................................................................................... 90
SAP DB as UNICODE Database ......................................................................................... 90
UNICODE......................................................................................................................... 91
Installing a UNICODE-Enabled Database........................................................................ 91
Setting Database Parameter _UNICODE..................................................................... 92
Setting Code Attribute UNICODE................................................................................. 92
UNICODE and SQL.......................................................................................................... 93
Example 1..................................................................................................................... 94
UNICODE in Programming Languages ........................................................................... 96
Example 2..................................................................................................................... 97
Data Management Using B* Trees .................................................................................... 101
Concepts ........................................................................................................................ 101
Primary Key ................................................................................................................ 101
Secondary Key ........................................................................................................... 102
B* Tree........................................................................................................................ 102
Root/Index Page ..................................................................................................... 103
Leaf Page................................................................................................................ 103
Table Access........................................................................................................... 103
Table ID ...................................................................................................................... 104
B* Trees for Tables ........................................................................................................ 104
B* Trees for Table with LONG Columns..................................................................... 104
B* Trees for Tables with Secondary Key.................................................................... 105
B* Trees for Tables with LONG Columns and Secondary Key .................................. 106
Table Access Using B* Tree .......................................................................................... 107
Table Access (SELECT) Using B* Tree ..................................................................... 107
Table Access (INSERT) Using B* Tree ...................................................................... 109
Table Access (DELETE) Using B* Tree ..................................................................... 110
Table Access (UPDATE) Using B* Tree..................................................................... 111
Changes in the B* Tree Structure .................................................................................. 111
Non-Uniform Distributions of Data Pages................................................................... 112
Lock Behavior .................................................................................................................... 113
Lock ................................................................................................................................ 113
Shared Lock................................................................................................................ 114
Exclusive Lock ............................................................................................................ 115
User Manual: SAP DB 9
SAP AG November 2002
Optimistic Lock ........................................................................................................... 115
Requesting and Releasing a Lock ................................................................................. 115
Isolation Level ................................................................................................................ 116
Isolation Level 0.......................................................................................................... 117
Isolation Level 1 or 10................................................................................................. 117
Isolation Level 15........................................................................................................ 118
Isolation Level 2 or 20................................................................................................. 118
Isolation Level 3 or 30................................................................................................. 118
Phenomena .................................................................................................................... 119
Dirty Read................................................................................................................... 119
Non-Repeatable Read ................................................................................................ 119
Phantom...................................................................................................................... 119
SAP DB Version 7.4........................................................................................................... 120
SAP DB Improvements Since 1997 ............................................................................... 120
SAP DB Tools................................................................................................................. 120
Technical Specification of SAP DB Version 7.4............................................................. 121
Improvements in SAP DB Version 7.4 ........................................................................... 122
Terms ................................................................................................................................. 123
Application Data ............................................................................................................. 125
Automatic Log Backup ................................................................................................... 126
Backup............................................................................................................................ 126
Backup History ............................................................................................................... 126
Backup ID ....................................................................................................................... 127
Backup Medium.............................................................................................................. 127
B* Tree ........................................................................................................................... 128
Cache ............................................................................................................................. 129
Catalog ........................................................................................................................... 129
COMMIT ......................................................................................................................... 129
Converter........................................................................................................................ 129
Data Cache..................................................................................................................... 130
Data Volume................................................................................................................... 130
Data Area ....................................................................................................................... 131
Data Backup................................................................................................................... 131
Database Administrator.................................................................................................. 131
Database Catalog........................................................................................................... 131
Database Instance ......................................................................................................... 132
Database Instance Type ................................................................................................ 132
Database Manager......................................................................................................... 133
Database Manager CLI .................................................................................................. 133
Database Manager GUI ................................................................................................. 134
User Manual: SAP DB 10
SAP AG November 2002
Database Manager Operator (DBM Operator)............................................................... 134
Database Name ............................................................................................................. 135
Database Parameters .................................................................................................... 135
Database Session .......................................................................................................... 135
Database Trace.............................................................................................................. 136
Database Tools .............................................................................................................. 136
Database User................................................................................................................ 136
DBA ................................................................................................................................ 137
DBM Operator ................................................................................................................ 137
DBMCLI .......................................................................................................................... 137
DBMGUI ......................................................................................................................... 137
DOMAIN ......................................................................................................................... 137
External Backup ID......................................................................................................... 137
External Backup Medium ............................................................................................... 138
External Backup Tool ..................................................................................................... 138
Group of Parallel Backup Media..................................................................................... 138
I/O Buffer Cache............................................................................................................. 138
Instance Type ................................................................................................................. 139
Interactive Log Backup................................................................................................... 139
Isolation Level ................................................................................................................ 139
Kernel ............................................................................................................................. 140
Language Support (MapChar Sets) ............................................................................... 140
liveCache........................................................................................................................ 140
Lock ................................................................................................................................ 140
Log Area ......................................................................................................................... 142
Log Backup..................................................................................................................... 142
Log Mode........................................................................................................................ 143
Log Volume .................................................................................................................... 144
Multiprocessor Configuration.......................................................................................... 144
Name of a Standard Backup Medium ............................................................................ 145
Name of External Backup Medium................................................................................. 145
Operating Mode.............................................................................................................. 145
Page ............................................................................................................................... 146
Page Pool....................................................................................................................... 146
Parallel Backup .............................................................................................................. 146
RESOURCE ................................................................................................................... 146
Restart ............................................................................................................................ 146
ROLLBACK .................................................................................................................... 147
Run Directory.................................................................................................................. 147
SAP DB Document Server ............................................................................................. 147
User Manual: SAP DB 11
SAP AG November 2002
SAP DB E-Catalog ......................................................................................................... 148
SAP DB Loader .............................................................................................................. 148
SAP DB OLAP................................................................................................................ 149
SAP DB OLTP................................................................................................................ 149
SAP DB User Classes.................................................................................................... 149
Savepoint........................................................................................................................ 149
Session........................................................................................................................... 150
Single Backup Medium................................................................................................... 150
SQL Locks ...................................................................................................................... 150
SQL Mode ...................................................................................................................... 150
SQL Studio ..................................................................................................................... 150
SYSDBA ......................................................................................................................... 151
Task................................................................................................................................ 151
Thread ............................................................................................................................ 151
Transaction..................................................................................................................... 152
Transaction File.............................................................................................................. 152
Transaction List .............................................................................................................. 152
UNICODE....................................................................................................................... 152
Version File..................................................................................................................... 153
Volume ........................................................................................................................... 153
Users .............................................................................................................................. 153
Web DBM ....................................................................................................................... 154
Web SQL ........................................................................................................................ 154
Web Server..................................................................................................................... 156
X Server.......................................................................................................................... 156
SAP DB Documentation..................................................................................................... 156
SAP DB Software............................................................................................................... 158
SAP DB Support ................................................................................................................ 158
User Manual: SAP DB 12
SAP AG November 2002
User Manual: SAP DB
This manual provides an overview of the database system SAP DB Version 7.4 and the tools
contained therein.
Architecture of the database system [Page 13]
User concept [Page
Security concepts [Page 37]
Log concept [Page
Database tools [Page
Directory structure of the database for SAP Systems [Page 67]
Directory structure of the database for Open Source [Page
Database parameters [Page
SAP DB as a UNICODE Database [Page 90]
Data management using B* trees [Page
Lock behavior [Page
SAP DB Version 7.4 [Page 120]
Terms [Page 123]
SAP DB documentation [Page
SAP DB software [Page 158]
SAP DB support [Page 158]
25]
43]
136]
74]
135]
101]
113]
156]
Architecture of the Database System
You can find an overview of the main architecture characteristics of the SAP DB relational
database system in the Fact Sheet on the SAP DB homepage www.sapdb.org . Some
aspects of the SAP DB architecture are described in more detail below:
• Database instance [Page
• Database instance type [Page
• SAP DB versions and database instance types [Page
• Operating system platforms [Page
• Multiprocessor configuration [Page
See also:
Directory structure of the database for Open Source [Page
Directory structure of the database for SAP Systems [Page 67]
Security concepts [Page
Data management using B* trees [Page 101]
Database tools [Page 136]
132]
132]
24]
25]
144]
74]
37]
User Manual: SAP DB 13
SAP AG November 2002
Database Instance
The SAP DB database can be installed and run on a computer in one mode (database
instance) or several modes (database instances) (Database Instance Type [Page
also: SAP DB Versions and Database Instance Types [Page 24]).
132], see
Every database instance consists of threads [Page
[Page 129]) and volumes [Page 153].
151], main memory structures (caches
Threads
Database
Caches
instance
Volumes
The following volumes are available for each database instance:
• One or more data volumes [Page
• One or more log volumes [Page
Each database instance differentiates between the following areas for the logical storage of
data:
• Database catalog [Page
131]
130]
144]
• Application data [Page
125]
Thread
A whole series of operating system threads (often referred to as kernels) belong to a
database instance [Page
We differentiate between UKTs (user kernel threads) and special threads.
The required number of UKTs and of special threads depends on the hardware configuration,
the number of volumes [Page
• User Kernel Thread (UKT) [Page
• Special Thread [Page
• Operating-System-Dependent Special Thread [Page
132].
153] used, and the database parameters that were set.
14]
17]
18]
User Kernel Thread (UKT)
A database instance [Page 132] contains a series of threads [Page 151]. A user kernel thread
(UKT) forms a subset of all tasks [Page
151] (for internal tasking).
The following types of tasks exist:
• Data Writer [Page
User Manual: SAP DB 14
15]
SAP AG November 2002
• Log Writer [Page 46]
• Server Task [Page
• Timer Task [Page
• Trace Writer Task [Page
• User Task [Page
• Utility Task [Page
16]
16]
16]
16]
16]
Data Writer
Data writers are user kernel threads (UKT) [Page 14]. Data writer tasks are responsible for
writing data from the data cache [Page
active when a savepoint [Page 149] is performed.
Savepoint writing takes a long time for a large data cache. The data writers also become
active between the end of one and the start of the next savepoint, to write data
asynchronously from the data cache to disk.
The number of data writers is calculated by the system. It depends primarily on the data
cache size (CACHE_SIZE [Page
[Page 85]).
130] to the data volumes [Page 130]. They become
81]) and the number of data volumes (MAXDATAVOLUMES
Log Writer
One active component of redo log management [Page 45] is the log writer.
The log writer is a user kernel thread (UKT) [Page 14]. When the database system is started,
it is initialized using permanently stored, internal configuration information. This configuration
information is written to the log area at regular intervals, in particular at a savepoint [Page
149].
• A log queue [Page
• The log writer writes the log pages [Page
of a COMMIT [Page 129] or ROLLBACK [Page 147], from the log queue to the log area
[Page 142]. The log pages are numbered (log sequence number), so that it is possible to
check that all log pages were written, and to ensure the correct working sequence in the
case of a restart [Page 146] or recovery. The log writer then notifies the transactions that
were waiting for their redo log entries [Page 44] to be written.
• Log pages of the log queue that were not full when a write operation was performed
remain in the log queue and continue to be filled, and are written to the log area in a
subsequent write operation. The log writer is configured so that it always writes one and
the same log page to the same physical place.
• The log writer regularly checks the state of the log area.
If the log area is full, the log writer locks the log queue so that all transactions that want to
enter redo log entries [Page
When the automatic log backup [Page 126] is active, the log writer ensures that the redo
log entries from the log area are backed up automatically.
When a certain number of log entries have been written, the administrative information is
copied to the log area, and savepoints are requested, if required. In the case of a restart,
this reduces the restart time.
45] is assigned to the log writer.
46] that are full, or have to be written as a result
44] into the log queue are stopped.
User Manual: SAP DB 15
SAP AG November 2002
Server Tasks
A server task is a user kernel thread (UKT) [Page 14]. The main purpose of server tasks is to
parallelize database functions such as saving to a group of parallel media, restoring from a
number of media in parallel, and compiling indexes.
When the database parameters are being configured, the number of server tasks is
determined automatically from the number of data volumes [Page
number of data backup devices in use.
The maximum number of server tasks available is defined by the MAXSERVERTASKS [Page
86] [Page 86] database parameter.
130] [Page 130] and the
Timer Task
A timer task is a user kernel thread (UKT) [Page 14]. The timer task handles timeout
situations of all types.
Trace Writer Task
The database system enables the database trace [Page 136] to be activated for the purpose
of diagnosis. The trace writer task (a special user kernel thread (UKT) [Page 14]) is provided
for this.
User Task
When he or it logs on to the database instance [Page 132], each user [Page 14] of the
instance or each application is assigned precisely one fixed user task. The user task ensures
the processing of SQL statements for the database session.
The number of user tasks available is defined by the MAXUSERTASKS [Page
parameter.
86] database
Utility Task
A utility task is a user kernel thread (UKT) [Page 14]. The utility task is reserved solely for
managing the database instance [Page
As there is only one utility task for each database instance, no parallel managing actions can
be performed.
An exception to this rule is the automatic log backup [Page
parallel with other management actions.
User Manual: SAP DB 16
132].
126]. This can be performed in
SAP AG November 2002
Special Thread
A database instance [Page 132] has the following special threads [Page 151]:
• Coordinator [Page
• Dev Threads [Page
• Requester [Page
• Temporary Dev Threads [Page
• Timer [Page
17]
17]
18]
18]
18]
Coordinator
The coordinator is a special thread [Page 17]. The coordinator monitors all kernel threads in
the database instance [Page
When the database instance is started, the coordinator is the first active thread. It coordinates
the starting processes of the other threads.
• If a thread fails while a UNIX operating system is running, the coordinator terminates all
other threads.
• If a thread fails while a Windows NT/Windows 2000 operating system is running, an
exception handler becomes responsible for terminating all the other threads in an orderly
way.
132].
Dev Thread
Dev threads are special threads [Page 17]. Dev threads handle the read and write commands
that read and write tasks [Page
The number of dev threads is primarily dependent on the number of volumes [Page
the database instance [Page 132]. Under normal circumstances, two dev threads are
activated for every data volume [Page 130] and every log volume [Page 144]. Only one dev
thread is activated for writing the kernel trace, if it is enabled.
The dev thread dev0 plays a special role; dev0 coordinates and monitors the dev threads.
• For example, if a log volume fails in warm mode (bad volume), dev0 ensures that the
corresponding dev threads are terminated. The database instance is transferred to
OFFLINE mode [Page
• If the database is enlarged while running by adding another data volume, dev0 ensures
that new dev threads are generated.
All the other dev<i> threads write data to or read data from the volumes.
151] ask to have performed.
153] in
145].
User Manual: SAP DB 17
SAP AG November 2002
Requester
The requester is a special thread [Page 17]. The requester receives both local communication
requests (CONNECT) and requests from the network and assigns them to a user kernel
thread (UKT) [Page 14] .
Temporary Dev Thread
Temporary dev threads (asdev<i>) (which are special threads [Page 17]) are activated to
read and write data for data backups [Page 131].
Timer
The timer is a special thread [Page 17]. The timer monitors time for timeout control.
Operating-System-Dependent Special Threads
A database instance [Page 132] has the following operating-system-dependent special
threads [Page 151]:
• Clock Thread [Page
• Console Thread [Page
18]
18]
Clock Thread
The clock thread is an operating-system dependent special thread [Page 18]. The clock
thread is only used under Windows NT.
It computes internal times; for example, to determine the time needed to execute an SQL
statement.
Console Thread
The console thread is an operating-system dependent special thread [Page 18].
Windows NT/Windows 2000 UNIX
Requests from the
XCONS console
knldiag file
User Manual: SAP DB 18
The console thread processes
requests from the XCONS console.
The XCONS program
communicates with the console
thread for this purpose.
The console thread collects all
database instance [Page
messages from the other threads
and logs these in a file called
132]
The XCONS console receives the
necessary information directly from
the shared memory of the threads.
The knldiag file is created. Each
thread writes information to this file.
SAP AG November 2002
knldiag.
Cache
Read and write operations to the volumes [Page 153] of a database instance [Page 132] are
buffered in order to save on disk accesses.
The pertinent main memory structures are called caches. They can be dimensioned
appropriately.
The database system recognizes the following caches, among others:
• Catalog Cache [Page
• I/O Buffer Cache [Page
Data Cache [Page 130]
Converter [Page 129]
• Log Queue [Page
19]
138]
45]
Catalog Cache
The catalog cache [Page 129] of a database instance [Page 132] stores the catalog objects
most recently used by a database session [Page 135] and the internal representation
(execution plans) of the most recently executed SQL statements.
Data which is expelled from the catalog cache is moved for the time being to the data cache
[Page 130].
A catalog cache is exists once per database user session.
I/O Buffer Cache
One important cache [Page 129] of a SAP DB database is the I/O buffer cache.
When the database system is started, the I/O buffer cache is created in the main memory in
accordance with the size entered in the database parameter CACHE_SIZE [Page
managed via the page pool [Page
A certain number of pages of the I/O buffer cache are made available to the converter [Page
129]. The remaining pages are made available to the data cache [Page 130].
The converter requires more pages during database operation, the distribution of all I/O buffer
cache pages among the converter and data cache is dynamically changes to meet this
requirement.
146].
81] and is
Converter
The converter is used for the assignment of logical to physical data pages. When data pages
that are not in the data cache [Page 130] are accessed, the system attempts to find their
physical position in the data volumes [Page 130] first in the converter.
User Manual: SAP DB 19
SAP AG November 2002
The converter is used by all users who are active at the same time, and is therefore located in
full in the main memory. Only the converter pages that contain a mapping of permanent data
pages are written to the data volumes with each savepoint [Page 149]. In the case of a restart
[Page 146], the converter can restore the database instance using these pages.
The converter is dimensioned dynamically, which means that you cannot directly influence the
size of the converter. The required converter pages are taken from the I/O buffer cache [Page
138], which is used jointly by the converter and the data cache. The size of the I/O buffer
cache is determined by the database parameter CACHE_SIZE [Page 81].
If the converter requires more pages than were originally assigned, the number of data cache
pages is reduced accordingly. If the converter requires fewer pages, the free pages are
managed via the page pool [Page
146], and can be used again by the converter if it grows.
Data Cache
The data cache contains the last read- or write-accessed pages of the data volumes [Page
130].
The data cache is shared by all simultaneously active users. It is therefore located in the main
memory.
The data cache is dimensioned dynamically, which means that you cannot directly influence
the size of the data cache. The required data cache pages are taken from the I/O buffer
cache [Page 138], which is used jointly by the converter [Page 129] and the data cache. The
size of the I/O buffer cache is determined by the database parameter CACHE_SIZE [Page
81]. If the converter grows, the number of data cache pages is reduced accordingly.
The hit rate, that is the relation between successful and unsuccessful accesses to the data
cache, is a crucial measure of performance. Successful access means that the required data
was already available in the data cache.
Log Queue
The area of the main memory required for redo log management [Page 45] is called the log
queue. The size of a log queue (in log pages [Page 46]) is determined by the database
parameter LOG_IO_QUEUE [Page
A transaction [Page
[Page 44]. The transaction writes the redo log entry to the log pages of the log queues.
Writing of the log pages to the log area [Page
152] uses a log queue to obtain a main memory area for a redo log entry
Process Flow
1. The user task [Page 16] of the transaction reserves main memory space for a redo log
entry in the log queue.
84].
142] is carried out by the log writer [Page 46].
2. The transaction writes the redo log entry to the reserved area of the log queue.
The time at which the redo log entry is written to the log queue is assigned to the relevant
log page (log queue sequence number).
3. The transaction releases the reserved area of the log queue for processing by the log
writer, and provides information on whether it wants to wait for log page from the log
queue to be written to the log area. This behavior is always required for COMMIT [Page
129] and ROLLBACK [Page 147] operations.
If a transaction does wait for the redo log entry to be written, the log writer notifies the
transaction once the relevant page has been written from the log queue to the log area,
User Manual: SAP DB 20
SAP AG November 2002
and informs the transaction of the log sequence number that was assigned when the log
page was written to the log area.
Volume
A volume is a logical grouping of physical storage units (disks). A volume can be a part of a
physical disk, a complete physical disk, or a completely structured storage system consisting
of several storage units.
The disks used should have identical performance parameters (specifically access speeds) to
ensure an even filling of the volumes.
If database management tools are used, a volume is addressed via a directory path.
The database system differentiates between data volumes [Page
144].
Every database instance [Page
configure the maximum numbers of data and log volumes that are possible when installing
the database instance.
If necessary, you can add data or log volumes to a database instance while the database is
running. Directory paths for data and log volumes can be changed.
132] has one or more log volumes and data volumes. You
130] and log volumes [Page
Data Volume
A database instance [Page 132] has volumes [Page 153], in which the data writer [Page 15]
records the database contents. These volumes are called data volumes. All data volumes
form the data area [Page 131].
Among other things, the data volumes contain the application data [Page 125] and the
metadata of the database catalog [Page
Managing Application Data
The application data of a table or index can use just one page [Page 146] in the data area as
a minimum. A table can extend across all data volumes of the data area as a maximum. A
table increases or decreases in size automatically without administrative intervention. As a
rule, a database-internal striping algorithm distributes the data belonging to a table evenly
across all the data volumes.
131].
It is therefore not possible or necessary to assign tables to the individual data volumes.
See: Data Management Using B* Trees [Page
101]
Managing Data Volumes
You can configure one or more data volumes when you install a new database instance. The
disk storage space defined by all the data volumes is the total size of the database.
During database operation, you can add new data volumes. The number of data volumes is
limited by the database parameter MAXDATAVOLUMES [Page
See:
Database Manager GUI: SAP DB 7.4, Section Managing Database Instances [See SAP DB
Library]
Database Manager CLI: SAP DB 7.4, Section Adding a Volume [See SAP DB Library]
User Manual: SAP DB 21
85].
SAP AG November 2002
Data Backups
To ensure safe database operation, it is necessary to back up the data area at regular
intervals.
See: Data Backups [Page
131]
Log Volume
A database instance [Page 132] has volumes [Page 153] in which the log writer [Page 46]
records all changes to the database contents. These volumes are called log volumes. All log
volumes form the log area [Page 142].
The log entries are needed to ensure that the database content can be restored if a media
device fails. To guarantee safe database operation, the writing of all log entries to the log area
should always be active. This ensures that the log area cannot be overwritten until the log
entries have been backed up.
Managing Log Volumes
You can configure one or more log volumes when you install a new database instance.
During database operation, you can add new log volumes. The number of log volumes is
limited by the database parameter MAXLOGVOLUMES [Page 86].
See:
Database Manager GUI: SAP DB 7.4, Section Managing Database Instances [See SAP DB
Library]
Database Manager CLI: SAP DB 7.4, Section Adding a Volume [See SAP DB Library]
Log Mode
Usually, the log mode [Page 143] should be set to SINGLE.
See:
Database Manager GUI: SAP DB 7.4, Section Changing the Log Mode [See SAP DB Library]
Database Manager CLI: SAP DB 7.4, Section Changing the Value of a Database Parameter
[See SAP DB Library]
For safety reasons, the log area should always be mirrored on a hardware basis. If it is not
possible to mirror the log area on a hardware basis, you can achieve a software-based
mirroring by setting the log mode DUAL.
Log Backups
• To ensure uninterrupted database operation, you must ensure that there is always
enough space available in the log area.
• For safe database operation, it is necessary for the log entries to be backed up at regular
intervals (including for mirroring the log area).
Therefore, carry out log backups [Page
uninterrupted database operation by authorizing an automatic log backup [Page 126].
The log segment size, the size of the log volume, and the capacity of the backup medium
must be –matched to each other. The log segment size should be selected so that enough
space remains in the log area during the backup of a log segments to ensure that the
changes can also be incorporated.
142] at regular intervals. You can best ensure
User Manual: SAP DB 22
SAP AG November 2002
Database Instance Type
The SAP DB database system supports different application areas. The SAP DB database
instance [Page 132] has different characteristics depending on the application area. The
following database instance types exist:
• SAP DB OLTP [Page
• liveCache [Page
• SAP DB Document Server [Page
• SAP DB OLAP [Page
• SAP DB E-Catalog [Page
See also:
SAP DB Versions and Database Instance Types [Page
149]
140]
147]
149]
148]
24]
SAP DB OLTP
SAP DB OLTP is a database instance type [Page 132] of the SAP DB database system. SAP
DB is a relational database system that was developed for OLTP (Online Transaction
Processing). The database system is optimized to process individual transactions fast in
environments with a high number of users and large databases.
liveCache
liveCache is a database instance type [Page 132] of the SAP DB database system.
In Supply Chain Management, large volumes of data must be permanently available and
changeable. For this reason, an addition has been made to the SAP DB OLTP [Page
relational database system to enable actual data structures and data flows (such as networks
and relationships) to be mapped more easily and effectively. The product is called liveCache.
The liveCache is object-oriented, and in contrast to SAP DB OLTP, stores its data in the main
memory of the database system.
149]
SAP DB Document Server
SAP DB Document Server is a database instance type [Page 132] of the SAP DB database
system.
In today’s information landscape, a large amount of data must be processed that does not
have the typical format of a relational database, but is “unstructured” (such as videos, XML
documents). SAP DB Document Server was developed on the basis of the SAP DB OLTP
[Page 149] relational database system to ensure that as much of this unstructured data as
possible can be processed outside the OLTP database. This improves the performance of the
SAP DB OLTP database.
One application example is the SAP Content Server.
User Manual: SAP DB 23
SAP AG November 2002
SAP DB OLAP
SAP DB OLAP is a database instance type [Page 132] of the SAP DB database system.
Online Analytical Processing (OLAP) technologies enable you to perform flexible analyses
from a variety of business perspectives. It is based on a multi-dimensional data model that is
achieved using relational database tables.
One application example is the Business Warehouse System. In contrast to SAP DB OLTP
[Page 149] systems, a Business Warehouse System is configured so that large quantities of
historical and operative data can be formatted with acceptable response times.
SAP DB E-Catalog
SAP DB E-Catalog is a database instance type [Page 132] of the SAP DB database system.
In Internet catalog applications, a small number of hits must be determined from a large
number of product descriptions. To support this, the TREX search engine has been integrated
in the SAP DB OLTP [Page 149] relational database system. With the TREX search engine,
product descriptions (long texts) can be indexed, and the search terms looked for (exact
search, phrase search, fuzzy search, linguistic search).
One application example is the BugsEye or eMerge product from Requisite.
SAP DB Versions and Database Instance Types
The following table contains a list of the SAP DB versions of the individual database instance
types [Page 132].
SAP DB
Version
7.2.05
7.3.00
SAP DB
OLTP
liveCache SAP DB
. . . .
.
Database Instance Type
SAP DB OLAP SAP DB
Document
Server
. . .
E-Catalog
7.3.32
7.4.01
7.4.02
User Manual: SAP DB 24
.
.
.
SAP AG November 2002
7.4.03
. . . . .
Operating System Platforms
SAP DB Version 7.4 supports the following operating system platforms:
• Compaq True64 Unix / Alpha
• IBM AIX / PowerPC
• SUN Solaris / SPARC
• HP-UX / HP-PA
• Linux / Intel
• Windows XP
• Windows NT / Intel
• .Net Server (under construction)
Multiprocessor Configuration
To allow multiprocessor configurations to be used to the best advantage, the database
system supports external/internal tasking that can be configured.
The aim here is to allow the maximum possible number of database sessions [Page
be supported by the minimum possible number of operating system threads.
The configuration of the database system controls the degree of external/internal tasking via
the two parameters MAXUSERTASKS [Page 86] and MAXCPU [Page 85].
On a computer, 4 processors are available for the database instance [Page
No more than 800 database sessions should be running simultaneously.
In this case, set MAXCPU to 4 and MAXUSERTASKS to 800.
The database instance can then utilize the four processors by establishing four
operating system threads, each of which performs an internal tasking for up to
200 users.
135] to
132].
User Concept
The SAP DB database system uses SAP DB user classes [Page 149] and supports different
roles (role concept [Page
30]).
Setting Up a Database Session
A database session [Page 135] is started when the user logs on to the database instance
[Page 132]. In order to log on, certain user data needs to be transferred to the SAP DB
component for identification purposes.
User Manual: SAP DB 25
SAP AG November 2002
The following user data is needed to log on:
userid
password
database_name
server_node
In addition to this generally required user data, you can enter further data, which is then
transferred when you log on.
You can also transfer the following user data to the SAP DB tools or the C/C++ Precompiler
programs:
• Enter user data as options [Page
always be entered for each tool used)
• Enter user data on the logon screen (for the Database Manager GUI and SQL Studio
tools)
Note the special features of entering user data in the SQL Studio (Opening a Database
Session [See SAP DB Library]).
• User Data Using XUSER [Page
Entering incomplete or incorrect user data when calling the C/C++ Precompiler programs,
application programs, or the SAP DB tools cause the program to terminate with an error
message.
When you call the C/C++ Precompiler and application programs that were
generated using the SAP DB programming interface, you can also transfer user
data directly in the program itself. For this reason, the program contains a special
modified rule for the transfer of user data. Basically, the logon options for the
C/C++ Precompiler and application programs are the same as for the SAP DB
tools.
User name
The user’s password
The name of the SAP DB instance you want to work on
The name of the server node on which the database you called is running
31] (note that the required options [Page 34] must
34]
SAP DB User Classes
The SAP DB database system differentiates between two main user classes:
• Database Manager Operator (DBM Operator) [Page
• Database Users [Page
136]
134]
Database Manager Operator (DBM Operator)
Users working with the database management tool Database Manager [Page 133] are known
as Database Manager Operators. The SAP DB user class [Page 149] is the DBM operator.
Depending on what authorizations [Page
operator is able to perform all kinds of Database Manager functions.
You create the first DBM operator when you install a database instance [Page
you register a database instance, you must specify the name and the password of the first
DBM operator. In this way, you legitimize yourself as the DBM operator of the database
instance. The DBM operator’s password can be changed at a later date.
27] the DBM operator has been given, a DBM
132]. When
User Manual: SAP DB 26
SAP AG November 2002
• This DBM operator is then responsible for managing and monitoring the database system
and for running backups. The DBM operator is also authorized to perform all Database
Manager functions, regardless of what operating mode the database instance is in.
• The DBM operator is also authorized to create additional DBM operators, and assign
these all or some of their authorizations.
• The DBM operator can log on to the Database Manager more than once, which means
the DBM operator can, for example, query operating parameters while functions that take
a long time are still running.
DBM operators are not database users [Page
users in order to work on a database instance.
136]. You need to create database
Authorizations
The database system SAP DB makes a distinction between two types of authorizations for
the Database Manager operator [Page
134] (DBM operator):
• User authorizations [Page
Authorizations of the DBM operator for executing the functions of the Database Manager
[Page 133]
• Operating system user authorizations [Page
Authorizations of the DBM operator for accessing remote servers via the Database
Manager
27]
28]
User Authorizations
User authorizations are the authorizations [Page 27] of the Database Manager operator [Page
134] (DBM operator) that he/she needs to use the required functionality of the Database
Manager [Page 133].
An authorization may cover more than one command and one command may have more than
one authorization assigned to it.
Use the Database Manager to assign the relevant user authorizations to a DBM operator.
See also:
Database Manager CLI: SAP DB 7.4: User Authorizations [See SAP DB Library]
The entries in the following table refer to the relevant sections of the documentation on the
Database Manager CLI.
.
User authorization Description
DBInfoRead [See SAP DB Library] Displaying state information
ExecLoad [See SAP DB Library] Running the LOAD program
SystemCmd [See SAP DB Library] Executing operating system commands
Backup [See SAP DB Library] Carrying out backups
InstallMgm [See SAP DB Library] Installation management
LoadSysTab [See SAP DB Library] Loading system tables
DBStart [See SAP DB Library] Starting the database instance
User Manual: SAP DB 27
SAP AG November 2002
DBStop [See SAP DB Library] Stopping the database instance
UserMgm [See SAP DB Library] Managing the DBM operator
Recovery [See SAP DB Library] Restoring backups
DBFileRead [See SAP DB Library] Accessing database files (read-only)
ParamCheckWrite [See SAP DB
Library]
ParamFull [See SAP DB Library] Accessing database parameters (read and write)
ParamRead [See SAP DB Library] Accessing database parameters (read-only)
AccessSQL [See SAP DB Library] Accessing SQL session
AccessUtility [See SAP DB Library] Accessing a utility session
Default Authorizations for the First DBM Operator [Page 28]
Accessing database parameters (checked write)
Default Authorizations for the First DBM Operator
The first DBM operator [Page 134] you create when you register a database instance
contains all user authorizations [Page
27].
Operating System User Authorizations
The operating system user authorization is one of the authorizations [Page 27] of the
Database Manager operator [Page 134] (DBM operator). To execute operating system
commands on remote computers using the Database Manager [Page
must have operating system user authorizations on those computers.
133], the DBM operator
Use the Database Manager functionality to assign a DBM operator the user identification for
the operating system.
Database User
Database users log on to a database instance [Page 132] and work with database objects
such as tables, views, and indexes (the SAP DB user class [Page 149] is database user).
SAP DB differentiates between various database user classes [Page
Database users can be grouped into user groups [Page 30].
28].
Database User Classes
Database users [Page 136] are divided into database user classes. The database user class
that a database user belongs to defines which operations that user is allowed to execute.
SAP DB differentiates between the following database user classes:
• SYSDBA [Page
151]
User Manual: SAP DB 28
SAP AG November 2002
• DBA [Page 137]
• DOMAIN [Page
• RESOURCE [Page
• STANDARD [Page
137]
146]
30]
SYSDBA
The SYSDBA user (database system administrator) is a special database user [Page 136] of
the database user class [Page 28] SYSDBA. The SYSDBA user is the first database user that
is created when a new database instance [Page 132] is installed. Enter a user name and
password for this user.
Each database instance has one single SYSDBA user.
The SYSDBA user’s password can be changed once the database registration has finished.
The SYSDBA user is very important, especially when database instances are installed. The
SYSDBA user is responsible for setting up the system and for creating other database users.
The SYSDBA user is the owner of system tables. When system tables are uploaded, the
upload tool logs on to the database instance as SYSDBA.
The SYSDBA is able to define data and database procedures. The SYSDBA can also grant
other users privileges for these database objects.
DBA
DBA users (database administrators) are special database users [Page 136] of the database
user class [Page 28] DBA. They are created by the SYSDBA [Page 151].
A DBA user is authorized to create RESOURCE [Page 146] and STANDARD users [Page
30]. The DBA user can also define data and database procedures and grant other users all or
some DBA user privileges for these database objects.
A DBA user can group users with identical access rights into user groups [Page
A special DBA user is the user DOMAIN [Page
137].
30].
DOMAIN
The DOMAIN user is a special database user [Page 136] of the database user class [Page
28] DBA [Page 137]. The DOMAIN user is the owner of the system tables of the database
catalog [Page 131].
Just like the SYSDBA [Page
instance [Page 132] is installed. The DOMAIN user is created by the system under the pre-
defined name DOMAIN. The password is the same as that of the SYSDBA user that was
created earlier.
The DOMAIN user’s password can be changed at a later date.
151] user, the DOMAIN user is created when a database
User Manual: SAP DB 29
SAP AG November 2002
RESOURCE
RESOURCE users are special database users [Page 136] of the database user class [Page
28] RESOURCE. RESOURCE users can be created by SYSDBA users [Page 151] and DBA
users [Page 137].
RESOURCE users can define data and database procedures and grant other users privileges
for these database objects.
STANDARD
STANDARD users are special database users [Page 136] of the database user class [Page
28] STANDARD. STANDARD users only have access to data and database procedures that
were defined by other users and for which they have privileges.
STANDARD users themselves can define view, synonyms, and temporary tables.
User Groups
Database users [Page 136] can be grouped into user groups.
A user group can either be assigned to the database user class [Page
146] or to the database user class STANDARD [Page 30]. Database users can be defined as
members of a user group.
All database objects defined by members of a certain user group can be identified by the user
group name. The owner of objects such as these is the user group and not the individual user
that defined the objects. If a member of a user group creates objects, each member of that
group can work with these objects as if they were the object owners.
Privileges can only be granted or removed from the user group as a whole and not from
individual members of the group.
28] RESOURCE [Page
The Role Concept
The SAP DB database system supports different roles. A role [See SAP DB Library] is a
grouping of privileges [See SAP DB Library]
136], user groups [Page 30], or other roles.
Procedure
1. A role is created using the CREATE ROLE statement [See SAP DB Library]. This role is
initially empty. Only database users belonging to database user class DBA [Page 137]
are able to create roles. The new role name [See SAP DB Library]
the name of any other role, a user, or a user group.
, which can be assigned to database users [Page
cannot be the same as
2. Privileges are assigned to a role using the GRANT statement [See SAP DB Library]
Privileges are can be revoked from a role using the REVOKE statement [See SAP DB
Library].
3. A role can be assigned to database users, user groups, or other roles using the GRANT
statement and specification of the role name.
User Manual: SAP DB 30
.
Loading...