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
.
SAP AG November 2002
4. You use the ALTER_USER- [See SAP DB Library] and ALTER_USERGROUP-statement
[See SAP DB Library] to define which of the roles that were assigned to a user or user
group is to be used when a database session [Page 135] is opened.
5. During a database session, you can use the SET statement [See SAP DB Library] to
activate other roles assigned to the user or user group
If a role is activated during a session, the current user then has all the privileges that are
assigned to a role.
If a password has been assigned to a role, users assigned to that role can only activate it
by entering the password in the SET statement.
Result
That a role is available and the properties of that role are all registered in the catalog [Page
131] in the form of metadata. A user that creates a role becomes its owner.
The roles assigned to the user or user group as a result of the ALTER USER and ALTERUSERGROUP statements are activated as soon as a database session is opened.
Roles are not active during execution of data definition commands [See SAP DB
Library].
See also:
Reference Manual: SAP DB 7.4, Section Role Name [See SAP DB Library]
User Data as Options
User data and, in some cases, other data can be entered as options when calling the SAP DB
tools, the C/C++ Precompiler, or the application programs.
Syntax
<dbmcli | loadercli | dbmgui | sqlsto | cpc <file_name>> [<options>]
...
<options>
SAP DB Component
DBMCLI DBMCLI options [Page 61]
SAP DB Loader Options (LOADERCLI) [Page 64]
DBMGUI DBMGUI options [Page 60]
SQL Studio SQL Studio options [Page 66]
C/C++ Precompiler/application programs C/C++ Precompiler options [Page 32]
If you enter all the required user data (in most cases, the options -u, -d, -n) and these
entries are complete and correct, you will be logged on to the desired SAP DB tool or the
C/C++ Precompiler.
For each tool, the user data required for logon to a database instance must be entered. For
DBMGUI and SQL Studio, options are not necessarily required, but this data can also be
entered in a logon screen. For other tools, there are required options [Page
always be entered.
The SAP DB tool DBMCLI and the C/C++ Precompiler support the XUSER concept (User
Data Using XUSER [Page 34]).
User Manual: SAP DB 31
34], which must
SAP AG November 2002
Options (C/C++ Precompiler)
C/C++ Precompiler
You can enter the following options when you call up the C/C++ Precompiler:
Option Default
ansi c -E cansi
c++ -E cplus
check nocheck
check syntax
comment -o
compatible -C
datatime eur
datetime iso
datetime jis
datetime usa
dblocale -x <dblocale>
extern -e
help -h
isolation-level -I <isolation_level> -I 10
list -l
margins -m <m_begin>,<m_end> -m 1,132
maxpacketsize -b <max_packet_size> -b 16000
nowarn -w
precom -c
profile -R
program -P <program_name> -P <file_name>
-H nocheck
-H syntax
-D eur
-D iso
-D jis
-D usa
-H check
-D internal
serverdb -d <database_name>
servernode -n <server_node>
silent -s
sqlmode internal
sqlmode ansi
sqlmode ansiora
sqlmode db2
sqlmode oracle
timeout -t <timeout>
trace file
trace long
trace short
unicode -G unicode
user -u <userid>,<password>
userkey -U <user_key>
User Manual: SAP DB 32
-S internal
-S ansi
-S ansiora
-S db2
-S oracle
-F <trace_file>
-X
-T
-S internal
SAP AG November 2002
version -V
32 Bit Support -BIT32
SDK Version -MmCC
M=Major m=Minor
C=Correctionlevel
The following user specifications as options [Page
database user [Page 136] and the name of the database instance [Page 132]
cpc -u smith,geheim -d MK1 testfile
The C/C++ Precompiler is called and the user smith connects to the database
instance MK1 to compile the file testfile.cpc.
See also:
• Description of all options: User Manual Precompiler, section The SAP DB Precompiler →
SAP DB Precompiler Functions → Precompiler Options
• Installation Guide C/C++-Precompiler: SAP DB 7.3, section Installation Guide for
Developers → Calling the C/C++-Precompiler [See SAP DB Library]
64 Bit if available
7401
31] should be transferred:
Application Programs
When an application program is called, you can enter the following options for the runtime
environment:
runtime_options
dblocale -x <dblocale>
isolation-level -I <isolation_level>
no select direct fast -f
profile -R
serverdb -d <database_name>
servernode -n <server_node>
timeout -t <timeout>
trace alt
trace file
trace long
trace no date/time
trace short
trace time
user -u <userid>,<password>
userkey -U <user_key>
See also:
• Description of all options: User Manual Precompiler, section The SAP DB Precompiler →
Functions of the SAP DB Runtime System → Runtime Options
-Y <statement_count>
-F <trace_file>
-X
-N
-T
-L <seconds>
• Installation Guide C/C++-Precompiler: SAP DB 7.3, section Installation Guide for
Developers → Example for Executing a Precompiler Application [See SAP DB Library]
User Manual: SAP DB 33
SAP AG November 2002
Required Options
If you do not enter all the required user data as options [Page 31], or if you were unable to log
on to the database instance [Page
follows:
132] with that user data, the SAP DB tools will react as
• DBMGUI [Page
Entry of options is not required. These tools are started even if options are not entered, or
if options are incorrect or incomplete. DBMGUI and SQL Studio always display a logon
screen. In the logon screen, enter the user data required to log on to the database
instance.
• DBMCLI [Page
Only some of the options required to log on to the database instance were entered, or
they were incorrect. DBMCLI starts and you are connected to the DBM Server. However,
you are not logged on to the database instance. In this instance, the DBMCLI can only be
used to call DBM commands that are not related to the database (for example, help,
dbm_version).
You need to connect to the database instance to use all the DBMCLI functions. Enter the
option -d <database_name> , if the database instance is on the local computer, and
enter the option -n <server_node> as well if the database instance is on a remote
computer.
Further user data (such as user name, ID) can be transferred as options (for example -u)
or, if required, as DBM commands (for example user_logon).
You should always start DBMCLI entering the options -d, -u and, if required, -n
when calling the tool (Options (DBMCLI) [Page
• LOADERCLI [Page
The minimum required option -d <database_name> for logging on to the database
instance and the required option -b <command_file> for entering the command file
were not entered at all, were incomplete, or incorrect. LOADERCLI is not started, but
displays a list of all possible options.
Log on to the SAP DB Loader again by entering the required options.
Further user data (such as user name, ID) can be transferred as options (for example -u)
or, if required, as commands (for example use_user).
134], SQL Studio [Page 65]
133]
61]).
148]
When you call the tool, you should always start the LOADERCLI by entering the
required options -d and -b (Options (LOADERCLI) [Page
64]).
User Data and XUSER
Using the tool XUSER, you can predefine and save user data (using XUSER [Page 35]). You
can access your XUSER data [Page 35] when you call the Database Manager CLI [Page
133] or a precompiler program or when you start an SAP system. In this case, the user would
use the data stored under a certain user key (USER_KEY).
• Windows NT: You can manage individual user data in this way if, for example, more than
one database user is using the same Windows PC and each user is logging on under a
different name.
• UNIX: You can manage individual user data in this way if, for example, more than one
database user is using the same HOME directory and each user is logging on under a
different name.
User Manual: SAP DB 34
SAP AG November 2002
Procedure
1. The database administrator (DBA operator [Page 137]) sets up a database user [Page
136] using the relevant CREATE USER statement.
2. The database administrator informs the operating system user what this database user’s
data is.
3. The operating system user saves the database user’s data using XUSER.
Result
When the Database Manager CLI or the C/C++ Precompiler is now called using the option -U
<user_key>, the system uses the data that was defined in user_key using XUSER. Enter
the user key exactly as it was defined in XUSER, that is, your entries are case-sensitive.
Applications such as SAP DB Loader [Page
and applications that use the ODBC interface (for example, SQL Studio [Page 65]) have no
way of accessing XUSER data.
148] and Database Manager GUI [Page 134]
Using XUSER
User data for setting up a database session [Page 135] can be predefined and saved by
entering a user key (USER_KEY) using the tool XUSER. When the DBMCLI [Page 133] or
the C/C++ Precompiler is called or an SAP system is started, the system can access the
required XUSER data [Page 35] by entering the relevant user key).
XUSER can manage up to 32 combinations of XUSER data per operating system user.
• Windows NT: XUSER data is stored in the database registry.
You are not permitted to make “manual“ changes in the registry because these may result
in inconsistencies in the operating system.
• UNIX: XUSER data is stored in the file .XUSER.62 (also referred to as the XUSER file) in
the user’s HOME directory.
Use
• XUSER data is generated using option -b (Generating XUSER Data in the Background
[Page 36]).
• XUSER data is called using option -U (User Data Using XUSER [Page
34]).
• All XUSER data is deleted by entering the following command:
xuser -b <empty_file>
The empty_file can only contain blanks or must be of length 0.
XUSER Data
Up to 32 combinations of XUSER data can be stored.
Parameter Explanation
User Manual: SAP DB 35
SAP AG November 2002
USERKEY Name of the user key used to address this combination of XUSER data
The first combination of parameters is called DEFAULT. This name cannot be
changed.
If you enter additional key names, these are case-sensitive.
USERID User name
If the user name contains small letters or special characters, it must appear
within double quotation marks. Otherwise small letters will be converted to
capitals.
PASSWORD The user’s password
The password is not visible and must be entered twice for security reasons
(Confirm Password).
If the password contains small letters or special characters, it must appear
within double quotation marks. Otherwise small letters will be converted to
capitals.
SERVERDB The name of the database instance you want to work on
If you do not specify a database, the system uses the name in the
environment variable SERVERDB.
This entry is case-sensitive.
SERVERNODE The name of the server node on which the database you called is running
If you do not specify a server node, the local computer is selected.
This entry is case-sensitive.
SQLMODE This ensures that the SQL dialects of other manufacturers are compatible.
The default value is INTERNAL. Other possible values are ANSI, DB2, and
ORACLE.
This parameter affects precompiler programs.
TIMEOUT The length of time (in seconds) that can pass before the system terminates
an inactive user session.
If you want to use the default setting, enter –1. The default value is already
set in the empty entry screen.
ISOLATION The parameter ISOLATION LEVEL is for locks that affect the user (for
application programs and precompilers only).
If you want to use the default setting, enter –1. The default value is already
set in the empty entry screen.
See also:
Using XUSER [Page
35]
Generating XUSER Data in the Background
XUSER data [Page 35] is generated in the background with the help of a file that you specify
when you call XUSER (Using XUSER [Page 35]).
Syntax
xuser -b <file_name>
You are free to define your own file name. The file is made up of groups of eight lines:
If you use the option -b <file_name>, this will always generate new XUSER data. All
existing XUSER data is overwritten.
User Manual: SAP DB 36
SAP AG November 2002
Setting Up Each Group (Combining Parameters)
The file entries begin in the first column. There are no field descriptions and they contain the
following values in the specified order:
USERKEY
USERID
PASSWORD
SERVERDB
SERVERNODE
SQLMODE
TIMEOUT
ISOLATION
These parameters are explained in more detail in the section XUSER Data [Page
DEFAULT
meier
confidential
db1dial
sqldial
INTERNAL
-1
-1
If you do not want to enter any optional parameters, make sure the line is left blank.
home
meier
"Strictly_Confidential"
db2dial
sqldial
90
1
In the above example, the parameter SQLMODE was left blank.
35].
Security Concepts
• You can achieve a high level of availability [Page 38] of the SAP DB database system by
using fault-tolerant hardware and software.
• The restartability [Page
correction of some errors when you start the database system.
• For backing up the datasets in line with the backup strategy [Page
tools for carrying out data backups [Page 131] and log backups [Page 142].
User Manual: SAP DB 37
38] of the SAP DB database system allows the automatic
39], SAP DB provides
SAP AG November 2002
Availability
To ensure high availability of the database system, bear in mind the security requirements
[Page 75].
See also:
Directory Structure of he Database System for Open Source [Page
Directory Structure of the Database System for SAP Systems [Page 67]
74]
Security Requirements
Using the appropriate hardware, operating system, or database features can improve the
availability [Page 38] of a database instance [Page 132].
• Log volumes [Page
In the production system, the log area [Page 142] should always be mirrored for
security.
Ideally, you should use hardware-based options for this mirroring.
However, you can also use the DUAL log mode [Page 143] of the SAP DB database
system.
You should not use RAID-5 configurations for the log volumes. RAID-5 systems do
not allow full mirroring. Because the database instance writes log entries sequentially,
this can lead to a loss in performance.
In a production system, logging should never be deactivated, even if you use RAID
systems.
See also: Log Concept [Page
144]
43]
• Data volumes [Page
If you wish to ensure a high standard of availability, we recommend using RAID-5 or
RAID-1 configurations for the data area [Page
not affect the running of the database, if the RAID system is able to carry out a
recovery.
Every volume category should be stored on a different disk.
When using fault-tolerant hardware, it is best to only use the same type of hardware when
you want to extend the capacity. For example, RAID-5 systems should only be extended
using RAID-5 systems and mirroring disks with mirroring disks.
UNIX: In a production system, data volumes and log volumes should be used in conjunction
with raw devices. In the event of a system crash, raw devices are extremely secure.
130]
131]. A disk crash and change will then
Restartability
If the database fails (for example, as the result of a power failure), and the volumes [Page
153] were fully functioning, the database system carries out a restart [Page 146]. This means
that the effects that completed transactions had on the data volumes [Page 130] are
reproduced (rolled forward), and the effects that uncompleted transactions would have had
are cancelled out (rolled back).
If a data volume fails (physical disk error), the last, complete data backup [Page 131] must be
imported after the problem has been rectified. However, the last consistent database state
can only be recovered if all the required redo log entries [Page 44] can be imported from the
User Manual: SAP DB 38
SAP AG November 2002
log area [Page 142] or the log backup [Page 142] (if the information in the log area is
insufficient).
Backup Strategy
One key element of the security concept [Page 37] for your database system is the regular
backing up of your data. You should therefore carry out the following backups [Page 126] at
regular intervals: data backups [Page
131] and log backups [Page 142].
You can use external backup tools [Page
138] to reduce the time needed for backup.
Data Backup
Note that, in the case of a recovery, a more up-to-date data backup means that fewer log
entries need to be reproduced. Therefore, carry out data backups as often as possible.
• Carry out a complete data backup [Page
• If you cannot or do not want to carry out a data backup every day, you should at least
carry out an incremental data backup [Page
If complete data backup is active, incremental data backup cannot be started.
You can carry out data backups in parallel to reduce the time required for the backups.
40] every productive day.
41] every productive day.
Log Backups
• Automatic log backup [Page 126]
The automatic log backup (autosave log mechanism) should always be active.
Complete and incremental data backups are also possible while the automatic log
backup is active.
• Interactive log backup [Page
You should save the log entries every productive day.
139]
If automatic log backup is not active, you must regularly check that there is enough
storage space available in the log area [Page
immediately by starting an interactive log backup. If new log entries cannot be written
to the log area because there is not enough storage space, the database stops.
• Archiving of version files [Page
You should regularly archive the version files written during a log backup to a medium
of your choice. Once archiving has been carried out successfully, you can delete the
version files written during the log backup, and thereby ensure that there is enough
space available in the directory for version files.
If you use an external backup tool [Page 138], you can automate the archiving of
version files. To do this, follow the instructions in Archiving Version Files of the Log
Area [See SAP DB Library].
Tapes containing data or log backups should not be directly overwritten with the
next backup. If you retain, say, the last four backup generations, it may be
possible to use an older backup if a media failure occurs.
142]. If necessary, back up the log area
153]
User Manual: SAP DB 39
SAP AG November 2002
Backup
The following backups should be carried out for the SAP DB database system at regular
intervals:
• Data backup [Page
• Log backup [Page
All backups can be carried out on an individual backup medium [Page 150], and data backups
can also be carried out on a group of parallel backup media [Page
The naming convention for a backup medium [Page 127] depends on whether you use an
external backup medium [Page 138].
• Name of standard backup medium [Page
• Name of external backup medium [Page
See also:
Backup Strategy [Page
131]
142]
138].
145]
145]
39]
Data Backup
In a data backup, the contents of the data volume [Page 130] are backed up.
For the SAP DB database system, we differentiate between the following types of data
backup:
• Complete data backup [Page
40]
• Incremental data backup [Page
Full and incremental data backups can be carried out in operating mode [Page 145] ONLINE
or ADMIN.
Data backups are always consistent in themselves. In the case of a recovery, in addition to a
data backup it may be necessary to have the relevant log backups [Page 142] in order to
restore the current state of the database.
Data backups can be carried out in parallel (Parallel Backup [Page
See also:
Backup Strategy [Page 39]
Saving Data Backups [Page
41]
41]
146]).
Complete Data Backup
In a complete data backup [Page 131], all the pages of the data volumes [Page 130] are
backed up to the backup medium [Page 127] you specified.
See also:
Backup Strategy [Page
Saving Data Backups [Page
39]
41]
User Manual: SAP DB 40
SAP AG November 2002
Incremental Data Backup
In an incremental data backup [Page 131], all the pages of the data volumes [Page 130]
changed since the last complete data backup [Page
[Page 127] you specified.
See also:
40] are backed up to the backup medium
Backup Strategy [Page
Saving Data Backups [Page
39]
41]
Parallel Backup
Parallel processing is possible for a data backup [Page 131]. In this case, a group of parallel
backup media [Page 138] must be defined.
See also:
Backup Strategy [Page
Saving Data Backups [Page
39]
41]
Saving Data Backups
For information on how to save data backups [Page 131], refer to the following
documentation:
• Database Manager GUI: SAP DB 7.4, Section Backup Processes [See SAP DB Library]
• Database Manager CLI: SAP DB 7.4, Section Backing Up and Recovering Database
Instances [See SAP DB Library]
• Database Management in CCMS: SAP DB OLTP → DBA Planning Calendar
Log Backup
In a log backup, the contents of the log area [Page 142] are copied to version files [Page 153]
or backed up using an external backup tool [Page 138].
A version file is generated for each log segment of the log area.
The space originally taken up by the backed up log area becomes free again after the log
backup.
Log Backup Types
The following log backup options are supported for the database system SAP DB:
• Automatic log backup [Page
• Interactive log backup [Page
126]
139]
User Manual: SAP DB 41
SAP AG November 2002
Need for log backup
• In the case of a recovery, a data backup [Page 131] alone is not enough to restore the
current state of the database instance [Page 132] up to a certain point in time (for
example, the time just before the disk error occurred).
To restore the current state, both the data backup and the log entries that were written
after the data backup must be imported into the database system.
• If new log entries cannot be written because there is not enough storage space in the
log area, the database stops.
For these reasons, it is necessary to back up the log entries at regular intervals.
See also:
Backup Strategy [Page
Saving Log Backups [Page
39]
43]
Automatic Log Backup
Automatic log backup [Page 142] is recommended to ensure the security of data in production
systems.
If automatic log backup is activated, a log segment is saved as soon as it has been filled. This
log segment is then released again. The advantage of this is that a log area [Page
overflow is almost impossible.
This mechanism is particularly recommended for all database instances [Page
extensive write and change-intensive transactions are carried out. In this way, constant
monitoring of the usage level of the log area is not necessary.
As long as automatic log backup is active, you cannot start an interactive log
backup [Page 139]. However, you can carry out a data backup [Page 131].
You can only carry out automatic log backups in operating mode [Page 145]
ONLINE.
In the case of an automatic log backup, you can only copy the log entries to
version files [Page 153].
142]
132] in which
See also:
Backup Strategy [Page
Saving Log Backups [Page
39]
43]
Interactive Log Backup
You can use the interactive log backup [Page 142] to back up all the pages of the log area
[Page 142] that have been written since the last log backup.
If you back up the log entries in version files [Page 153], a version file is also generated for
the last log segment, which may not be full.
Prerequisites
A full data backup [Page 131] of the current database instance has been created.
User Manual: SAP DB 42
SAP AG November 2002
You can perform interactive log backups in operating mode [Page
or ADMIN.
You cannot perform any other backup while the interactive log backup is running.
See also:
145] ONLINE
Backup Strategy [Page
Saving Log Backups [Page
39]
43]
Saving Log Backups
For information on how to save log backups [Page 142], refer to the following documentation:
• Database Manager GUI: SAP DB 7.4, Section Backup Procedure [See SAP DB Library]
• Database Manager CLI: SAP DB 7.4, Section Backing Up and Recovering Database
Instances [See SAP DB Library]
• Database Management in CCMS: SAP DB OLTP → DBA Planning Calendar
External Backup Tool
Backups [Page 126] can also be carried out using external backup tools on external backup
media.
A detailed description of the use of external backup tools can be found in the following
documentation: External Backup Tools: SAP DB [See SAP DB Library]
.
See also:
External backup medium [Page
Name of external backup medium [Page
External backup ID [Page 137]
138]
145]
Log Concept
One of the main, internal functionalities of the SAP DB database system is the logging.
In normal database operation (operating state [Page
statements of every transaction must be logged, meaning that log entries [Page
[Page 44] and undo log entries [Page 44]) are written. These log entries are required so that
the database system can ensure transaction consistency and so that individual SQL
statements can be reversed if required. In addition, the undo log entries are needed to enable
consistent reading when no locks are set.
The task of online logging [Page
available for normal database operation.
In the case of a restart or recovery [Page
and processed in the correct time-based sequence.
44] is to store the log entries so that they are constantly
49], the required log entries must be made available
145] ONLINE), the modifying SQL
44] (redo
User Manual: SAP DB 43
SAP AG November 2002
Log Entry
The log concept [Page 43] includes the writing of log entries. The SAP DB database system
differentiates between the following log entries:
• Redo log entry [Page
• Undo log entry [Page
44]
44]
Redo Log Entry
Redoing (rolling forward) a transaction [Page 152] means that the modifications of a
successful transaction are repeated, that is to say, the database is set to the consistent state
that it had after the transaction had ended. The redo measure means that additional,
redundant data management is required.
For each transaction, the values of the changed objects are therefore recorded. These logged
values are described as redo log entries [Page
Redo log entries must first be stored permanently in the log area [Page
COMMIT [Page 129] in the transaction (Redo Log Management [Page 45]).
The redo log entries are also required for the redo when the database system is restarted or
recovered [Page 49].
44] (or after-image entries).
142] using the
Undo Log Entry
Undoing (rolling back) a transaction [Page 152] means that the transaction is reset, that is to
say, the database is set to the consistent state that it had before the transaction was started.
The undo measure means that additional, redundant data management is required.
Therefore, for each transaction, the original values of the data objects that are to be changed
by the transaction, that is, the values that were available before the transaction was started,
are stored. These logged values are described as undo log entries [Page 44] (or beforeimage entries).
Undo log entries are permanently stored in the stored in the data area [Page
SQL statement is executed (Undo Log Management [Page 47]). Each transaction can access
its undo log entries without any locks being set.
In addition, the undo log entries are needed for the undo if the database is restarted or
recovered [Page 49] and are used for History Management [Page 48].
131] before an
Online Logging
The log concept [Page 43] for the SAP DB database system includes online logging, which is
required for normal database operation.
User Manual: SAP DB 44
SAP AG November 2002
• The redo log entries [Page 44] of the transactions are managed: Redo Log Management
[Page 45]
• The undo log entries [Page
[Page 47]
• For the database instance type liveCache [Page
carried out for the undo log entries.
44] of the transactions are managed: Undo Log Management
140], History Management [Page 48] is
Redo Log Management
During online logging [Page 44], the redo log entries [Page 44] are written and managed.
• Active components: transactions [Page
• Storage units: log queue [Page
45], log area [Page 142]
Use
Redo log management plays a part in the following actions:
• During execution of a modifying transaction, the redo log entries are included in the log
queue. The log writer writes the log pages from the log queue to the log area.
• During a log backup [Page
area are to be backed up, and if so, how many.
142], information is obtained on whether entries from the log
152] in user tasks [Page 16], log writer [Page 46]
• For display on the monitor, information is obtained on waiting periods, and the type of
access to the entries in the log queue.
• In the case of a restart or recovery [Page
the log area.
• The database parameters and other internal information that controls logging are stored
in the log area and updated at regular intervals.
49], the required redo log entries are read from
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
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
84].
Process Flow
4. The user task [Page 16] of the transaction reserves main memory space for a redo log
entry in the log queue.
46]) is determined by the database
142] is carried out by the log writer [Page 46].
5. 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).
6. 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
User Manual: SAP DB 45
SAP AG November 2002
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,
and informs the transaction of the log sequence number that was assigned when the log
page was written to the log area.
Log Page
The main storage units required for redo log management [Page 45] are the log pages. The
size of the log pages is 8 KB. A fixed number of log pages defined in the database parameter
LOG_IO_QUEUE [Page
84] forms the log queue [Page 45].
• A transaction [Page
queues.
Each log page is assigned a log queue sequence number, which specifies the time at
which the redo log entries are written to the log queue.
• If a log page is to be written to the log area [Page
The log writer fetches the log pages in question from the log queue, and writes them to
the log area.
When each log page is written from the log queue to the log area, the log writer assigns it
a log sequence number and a time stamp. These log sequence numbers can then be
used to determine the unique sequence of the log pages in the log area. The time stamp
is needed for a point-in-time recovery.
A log page that was written by the log writer, but was not full, remains in the log queue and
can be filled with further redo log entries.
152] writes redo log entries [Page 44] to the log pages of the log
142], the log writer [Page 46] is notified.
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
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].
14]. When the database system is started,
• 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
User Manual: SAP DB 46
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.
SAP AG November 2002
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.
Log Area
The volume storage area needed for redo log management [Page 45] is called the log area. A
log area can extend across several log volumes [Page
The log area is managed by the log writer [Page 46]. The log writer fills the log area with log
pages [Page 46] from the log queue [Page 45].
For security, the log area should always be mirrored. If possible, you should
mirror the log area on a hardware basis.
If hardware-based mirroring is not possible, you can use the log mode [Page
DUAL.
144].
143]
Ensure that writing of the log entries is always active in a productive database
system.
A full log area leads to a database downtime. Therefore, you must always carry out log
backups [Page 142] in good time. For this purpose, make sure you activate automatic log
backup [Page 126].
In log backups, the log area is not saved as a whole. Instead, it is saved in backup units,
called log segments. The size of a log segment is defined by the parameter
LOG_SEGMENT_SIZE [Page
84].
Undo Log Management
During online logging [Page 44], the undo log entries [Page 44] are managed. The modifying
transactions [Page 152] store the undo log entries in undo log files [Page 48].
Use
Undo log management plays a part in the following actions:
• The database can create a transaction-consistent database state that reflects the state of
the database system at the time of the last savepoint [Page
undo log files is sufficient for this.
149]. The information in the
• In a restart or recovery [Page
• For the database instance type liveCache [Page
carried out for the undo log entries.
User Manual: SAP DB 47
49], the required undo log entries are determined.
140], History Management [Page 48] is
SAP AG November 2002
Undo Log File
During undo log management [Page 47], every modifying transactions [Page 152] creates its
own undo log file, in which the undo log entries [Page
is assigned an undo log sequence number starting with 0.
Undo log files are internal database storage structures, which are stored in the data area
[Page 131].
Storage of the undo log files in the data area means that the information in the data area can
be used to create a transaction-consistent database state that reflects the state of the
database system at the time of the last savepoint [Page 149].
Delete Undo Log Files
• SAP DB OLTP [Page 149]: At the end of a transaction, the transaction itself deletes the
undo log files.
44] are written. Every undo log entries
• liveCache [Page
history management [Page
reading. The undo log files are deleted at a later point in time by the garbage collector
[Page 49].
In exceptional circumstances, storage of the undo log files in the data area can lead to a full
data area. In this case, increase the size of the data area.
140]: At the end of the transaction, the undo log files are transferred to
48], because they are needed for unlocked, consistent
History Management
History management, as a task during online logging [Page 44], is currently only used for the
database instance liveCache [Page 140].
During history management, all information that is needed by the garbage collectors [Page
49] is made available, so that the deleted objects can be completely removed, that is to say,
so that the undo log files [Page 48] can be deleted. In particular, information from the history
files [Page 48] is written to the history list [Page 49].
History File
History files are used during history management [Page 48]. A history file is an internal
database file, which contains the access and statistical information about the undo log files
[Page 48] of transactions [Page 152] that have ended.
When a database instance is created, the initial history file is stored in the data area [Page
131]. The number of history files is defined by the parameter MAXUSERTASKS. Once a
transaction has ended, it writes an entry to its history file. This entry identifies the undo log file
that is assigned to the transaction. With each savepoint [Page 149], the history file is saved to
the data area.
The history list [Page
49].
User Manual: SAP DB 48
49] is used to assign the history files to the garbage collectors [Page
SAP AG November 2002
History List
The main storage area required for history management [Page 48] is the history list.
The history list is an internal database list of all history files [Page 48]. The history list is used
to assign a suitable history file to the garbage collectors [Page 49] for processing.
The history list is written to the data area [Page
case of a restart [Page 146], the history list is rebuilt on the basis of the data stored in the
data area.
131] on each savepoint [Page 149]. In the
Garbage Collector
The garbage collectors are active components during history management [Page 48].
Garbage collectors are user kernel threads (UKT) [Page 14]. When the database system is
started, they are initialized using permanently stored, internal configuration information.
During history management, the history list [Page 49] is used to determine the history file
[Page 48] that is to be processed by the garbage collector. In the undo log files [Page 48] that
were determined using the history files, the system searches for delete operations for objects.
If it finds a delete operation, the garbage collector deletes the object.
Restart or Recovery
The log concept [Page 43] for the SAP DB database system includes the import of the
required log entries [Page
The redo log manager [Page 49] ensures that the required log entries are made available and
processed in the correct, time-based sequence.
• For each transaction [Page
the log reader [Page
• Transactions for which a redo is actually required are stored by the log reader in a redo
list [Page 51].
• The redo tasks [Page
log entries [Page 44] for each transaction they find.
See also:
Example: Restart [Page
44], if the database instance is restarted [Page 146] or recovered.
152], the available redo log entries [Page 44] are stored by
50] in a redo log file [Page 50].
51] evaluate the redo list, and import the required redo and undo
52]
Redo Log Manager
The redo log manager is the component that manages all the resources required for actions
during a restart or recovery [Page 49].
Actions
• The redo log manager starts the log reader [Page 50] and several redo tasks [Page 51].
User Manual: SAP DB 49
SAP AG November 2002
• The redo log manager can cancel any of the tasks it starts.
At the end of an action, the redo log manager collects all error messages, and may then
terminate the restart [Page
146] or recovery, if required.
• The redo log manager generates the redo list [Page
51]
Log Reader
The log reader is an active component during a restart or recovery [Page 49]. The log reader
is a server task [Page 16].
Actions
From the data that was stored at the time of the last savepoint [Page 149], the log reader
determines the transaction file [Page
transaction list [Page 152].
The log reader determines the required information from redo log management [Page 45] or
from the backup manager, so that all the redo log entries [Page
savepoint can be made available.
• If transactions [Page
savepoint, the log reader enters these transactions in the redo list [Page 51] immediately.
• The log reader creates a redo log file [Page
redo log entries.
• COMMIT transactions
If a COMMIT [Page
transaction in the redo list.
152] in the transaction list are marked as ended at the time of the
129] is found for the whole transaction, the log reader enters the
152], and uses this transaction file to re-generate the
44] written since the last
50] for each further transaction found in the
• ROLLBACK transactions
If a ROLLBACK [Page
transaction in the redo list.
• ROLLBACK in subtransactions
If a ROLLBACK is found within a transaction, the log reader removes the corresponding
log entries from the redo log file. This means that these log entries are not processed
unnecessarily.
• Uncompleted transactions
If uncompleted transactions are found after all redo log entries are read, the log reader
flags these transactions with a ROLLBACK and enters them in the redo list. These
transactions are then treated as ROLLBACK transactions.
For information on the further processing of the transactions, see Redo Tasks [Page
See also:
Example: Restart [Page
147] is found for the whole transaction, the log reader enters the
52]
Redo Log File
When a restart or recovery [Page 49] is carried out, a redo log file is created for each
transaction [Page
database storage structures, which are stored in the data area [Page
152] found in the redo log entries [Page 44]. Redo log files are internal
131].
51].
The log reader [Page 50] copies exactly those redo log entries that belong to a transaction to
each redo log file.
User Manual: SAP DB 50
SAP AG November 2002
Redo List
The redo list is an internal database list of transactions [Page 152], which is in the main
memory. The redo list is created by the redo log manager [Page
If, during a restart or recovery [Page 49], the log reader [Page 50] finds a COMMIT [Page
129] in a redo log file [Page 50] for the whole transaction, this transaction is entered in the
redo list.
49].
The redo tasks [Page
repeated, and the sequence in which they are to be executed. The sequence for executing
the transactions, defined in the redo list, is important to ensure that the same data is not
changed simultaneously.
51] use the redo list to determine the transactions that need to be
Redo Task
Redo tasks are active components during a restart or recovery [Page 49]. Redo tasks are
server tasks [Page
Actions
COMMIT transactions
1. In the redo list [Page 51], the redo tasks look for transactions [Page 152] that were
completed with a COMMIT [Page 129], and are therefore ready to be re-imported. In the
redo list, the transactions are sorted by the log sequence numbers of their COMMIT.
2. The redo task starts by processing the transaction with the lowest COMMIT log sequence
number.
The redo log files [Page 50] that belong to this transaction are processed in sequence.
If a redo log entry [Page 44] of this redo log file has a higher log sequence number than
the lowest COMMIT log sequence number, it cannot be processed. In this case, the redo
task waits for the COMMIT of the other transaction. Only then can the redo task continue
processing the redo log file.
This mechanism ensures consistent data.
16].
3. Once a COMMIT transaction has been processed in full, the redo task deletes the redo
log file and the undo log file [Page
the redo list.
48] of the transaction. The transaction is removed from
ROLLBACK transactions
1. In the redo list, the redo tasks look for transactions that were completed with a
ROLLBACK [Page 147].
2. The redo tasks delete the relevant redo log files. This prevents the transaction being
redone unnecessarily.
3. If the ROLLBACK transaction started before the savepoint, the redo task evaluates the
undo log file of the transaction, in order to set the transaction to the state before the
savepoint. Once the ROLLBACK transaction has been fully processed, the redo task
deletes the undo log file of the transaction.
If the ROLLBACK transaction started after the savepoint [Page 149], the redo task does
not process the transaction further.
See also:
Example: Restart [Page
User Manual: SAP DB 51
52]
SAP AG November 2002
Savepoint on Restart
Savepoints [Page 149] are also written during a restart [Page 146] (redo of a redo).
In this case, the items of the log reader [Page
are saved to the data area [Page 131], and reused for the next restart.
In this way, a terminated restart can be started again.
50] and the state of all open redo transactions
Example: Restart
The database crashed.
For the subsequent restart [Page
time of the last savepoint [Page 149].
146], the database instance is recovered starting from the
No recovery measures are required for transactions T1 and T5. The changes for transaction
T1 were recorded in full by the last savepoint. The changes for T5 were rolled back before the
crash, and are not yet stored in the data area.
For transactions T4 and T6, the log reader [Page
relevant entries in the redo list [Page
log entries for these transactions.
No recovery measures are required for transactions T2 and T3. However, the modifications
made before the savepoint must be reversed. The redo tasks therefore evaluate the relevant
undo log files [Page
User Manual: SAP DB 52
48].
51]. The redo tasks [Page 51] import the required redo
50] creates redo log files [Page 50] and
SAP AG November 2002
Database Tools
The SAP DB database system offers a series of tools for working with the database
instances.
• Architecture of the SAP DB Tools [Page
• The following SAP DB tools are described in more detail:
Database Manager [Page
SAP DB Loader [Page 148]
SQL Studio [Page 65]
133]
53]
Architecture of the SAP DB Tools
The SAP DB tools Database Manager [Page 133] and SAP DB Loader [Page 148] each
consist of a server part and a client part. The server part is responsible for the functions, and
the user uses the client to access the tool.
The SAP DB tool SQL Studio [Page
database instance [Page 132] through ODBC.
Client/Server for the SAP DB Tools
Client Server
Database Manager DBMGUI, DBMCLI
Web DBM
Script interface (for example to Perl or
Python) available
65] has a graphical user interface and accesses the
DBM Server [Page
58]
SAP DB Loader LOADERCLI
Script interface (for example to Perl or
Python) available
SQL Studio SQL Studio
Web SQL
Prerequisites
Client Windows NT/Windows 2000 UNIX
DBMCLI
LOADERCLI
DBMGUI The X server must be active as a
Web DBM
Web SQL
The X server [Page 156] must be
active as a service on the database
server.
service on the database server.
The Web services must be installed
together with the Web Server [Page
156] on one computer.
The X server must be running as a
background process on the database
server.
The X server must be running as a
background process on the database
server.
The Database Manager GUI is installed
on a Windows server. The database
instance on the UNIX server is
administered remotely.
The Web services must be installed
together with the Web Server on one
computer.
Loader [Page
Database Instance
59]
User Manual: SAP DB 53
SAP AG November 2002
Architecture
• Architecture of the Database Manager [Page 54]
• Architecture of the SAP DB Loader [Page
• SQL Studio Architecture [Page
• Architecture of the SAP DB Web Tools [Page
56]
55]
57]
Architecture of the Database Manager
The Database Manager [Page 133] has a client/server architecture.
• Clients: DBMGUI, DBMCLI, WEB DBM, clients via Perl, Python and JAVA interfaces
• Server: DBM Server [Page
The client of the Database Manager (for example, DBMGUI) is remote-enabled, which means
that the client and DBM server can be installed on different computers. The database
instance must always be installed on the computer that the DBM server is installed on.
The Database Manager client is on one computer, and the DBM server and the
database instance are on another computer.
58]
User Manual: SAP DB 54
SAP AG November 2002
Database Manager
(DBMGUI,
DBMCLI)
Client comp uter
Server computer
X Ser ve r
DBM Server
Kernel
Database
Explanation
At the request of the client (Database Manager GUI or Database Manager CLI), the X server
[Page 156] starts the DBM server. Once a connection has been successfully established, an
additional X server instance is started, which is needed for transporting the data packages
across the network.
See also:
Architecture of the SAP DB Web Tools [Page
Architecture of the SAP DB Tools [Page
57]
53]
Architecture of the SAP DB Loader
The SAP DB Loader [Page 148] has a client/server architecture.
• Clients: LOADERCLI, clients via Perl, Python and JAVA interfaces
• Server: Loader [Page
The SAP DB Loader is remote-enabled, which means that the SAP DB Loader and database
instance can be installed on different computers.
User Manual: SAP DB 55
59].
SAP AG November 2002
The client of the SAP DB Loader (for example, LOADERCLI) is remote-enabled, which means
that the client and Loader can be installed on different computers. It is important that the
media [See SAP DB Library] (for example files) of the SAP DB Loader must always be on the
computer that the Loader is installed on.
In practice, the following configuration is frequently used:
All SAP DB Loader components are on one computer, and the database instance
is on another computer.
Explanation
1. At the request of the clients (for example, LOADERCLI), the Loader is started directly,
without the X server [Page
2. Communication between the Loader and the database kernel is established via the X
server.
See also:
Architecture of the SAP DB Tools [Page
156] being called.
53]
SQL Studio Architecture
At the request of the client, SQL Studio [Page 150] GUI, a connection to the database
instance [Page 132] is created using the ODBC interface. After this, communication can take
place between the client and the database instance.
A connection to the client (Web SQL [Page 154] service integrated into the Web server [Page
156] is created by calling the Web SQL in the Web Browser. The communication between this
User Manual: SAP DB 56
SAP AG November 2002
client and the database instance takes places through the ODBC interface (see also
Architecture of the SAP DB Web Tools [Page 57]).
SQL Studio (Windows)
GUI (VB,C++)
SQL Studio and Web SQL
ODBC
SAP DB
(Linux, UNIX,
Win NT/2000)
See also:
Architecture of the SAP DB Tools [Page
Web SQL (HTML)
Web Browser
Web Server
Web SQL Service
53]
Architecture of the SAP DB Web Tools
One example of the architecture of the SAP DB tools [Page 53] is the architecture of the SAP
DB Web tools. The SAP DB Web tools are implemented as a Web service and can be used
with our own SAP DB Web Server as well as with the well-known Apache Web server [Page
156].
The Web services are installed together with the Web server on one computer. The database
system can be on a different computer or on the same computer as the Web server.
User Manual: SAP DB 57
SAP AG November 2002
WWW
HTTP
SAP DB WWW
Web Serve r
Web Tools
SAP DB
Server A
Server B
WWW
HTTP
SAP DB WWW
Web Serve r
Web Tools
SAP DB
Server A
A connection to the client (Web DBM or Web SQL service integrated into the Web Server) is
created by calling the SAP DB Web tool (Web DBM [Page
154] or Web SQL [Page 154]) in
the Web Browser. The communication between the client and the database instance [Page
132] takes place through the DBM Server [Page 58] for the Web DBM tool, and through the
ODBC interface for the Web SQL tool (SQL Studio Architecture [Page 56]).
X Server
The X server (Remote SQL Server) is the communication instance of the SAP DB software
when the individual components are located on different computers.
• Database applications and servers of the SAP DB tools (for example, Loader) address
the X server when they want to establish a connection with a database instance installed
on another computer.
• Clients of the SAP DB tools (for example, Loader) address the X server when they want
to establish a connection with a SAP DB tool server (for example, DBM Server, Loader)
installed on a different computer.
Each time a connection is successfully established, an additional X server instance is started
(Windows: Thread, UNIX: Process). This X server instance is needed to transport the data
packages across the network. When the connection ends, the X server instance is closed.
See also:
Architecture of the Database Manager [Page
54]
Architecture of the SAP DB Loader [Page 55]
DBM Server
The Database Manager Server (DBM Server) is the server part of the Database Manager
[Page 133]. It is installed by the server installation on the database server.
The DBM Server creates the connection to the database instance [Page 132] and can access
its environment using operating system resources.
User Manual: SAP DB 58
SAP AG November 2002
Client applications, such as the Database Manager GUI [Page 134], the Database Manager
CLI [Page 133], or Web DBM, the program integrated into the Web Server [Page 156] create
a connection to the DBM Server and exchange data with the DBM Server using a RequestResponse mechanism.
See also:
Architecture of the Database Manager [Page
54]
Loader
The Loader is the server part of the SAP DB Loader [Page 148]. The Loader must be installed
on the computer that the media [See SAP DB Library]
The Loader creates the connection to the database instance [Page
environment using operating system resources.
The Loader can communicate remotely with the database instance and with the client.
See also:
Architecture of the SAP DB Loader [Page
SAP DB Loader: SAP DB 7.4 [See SAP DB Library]
55]
are located on.
132] and can access its
Web Server
The Web Server is the client part of the SAP DB Web tools Web DBM [Page 154] and Web
SQL [Page 154]. The server for the Web DBM tool is the DBM Server [Page 58], the server
for the Web SQL tool is the database instance [Page 132].
Possible Web servers are the SAP DB Web Server or the well-known Web server Apache.
The SAP DB Web server is installed during the installation of the SAP DB Web tools. Apache
must be installed separately or configured. For more information, see the Installation Guide
Web Tools: SAP DB 7.4 [See SAP DB Library].
Web DBM and Web SQL are operating-system-independent. These tools are installed once,
and can then be called from any browser. This enables occasional users as well as frequent
users to make use of the Database Manager and SQL Studio functionalities quickly and easily
in the network.
See also:
Architecture of the SAP DB Web Tools [Page
57]
Database Manager
The Database Manager is a database tool [Page 136] for managing SAP DB database.
The tasks of the Database Manager comprise creating, controlling, and monitoring database
instances [Page 132] on the local computer or on remote computers. You can use the
Database Manager to carry out backups and, if necessary, recoveries.
Architecture
The Database Manager consists of a server part and a client part. The same functionality is
offered for the Database Manager regardless of which client you use.
User Manual: SAP DB 59
SAP AG November 2002
Server/Client for the Database Manager
Server Client
DBM Server [Page 58]
See also:
Architecture of the Database Manager [Page
Architecture of the SAP DB Web Tools [Page 57]
DBMGUI [Page 134]
DBMCLI [Page
Web DBM [Page 154]
A script interface is available.
133]
54]
Database Manager GUI
The Database Manager [Page 133] has a user-friendly graphical user interface, the Database
Manager GUI (DBMGUI). If you want to use the Database Manager to monitor several SAP
DB database instances, which may be on different computers, you should use the Database
Manager GUI.
The Database Manager GUI can only be used on Windows operating systems. If you want to
use the functionality of the Database Manager on other operating system platforms, you must
use the Database Manager CLI [Page 133] or Web DBM [Page 154].
Call
You have the following options for calling the Database Manager GUI:
• Choose Start → Programs → SAP DB → Database Manager.
• You can start the Database Manager GUI from the command line. In this case, you can
transfer options [Page
See also: Database Manager GUI: SAP DB 7.4 [See SAP DB Library]
60] to the Database Manager program.
Options (DBMGUI)
When you call the Database Manager GUI [Page 134] at command line level, you can specify
options (Database Manager GUI: SAP DB 7.4 → Starting the DBMGUI [See SAP DB
Library]). If you do not enter any options, you can make the entries needed for logging on to
the database instance on the logon screen.
The following user specifications as options [Page
DBM operator [Page
dbmgui -u dbmmann,secret -d MK1
The Database Manager is called and a database session is created for the DBM
operator dbmmann, password secret with the registered database instance MK1.
134] and the name of the database instance [Page 132]
31] should be transferred: the
User Manual: SAP DB 60
SAP AG November 2002
Database Manager CLI
The Database Manager [Page 133] has a command-line oriented client, the Database
Manager CLI (DBMCLI). The Database Manager CLI is operating-system-independent.
You can use the Database Manager CLI to carry out all Database Manager actions. You can
also use the Database Manager CLI to schedule these actions in the background. You should
use this option to automate Database Manager actions that have to be carried out regularly.
If you do not have a Windows operating system, you can only use Database Manager clients
Database Manager CLI and Web DBM [Page
Call
dbmcli [<options>] [<command>]
154] to manage database instances.
You can transfer options [Page
command line to the Database Manager CLI.
For a link to be established with a database instance on the local computer, you must enter at
least the name of the database instance (option -d <database_name>) when calling the
Database Manager CLI. If the required database instance is on a remote computer, you must
also enter this computer name (option -n <server_node>).
You can open an interactive Database Manager CLI session if you do not enter any DBM
commands other than the required options. You can then enter the required DBM commands
interactively.
You can write the required DBM commands to a separate file <file_name>. In this case,
when you call the Database Manager CLI, you can enter option -i <file_name> in addition
to the required options.
See also:
Database Manager CLI: SAP DB 7.3 [See SAP DB Library]
Manager CLI [See SAP DB Library]
61] and a maximum of one DBM command [Page 62] in a
→ Functions of the Database
Options (DBMCLI)
A series of options can be transferred to the Database Manager CLI [Page 133] (Database
Manager CLI: SAP DB 7.4, Section Options [See SAP DB Library]).
For a link to be established with the database instance on the local computer, you must enter
at least option -d <database_name>. If you do not enter a DBM command [Page
command line when calling the Database Manager, an interactive Database Manager CLI
session is opened.
The following user data as options [Page
operator [Page 134] and the name of the database instance [Page 132] on the
local computer:
dbmcli -u dbmmann,secret -d MK1
The Database Manager is called and a database session is created for the DBM
operator dbmmann, password secret with the registered database instance MK1.
You can then enter the required DBM commands interactively.
User Manual: SAP DB 61
31] should be transferred: DBM
62] in the
SAP AG November 2002
The name of the database instance on the local computer should be transferred
as an option. The database instance should be started.
dbmcli -d MK1
user_logon dbmmann,secret
db_online
exit
The Database Manager is called and an interactive database session is created
with registered database instance MK1. Details on the DBM operator dbmmann,
password secret are made via the interactively entered DBM command
user_logon. Database instance MK1 is started with DBM command
db_online.
The name of the database instance on the local computer should be transferred
as an option. The database instance should be started. This action should be
carried out in the background.
Create file startMK1 with the following contents:
user_logon dbmann,secret
db_online
exit
Execute the command in the background
dbmcli -d MK1 -i startMK1
The Database Manager is called and an interactive database session is created
with registered database instance MK1. File startMK1 is processed.
DBM Commands
Commands can be transferred to the Database Manager CLI [Page 133] (Database Manager
CLI: SAP DB 7.4, Section DBM Commands [See SAP DB Library]).
Syntax
<command_name> [<parameters>]
A DBM command is always made up of the command name and optional parameters
affecting its execution.
dbmcli -u dbmmann,secret -d MK1 param_getfull cache_size
The Database Manager is called and a database session is created for the DBM
operator dbmmann, password secret with the registered database instance MK1.
The DBM command param_getfull is used to request all data for database
parameter CACHE_SIZE [Page
81].
Web DBM
The Database Manager [Page 133] has a web-based client, the Web DBM. Install the Web
DBM once in the network. The Web DBM can then be called from all browsers.
User Manual: SAP DB 62
SAP AG November 2002
If you do not have a Windows operating system, you can only use Database Manager clients
Database Manager CLI [Page 133] and Web DBM to monitor database instances.
Web DBM provides you with basically the same functionality as the
[Page 134]
See also:
Architecture of the SAP DB Web Tools [Page
. Operation depends on the Web-based application.
57]
Database Manager GUI
Differences between Web DBM and Database Manager GUI
With the Database Manager GUI, you can monitor several database instances
simultaneously. With the Web DBM, you can only monitor one database instance at a time.
When you log on, you enter the database instance that you want to monitor. If you want to
switch to another database instance, you must log off, and log on again entering the new
database instance.
It is possible to open several browsers simultaneously. In this way, you can monitor a different
database instance in each browser with the Web DBM.
In contrast to the Database Manager GUI, a timeout may occur when working with the Web
DBM. This results in the connection to the database instance being broken. If this happens,
you must log on again.
Set-up of the Web DBM
All administrative functions are executed in the window of the Web DBM. The functions are
divided up into the same groups as in the Database Manager GUI. The colored symbols
(green dots and red squares) indicate whether an administrative function can be used in the
database instance in its present state. There is no menu bar or toolbar of the kind found in the
Database Manager GUI.
Instead of a list of database instances registered in the Database Manager GUI, the Web
DBM displays the detailed status of the database instance. The most important values
displayed in the status have a link that takes you to the relevant function. The functions for
starting and stopping the database instance are at the bottom of the status display.
The work area is located below the status display. The subsequent web pages corresponding
to the selected administrative function are displayed here.
The structure of these web page is similar to the corresponding screens in the Database
Manager GUI. The bottom of these pages often contains a toolbar with the available
administrative functions. If a new administrative function is selected in the Web DBM, and in a
new page displayed in the work area as a result of this, the previous display and status is lost.
To return to the previous display, you must reselect the relevant administrative function.
The header line contains a logoff link that enables you to end the current connection with a
database instance.
SAP DB Loader
The SAP DB Loader is a database tool [Page 136] for loading and unloading data.
In addition to processing Loader commands [Page 64], the SAP DB Loader can also execute
all SQL statements [See SAP DB Library]
See also:
.
SAP DB Loader: SAP DB 7.4 [See SAP DB Library]
Architecture
The SAP DB Loader consists of a server part and a client part.
User Manual: SAP DB 63
SAP AG November 2002
Server/Client for the SAP DB Loader
Server Client
Loader
[Page 59]
If you want to react to SAP DB Loader return codes, you must use the script interface.
See also: Architecture of the SAP DB Loader [Page 55]
LOADERCLI
A script interface (for example to Perl and Python) is available
Calling with LOADERCLI
loadercli [<options>] –b <command_file>
When you call the SAP DB Loader with the LOADERCLI [See SAP DB Library]
specify options [Page
statements must be stored in a command file [See SAP DB Library]
which you specify using option -b <command_file>.
For a link to be established with a database instance on the local computer, you must enter at
least the name of the database instance (option -d <database_name>) when calling the
Loader.
When creating the connection between the Loader and the database instance [Page
Loader first uses the specified options. The commands and SQL statements of the command
file are then processed in the specified sequence.
See also:
Calling with Loader Perl Script [See SAP DB Library]
Calling with Loader Python Script [See SAP DB Library]
64], commands, and SQL statements. The commands and SQL
(<command_file>),
, you can
132], the
Options (LOADERCLI)
When you call the SAP DB Loader [Page 148] with the SAP DB Loader CLI (LOADERCLI),
you can transfer a number of options to the SAP DB Loader. For a list of all possible options,
see SAP DB Loader: SAP DB 7.4, Section Options [See SAP DB Library]
For a link to be established with the database instance on the local computer, you must enter
at least option -d <database_name>. In addition, the name of a command file [See SAP
DB Library] must be specified in option -b <command_file>.
The following user data as options [Page
user [Page 136] and the name of the database instance [Page 132]
loadercli -u samplename,secret -d TST -b command.dat
The Loader is called and a database session is created for the database user
samplename, password secret, with the database instance TST. All further
commands statements can be found in command file command.dat.
31] should be transferred: database
.
Loader Commands
A series of commands and SQL statements [See SAP DB Library] can be transferred to the
SAP DB Loader [Page 148]. For a list of all possible commands, see SAP DB Loader: SAP
DB 7.4, Section Commands [See SAP DB Library].
User Manual: SAP DB 64
SAP AG November 2002
If you use the SAP DB Loader CLI (LOADERCLI) to call the Loader, these must be
transferred in a command file [See SAP DB Library]
DB Library]).
<command_file> (Option -b [See SAP
SQL Studio: Introduction
The SQL Studio is a database tool [Page 136] that enables easy access to application data
[Page 125] and the database catalog [Page 131] of an SAP DB database instance.
You can create, execute, and manage any number of SQL statements. You can create,
display, or change database catalog objects.
Architecture
The client part of the SQL Studio creates a connection to the database instance [Page 132]
using the ODBC interface. The same functionality is offered for the SQL Studio regardless of
which client you use.
Server/Clients for SQL Studio
Server Client
Database Instance SQL Studio [Page 150]
Web SQL [Page
See also:
SQL Studio Architecture [Page
Architecture of the SAP DB Web Tools [Page 57]
56]
154]
SQL Studio
The SQL Studio (SQL Studio: Introduction [Page 65]) has a user-friendly, graphical user
interface.
The SQL Studio in the form described here can only be used on Windows operating systems.
If you want to use the functionality of the SQL Studio on other operating system platforms,
you must use Web SQL [Page 154].
Prerequisite
Check if the database instance is started.
Call
You have the following options for calling the graphical user interface of the SQL Studio:
• Choose Start → Programs → SAP DB → SQL Studio. Log on to the desired database
instance.
• You can start SQL Studio from the command line. In this case, you can transfer options
[Page 66] to the SQLSTO program.
See also:
SQL Studio: SAP DB 7.3 [See SAP DB Library]
User Manual: SAP DB 65
→ Starting SQL Studio [See SAP DB Library]
SAP AG November 2002
Options (SQL Studio)
You can start SQL Studio [Page 65] from the command line. Execute the program SQLSTO
with options if necessary (SQL Studio: SAP DB 7.4 → Starting SQL Studio [See SAP DB
Library]).
Procedure
1. Change to the directory, in which the program sqlsto.exe is stored.
2. Enter the following command:
sqlsto [<options>]
The following user data as options [Page
operator [Page 136] and the name of the database instance [Page 132] on the
local computer:
sqlsto -u samplename,secret -d MK1
SQL Studio is called and a database session is created for the database user
samplename, password secret, with the database instance MK1.
31] should be transferred: database
Web SQL
The Web SQL Studio is a Web-based client that enables easy access to user data [Page 125]
and the database catalog [Page 131] of an SAP DB database instance (SQL Studio:
Introduction [Page 65]).
Install the Web SQL once in the network. The Web SQL can then be called from all browsers.
If you do not have a Windows operating system, you can only use Web SQL to send requests
to the database instance.
Web SQL provides you with basically the same functionality as the SQL Studio [Page 150].
Operation depends on the Web-based application.
See also:
Architecture of the SAP DB Web Tools [Page
Prerequisites
Check if the database instance is started.
Call
Enter the following address in the browser:
http://<web_server>:<port>/websql
57]
See Web Tools Installation Guide: SAP DB 7.4 Section Using the SAP DB Web Tools [See
SAP DB Library]
Enter the name of the database computer, the name of the database instance, the user
name, and the user password.
Set-up of the Web SQL
Header
The header contains a link for logging off, among other things.
User Manual: SAP DB 66
SAP AG November 2002
Stored SQL Studio Objects
Web SQL the same data as SQL Studio. The folders and SQL Studio objects stored in SQL
Studio are visible in Web SQL. One exception is the SQL Studio 'Local Folder' for storing
local SQL Studio objects.
Objects
SQL Dialog objects can be read and written. In the case of the objects Form Dialog, Visual
Query and Result Tables, the underlying SQL statement is displayed, but the objects cannot
be changed. Web SQL can create, delete, move, and rename folders and SQL Dialog objects
itself.
SQL Dialog window
You can enter and execute SQL statements in this window. You can display SQL statements
from all SQL Studio objects, and you can also store SQL Dialog objects. You can store newly
created SQL statements.
Additional functions
Before executing each SQL statement, you can set the AutoCommit mode, the SQL mode,
and the isolation level. You can display previously executed SQL statements using Previous
and Next. A button is available for clearing the SQL window. You can execute several SQL
statements. You must separate these from each other using a line with // or -- .
Result window
If SQL mode INTERNAL was selected for executing SQL statements, you can navigate
flexibly through the result set.
If SQL mode INTERNAL was selected for executing SQL statements, you can zoom the
column of data type LONG.
If one of the other SQL modes was selected for executing SQL statements, you can only
navigate forwards in the result table.
If several SQL statements were executed, you can access the results and the messages
relating to these SQL statements with a drop-down list.
Directory Structure of the Database for SAP
Systems
How the directories required for the SAP DB database system are distributed to the available
hard disks has a significant impact on the security and performance of your database system.
A good distribution of files on the hard disk must meet the following requirements:
• There must be enough free space to allow the database to expand
• The data must be stored securely
• The hardware must meet the performance requirements
Distribution of the SAP DB Directories on the Hard Disk [Page
Conventions
Variables [Page
User Manual: SAP DB 67
68]
68]
SAP AG November 2002
Variables
The following table shows which variables are used in the explanation of the Directory
Structure of the Database System for SAP Systems [Page 67].
<version>
<database_name>
<independent_data_path>
<independent_program_path>
<dependent_path>
Version number of the SAP DB software
Name of database instance
Data path independent of the version (IndepDataPath)
Program path independent of the version
(IndepProgPath)
Data path dependent on the version (InstRoot directory).
This path specification must be explicit.
Distribution of the SAP DB Directories on the Hard
Disk
The ideal directory structure of the database system for SAP Systems [Page 67] depends on
the hardware configuration. There is no single solution or definition for the distribution of the
files of an SAP DB database instance [Page 132].
When attempting to find the optimum data distribution for your database environment in a
production system, bear in mind the information in the following sections:
• Security Requirements [Page
75]
• Performance Requirements [Page
• Example Configuration [Page
• Various Database Systems [Page
• SAP DB Directories [Page
70]
76]
77]
77]
Security Requirements
Using the appropriate hardware, operating system, or database features can improve the
availability [Page 38] of a database instance [Page 132].
• Log volumes [Page
In the production system, the log area [Page 142] should always be mirrored for
security.
Ideally, you should use hardware-based options for this mirroring.
However, you can also use the DUAL log mode [Page 143] of the SAP DB database
system.
You should not use RAID-5 configurations for the log volumes. RAID-5 systems do
not allow full mirroring. Because the database instance writes log entries sequentially,
this can lead to a loss in performance.
In a production system, logging should never be deactivated, even if you use RAID
systems.
144]
See also: Log Concept [Page
User Manual: SAP DB 68
43]
SAP AG November 2002
• Data volumes [Page 130]
If you wish to ensure a high standard of availability, we recommend using RAID-5 or
RAID-1 configurations for the data area [Page
131]. A disk crash and change will then
not affect the running of the database, if the RAID system is able to carry out a
recovery.
Every volume category should be stored on a different disk.
When using fault-tolerant hardware, it is best to only use the same type of hardware when
you want to extend the capacity. For example, RAID-5 systems should only be extended
using RAID-5 systems and mirroring disks with mirroring disks.
UNIX: In a production system, data volumes and log volumes should be used in conjunction
with raw devices. In the event of a system crash, raw devices are extremely secure.
Performance Requirements
For performance reasons, each of the different types of volume [Page 153] should be stored
on a different disk. Therefore, create data volumes [Page 130] and log volumes [Page 144] on
different disks. Because all changes to the database instance are logged in the log areas
[Page 142] , it is the log volumes for a database instance [Page 132] that see the most write
activity.
When RAID-5 systems are used, the database instance should be configured with several
data volumes. Performance will be better with many data volumes than with a single one,
because some parallel mechanisms used by the database system depend on the number of
configured data volumes.
For performance reasons, the log volumes must not be created on RAID-5 systems but only
on dedicated disks or RAID-1 systems.
If swap or paging areas and log entries are kept on the same disk, performance will be
negatively affected.
UNIX: Raw devices should be used for the data volumes and log volumes, because
accessing to data in raw devices is generally quicker than accessing data in files.
Example Configuration
... 03
... 03
... 02
DB data
Data
Data
DISKD01
Volume
Volume
DISKD01
DISKD01
... 02
DB data
Log
Volume
Log
Volume
DISKD01
DISKLA1
DISKLA1
... A2
... A2
... A5
... A5
Mirrored
Mirrored
DB data
Log
Volume
Log
Volume
DISKD01
DISKLB1
DISKLB1
... B2
... B2
... B5
... B5
User Manual: SAP DB 69
SAP AG November 2002
The data volumes [Page 130] and log volumes [Page 144] are on different disks. The log
volumes are mirrored.
Various Database Systems
For performance reasons, the SAP DB database system should not be installed on the same
computer along with other database systems.
IF you want to install several SAP DB database instances [Page
instance should be installed on a separate computer. This will allow you to deal with possible
performance problems more easily.
132], then every database
SAP DB Directories
The following table contains the distribution of the SAP DB directories on the hard disk [Page
68] for a database instance [Page 132].
Conventions
Variables [Page
SAP DB Directories
Directory name Description
<independent_data_path>
68]
IndepDataPath-Directory that, along with other
data, contains the following instance data [Page
71]:
• Configuration directory
• Run directory [Page
instance
147] of the database
<independent_data_path>/config
<independent_data_path>/wrk/<database
_name>
<independent_program_path>
<dependent_path>
User Manual: SAP DB 70
Configuration directory
• Parameter files
• Files for user authorization
Run directory of the database instance
<database_name>
• Log files (knldiag)
• Kernel trace files
• Kernel dump files
IndepProgPath-The directory that contains the
programs that are independent of the database
software version
• Programs Independent of the Database
Software Version [Page 71]
• Libraries for the Client Run-Time
Environment [Page 72]
The directory (InstRoot) that contains the
SAP AG November 2002
programs that are dependent on the database
software version [Page 72]
/sapdb/<database_name>/data
/sapdb/<database_name>/log
/sapdb/<database_name>/log
See also:
Example: SAP DB Directory Structure [Page
Display SAP DB Directories [Page 73]
Define SAP DB Directories [Page
74]
Instance Data
73]
Directory for data volumes [Page
Directory for volumes
• Log volumes [Page
• Mirrored log volumes
Directory for security files
• Backups of log entries
• Backups of data
144]
130]
The instance data is the run directories [Page
132] and the configuration directory.
SAP DB Directories [Page 70]
Variables [Page
The instance data is stored in the IndepDataPath directory: <independent_data_path>.
Run directory: <independent_data_path>/wrk/<database_name>
Configuration directory: <independent_data_path>/config
68]
(UNIX)
/sapdb/data/wrk/LVC (Run directory for the LVC instance)
/sapdb/data/wrk/SDB (Run directory for the SDB instance)
/sapdb/data/wrk/P01 (Run directory for the P01 instance)
/sapdb/data/config (Configuration directory for all instances)
147] required for the database instance [Page
Programs that Are Independent of the Database
Software Version
Programs that are independent of the database software version are only installed once for
each computer, since they are needed for the database services that exist for each computer.
SAP DB Directories [Page
Variables [Page
User Manual: SAP DB 71
68]
70]
SAP AG November 2002
The programs that are independent of the database software version are stored in the
IndepProgPath directory: <independent_program_path>.
In this directory and its subdirectories, you will always find the programs for the most recently
installed version of the database software.
(UNIX)
/sapdb/programs/bin/x_server
(version-independent program X server)
/sapdb/programs/pgm/dbmcli
(version-independent program Database Manager CLI)
Libraries for the Client Run-time Environment
The libraries required for the client run-time environment are installed once on each
computer, but must be available in different versions.
SAP DB Directories [Page
Variables [Page 68]
The libraries are stored in a subdirectory of the IndepProgPath directory:
<independent_program_path>/runtime/<version>
(UNIX)
/sapdb/programs/runtime/7240/lib
70]
Programs that Are Dependent on the Database
Software Version
The programs that are dependent on the database software version are installed once per
database instance [Page
instance is independent of the database software versions of other database instances
running on the same machine.
SAP DB Directories [Page 70]
Variables [Page
The programs that are dependent on the database software version are situated in the
directory <dependent_path>.
68]
132]. This means that the database software version of a database
(UNIX)
/sapdb/LVC/db (InstRoot directory for the liveCache instance LVC)
/sapdb/SDB/db (InstRoot directory for the Content Server instance SDB)
/sapdb/P01/db (InstRoot directory for the database instance P01)
User Manual: SAP DB 72
SAP AG November 2002
Client Tools
The client tools supported by SAP DB can be installed onto the database server or onto a
computer of your choice.
The GUI clients are only installed once per computer. The settings for each user can be
stored user-specifically.
The directory for the client tools is freely-definable during the installation.
SAP DB Directories [Page
70]
Example: SAP DB Directory Structure
Example for the structure of SAP DB directories [Page 70]
Display SAP DB Directories
You can use the following DBMCLI commands to display the paths for the SAP DB directories
[Page 70] IndepDataPath, IndepProgPath and InstRoot:
Conventions
Variables [Page 68]
User Manual: SAP DB 73
SAP AG November 2002
Directory name DBMCLI command
<independent_data_path>
<independent_program_path>
<dependent_path>
dbm_getpath IndepDataPath
dbm_getpath IndepProgPath
db_enum
Define SAP DB Directories
You can use the following DBMCLI commands to define the paths for the SAP DB directories
[Page 70] IndepDataPath, IndepProgPath and InstRoot:
Conventions
Variables [Page 68]
Directory name DBMCLI command
<independent_data_path>
dbm_putpath IndepDataPath
<independent_data_path>
<independent_program_path>
<dependent_path>
dbm_putpath IndepProgPath
<independent_program_path>
inst_reg <dependent_path>
Directory Structure of the Database System for
Open Source
How the directories required for the SAP DB database system are distributed to the available
hard disks has a significant impact on the security and performance of your database system.
A good distribution of files on the hard disk must meet the following requirements:
• There must be enough free space to allow the database to expand
• The data must be stored securely.
• The hardware must meet the performance requirements.
Distribution of the SAP DB Directories on the Hard Disk [Page
Conventions
75]
Variables [Page
User Manual: SAP DB 74
75]
SAP AG November 2002
Variables
The following table shows which variables are used in the explanation of the Directory
Structure of the Database System for Open Source [Page 74].
<version>
<database_name>
<independent_data_path>
<independent_program_path>
<dependent_path>
Version number of the SAP DB software
Name of database instance
Data path independent of the database version
(IndepDataPath)
Linux
For an RPM-based installation, you should define this
directory as follows: /var/opt/sapdb/indep_data
Program path independent of the database version
(IndepProgPath)
Linux
For an RPM-based installation, you should define this
directory as follows: /var/opt/sapdb/indep_prog
Data path dependent on the database version (InstRoot
directory). This path specification must be explicit.
Linux
For an RPM-based installation, you should define this
directory as follows: /opt/sapdb/depend
Distribution of the SAP DB Directories on the Hard
Disk
The ideal directory structure of the database system for Open Source [Page 74] depends on
the hardware configuration. There is no single solution or definition for the distribution of the
files of an SAP DB database instance [Page
When attempting to find the optimum data distribution for your database environment in a
production system, bear in mind the information in the following sections:
• Security Requirements [Page
• Performance Requirements [Page
• Example Configuration [Page
• Various Database Systems [Page
• SAP DB Directories [Page
75]
77]
77]
132].
76]
77]
Security Requirements
Using the appropriate hardware, operating system, or database features can improve the
availability [Page
• Log volumes [Page
In the production system, the log area [Page 142] should always be mirrored for
security.
User Manual: SAP DB 75
38] of a database instance [Page 132].
144]
SAP AG November 2002
Ideally, you should use hardware-based options for this mirroring.
However, you can also use the DUAL log mode [Page 143] of the SAP DB database
system.
You should not use RAID-5 configurations for the log volumes. RAID-5 systems do
not allow full mirroring. Because the database instance writes log entries sequentially,
this can lead to a loss in performance.
In a production system, logging should never be deactivated, even if you use RAID
systems.
See also: Log Concept [Page
• Data volumes [Page
If you wish to ensure a high standard of availability, we recommend using RAID-5 or
RAID-1 configurations for the data area [Page 131]. A disk crash and change will then
not affect the running of the database, if the RAID system is able to carry out a
recovery.
Every volume category should be stored on a different disk.
When using fault-tolerant hardware, it is best to only use the same type of hardware when
you want to extend the capacity. For example, RAID-5 systems should only be extended
using RAID-5 systems and mirroring disks with mirroring disks.
UNIX: In a production system, data volumes and log volumes should be used in conjunction
with raw devices. In the event of a system crash, raw devices are extremely secure.
130]
43]
Performance Requirements
For performance reasons, each of the different types of volume [Page 153] should be stored
on a different disk. Therefore, create data volumes [Page 130] and log volumes [Page 144] on
different disks. Because all changes to the database instance are logged in the log areas
[Page 142] , it is the log volumes for a database instance [Page 132] that see the most write
activity.
When RAID-5 systems are used, the database instance should be configured with several
data volumes. Performance will be better with many data volumes than with a single one,
because some parallel mechanisms used by the database system depend on the number of
configured data volumes.
For performance reasons, the log volumes must not be created on RAID-5 systems but only
on dedicated disks or RAID-1 systems.
If swap or paging areas and log entries are kept on the same disk, performance will be
negatively affected.
UNIX: Raw devices should be used for the data volumes and log volumes, because
accessing to data in raw devices is generally quicker than accessing data in files.
User Manual: SAP DB 76
SAP AG November 2002
Example Configuration
... 03
... 03
... 02
DB data
Data
Data
DISKD01
Volume
Volume
DISKD01
DISKD01
... 02
DB data
Log
Volume
Log
Volume
DISKD01
DISKLA1
DISKLA1
... A2
... A2
... A5
... A5
Mirrored
Mirrored
DB data
Log
Volume
Log
Volume
DISKD01
DISKLB1
DISKLB1
... B2
... B2
... B5
... B5
The data volumes [Page
130] and log volumes [Page 144] are on different disks. The log
volumes are mirrored.
Various Database Systems
For performance reasons, the SAP DB database system should not be installed on the same
computer along with other database systems.
IF you want to install several SAP DB database instances [Page
instance should be installed on a separate computer. This will allow you to deal with possible
performance problems more easily.
132], then every database
SAP DB Directories
The following table contains the distribution of the SAP DB directories on the hard disk [Page
75] for a SAP DB database instance [Page 132].
Conventions
Variables [Page 75]
SAP DB Directories
Directory name Description
<independent_data_path>
<independent_data_path>/config
User Manual: SAP DB 77
IndepDataPath-Directory that, along with other
data, contains the following instance data:
• Configuration directory
• Run directory [Page
147] of the database
instance
Configuration directory
SAP AG November 2002
• Parameter files
• Files for user authorization
<independent_data_path>/wrk/<database
_name>
<independent_program_path>
<dependent_path>
The client tools supported by SAP DB can be installed onto the database server or onto a
computer of your choice.
Run directory of the database instance
<database_name>
• Log files (knldiag)
• Kernel trace files
• Kernel dump files
IndepProgPath-The directory that contains the
programs that are independent of the database
software version
Programs that are independent of the database
software version are only installed once for each
computer, since they are needed for the
database services that exist for each computer.
The libraries required for the client run-time
environment are installed once on each
computer, but must be available in different
versions.
The directory (InstRoot) that contains the
programs that are dependent on the database
software version
The GUI clients are only installed once per computer. The settings for each user can be
stored user-specifically.
The directory for the client tools is freely-definable during the installation.
See also:
Display SAP DB Directories [Page
Define SAP DB Directories [Page
78]
79]
Display SAP DB Directories
You can use the following DBMCLI commands to display the paths for the SAP DB directories
[Page 77] IndepDataPath, IndepProgPath and InstRoot:
Conventions
Variables [Page 75]
Directory name DBMCLI command
<independent_data_path>
<independent_program_path>
<dependent_path>
dbm_getpath IndepDataPath
dbm_getpath IndepProgPath
db_enum
User Manual: SAP DB 78
SAP AG November 2002
Define SAP DB Directories
You can use the following DBMCLI commands to define the paths for the SAP DB directories
[Page 77] IndepDataPath, IndepProgPath and InstRoot:
Conventions
Variables [Page 75]
Directory name DBMCLI command
<independent_data_path>
<independent_program_path>
<dependent_path>
dbm_putpath IndepDataPath
<independent_data_path>
dbm_putpath IndepProgPath
<independent_program_path>
inst_reg <dependent_path>
Database Parameters
To initialize the database parameters, use is generally made of the default configuration
which was stored when the database software was installed.
The configuration generated by the system will always be runnable. If necessary, you can still
adjust the database parameters originally set to suit any specific requirements you may have.
Alternatively you can use the configuration of a database instance [Page
on the computer or the configuration from a data backup [Page 131]. Even after this you can
still adjust the database parameters to suit your own requirements.
The database parameters are divided into general database parameters [Page
database parameters [Page 80] and support database parameters [Page 80].
Display or change database parameters
132] already present
79], special
You can use the Database Manager [Page 133] to display or change database parameters.
See:
Database Manager GUI: SAP DB 7.4, Section Displaying and Changing Current Database
Parameters [See SAP DB Library]
Database Manager CLI: SAP DB 7.4, Section Configuring Database Instances [See SAP DB
Library]
General Database Parameters
Database parameters [Page 135] that determine the general database features
CACHE_SIZE [Page
LOG_MODE [Page 84] LOG_SEGMENT_SIZE [Page
MAXCPU [Page 85] MAXDATAVOLUMES [Page 85] MAXLOCKS [Page 85]
MAXLOGVOLUMES [Page 86] MAXUSERTASKS [Page 86] RESTART_SHUTDOWN [Page
User Manual: SAP DB 79
81] INSTANCE_TYPE [Page 82] KERNELVERSION [Page 83]
MAXBACKUPDEVS [Page
84]
85]
SAP AG November 2002
88]
RUNDIRECTORY [Page 88]
Special Database Parameters (Extended)
Database parameters [Page 135] that determine the special database features
BACKUP_BLOCK_CNT [Page
81]
DEADLOCK_DETECTION
[Page 81]
JOIN_MAXTAB_LEVEL4 [Page
82]
KERNELDIAGSIZE [Page 83] LOG_BACKUP_TO_PIPE
LRU_FOR_SCAN [Page 84] MAXRGN_REQUEST [Page 86] MAXSERVERTASKS [Page 86]
MP_RGN_LOOP [Page 86] OPTIM_BUILD_RESLT [Page
OPTIM_KEY_INV_RATE [Page
87]
OPTIM_OR_DISTINCT [Page
88]
SESSION_TIMEOUT [Page 89] UTILITY_PROT_SIZE [Page
_DATA_CACHE_RGNS [Page
89]
CAT_CACHE_SUPPLY [Page
81]
DEFAULT_CODE [Page 81] DEVNO_BIT_COUNT [Page 81]
JOIN_MAXTAB_LEVEL4 [Page
82]
[Page 83]
86]
OPTIM_MAX_MERGE [Page
87]
REQUEST_TIMEOUT [Page
88]
89]
_EVENT_ALIVE_CYCLE [Page
89]
DATE_TIME_FORMAT [Page
81]
JOIN_SEARCH_LEVEL [Page
83]
LOG_IO_QUEUE [Page
OPTIM_FETCH_RESLT [Page
87]
OPTIM_ORDERBY_IDX [Page
87]
SEQUENCE_CACHE [Page 89]
_MAXEVENTS [Page 89]
84]
_MAX_MESSAGE_FILES
[Page 89]
_TRANS_RGNS [Page 90] _TREE_RGNS [Page 90] _UNICODE [Page 90]
_ROW_RGNS [Page
90] _TAB_RGNS [Page 90]
Support Database Parameters
There area certain database parameters [Page 135] that should not, or do not have to be
modified for normal database operation.
The setting of these database parameters requires a detailed knowledge of the database
system, and should therefore only be carried out by the SAP DB Support [Page
User Manual: SAP DB 80
158] team.
SAP AG November 2002
BACKUP_BLOCK_CNT
The special database parameter [Page 80] BACKUP_BLOCK_CNT denotes the block size in
pages when data is backed up or restored.
CACHE_SIZE
The general database parameter [Page 79] CACHE_SIZE denotes the size of the I/O buffer
cache [Page 138] in pages [Page 146].
CAT_CACHE_SUPPLY
The special database parameter [Page 80] CAT_CACHE_SUPPLY denotes the memory size
of the catalog cache [Page
19] in pages for all user tasks [Page 16].
DATE_TIME_FORMAT
The special database parameter [Page 80] DATE_TIME_FORMAT denotes the default format
for displaying dates and times.
DEADLOCK_DETECTION
The special database parameter [Page 80] DEADLOCK_DETECTION denotes the maximum
search level for deadlock detection.
Deadlocks that have not been detected by the deadlock detection up to the given search level
of the specified value are only resolved by the REQUEST_TIMEOUT [Page 88].
If DEADLOCK_DETECTION = 0, deadlock detection is disabled.
DEFAULT_CODE
If no other code attribute was specified by appropriate SQL statements, the special database
parameter [Page 80] DEFAULT_CODE denotes which code attribute [See SAP DB Library]
was used to create columns of data type CHAR[ACTER] [See SAP DB Library], VARCHAR
[See SAP DB Library] and LONG[VARCHAR] [See SAP DB Library] in the database instance.
DEVNO_BIT_COUNT
The special database parameter [Page 80] DEVNO_BIT_COUNT denotes the number of bits
in the converter block address that is reserved for the logical device number of a data volume.
Default value: 8
User Manual: SAP DB 81
SAP AG November 2002
Size of a page: 8 KB
DEVNO_BIT_COUNT is set to 8
SAP DB can therefore manage 256 data volumes. Each data volume can have a
maximum size of 128 GB.
The value that was set for DEVNO_BIT_COUNT when the database instance
was installed should not be subsequently changed. If you change this database
parameter during database operation, you must then recover the database
instance to update the numbering of the data volumes.
Possible values for DEVNO_BIT_COUNT: 6 <= DEVNO_BIT_COUNT <= 12
The higher the value of the database parameter DEVNO_BIT_COUNT, the more data
volumes can be managed. However, a high number of data volumes means the capacity of
the individual data volumes is reduced.
INSTANCE_TYPE
The general database parameter [Page 79] INSTANCE_TYPE denotes the database instance
type [Page 132].
• OLTP: SAP DB OLTP [Page
• LVC: liveCache [Page
• BW: SAP DB OLAP [Page
• CS: SAP DB Document Server [Page
• EMERGE: SAP DB E-Catalog [Page
140]
149]
149]
147]
148]
JOIN_MAXTAB_LEVEL9
The special database parameter [Page 80] JOIN_MAXTAB_LEVEL9 denotes the maximum
number of tables allowed in a join when selecting the join sequence algorithm.
The default value is 4.
See also:
JOIN_SEARCH_LEVEL [Page
83]
JOIN_MAXTAB_LEVEL4
The special database parameter [Page 80] JOIN_MAXTAB_LEVEL4 denotes the maximum
number of tables allowed in a join when selecting the join sequence algorithm.
The default value is 16.
See also:
JOIN_SEARCH_LEVEL [Page
User Manual: SAP DB 82
83]
SAP AG November 2002
JOIN_SEARCH_LEVEL
The special database parameter [Page 80] JOIN_SEARCH_LEVEL determines the algorithm
for the join sequence search. The level specified here determines how many resources and
how much time the join sequence search takes.
• 9 (Join sequence search level 9): All possible join sequences are calculated.
• 4 (join sequence level 4): Various join sequences are calculated, depending on the query
structure (transformer algorithm).
• 1 (join sequence search level 1): The simplest algorithm is used for the join sequence
search (greedy algorithm).
• 0 (classified join sequence search level 0, level 0 is the default setting): The algorithm
that is used for the join sequence search depends on the number of tables that were
selected in join.
Joins with n number of tables, where n<=JOIN_MAXTAB_LEVEL9 [Page
the join sequence search is used.
Joins with n number of tables, where
JOIN_MAXTAB_LEVEL9<n<=JOIN_MAXTAB_LEVEL4 [Page 82]: Level 4 of the join
sequence search is used.
Joins with n number of tables, where JOIN_MAXTAB_LEVEL4<n: Level 1 of the join
sequence search is used.
82]: Level 9 of
JOIN_MAXTAB_LEVEL4 is set to 16.
JOIN_MAXTAB_LEVEL9 is set to 4.
JOIN_SEARCH_LEVEL is set to 0.
5 tables are used for a join.
Join sequence search level 4 (transformer algorithm) is used for this join because
the database parameter JOIN_MAXTAB_LEVEL9<5<=JOIN_MAXTAB_LEVEL4
is most appropriate.
KERNELDIAGSIZE
The special database parameter [Page 80] KERNELDIAGSIZE denotes the size of log file of
the kernel [Page
140] in KB.
KERNELVERSION
The general database parameter [Page 79] KERNELVERSION denotes which database
software version is being used.
LOG_BACKUP_TO_PIPE
The special database parameter [Page 80] LOG_BACKUP_TO_PIPE determines whether or
not a log backup [Page
User Manual: SAP DB 83
142] is permitted (YES) or not permitted (NO) in pipes.
SAP AG November 2002
LOG_IO_QUEUE
The special database parameter [Page 80] LOG_IO_QUEUE denotes the size of the log
queue [Page 45] in pages [Page 46].
LOG_MODE
The general database parameter [Page 79] LOG_MODE denotes the log mode [Page 143].
• SINGLE: Log entries are saved to a single log area [Page
• DUAL: Log entries are saved to two log areas in parallel
142]
LOG_SEGMENT_SIZE
The general database parameter [Page 79] LOG_SEGMENT_SIZE denotes the size of a log
segment in the log area [Page
For interactive backups [Page 139], the log segment size determines the size of the segments
that the redo log entries [Page 44] are saved to. It also determines the intervals at which log
areas are backed up automatically [Page 126].
The size of the log segment depends on the size of the individual log volumes [Page 144] and
must not be greater than the sum of all log volumes.
User-defined value Log segment sizes used by the system
No definition of log segment size
(LOG_SEGMENT_SIZE = 0)
The log segment size in pages is smaller than or
the same as 50% of the title log area.
The log segment > 50% of the total log area. 50% of the log area.
142] in pages.
A third of the total log area.
The defined value for LOG_SEGMENT_SIZE.
LRU_FOR_SCAN
The special database parameter [Page 80] LRU_FOR_SCAN governs where data pages that
have been scanned into the data cache [Page 130] are placed in the LRU (last recently used)
list.
• no (default setting): The data pages scanned into the data cache are placed at the end of
the LRU list. This means the data pages will removed from the data cache promptly if
new entries are made in the LRU list.
• yes: The data pages scanned into the data cache remain in the same place in the LRU
list as they were before the scan.
MAXARCHIVELOGS
See MAXLOGVOLUMES [Page 86]
User Manual: SAP DB 84
SAP AG November 2002
MAXBACKUPDEVS
The general database parameter [Page 79] MAXBACKUPDEVS denotes the maximum
number of files or tape devices that can be used in parallel.
You can speed up the process of backing up and restoring data volumes [Page
more than one file or tape device in parallel.
130] by using
MAXCPU
The general database parameter [Page 79] MAXCPU denotes the maximum number of CPUs
allowed on the database instance [Page 132]. MAXCPU defines over how many CPUs the
user tasks [Page
When defining MAXCPU, however, remember that operating system resources must also be
available for other processes.
This means you can define and also restrict the number of CPUs the database instance uses
on multiprocessor computers (Multiprocessor Configuration [Page
If you are using a single processor computer, you should choose a value of 1 for the
MAXCPU.
16] generating the main load are distributed.
144]).
MAXDATADEVSPACES
See MAXDATAVOLUMES [Page 85]
MAXDATAVOLUMES
The general database parameter [Page 79] MAXDATAVOLUMES (depending on the version,
may also be called MAXDATADEVSPACES) denotes the maximum number of data volumes
[Page 130].
MAXLOCKS
The general database parameter [Page 79] MAXLOCKS determines the maximum number of
entries in the lock list in which the locks [Page
lock requests are stored.
See also:
Lock behavior [Page
113]
140] (line or table locks) of all users and their
User Manual: SAP DB 85
SAP AG November 2002
MAXLOGVOLUMES
The general database parameter [Page 79] MAXDATAVOLUMES (depending on the version,
may also be called MAXDATADEVSPACES) denotes the maximum number of data volumes
[Page 144].
MAXRGN_REQUEST
The special database parameter [Page 80] MAXRGN_REQUEST denotes the maximum
number of times a task [Page
exceeded, the task lets another task access the CPU as long as it belongs to the same user
kernel thread [Page 14].
151] can attempt to access a critical section. If this number is
MAXSERVERTASKS
The special database parameter [Page 80] MAXSERVERTASKS denotes the maximum
number of server tasks [Page 16] that are allowed when processing tasks.
MAXUSERTASKS
The general database parameter [Page 79] MAXUSERTASKS denotes the maximum number
of users that can work on a database at any one time (user tasks [Page
sessions [Page 135]).
Overconfiguration exceeding the actual requirement leads to increased demands on address
space, especially shared memory.
Once the number of configured database sessions is reached, no other users can
connect to the database instance concerned. The number of active database
sessions is therefore a critical parameter of database operation and must be
monitored.
16], database
MP_RGN_LOOP
The special database parameter [Page 80] MP_RGN_LOOP denotes the maximum number
of times a task [Page 151] may attempt to access a critical section that has been locked by
another task. If this number is exceeded, the status of the task changes to “Waiting”.
OPTIM_BUILD_RESLT
The special database parameter [Page 80] OPTIM_BUILD_RESLT determines how the
Optimizer’s results are structured.
User Manual: SAP DB 86
SAP AG November 2002
If the Optimizer estimates that the percentage of all pages of an index that need to be read in
order to get results is greater than the figure defined in OPTIM_BUILD_RESLT, this index will
not be used.
OPTIM_FETCH_RESLT
The special database parameter [Page 80] OPTIM_FETCH_RESLT determines how the
Optimizer is used.
If the Optimizer estimates that the percentage of all pages of an index that need to be read is
greater than the figure defined in OPTIM_BUILD_RESLT and no results need to be built up,
this index will not be used.
OPTIM_KEY_INV_RATE
The special database parameter [Page 80] OPTIM_KEY_INV_RATE determines which
optimizing algorithm should be used. This depends on whether it would be better to use the
index or the primary key.
• Relationship 1: The number of index pages that need to be read in relation to the size of
the index
• Relationship 2: The number of pages that need to be read using the primary key (primary
data pages) in relation to the number of all primary data pages
If relationship 1 is OPTIM_KEY_INV_RATE percent smaller than relationship 2, the index will
be used. If not, the data will be accessed directly using the primary key.
OPTIM_MAX_MERGE
The special database parameter [Page 80] OPTIM_MAX_MERGE denotes how the
optimizing algorithm for merging index lists changes.
If the number of pages of an index that need to be merged exceeds the value specified in
OPTIM_MAX_MERGE, this index will not be used for an index merging strategy.
OPTIM_ORDERBY_IDX
The special database parameter [Page 80] OPTIM_ORDERBY_IDX denotes how the
optimization algorithm changes if you are using ORDER BY clauses.
If the OPTIM_ORDERBY_IDX values are quite high, the system will tend to use inversions to
process SQL statements with ORDER BY clauses. If this is the case, the results do not need
to be built up so the first results can be delivered quickly.
This parameter does not affect the join SELECT statements.
User Manual: SAP DB 87
SAP AG November 2002
OPTIM_OR_DISTINCT
The special database parameter [Page 80] OPTIM_OR_DISTINCT denotes how the
optimization algorithm changes if you are using OR search conditions in SQL statements.
• Low OPTIM_OR_DISTINCT values: You should use low values for OR search conditions
if you want DISTINCT hitlists where no lines are duplicated.
• High OPTIM_OR_DISTINCT values: You should use high values for OR search
conditions if you do not want DISTINCT hitlists.
REQUEST_TIMEOUT
The special database parameter [Page 80] REQUEST_TIMEOUT denotes the maximum
amount of time (in seconds) you should have to wait for a lock [Page
This database parameter limits the amount of time you have to wait until a lock is lifted by
other users for all database sessions [Page
If a lock request cannot be satisfied within the time thus defined, a message is returned to the
waiting database session.
135].
140] to be released.
RESTART_SHUTDOWN
The general database parameter [Page 79] RESTART_SHUTDOWN determines whether
database instance [Page 132] modes should be changed automatically or not.
The database parameter only takes effect if you are using a Windows operating system (NT,
2000, XP).
• Manual
[Page 137].
• Auto:
The Windows Service Manager switches the database instance straight to operating
mode [Page 145] ONLINE when it starts.
A database instance in ONLINE mode will be shut down automatically if the operating
system shuts down.
: All changes to the mode of the database instance must be initiated by the DBA
The following changes are made to the database instance automatically:
Since the shutdown of an operating system is subject to a time limit, it may
interrupt the automatic shutdown of a database instance, particularly if you are
working with large database instances where a high volume of data is being
changed.
If you restart the database after the shutdown was interrupted, you must import
log entries to restore the database instance. We therefore recommend you set
the database parameter RESTART_SHUTDOWN to manual.
RUNDIRECTORY
The general database parameter [Page 79] RUNDIRECTORY determines which directory is
used as the run directory [Page 147] on the database instance [Page 132].
User Manual: SAP DB 88
SAP AG November 2002
SEQUENCE_CACHE
The special database parameter [Page 80] SEQUENCE_CACHE determines how big the
sequence cache is in pages.
SESSION_TIMEOUT
The special database parameter [Page 80] SESSION_TIMEOUT determines for how many
seconds database sessions [Page
If no SQL statement is issued within the specified time, the database system terminates the
database session concerned (ROLLBACK WORK RELEASE statement).
135] can remain inactive before being timed out.
UTILITY_PROT_SIZE
The special database parameter [Page 80] UTILITY_PROT_SIZE denotes the size of the log
file (dbm.utl) that was written in the run directory [Page
132].
147] of the database instance [Page
_DATA_CACHE_RGNS
The special database parameter [Page 80] _DATA_CACHE_RGNS determines how many
critical regions you can work with at once.
_EVENT_ALIVE_CYCLE
The special database parameter [Page 80] _EVENT_ALIVE_CYCLE denotes the number of
seconds an event cycle takes.
_MAXEVENTS
The special database parameter [Page 80] _MAXEVENTS denotes the maximum number of
events stored by the kernel [Page 140] in the cache for processing by the Database Manager
[Page 133] .
_MAX_MESSAGE_FILES
The special database parameter [Page 80] _MAX_MESSAGE_FILES denotes the maximum
number of trace files that can be opened at one time.
User Manual: SAP DB 89
SAP AG November 2002
_ROW_RGNS
The special database parameter [Page 80] _ROW_RGNS denotes the number of critical
regions in which you can check lock collisions in rows.
_TAB_RGNS
The special database parameter [Page 80] _TAB_RGNS denotes the number of critical
regions in which you can check lock collisions in tables.
_TRANS_RGNS
The special database parameter [Page 80] _TRANS_RGNS denotes the number of critical
regions in which you can check lock collisions in transactions [Page 152] simultaneously.
_TREE_RGNS
The special database parameter [Page 80] _TREE_RGNS denotes the number of critical
regions in which you can check lock collisions in B* trees [Page
128] simultaneously.
_UNICODE
The special database parameter [Page 80] _UNICODE determines whether user data and
metadata for database objects is saved in UNICODE [Page
This database parameter cannot be changed once the SAP DB database system is installed.
See also:
SAP DB as UNICODE Database [Page
[Page 91]
90] → Installing a UNICODE-Enabled Database
152].
SAP DB as UNICODE Database
SAP DB can be used as a UNICODE database.
• UNICODE [Page
• Installing a UNICODE-Enabled Database [Page
• UNICODE and SQL [Page
• UNICODE in Programming Languages [Page
User Manual: SAP DB 90
152]
91]
93]
96]
SAP AG November 2002
UNICODE
Data types such as CHAR ASCII and CHAR EBCDIC are mainly suited to English and central
European languages. With other character sets, a code attribute is usually used for these
data types. This code attribute uses a different presentation code to ASCII and EBCDIC, even
for internal storage in the database system. This causes problems if you want to access these
database systems using a different character set, or if you want to exchange data between
database systems with different character sets.
You can avoid these problems by using internal character coding in accordance with
UNICODE. Internally, the UNICODE data is stored in UTF-16/UCS-2 format. In UTF-16/UCS2 format, all characters are two bytes long.
SAP DB is able to display various presentation codes in UNICODE format (UNICODE code in
line with ISO 10646, page 1).
Metadata in UNICODE
The names of the database objects (such as table or column names) can be stored internally
in UNICODE and can therefore then be displayed in the required presentation code in the
database tools.
Application data in UNICODE
SAP DB supports the code attribute UNICODE for the data types CHAR[ACTER], VARCHAR
and LONG[VARCHAR].
See also:
Installing a UNICODE-Enabled Database [Page
Reference Manual: SAP DB 7.4, Code attribute [See SAP DB Library]
91]
Installing a UNICODE-Enabled Database
To make storage of metadata [Page 131] and application data [Page 125] in UNICODE [Page
152] , proceed as follows:
Metadata in UNICODE
When installing the database instance, set the database parameter _UNICODE [Page
YES.
Application data in UNICODE
1. When installing the database instance, set the database parameter _UNICODE to YES.
2. Enter the code attribute [See SAP DB Library]
Please note that SAP DB UNICODE data is stored internally in UTF-16/UCS-2
format. As a result, double the space is required to store the UNICODE data in
the database instance.
UNICODE.
90] to
Procedure
Setting Database Parameter _UNICODE [Page 92]
Setting Code Attribute UNICODE [Page
User Manual: SAP DB 91
92]
SAP AG November 2002
Setting Database Parameter _UNICODE
In order to install a UNICODE-enabled database [Page 91], the database parameter
_UNICODE [Page
You can set the database parameter _UNICODE when you install the database instance with
the Database Manager GUI or the Database Manager CLI.
Database Manager GUI
1. Start the Database Wizard.
2. Carry out the first four installation steps.
3. In step 5 (Parameters), choose Extended.
4. Set the _UNICODE parameter to YES.
5. Continue with the installation.
90] must be set to YES.
Please note that you cannot change database parameter _UNICODE once it has
been set.
See also:
Database Manager GUI: SAP DB 7.4 , Section Creating a New Database Instance [See SAP
DB Library]
Database Manager CLI
A script with configuration information for the database instance contains, among other things,
lines for definition of the database parameters. You must insert the following line at this point:
param_put _UNICODE YES
See also:
Database Manager CLI: SAP DB 7.4, Changing a Parameter Value [See SAP DB Library]
Setting Code Attribute UNICODE
To be able to store the application data [Page 125] in UNICODE [Page 152], the database
must be UNICODE-enabled and you must set the code attribute [See SAP DB Library]
UNICODE for the required application data (Installing a UNICODE-Enabled Database [Page
91]). You can set the code attribute in the following ways:
• Database parameter DEFAULT_CODE [Page
In the column definition, make sure you enter code attribute UNICODE. Application data
is then only stored in UNICODE for these column values.
If you do not enter a code attribute, the code entered in the parameter DEFAULT_CODE
(that is to say, not UNICODE) is used for these column values.
81] is not set to value UNICODE.
• Database parameter DEFAULT_CODE is set to value UNICODE.
In the column definition, enter code attribute UNICODE. Application data is stored in
UNICODE for these column values.
If you do not enter a code attribute, the application data is stored in UNICODE for these
column values, because the parameter DEFAULT_CODE is set to UNICODE.
Database parameter DEFAULT_CODE is set to value UNICODE.
User Manual: SAP DB 92
SAP AG November 2002
Column definition Result
CHAR (n) UNICODE
CHAR (n)
CHAR (n) ASCII
CHAR (n) BYTE
For information on setting database parameters, see the following documentation:
• Database Manager GUI: SAP DB 7.4, Section Displaying and Changing Current
Database Parameters [See SAP DB Library]
• Database Manager CLI: SAP DB 7.4, Section Changing the Value of a Database
Parameter [See SAP DB Library]
UNICODE column
UNICODE column
ASCII column
Code neutral, i.e. the column values are not converted by
the database system
UNICODE and SQL
UNICODE can be used for metadata, application data and in SQL statements if installation of
a UNICODE-enabled database [Page 91] has been carried out.
UNICODE for metadata
If the database is UNICODE-enabled, all columns in the system tables that can be used to
request the metadata have a data type with the code attribute [See SAP DB Library]
UNICODE.
UNICODE for application data
To make application data UNICODE-enabled, you must set the UNICODE code attribute
[Page 92] in a UNICODE-enabled database for the required application data. The UNICODE
[Page 152] code attribute can be used for the data types CHAR[ACTER] (n), VARCHAR (n)
and LONG[VARCHAR]:
• CHAR[ACTER] (n) UNICODE
• VARCHAR (n) UNICODE
• LONG[VARCHAR] UNICODE
Example 1 [Page
can be used to display the results of various column definitions.
Displaying the Column Definition of a Table
java TableDef <jdbcurl> <table_name>
Creating a temporary table using the determined column definitions, and displaying
these column definitions
<command> ::= java TableDef <jdbcurl> <table_name>
<column_definition>
<jdbcurl> ::=
jdbc:sapdb:<database_name>?user=<userid>&password=<password>
94] illustrates the definition of Java class TableDef. Java class TableDef
User Manual: SAP DB 93
SAP AG November 2002
java TableDef jdbc:sapdb:TST?user=TEST&password=TEST DUMMY a
varchar (20)
TABLE: DUMMY
A: VARCHARASCII (20)
UNICODE in SQL Statements
SQL statements can contain both UNICODE literals and UNICODE identifiers. The
prerequisite for implementing these SQL statements is a UNICODE-enabled client (C/C++Precompiler, JDBC, ODBC, SQL Studio or Web SQL).
The prerequisite for using UNICODE in the SQL Studio and Web SQL is that a UNICODEenabled ODBC has been installed. SQL Studio and Web SQL are used on the operating
system Windows 2000. This operating system supports UNICODE.
Example 1
The Java class TableDef can be used to display the results of various column definitions
(see UNICODE and SQL [Page 93], section UNICODE for application data).
TableDef Definition
import java.sql.*;
/**
*
*/
public class TableDef
{
private Connection connection;
/**
* creates a new TableDef
*/
public
TableDef (
String url)
throws SQLException, ClassNotFoundException
{
// load class
Class.forName ("com.sap.dbtech.jdbc.DriverSapDB");
// create connection
this.connection = DriverManager.getConnection(url,
new java.util.Properties ());
this.connection.setAutoCommit (false);
}
/**
*
*/
protected void
createTable (
String tableName,
String createCommand)
throws SQLException
{
String fullCommand = "CREATE TABLE " + tableName + " ("
+ createCommand + " )";
Statement stmt = this.connection.createStatement ();
stmt.execute (fullCommand);
}
/**
*
*/
protected void
User Manual: SAP DB 94
SAP AG November 2002
showTableDef (
String tableName)
throws SQLException
{
System.out.println ("Table: " + tableName); //#print
DatabaseMetaData metaData = this.connection.getMetaData ();
ResultSet tableColumns = metaData.getColumns(null,
metaData.getUserName (), tableName, null);
while (tableColumns.next ()) {
String columnName = tableColumns.getString
("COLUMN_NAME");
String typeName = tableColumns.getString ("TYPE_NAME");
int colSize = tableColumns.getInt ("COLUMN_SIZE");
System.out.println (" " + columnName
+ ": " + typeName + " (" + colSize + ")"); //#print
}
}
/**
*
*/
protected void
close ()
{
try {
this.connection.rollback ();
this.connection.close ();
}
catch (SQLException sqlExc) {
// ignore
}
}
/**
*
*/
static protected String
join (
String [] args,
int startIndex)
{
if (startIndex >= args.length) {
return null;
}
StringBuffer result = new StringBuffer ();
for (int i = startIndex; i < args.length; ++i) {
result.append(args [i]);
result.append (' ');
}
return result.toString();
}
/**
* used when called form the command line
*/
public static void main (String [] args)
throws ClassNotFoundException
{
String url = args [0];
String tableName = args [1];
String createCommand = join (args, 2);
TableDef tableDef = null;
try {
tableDef = new TableDef (url);
if (createCommand != null) {
tableDef.createTable (tableName, createCommand);
}
tableDef.showTableDef (tableName);
}
catch (SQLException sqlExc) {
System.out.println (sqlExc);
User Manual: SAP DB 95
SAP AG November 2002
}
finally {
if (tableDef != null) {
tableDef.close ();
}
}
}
}
UNICODE in Programming Languages
JDBC, ODBC, the C/C++ Precompiler and Python support UNICODE [Page 152].
JDBC
Since Java works with UNICODE strings, it can read and write UNICODE columns.
If you also want to use UNICODE in SQL statements, you must set the unicode
CONNECT-property to true. SQL statements are then transferred to the database instance
in UTF-16/UCS-2 format. If the transfer package for the SQL statements is not large enough,
you can increase its size using database parameter _PACKET_SIZE.
ODBC
UNICODE is supported in the ODBC driver.
Depending on your operating system, you must take account of the following factors:
Operating
system
Windows 2000 The ODBC driver only exports the UNICODE and/or Wide functions of the
UNIX/Linux The use of the ODBC driver is currently not possible on platforms for which
ODBC-API. ANSI functions are mapped to the relevant Wide functions by the
Windows Driver Manager . This means that applications can use both the
ANSI and the UNICODE functions of the ODBC-API.
the standard UNICODE type WCHAR_T is defined with four bytes. The
database and ODBC driver process UNICODE internally as values that are
two bytes long.
Both the ANSI and UNICODE variants of the ODBC-API are defined in the
driver.
Applications that do not require the functionality of a Driver Manager can be
statically linked with the ODBC driver.
C/C++ Precompiler
During CONNECT, the C/C++ Precompiler checks whether the database is UNICODEenabled (Database parameter _UNICODE [Page
database, all SQL commands are transferred as UNICODE to the database instance.
92] = YES). In UNICODE-enabled
EXEC SQL BEGIN DECLARE SECTION;
/* "SELECT tablename FROM domain.tables" encoded in UCS2
*/
SQLUCS2 sqlstmt[36] =
{0x0053, 0x0045, 0x004C, 0x0045,
0x0043,
0x0054, 0x0020, 0x0054, 0x0041,
0x0042,
User Manual: SAP DB 96
SAP AG November 2002
0x004C, 0x0045, 0x004E, 0x0041,
0x004D,
0x0045, 0x0020, 0x0046, 0x0052,
0x004F,
0x004D, 0x0020, 0x0044, 0x004F,
0x004D,
0x0041, 0x0049, 0x004E, 0x002E,
0x0054,
0x0041, 0x0042, 0x004C, 0x0045,
0x0053,
0x0000};
SQLUCS2 resutlstring [64];
EXEC SQL END DECLARE SECTION;
/* connect ... */
/* parse a unicode SQL statement and give it a statement
name */
EXEC SQL PREPARE stmt1 FROM :sqlstmt;
EXEC SQL DECLARE curs1 CURSOR FOR stm1;
EXEC SQL OPEN curs1;
/* loop over resultset */
WHILE (sqlca.sqlcode != 100)
{
EXEC SQL FETCH curs1 INTO :resultstring;
}
EXEC SQL CLOSE curs1;
For the complete example, see Example 2 [Page
• The data type SQLUCS2 [] marks positive 16-bit wide whole numbers (unsigned short).
• Input and output variables can have data type CHAR [] or SQLUCS2 []. The values are
converted into suitable data types internally, if required.
• SQL commands can have data type CHAR [] or SQLUCS2 [].
97] (HelloUnicodeDB.cpc).
Python
Python 2.*: UNICODE columns can be read and written. UNICODE character strings cannot
be used as SQL statements.
Python 1.5.2: UNICODE-type output values are converted to ASCII. If this conversion fails,
an error is reported.
Example 2
HelloUnicodeDB.cpc is a Precompiler program that uses UNICODE host variables (see
UNICODE in Programming Languages [Page
HelloUnicodeDB.cpc Definition
/********************************************************************
**
module : HelloUnicodeDB.cpc
-------------------------------------------------------------------
--
responsible : MarcoP
special area: demonstration program precompiler
96], Section C/C++ Precompiler).
User Manual: SAP DB 97
SAP AG November 2002
description : demonstration program for unicode features of SAPDB
precompiler
The following steps are needed to execute the demo program:
1. customize the connect data in the embedded SQL program source
(line 43 - 46)
2. precompile/compile the embedded SQL program HelloUnicodeDB.cpc
cpc [-u <userid>,<password> -d <database_name> -n <server_node>]
HelloUnicodeDB
3. linking the embedded SQL program HelloUnicodeDB.cpc
cpclnk HelloUnicodeDB
last changed: 2000-04-30 17:17
see also :
-------------------------------------------------------------------
--
copyright: Copyright by SAP AG, 2001
*********************************************************************
*/
/*===================================================================
*
* INCLUDES *
*===================================================================
*/
#include <stdio.h>
#include <string.h>
/*===================================================================
*
* DEFINES *
*===================================================================
*/
#define KNLIDNTFR 63 /* max. number of character for a unicode
kernel identifier*/
/* connect data */
#define USERID "TEST" /* username */
#define PASSWD "TEST" /* password */
#define SERVERNODE "localhost" /* computer name */
#define SERVERDB "TST" /* name of database*/
/*===================================================================
*
* MACROS *
*===================================================================
*/
/*===================================================================
*
* LOCAL CLASSES, STRUCTURES, TYPES, UNIONS ... *
*===================================================================
*/
/*===================================================================
User Manual: SAP DB 98
SAP AG November 2002
*
* STATIC/INLINE FUNCTIONS (PROTOTYPES) *
*===================================================================
*/
/* print UCS2 string as 7-bit Ascii, replace non-ascii characters
with '?'*/
static void printAs7bitAscii(SQLUCS2 *aUCS2Str, int length)
{
int i;
for (i = 0; i < length; i++) {
if ( aUCS2Str[i] == 0x0000
|| aUCS2Str[i] == 0x0020 )
return;
if ( aUCS2Str[i] < 0x007f
&& aUCS2Str[i] > 0x0000)
printf("%c",aUCS2Str[i]);
else
printf("?");
}
}
/* format sql error for output */
static char *FormatSQLError(sqlcatype *sqlca)
{
static char buffer[512];
#ifndef sql_oracle
sprintf(buffer, "(%d):%.*s", sqlca->sqlcode,
sqlca->sqlerrml, sqlca->sqlerrmc);
#else
sprintf(buffer, "(%d):%.*s", sqlca->sqlcode,
sqlca->sqlerrm.sqlerrml, sqlca->sqlerrm.sqlerrmc);
#endif
return(buffer);
}
/*==================================================================*
* METHODS *
*==================================================================*
/
int main(int argc, char **argv)
{
EXEC SQL BEGIN DECLARE SECTION;
char *user = USERID;
char *pwd = PASSWD;
char *servernode = SERVERNODE;
char *serverdb = SERVERDB;
/* "SELECT TABLENAME FROM DOMAIN.TABLES " encoded in UCS2 */
SQLUCS2 sqlstmt[36] = {0x0053, 0x0045, 0x004C, 0x0045, 0x0043,
0x0054, 0x0020, 0x0054, 0x0041, 0x0042,
0x004C, 0x0045, 0x004E, 0x0041, 0x004D,
0x0045, 0x0020, 0x0046, 0x0052, 0x004F,
0x004D, 0x0020, 0x0044, 0x004F, 0x004D,
0x0041, 0x0049, 0x004E, 0x002E, 0x0054,
0x0041, 0x0042, 0x004C, 0x0045, 0x0053,
0x0000};
SQLUCS2 resultstring[64];
EXEC SQL END DECLARE SECTION;
User Manual: SAP DB 99
SAP AG November 2002
/* set connect properties */
EXEC SQL SET SERVERDB :serverdb ON :servernode;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
/* connect to database */
EXEC SQL CONNECT :user IDENTIFIED BY :pwd;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
/* parse a unicode sql command and give it a statement name */
EXEC SQL PREPARE stmt1 FROM :sqlstmt;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
/* declare a cursor for a prepared statement name */
EXEC SQL DECLARE curs1 CURSOR FOR stmt1;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
/* open the cursor "curs1" */
EXEC SQL OPEN curs1;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
/* loop over resultset */
while (sqlca.sqlcode != 100)
{
/* fetch a single row into a character hostvariable encoded in
UCS2 */
EXEC SQL FETCH curs1 INTO :resultstring;
if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
else {
printAs7bitAscii(resultstring, KNLIDNTFR);
printf("\n");
}
}
/* close the cursor */
EXEC SQL CLOSE curs1;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
/* commit and release the session */
EXEC SQL COMMIT WORK RELEASE;
if (sqlca.sqlcode != 0 ) {
printf("\n%s\n", FormatSQLError(&sqlca));
}
}
/*===================================================================
*
User Manual: SAP DB 100
Loading...