- Shopping Bag ( 0 items )
Other sellers (Paperback)
-
All (6) from $1.99
-
Used (6) from $1.99
Ships from: Mishawaka, IN
Usually ships in 1-2 business days
Ships from: Dallas, TX
Usually ships in 1-2 business days
Ships from: Tucson, AZ
Usually ships in 1-2 business days
Ships from: London, United Kingdom
Usually ships in 1-2 business days
Ships from: Maplewood, MO
Usually ships in 1-2 business days
Ships from: Fort Mill, SC
Usually ships in 1-2 business days
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:
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.
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 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.
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.
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.
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.
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.
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.
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.
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:
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.
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 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.
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.
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.
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.
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.
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.
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.
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:
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.
Anonymous
Posted Mon Jan 12 00:00:00 EST 2004
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 NoThank you for your feedback. Report this reviewThank you, this review has been flagged.Anonymous
Posted Sun Nov 02 00:00:00 EST 2003
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 NoThank you for your feedback. Report this reviewThank you, this review has been flagged.Anonymous
Posted Thu Oct 23 00:00:00 EDT 2003
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 NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Overview
Whether ...