You are previewing Understanding DB2®: Learning Visually with Examples.
O'Reilly logo
Understanding DB2®: Learning Visually with Examples

Book Description

The Easy, Visual Guide to IBM DB2 V8.2

IBM DB2 Universal Database™ V8.2 for Linux®, UNIX®, and Windows® is a flexible, scalable, cost-effective, and easy-to-use enterprise database. Now, one book makes DB2 even easier. Understanding DB2 teaches DB2 V8.2 visually, step by step, with dozens of examples and case studies drawn from the authors' unsurpassed experience as DB2 consultants at IBM.

Understanding DB2 doesn't just present the big picture. It thoroughly introduces every DB2 concept, procedure, and tool you'll need to get results as an administrator, user, or developer. You'll find authoritative coverage of installation, configuration, objects, database instances, storage, connectivity, security, performance, backup and recovery, SQL fundamentals, and much more. Virtually every new concept is explained with screenshots, diagrams, or tables, all designed to help you learn faster and remember more.

For those preparing for certification, this book also contains over a hundred sample questions crafted by the authors to reflect the content and format of the IBM DB2 UDB Database Administration Certification exams, with answers and detailed explanations.

Coverage includes

  • Understanding the DB2 product family, DB2 commands, and the DB2 environment

  • Configuring client and server connectivity: walkthroughs of four typical scenarios

  • Managing DB2 storage: partitions, tablespaces, buffer pools, and more

  • Leveraging the power of SQL: queries, inserts, deletes, and updates

  • Implementing security: encryption, authentication, and authorization

  • Administering and maintaining data, from LOAD and EXPORT to backup/recovery

  • Providing high availability with online split mirroring and suspended I/O

  • DB2 architecture: process and memory models

  • Managing database performance and troubleshooting



  • Table of Contents

    1. Copyright
    2. IBM Press
    3. Foreword
    4. Preface
    5. Acknowledgments
    6. About the Authors
    7. Introduction to DB2 UDB
      1. A Brief History of DB2
      2. DB2 software and the IBM E-Business On-Demand Model
      3. DB2 UDB Editions
      4. DB2 UDB Clients
      5. “Try and Buy” Versions
      6. Host Connectivity
      7. Federated Support
      8. Replication Support
      9. IBM DB2 Information Integrator
      10. Special Package Offerings for Developers
      11. DB2 Syntax Diagram Conventions
      12. Case Study
      13. Summary
      14. Review Questions
    8. DB2 at a Glance: The Big Picture
      1. SQL Statements and DB2 Commands
      2. DB2 Tools Overview
      3. The DB2 Environment
      4. Federation
      5. Case Study: The DB2 Environment
      6. Database Partitioning Feature
      7. Case Study: DB2 with DPF Environment
      8. Summary
      9. Review Questions
    9. Installing DB2
      1. DB2 Installation: The Big Picture
      2. Required User IDs and Groups
      3. Installing DB2 Using the DB2 Setup Wizard
      4. Silent Install Using a Response File
      5. Installing DB2 Manually (Linux/UNIX Only)
      6. Installing a DB2 License
      7. Installing DB2 in a DPF Environment
      8. Installing DB2 FixPaks
      9. Case Study
      10. Summary
      11. Review Questions
    10. Using the DB2 Tools
      1. DB2 Tools: The Big Picture
      2. The Command Line Tools
      3. Development Tools
      4. General Administration Tools
      5. Information Tools
      6. Monitoring Tools
      7. Setup Tools
      8. Other Tools
      9. Tool Settings
      10. Case Study
      11. Summary
      12. Review Questions
    11. Understanding the DB2 Environment, DB2 Instances, and Databases
      1. The DB2 Environment, DB2 Instances, and Databases: The Big Picture
      2. The DB2 Environment
      3. The DB2 Instance
      4. The Database Administration Server
      5. Configuring a Database
      6. Instance and Database Design Considerations
      7. Case Study
      8. Summary
      9. Review Questions
    12. Configuring Client and Server Connectivity
      1. Client and Server Connectivity: The Big Picture
      2. The DB2 Directories
      3. Supported Connectivity Scenarios
      4. Configuring Database Connections Using the Configuration Assistant
      5. Case Study
      6. Summary
      7. Review Questions
    13. Working with Database Objects
      1. DB2 Database Objects: The Big Picture
      2. Databases
      3. Partition Groups
      4. Table Spaces
      5. Buffer Pools
      6. Schemas
      7. Data Types
      8. Tables
      9. Indexes
      10. Multidimensional Clustering Tables and Block Indexes
      11. Views
      12. Packages
      13. Triggers
      14. Stored Procedures
      15. User-Defined Functions
      16. Sequences
      17. Case Study
      18. Summary
      19. Review Questions
    14. The DB2 Storage Model
      1. The DB2 Storage Model: The Big Picture
      2. Databases: Logical and Physical Storage of Your Data
      3. Database Partition Groups
      4. Table Spaces
      5. Buffer Pools
      6. Case Study
      7. Summary
      8. Review Questions
    15. Leveraging the Power of SQL
      1. Querying DB2 Data
      2. Modifying DB2 Data
      3. Selecting from UPDATE, DELETE, and INSERT
      4. The MERGE Statement
      5. Recursive SQL
      6. The UNION, INTERSECT, and EXCEPT Operators
      7. Case Study
      8. Summary
      9. Review Questions
    16. Implementing Security
      1. DB2 Security Model: The Big Picture
      2. Authentication
      3. Data Encryption
      4. Administrative Authorities
      5. Database Object Privileges
      6. Authority and Privilege Metadata
      7. Windows Domain Considerations
      8. Case Study
      9. Summary
      10. Review Questions
    17. Understanding Concurrency and Locking
      1. DB2 Locking and Concurrency: The Big Picture
      2. Concurrency and Locking Scenarios
      3. DB2 Isolation Levels
      4. Changing Isolation Levels
      5. DB2 Locking
      6. Diagnosing Lock Problems
      7. Techniques to Avoid Locking
      8. Case Study
      9. Summary
      10. Review Questions
    18. Maintaining Data
      1. DB2 Data Movement Utilities: The Big Picture
      2. Data Movement File Formats
      3. The DB2 EXPORT Utility
      4. The DB2 IMPORT Utility
      5. The DB2 LOAD Utility
      6. The db2move Utility
      7. The db2relocatedb Utility
      8. Generating Data Definition Language
      9. DB2 Maintenance Utilities
      10. Case Study
      11. Summary
      12. Review Questions
    19. Developing Database Backup and Recovery Solutions
      1. Database Recovery Concepts
      2. DB2 Transaction Logs
      3. Recovery Terminology
      4. Performing Database and Table Space Backups
      5. Database and Table Space Recovery Using the RESTORE DATABASE Command
      6. Database and Table Space Roll Forward
      7. Recovering a Dropped Table
      8. The Recovery History File
      9. Database Recovery Using the RECOVER DATABASE Command
      10. High Availability Through Online Split Mirroring and Suspended I/O Support
      11. High Availability Disaster Recovery
      12. Using DB2 Tools to Inspect the Health of Your Database
      13. Case Study
      14. Summary
      15. Review Questions
    20. The DB2 Process Model
      1. The DB2 Process Model: The Big Picture
      2. The DB2 Engine Dispatchable Units
      3. Tuning the Number of EDUs
      4. Monitoring and Tuning the DB2 Agents
      5. The Connection Concentrator
      6. Commonly Seen DB2 Executables
      7. Additional Services/Processes on Windows
      8. Case Study
      9. Summary
      10. Review Questions
    21. The DB2 Memory Model
      1. DB2 Memory Allocation: The Big Picture
      2. Instance-Level Shared Memory
      3. Database-Level Shared Memory
      4. Application-Level Shared Memory
      5. Agent-Level Private Memory
      6. The Memory Model
      7. 32-Bit Memory Model Considerations
      8. 64-Bit Memory Model Considerations
      9. AWE Support with Windows
      10. Case Study
      11. Summary
      12. Review Questions
    22. Database Performance Considerations
      1. Performance Fundamentals
      2. System/Server Configuration
      3. The DB2 Configuration Advisor
      4. Configuring the DB2 Instance
      5. Configuring Your Databases
      6. Lack of Proper Maintenance
      7. The Snapshot Monitor
      8. Event Monitors
      9. The DB2 Optimizer
      10. The Explain Tool and Explain Tables
      11. Using Visual Explain to Examine Access Plans
      12. Case Study
      13. Summary
      14. Review Questions
    23. Diagnosing Problems
      1. Problem Diagnosis: The Big Picture
      2. How Does DB2 Report Problems?
      3. DB2 Error Message Description
      4. DB2 First-Failure Data Capture
      5. Receiving E-mail Notifications
      6. The db2support Tool
      7. The DB2 Trace Facility
      8. Searching for Known Problems
      9. Case Study
      10. Summary
      11. Review Questions
    24. Solutions to the Review Questions
      1. Chapter 1
      2. Chapter 2
      3. Chapter 3
      4. Chapter 4
      5. Chapter 5
      6. Chapter 6
      7. Chapter 7
      8. Chapter 8
      9. Chapter 9
      10. Chapter 10
      11. Chapter 11
      12. Chapter 12
      13. Chapter 13
      14. Chapter 14
      15. Chapter 15
      16. Chapter 16
      17. Chapter 17
    25. Use of Uppercase Versus Lowercase in DB2
    26. IBM Servers
    27. Using the DB2 System Catalog Tables
      1. DB2 System Catalog Tables
      2. How to Extract Information from the System Catalog Tables
      3. How to Use the SYSSTAT Tables to Perform What-if Modeling and Analysis
    28. Setting Up Database Connectivity for DB2 UDB for z/OS and DB2 UDB for iSeries
      1. Part I: Setting Up Database Connectivity for DB2 UDB for z/OS
      2. Part II: Setting Up Database Connectivity for DB2 UDB for iSeries
    29. Diagnosing DB2 Connectivity Problems
      1. Diagnosing Client-Server TCP/IP Connection Problems
    30. Resources
    31. Glossary
      1. A
      2. B
      3. C
      4. D
      5. E
      6. F
      7. G
      8. H
      9. I
      10. J
      11. K
      12. L
      13. M
      14. N
      15. O
      16. P
      17. Q
      18. R
      19. S
      20. T
      21. U
      22. V
      23. W
      24. X
      25. Z
    32. About the CD-ROM
    33. CD-ROM Warranty
    34. Index