MaxDB™ FAQ

This document is a collection of frequently asked questions and their answers. Most questions are collected from the MaxDB mailing list run by MySQL AB and from the service work of MySQL and SAP. Annotations and corrections to this FAQ are welcome.

This FAQ is distributed under the same conditions as the MySQL Server manual.

The copyright (2005) to the MySQL Server manual is owned by the Swedish company MySQL AB. The copyright (2005) to this FAQ is owned by MySQL AB and SAP AG. MySQL and the MySQL logo are (registered) trademarks of MySQL AB. Other trademarks and registered trademarks referred to in this manual are the property of their respective owners, and are used for identification purposes only.

The most recent version of the FAQ is available on http://www.mysql.com.

You can download a packed HTML version and a PDF version.

We recommend that you check the FAQ regularly to be informed about the most recent changes.

This FAQ is on MaxDB 7.5.00.19 and above. Most information given in it is applicable to any 7.5 build.

Please understand that we needed a starting point for this FAQ. We have chosen 7.5 for this because it's the recommended production version at the time of writing the first version of this FAQ.

Many things have changed from 7.3 to 7.5 and even older versions. The official manual does not mention all of them. You can use the WebPTS to get a list of Changes between two versions. Use the search form on "My Release Notes" to find out about changes. It will show you more than 4.500 improvements between 7.4.03.00 and current versions.

Examples given in this FAQ might not work on other versions than 7.5. We know that many people still work with 7.3 and we recommend that you do not update your working installations unless there is a strong demand for a feature or bug fix that is only available in a more recent version.

Our current milestone is on writing a FAQ with the most needed questions and answers to create "cookbook"? style documentation. Once that is done, we can add notes for older version.

Our MaxDB team has limited human resources and we appreciate your help very much!

MaxDB is an ANSI SQL-92 (entry level) compliant relational database management system (RDBMS) from SAP AG, which is delivered by MySQL AB as well. MaxDB fulfills the needs for enterprise usage: safety, scalability high concurrency and performance. It runs on all major operating systems. Over the years it has proven able to run SAP R/3 and terabytes of data in 24x7 operation

The database development started in 1977 as a research project at the Technical University of Berlin . In the early 80s it became a database product that subsequently was owned by Nixdorf, Siemens Nixdorf, Software AG and today by SAP AG. Along this line it has been named VDN, Reflex, Supra 2, DDB/4, Entire SQL-DB-Server and ADABAS. In 1997 SAP took over the software from Software AG and renamed it to SAP DB. Since October 2000 SAP DB sources additionally were released as open source under the GNU General Public License. In 2003 SAP AG and MySQL AB joined a partnership and re-branded the database system to MaxDB.

SAP DB is the former name of MaxDB. As a consequence of the cooperation SAP DB was re-branded to MaxDB in 2003. The development of MaxDB has continued since then as it was done before through the SAP developer team.

ADABAS D is a relational database distributed by Software AG. ADABAS D and MaxDB have a common history, see also "What is MaxDB?". In 1997 SAP DB was spun off from ADABAS D version 6.1.15.57.

MySQL is the world's most popular open-source database. Over six million installations use MySQL to power high-volume Web sites and other critical business systems.

Open Source means that the software sources are freely visible to everybody. Using and changing the sources for own purpose underlies a certain license. MaxDB is released under the Free Software/Open Source GNU General Public License (GPL). Open Source does not implicitly mean that the usage of the product is free. It could be for non-commercial use whereas you have to follow the GPL condition. For MaxDB, you can choose between a commercial license and an open source license. Check MySQL Licensing Policy for details.

No, MaxDB database systems are suitable for thousands of users and data volumes up to 32 terabytes (standard compilation). Over the past years we have been investing constantly in the enhancements of automatic space management so that MaxDB requires even less disk space than other database systems in certain environments up to 40% less.

Yes. All backup activities and the most important maintenance tasks can be executed online. As MaxDB because of its automatic space management concept doesn't have any need for reorganization measures, downtimes for this purpose are not required at all.

The main source of information is the MaxDB documentation. It is available for online reading and download at http://dev.mysql.com/doc/maxdb/en/default.htm and for download at http://dev.mysql.com/downloads/ maxdb/7.6.00.html#docul. If you are working on Windows, we recommend that you download the .chm version (HTMLHelp) of the documentation, which provides a very good full text search.

The MaxDB documentation is version dependent. Previous versions are available for download from the MaxDB archive at http://dev.mysql.com/downloads/ maxdb/archives/archive_index.html

The MaxDB documentation web page at http://dev.mysql.com/doc/maxdb/index.html provides additional information, including links to presentations and articles.

Several other online resources exist:

We recommend the following books for general database topics.

SQL basics

The main source of information is the MaxDB documentation. It is available for online reading at http://dev.mysql.com/doc/maxdb/en/default.htm. To download the documentation, go to http://dev.mysql.com/downloads/ maxdb/7.6.00.html#docu. If you are working on Windows, we recommend that you download the .chm version (HTMLHelp) of the documentation, which provides a very good full text search.

We have added links to the MaxDB Glossary to most answers. The glossary is a good quick-start point if you're looking for a specific topic as you don't have to navigate through the manual but will find direct links.

Yes, there is a list <maxdb@lists.mysql.com> where users can help each other. You can subscribe to it on lists.mysql.com. Many of the core developers are subscribed to the list and are doing an excellent job in helping users. However, note that this is not an official support channel; It is for users helping users.

The mailing list is also the first address to get notified about new developments. All announcements on new software versions, new tools, new documentation of any kinds and events are announced on the list.

A mailing list archive is available on http://lists.mysql.com/maxdb.

Please do not use this forum for bug reports. Use http://bugs.mysql.com/ instead.

Yes, both SAP and MySQL offer a forum. For questions not related to SAP applications, you can use the MySQL MaxDB forum on http://forums.mysql.com. The forum contained in the SAP Developer network is the correct place for MaxDB questions related to SAP applications.

MySQL AB and SAP both offer commercial support.

SAP offers support contracts for MaxDB in combination with SAP application contracts. You are entitled to SAP support through your maintenance contracts with SAP.

MySQL AB offers MaxDB support for all other cases. The level of support ranges from per incident to 24x7. More information is available on the webpage MySQL Technical Support .

MySQL offers two courses on MaxDB in regular intervals. One course is named "Using and Developing with MaxDB". The other course is "MaxDB Database Administration". In-house trainings can be arranged. The courses are held in German or in English. Please check the webpage MySQL Training Courses for further information.

SAP provides courses on how to use MaxDB with SAP applications. Their abbreviations are: "ADM515 MaxDB Administration (Intermediate)", "WB550 MaxDB Internals (Expert)", "UMEW60 MaxDB Performance(Expert)", "TEWA60 liveCache Monitoring(Expert)". Please check the SAP Training Catalog.

Nothing, but you are SHOUTING. Try to stay relaxed and polite, even in an emergency situation. We will soon add a section "How to report problems" to tell you how to prepare your questions in an optimal way. The chances of getting a good and precise answer to a well-prepared question are much higher than the chances of getting an answer to a quick, unclear question.

If you're new to the internet, please check the Network Etiquette (Netiquette) on http://www.faqs.org/rfcs/rfc1855.html. It shows you basic examples of and rules for communicating on the internet. Check also http://learn.to/quote on how to quote messages. Sadly, few people know how to quote messages correctly. Incorrect quotes make it hard to follow a discussion.

However, generally speaking the spirit in the mailing list is very polite and we hope that everybody continues using common sense as the main rule.

MaxDB can be installed in four different ways: building from source, using the command line SDBINST tool, using RPM archives (Unix) or using the graphical installer SDBSETUP (MaxDB Installation Manager), beta version available since 7.5.00.19, non-beta as of 7.6.

Traditionally, the SDBINST and SDBUPD tols have been used for installing and upgrading MaxDB. These tools were developed long before the MaxDB RPM distribution and are proven to be stable

If you have a graphical system (Unix / Windows), you should try out the new GUI installation tool SDBSETUP, the MaxDB Installation Manager. It does not only install the necessary software but can be used to create an initial database instance as well.

Note: The SDBINST, SDBUPD and SDBSETUP tools are referred to as SDB* tools in the following.

Detailed installation instructions are given in the MaxDB manual in the part "Installation Manual". A short description for each installation procedure is contained in this FAQ.

Building from source is rather complex and we are recommending it only for experts. When building from source, you can set compile options different from the default ones and you can even start porting MaxDB to an operating system, that does not belong to the official platform set of MaxDB. See MaxDB Glossary : VMAKE.

Please note that neither MySQL nor SAP support will handle issues with self-built binaries. We simply cannot support a binary that has not passed our internal quality assurance tests. However, others users participating on the mailing list might have experience or interest in such issues and assist.

Despite being designed as an enterprise-class DBMS, the use of MaxDB is not restricted to multi-user server-based scenarios. As its ease-of-use capabilities are built in and not built around, it can also be used as a desktop or laptop DBMS with a very small footprint. If used as an enterprise class server, MaxDB needs a machine suitably equipped. Database severs in general need fast I/O interfaces and lots of main memory. A fast storage system is required to minimize the performance gap between persistent storage, main memory and CPU internal caches. Fast network interfaces are needed for the TCP/IP based communication between remote application clients and the 'X Server' communication component of MaxDB. Main memory is important to cache as much data as possible. Disk accesses are roughly 1.000 times slower than memory accesses.

The hardware you need depends very much on your application. Please understand that we cannot give you clear hardware recommendations, Feel free to ask users on the mailing list about their experiences and decisions they have made regarding hardware.

The best system varies with your needs. In general we recommend choosing the fastest and most secure system you can afford.

If you decide to go for a RAID system, do not choose a RAID level that is not optimized for fault tolerance. RAID 0 (striping) and RAID 5 (distributed parity information) are not optimized to handle disk failures. A failure of only one disk causes data lost on RAID 0. RAID 5 arrays fail if more than one disks crashes.

RAID 1 (mirroring) and RAID 10 (mirroring of striped disks) give you a higher protection from hardware failures. The RAID 1 level mirrors all disks. It does not give you a speed benefit and it comes at the cost that you need to buy 2 of every disk you want to add to the RAID. RAID 10 combines mirroring and striping. It gives you the security of mirroring (RAID 1) and speed benefit of striping (RAIDl 0). The disadvantage of RAID 10 is that you need even more disks than with RAID 1.

Hot Standby installations need specific hardware. The following hardware fulfils the Hot Standby requirements: EMC Symmetrix, IBM TotalStorage Enterprise Storage Server (ESS), IBM TotalStorage SAN Volume Controller (SVC). See also MaxDB Glossary , Hot Standby.

The following table shows you which hardware is supported by which version of MaxDB. Please check also which operating systems are supported (next FAQ item below)

For the availability of MaxDB on different platforms see also the availability matrix.

The main benefit of 64-bit hardware is the memory that can be allocated by a process. Most 32-bit systems can handle 4 GB, most 64-bit processors use at least 40 bits for addressing, which sets the limit to 1024 GB.

Main memory is crucial for the performance of a database. Data reads from harddisks and other permanent storage mediums are several hundred times slower than reads from the main memory. Database systems use large main memory caches to minimize the slowdown caused by disk access.

Every MaxDB instance runs as a single process. The total memory available for a process is limited to 2 GB on most 32-bit systems. As a rule of thumb you should assign 2/3 of your memory for I/O buffering. Experience has shown that the best CACHE_SIZE database parameter value is about 1.5 GB on a dedicated 2 GB box. 0.5 GB should be left for the MaxDB kernel, the X Server and the operating system. If you set the CACHE_SIZE to more than 1.5 GB, then the system might start swapping. Swapping is a worst-case situation with severe impact on system performance. Note: this is only a rule of thumb; it does not free you from testing your setup!

See also MaxDB Glossary , Database Parameters.

Version 7.5.00.31 is available on:

7.6 is available on the following platforms:

Please understand that we cannot port our product to every operating system and every hardware. We are limited to the systems most requested by our customers. Feel free to ask us and other users on experiences and hints in porting.

You should use MaxDB 7.5 or MaxDB 7.6, which as of build #10 has been released for production.

If you are using MaxDB with SAP solutions please follow SAP's release information and recommendations.

We do not recommend the use of any alpha/beta versions in a production environment.

Beta versions are frequently released before a stable version is released. Beta means that all major quality assurance tests have been successful on the most important operating systems and hardware platforms. We kindly request our users to try out beta version in un-critical environments and give us as much feedback as possible.

Alpha versions are made available infrequently. They are early-bird snapshots from the development work and subject to changes.

Yes, there are. Command line and graphical tools can perform all administrative tasks and SQL queries.

The graphical administration tool is called Database Manager GUI. It is only available on Windows. A new version that can run under other operating systems as well is on the long-term to do list. However, you are not forced to run Windows in order to be able to use graphical tool. The Web DBM is a web-based variant of the Database Manager and it offers the same functionality as the Windows tool. If you prefer command line interface tools, then you can use the last variant of the Database Manager dbmcli, which is also available on all platforms.

In SQL Studio, Web SQL and the sqlcli tool, you can enter SQL queries. The same restrictions apply to the SQL tools as to the Database Manager. The SQL Studio is only available on Windows, whereas the Web SQL web-based variant and the sqlcli command line interface are available on all supported platforms.

The following table shows how the graphical tools can be compared to tools of other database systems.

See also MaxDB Glossary , Database Tool

First of all: a RPM installation differs from an SDB*-tool based installation (SDBINST - command line installation tool distribution, SDBSETUP - Installation Manager GUI). The RPM distribution contains tools that may not be part of the SDB*-tool distribution and vice versa. Most of the files missing in the RPM distribution are required for SAP usage (R/3, NetWeaver and liveCache) or for backward compatibility with versions prior to 7.5.

Examples of files that are contained in the SDB*-tool distributions but not in the RPM distribution:

Examples of files that are contained in the RPM binaries but not in the SDB*-tool distributions:

When to choose the RPM installation, when to choose a SDB*-tool distribution? RPM is suitable for new installations of MaxDB. If you want to update old installations or if you are planning to use MaxDB with SAP applications, then you should go for a SDB*-tool based installation. SDB*-tool based installations can be performed unattended and they can be updated comfortably with SDBUPD.

The following RPM binaries consists the following packages:

You need to install at least maxdb-ind.rpm and maxdb-srv75.rpm (or whateverversion you need). RPM binaries are installed with rpm -Uhv. When you start your installation, you have to take care of the dependencies between the packages. You can't install the server before having installed the release independent files, and you can't install the web tools before you have installed the script language interface, because the web tools are based on Python. Use the order given in the list above to avoid problems.

See also MaxDB Glossary : Installation

The use of the command line SDB*-tools is the most common way to install MaxDB. The tools are proven to be stable and reliable. Download the most recent, stable version of MaxDB from http://www.mysql.com/products/maxdb/ (see also Which database version shall I use?) and unpack it to a local directory.

The following SDB*-tools belong to the installation package.

This section of the FAQ describes the installation using SDBINST. The examples are based on MaxDB Version 7.5.00.19. In order to run SDBINST you need root permissions. MaxDB will create a user account (Unix), install services and modify the registry during the installation, see What changes to the operating system have been made during the installation? for more details.

Run SDBINST. A list of available profiles is displayed and you are asked to choose between them.

Installation of MaxDB Software        
*******************************

starting installation Su, Nov 28, 2004 at 13:31:08
operating system: Windows I386 NT 5.1 Service Pack 2
callers working directory: C:/downloads/maxdb-all-win-32bit-i386-7_5_00_19
installer directory: C:/downloads/maxdb-all-win-32bit-i386-7_5_00_19
archive directory: C:/downloads/maxdb-all-win-32bit-i386-7_5_00_19

existing profiles:
0:   C Precompiler
1:   Runtime For SAP AS
2:   DB Analyzer
3:   JDBC
4:   Server
5:   Loader
6:   ODBC
7:   Script Interface
8:   Webtools
9:   XML Indexing Engine
10:  all
11:  none

please enter profile id:

(Example taken from version 7.5.00.19 on Windows XP. The output can differ on other versions and systems.)

For a complete installation choose the profile "all". In the example entering 10 will achive this. Follow the instructions given on the screen. You can safely agree to all default settings suggested by the tool.

After you have installed the software you can continue to create a database instance.

See also MaxDB Glossary : Installation

From Version 7.5.00.19 on, the graphical Installation Manager is the easiest way to install MaxDB. It has one major advantage for beginners over the command line variants: it can be used to install the software and an instance. The instance that is created is a good starting point for configuration tweaking.

Download the most recent, stable version of MaxDB from the http://www.mysql.com/products/database/maxdb/ download area (see also Which database version shall I use?) and unpack it to a local directory. In order to run SDBSETUP you need root permissions (UNIX, Linux) or administrator privileges (Windows). MaxDB will create a user account (Unix), install services and modify the registry during the installation process, see What changes to the operating system have been made during the installation? for more details.

Follow the instructions given on the screen.

See also MaxDB Glossary : Installation

The tools that are installed depend on the installation profile you have chosen. The most important ones are:

A detailed list is given in the manual. See also MaxDB Glossary : Database Tool

Most administrative tasks are performed using the Database Manager. Three variants of the Database Manager exist. The command line variant dbmcli and the web interface "Web DBM" are available on all supported platforms, whereas the most comfortable tool, the "Database Manager GUI" is only available on Windows. The limitation to Windows is not much of a problem as you can use it to access any MaxDB instance in the network. Future versions might lift the limitation to Windows.

The Database Manager suite covers all administrative tasks including creating and dropping instances, modifying instance settings, backing up and restoring data, displaying status information, log files and much more.

An SQL session can be opened with the command line tool sqlcli, the SQL Studio (Windows only) and the Web SQL Studio.

The xserver is a communication component that allows you to access an instance from a remote computer. The xserver and its clients communicate with each other on a TCP/IP port. If you use the xserver, make sure that the port is not blocked by security settings. MaxDB requires a port entry "sql6 7210"

If you do not want to use SQL Studio, you can also open an SQL session with the sqlcli tool: sqlcli -u <database_user>,<database_user_password> -d <database_name>, e.g. sqlcli -u MONA,RED -d DEMODB. A complete list of options can be obtained by executing sqlcli -h.

# sqlcli -u MONA,RED -d DEMODB
      
Welcome to the MaxDB interactive terminal.
      
Type:   \h for help with commands
          \q to quit
                  

sqlcli=> \h

\? or \h[elp]  show help on internal slash commands
\q[uit]        quit SQLCLI
\c[onnect]     -n <database_server>[:<port>]
              -d <database_name>
              -u <user_name,password>
              connecting to the database
\di[sconnect]  disconnecting from the database

\a[utocommit]  [ON|OFF] switch autocommit mode on or off
\m[ode]        [INTERNAL|ORACLE|ANSI|DB2] change SQL mode
\o[utput]      <filename> send all query results to file
\i[nput]       <filename> read input from the file <filename>

\p[rint]       print out the current query buffer
\r[eset]       reset (clear) the query buffer
\e[dit]        <filename>  edit the query buffer (or file) with external editor
\g             send query buffer to server (and print out the results)

\al[ign]       [ON|OFF] toggle the aligned output on or off
\f[ieldsep]    <separator> use <separator> as the field separator
\s[tatus]      print out host, database, user etc.
\!<command>    execute <command> in shell

\dc [PATTERN]  list columns
\de [PATTERN]  list indices
\dp [PATTERN]  list procedures
\dt [PATTERN]  list tables
\du [NAME]     list users
\dv [PATTERN]  list views
[PATTERN] = [OWNER.][OBJECT NAME] eg. <username>.%A%

sqlcli=> SELECT * FROM "DOMAIN"."VERSIONS"
| KERNEL                                   | RUNTIMEENVIRONMENT                       |
| ---------------------------------------- | ---------------------------------------- |
| Kernel    7.5.0    Build 019-121-082-363 | W32/INTEL 7.5.0    Build 019-121-082-363 |

1 row selected (56 msec)              

(Example taken from version 7.5.00.19)

Alternatively you can use the dbmcli sql_execute command.

dbmcli on DEMODB>sql_execute SELECT * FROM "DOMAIN"."VERSIONS"
OK
END
'Kernel    7.5.0    Build 019-121-082-363';'W32/INTEL 7.5.0    Build 019-121-082-363'							
     

# dbmcli -u MONA,RED -d DEMODB sql_execute "SELECT * FROM DOMAIN.VERSIONS"
OK
END
'Kernel    7.5.0 Build 019-121-082-363';'X32/LINUX 7.5.0    Build 019-121-082-343'

See also MaxDB Glossary : DBMCLI Command

MaxDB has a rich set of log and configuration files. They are all maintained automatically you do not have to modify them for operational purposes. You can display the available log files using the Database Manager GUI tools. Connect to an instance and select Diagnosis Files from Check to get a list of all available logfiles. Alternatively, you can execute the dbmcli command file_getlist to get a list of all database files. Note that the list contains log and configuration files.

# dbmcli -u DBADMIN,DBADMIN -d DEMODB file_getlist 1
OK
keyname,mode,size,date,time,comment,filename
KNLDIAG ASCII   819200  20041228        184517          Database Messages       knldiag
KNLDIAGERR      ASCII   899     20041226        155942          Database Errors knldiag.err
KNLDIAGOLD      ASCII   819200  20041226        155942          Database Messages (OLD) knldiag.old
KNLTRC  BINARY  1630208 20041228        184529          Database Trace  knltrace
UTLPRT  ASCII   102400  20041226        155942          Utility Statements      dbm.utl
BACKHIST        ASCII   570     20041213        130048          Backup History  dbm.knl
DBMPRT  ASCII   1379    20041228        184552          Database Manager Protocol       dbm.prt
DBMMDF  ASCII   180     20041213        130020          Database Manager Media  dbm.mmm
DBMPAHI ASCII   37472   20041213        130000          Database Parameter History      DEMO.pah
LCINITCMD       ASCII   16083   20041019        083801          LiveCache Initialisation Script lcinit.bat
INSTPRT ASCII   879283  20041213        130058          Installation Protocol   dbm.ins
DIAGDIR DIRECTORY       0       20041222        113933          Diagnose History File
ANALYZER        DIRECTORY       0       20041110        175343          DB Analyzer File        analyzer               

The following list contains log and configuration files.

See also MaxDB Glossary : DBMCLI Command

See also MaxDB Glossary : Variable

The values of these variables depend on the installation path for you database instance. You can check your settings using dbmcli:

# dbmcli -u MONA,RED -d DEMODB
dbmcli on DEMODB> dbm_getpath IndepDataPath
OK
/var/opt/sdb/data/wrk/DEMODB

---
dbmcli on DEMODB>dbm_getpath IndepProgPath
OK
/opt/sdb/programs

---
dbmcli on DEMODB>param_getvalue RUNDIRECTORY
OK
/var/opt/sdb/data/wrk/DEMODB 

To get the <dependent_path> you can use the dbmcli command dbm_version which lists version-dependent information. INSTROOT = <dependent_path>.

# dbmcli -u DBADMIN,DBADMIN -d DEMODB
dbmcli on DEMODB> dbm_version
OK
VERSION     = 7.5.0
BUILD        = DBMServer 7.5.0   Build 019-121-082-363
OS               = UNIX
INSTROOT   = /opt/sdb/DEMODB
LOGON        = True
CODE           = UTF8
SWAP          = full
UNICODE      = YES
INSTANCE    = OLTP
SYSNAME   = Linux             

See also MaxDB Glossary : DBM Command, Directory

The easiest way to access the log files is to use the Database Manager GUI on Windows. If you can't use the Database Manager GUI, then you can use the Web DBM which is a web based adaptation of the Database Manager GUI and very similar in use.

With Database Manager GUI, connect to a database instance and then choose Instance -> Check -> Diagnosis Files to get a list of all logfiles. Select a logfile and choose Actions -> View, View with Notepad from Actions or Get.

See also MaxDB Glossary : Database Manager GUI

MaxDB has several configuration files. To locate the files use the information given in Which logfiles exist?.

See also MaxDB Glossary : Configuration File

Two services are added to C:\WINDOWS\system32\drivers\etc\services (Windows) and /etc/services (UNIX): sapdbni72 7269/tcp and sql6 7210/tcp. The service sapdbni72 7269/tcp is only used in SAP systems.

On Windows, some registry entries are created / changed.

On Unix, the operating system user sdb and the operating system usergroup sdba are created. The sdb user is not given login permission.

No automatic start scripts are installed.

See also MaxDB Glossary : Port, sdb/sdba, Automatic Start

We recommend that you install the MaxDB software using the SDB*-tools. To upgrade an installation (updating the database instance and updating the database software), run SDBUPD. SDBUPD takes into account the complete environment and instance settings, verifies compatibility of the upgrade and initiates all necessary upgrade steps on the current instance.

Below are some tips on how to make upgrades even safer just in case you encounter problems during the update.

For paranoid DBAs: before you start to install a new version, get the installation package of your current, old version. Save it to a safe place. If everything else fails, you can use it to restore the old version of the software. Previous versions of MaxDB are available for download from the MaxDB archive at http://dev.mysql.com/downloads/ maxdb/archives/archive_index.html

For paranoid DBAs: if you have a system backup tool, run it before you install the new version. If everything else fails, you can use your backup to recover the old version of MaxDB.

For paranoid DBAs: create a complete data backup of all instances. If the upgrade fails, you can use the data backups for recovery.

For paranoid DBAs: do not rely on a *.tar archive as a secure backup media. From version to version MaxDB puts files into many directories in the file system. It is very likely that you will forget to include all files, and a restore would result in a mixture of new and old versions or simply not include all required files.

Run SDBUPD and follow the instructions given on the screen.

We hope you are a paranoid DBA, aren't you?

See also MaxDB Glossary : Upgrade

We put much effort in making upgrades smooth, so generally speaking we do not expect any problems when upgrading from version X to version Y. Nevertheless you should check the change logs to get informed about all major changes that have been made in between. Even if data migration works smoothly, deprecated tools can cause trouble.

If you are in doubt you can always ask on the mailing list for help.

See also MaxDB Glossary : Upgrade

The procedure to uninstall MaxDB depends on the installation procedure you chose. If you used RPMs, then the rpm tool is used for uninstallation. If you used SDBINST, then SDBUNINST has to be used.

In both cases you have to stop and drop your database instances before starting the software uninstallation. Please make sure that your databases do not contain any important information before you drop them. If you are in doubt, make a backup of your data. Support will not be able to recover instances for you once you have dropped them. You can stop and drop the database instances using all variants of the Database Manager (Database Manager GUI, Web GUI, dbmcli).

# dbmcli -u DBADMIN,DBADMIN -d DEMODB
dbmcli on DEMODB>db_offline
OK

---
dbmcli on DEMODB>db_drop
OK

---					  

Now you can use SDBUNINST or rpm -e maxdb[package_name]. Remember that on UNIX you need to be root to run these commands.

Deinstallation using SDBUNINST does not automatically remove the sdb operating system user and the sdba operating system user group on Unix, and they do not remove the /etc/service entries.

# SDBUNINST -all

uninstall following package(s):

Database Kernel        /opt/sdb/7500          7.5.00.19  32 bit
CPC SDK 7500           /opt/sdb/programs      7.5.00.19  32 bit
PCR 7500               /opt/sdb/programs      7.5.00.19  32 bit
PCR 7403               /opt/sdb/programs      7.4.03.36  32 bit
DB Analyzer            /opt/sdb/programs      7.5.00.19  32 bit
Webtools               /opt/sdb/programs/web  7.5.00.19  32 bit
XML Indexing Engine    /opt/sdb/programs/web  7.5.00.19  32 bit
ODBC                   /opt/sdb/programs      7.5.00.19  32 bit
Database Connectivity  /opt/sdb/programs      7.5.00.19  32 bit
CPC Base               /opt/sdb/programs      7.5.00.19  32 bit
Server Utilities       /opt/sdb/programs      7.5.00.19  32 bit
Python Interface       /opt/sdb/programs      7.5.00.19  32 bit
JDBC                   /opt/sdb/programs      7.6.00.00
Redist Python          /opt/sdb/programs      7.5.00.19  32 bit
Loader                 /opt/sdb/programs      7.5.00.19  32 bit
Perl Interface         /opt/sdb/programs      7.5.00.19  32 bit
PCR 7300               /opt/sdb/programs      7.3.00.52
PCR 7301               /opt/sdb/programs      7.3.01.18
Base                   /opt/sdb/programs      7.5.00.19  32 bit


start uninstallation now (y/n)?y
unregister Database Kernel 7.5.00.19
cannot remove directory "/var/opt/sdb/data"
cannot remove directory "/var/opt/sdb/data/wrk"
done

To uninstall all MaxDB related RPM packages, you can ask the RPM package manager for a list of all installed packages, grep through it and pass the result as an argument to the package manager again.

# rpm -qa | grep -i maxdb | xargs rpm -e

To delete the sdb operating user and the sdba operating user group, use operating system commands, for example:

# userdel sdb
# groupdel sdba

If you installed MaxDB using rpm, then you do not need to remove the sapdbni service, the niserver tool is not contained in the rpm binary because it is only used in SAP systems.

The next step is creating a database instance, if you did not already do so during an installation with the Installation Manager. Before you create a database instance, you have to make some decisions. Take some time and ask yourself the following questions:

All decisions you take are reversible except for Unicode support. Unicode support can only be enabled during instance creation. It is switched off by default and cannot be enabled afterwards. In oder to find out later whether your database instance supports Unicode, check the database parameter _UNICODE using the Database Manager GUI or dbmcli.

# dbmcli -u DBADMIN,DBADMIN -d DEMODB
dbmcli on DEMODB>param_directget _UNICODE
OK
_UNICODE        NO
            

See also MaxDB Glossary : Database Parameter

Another decision that should be made before you create an instance is what size your database instance should be. You can later add data and log volumes to your database instance while it is online (up to MAXDATAVOLUMES (default: 11) data volumes and up to MAXLOGVOLUMES (default: 2) log volumes). See also How do I add a data volume? and How do I remove a data volume?.

It is very hard to make a good guess for the database size you will need. Generally speaking the amount of data you are going to store in the database is the most important factor to estimate the memory requirements. In theory you can use your database schema and the estimated number of rows per table to compute the total amount of storage. Check the Reference Manual for the memory requirements of individual data types.

See also MaxDB Glossary : Data Type

Data and log entries of a MaxDB database instance are stored on so called volumes which are either files or, where this is possible, raw devices. We have two types of database volumes: log volumes and data volumes. Remark: Former versions called them log devspace and data devspace. The log volumes contain log information about the transactions performed on the database, whereas the data volumes contain the data pages.

While the database is running, you can add new data and log volumes.

MaxDB stripes the data evenly over all configured volumes. Such spreading up the data area across multiple volumes increases concurrency of the I/O-system. So, the bigger your system gets this should be taken into account for your configuration. For smaller databases, MaxDB may run with a single data and a single log volumne.

See also MaxDB Glossary : Volume

Adding data volumes is a fairly simple task. It can be performed with the Database Manager GUI, on the command line with dbmcli, or with the Web Tools. We'll demonstrate only the dbmcli way, because the other ways are self-explaining.

Data volumes can be added while the database is running. You do not need to stop your MaxDB instance for this, unless you exceed the maximum number of configured data volumes. First, connect to the database and check which volumes are configured:

# dbmcli -u DBADMIN,DBADMIN -d DEMODB
dbmcli on DEMODB>param_getvolsall
OK
LOG_MIRRORED                     NO
MAXLOGVOLUMES                    3
MAXDATAVOLUMES                   64
LOG_VOLUME_NAME_001              131072     F  /var/opt/sdb/DEMODB/log/DISKL0001
LOG_VOLUME_NAME_002              131072     F  /var/opt/sdb/DEMODB/log/DISKL0002
DATA_VOLUME_NAME_0001            262144     F  /var/opt/sdb/DEMODB/data/DISKD0001            

The output shows that you can configure up to 64 (MAXDATAVOLUMES) data volumes while the database is running. Only one data volume exists in /var/opt/sdb/DEMODB/data/DISKD0001 , so you have room left for 63 new volumes. The size of the example data volume is 262144 pages. MaxDB uses a page size of 8 KB. This gives a total size of 262144 x 8 KB = 2097152 KB = 2048 MB = 2 GB. As there's plenty of room left for new volumes you can continue to add a volume - if your filesystem space is sufficient.

Adding a new data volume using dbmcli:

dbmcli on DEMODB>db_addvolume DATA DISKD0002 F 262144							
OK

---

File name and label of the data volume are set to DISKD0002. If you did not specify a directory, the system has created the new volume in the database run directory. To find the location of your run directory, you can use dbmcli.

             
dbmcli on MAXDB1>param_directget RUNDIRECTORY
OK
RUNDIRECTORY    /var/opt/sdb/data/wrk/DEMODB

---
dbmcli on MAXDB1>quit
OK              
---
nixnutzlinux:/opt/sdb/7500/bin # ls -la /var/opt/sdb/data/wrk/DEMODB/DISKD0002
-rw-rw----  1 sdb sdba 2147491840 2005-01-10 16:58 /var/opt/sdb/data/wrk/DEMODB/DISKD0002

As you can see in the example the new volume's location is different from the location of the other volumes. To create the new volume in the same directory as the other volumes we would have needed to write /var/opt/sdb/MAXDB1/data/DISKD0002 as the second parameter to db_addvolume. The type of the new volume is F = file and it's size is 262144 pages. Check the manual for details about the other types L = link and R = raw (see also MaxDB Glossary : Volume).

If you run into the unlucky situation that you need to increase the database parameter MAXDATAVOLUMES, then you need to restart the database instance in order to apply the parameter change.

dbmcli on DEMODB>param_startsession
OK

---
dbmcli on DEMODB>param_put MAXDATAVOLUMES 128
OK

---
dbmcli on DEMODB>param_checkall
OK

---
dbmcli on DEMODB>param_commitsession
OK

---
dbmcli on DEMODB1> db_offline
OK

---
dbmcli on DEMODB> db_online
OK

See also MaxDB Glossary : DBM Command

The steps to shrink the size of you database instance are different in 7.5 and 7.6.

As of version 7.6 you can physically delete a data volume and then re-release the resources, you use the DBM command db_deletevolume. The database has to be in the ONLINE operational state. You specify the volume to be deleted specifing the volume type (DATA), and the volume name (DISKD0002) or the volume ID:

dbmcli on DEMODB>db_deletevolume DAT_0002
OK

---

Alternatively you may shrink the size of your database instance by copying the content of your database instance into a new database instance with smaller or fewer volumes. This is the only possible way to shrink your database instance when you run 7.5.

On 7.5 it is not possible to remove a data volume in order to release the space used by the volume. The Database Manager command param_deletevolume only removes the parameters of a volume from the database configuration but does not remove the volume itself.

See also MaxDB Glossary : Database Copy, DBM Command

You can add upto MAXDATAVOLUMES volumes while the database is running. If you have reached this number of data volumes and your database runs out of space, you need to stop your database instance, increase MAXDATAVOLUMES, add a new volume and restart your database instance. From version 7.6 on you do not need to restart your database instance. The database parameter changes will become effective even if the database is in ONLINE mode. See How do I add a data volume for details.

To add a log volume in Database Manager GUI, choose Choose Instance -> Configuration -> Volumes.

Like with data volumes, your work should start with a look at the output of the Database Manager command param_getvolsall. The MAXLOGVOLUMES and LOG_MIRRORED values are of interest. Upto MAXLOGVOLUMES log volumes can be added while the database is running. If param_getvolsall tells you that you already have 2 log volumes and MAXLOGVOLUMES is 32, then you can add another 30 log volumes without stopping the database. If you have already 32 log volumes configured, then you need to stop the database instance, change MAXLOGVOLUMES and restart the database. Check How do I add a data volume for details.

            
dbmcli on DEMODB>param_getvolsall
OK

LOG_MIRRORED                         YES
MAXLOGVOLUMES                    32
MAXDATAVOLUMES                  15
LOG_VOLUME_NAME_001         131072     F  LOG_001
[...]

You should always mirror your log volumes. Mirrored log volumes as this increases your protection from hardware failures.We recommend hardware mirrors (RAID 1). If hardware based mirroring is not available, you can use the MaxDB log mirroring feature. For this case, in the example LOG_MIRRORED is set to YES. MaxDB now will write all log entries for a log volume into the log volume itself and its mirror. See also MaxDB Glossary : Log Mode

If LOG_MIRRORED is set to NO, you can add a new log volume using dbmcli as follows: (Label LOG_002 type F = file (alternatives: R = raw, L = link), size of 1000 pages (1000 x 8 KB = 8000 KB)).

                        
dbmcli on DEMODB>db_addvolume LOG LOG_002 F 1000
OK

If LOG_MIRRORED is set to YES, the add_volume commands needs two additional parameters that describe the settings for the mirror. If you forget to specify them, the system returns "-24918,ERR_MISSMIRR: missing data for mirror volume".

                        
dbmcli on DEMODB>db_addvolume LOG LOG_003 F 1000
ERR
-24918, ERR_MISSMIRR: missing data for mirror volume

You need to specify the additional parameters for the mirrored volume, for example LOG_M_003 and a F (file).

 
dbmcli on DEMODB>db_addvolume LOG LOG_003 F 1000 LOG_M_003 F
OK

See also MaxDB Glossary : DBM Command

Note: Adding a new log volume does not solve a log full situation immediately! Learn more in How do I handle a log volume full situation? As the log entries are written cyclically across multiple log volumes, the log has to be backed up first, before the systen use new log volume.

See also MaxDB Glossary : Log Backup

You can't remove log volumes. To shrink the size of the database or to reduce the log size you need to create a backup first and restore it to a new database instance with a different (log) volume configuration.

See also MaxDB Glossary : Backup, Restore

Create a log backup or enable automatic log backup overwrite for logs. The latter will also backup the log immediately and subsequently do a log backup every time, a certain amount of log pages has been written, thus avoiding log full situations.

You can think of a log as a queue of 8kb log pages. Once the queue is full, the database cannot store log entries any more without overwriting older entries. Overwriting log entries that have not been backed up yet would mean to loose informations for recovery tasks.

See also MaxDB Glossary : Log Backup

For demo purpose, you can configure older log entries to be overwritten automatically (without backup). To enable this feature using dbmcli, use the following command

dbmcli on DEMODB>db_execute SET LOG AUTO OVERWRITE ON
OK

See also MaxDB Glossary : Log Setting, DBM Command

The database parameter CACHE_SIZE specifies the size of the bugger for I/O operations used by the data cache and the converter. This parameter is very important for the performance of the database. The lower and upper limits are: 800 <= CACHE_SIZE <= 2147483640 pages. The default size of a database page is 8 KB.

The converter assigns logical page numbers to physical block addresses on the data volumes. Because it is so important for database operation, the converter resides completely in main memory. The rest of the main memory (as configured by CACHE_SIZE) is used by the data cache.

See also MaxDB Glossary : I/O Buffer Cache

Use the monitoring functions of the Database Manager to obtain information about the data cache hit rate and the performance of the system.

For example, in dbmcli:

dbmcli on DEMODB>info CACHES
OK
END
Type                 | Accesses             | Successful           | Unsuccessfu
l         | Hit Rate (%)

DATA                 | 7471                 | 7304                 | 167
          | 98
SEQUENCE             | 0                    | 0                    | 0
          | (null)
COMMANDPREPARE       | 3                    | 0                    | 3
          | 0
COMMANDEXECUTE       | 3                    | 3                    | 0
          | 100
CATALOGCACHE         | 10                   | 3                    | 7
          | 30
CATALOG              | 0                    | 0                    | 0
          | (null)

See also MaxDB Glossary : Monitoring, Cache, DBM Command

With the database parameter MAXUSERTASKS you configure the maximum number of simultaneously active users (database sessions). Do not configure MAXUSERTASKS higher than necessary because the system needs a log of address space (especially shared memory) for it.

See also MaxDB Glossary : Database Parameter

With the database parameter MAXCPU you configure the maximum number of processors to which the database system can distribute user tasks. If the computer is used as database server exclusively, MAXCPU should correspond to the actual number of physical CPUs of the machine. Otherwise reduce the value by the number of CPUs occupied by other applications.The lower and upper limits are: 1 <= MAXCPU <= 128.

See also MaxDB Glossary : Database Parameter

Yes, it does. One of the strongest points of MaxDB is the availability of build-in backup and recovery functions. These functions can be used via the Database Manager GUI and the Database Manager CLI command line tools. You can make consistent backups while the database is running. Backups in general do not have a negative impact on performance; the additional workload created by backups is mainly dependent on the backup media used.

See also MaxDB Glossary : Backup and Restore.

MaxDB like many other transactional database systems uses two groups of files to store its data. One group of files contains the data, the other group contains log entries. The data files are called data volumes, the log files are called log volumes. As of version 7.4, MaxDB only needs data backups to restore a database to a consistent state. We nevertheless recommend that you back up log entries as well (you need a log backup to restore the database to the latest consistent state (point of failure) or a certain point in time after your data backup, if your log area has been damaged and your last data backup is quite old).

See also MaxDB Glossary : Backup Type.

Automatic log backup: You activate automatic log backup using the Database Manager. As soon as a log segment in the log area is full, the system automatically backs it up to a version file. Afterwards it releases the log segment in the log area to be overwritten.

Interactive log backup: With the Database Manager, you back up all redo log entries that the database system has written to the log area since the last log backup from the log area to a backup medium.

See also MaxDB Glossary : Log Backup.

You cannot simulate data backups.

You can use backup tools from other providers to back up and restore data and log entries or to export and import data. The following backup tools can be used with MaxDB:·

See also MaxDB Glossary : External Backup Tool

To find the cause of the failure, check the knldiag kernel log file and the Database Manager log files ( dbm.* files).

You do not have to manually reorganize data. MaxDB uses B* trees for storing and accessing data. The database system automatically manages its complete space and balances the distribution recognizes if the data is distributed irregularly across within the B* tree structuress during operation, and automatically balances the distribution..

See also MaxDB Glossary : B* Tree

Microsoft Windows: You define whether a database instance will be started automatically when you create the database instance.To configure automatic startup later, use the Database Manager CLI (you update the registration of the database instance).

On UNIX/Linux, you create a start script and generate symbolic links to it in the desired run-level directories. You can find more information about starting programs automatically in your operating system documentation.

See also MaxDB Glossary : Automatic Start

You should monitor at least the following things.

See also MaxDB Glossary : Monitoring

To monitor your database instance, use MaxDB Database Manager and MaxDB Database Analyzer.

See also MaxDB Glossary : Database Manager and Database Analyzer

You can use event dispatchers to automate administrative tasks.

Event dispatchers monitor database instances. If the database instances monitored by an event dispatcher reach a certain status and trigger an event, the event dispatcher calls a previously-specified program.

Example: If the data area of your database instance is full, the database instance triggers the DATABASEFULL event. An event dispatcher then calls the e-mail program, which sends an e-mail to you.

Other mechanisms for automating administrative tasks include:

See also MaxDB Glossary : Event and Automation

MaxDB provides state-of-the-art performance and scales well on symmetric multi-processor architectures (SMP). Standard application benchmarks of SAP solutions can be made available by our partners.

You can find out which strategy the SQL Optimizer has chosen for executing an SQL statement by using the EXPLAIN statement. The result of an EXPLAIN statement is a table. The STRATEGY column of this table shows you the strategy that the SQL Optimizer selected for this SQL statement, the PAGECOUNT column shows you the costs determined for the chosen strategy, for example, whether the system used key columns or indexes.

See also MaxDB Glossary : EXPLAIN Statement

There is no dedicated query monitor, but there are several MaxDB tools you can use for monitoring, for example Database Manager, Database Analyzer and XCONS. To find out which strategy the SQL optimizer has chosen for an SQL statement, you can use the EXPLAIN statement.

See also MaxDB Glossary : Monitoring

MaxDB comes with the following programming interfaces.