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.
SAP AG November 2002 Icons Icon Meaning Caution Example Note Recommendation Syntax Typographic Conventions Type Style Description 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. Example text Emphasized words or phrases in body text, titles of graphics and tables. EXAMPLE TEXT Names of elements in the system.
SAP AG November 2002 User Manual: SAP DB ............................................................................................................. 13 Architecture of the Database System .................................................................................. 13 Database Instance ........................................................................................................... 14 Thread...................................................................................................
SAP AG November 2002 User Concept ....................................................................................................................... 25 SAP DB User Classes...................................................................................................... 26 Database Manager Operator (DBM Operator) ............................................................. 26 Authorizations ...........................................................................................................
SAP AG November 2002 Log Entry .......................................................................................................................... 44 Redo Log Entry............................................................................................................. 44 Undo Log Entry............................................................................................................. 44 Online Logging ....................................................................................
SAP AG November 2002 SAP DB Loader ................................................................................................................ 63 Options (LOADERCLI).................................................................................................. 64 Loader Commands ....................................................................................................... 64 SQL Studio: Introduction ..........................................................................................
SAP AG November 2002 DATE_TIME_FORMAT .................................................................................................... 81 DEADLOCK_DETECTION............................................................................................... 81 DEFAULT_CODE............................................................................................................. 81 DEVNO_BIT_COUNT ......................................................................................................
SAP AG November 2002 _MAXEVENTS ................................................................................................................. 89 _MAX_MESSAGE_FILES................................................................................................ 89 _ROW_RGNS .................................................................................................................. 90 _TAB_RGNS .........................................................................................................
SAP AG November 2002 Optimistic Lock ........................................................................................................... 115 Requesting and Releasing a Lock ................................................................................. 115 Isolation Level ................................................................................................................ 116 Isolation Level 0.............................................................................................
SAP AG November 2002 Database Manager Operator (DBM Operator)............................................................... 134 Database Name ............................................................................................................. 135 Database Parameters .................................................................................................... 135 Database Session ..........................................................................................................
SAP AG November 2002 SAP DB E-Catalog ......................................................................................................... 148 SAP DB Loader .............................................................................................................. 148 SAP DB OLAP................................................................................................................ 149 SAP DB OLTP .............................................................................................
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.
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].
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
SAP AG November 2002 The following user data is needed to log on: userid User name password The user’s password database_name The name of the SAP DB instance you want to work on server_node The name of the server node on which the database you called is running In addition to this generally required user data, you can enter further data, which is then transferred when you log on.
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.
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] Accessing database parameters (checked write) ParamFull [See SAP DB Library] Accessing database parameters (read and write) ParamRead [See SAP DB Library] Accessing database parameters (read-only) AccessSQL
SAP AG • DBA [Page 137] • DOMAIN [Page 137] • RESOURCE [Page 146] • STANDARD [Page 30] November 2002 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.
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.
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.
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 ansi c -E cansi c++ -E cplus check nocheck check syntax -H nocheck -H syntax comment -o compatible -C datatime datetime datetime datetime -D -D -D -D eur iso jis usa eur iso jis usa Default -H check -D internal dblocale -x extern -e help -h isolation-level -I list -l margins -m , -m 1,
SAP AG November 2002 version -V 32 Bit Support -BIT32 64 Bit if available SDK Version -MmCC M=Major m=Minor C=Correctionlevel 7401 The following user specifications as options [Page 31] should be transferred: 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.
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 132] with that user data, the SAP DB tools will react as follows: • DBMGUI [Page 134], SQL Studio [Page 65] 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.
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.
SAP AG USERKEY November 2002 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.
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 35]. 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.
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 74] Directory Structure of the Database System for SAP Systems [Page 67] Security Requirements Using the appropriate hardware, operating system, or database features can improve the availability [Page 38] of a database instance [Page 132].
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.
SAP AG November 2002 Backup The following backups should be carried out for the SAP DB database system at regular intervals: • Data backup [Page 131] • Log backup [Page 142] 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 138]. The naming convention for a backup medium [Page 127] depends on whether you use an external backup medium [Page 138].
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 40] are backed up to the backup medium [Page 127] you specified. See also: Backup Strategy [Page 39] Saving Data Backups [Page 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.
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.
SAP AG November 2002 You can perform interactive log backups in operating mode [Page 145] ONLINE or ADMIN. You cannot perform any other backup while the interactive log backup is running. See also: Backup Strategy [Page 39] Saving Log Backups [Page 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.
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 44] • Undo log entry [Page 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.
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 44] of the transactions are managed: Undo Log Management [Page 47] • For the database instance type liveCache [Page 140], History Management [Page 48] is carried out for the undo log entries. Redo Log Management During online logging [Page 44], the redo log entries [Page 44] are written and managed.
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.
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.
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 44] are written. Every undo log entries 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].
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 131] on each savepoint [Page 149]. In the case of a restart [Page 146], the history list is rebuilt on the basis of the data stored in the data area.
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].
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 49]. 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.
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 50] and the state of all open redo transactions are saved to the data area [Page 131], and reused for the next restart. In this way, a terminated restart can be started again. Example: Restart The database crashed.
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 53] • The following SAP DB tools are described in more detail: Database Manager [Page 133] SAP DB Loader [Page 148] SQL Studio [Page 65] 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.
SAP AG November 2002 Architecture • Architecture of the Database Manager [Page 54] • Architecture of the SAP DB Loader [Page 55] • SQL Studio Architecture [Page 56] • Architecture of the SAP DB Web Tools [Page 57] Architecture of the Database Manager The Database Manager [Page 133] has a client/server architecture.
SAP AG November 2002 Database Manager (DBMGUI, DBMCLI) Client computer Server computer X Server 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.
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.
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) Web SQL (HTML) SQL Studio and Web SQL Web Browser GUI (VB,C++) Web Server Web SQL Service ODBC SAP DB (Linux, UNIX, Win NT/2000) See also: Architecture of the SAP DB Tools [Page 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.
SAP AG November 2002 WWW WWW HTTP HTTP SAP DB WWW Web Server Server A Web Tools SAP DB WWW Web Server Server A Web Tools Server B SAP DB SAP DB 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.
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].
SAP AG November 2002 Server/Client for the Database Manager Server Client DBM Server [Page 58] DBMGUI [Page 134] DBMCLI [Page 133] Web DBM [Page 154] A script interface is available. See also: Architecture of the Database Manager [Page 54] Architecture of the SAP DB Web Tools [Page 57] Database Manager GUI The Database Manager [Page 133] has a user-friendly graphical user interface, the Database Manager GUI (DBMGUI).
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.
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.
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 Database Manager GUI [Page 134]. Operation depends on the Web-based application.
SAP AG November 2002 Server/Client for the SAP DB Loader Server Client Loader [Page 59] LOADERCLI A script interface (for example to Perl and Python) is available 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] Calling with LOADERCLI loadercli [] –b When you call the SAP DB Loader with the LOADERCLI [See SAP DB Library], you can specify options [Page 64], commands, and SQL statements.
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] (Option -b [See SAP DB Library]). 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.
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.
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.
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 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).
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.
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 132], then every database instance should be installed on a separate computer. This will allow you to deal with possible performance problems more easily.
SAP AG November 2002 programs that are dependent on the database software version [Page 72] /sapdb//data Directory for data volumes [Page 130] /sapdb//log Directory for volumes /sapdb//log • Log volumes [Page 144] • Mirrored log volumes Directory for security files • Backups of log entries • Backups of data See also: Example: SAP DB Directory Structure [Page 73] Display SAP DB Directories [Page 73] Define SAP DB Directories [Page 74] Instance Data
SAP AG November 2002 The programs that are independent of the database software version are stored in the IndepProgPath directory: . In this directory and its subdirectories, you will always find the programs for the most recently installed version of the database software.
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 AG November 2002 Directory name DBMCLI command 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 dbm_putpath IndepDataPath
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].
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.
SAP AG November 2002 Example Configuration ... 03 DB data Data DISKD01 Volume DISKD01 ... 02 ... A5 DB Logdata Volume DISKD01 DISKLA1 ... A2 ... B5 Mirrored DB data Log Volume DISKD01 DISKLB1 ... B2 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.
SAP AG November 2002 /wrk/ • Parameter files • Files for user authorization Run directory of the database instance • 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
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 dbm_putpath IndepDataPath dbm_putpath IndepProgPath inst_reg Database Parameters To initialize the database parame
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] CAT_CACHE_SUPPLY [Page 81] DATE_TIME_FORMAT [Page 81] DEADLOCK_DETECTION [Page 81] DEFAULT_CODE [Page 81] DEVNO_BIT_COUNT [Page 81] JOIN_MAXTAB_LEVEL4 [Page 82] JOIN_MAXTAB_LEVEL4 [Page 82] JOIN_SEARCH_LEVEL [Page 83] KERNELDIAGSIZE [Page 83] LOG_BACKUP_TO_PIPE [Page 83] LOG_IO_QUEUE [Page 84] LRU_FOR_SCAN
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].
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.
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).
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].
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 130] by using more than one file or tape device in parallel. MAXCPU The general database parameter [Page 79] MAXCPU denotes the maximum number of CPUs allowed on the database instance [Page 132].
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 151] can attempt to access a critical section.
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.
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.
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 135] can remain inactive before being timed out. If no SQL statement is issued within the specified time, the database system terminates the database session concerned (ROLLBACK WORK RELEASE statement).
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.
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.
SAP AG November 2002 Setting Database Parameter _UNICODE In order to install a UNICODE-enabled database [Page 91], the database parameter _UNICODE [Page 90] must be set to YES. Please note that you cannot change database parameter _UNICODE once it has been set. 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.
SAP AG November 2002 Column definition Result CHAR (n) UNICODE UNICODE column CHAR (n) UNICODE column CHAR (n) ASCII ASCII column CHAR (n) BYTE Code neutral, i.e. the column values are not converted by the database system 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.
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.
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.
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.
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.
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 , -d -n ] HelloUnicodeDB 3. linking the embedded SQL program HelloUnicodeDB.
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 */ st
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.
SAP AG November 2002 * END OF CODE * *=================================================================== */ Data Management Using B* Trees The SAP DB data management architecture ensures efficient data storage on disks and fast data access. The SAP DB database system performs automatic load balancing, thereby making reorganization unnecessary. The data areas [Page 131] can be extended online.
SAP AG November 2002 Secondary Key Secondary keys can be defined for each table to optimize the data access via SQL statements. They can refer to any column combination and they help to prevent sequential scans over the table. Like the primary key [Page 101], the secondary key can consist of multiple columns. A secondary key is often called an index (not to be confused with B* index). B* Tree All SAP DB data is stored in structures called B* trees.
SAP AG November 2002 B* Trees for Tables [Page 104] Root/Index Page Entries in root pages and index pages in a B* tree [Page 128] contain two sections. • First section: This section contains an initial segment of the key fields of a table row. This section is called the separator. SAP DB uses only this part of the primary key [Page 101], which is required to distinguish the subsequent entries, thereby minimizing the storage space used in these pages.
SAP AG November 2002 See also: B* Trees for Tables [Page 104] Table Access Using a B* Tree [Page 107] Table ID The database user [Page 136] specifies a table by entering its name. The table is accessed internally using a table ID of a fixed length. The relationship between the table name and this table ID is stored in the database catalog [Page 131].
SAP AG November 2002 B* Trees for Table with LONG Columns Long-Felder Primary Table K1 K2 K3 K4 10 4000 32000 568000 Longid 1 Longid 2 Longid 3 Longid 4 Table for short LONG Data One Table for Each long LONG Data Longid 1 L-Data Longid 2 L-Data L-Data L-Data The primary table contains a column of data type LONG. The numerical column defines the length of the LONG field for each line.
SAP AG November 2002 B* Trees for Tables with Secondary Key Invertierungen (Index) Primary Table K1 K2 K3 K4 K5 K6 K7 10 20 10 20 10 20 40 10 10 10 40 10 40 30 Secondary Key Table 1010 2010 2040 4030 K1 K3 K5 K2 K4 K6 K7 The table has a two-column multiple secondary key. There is one B* tree for the primary data and a second B* tree for the data of the secondary key table.
SAP AG November 2002 B* Trees for Tables with LONG Columns and Secondary Key B*-Bäume Primary Data Tree K I Secondary Key Tree L short LONG Tree long LONG Trees K: Key I: Index L: Long Table Access Using B* Tree For all SELECT and DML (data manipulation language) statements, the database system uses the same search algorithm to determine the leaf page [Page 103] in which a table entry can be found or has to be changed.
SAP AG November 2002 Table Access (SELECT) using B* Tree (1) SELECT SELECT FROM FROM address address WHERE WHERE city city == 'Athens' 'Athens' Root Level Ba Wa Athens Athens << Ba Ba ?? Yes ⇒ Yes ⇒ Follow Follow leftmost leftmost address address An Au Athens Athens << Au Au ?? Yes ⇒ Yes... ⇒ ... Bi Az Follow Baf Follow address address ‘An’ ‘An’ Index Level Waf ... .. Zu Leaf Level Aalen .. Amiens Anizy Anizy .. Athens Auber .. Avon |Greece |France Anizy ... ..
SAP AG November 2002 Table Access (SELECT) using B* Tree (2) Data Entries (unsorted) Anizy |....... Ardwick|....... Athens Apach |Greece |. Ap- Sort Order: ensen|..... Arnhem |... SAP DB Data Page Anizy |....... Apach |. Apensen|..... 2 6 3 5 4 1 Ardwick|........ Arnhem |... Anizy .. Athens Position List (sorted) Athens |Greece 3. The search continues at the leaf level. In the position list of the leaf page a binary search algorithm is used.
SAP AG November 2002 Table Access (INSERT) Using B* Tree INSERT INSERT INTO INTO address address SET SET city city == 'Arbon' 'Arbon' Arbon Anizy |....... Ardwick|....... Athens Apach |Greece |. Ap- ensen|..... Arnhem |... Arbon ..... |....... SAP DB Data Page | 7 Anizy |....... Apach |. Apensen|..... Arbon |....... Ardwick|........ Arnhem |... 2 6 3 5 4 1 Athens |Greece The address table is defined via the city primary key [Page 101] column.
SAP AG November 2002 Table Access (DELETE) Using B* Tree DELETE DELETE FROM FROM address address WHERE WHERE city city == 'Apach' 'Apach' Anizy |....... Athens |Greece Ardwick|....... Apach Ap- ensen|..... Arnhem |... Arbon ..... | |. 4 SAP DB Data Page 2 6 3 7 5 1 Anizy Athens |....... |Greece Apensen|..... A Ardwick|....... rnhem |... Arbon |....... SAP DB Data Page Close Gaps 2 6 3 7 5 1 The address table is defined via the city primary key [Page 101] column.
SAP AG November 2002 Changes in the B* Tree Structure Root Level Leaf Level An Aalen INSERT INSERT INTO INTO address address SET SET city city == 'Albas' 'Albas' Au Amiens Al An Anizy Au Athens Root Level Leaf Level Aalen Akkrum Albas Amiens Anizy Athens The address table is defined via the city primary key [Page 101] column. In the address table, insert an entry with the value Albas for the city primary key field.
SAP AG November 2002 procedure, page entries are moved to new locations and page pointers are redirected. As a result, data pages are used more efficiently. Uniform distribution of data prevents individual data regions from overflowing. The only restriction on the size of tables is the storage space available in the database system. Lock Behavior The database system SAP DB allows concurrent transactions [Page 152] to use the same database objects.
SAP AG November 2002 A transaction has a E lock S lock On a table E lock S lock On a row E lock S lock On the database catalog Request an E lock for the table? No No No No No Yes Request an S lock for the table? No Yes No Yes No Yes Request an E lock for any line of the table? No No --- --- No Yes Request an E lock for the locked row? --- --- No No --- --- Request an E lock for another row? --- --- Yes Yes --- --- Request an S lock for any row of the table? No Yes
SAP AG November 2002 Once a shared lock is assigned to a transaction [Page 152] for a particular data object, concurrent transactions can access the object but not modify it. Other transactions can set a shared lock, but not an exclusive lock [Page 115] for this object. Shared locks refer to a row or a table. See also: Requesting and Releasing a Lock [Page 115] Exclusive Lock One type of lock [Page 140] is the exclusive lock.
SAP AG November 2002 Request Locks • Request locks implicitly The lock mode can be determined by specifying an isolation level [Page 139] in the CONNECT statement [See SAP DB Library]. In this case, the database system requests locks implicitly during processing of an SQL statement in accordance with the specified isolation level. These locks are then assigned to the transaction [Page 152] that contains this SQL statement.
SAP AG November 2002 • A high degree of concurrency is characterized by a state in which a maximum number of concurrent transactions [Page 152] can process a database without long waiting periods for locks to be released. • As far as consistency is concerned, phenomena [Page 119] can arise through concurrent access to the same database. The lower the value of the isolation level, the higher the degree of concurrency and the lower the guaranteed consistency.
SAP AG November 2002 Isolation Level 15 The following rules apply if isolation level [Page 139] 15 is specified: For all SQL statements, the behavior described for isolation level 1 or 10 [Page 117] also applies for isolation level 15: The only difference is that, with isolation level 15, shared locks [Page 114] are requested for all the tables addressed by the SQL statement before processing starts.
SAP AG November 2002 Phenomena During processing of concurrent transactions [Page 152], unclarified situations, or “phenomena” can arise. When you define the lock behavior [Page 113] in your database system, you should take account on the ways of avoiding these phenomena.
SAP AG November 2002 UPDATE statement to create at least one additional row that satisfies the search condition. If S is subsequently re-executed within T1, the set of read rows will differ from M. SAP DB Version 7.4 Development of the independent SAP DB software was begun in 1997 on the basis of version 6.1. Since then, SAP AG has significantly further developed and improved the SAP DB technology.
SAP AG November 2002 You can make additional clients available using a script interface to Perl or Python. Entering SQL Statements You can use SQL Studio to enter SQL statements, such as data requests. You can use the following clients: • SQL Studio on Windows-based operating systems • Web SQL Unloading and Loading of Data You can use the SAP DB Loader to unload and load data.
SAP AG November 2002 Number of join tables in a SELECT statement 64 Number of triggers per Basis table 3 Number of indexes per table 255 Number of referential CONSTRAINT definitions (foreign key dependencies) per table unlimited Number of references per table unlimited Number of rows per table limited by database size Internal length of a table row 8088 bytes Total of internal lengths of all key columns 1024 bytes Total of internal lengths of all columns belonging to an index 1024 bytes
SAP AG November 2002 Parallel converter queries • Multi-region converter: Converter queries can be made to multiple critical regions in parallel Improved log management Separation of before and after images • Before images are stored in transaction-specific undo log files • After images are stored in the log volume Checkpoints are obsolete • Each database backup is consistent, even without the entries in the log volume • The database can be started when the log is missing Parallel archiving of l
SAP AG November 2002 Data area [Page 131] Data backup [Page 131] Database administrator [Page 131] Database catalog [Page 131] Database instance [Page 132] Database instance type [Page 132] Database Manager [Page 133] Database Manager CLI [Page 133] Database Manager GUI [Page 134] Database Manager operator (DBM operator) [Page 134] Database name [Page 135] Database parameters [Page 135] Database session [Page 135] Database trace [Page 136] Database tools [Page 136] Database user [Page 136] DBA [Page 137]
SAP AG November 2002 Operating state [Page 145] Page [Page 146] Page pool [Page 146] Parallel backup [Page 146] RESOURCE [Page 146] Restart [Page 146] ROLLBACK [Page 147] Run directory [Page 147] SAP DB Document Server [Page 147] SAP DB E-Catalog [Page 148] SAP DB Loader [Page 148] SAP DB OLAP [Page 149] SAP DB OLTP [Page 149] SAP DB user classes [Page 149] Savepoint [Page 149] Session [Page 150] Single backup medium [Page 150] SQL lock [Page 150] SQL mode [Page 150] SQL Studio [Page 150] SYSDBA [Page 151
SAP AG November 2002 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 142] overflow is almost impossible.
SAP AG November 2002 Backup ID The database system automatically gives each backup a backup ID to allow them to be identified. This ID uniquely identifies the backups done since the creation of the database instance. If external backup tools are used for the backup, the backup is given an external backup ID [Page 137]. The backup ID consists of the type of backup (complete or incremental data backup [Page 131] or log backup [Page 142]) and a sequential number.
SAP AG November 2002 Naming When names are assigned, we differentiate between standard backup media [Page 145] and backup media for external backup tools [Page 145]. B* Tree All SAP DB data is stored in structures called B* trees. The B* tree method is far more efficient for accessing the rows of a table than other access methods (for example, sequential scans).
SAP AG 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.
SAP AG November 2002 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.
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] Data Area The total of all data volumes [Page 130] is called the data area. In the data area, there must always be sufficient space to hold all the data that arises during database operation. Data Backup In a data backup, the contents of the data volume [Page 130] are backed up.
SAP AG November 2002 The database catalog is made up of a number of sections. One section consists of the information about the installation of the database system and the metadata with the definitions of users and user groups. This section is not assigned to any user or user group. For each user or user group, the catalog contains a section in which the metadata of the objects generated by this user or user group is stored. This includes metadata on Basis tables, view tables, and so on.
SAP AG • November 2002 SAP DB E-Catalog [Page 148] See also: SAP DB Versions and Database Instance Types [Page 24] 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.
SAP AG November 2002 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 . In this case, when you call the Database Manager CLI, you can enter option -i in addition to the required options. See also: Database Manager CLI: SAP DB 7.
SAP AG November 2002 DBM operators are not database users [Page 136]. You need to create database users in order to work on a database instance. Database Name In principle, the name of a database instance [Page 132] is freely definable, but must meet the following conditions: • The maximum length of a database name is 8 characters. • The database name can contain the characters from the 7-bit ASCII character set. • The database name can start with a letter or a number.
SAP AG November 2002 If the user does not belong to a user group, the “Current User” is the user name they entered to gain access to the database instance. If, on the other hand, the user does belong to a user group, the “Current User” is name of the user group. See also: User Concept [Page 25] Database Trace You can enable the writing of a database trace. In this case, the trace writer task [Page 16] logs all reactions of the database kernel to database statements in the database trace.
SAP AG November 2002 Database users can be grouped into user groups [Page 30]. 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.
SAP AG November 2002 The external backup ID is written to logs dbm.knl (backup history [Page 126]) and dbm.ebf during backup. The external backup ID is used during a restore to request the desired backup. The external backup ID is displayed before the restore operation, and can be corrected if necessary. To do this, the Database Manager [Page 133] transfers the external backup ID to the DBM Server as a command parameter, and the DBM server forwards this to the external backup tool.
SAP AG November 2002 Instance Type See Database instance type [Page 132] 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.
SAP AG • November 2002 Isolation Level 3 or 30 [Page 118] (serializable) Kernel The threads [Page 151] of a database instance [Page 132] are often referred to as the kernel. Language Support (MapChar Sets) A MapChar set is a character string that maps language-specific characters to the ASCII code or EBCDIC code. This character set is only of any significance for internal system functions. MapChar sets were introduced to allow letters to be converted.
SAP AG November 2002 • Exclusive lock [Page 115] • Optimistic lock [Page 115] • Special locks for he metadata of the database catalog [Page 131], which must, however, always be explicitly assigned. The following table provides an overview of the possible parallel shared locks (S locks) and exclusive locks (E locks).
SAP AG November 2002 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 144]. 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.
SAP AG November 2002 Backup Strategy [Page 39] Saving Log Backups [Page 43] Log Mode By specifying the log mode, you can determine how log entries are backed up: • SINGLE: Log entries are saved to a single log area [Page 142] • DUAL: Log entries are saved to two log areas in parallel SINGLE In the log mode SINGLE, the database instance uses only one log area. The log area cannot be overwritten until the log entries have been backed up, that is to say, a log backup [Page 142] has been carried out.
SAP AG November 2002 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.
SAP AG November 2002 The aim here is to allow the maximum possible number of database sessions [Page 135] to 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 132]. No more than 800 database sessions should be running simultaneously.
SAP AG November 2002 • ONLINE: The database instance has been started, and users can log on. • ADMIN: The database instance is only available to administrators for administrative work. • OFFLINE: The database instance is not running. Page A page is a basic physical unit of disk input and output, that is, a page corresponds to a certain number of blocks. Storage and disk space is often measured in pages. For the SAP DB database system, the page size is fixed at 8 KB.
SAP AG November 2002 2. All the transactions that were still open at the time of the savepoint are determined using the transaction list. 3. The transactions are processed in the way described in Restart or Recovery [Page 49]. See also: Restartability [Page 38] Example: Restart [Page 52] ROLLBACK In a ROLLBACK, all the changes made by a transaction [Page 152] or a subtransaction [See SAP DB Library] on the database instance [Page 132] are reversed.
SAP AG November 2002 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.
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.
SAP AG November 2002 Session See Database session [Page 135] Single Backup Medium You have the option of defining single backup media [Page 127]. The database system performs its backup [Page 126] to this medium. Where the capacity of the backup medium is too small for the backup that has started, the system asks for a succeeding medium.
SAP AG November 2002 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.
SAP AG November 2002 Transaction A transaction is a series of database operations that form a unit with regard to data backup and synchronization. Transactions are closed with COMMIT [Page 129] or ROLLBACK [Page 147]. See also: Reference Manual: SAP DB 7.
SAP AG November 2002 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 91] Reference Manual: SAP DB 7.
SAP AG November 2002 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. 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 Database Manager GUI [Page 134]. Operation depends on the Web-based application.
SAP AG November 2002 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 57] Prerequisites Check if the database instance is started.
SAP AG November 2002 If several SQL statements were executed, you can access the results and the messages relating to these SQL statements with a drop-down list. 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.
SAP AG • November 2002 CCMS documentation: Choose SAP R/3 → SAP R/3 Release → → Basis Components → Computing Center Management System → Computing Center Management System → Database Administration in CCMS → SAP DB - DBA in CCMS. You will find the installation guide for SAP solutions in the SAP Service Marketplace (http://service.sap.com). Choose Installation/Upgrade Guides → .
SAP AG November 2002 SAP DB Software You can obtain the SAP DB software free of charge: • SAP DB-Homepage: http://www.sapdb.org You can download the latest SAP DB version from the SAP DB homepage. • Software CD: material number 50056472 You can order this CD in the SAP Shop: http://www.sap.com/company/shop • SAP installation package SAP customers receive the SAP installation package. This installation package also includes the SAP DB software.
