Advanced DBA Certification Guide and Reference: for DB2 Universal Database v8.1 for Linux, UNIX, and Windows

Overview

  • 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 ...

See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (6) from $1.99   
  • Used (6) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$1.99
Seller since 2006

Feedback rating:

(59850)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

Good
Former Library book. Sorry, CD missing. Shows some signs of wear, and may have some markings on the inside. 100% Money Back Guarantee. Shipped to over one million happy customers. ... Your purchase benefits world literacy! Read more Show Less

Ships from: Mishawaka, IN

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2009

Feedback rating:

(6715)

Condition: Very Good
Nice condition with minor indications of previous handling. Book selection as BIG as Texas.

Ships from: Dallas, TX

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$8.25
Seller since 2007

Feedback rating:

(4268)

Condition: Acceptable
2003 Paperback Fair Satisfaction 100% guaranteed.

Ships from: Tucson, AZ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$20.20
Seller since 2010

Feedback rating:

(21)

Condition: Good
07/07/2003 Paperback 1 Good **SHIPPED FROM UK** We believe you will be completely satisfied with our quick and reliable service. All orders are dispatched as swiftly as ... possible! Buy with confidence! *****PLEASE NOTE: This item is shipping from an authorized seller in Europe. In the event that a return is necessary, you will be able to return your item within the US. To learn more about our European sellers and policies see the BookQuest FAQ section***** Read more Show Less

Ships from: London, United Kingdom

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
$22.00
Seller since 2008

Feedback rating:

(72)

Condition: Very Good
Lebanon, Indiana, U.S.A. 2003 Paperback Very Good 2003. Paperback, with CD, in very good condition.

Ships from: Maplewood, MO

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$25.99
Seller since 2014

Feedback rating:

(37)

Condition: Good
Buy with Confidence. Excellent Customer Support. We ship from multiple US locations. No CD, DVD or Access Code Included.

Ships from: Fort Mill, SC

Usually ships in 1-2 business days

  • Standard, 48 States
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

  • 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 spaces
  • Ability to design, create, and manage buffer pools
  • When and how to exploit parallelism in DB2 UDB
  • Configuring federated database access
  • Designing and implementing a backup and recovery strategy
  • Ability to implement a standby database
  • Monitoring and tuning DB2
  • Using and managing tables with multidimensional clustering
  • Designing and configuring multiple database partitions
  • Implementing authentication and encryption on a large scale
  • Leveraging parallelism and symmetric multiprocessing
  • Preparing for and executing recovery in an enterprise environment
  • Rapid recovery in the event of table space or table level disasters
  • Effective monitoring and problem determination
  • Maximizing performance and scalability
About 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.
Read More Show Less

Product Details

  • ISBN-13: 9780130463883
  • Publisher: IBM Press
  • Publication date: 7/10/2003
  • Series: IBM Press Series--Information Management
  • Edition description: BK&CD-ROM
  • Pages: 800
  • Product dimensions: 6.98 (w) x 9.17 (h) x 1.46 (d)

Meet 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.

Read More Show Less

Table of Contents

Foreword.

Preface.

1. Data Storage.

Some Terminology Used in DB2. The Default Database. Table Spaces. Containers. Extents. Creating Table Spaces. The Table Space Types. SMS Table Spaces. DMS Table Spaces. Should the Temporary Table Space Be SMS or DMS? Additional Table Space Options/Parameters. Listing Table Spaces. SYSCAT.TABLESPACES View. SYSCAT.TABLES View. When Is a Table Space Full? Table Space Maps and Table Space Extent Maps. Altering a Table Space. The Table Space's Partition Group. Database Partition Groups. Disk Layout. Summary.

2. Data Manipulation.

The Buffer Pools. Buffer Pool Usage. How Many Buffer Pools Should a Database Have? When to Consider Multiple Buffer Pools. Buffer Pool Overhead. 32- and 64-bit Considerations. Hidden Buffer Pools. Altering Buffer Pools. Block-Based Buffer Pools Can Improve Sequential Prefetching. System Catalog Views Relating to Buffer Pools. What Is Prefetching? What Is Page Cleaning? Monitoring Buffer Pool Activity. Buffer Pool Tuning. Parallelism. Intra-Partition Parallelism. Inter-Partition Parallelism. Combined Intra-Partition Parallelism and Inter-Partition Parallelism. Why Partition a Database on a Large SMP Server? Considerations with Multiple Database Partitions. Types of Database Objects. Tables. Partition Maps. The Relationship between Partition Groups, Table Spaces, and Tables. Partitioning Keys. Summary.

3. Database Availability.

Ability to Manage Logs. Primary Logs (LOGPRIMARY). Secondary Logs (LOGSECOND). Log File Size (LOGFILSIZ). Log Buffer Size (LOGBUFSZ). Number of Commits to Group (MINCOMMIT). New Log Path (NEWLOGPATH). Overflow Log Path (OVERFLOWLOGPATH). Log Mirroring. Reducing Logging with the NOT LOGGED INITIALLY Parameter Option. Reducing Logging with Declared Global Temporary Tables (DGTTs). Managing Log Files. Archiving Log Files with a User Exit Program. How Truncated Logs Are Handled. Log File Allocation and Removal. Blocking Transactions When the Log Directory File Is Full. On Demand Log Archive. Using Raw Logs. How to Prevent Losing Log Files. Review Session. Observation. Developing a Backup and Recovery Strategy. The Recovery History File. Deciding How Often to Back Up. Storage Considerations. Keeping Related Data Together. Using Different Operating Systems. Crash Recovery. Disaster Recovery. Version Recovery. Rollforward Recovery. Incremental Backup and Recovery. Backup Overview. Displaying Backup Information. Authorities Required to Use Backup. Using Backup. Backing Up to Tape. Backing Up to Named Pipes. Backup Database: Examples. Optimizing Backup Performance. Offline versus Online Backup. Backup Considerations. Restore Overview. Authorities Required to Use Restore. Using Restore. Using Incremental Restore in a Test and Production Environment. Redefining Table Space Containers During a Restore Operation (Redirected Restore). Restoring to an Existing Database. Restoring to a New Database. Restore Database: Examples. Optimizing Restore Performance. Restore Considerations. Restore to a Damaged Partition. Rollforward Overview. Authorities Required to Use Rollforward. Using Rollforward. Rollforward Database: Examples. High Availability Clustering. Split Mirror. Suspend I/O Feature. The db2inidb Tool. High Availability through Log Shipping. High Availability through Online Split Mirror and Suspended I/O Support. Split Mirror to Clone a Database. Split Mirror as a Standby Database. Split Mirror as a Backup Image. High Availability on AIX. Scenario #1: Hot Standby with a Cascading Resource Group. Scenario #2: Mutual Takeover with a Cascading Resource Group. High Availability on the Windows Operating System. Clustered Servers for High Availability. High Availability on Sun Solaris. High Availability with VERITAS Cluster Server. Considerations for High Availability with DB2 ESE. Sun Cluster 3.0 DB2-HA Agent—Packages. Sample Configuration Sun Cluster 3.x and DB2 UDB. Configuration of Multiple DB2 Instances. High Availability on HP/UX. Availability Enhancements. Online Table Load. Incremental Maintenance of Materialized Query Tables During Load Append. Online Table Reorganization. Online Index Reorganization. Ability to Use db2dart. Summary.

4. Database Security.

Selecting User Names and Group Names for DB2 Installation. Recommendations. User and Group Naming Rules. Authentication Methods. SERVER. SERVER_ENCRYPT. CLIENT. KERBEROS Authentication. Authentication Considerations for Remote Clients. Partitioned Database Authentication Considerations. Privileges, Authorities, and Authorizations. Tasks and Required Authorizations. Using the System Catalog for Security Issues. Firewall Support. What Is LDAP? Supported LDAP Client and Server Configurations. Support for Windows Active Directory. Configuring DB2 to Use Microsoft Active Directory. Configuring DB2 in the IBM LDAP Environment. Creating an LDAP User. Configuring the LDAP User for DB2 Applications. Registration of DB2 Servers after Installation. Update the Protocol Information for the DB2 Server. Catalog a Node Alias for ATTACH. Deregistering the DB2 Server. Attaching to a Remote Server in the LDAP Environment. Deregistering the Database from the LDAP Directory. Refreshing LDAP Entries in Local Database and Node Directories. Searching the LDAP Directory Partitions or Domains. Setting DB2 Registry Variables at the User Level in the LDAP Environment. Enabling LDAP Support after Installation Is Complete. Removing/Disabling LDAP Support. Security Considerations in an LDAP Environment. Security Considerations for Windows 2000 Active Directory. Extending the LDAP Directory Schema with DB2 Object Classes and Attributes. Extending the Directory Schema for Windows 2000 Active Directory. DB2 Objects in the Windows 2000 Active Directory. Netscape LDAP Directory Support and Attribute Definitions. Security Implementation and Usage. Column-Level Encryption (CLE). Row-Column (Cell) or Set-Column Level Encryption (SCLE). Encrypting Non-Character Values (ENCV). Performance Considerations. How DB2 for Windows NT/2000 Works with Windows NT/2000 Security. Terminology. Windows NT/2000 Authentication. Trust Relationships between Domains. User Authentication with DB2 for Windows NT. DB2 for Windows NT User Name and Group Name Restrictions. Groups and User Authentication on Windows NT. Trust Relationships between Domains on Windows NT. DB2 for Windows NT Security Service. Installing DB2 on a Backup Domain Controller. DB2 for Windows NT Authentication with Groups and Domain Security. Summary.

5. Multi-Dimensional Clustering.

MDC Terminology. Design Guidelines for MDC Tables. Identify Candidate Dimension Attributes. Estimating the Number of Cells. Estimating the Actual Space Usage per Cell. Varying the Number of Dimension Keys. Varying the Granularity of a Dimension Key. Varying the Block Size and Page Size Values. Using a Regular Table as a Baseline for the MDC Table Design. Summary of MDC Design Guidelines. MDC Performance Guidelines. Query Processing Overview. Block Index Scans. Block Index ANDing. Block Index ORing. Combining Dimension Block Indexes and Record Indexes. Index-Only Access Restrictions on Block Indexes. Monotonicity. Space Usage with MDC Tables. MDC Indexes. Summary.

6. The DB2 Optimizer.

Joining in DB2 UDB. Join Methods. Joining in a Partitioned Database. Broadcast Table Joins. Partitioned Join Strategies and Performance. Materialized Query Tables. Avoiding Repetitious Calculations. REFRESH IMMEDIATE vs REFRESH DEFERRED. Understanding DB2 Query Access Plans. Overview Area. Database Context Area. Package Context Area. Original Statement. Optimized Statement. Access Plan. Recognizing List Prefetch. Recognizing Index ORing. Recognizing Index ANDing. Handling Predicates. Definitions and Terminology. Sargable Predicates vs Residual Predicates. Analyzing Problem SQL Statements. Steps in Analyzing a Poorly Performing Query. Determining Good Indexes. Writing Better SQL Statements. Additional Ways to Help the Optimizer. Summary.

7. Federated Database Access.

What Is a Federated System? Federated Server. Data Sources. Federated Database. The SQL Compiler and the Query Optimizer. Compensation. Pass-Through Sessions. Wrappers and Wrapper Options. Server Definitions. User Mappings and User Options. Nicknames and Data Source Objects. Column Options. Data Type Mappings. Function Mappings and Function Templates. Index Specifications. Overview of the Tasks to Set Up a Federated System. How Do You Interact with a Federated System? DB2 Command Line Processor (CLP). DB2 Command Center. DB2 Control Center. Application Programs. Setting Up the Federated Server and Database. Setting Up the Server. Configuring Access to Data Sources. Improving Performance by Setting the DB2_DJ_COMM Environment Variable (UNIX). Working with the Federated Data. Working with Nicknames. Transaction Support in a Federated System. Selecting Data in a Federated System. Modifying Data in a Federated System. Sample Configuration Federated Between DB2 on OS/390 and DB2 on Sun Solaris. Worksheet: For Windows Clients Connecting to DB2 for OS/390 Database via DB2 Connect Gateway. Federated Database System Test Scenarios. Using DB2 Connect (Optional). Configuration of a Federated Database System. Enabling Federated Database Functionality. Adding a DB2 Data Source to a Federated System. Verifying Connections to a DB2 Data Source. SQL Statements Summary. Federated Database Considerations. Network Considerations. Nickname Characteristics Affecting Global Optimization. Index Considerations. Distributed Queries Using Materialized Query Tables (MQTs) on Nicknames. Analyzing Query Optimization. Summary.

8. Performance Tuning.

Performance Fundamentals. The Magic Triangle of Performance. Ensure Enough Available Memory. Ensure Sufficient I/O Handling Capability. Use the DB2 Configuration Advisor for an Initial Set of Database Configuration Parameters. Use Proper Indexes. Do Not Hold Locks Longer than Absolutely Necessary. Use Efficient SQL. Use Parallelism Only When the Workload Requires. Ensure Current Catalog Statistics. Configuration Parameter Tuning. Database Manager Configuration Parameter Tuning and Monitoring. Application Support Layer Heap Size (ASLHEAPSZ). Maximum Requester I/O Block Size (RQRIOBLK). Sort Heap Threshold (SHEAPTHRES). Enable Intra-Partition Parallelism (INTRA_PARALLEL). Maximum Query Degree of Parallelism (MAX_QUERYDEGREE). Query Heap Size (QUERY_HEAP_SZ). Number of FCM Buffers (FCM_NUM_BUFFERS). Agent Pool Size (NUM_POOLAGENTS). Initial Number of Agents in the Agent Pool (NUM_INITAGENTS). Priority of Agents (AGENTPRI). Keep Fenced Process (KEEPFENCED). Maximum Total of Files Open (MAXFILOP). Database Configuration Parameter Tuning and Monitoring. Default Buffer Pool Size (BUFFPAGE). Log Buffer Size (LOGBUFSZ). Application Heap Size (APPLHEAPSZ). Sorting (SORTHEAP, SHEAPTHRES_SHR). Locking (LOCKLIST, MAXLOCKS, LOCKTIMEOUT, DLCHKTIME). Number of Asynchronous Page Cleaners (NUM_IOCLEANERS). Number of I/O Servers (NUM_IOSERVERS). Number of Commits to Group (MINCOMMIT). Catalog Cache Size (CATALOGCACHE_SZ). Changed Pages Threshold (CHNGPGS_THRESH). Average Number of Active Applications (AVG_APPLS). Registry Variable Tuning. DB2_USE_PAGE_CONTAINER_TAG. DB2_HASH_JOIN. DB2_ANTIJOIN. DB2_INLIST_TO_NLJN. DB2_CORRELATED_PREDICATES. DB2_REDUCED_OPTIMIZATION. DB2_OVERRIDE_BPF. DB2_PINNED_BP. DB2_FORCE_FCM_BP. DB2_AWE. DB2_BINSORT. DB2_AVOID_PREFETCH. DB2TCPCONNMGRS. DB2MAXFSCRSEARCH. DB2_SELECTIVITY. DB2_PRED_FACTORIZE. DB2MEMDISCLAIM. DB2MEMMAXFREE. DB2_MMAP_READ. DB2_MMAP_WRITE. Other Important Configuration Parameters. CPUSPEED. DIAGLEVEL/NOTIFYLEVEL. Summary.

9. Database Communications.

Database Partition Configuration File (db2nodes.cfg). Windows Considerations. Default Port Range. Sample db2nodes.cfg and /etc/services Configuration. Non-Partitioned Database Environment. Preparing the Environment for Installation. Installing the DB2 Server. Verifying the Installation. Partitioned Database Environment. Preparing the Environment for Installation. Installing the DB2 Server. Post-Installation Setup. Verifying the Installation. Installing and Configuring DB2 Client to DB2 Server Communications. Installing and Configuring DB2 Client to DB2 Host Communications. Preparing the DB2 for OS/390 for DB2 Connect. Installing DB2 Connect Personal Edition. Configuring DB2 Clients in a Two-Tier Environment. Configuring DB2 Clients in a Three-Tier Environment. Configure the Host. Configure DB2 Connect Gateway to Accept Incoming Clients. Configure the DB2 Client. FCM Communication Buffers Considerations. Example of Overallocated FCM Communication Buffers. Summary.

10. Performance Tuning Examples.

Example 1: Create Index Taking Too Long. Problem Description. Problem Analysis and Resolution. Example 2: Unable to Get Desired Throughput. Problem Description. Problem Analysis and Resolution. Example 3: Query1 Taking Too Long to Run. Problem Description. Problem Analysis and Resolution. Example 4: Query2 Taking Too Long to Run. Problem Description. Problem Analysis and Resolution. Example 5: Periodic Noticeable Slowdown of Database Response Time. Problem Description. Problem Analysis and Resolution.

11. Useful DB2 Commands.

System Commands. db2adutl—Work with TSM Archived Images. db2ckbkp—Check Backup. db2ckrst—Check Incremental Restore Image Sequence. db2flsn—Find Log Sequence Number. db2inidb—Initialize a Mirrored Database. CLP Commands. ARCHIVE LOG. LIST HISTORY. PRUNE HISTORY/LOGFILE. UPDATE HISTORY FILE. ONLINE Utility. INSPECT. parseSQL.pl. exfmtDIF.pl. Comparing Index Advisor Recommended Indexes with Existing Indexes. Configuration Parameters That Can Be Changed Online. Explain Tools. EXPLAIN Tables. EXPLAIN Commands. db2exfmt. db2expln. dynexpln. EXPLAIN Output. Starting and Stopping a DB2 Instance. LDAP Integration in DB2 UDB Using Microsoft Active Directory. Implementation Guidelines. LDAP Directory Server. DB2 UDB Server. DB2 UDB Client. Test Scenarios. Tuning DB2 UDB in the IBM LDAP Environment. DB2 Support Information. DB2 Support Resources. Opening a PMR. Collecting Diagnostic Information. 1. Operating System Information. 2. DB2 Information. 3. Copy of the db2diag.log, Any trap and dump Files. 4. Details of the Errors. 5. DB2 Trace. 6. DB2 DRDA Trace. 7. DB2 CLI Trace. Sending Diagnostic Information. DB2 Connect Implementation. Installing DB2 Connect Enterprise Edition. Sample Questions. Answers to Sample Questions.

Index.

Read More Show Less

Preface

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 Administration 22%
Ability to design table spaces 1%
Ability to design buffer pools 5%
Ability to create buffer pools 4%
Ability to exploit inter-partition parallelism 12%
High Availability 28%
Ability to develop a logging strategy 5%
Ability to use advanced backup features 8%
Ability to use advanced recovery features 10%
Ability to implement a standby database 5%
Performance and Scalability 31%
Ability to identify and use DB2registry variables that affect database system performance 1%
Manage and tune memory and I/O 6%
Ability to analyze performance problems 1%
Ability to manage a large number of users and connections 3%
Ability to partition large amounts of data for performance 12%
Ability to manage the number of partitions in a database 8%
Networking and Security 19%
Ability to configure a partitioned database on multiple servers 8%
Ability to manage connections to host systems 3%
Ability to identify and resolve connection problems 5%
Knowledge of external authentication mechanisms 3%

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 Lab
  • Ian Finlay, IBM Toronto Lab
  • Guy Lohman, IBM Silicon Valley Lab
  • Kelly Schlamb, IBM Toronto Lab
  • Dale McInnis, IBM Toronto Lab
  • Leslie Cranston, IBM Toronto Lab
  • Adriana Zubiri, IBM Toronto Lab
  • Calisto Zuzarte, IBM Toronto Lab
  • Steve Raspudic, IBM Toronto Lab
  • Peter Shum, IBM Toronto Lab
  • Larry Pay, IBM Toronto Lab
  • Jon Rubin, IBM Silicon Valley Lab

Trademarks

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.

Read More Show Less

Introduction

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 Administration 22%
Ability to design table spaces 1%
Ability to design buffer pools 5%
Ability to create buffer pools 4%
Ability to exploit inter-partition parallelism 12%
High Availability 28%
Ability to develop a logging strategy 5%
Ability to use advanced backup features 8%
Ability to use advanced recovery features 10%
Ability to implement a standby database 5%
Performance and Scalability 31%
Ability to identify and use DB2 registry variables that affect database system performance 1%
Manage and tune memory and I/O 6%
Ability to analyze performance problems 1%
Ability to manage a large number of users and connections 3%
Ability to partition large amounts of data for performance 12%
Ability to manage the number of partitions in a database 8%
Networking and Security 19%
Ability to configure a partitioned database on multiple servers 8%
Ability to manage connections to host systems 3%
Ability to identify and resolve connection problems 5%
Knowledge of external authentication mechanisms 3%

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 Lab
  • Ian Finlay, IBM Toronto Lab
  • Guy Lohman, IBM Silicon Valley Lab
  • Kelly Schlamb, IBM Toronto Lab
  • Dale McInnis, IBM Toronto Lab
  • Leslie Cranston, IBM Toronto Lab
  • Adriana Zubiri, IBM Toronto Lab
  • Calisto Zuzarte, IBM Toronto Lab
  • Steve Raspudic, IBM Toronto Lab
  • Peter Shum, IBM Toronto Lab
  • Larry Pay, IBM Toronto Lab
  • Jon Rubin, IBM Silicon Valley Lab

Trademarks

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.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 3 Customer Reviews
  • Anonymous

    Posted January 12, 2004

    Advanced DBA Certification Guide and Reference: for DB2 Universal Database v8.1 for Linux, UNIX, and Windows

    This is the best DB2 book I have read. The book is very well organized and covers all of the topics that a DBA needs to do their job better. I have already found a number of tips in the book that have helped me make my databases faster.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted November 2, 2003

    Advanced DBA Certification Guide and Reference: for DB2 Universal Database v8.1 for Linux, UNIX, and Windows

    I bought this book to study for the new advanced DBA certification test, but found that it has material that is not available any where else. I definitely will keep this book on my desk for future reference.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted October 23, 2003

    A Very Good Read

    Overall, a very good technical reference for DB2. They did a thorough job of explaining the DB2 intricacies and making it very readable. The chapter on the DB2 Optimizer did a good job of explaining why DB2 takes one access path over another. The Performance Tuning chapter also stands out for it's detail. I expect to use it repeatedly as a desktop reference.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing all of 3 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)