MaxDB Solution in Detail
Contents
1. tam a 16 a asa a 16 Ticensin gs Model s apama ayaqa paga dante dd ales Base a pumaqa ua aps la Pa 17 COTS ANC sraa athe st sa aap eats a q als AA 17 SUPPO Z ES sai p aqui pues 17 Trainin sand Certificado kaq aaa 18 Traming saris ed easy Ay oy RAE OTS quyu alee 18 Tamang Dy MySQL uyu ike A alee eed oh Ra teat 18 Certification by SAP sss sss 19 Certification by MySQL eon Roorkee 19 Technical Specifications aie 19 MaxDB Doctimentation OVErvieW eee a ner ea ee ed eae 19 Why You Need MaxDB 22 eccics scien anaq ya waspaqa pa qay aaah nee tes 21 For More Information 0 0 21 WHAT IS MaxDB MaxDB is the enterprise database management system DBMS you need to run your company efficiently Easy to use it offers low cost of ownership outstanding performance and much more MaxDB supports a broad range of platforms a high number of users and demanding workloads It is highly scalable available and reliable Administration costs are low because there are not many administrative tasks to e2. importing the latest database backup to the data volumes and redoing the latest log backups ecovery can also be used to restore a previous database state R y Iso b dt t database stat point in time recovery This may be required if application problems or administration errors occur for example if a table is accidentally deleted High Availability Database instances can fail or become defective as a result of hardware errors such as disk or controller defects power failure and operating system failures or as a result of operating errors for example tables unintentionally deleted or incorrect data imported or processed To avoid downtime in these situations MaxDB offers several defined procedures to support high mission critical database instances These procedures include the options of standby or hot standby configurations A standby database is a copy of the active database instance master Once it has been initialized on the basis of a data backup of the master instance the log backups of the master are continuously imported into the standby As a result all changes to the master are replicated in the standby Conse quently the standby instance always shows the data set of the master instance per the most recent log backup imported STANDBY Log backup Staging Data ecg Data Log Log restore Initial data backup Figure 6 Standby Database with Log Shipping In
3. Number of correlated tables in an SQL statement 16 Number of parameters in an SQL statement 2 000 MaxDB Documentation Overview The following table contains an overview of the entire user documentation available for MaxDB Installation Database Software Installation Guide Standard installation and uninstallation of the database software Installation Manual Installation update and uninstallation of installation profiles and individual software components for the database update of database instances including software Number of columns in an order or a group clause Number of columns in a select statement Number of columns in an insert statement Number of columns in a result table Number of join tables in a select statement Number of triggers per base table Number of indexes per table 255 SAP Web Application Server Installation Installation of an SAP system on UNIX on UNIX MySQL MaxDB only available for SAP customers on the SAP Service Marketplace SAP Web Application Server Installation Installation of an SAP system on Microsoft on Microsoft Windows MySQL MaxDB Windows only available for SAP customers on the SAP Service Marketplace SAP Web Application Server Copying SAP systems only available Homogeneous and Heterogeneous for SAP customers on the SAP Service System Copy Marketplace 19 Basic Know
4. Number of data volumes size per volume 4 095 volumes 8 GB Number of log volumes Log size SQL statement length Maximum 128 KB default 64 KB Identifier length 32 characters 38 digits Numeric precision Number of tables Unlimited Number of columns per table with key 1 024 Number of columns per table without key Number of primary key columns per table Number of columns in a compound index Number of foreign key columns per table Description Maximum Value Number of referring constraint definitions unlimited foreign key dependencies per table Number of references per table unlimited Number of rows per table limited by database size Internal length of a table row 8 088 bytes Total of internal lengths of all primary key columns 1 024 bytes Total of internal lengths of all foreign key columns 1 024 bytes Total of internal lengths of all columns belonging 1 024 bytes to an index Internal length of a BLOB CLOB or LONG column 2 Note BLOB is a binary large object CLOB is a character large object LONG is a MaxDB large object of any type Length of columns in an order or a group clause 1 020 bytes Nested trigger levels Unlimited Nested subqueries 127 Number of join conditions in a where clause 128 select statement Number of correlated columns in an SQL statement 64
5. MaxDB SQL studio MaxDB Web SQL an autocommit option implicitly turns each single command into separate transaction Data Security and Consistency MaxDB stores all data object change operations in log volumes When the database is restarted after an unplanned downtime it uses these log entries to decide on completed and uncompleted transactions and to restore the latest consistent state of the database The transaction log ensures transactional consistency but will not protect from media errors that is data loss This is why you have to have a backup strategy in place for each pro ductive MaxDB system a strategy based on periodic data and log backups You can create the backup strategy that suits you best For example you can perform a complete data backup or at least an incremental backup on each productive day to back up all changes made since the last complete data backup The more recent the previous backup the fewer log entries will have to be reworked to restore the database instance Or you can initiate an incremental backup strategy that is limited to the pages that were actually changed since the last complete backup Whichever method you choose you must follow it faith fully Log backups are required they record the change history of a database upon the latest data volume backup MaxDB allows for data and log backup during operation with little noticeable effect on overall performance To a
6. entire landscape of MaxDB instances centrally It can create and monitor the instances on local or remote computers back up the data on them and restore it if required MaxDB database manager is available as a Windows based tool MaxDB database manager GUI a browser based tool MaxDB Web database manager and a command line based tool Max DB database manager CLI The CLI version is used mainly to create automated administration processes scripts MaxDB Web dabase manager and MaxDB database manager DBMCLI are available for all operating systems supported by MaxDB The Max DB database manager GUI DBMGUI requires a Windows platform although it can manage database instances on any operating system platform supported Database Manager File Edt View Instance Actions Tools Help i e os r 130 440 Used 8296 KB Free 122 144 KB 7 Used 13 Name MAXDEMO Auto Log Ott Version 750002 Command Monitor Ott Operating windows 2000 WN32 Resource Montor Off c documerts and settingsta Database Trace on Rundirectory userstapplication Database Analyzer Off datatsdbidatatwrkiWiAXDEMO Start On 12 7 2004 2 03 37 PM Figure 10 MaxDB Database Manager GUI Figure 10 shows the overview status of a database Instance MAXDEMO The screen is divided into areas so that you can navigate within the System landscape top left Short overview of the status of
7. the event of problems with the master a standby can be put into operation immediately and work can be continued with out a lengthy recovery process Simply make sure that the remaining log from the master instance is transferred to the standby instance This configuration is supported by third party tools such as Libelle www libelle de Hot standby refers to the MaxDB high availability strategy which allows you to switch over to a parallel standby database instance within seconds should the active database instance fail A hot standby system consists of an active master instance and one or several standby components installed in an operating system cluster configuration To effect the hot standby strategy you need an intelligent stor age system with direct copy facilities BCV FlashCopy Snapshot The standby component is initialized by a direct copy within the storage system Once the copy has been made the standby component reads in the master log entries which detail which transactions the master component has executed Then it writes the resulting database changes to the standby data volumes Outwardly a hot standby configuration behaves like a single database instance Figure 7 shows how the configuration is structured In the event of a master failure the IP routing is redirected to the standby node which restarts immediately and is available to the application again within seconds preventing any loss of committe
8. you can hand the monitoring of data base instances over to a tool reducing the burden on the DBA expert 13 MaxDB database analyzer facilitates the troubleshooting required in the event of performance or throughput problems with a MaxDB instance It can be used to analyze the database config uration caches heap area parameters the synchronization locks critical sections the optimization of database queries processing strategy indexes optimizer statistics or the hardware configuration The analysis concludes with recommendations for example a suggestion to create additional indexes or to increase the size of the database cache Database Analyzer Bottleneck Analysis 10 09 2004 Type Message 4612 at 2004 09 10 00 05 58 Data cache hitrate SQL Pages 95 55 1715 of 38531 accesses failed Sii Rw Selects and fetches selectivity 4 15 1242 selects and fetches 151844 rows read 6303 rows qualified W1 1160 primary key range accesses selectivity 269 129299 rows read 3478 rows qualified serer tasks activity but not for backup Dispatches 2 I W1 Number of tables where update statistics is required 50 9613 at 2004 09 10002058 I Data cache hitrate SOL Pages 9 86 655 of 57697 accesses failed Catalog cache hitrate SOL Pages 89 8595 433 of 4265 accesses failed W2 4 index range accesses selectivity 1 16 13763 rows read 160 rows qualified at 2004 09 1
9. 0 00 35 59 SOL commands executed 18049 Data cache hitrate SQL Pages 96 67 2419 of 181543 accesses failed 2004 09 10 00 5059 Data cache hitrate SOL Pages 97 88 800 of 37772 accesses failed Catalog cache hitrate SOL Pages 89 46 1077 of 10214 accesses failed 2004 09 10 01 06 00 W3 Data cache hitrate SOL Pages 83 39 17576 of 105835 accesses failed 7 W2 Selects and fetches selectivity 0 81 826 selects and fetches 646917 rows read 5235 rows qualified 7 W2 747 primary key range accesses selectivity 0 44 628067 rows read 2734 rows qualified 117478 physical reads for user task 52 373 commands application pid 3000 E Figure 12 MaxDB Database Analyzer Log Importing and Exporting Data MaxDB loader lets you import application data from the file system to a MaxDB instance or export data from a MaxDB instance to the file system Because it supports a large number of data formats and record layouts it can often replace a dedicated application program for loading or unloading data sets And because of its close connection to the MaxDB instance MaxDB loader usually offers performance advantages It also supports the transport of any data from one MaxDB instance to another one that may also run on different hardware architectures or different operating systems The data can be individual tables all tables belonging to a specific
10. 7 5 Version 7 5 of MaxDB is a direct advancement of the SAP DB 7 4 code basis Therefore the MaxDB soft ware version 7 5 can be used as direct upgrade of previous SAP DB versions starting 7 2 04 and higher The former SAP DB development team at SAP AG is now responsible for developing and supporting MaxDB Sales and distribution are handled by both SAP AG and MySQL AB The advancement of MaxDB and the MySQL server leverages synergies that benefit both product lines MaxDB is subjected to the SAP AG quality assurance process before it is shipped with SAP solutions or provided as a download from the MySQL site Unlike other SOL DBMSs MaxDB offers patented SAP liveCache technology an object oriented DBMS extension for C applications When used for large tree type or network type data models this technol ogy improves performance by a factor of 20 to 50 compared with OLTP DBMSs SAP liveCache tech nology and applications written as stored procedures in C are the cornerstone of the mySAP Supply Chain Management SCM solution mySAP SCM customers can choose an SCM One DB configura tion that combines MaxDB and SAP liveCache in a single database to reduce the need for database administration thus reducing cost of ownership Component Portfolio The MaxDB portfolio includes tools for installation and admin istration tools for querying and moving data and interfaces for database programming See Figure 1 MaxD
11. B KERNEL Figure 1 MaxDB Component Portfolio Operating System Platforms Supported by MaxDB MaxDB is available on the following operating systems and hardware processor architectures Operating System Hardware Architecture SUN Solaris Microsoft Windows 2000 Microsoft Windows XP Microsoft Windows Server 2003 FEATURES AND FUNCTIONS OF MaxDB MaxDB simplifies enterprise data management tasks through a wide range of features and functions detailed in this section Data Organization and Storage Application data including metadata and a log of the latest data changes is contained in the database instance The application and log data are kept in volumes Volumes can be complete physical disks parts of a physical disk or files in a file system Each database instance has at least one log and one data volume Volumes contain a fixed number of 8 kilobyte KB pages and are thus a fixed size The growth of a database or the demand for more log space is managed by adding further data or log volumes to its configuration APPLICATION MaxDB Instance MaxDB KERNEL Data volume Data volume Data volume Figure 2 MaxDB Database Instance The total size of a MaxDB instance is limited by the sum of all data volume sizes MaxDB does not require any size estimations for individual database objects such as tables and indexes Each table can grow and shrink without any administrative action automat
12. DB Administration This course focuses on using MaxDB from an administrator s point of view You will learn to install a MaxDB instance to understand the capabilities of the database in different scenar ios and to use the administration tools provided After the course you will be able to understand and handle advanced administrative and monitoring tasks on your MaxDB Using and Developing MaxDB This course focuses on using MaxDB from a developer s point of view You will learn in detail how to set up and use the database when programming an application as well as how to handle the MaxDB capabilities After the course you will be able to design your database according to your needs and know how to optimize it to achieve the best possible perform ance Combined with adequate practical experience these courses will prepare you for the MaxDB 7 5 Administrator and Developer Certifications Certification by SAP SAP offers certification to administer SAP systems in a 20 day basic training program followed by a choice of database dependent administration courses The MaxDB course is a three day course Having passed the examination after the course you will be cer tified as Technology Consultant SAP Netweaver SAP WebAS on MaxDB Certification by MySQL Certification for MaxDB is under way Technical Specifications Database size 32 TB with 8 KB page size 63 volumes S12 127 volumes 256 GB
13. JavaScript is a registered trademark of Sun Microsystems Inc used under license for technology invented and implemented by Netscape MaxDB is a trademark of MySQL AB Sweden SAP R 3 mySAP mySAP com xApps xApp SAP NetWeaver and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world All other product and service names mentioned are the trademarks of their respective companies Data contained in this document serves informational purposes only National product specifications may vary These materials are subject to change without notice These materials are provided by SAP AG and its affiliated companies SAP Group for informational purposes only without representation or warranty of any kind and SAP Group shall not be liable for errors or omissions with respect to the materials The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services if any Nothing herein should be construed as constituting an additional warranty CONTENTS Whatis MaxDB uuu usa asas qaku Sa 4 Meeting the Challenges of an Enterprise Environment 5 How SAP DB Became MaxDB u u song coe yen Sek bd dae bone Q 5 Component Portoko
14. SAP Solution in Detail MaxDB THE PROFESSIONAL DATABASE FOR TODAY S ENTERPRISE ENVIRONMENT THE BEST RUN BUSINESSES RUN SAP SARA Copyright 2005 SAP AG All rights reserved No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG The information contained herein may be changed without prior notice Some software products marketed by SAP AG and its distri butors contain proprietary software components of other software vendors Microsoft Windows Outlook and PowerPoint are registered trademarks of Microsoft Corporation IBM DB2 DB2 Universal Database OS 2 Parallel Sysplex MVS ESA AIX 5 390 5 400 OS 390 OS 400 iSeries pSeries xSeries zSeries z OS AFP Intelligent Miner WebSphere Netfinity Tivoli and Informix are trademarks or registered trademarks of IBM Corporation in the United States and or other countries Oracle is a registered trademark of Oracle Corporation UNIX X Open OSF 1 and Motif are registered trademarks of the Open Group Citrix ICA Program Neighborhood MetaFrame WinFrame VideoFrame and MultiWin are trademarks or registered trademarks of Citrix Systems Inc HTML XML XHTML and W3C are trademarks or registered trademarks of W3C World Wide Web Consortium Massachusetts Institute of Technology Java is a registered trademark of Sun Microsystems Inc
15. ach database instance uses several operating system processes and implements internal threading MaxDB does not rely on operating system threads but on internal threads that release database resources such as locks on commit that can be prioritized increasing the database throughput Internal threading also enables the DBMS to distribute the workload optimally The read and write operations on the data of a database instance are buffered in order to minimize physical disk access These buffers or caches can be configured using database parameters Major MaxDB caches include the data cache and the catalog cache for the SQL catalog metadata MaxDB uses a special variant of prefix B trees for its disk storage organization There is tree for each table primary data as well as a B tree for each index of a table secondary data Automatic space management and automatic balancing of the I O workload across all data volumes provide disk storage organization that does not require any reorganization whatsoever It will not degenerate even after years of high volume write operations All data structures are checked for internal consistency As a result potential hardware defects are detected and isolated as soon as possible MaxDB uses a cost based query optimizer to determine the best execution plan for a select command Cost based optimizers typically use statistical data about table index and column cardinal
16. alogous to folders in a file system Additional schemas can be created explicitly and filled with new database objects In Figure 9 the user Mona has explicitly created the additional schema 4You to her default schema and has assigned the rights to create objects in this schema to the users Bob and Carl These users then added their own tables to this schema 11 Grant Access to Schema 4You to Bob and Bob s Table 4You T2 Carl s Te 4You T3 Figure 9 Schemas Schemas are used to decouple the database users from name spaces With schemas the user name is no longer part of the name of a database object which enhances DBMS flexibility Secure Communication As an option the socket communication between MaxDB client and MaxDB server can be safeguarded by means of SSL This is security requirement for certain Internet scenarios If the database and its clients are implemented in a secure in house environment SSL support can be deactivated The MaxDB authentication procedure is a challenge response procedure so that passwords that may have been captured cannot be used to open any other session 12 USING THE DATABASE MANAGEMENT SYSTEM MaxDB provides a variety of tools for working with database instances All MaxDB tools can be used in connection with both SAP solutions and other applications Managing Database Instances The MaxDB database manager is a tool that permits you to operate an
17. ccelerate the backup of very large database instances write the backup multiple media tapes disks in parallel data streams Such parallelization is also possible when restoring a database instance to minimize potential downtime MaxDB also provides an automatic log backup an autosave log that should be switched on at all times This enables the DBMS to automatically back up the log volumes to a set of files once the log reaches a precalculated filling level The MaxDB database manager logs all backup activities and assists you during the recovery process The recovery wizard first offers the most likely recovery path From there you can control the recovery process by selecting the alternatives that will work best for you File Edt View Instance Actions Tools Help x JMyFo2 Name sue Sessions 8 eve Offline p Figure 3 Backup History The screen shown in Figure 3 is an extract from a backup history at the start of a backup generation It shows a complete backup several log backups LOG_ and incremental backup The MaxDB database manager uses this log to select the appropriate data backups for the recovery process shown in Figure 4 95 Restore Database Instance xj Select Items for Recovery OOA Select items to specify the use of incremental backups Select the inc
18. customer_addr S A SOL Studio Objects CJ Tables DOMAIN B aty E CUSTOMER CUSTOMER_ADDR CUSTOM_HOTEL Datasolt EMPLOYEE Brian HOTEL Griffith HOTEL_ADDR Randolph RESERVATION Smith E Room r sys 4 SYSINFO J Indexes Auto Commit SQL Mode Internal Isolation Levet Committed select from customet_addr int Ln3of Sins select from customer_addr internat committed Statement suc Figure 11 Querying with MaxDB SQL Studio MaxDB SQL studio offers navigation support within the objects of the database catalog Figure 11 left The SQL dialog shown in Figure 11 is one among three dialog types permitting you to enter the SQL command directly in the SQL syntax top right The result is displayed in report format In the lower area a trace can be activated that logs the commands sent to the data base together with the corresponding messages The other query modes offer form based and visual support in creating the database query and implicitly generate the required SQL syntax Monitoring the Database and Analyzing Problems MaxDB database analyzer is an expert tool for MaxDB mon itoring and tuning It retrieves logs and assesses performance relevant data at regular time intervals The findings obtained are categorized in five classes information three warning levels and error messages As a result
19. d data Thus the standby automatically becomes the master Once the defect on the former master has been eliminated that former master can be initialized for stand by usage completing the configuration Switching from master to standby cancels the transactions rollback on the various current applications Reconnect gt IP SWITCH STANDBY Continuous Restart CLUSTER Figure 7 Hot Standby Switchover Situation You can also define a time interval by which the standby is to lag behind the master This supplies a time window for correcting logical errors in database operations which can be caused by the DBA incorrect data or incorrect applications In addition to the high availability options described earlier MaxDB also lets you create a consistent snapshot of a complete database state The current database state is frozen and can be restored again if required This snapshot process can be done quickly since no copying of data is involved It prevents logical database errors and is thus suitable for establishing fallbacks for example during application upgrades It can also be used to reset training systems to their initial state Because no additional backup media are required for a snapshot it will not protect you from media loss 10 Performance MaxDB is based on a multiprocess multi threaded server architecture geared toward optimal scalability on symmetric multiprocessor systems E
20. database user or even the complete content of a database MaxDB loader simply exports 14 it from database A to a transport medium such as a file a pipe or a tape and then imports from the transport medium into database B Replicating Data MaxDB synchronization manager handles replicated redun dant data in different MaxDB instances Typically it is used for mobile MaxDB databases on laptops that are occasionally synchronized with a central MaxDB instance In this context synchronizing means merging the changes that were made in the central instance or in the laptop instance To synchronize data one MaxDB instance is defined as the master instance Further the landscape of the client instances is defined and the master database sections to be replicated are determined After the replicated data has been initially trans ferred all changes are logged in the master as well as in all client instances and then merged during the next synchronization With bidirectional replication some change conflicts may have to be resolved manually The message server of the MaxDB synchronization manager is a dedicated MaxDB instance that is independent from the master and the client instances It buffers all changes until the propaga tion for these changes has been completed MaxDB synchroni zation manager thus decouples the availability of the master and the client instances from the time of synchronization MaxDB Synchronizati
21. dministration efforts and training costs Transactional Control of Data Changes Database applications carry out changes of data under the control of transactions which consist of consecutive SQL statements that are treated as a unit by the database system Consequently unless all changes of a transaction are executed the transaction will have no effect In a standard transaction for example rebooking the amount of 100 from account A to account B the sum of 100 either has to be debited from account A and credited to account B or it has to remain available on account A It is impos sible to have an error situation in which the 100 is missing from account A but has not yet been credited to account B Transaction management ensures that all transactions for all con current DBMS users are processed and that the database is in a defined consistent and restorable condition at any time and for every possible error situation for example a power failure MaxDB is never in transactionless mode The user login implicitly triggers the first transaction Once the transaction has been successfully terminated by a commit all changes are made permanent If the transaction is ended by a rollback or is otherwise terminated all data changes made within the transaction are rolled back Both commit and rollback will terminate the transaction and implicitly open a new trans action For interactive query tools
22. e capabilities are built in not built around it can also be used as a desktop or laptop DBMS In these environ ments its self managing capabilities turn it into an invisible DBMS Commercial licenses and support for MaxDB are available from SAP and MySQL For open source or other software develop ment projects and for trial or educational purposes MaxDB is also available as open source under the GNU General Public License GPL the most popular open source license To develop commercial software based on MaxDB you need a commercial MaxDB version that enables you to deploy the software at your customer s site To implement MaxDB you can either use one of the compiled executables available for prominent operating system plat forms or compile your own executable programs using the MaxDB source code Downloads of the MaxDB development environment and released versions of the MaxDB sources are available at www mysql com maxdb You can also use Concurrent Versions System CVS to download the latest MaxDB source code MEETING THE CHALLENGES OF ENTERPRISE ENVIRONMENT MaxDB provides the solutions and tools you need to respond effectively to the challenges you encounter in today s enterprise environment It offers Proven DBMS technology based on many years of experience with OLTP data warehousing decision support and object oriented SAP liveCache applications Attractive pricing Easy configurati
23. esult sets has an additional large object interface and supports a wide range of SQL and C data types Figure 14 MaxDB SQLDBC Programming Interface Apart from supporting the major development environments and programming languages via ODBC and MaxDB provides native drivers for the most popular script languages PHP Perl and Python for example used in open source projects 15 IMPLEMENTING MaxDB At the MySQL Web site www mysql com you can either download the source code of MaxDB and compile it yourself creating an executable program tailored for your specific system environment or you can use one of the precompiled and quality assured executable programs available for the prominent operating system platforms These precompiled executables are kept up to date and include the latest patches Product documentation and release information are also available on the Web site along with a moderated mailing list that enables MaxDB users to support and educate each other and a Web based problem tracking system that enables users to monitor the MaxDB problem resolution process and check whether a problem has already been reported by other users Download For SAP customers MaxDB software is included in the delivery scope of their SAP solution Further it is available through the SAP Service Marketplace http service sap com To download the latest open source version of MaxDB software including a
24. ic space management All data of all tables is distributed on all data volumes Therefore tables cannot be assigned to any particular data volume and vice versa The implicit distribution of all data offers several advantages The growth is distributed equally among all data volumes and the input output I O load is spread evenly among all data volumes Consequently there is no need and no possibility for the database administrator DBA to fine tune table or index allocations to any particular volume in order to balance the I O load because this is done automatically by MaxDB When MaxDB is close to full it can be extended by additional volumes while it is online Such data volume extensions can be carried out manually by the DBA or automatically by MaxDB Moreover an automatic procedure can be used to back up the database log preventing the log volumes from filling up SQL DBMSs generally provide high abstractions with respect to the physical aspects of data management MaxDB takes this one step further by defining all database objects for example tables on a purely logical level No DBA assistance is required in defining the properties of their physical storage For example it is indeed possible to obtain information about the size and internal structure of a table or an index but there is no require ment and therefore no possibility to influence the physical storage by means of any low level directives This reduces both a
25. ities as well as value distributions But outdated statistical data can cause the optimizer make a wrong decision calculation on how to best access data For single table queries the cost based optimization of MaxDB is not dependent on statistical data Instead it uses actual cardi nalities that are administered within the scope of the B tree organization thus eliminating the possibility of incorrect opti mizations caused by outdated statistical data The process of optimizing joins requires up to date statistics MaxDB detects when these statistics are no longer in sync with the actual data base cardinalities and places a refresh request with the DBA Alternative query plans are created during preparation of a select command The final query execution strategy is determined at execution time once all actual parameter values are known Additionally query rewrites are applied to optimize the execution speed of certain select commands Control of User Access to Data MaxDB distinguishes two types of users database users and database administrators users of the MaxDB data manager tool Database users work with the database objects using SQL while DBM users monitor and service their DBMS instance s using the administration tool Every object in the database tables indexes views procedures users authorizations synonyms sequences is described and managed in the SQL catalog Special au
26. ledge Concepts of the Database System Architecture of the database system database parameters database tools users and security concepts logging B trees standby databases and more Reference Manual SQL statements and their syntax system tables special features in the MaxDB Oracle mode SQL Tutorial Use of SQL language for operations with data and tables of the MySQL MaxDB system using the demo schema HOTELDB as an example Messages 5 Glossary List of all messages Database system terms SAP Security Guide MySQL MaxDB Recommended security actions for the database system High Availability for the MySQL MaxDB Database User manual SAP liveCache Database Manager Database Manager GUI Web DBM Database Manager CLI Conditions for ensuring high availability of a MaxDB instance in an SAP solution Notes on managing SAP liveCache Creating and managing database instances Windows tool Browser tool Command line tool Database Analyzer Monitoring performance and resources of database instances detecting pain points Loader Loading and unloading data into and from MaxDB instances Query Tools SQL Studio Web SQL SQLCLI SQL queries and catalog management Windows tool Browser tool Command line tool WebDAV GUI Administration of a MaxDB WebDAV repository for XML documents Synchronization Manager 20 Administration of a MaxDB la
27. ll tools and interfaces go to http dev mysql com downloads maxdb This all software package contains every thing required to create and work with MaxDB including the database kernel the administration and access tools and the programming interface manager It does not however contain the MaxDB database manager GUI and MaxDB SQL studio tools available only for Windows You will find these tools at http dev mysql com downloads maxdb clients_75 html The interfaces can also be downloaded as separate packages 16 Downloads of the MaxDB development environment and released versions of the MaxDB sources are also available at www mysql com maxdb You can use CVS to download the latest MaxDB source code Former versions of MaxDB are archived at http downloads mysql com archives php p maxdb 7 5 00 Installation The MaxDB installation manager installs MaxDB on all supported operating systems During installation you can select one of the preconfigured desktop or laptop installations or perform a custom installation if for example MaxDB is to be used as a server database The sample database mentioned in the MaxDB tutorial can be loaded as an option The screens in Figures 15 and 16 show the MaxDB installation manager Installation Manager 7 5 00 19 91 MaxDB Installation Select database confiquration Desktop PC Laptop Typical installation for a
28. nd its administration tools MaxDB database manager GUI MaxDB database manager CLI as well as with the administration of SAP systems CCMS so that you can implement appropriate concepts for backing up MaxDB and discovering and analyzing performance bottlenecks Empowering Workshop MaxDB Performance Monitoring and Optimization UMEW60 This workshop deals with the basics of the MaxDB architecture Here you will learn how the database influences general system performance and how database intensive SAP transactions can 18 be identified You will also learn how to monitor MaxDB components such as the data cache volumes and locks You will practice dealing with bottleneck analysis using the database analyzer and practice monitoring expensive SQL statements using the command monitor and the resource monitor MaxDB experts will explain the concepts of the cost based optimizer and the secondary index design They will also teach you how to optimize expensive SQL statements and how to structure a performance analysis cookbook MaxDB Internals Workshop WBs550 This workshop deals with the internals of MaxDB ona detailed technical level Intended for expert level users and administrators it shows logging locking parameter handling error diagnostics storage handling and other important internal algorithms and data structures for MaxDB Training by MySQL MySQL AB offers two professional training courses for MaxDB users Max
29. ndscape with replicated datasets ODBC Manual MaxDB ODBC driver for Windows developers JDBC Manual MaxDB JDBC driver for Java developers SQLDBC Manual PHP Manual Native MaxDB driver for all operating system environments especially for C and C MaxDB PHP driver for PHP developers Perl Manual MaxDB Perl driver for Perl developers Python Manual MaxDB Python driver for Python developers Backint for MaxDB Description of the MaxDB Backint interface for external backup tools Development Development Environment Use of the MaxDB open source development environment Web Based Problem Tracking System Browser based system for tracking problem messages and processing Disclaimer This document reflects current product layout and current planning Contents may be changed without prior notice and are in no way binding upon SAP AG or MySQL AB WHY YOU NEED MaxDB MaxDB provides the solutions and tools that will help run your business efficiently in today s challenging enterprise environment It features Proven DBMS technology Attractive pricing Easy configuration and administration Around the clock operation Elaborate backup and restore capabilities Not only does MaxDB support a large number of users and demanding workloads it is available for all major operating system platforms Best of all it is bundled with the SAP NetWeaver platfo
30. on Manager GUI Client 1 DB I 1 I I I Client 2 DB Message DB Figure 13 MaxDB Synchronization Manager Architecture Operating MaxDB instance conventional laptops is problem and does require any administration For smaller devices for example Pocket PC or BlackBerry there is MinDB a pure Java DBMS with minimal resource requirements that can be operated via a JDBC Interface Like MaxDB MinDB can serve in bidirectional replication via the MaxDB synchronization manager MinDB provides a reduced SQL feature set but offers transactions multiple sessions and data persistence to external media such as memory sticks DEVELOPING DATABASE APPLICATIONS MaxDB provides a series of standardized programming interfaces for developing SQL based applications in different programming environments Programming Interfaces For application development MaxDB provides an open database connectivity ODBC driver in accordance with ODBC 3 5 For the Java community it provides a Java database connectivity JDBC driver according to JDBC 3 0 Additionally MaxDB provides its native call level interface SQL database connectivity SQLDBC on all supported operating system platforms for example for programming in C or C The architecture of SQLDBC is similar to that of ODBC and JDBC SQLDBC supports an unlimited number of connections and SQL statements 15 fully Unicode compliant provides flexible r
31. on and administration achieved by incorporating a GUI based MaxDB installation manager and MaxDB database manager as single administration tools for DBMS operations Around the clock operation no planned downtime thanks to automatic space management which ensures that permanent attendance and reorganizations are not required Elaborate backup and restore capabilities including online and incremental backups as well as a wizard to guide you through the recovery process Support for a large number of users and demanding workloads ensuring proven reliability performance and scalability for terabyte size databases High availability thanks to cluster support standby configuration and hot standby configuration Tools including MaxDB installation manager MaxDB database manager MaxDB SQL studio MaxDB loader MaxDB synchronization manager to replicate data between MaxDB instances and a WebDAV based document and XML repository Interfaces including ODBC JDBC SQLDBC PHP Perl and Python Most important MaxDB Is available for all major operating system platforms Supports all major SAP solutions Is bundled with the SAP NetWeaver platform How SAP DB Became MaxDB In 2003 SAP and MySQL concluded a partnership and development cooperation agreement As result the SAP open source database SAP DB is being delivered under the name MaxDB and has been since the release of version
32. pen source GPL or under a commercial license MaxDB software is covered 100 by the GPL thus if your application or solution connecting to the database is 100 GPL compliant you will be exempt from royalty This is a great opportunity for the open source community since it allows educational and trial usage and even supports the development phase of application software The commercial license is an agreement with MySQL AB for organizations that require MaxDB product support or do not wish to publish their application source code under GPL Consulting If MaxDB has been supplied by SAP AG and is used with SAP solutions consulting is provided by SAP AG MySQL AB also offers consulting services for MaxDB that cover the following areas Migration of data and functionality from other DBMSs to MaxDB Adaptation of existing applications to MaxDB including data base configuration tuning and optimization Assistance from database experts on application development projects a On site training tailored to customer requirements Support If MaxDB is used with SAP solutions or with SAP NetWeaver support is included in the SAP maintenance contract and is provided by SAP AG MySQL AB offers commercial MaxDB support to customers with a commercial license for MaxDB Two support levels are available Entry level support which comprises unlimited e mail communication with MaxDB experts from MySQL AB Premium suppor
33. remental backup items you want to use for recovery The use of incremental backups is much faster than the recovery of logs We recommend that you use the specified incremental backups PAG 000000002 21 09 2004 15 16 44 11 000000003 21 09 2004 15 19 02 12 NO 7616 1 000000003 23 09 2004 13 02 49 13 Figure 4 Select Recovery Path In Figure 5 the incremental backup results in the selected recovery path suggested by the recovery wizard Restore Database Instance Start Recovery Check your selection and start the recovery You have completed the steps required to perform a recovery Your recovery is defined below Backup ID State Jal Label Medium Device File External gt DAT_000000001 MD2_COM PAG_000000002 11 MD2_INC LOG_000000005 AL MD2_LOG 005 LOG_000000006 AL MD2_LOG 006 LOG_000000007 AL MD2_LOG 007 106 000000008 AL MD2_LOG 008 106 000000009 AL 2 106 009 106 000000010 AL MD2_LOG 010 gt J Make the specified medium available for recovery Choose Start to begin the recovery If you want to restore the database until a particular point in time specify the date and time Restore database until a specific time 13 Figure 5 Recovery Path Display In the event of media problems for example defective data volume the DBA has to recover the entire database once the media failure has been repaired The recovery is effected
34. rm and supports all major SAP solutions For More Information To find out more about MaxDB and learn how the database can transform your enterprise management capabilities go to www mysql com maxdb www sdn sap com gt Developer Areas gt Technologies gt MaxDB amp SAP liveCache or call your local SAP representative 21 wuww sap com contactsap 50 074 399 05 05 THE BEST RUN BUSINESSES RUN SAP SAPA
35. single user for the MaxDB tutorial or for testing purposes Beck Figure 15 MaxDB Installation Manager Choosing a Database Template Installation Manager 7 5 00 19 19 MaxDB Installation Database configuration Desktop PC Laptop Specify database name provide authorization deta Detebase Name demo Detebase Size MB Lood Tutorial Data Detabase System Administrator UserName mab User Possword Contirm Password 1 Figure 16 MaxDB Installation Manager Configuring the Database As an alternative to the MaxDB installation manager you can create MaxDB instances using the MaxDB database manager in interactive mode or in a batch mode via a command line script see section on MaxDB database manager CLI Licensing Model MaxDB is available in combination with SAP solutions or with the SAP NetWeaver technology and integration platform from SAP AG www sap com or www sdn sap com Costs are based on the SAP price list For a quotation on MaxDB for SAP appli cations please go to www sap com contactsap where you will be directed to the nearest SAP sales office MaxDB is also available as a stand alone database system for your own or third party solutions Go to MySQL AB www mysql com maxdb MySQL AB offers MaxDB under its dual licensing model In this model users may choose to use MaxDB under the free software o
36. sten aaiae A EREE dE EE 6 Operating System Platforms Supported by MaxDB 2 0 00 eee 6 Features and Functions of MaxDB as ccc aien aei eee acusa uswa 6 Data Oreanizaion and Storage uu oe saa pasaqa 6 Transactional Control of Data Changes aaa saa aen ayqiq ninie eee nee 7 Data Security and Conten ice yakupa Sak et eek EE oh alt aod i Puch Availability a eben 9 so yu cay een wid atid ee ao q qp 10 Control of User Access to Data u ul ccc 11 Secure ee a 12 Using the Database Management System 12 Matidsing Database Instances ies 12 Defining and Changing Data Using SOL aa esas enn eens 13 Monitoring the Database and Analyzing Problems 00 0000 cee cece eens 13 lmportuig atid Exporting Data 14 Data uya app cuy mp aon nak a al qasa A 14 Developing Database Applications 15 Programing Interfaces thn ye aoe ad aloes atone E 15 Implementing MaxDB ee 16
37. t which comprises e mail login and up to 24 7 phone support to customers with business critical applications on MaxDB 17 For users of the GPL version MySQL provides the MaxDB mailing list at http lists mysql com maxdb where MaxDB users can help each other Many expert users and MaxDB developers with years of experience actively participate in the list Participating in the mailing list is an excellent way for you to become a MaxDB expert You can also use the list to communicate feature requests as well as problem reports to the MaxDB development team MaxDB developers handle change requests and problem resolu tions in the internal MaxDB problem tracking system PTS The URL of the corresponding problem message is subsequently sent to the subscribers of the mailing list and can be used to monitor problem resolution via the publicly available Web PTS Training and Certification Training options are available from both SAP and SQL SAP Training SAP currently offers one MaxDB course and two workshops for SAP customers Database Administration MaxDB 515 Application developers experienced administrators and users working directly with MaxDB should attend this course It is also recommended for IT managers who want to learn more about MaxDB as well as users of the previous versions of MaxDB SAP DB and ADABAS D who plan to upgrade to MaxDB In this course you will become familiar with the MaxDB database a
38. the selected systems top right Pull down menus for the various administration tasks bottom left Main window central The main window shows detailed information about the action currently in focus Through MaxDB database manager MaxDB provides the option of reacting to administrative database events by using programs by automating the administration of database instances for rou tine cases such as maintenance of optimizer statistics or by using e mail to inform people about an event that occurred such as the shutdown of an instance MaxDB database manager is intended purely as a maintenance and monitoring tool for database operation It does not grant any access to the data or to the definition of database objects Defining and Changing Data Using SQL The MaxDB SQL studio MaxDB Web SQL and MaxDB SQLCLI query tools serve to Access the data and database catalog of MaxDB instances using SQL statements Insert and process data Query data Create and manage database objects and database users Two options are available the Windows based MaxDB SQL studio and the browser based MaxDB Web SQL MaxDB SQLCLI can be used to execute SQL commands on a command line basis for example from script languages EZ SQL Studio MONA maxdemo Local Host SQL Dialog 1 DJ SOL Session View SQt Dialog Window Help 8 m m m lt fel Q Ya 2a HR Oo select from
39. thorizations for catalog administration can be assigned to specific users MaxDB database administrators have no permission to access the database content They have no SQL access to the database nor do they have permission to create new database user accounts MaxDB database manager merely permits them to manage and monitor the database instances PF Ele Edt Favorites Tools Hep beck gt O A Gh meda OI aa 1 49 Web DBM maxdemo State 21 Data Cache Ht Rate 90 Auto Qe oe Database Full Bad ir ONLINE Sessions 7 Kerne Offline Admin Online a Data Information Versions 21 Auto Log onoff Name Value Kernel Kernel 7 6 00 Build 002 121 085 574 of Runtime Environment WS2ANTEL 7 6 00 Build 002 121 085 574 2 Figure 8 Browser Based Administration MaxDB Web Database Manager The creator of a data object implicitly becomes the owner of the object he or she creates and has all the authorizations privileges for it The owner can then forward access privileges D select update delete insert and more to other data base users A default schema is created implicitly behind the name of every user entered in the system The schema groups the data objects associated with that person into a separate namespace to avoid naming conflicts Schemas are an
40. xecute You can do most of them while the database is online This online approach which eliminates the need for costly maintenance downtime is a prerequisite for adaptive computing and self management It is also the reason MaxDB 15 less expensive than other enterprise DBMSs MaxDB is a rebranded and enhanced version of the SAP open source database formerly called SAP DB See sidebar It has been used for many years to support small medium and large installations of the mySAP Business Suite family of business solutions It has also been used for demanding SQL applications requiring an enterprise class DBMS because of the number of users on the system the transactional workload and the size of the database Today MaxDB is being used in about 3 500 SAP customer installations worldwide In addition MaxDB technology is bundled with SAP solutions such as mySAP Supply Chain Management SAP Solution Manager and multisite workforce deployment WFD and so on in approximately 7 000 customer installations It is the database of choice for the majority of internal SAP installations on UNIX and Linux MaxDB is designed for heavy duty online transaction processing OLTP with several thousand users and supports database sizes ranging from several hundred gigabytes GB to multiple terabytes TB Despite its suitability as an enterprise class DBMS MaxDB is not restricted to multiuser server based situations Because its ease of us
Download Pdf Manuals
Related Search