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

Book Description

In DB2 9 for Linux, UNIX, and Windows Database Administration Certification Study Guide, Roger E. Sanders—one of the world’s leading DB2 authors and an active participant in the development of IBM’s DB2 certification exams—covers everything a reader needs to know to pass the DB2 9 UDB DBA Certification Test (731). This comprehensive study guide steps you through all of the topics that are covered on the test, including server management, data placement, database access, analyzing DB2 activity, DB2 utilities, high availability, security, and much more. Each chapter contains an extensive set of practice questions along with carefully explained answers. Taking and passing the DB2 9 UDB DBA Certification Test (731) provides validation that you have mastered the next level of DB2 9. Passing this exam also earns you the IBM Certified Database Administrator certification. The book includes a complete practice test of 150 questions that closely models the actual exam along with an answer key with a full description of why the answer is the correct one. No other source gives you this much help in passing the exam. With the DB2 9 for Linux, UNIX, and Windows Database Administration Certification Study Guide 8, you will: • Gain the knowledge necessary to pass the DB2 9 UDB DBA Certification • Test (731) Learn how to effectively implement and administer a DB2 database with XML data • Create, maintain, and administer database objects like tables, views, and indexes • Receive an explanation of every topic included on the test…by someone involved in the creation of the actual exam • Find 150 practice questions based on the actual exam’s format and approach, along with comprehensive answers to the test questions to help you gain understanding

Table of Contents

  1. Copyright
    1. Dedication
  2. Acknowledgments
  3. About the Author
  4. About This Book
    1. Conventions Used
  5. Foreword
  6. Preface
  7. 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
    2. The Certification Process
      1. Preparing for the Certification Exams
      2. Arranging to Take a Certification Exam
      3. Taking an IBM Certification Exam
  8. 2. Server Management
    1. Working with Instances
      1. Attaching to an Instance
      2. Detaching from an Instance
      3. Starting and Stopping an Instance
      4. Quiescing an Instance
      5. A Word About the DB2 Administration Server (DAS) Instance
    2. Configuring the DB2 System Environment
    3. Configuring Instances and Databases
      1. The DB2 Database Manager Instance Configuration
      2. Database Configurations
      3. The Configuration Advisor
      4. The AUTOCONFIGURE Command
    4. Configuring Communications
      1. Manually Configuring Communications
      2. A Word About DB2’s Directory Files
      3. Cataloging and Uncataloging a Node
      4. Cataloging and Uncataloging a DB2 Database
    5. DB2 Discovery
    6. Taking Control of a Server
      1. Finding Out Who Is Using an Instance or a Database
      2. The FORCE APPLICATION Command
    7. Using the Task Center
      1. The DAS Configuration and the Task Center
    8. Automatic Maintenance
    9. Utility Throttling
      1. Obtaining Information About Running Utilities
      2. Changing a Running Utility’s Impact Priority
    10. Self-Tuning Memory Manager
    11. Problem Determination
      1. Obtaining Information About An Error Code
        1. Reason codes
      2. First Failure Data Capture (FFDC)
        1. Where FFDC information is stored
        2. Controlling how much FFDC information is collected
        3. The DB2 diagnostic log file
        4. The administration notification log
        5. Interpreting DB2 diagnostic log and administration notification log entry headers
        6. The db2diag utility
    12. Practice Questions
    13. Answers
  9. 3. Data Placement
    1. Servers, Instances, and Databases
    2. Creating a DB2 9 Database
      1. What Happens When a DB2 9 Database Is Created
      2. The Complete CREATE DATABASE Command
      3. Creating a DB2 9 Database with the Create Database Wizard
    3. Table Spaces
      1. Obtaining Information About Existing Table Spaces
      2. Obtaining Information About the Containers Used by a Table Space
      3. Creating New Table Spaces
      4. Modifying Existing Table Spaces
        1. Adding new containers to existing automatic storage table spaces
      5. A Word About Declared Temporary Tables and User Temporary Table Spaces
    4. Schemas
    5. Range Clustering and Range Partitioning
      1. Range-Clustered Tables
      2. Range-Partitioned Tables
    6. Working with XML Data
      1. The XML Data Type and XML Columns
      2. Manipulating XML Data
      3. XML Indexes
    7. Data Row Compression
      1. Enabling a Table for Data Row Compression
      2. Building a Compression Dictionary
      3. Estimating Storage Savings from Data Row Compression
    8. Practice Questions
    9. Answers
  10. 4. Database Access
    1. Tables
    2. Constraints
      1. NOT NULL Constraints
      2. Default Constraints
      3. Check Constraints
      4. Unique Constraints
      5. Referential Integrity Constraints
        1. The Insert Rule for referential constraints
        2. The Update Rule for referential constraints
        3. The Delete Rule for referential constraints
      6. Informational Constraints
        1. Temporarily Suspending Constraint Checking with the SET INTEGRITY SQL Statement
    3. Creating Tables and Defining Constraints
    4. Views
      1. A Word About Inoperative Views
    5. Indexes
      1. Clustering Indexes
      2. Multidimensional Clustering (MDC) Indexes
      3. Type-1 Versus Type-2 Indexes
    6. A Word About the System Catalog
    7. DB2 9’s Comprehensive Tool Set
      1. The Control Center
      2. The Replication Center
      3. The Satellite Administration Center
      4. The Command Editor
        1. SQL Assist
        2. Visual Explain
      5. The Task Center
      6. The Health Center
      7. The Journal
      8. The License Center
      9. The Configuration Assistant
      10. The Tools Settings Notebook
      11. The Design Advisor
      12. The Activity Monitor
      13. The Command Line Processor
    8. Practice Questions
    9. Answers
  11. 5. Analyzing DB2 Activity
    1. The Database System Monitor
      1. The Snapshot Monitor
        1. Snapshot monitor switches
        2. Viewing current snapshot monitor switch settings
        3. Changing the state of a snapshot monitor switch
        4. Capturing snapshot data
        5. Capturing snapshot monitor data using SQL
        6. Resetting snapshot monitor counters
      2. Event Monitors
        1. Activating and deactivating event monitors
        2. Forcing an event monitor to generate output prematurely
        3. Deleting an event monitor
        4. Viewing event monitor data
    2. The Health Monitor and the Health Center
    3. Monitoring SQL with the Explain Facility
      1. The Explain Tables
      2. Collecting Explain Data
        1. The EXPLAIN SQL statement
        2. The CURRENT EXPLAIN MODE and the CURRENT EXPLAIN SNAPSHOT special registers
        3. The EXPLAIN and EXPLSNAP bind options
      3. Evaluating Explain Data
        1. db2expln
        2. db2exfmt
        3. Visual Explain
        4. A word about optimization classes
    4. Other Troubleshooting Tools
      1. The DB2 Bind File Description Tool
      2. The DB2 Memory Tracker
      3. The DB2 Problem Determination Tool
    5. Practice Questions
    6. Answers
  12. 6. DB2 Utilities
    1. DB2’s Data Movement Utilities and the File Formats They Support
      1. Delimited ASCII (DEL)
      2. Non-Delimited ASCII (ASC)
      3. Worksheet Format (WSF)
      4. PC Integrated Exchange Format (IXF)
      5. Extracting Columnar Data from External Files
        1. The position method
        2. The location method
        3. The name method
      6. The DB2 Export Utility
      7. A Word About the ADMIN_CMD() Stored Procedure
      8. The DB2 Import Utility
      9. The DB2 Load Utility
        1. The four phases of a load operation
        2. Performing a load operation
      10. db2move and db2look
    2. Maintenance Utilities
      1. The REORGCHK Utility
        1. Interpreting REORGCHK output
      2. The REORG Utility
      3. The RUNSTATS Utility
        1. A word about rebinding
        2. Flushing the package cache
    3. Other Utilities
      1. The db2batch Utility
      2. The Design Advisor
    4. Practice Questions
    5. Answers
  13. 7. High Availability
    1. Transactions
    2. Transaction Logging
      1. Logging Strategies
      2. Circular logging
      3. Archival logging
      4. Other Logging Considerations
      5. Infinite logging
      6. Dual logging
      7. Controlling how “disk full” errors are handled
    3. Database Recovery Concepts
      1. Crash Recovery
      2. Version Recovery
      3. Roll-Forward Recovery
      4. Recoverable and Nonrecoverable Databases
      5. Online Versus Offline Backup and Recovery
      6. Incremental and Delta Backup and Recovery
    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
      3. Redirected restore
      4. The DB2 Roll-Forward Utility
      5. A Word About the Recovery History File
      6. The DB2 Recover Utility
      7. Rebuilding Invalid Indexes
    6. Backing Up a Database with Split Mirroring
      1. Initializing a Split Mirror with db2inidb
    7. High Availability Disaster Recovery (HADR)
      1. Requirements for HADR Environments
      2. Setting Up an HADR Environment
      3. Load Operations and HADR
    8. Practice Questions
    9. Answers
  14. 8. Security
    1. Controlling Database Access
    2. Authentication
      1. Where Does Authentication Take Place?
      2. Security Plug-ins
      3. Trusted Clients Versus Untrusted 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
    4. Granting Authorities and Privileges
      1. Granting and Revoking Authorities and Privileges from the Control Center
      2. Granting Authorities and Privileges with the GRANT SQL Statement
        1. Database-level authorities and privileges
        2. Table space privileges
        3. Schema privileges
        4. Table privileges
        5. View privileges
        6. Index privileges
        7. Sequence privileges
        8. Routine privileges
        9. Package privileges
        10. Server privileges
        11. Nickname privileges
        12. GRANT SQL statement examples
      3. Revoking Authorities and Privileges with the REVOKE SQL Statement
        1. Database-level authorities and privileges
        2. Table space privileges
        3. Schema privileges
        4. Table privileges
        5. View privileges
        6. Index privileges
        7. Sequence privileges
        8. Routine privileges
        9. Package privileges
        10. Server privileges
        11. Nickname privileges
        12. REVOKE SQL statement examples
      4. Requirements for Granting and Revoking Authorities and Privileges
    5. Authorities and Privileges Needed to Perform Common Tasks
    6. 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 a 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 a LBAC-protected table and granting privileges and security labels to users
        3. Creating LBAC-protected columns
        4. DB2LBACRULES rules
        5. Granting exemptions
        6. Putting column-level LBAC into action
      3. Combining Row-Level and Column-Level LBAC
    7. Practice Questions
    8. Answers
  15. A. DB2 9 for Linux, UNIX, and Windows Database Administration Exam (Exam 731) Objectives
    1. DB2 Server Management (20.5%)
    2. Data Placement (17.5%)
    3. Database Access (11.5%)
    4. Analyzing DB2 Activity (13%)
    5. DB2 Utilities (14.5%)
    6. High Availability (14.5%)
    7. Security (8.5%)
  16. B. Sample Test
    1. DB2 Server Management
    2. Data Placement
    3. Database Access
    4. Analyzing DB2 Activity
    5. DB2 Utilities
    6. High Availability
    7. Security
    8. Answers: DB2 Server Management
    9. Data Placement
    10. Database Access
    11. Analyzing DB2 Activity
    12. DB2 Utilities
    13. High Availability
    14. Security