From Book News, Inc.
This study guide covers the major concepts and techniques appearing on the DBA Certification exam--including administration, backup and recovery, performance tuning and scalability, networking and security, authentication, and encryption. The book features practice tests, followed by detailed explanations of the correct answers. A companion CD-ROM contains additional study questions, a sample exam, and a trial version of DB2. Snow is a database manager. Phan is a data management consultant.Copyright © 2004 Book News, Inc., Portland, OR


From the Back Cover
Definitive preparation for Exam 704: DB2 UDB V8.1 Advanced Database Administration for Linux, UNIX, and Windows, which leads to an IBM Certified Advanced Database Administrator certification.Database design, performance monitoring and tuning, scaling for terabytes of data, high availability, enterprise-level security, and more.The appendices contain sample certification exam questions and answers.

Whether you're a long-time DB2 UDB professional or an experienced DBA who's migrated from another database platform, there's one sure way to demonstrate the highest level of skills in DB2 Universal Database administration: pass the challenging IBM DB2 Universal Database Advanced Database Administration Exam 704.

Now, there's a definitive guide to advanced DB2 UDB database administration that also delivers all the depth and insight you need to prepare for the tough new IBM exam. Straight from the DB2 experts at IBM, this book offers systematic and authoritative coverage of every exam objective and will serve as a reference after you have passed the test.For the first time, this book brings together IBM best practices, techniques, tips, guidelines, and rules of thumb for virtually every aspect of advanced DB2 UDB v8 database administration-including all this and more:Ability to design, create, and manage table spacesAbility to design, create, and manage buffer poolsWhen and how to exploit parallelism in DB2 UDBConfiguring federated database accessDesigning and implementing a backup and recovery strategyAbility to implement a standby databaseMonitoring and tuning DB2Using and managing tables with multidimensional clusteringDesigning and configuring multiple database partitionsImplementing authentication and encryption on a large scaleLeveraging parallelism and symmetric multiprocessingPreparing for and executing recovery in an enterprise environmentRapid recovery in the event of table space or table level disastersEffective monitoring and problem determinationMaximizing performance and scalabilityAbout the CDThe accompanying CD-ROM contains the IBM® DB2® Universal Database Enterprise Server Edition Version 8.1, for Windows® Operating Environments, Evaluation Copy, plus DB2 UDB v8 Enterprise Server Edition documentation in PDF format.


About the Author

DWAINE R. SNOW has twelve years of experience implementing and tuning DB2 to support workloads from Web-based transactions up to multiterabyte data warehouses. Snow is the product manager for DB2 partitioned databases and is co-author of The DB2 Cluster Certification Guide.

THOMAS X. PHAN is an IBM Certified I/T Specialist in the IBM Data Management Consulting practice with over thirteen years of data processing experience. He specializes in relational database-related services including application design, logical and physical database design, and performance and tuning on numerous DB2 UDB platforms.


Excerpt. © Reprinted by permission. All rights reserved.
Preface

This book is a complete guide to the advanced features of IBM DB2 Universal Database (UDB) Enterprise Server Edition (ESE) Version 8.1. DB2 UDB Version 8.1 is available on many operating systems, and the book has been written with this in mind. Any significant differences in the implementation of DB2 on the various operating systems are highlighted. Although this book was written as a study guide for the new Advanced DBA certification (DB2 Exam number 704), it can also be used as a reference by DBAs as they perform their day-to-day activities.

The Advanced DBA exam contains the following sections:Advanced Administration22%Ability to design table spaces1%Ability to design buffer pools5%Ability to create buffer pools4%Ability to exploit inter-partition parallelism12%High Availability28%Ability to develop a logging strategy5%Ability to use advanced backup features8%Ability to use advanced recovery features10%Ability to implement a standby database5%Performance and Scalability31%Ability to identify and use DB2 registry variables that affect database system performance1%Manage and tune memory and I/O6%Ability to analyze performance problems1%Ability to manage a large number of users and connections3%Ability to partition large amounts of data for performance12%Ability to manage the number of partitions in a database8%Networking and Security19%Ability to configure a partitioned database on multiple servers8%Ability to manage connections to host systems3%Ability to identify and resolve connection problems5%Knowledge of external authentication mechanisms3%DB2: Any Workload, Any Data, All the Time

DB2 UDB ESE provides unparalleled performance and scalability to handle the most demanding workloads. Today, workloads are not strictly online transaction programming or data warehousing. More and more, customers are buying or building systems that combine both transaction processing and decision support workloads.

To satisfy the needs of your most complex databases and applications, IBM has extended the rich feature set of DB2 UDB to deliver unparalleled power and scalability to your entire enterprise. This has been accomplished through the features discussed below.Intelligent Data Distribution

DB2 UDB supports parallel queries through intelligent database partitioning. When a DB2 UDB ESE database is partitioned, DB2 automatically distributes the data across the database partitions, or subsets of the database, which can reside on multiple servers or within a large SMP server. A unique partition map allows DB2 to manage the distribution and redistribution of the data as required.

DB2 UDB uses a shared-nothing architecture that has proven to provide superior scalability, maintenance, and optimization, compared with a shared-disk architecture. The shared-nothing architecture eliminates the overhead of distributed lock management and distributed views required by a shared-disk architecture.Efficient Optimization

DB2's unrivaled cost-based SQL optimizer makes use of the database and system configuration information to evaluate the potential execution paths for an SQL query and choose the lowest-cost path for execution. DB2 UDB has an enhanced optimizer that supports SQL query rewrite, OLAP SQL extensions, Dynamic Bit Mapped Indexing, and star joins commonly used in data warehousing.Parallel Everything

In DB2 UDB, access plans are automatically created for parallel execution with standard SQL, and no additional programming is needed. DB2's parallel execution applies to SELECT, INSERT, UPDATE, and DELETE functions. Data scans, joins, sorts, load balancing, table reorganization, data load, index creation, indexed access, backup, and restore can all be performed on all database partitions simultaneously.

Although DB2 can break a query into a number of pieces that can be executed in parallel, the scalability of DB2 with intra-partition parallelism only (i.e., using SMP-type parallelism with a single database image) can be limited or restricted by the underlying operating system or hardware.

Creating multiple database partitions within a larger SMP server (or across multiple servers) has proven to provide better scalability than intra-partition parallelism alone. As the number of CPUs grows, the scalability decreases when using intra-partition parallelism alone. By creating multiple database partitions within the SMP server, the scalability is able to remain almost linear.Supreme Scalability

As you expand your data warehouse, accommodate more users, and move projects from pilot to production, you'll appreciate the predictably scalable performance of DB2 UDB. Its shared-nothing architecture allows parallel database queries with minimal data transfer between database partitions. Because the number of database partitions has little impact on inter-partition traffic, performance scales in a near-linear fashion when you add more servers to your cluster of servers or add SMP servers to an existing server cluster.

Multi-dimensional clustering (MDC) provides an elegant method of ensuring flexible, continuous, and automatic clustering of data based on multiple dimensions within a table. This can result in significant improvement in the performance of queries, as well as significant reduction—or even elimination—in the overhead of data maintenance operations such as table reorganization and index maintenance operations during INSERT, UPDATE, and DELETE operations.

In many of today's database servers, a process or thread is dedicated to each client that connects to a database. For a typical OLTP workload that handles large numbers of connected users who perform relatively short-lived transactions with some delay between subsequent transactions, this puts a heavy load on the database server because system resources are being tied up by client connections that are not performing any work. DB2 UDB Version 8.1 has implemented a connection multiplexing architecture called the Connection Concentrator that will allow users to move from a configuration where the number of connected users is constrained by the physical limitations of the underlying hardware to a scenario where the limiting factor will be based solely on the transaction load and the machine's ability to handle such a load.

Materialized query tables (previously ASTs) allow you to precompute some typical table joins, queries, aggregates, etc., that can be reused by other users. By reusing the result set and not having to rerun the statements each time, the response time is much faster and the resources required are drastically reduced.Database Partitioning

The DB2 UDB Database Partitioning Feature (DPF) is required in order to partition your DB2 UDB ESE database, either within a single server or across multiple servers. The DPF is a license only and does not require any products additional to DB2 UDB ESE to be installed on your database server to support database partitioning.

In the past, database partitioning was provided by DB2 UDB Enterprise-Extended Edition (EEE), and to partition a database, this product needed to be installed. With DB2 UDB Version 8.1, if you already have DB2 UDB ESE installed and determine that it would be beneficial to partition the database, there is no need to remove or install any software. You need only purchase the DPF for the server(s) where you will create the database partitions.Conventions Used Throughout the Book

Many examples of SQL statements, DB2 commands, and operating system commands are included throughout the book. If SQL keywords are referred to in the text portion of the book, they will be written in CAPITALS. For example, the SELECT statement is used to retrieve data from a DB2 database.

DB2 commands will be shown using the same method as SQL keywords. For example, the CREATE DATABASE command allows you to define the initial location of database objects. DB2 UDB commands are issued from the Command Line Processor (CLP) utility. This utility will accept the commands in upper- and lowercase. The CLP program itself is an executable called db2.

In the UNIX operating systems, program names are case sensitive. Therefore, be careful to enter the program name using the proper case. On UNIX, db2 must be entered in lowercase.

Displayed SQL statements and commands usually appear within a shaded box.Acknowledgments

Dwaine and Tom would like to thank Patti Cartwright and Bob Harbus for their dedication and going above and beyond our expectations. Their review of the book and suggestions were invaluable in ensuring that the material is useful, understandable, and meets the objectives of the test.

The following people also provided a great deal of assistance, either through contributions to the book, technical expertise, and/or comments:John Hornibrook, IBM Toronto LabIan Finlay, IBM Toronto LabGuy Lohman, IBM Silicon Valley LabKelly Schlamb, IBM Toronto LabDale McInnis, IBM Toronto LabLeslie Cranston, IBM Toronto LabAdriana Zubiri, IBM Toronto LabCalisto Zuzarte, IBM Toronto LabSteve Raspudic, IBM Toronto LabPeter Shum, IBM Toronto LabLarry Pay, IBM Toronto LabJon Rubin, IBM Silicon Valley LabTrademarks

DB2, DB2 Universal Database, AIX, IBM, and the IBM logo are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Sun, Solaris, Java, and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and/or other countries.

Microsoft, Windows, Windows NT, Windows 2000, Windows XP, and the Windows logo are trademarks of Microsoft Corporation in the United States and/or other countries.

UNIX is a registered trademark in the United States and other countries, licensed exclusively through the X/Open Company Ltd.

Other company, product, and service names may be trademarks or registered trademarks of their respective companies.

FROM THE PUBLISHER

Definitive preparation for Exam 704: DB2 UDB V8.1 Advanced Database Administration for Linux, UNIX, and Windows, which leads to an IBM Certified Advanced Database Administrator certificationDatabase design, performance monitoring and tuning, scaling for terabytes of data, high availability, enterprise-level security, and moreThe appendices contain sample certification exam questions and answers

Whether you're a long-time DB2 UDB professional or an experienced DBA who's migrated from another database platform, there's one sure way to demonstrate the highest level of skills in DB2 database administration: pass the challenging IBM DB2 Universal Database Advanced Database Administration Exam.

Now, there's a definitive guide to advanced DB2 UDB database administration that also delivers all the depth and insight you need to prepare for the tough new IBM exam. Straight from the DB2 experts at IBM, this book offers systematic and authoritative coverage of every exam objective and will serve as a reference after you have passed the test.

For the first time, this book brings together IBM best practices, techniques, tips, guidelines, and rules of thumb for virtually every aspect of advanced DB2 UDB v8 database administration--including all this and more:Ability to design, create, and manage table spacesAbility to design, create, and manage buffer poolsWhen and how to exploit parallelism in DB2 UDBConfiguring federated database accessDesigning and implementing a backup and recovery strategyAbility to implement a standby databaseMonitoring and tuning DB2Using and managing tables using multidimensional clusteringDesigning and configuring multiple database partitionsImplementing authentication and encryption on a large scaleLeveraging parallelism and symmetric multiprocessingPreparing for and executing recovery in an enterprise environmentRapid recovery in the event of table space or table level disastersEffective monitoring and problem determinationMaximizing performance and scalabilityAbout the CD-ROM

The accompanying CD-ROM contains the IBM DB2 Universal DatabaseEnterprise Server Edition Version 8.1, for Windows Operating Environments,Evaluation Copy, plus DB2 UDB v8 Enterprise Server Edition documentation inPDF format.

SYNOPSIS