You are previewing DB2 9 for Linux, UNIX, and Windows Advanced Database Administration Certification Study Guide.
O'Reilly logo
DB2 9 for Linux, UNIX, and Windows Advanced Database Administration Certification Study Guide

Book Description

Database administrators versed in DB2 wanting to learn more about advanced database administration activities and students wishing to gain knowledge to help them pass the DB2 9 UDB Advanced DBA certification exam will find this exhaustive reference invaluable. Written by two individuals who were part of the team that developed the certification exam, this comprehensive study guide prepares the student for challenging questions on database design; data partitioning and clustering; high availability diagnostics; performance and scalability; security and encryption; connectivity and networking; and much more. Providing sample questions in each chapter, a complete practice test modeled after the actual exam, and an extensive answer key providing full explanations for each correct answer, readers will find this to be a key resource in stimulating the learning process.

Table of Contents

  1. Copyright
    1. Dedication
  2. Acknowledgments
  3. About The Authors
  4. Foreword
  5. Preface
    1. About this Book
    2. Conventions Used
  6. 1. IBM DB2 9 Certification
    1. DB2 9 Certification Roles
      1. IBM Certified Database Associate – DB2 9 Fundamentals
      2. IBM Certified Database Administrator – DB2 9 for Linux, UNIX, and Windows
      3. IBM Certified Database Administrator – DB2 9 for z/OS
      4. IBM Certified Application Developer – DB2 9 Family
      5. IBM Certified Advanced Database Administrator – DB2 9 for Linux, UNIX, and Windows
      6. IBM Certified Solution Designer – DB2 Data Warehouse Edition V9.1
      7. IBM Certified Solution Developer – DB2 9.5 SQL Procedure Developer
    2. The Certification Process
      1. Preparing for the Certification Exams
      2. Arranging to Take a Certification Exam
      3. Taking an IBM Certification Exam
  7. 2. Database Design
    1. Servers, Instances, and Databases
    2. Creating a Non-partitioned DB2 Database
      1. What Happens When a DB2 9.x Database Is Created
      2. The Complete CREATE DATABASE Command
      3. Creating a DB2 Database with the Create Database Wizard
    3. Creating a Partitioned DB2 Database
      1. Automatic Storage and Partitioned Databases
      2. The Directory Tree for a Partitioned Database
    4. Buffer Pools
      1. Creating New Buffer Pools
      2. Modifying Existing Buffer Pools
      3. DB2’s Hidden Buffer Pools
      4. How Many Buffer Pools Should a Database Have?
      5. Block-Based Buffer Pools
      6. A Word About Page Cleaners
        1. Choosing the right number of page cleaners
        2. Setting the right changed pages threshold value
    5. Table Spaces
      1. A Word about Storage Containers
        1. Raw Devices Versus Files
      2. Table Space Types
        1. Regular Table Spaces
        2. Large Table Spaces
        3. Temporary Table Spaces
      3. Automatic Storage Table Spaces
      4. A Word about the System Catalog Table Space
      5. Creating New Table Spaces
        1. Creating Table Spaces That Span Multiple Database Partitions
      6. Modifying Existing Table Spaces
        1. Adding new containers to existing automatic storage table spaces
      7. The Table Space High-Water Mark
        1. Lowering the Table Space High-Water Mark
      8. A Closer Look at Prefetching
        1. How Does Prefetching Work?
        2. Other Roles of the Prefetchers (I/O Servers)
        3. Choosing the Optimal Prefetch Size
        4. Choosing the Optimum Number of Prefetchers (I/O Servers)
    6. Expanding a Database’s Boundaries with Federated Systems
      1. Federated Servers
      2. Data Sources
      3. Federated Databases
      4. Wrappers
      5. Server Definitions
      6. Nicknames
      7. Data Type Mappings
      8. Function Mappings
      9. Index Specifications
      10. User Mappings
      11. A Word about Pass-through Sessions
    7. Practice Questions
    8. Answers
  8. 3. Data Partitioning and Clustering
    1. Parallelism
      1. Input/Output Parallelism
      2. Query Parallelism
        1. Intra-partition Parallelism
        2. Inter-partition Parallelism
        3. Intra-partition Parallelism and Inter-partition Parallelism
      3. Utility Parallelism
      4. Types of Parallelism Supported by Common Hardware Configurations
    2. Partitioned Databases—A Closer Look
      1. The DB2 Node Configuration File (db2nodes.cfg)
      2. The Default Port Range
      3. The Catalog Partition
      4. Coordinator Partitions
      5. Adding Database Partitions to a Partitioned Database System
      6. A Word About Creating Multiple Database Partitions Within a Single Server
      7. Why Partition a Database on a Large SMP Server?
    3. Partition Groups
      1. Creating Database Partition Groups
      2. Modifying Existing Database Partition Groups
    4. Partitioning Keys and Partitioning Maps
      1. Choosing a Partitioning Key
      2. Changing the Partitioning Key
      3. A Word About Indexes and Partitioning Keys
      4. Partitioning (Distribution) Maps
      5. A Word About Collocated Tables
    5. Range Clustering and Range Partitioning
      1. Range-Clustered Tables
      2. Range-Partitioned Tables
        1. Roll-In and Roll-Out of Data
    6. Multidimensional Clustering
      1. Guidelines for Designing MDC Tables
      2. Choosing the Optimum MDC Table Design
      3. “Natural” Uses for MDC Tables
    7. Combining Database Partitioning, Table Partitioning, and Multidimensional Clustering
    8. A Word About the Design Advisor
    9. The Balanced Configuration Unit (BCU)
      1. BCU Layers
      2. The BCU for AIX, Version 2.1
      3. The BCU for Linux, Version 2.1
    10. Practice Questions
    11. Answers
  9. 4. High Availability and Diagnostics
    1. Transactions
    2. Transaction Logging
      1. Logging Strategies
        1. Circular Logging
        2. Archival Logging
      2. Other Logging Considerations
        1. Infinite Logging
        2. Mirrored Logging
        3. Log File Size
        4. Reducing logging with NOT LOGGED INITIALLY
        5. Log File Truncation
        6. Storing Log Files on a Raw Device
    3. Database Recovery Concepts
      1. Crash Recovery
      2. Version Recovery
      3. Roll-Forward Recovery
      4. Online Versus Offline Backup and Recovery
      5. Incremental and Delta Backup and Recovery
      6. Deciding How, and How Often to Back Up
      7. Storage Considerations
    4. Performing a Crash Recovery Operation
      1. A Word About Soft Checkpoints
    5. Backup and Recovery
      1. The DB2 Backup Utility
      2. The DB2 Restore Utility
        1. Redirected Restore
      3. The DB2 Roll-Forward Utility
      4. A Word About the Recovery History File
      5. The DB2 Recover Utility
    6. Backing Up and Restoring a Partitioned Database
    7. Testing the Integrity of a Backup Image
    8. Backing Up a Database with Split Mirroring
      1. Initializing a Split Mirror with db2inidb
    9. Managing Log files With a User Exit Program
      1. Constructing a User Exit Program
      2. Configuring a DB2 Database to Use a User Exit Program
      3. User Exit Program Considerations
    10. High Availability Disaster Recovery (HADR)
      1. Requirements for HADR Environments
      2. Setting Up an HADR Environment
      3. Automatic Client Reroute and HADR
      4. Load Operations and HADR
    11. Problem Determination Tools
      1. The DB2 Memory Tracker
      2. The DB2 Problem Determination Tool
    12. Practice Questions
    13. Answers
  10. 5. Performance and Scalability
    1. Basic Performance Tuning
    2. Tuning the DB2 System Environment
      1. Registry Variables that Impact Performance
      2. A Word About The DB2_PARALLEL_IO Registry Variable
    3. Configuring Instances and Databases
      1. The Database Manager (DB2 Instance) Configuration
      2. Database Configurations
      3. A Word About the NUM_IOSERVERS Database Configuration Parameter
      4. A Word About the NUM_IOCLEANERS Database Configuration Parameter
      5. The Configuration Advisor
      6. The AUTOCONFIGURE Command
    4. Self-Tuning Memory Manager
      1. Using the Self-Tuning Memory Manager in a Partitioned Database Environment
    5. Indexes
      1. Unique Indexes
      2. Clustering Indexes
        1. Clustering Indexes and Partitioned Tables
        2. Improving Insert Performance
    6. Object Statistics and the RUNSTATS Utility
      1. Statistical Views
    7. Another Look at Parallelism
      1. Types of Parallelism Supported
      2. Connections and Connection Pooling
      3. Applications and Intra-Partition Parallelism
      4. A Word About Table Queues
    8. The DB2 Optimizer
    9. Optimizer Techniques
    10. Writing Better Queries
      1. Only Retrieve Data That Is Needed
      2. Limit the Number of Rows Returned
      3. Use the FOR UPDATE Clause
      4. Use the OPTIMIZE FOR n ROWS Clause
      5. Use the FETCH FIRST n ROWS Clause
      6. Use the FOR FETCH ONLY Clause
      7. Avoid Data Type Conversions
    11. A Word About Joins
    12. Locking
      1. How Locks Are Acquired
      2. Lock Granularity and Concurrency
    13. Analyzing DB2 Query Access Plans
      1. The Explain Facility
      2. Understanding db2exfmt Output
        1. Overview Area
        2. Database Context Area
      3. Package Context Area
        1. Original Statement Area
        2. Optimized Statement Area
        3. Access Plan Area
    14. Another Access Plan—With Details
      1. Access Plan Details—Cost
      2. Access Plan Details—Arguments
      3. Access Plan Details—Predicates
      4. Access Plan Details—Input Stream(s)
      5. Access Plan Details—FETCH
      6. Access Plan Details—Index Scan
      7. Access Plan Details—Sort
      8. Access Plan Details—Table Scan
      9. Recognizing List Prefetch
      10. Recognizing Index ORing
      11. Recognizing Index ANDing
    15. Taking Control of The Server
      1. Finding Out Who Is Using an Instance or a Database
      2. The FORCE APPLICATION Command
    16. Deep Compression
      1. Enabling a Table for Deep Compression
      2. Building a Compression Dictionary
      3. Estimating Storage Savings from Deep Compression
      4. Automatic Dictionary Creation (ADC)
      5. Deep Compression and the Load Utility
      6. The Deep Compression Administrative View and Table Function
    17. Practice Questions
    18. Answers
  11. 6. Security
    1. Controlling Database Access
    2. Authentication
      1. Where Does Authentication Take Place?
      2. Security Plug-ins
        1. A Word About LDAP Security Plug-ins
      3. Trusted Clients Versus Untrusted Clients
      4. Authentication Considerations for Remote Clients
    3. Authorities and Privileges
      1. Authorities
        1. System Administrator Authority
        2. System Control Authority
        3. System Maintenance Authority
        4. System Monitor Authority
        5. Database Administrator Authority
        6. Security Administrator Authority
        7. Load Authority
      2. Privileges
        1. Database Privileges
        2. Object Privileges
      3. A Word About Roles
      4. A Word About Trusted Contexts
    4. Granting Authorities and Privileges
      1. Requirements for Granting and Revoking Authorities and Privileges
    5. Authorities and Privileges Needed to Perform Common Tasks
    6. Securing Data Using Encryption
      1. Encrypting Data Using the ENCRYPT() Function
      2. Decrypting Data Using the DECRYPT_CHAR() and DECRYPT_BIN() Functions
      3. Obtaining Password Hints Using the GETHINT() Function
      4. Specifying an Encryption Password to Use for the Current Session
    7. Securing Data with Label-Based Access Control (LBAC)
      1. Implementing Row-Level LBAC
        1. Defining a Security Label Component
        2. Defining a Security Policy
        3. Defining Security Labels
        4. Creating an LBAC-Protected Table
        5. Granting Security Labels to Users
        6. Putting Row-Level LBAC into Action
      2. Implementing Column-Level LBAC
        1. Defining Security Label Components, Security Policies, and Security Labels
        2. Creating an LBAC-Protected Table and Granting Privileges and Security Labels to Users
        3. Creating LBAC-Protected Columns
        4. DB2LBACRULES Rules
        5. Granting Exemptions
        6. Revoking Exemptions
        7. Putting Column-Level LBAC into Action
      3. Combining Row-Level and Column-Level LBAC
    8. DB2’s Audit Facility
      1. A Word About the AUDIT_BUF_SZ DB2 Database Manager Configuration Parameter
    9. Practice Questions
    10. Answers
  12. 7. Connectivity and Networking
    1. Configuring Communications
      1. Manually Configuring Communications
    2. DB2’s Directory Files
      1. The System Database Directory
      2. The Local Database Directory
      3. The Node Directory
      4. The Database Connection Services (DCS) Directory
    3. Cataloging Remote Servers and Databases
      1. Cataloging a DB2 Database
      2. Cataloging a Remote Server (Node)
        1. A Word About IPv4 and IPv6
      3. Cataloging a DCS Database
    4. Configuring Communications to System z and System i Database Servers
      1. Binding Utilities and Applications
      2. A Word About the db2schema.bnd Bind File
      3. Considerations for System z Sysplex Exploitation
    5. DB2 Discovery
    6. Fast Communications Manager (FCM) Communications
    7. Troubleshooting Communications Errors
    8. Practice Questions
    9. Answers
  13. A. DB2 9 for Linux, UNIX, and Windows Advanced Database Administration Exam (Exam 734) Objectives
    1. Database Design (14%)
    2. Data Partitioning and Clustering (15%)
    3. High Availability and Diagnostics (20%)
    4. Performance and Scalability (33%)
    5. Security (8%)
    6. Connectivity and Networking (10%)
  14. B. Monitoring Buffer Pool Activity
    1. Buffer Pool Tuning
    2. Buffer Pool Hit Ratio
    3. Index Hit Ratio
    4. Data Hit Ratio
    5. Asynchronous Read Ratio
    6. Physical Read Rate
    7. Read Time
    8. Page Cleaner Triggers
    9. Asynchronous Pages per Write
  15. C. Using Materialized Query Tables (MQTs)
    1. Avoiding Repeated Calculations
    2. Avoiding Resource Intensive Scans
    3. Enabling Collocated Joins Using Replicated MQTs
    4. Refresh Immediate vs. Refresh Deferred
    5. Using MQTs – Let the Optimizer Decide
    6. MQTs and Subdomains
  16. D. Joining in DB2
    1. Join Techniques
    2. Nested Loop Join
    3. Merge Join
    4. Hash Join
    5. Which Join Technique to Use
    6. Joining in a Partitioned Database
    7. Collocated Table Joins
    8. Directed Outer Table Joins
    9. Directed Inner Table Joins
    10. Directed Inner and Outer Table Joins
    11. Broadcast Table Joins
    12. Partitioned Join Strategies and Performance
    13. A Word About Predicate Handling
    14. Definitions and Terminology
    15. Sargable vs. Residual Predicates
  17. E. Explain Tools
    1. Explain Tables
      1. Collecting Explain Data
      2. The Explain SQL Statement
      3. Evaluating Explain Data
        1. db2expln
        2. db2exfmt
        3. Visual Explain
  18. F. Sample Test
    1. Database Design
    2. Data Partitioning and Clustering
    3. High Availbility and Diagnostics
    4. Performance and Scalability
    5. Security
    6. Connectivity and Networking
    7. Answers
    8. Database Design
    9. Data Partitioning and Clustering
    10. High Availability and Diagnostics
    11. Performance and Scalability
    12. Security
    13. Connectivity and Networking