User manual SAP DB

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 Example text Description 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. Example text EXAMPLE TEXT Emphasized words or phrases in body text, titles of graphics and tables. 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. 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. Example text EXAMPLE TEXT Example text 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 25] Security concepts [Page 37] Log concept [Page 43] Database tools [Page 136] Directory structure of the database for SAP Systems [Page 67] Directory structure of the database for Open Source [Page 74] Database parameters [Page 135] SAP DB as a UNICODE Database [Page 90] Data management using B* trees [Page 101] Lock behavior [Page 113] SAP DB Version 7.4 [Page 120] Terms [Page 123] SAP DB documentation [Page 156] SAP DB software [Page 158] SAP DB support [Page 158] 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 132] Database instance type [Page 132] SAP DB versions and database instance types [Page 24] Operating system platforms [Page 25] Multiprocessor configuration [Page 144] See also: Directory structure of the database for Open Source [Page 74] Directory structure of the database for SAP Systems [Page 67] Security concepts [Page 37] Data management using B* trees [Page 101] Database tools [Page 136] 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 132], see also: SAP DB Versions and Database Instance Types [Page 24]). Every database instance consists of threads [Page 151], main memory structures (caches [Page 129]) and volumes [Page 153]. Threads Database instance Caches Volumes The following volumes are available for each database instance: · · One or more data volumes [Page 130] One or more log volumes [Page 144] Each database instance differentiates between the following areas for the logical storage of data: · · Database catalog [Page 131] Application data [Page 125] Thread A whole series of operating system threads (often referred to as kernels) belong to a database instance [Page 132]. 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 153] used, and the database parameters that were set. · · · User Kernel Thread (UKT) [Page 14] Special Thread [Page 17] Operating-System-Dependent Special Thread [Page 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 15] User Manual: SAP DB 14 SAP AG · · · · · · Log Writer [Page 46] Server Task [Page 16] Timer Task [Page 16] Trace Writer Task [Page 16] User Task [Page 16] Utility Task [Page 16] November 2002 Data Writer Data writers are user kernel threads (UKT) [Page 14]. Data writer tasks are responsible for writing data from the data cache [Page 130] to the data volumes [Page 130]. They become 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 81]) and the number of data volumes (MAXDATAVOLUMES [Page 85]). 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 45] is assigned to the log writer. The log writer writes the log pages [Page 46] that are full, or have to be written as a result 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 44] into the log queue are stopped. 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. · · 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 130] [Page 130] and the 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. 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 86] database parameter. 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 132]. 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 126]. This can be performed in parallel with other management actions. User Manual: SAP DB 16 SAP AG November 2002 Special Thread A database instance [Page 132] has the following special threads [Page 151]: · · · · · Coordinator [Page 17] Dev Threads [Page 17] Requester [Page 18] Temporary Dev Threads [Page 18] Timer [Page 18] Coordinator The coordinator is a special thread [Page 17]. The coordinator monitors all kernel threads in the database instance [Page 132]. 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. Dev Thread Dev threads are special threads [Page 17]. Dev threads handle the read and write commands that read and write tasks [Page 151] ask to have performed. The number of dev threads is primarily dependent on the number of volumes [Page 153] in 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 145]. If the database is enlarged while running by adding another data volume, dev0 ensures that new dev threads are generated. · All the other dev threads write data to or read data from the volumes. 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) (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 18] Console Thread [Page 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 Requests from the XCONS console 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 132] messages from the other threads and logs these in a file called UNIX The XCONS console receives the necessary information directly from the shared memory of the threads. knldiag file The knldiag file is created. Each thread writes information to this file. User Manual: SAP DB 18 SAP AG knldiag. November 2002 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 19] I/O Buffer Cache [Page 138] Data Cache [Page 130] Converter [Page 129] Log Queue [Page 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 81] and is managed via the page pool [Page 146]. 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. 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 84]. A transaction [Page 152] uses a log queue to obtain a main memory area for a redo log entry [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 142] is carried out by the log writer [Page 46]. Process Flow 1. The user task [Page 16] of the transaction reserves main memory space for a redo log entry in the log queue. 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