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

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.

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.