Understanding DB2®: Learning Visually with Examples, Second Edition

Understanding DB2®: Learning Visually with Examples, Second Edition

Released December 2007
Publisher(s): IBM Press
ISBN: 9780768681772

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The Easy, Visual Way to Master IBM® DB2 for Linux®, UNIX®, and Windows®—Fully Updated for Version 9.5

IBM DB2 9 and DB2 9.5 provide breakthrough capabilities for providing Information on Demand, implementing Web services and Service Oriented Architecture, and streamlining information management. Understanding DB2: Learning Visually with Examples, Second Edition, is the easiest way to master the latest versions of DB2 and apply their full power to your business challenges.

Written by four IBM DB2 experts, this book introduces key concepts with dozens of examples drawn from the authors' experience working with DB2 in enterprise environments. Thoroughly updated for DB2 9.5, it covers new innovations ranging from manageability to performance and XML support to API integration. Each concept is presented with easy-to-understand screenshots, diagrams, charts, and tables.

This book is for everyone who works with DB2: database administrators, system administrators, developers, and consultants. With hundreds of well-designed review questions and answers, it will also help professionals prepare for the IBM DB2 Certification Exams 730, 731, or 736.

Coverage includes

  • Choosing the right version of DB2 for your needs

  • Installing and configuring DB2

  • Understanding the DB2 environment, instances, and databases

  • Establishing client and server connectivity

  • Working with database objects

  • Utilizing breakthrough pureXML™ technology, which provides for nativeXML support

  • Mastering administration, maintenance, performance optimization, troubleshooting, and recovery

  • Understanding improvements in the DB2 process, memory, and storage models

  • Implementing effective database security

  • Leveraging the power of SQL and XQuery

    •

    Table of contents

    1. Copyright
      1. Dedication
    2. IBM Press
      1. Information Management
      2. Rational and Software Development
      3. Computing
      4. Web Sphere
      5. Lotus
      6. Open Source
      7. Business Strategy & Management
    3. Foreword
    4. Preface
      1. Who Should Read This Book?
      2. Getting Started
        1. A Word of Advice
      3. Conventions
      4. Contacting the Authors
      5. What’s New
        1. DB2 9
        2. DB2 9.5
    5. Acknowledgments
    6. About the Authors
      1. A.0.1 Author Publications
    7. 1. Introduction to DB2
      1. 1.1. Brief History of DB2
      2. 1.2. The Role of DB2 in the Information On Demand World
        1. 1.2.1. On-Demand Business
        2. 1.2.2. Information On Demand
        3. 1.2.3. Service-Oriented Architecture
        4. 1.2.4. Web Services
        5. 1.2.5. XML
        6. 1.2.6. DB2 and the IBM Strategy
      3. 1.3. DB2 Editions
        1. 1.3.1. DB2 Everyplace Edition
        2. 1.3.2. DB2 Personal Edition
        3. 1.3.3. DB2 Express-C
        4. 1.3.4. DB2 Express Edition
        5. 1.3.5. DB2 Workgroup Server Edition
        6. 1.3.6. DB2 Enterprise Server Edition
      4. 1.4. DB2 Clients
      5. 1.5. Try-and-Buy Versions
      6. 1.6. Host Connectivity
      7. 1.7. Federation Support
      8. 1.8. Replication Support
      9. 1.9. IBM WebSphere Federation Server and WebSphere Replication Server
      10. 1.10. Special Package Offerings for Developers
      11. 1.11. DB2 Syntax Diagram Conventions
      12. 1.12. Case Study
      13. 1.13. Summary
      14. 1.14. Review Questions
    8. 2. DB2 at a Glance: The Big Picture
      1. 2.1. SQL Statements, XQuery Statements, and DB2 Commands
        1. 2.1.1. SQL Statements
        2. 2.1.2. XQuery Statements
        3. 2.1.3. DB2 System Commands
        4. 2.1.4. DB2 Command Line Processor (CLP) Commands
      2. 2.2. DB2 Tools Overview
        1. 2.2.1. Command-Line Tools
        2. 2.2.2. General Administration Tools
        3. 2.2.3. Information Tools
        4. 2.2.4. Monitoring Tools
        5. 2.2.5. Setup Tools
        6. 2.2.6. Other Tools
      3. 2.3. The DB2 Environment
        1. 2.3.1. An Instance(1)
        2. 2.3.2. The Database Administration Server
        3. 2.3.3. Configuration Files and the DB2 Profile Registries(2)
          1. 2.3.3.1. Environment Variables
          2. 2.3.3.2. The DB2 Profile Registry
          3. 2.3.3.3. Configuration Parameters
        4. 2.3.4. Connectivity and DB2 Directories(3)
          1. 2.3.4.1. System Database Directory
          2. 2.3.4.2. Local Database Directory
          3. 2.3.4.3. Node Directory
          4. 2.3.4.4. Database Connection Services Directory
        5. 2.3.5. Databases(4)
        6. 2.3.6. Table Spaces(5)
        7. 2.3.7. Tables, Indexes, and Large Objects(6)
        8. 2.3.8. Logs(7)
        9. 2.3.9. Buffer Pools(8)
        10. 2.3.10. The Internal Implementation of the DB2 Environment
      4. 2.4. Federation
      5. 2.5. Case Study: The DB2 Environment
      6. 2.6. Database Partitioning Feature
        1. 2.6.1. Database Partitions
        2. 2.6.2. The Node Configuration File
        3. 2.6.3. An Instance in the DPF Environment
        4. 2.6.4. Partitioning a Database
        5. 2.6.5. Configuration Files in a DPF Environment
        6. 2.6.6. Logs in a DPF Environment
        7. 2.6.7. The Catalog Partition
        8. 2.6.8. Partition Groups
        9. 2.6.9. Buffer Pools in a DPF Environment
        10. 2.6.10. Table Spaces in a Partitioned Database Environment
        11. 2.6.11. The Coordinator Partition
        12. 2.6.12. Issuing Commands and SQL Statements in a DPF Environment
          1. 2.6.12.1. The db2_all command
          2. 2.6.12.2. Using Database Partition Expressions
        13. 2.6.13. The DB2NODE Environment Variable
        14. 2.6.14. Distribution Maps and Distribution Keys
      7. 2.7. Case Study: DB2 with DPF Environment
      8. 2.8. IBM Balanced Warehouse
      9. 2.9. Summary
      10. 2.10. Review Questions
    9. 3. Installing DB2
      1. 3.1. DB2 Installation: The Big Picture
      2. 3.2. Installing DB2 Using the DB2 Setup Wizard
        1. 3.2.1. Step 1 for Windows: Launch the DB2 Setup Wizard
        2. 3.2.2. Step 1 for Linux and UNIX: Launch the DB2 Setup Wizard
        3. 3.2.3. Step 2: Choose an Installation Type
        4. 3.2.4. Step 3: Choose Whether to Generate a Response File
        5. 3.2.5. Step 4: Specify the Installation Folder
        6. 3.2.6. Step 5: Set User Information for the DB2 Administration Server
        7. 3.2.7. Step 6: Create and Configure the DB2 Instance
        8. 3.2.8. Step 7: Create the DB2 Tools Catalog
        9. 3.2.9. Step 8: Enable the Alert Notification Feature
        10. 3.2.10. Step 9: Specify a Contact for Health Monitor Notification
        11. 3.2.11. Step 10: Enable Operating System Security for DB2 Objects (Windows Only)
        12. 3.2.12. Step 11: Start the Installation
      3. 3.3. Non-Root Installation on Linux and Unix
        1. 3.3.1. Differences between Root and Non-Root Installations
        2. 3.3.2. Limitations of Non-Root Installations
        3. 3.3.3. Installing DB2 as a Non-Root User
        4. 3.3.4. Enabling Root-Based Features in Non-Root Installations
      4. 3.4. Required User IDs and Groups
        1. 3.4.1. User IDs and Groups Required for Windows
        2. 3.4.2. IDs and Groups Required for Linux and UNIX
        3. 3.4.3. Creating User IDs and Groups if NIS Is Installed in Your Environment (Linux and UNIX Only)
      5. 3.5. Silent Install Using a Response File
        1. 3.5.1. Creating a Response File
          1. 3.5.1.1. Creating a Response File Using the DB2 Setup Wizard
          2. 3.5.1.2. Creating a Custom Response File Using a Sample Response File
          3. 3.5.1.3. Creating a Response File Using the Response File Generator (Windows Only)
        2. 3.5.2. Installing DB2 Using a Response File on Windows
        3. 3.5.3. Installing DB2 Using a Response File on Linux and UNIX
      6. 3.6. Advanced DB2 Installation Methods (Linux and UNIX Only)
        1. 3.6.1. Installing DB2 Using the db2_install Script
        2. 3.6.2. Manually Installing Payload Files (Linux and UNIX)
      7. 3.7. Installing a DB2 License
        1. 3.7.1. Installing a DB2 Product License Using the License Center
        2. 3.7.2. Installing the DB2 Product License Using the db2licm Command
      8. 3.8. Installing DB2 in a DPF Environment
      9. 3.9. Installing Multiple db2 Versions and Fix Packs on the Same Server
        1. 3.9.1. Coexistence of Multiple DB2 Versions and Fix Packs (Windows)
        2. 3.9.2. Using Multiple DB2 Copies on the Same Computer (Windows)
        3. 3.9.3. Coexistence of Multiple DB2 Versions and Fix Packs (Linux and UNIX)
          1. 3.9.3.1. The db2ls Command (Linux and UNIX)
          2. 3.9.3.2. DB2 Administrative Server (DAS) and Multiple DB2 Copies
      10. 3.10. Installing DB2 Fix Packs
        1. 3.10.1. Applying a Regular DB2 Fix Pack (All Supported Platforms and Products)
        2. 3.10.2. Applying Fix Packs to a Non-Root Installation
      11. 3.11. Migrating DB2
        1. 3.11.1. Migrating DB2 Servers
        2. 3.11.2. Migration Steps for a DB2 Server on Windows
        3. 3.11.3. Migration Steps for a DB2 Server on Linux and UNIX
      12. 3.12. Case Study
      13. 3.13. Summary
      14. 3.14. Review Questions
    10. 4. Using the DB2 Tools
      1. 4.1. DB2 Tools: The Big Picture
      2. 4.2. The Command-Line Tools
        1. 4.2.1. The Command Line Processor and the Command Window
          1. 4.2.1.1. Methods to Work with the CLP
            1. Method 1: Interactive Mode
            2. Method 2: Non-Interactive Mode
            3. Method 3: Non-interactive Mode Using a File as Input
          2. 4.2.1.2. CLP Command Options
          3. 4.2.1.3. Obtaining Help Information from the CLP
          4. 4.2.1.4. Line Continuation
            1. Method 1: Using the Backslash (\) Character
            2. Method 2: Using a Delimiter Terminator Character with the CLP in Interactive Mode
          5. 4.2.1.5. The CLP Front-End and Back-End Processes
        2. 4.2.2. The Command Editor
          1. 4.2.2.1. Visual Explain
      3. 4.3. Web-Based Tools
        1. 4.3.1. The Data Server Administration Console
      4. 4.4. General Administration Tools
        1. 4.4.1. The Control Center
        2. 4.4.2. The Journal
        3. 4.4.3. License Center
        4. 4.4.4. The Replication Center
        5. 4.4.5. The Task Center
      5. 4.5. Information Tools
        1. 4.5.1. Information Center
        2. 4.5.2. Checking for DB2 Updates
      6. 4.6. Monitoring Tools
        1. 4.6.1. The Activity Monitor
        2. 4.6.2. Event Analyzer
        3. 4.6.3. Health Center
        4. 4.6.4. Indoubt Transaction Manager
        5. 4.6.5. The Memory Visualizer
      7. 4.7. Setup Tools
        1. 4.7.1. The Configuration Assistant
        2. 4.7.2. Configure DB2 .NET Data Provider
        3. 4.7.3. First Steps
        4. 4.7.4. Register Visual Studio Add-Ins
        5. 4.7.5. Default DB2 and Database Client Interface Selection Wizard
      8. 4.8. Other Tools
        1. 4.8.1. The IBM Data Studio
        2. 4.8.2. SQL Assist
        3. 4.8.3. Satellite Administration Center
        4. 4.8.4. The db2pd Tool
      9. 4.9. Tool Settings
      10. 4.10. Case Study
      11. 4.11. Summary
      12. 4.12. Review Questions
    11. 5. Understanding the DB2 Environment, DB2 Instances, and Databases
      1. 5.1. The DB2 Environment, DB2 Instances, and Databases: The Big Picture
      2. 5.2. The DB2 Environment
        1. 5.2.1. Environment Variables
        2. 5.2.2. DB2 Profile Registries
      3. 5.3. The DB2 Instance
        1. 5.3.1. Creating DB2 Instances
        2. 5.3.2. Creating Client Instances
        3. 5.3.3. Creating DB2 Instances in a Multipartitioned Environment
        4. 5.3.4. Dropping an Instance
        5. 5.3.5. Listing the Instances in Your System
        6. 5.3.6. The DB2INSTANCE Environment Variable
        7. 5.3.7. Starting a DB2 Instance
        8. 5.3.8. Stopping a DB2 Instance
        9. 5.3.9. Attaching to an Instance
        10. 5.3.10. Configuring an Instance
        11. 5.3.11. Working with an Instance from the Control Center
        12. 5.3.12. The DB2 Commands at the Instance Level
      4. 5.4. The Database Administration Server
        1. 5.4.1. The DAS Commands
      5. 5.5. Configuring a Database
        1. 5.5.1. Configuring a Database from the Control Center
        2. 5.5.2. The DB2 Commands at the Database Level
      6. 5.6. Instance and Database Design Considerations
      7. 5.7. Case Study
      8. 5.8. Summary
      9. 5.9. Review Questions
    12. 6. Configuring Client and Server Connectivity
      1. 6.1. Client and Server Connectivity: The Big Picture
      2. 6.2. The DB2 Database Directories
        1. 6.2.1. The DB2 Database Directories: An Analogy Using a Book
        2. 6.2.2. The System Database Directory
        3. 6.2.3. The Local Database Directory
        4. 6.2.4. The Node Directory
        5. 6.2.5. The Database Connection Services Directory
        6. 6.2.6. The Relationship between the DB2 Directories
          1. 6.2.6.1. A Local Connection
          2. 6.2.6.2. A Remote Connection to a DB2 Database on another Server
          3. 6.2.6.3. A Remote Connection to a Host DB2 Server
      3. 6.3. Supported Connectivity Scenarios
        1. 6.3.1. Scenario 1: Local Connection from a DB2 Client to a DB2 Server
        2. 6.3.2. Scenario 2: Remote Connection from a DB2 Client to a DB2 Server
          1. 6.3.2.1. Enabling the Database Server to Accept Client Connections
            1. 6.3.2.1.1. TCP/IP connection
          2. 6.3.2.2. Cataloging the Node Directory and Database Directory on the Client
            1. 6.3.2.2.1. TCP/IP Connection
        3. 6.3.3. Scenario 3: Remote Connection from a DB2 Client to a DB2 Host Server
          1. 6.3.3.1. Enabling the Database Server to Accept Client Connections
          2. 6.3.3.2. Cataloging the Node Directory, Database Directory, and DCS Directory on the Client
            1. 6.3.3.2.1. TCP/IP Connection
        4. 6.3.4. Scenario 4: Remote Connection from a DB2 Client to a DB2 Host Server via a DB2 Connect Gateway
        5. 6.3.5. Binding Utilities
      4. 6.4. Configuring Database Connections Using the Configuration Assistant
        1. 6.4.1. Configuring a Connection Using DB2 Discovery in the Configuration Assistant
        2. 6.4.2. Configuring a Connection Using Access Profiles in the Configuration Assistant
          1. 6.4.2.1. Using the Configuration Assistant to Export an Access Profile
          2. 6.4.2.2. Using the Configuration Assistant to Import an Access Profile
            1. 6.4.2.2.1. Importing an Access Profile Using the CA’s Import Profile Function
            2. 6.4.2.2.2. Importing an Access Profile Using the CA’s Add Database Wizard
        3. 6.4.3. Configuring a Connection Manually Using the Configuration Assistant
        4. 6.4.4. Automatic Client Reroute Feature
        5. 6.4.5. Application Connection Timeout Support
      5. 6.5. Diagnosing DB2 Connectivity Problems
        1. 6.5.1. Diagnosing Client-Server TCP/IP Connection Problems
          1. 6.5.1.1. Verifying the Server Configuration
          2. 6.5.1.2. Verifying the Client Configuration
      6. 6.6. Case Study
      7. 6.7. Summary
      8. 6.8. Review Questions
    13. 7. Working with Database Objects
      1. 7.1. DB2 Database Objects: The Big Picture
      2. 7.2. Databases
        1. 7.2.1. Database Partitions
        2. 7.2.2. The Database Node Configuration File (db2nodes.cfg)
      3. 7.3. Partition Groups
      4. 7.4. Table Spaces
        1. 7.4.1. Table Space Classification
        2. 7.4.2. Default Table Spaces
      5. 7.5. Buffer Pools
      6. 7.6. Schemas
      7. 7.7. Data Types
        1. 7.7.1. DB2 Built-in Data Types
          1. 7.7.1.1. Numeric Data Types
          2. 7.7.1.2. String Data Types
          3. 7.7.1.3. Datetime Data Types
          4. 7.7.1.4. Extensible Markup Language Data Type
        2. 7.7.2. User-Defined Types
        3. 7.7.3. Choosing the Proper Data Type
      8. 7.8. Tables
        1. 7.8.1. Table Classification
        2. 7.8.2. System Catalog Tables
        3. 7.8.3. User Tables
        4. 7.8.4. Default Values
        5. 7.8.5. Using NULL Values
        6. 7.8.6. Identity Columns
        7. 7.8.7. Constraints
          1. 7.8.7.1. Unique Constraints
          2. 7.8.7.2. Referential Constraints
            1. 7.8.7.2.1. Using Primary, Unique, and Foreign Keys to Establish Referential Integrity
            2. 7.8.7.2.2. Referential Integrity Implications on SQL Operations
          3. 7.8.7.3. Check Constraints
          4. 7.8.7.4. Informational Constraints
        8. 7.8.8. Not Logged Initially Tables
        9. 7.8.9. Partitioned Tables
          1. 7.8.9.1. Detaching Partitions
        10. 7.8.10. Row Compression
          1. 7.8.10.1. Enabling Row Compression
          2. 7.8.10.2. Creating the Compression Dictionary
          3. 7.8.10.3. Verifying Compression Characteristics
          4. 7.8.10.4. Automatic Dictionary Creation
        11. 7.8.11. Table Compression
        12. 7.8.12. Materialized Query Tables and Summary Tables
        13. 7.8.13. Temporary Tables
      9. 7.9. Indexes
        1. 7.9.1. Working with Indexes
        2. 7.9.2. Clustering Indexes
      10. 7.10. Multidimensional Clustering Tables and Block Indexes
        1. 7.10.1. MDC Tables
        2. 7.10.2. Block Indexes
        3. 7.10.3. The Block Map
        4. 7.10.4. Choosing Dimensions for MDC Tables
      11. 7.11. Combining DPF, Table Partitioning, and MDC
      12. 7.12. Views
        1. 7.12.1. View Classification
          1. 7.12.1.1. Deleteable Views
          2. 7.12.1.2. Updatable Views
          3. 7.12.1.3. Insertable Views
          4. 7.12.1.4. Read-Only Views
        2. 7.12.2. Using the WITH CHECK OPTION
        3. 7.12.3. Nested Views
      13. 7.13. Packages
      14. 7.14. Triggers
      15. 7.15. Stored Procedures
      16. 7.16. User-Defined Functions
      17. 7.17. Sequences
      18. 7.18. Case Study
        1. 1)
        2. 2)
      19. 7.19. Summary
      20. 7.20. Review Questions
    14. 8. The DB2 Storage Model
      1. 8.1. The DB2 Storage Model: The Big Picture
      2. 8.2. Databases: Logical and Physical Storage of Your Data
        1. 8.2.1. Creating a Database
        2. 8.2.2. Database Creation Examples
          1. 8.2.2.1. Creating a Database in a Single-Partition Environment
          2. 8.2.2.2. Creating a Database in a Multipartition Environment
        3. 8.2.3. Listing Databases
        4. 8.2.4. Dropping Databases
        5. 8.2.5. The Sample Database
      3. 8.3. Database Partition Groups
        1. 8.3.1. Database Partition Group Classifications
        2. 8.3.2. Default Partition Groups
        3. 8.3.3. Creating Database Partition Groups
        4. 8.3.4. Modifying a Database Partition Group
        5. 8.3.5. Listing Database Partition Groups
        6. 8.3.6. Dropping a Database Partition Group
      4. 8.4. Table Spaces
        1. 8.4.1. Containers
        2. 8.4.2. Storage Paths
        3. 8.4.3. Pages
        4. 8.4.4. Extents
        5. 8.4.5. Creating Table Spaces
        6. 8.4.6. Automatic Storage
          1. 8.4.6.1. Creating Automatic Storage Table Spaces
        7. 8.4.7. SMS Table Spaces
          1. 8.4.7.1. Creating SMS Table Spaces
        8. 8.4.8. DMS Table Spaces
          1. 8.4.8.1. Creating DMS Table Spaces
            1. 8.4.8.1.1. Using Device Containers
            2. 8.4.8.1.2. Using File Containers
        9. 8.4.9. Table Space Considerations in a Multipartition Environment
        10. 8.4.10. Listing Table Spaces
        11. 8.4.11. Altering a Table Space
          1. 8.4.11.1. Enlarging a DMS Table Space
          2. 8.4.11.2. Shrinking a DMS Table Space
            1. 8.4.11.2.1. The Table Space High-Water Mark
          3. 8.4.11.3. Dropping a Table Space
      5. 8.5. Buffer Pools
        1. 8.5.1. Creating Buffer Pools
        2. 8.5.2. Altering Buffer Pools
        3. 8.5.3. Dropping Buffer Pools
      6. 8.6. Case Study
      7. 8.7. Summary
      8. 8.8. Review Questions
    15. 9. Leveraging the Power of SQL
      1. 9.1. Querying DB2 Data
        1. 9.1.1. Derived Columns
        2. 9.1.2. The SELECT Statement with COUNT Aggregate Function
        3. 9.1.3. The SELECT Statement with DISTINCT Clause
        4. 9.1.4. DB2 Special Registers
        5. 9.1.5. Scalar and Column Functions
        6. 9.1.6. The CAST Expression
        7. 9.1.7. The WHERE clause
        8. 9.1.8. Using FETCH FIRST n ROWS ONLY
        9. 9.1.9. The LIKE Predicate
        10. 9.1.10. The BETWEEN Predicate
        11. 9.1.11. The IN Predicate
        12. 9.1.12. The ORDER BY Clause
        13. 9.1.13. The GROUP BY...HAVING Clause
        14. 9.1.14. Joins
        15. 9.1.15. Working with NULLs
        16. 9.1.16. The CASE Expression
        17. 9.1.17. Adding a Row Number to the Result Set
      2. 9.2. Modifying Table Data
      3. 9.3. Selecting from UPDATE, DELETE, or INSERT
      4. 9.4. The MERGE Statement
      5. 9.5. Recursive SQL
      6. 9.6. The UNION, INTERSECT, and EXCEPT Operators
        1. 9.6.1. The UNION and UNION ALL Operators
        2. 9.6.2. The INTERSECT and INTERSECT ALL Operators
        3. 9.6.3. The EXCEPT and EXCEPT ALL Operators
      7. 9.7. Case Study
      8. 9.8. Summary
      9. 9.9. Review Questions
    16. 10. Mastering the DB2 pureXML Support
      1. 10.1. XML: The Big Picture
        1. 10.1.1. What Is XML?
        2. 10.1.2. Well-Formed Versus Valid XML Documents
        3. 10.1.3. Namespaces in XML
        4. 10.1.4. XML Schema
        5. 10.1.5. Working with XML Documents
          1. 10.1.5.1. XML Parsers: DOM Versus SAX
          2. 10.1.5.2. XQuery and XPath
            1. 10.1.5.2.1. XPath Expressions
          3. 10.1.5.3. SQL/XML
        6. 10.1.6. XML versus the Relational Model
        7. 10.1.7. XML and Databases
          1. 10.1.7.1. Methods to Store and Work with XML Documents in Databases
            1. 10.1.7.1.1. Using a CLOB or VARCHAR Column
            2. 10.1.7.1.2. Using the Shredding Method to Store XML Documents
            3. 10.1.7.1.3. Using pureXML
      2. 10.2. pureXML in DB2
        1. 10.2.1. pureXML Storage in DB2
        2. 10.2.2. Base-Table Inlining and Compression of XML Data
        3. 10.2.3. The pureXML Engine
      3. 10.3. Querying XML Data
        1. 10.3.1. Querying XML Data with XQuery
          1. 10.3.1.1. The db2-fn:xmlcolumn Function
          2. 10.3.1.2. The db2-fn:sqlquery Function
          3. 10.3.1.3. XQuery with FLWOR Expressions
          4. 10.3.1.4. Join Operations with XQuery
        2. 10.3.2. Querying XML Data with SQL/XML
          1. 10.3.2.1. XMLQUERY
          2. 10.3.2.2. XMLEXISTS
          3. 10.3.2.3. XMLTABLE
          4. 10.3.2.4. XML Serialization
      4. 10.4. SQL/XML Publishing Functions
      5. 10.5. Transforming XML Documents Using XSLT Functions
      6. 10.6. Inserting XML Data into a DB2 Database
        1. 10.6.1. XML Parsing
      7. 10.7. Updating and Deleting XML Data
      8. 10.8. XML Indexes
        1. 10.8.1. System Indexes
        2. 10.8.2. User-Created Indexes
        3. 10.8.3. Conditions for XML Index Eligibility
      9. 10.9. XML Schema Support and Validation in DB2
        1. 10.9.1. XML Schema Repository
          1. 10.9.1.1. Registering XSR Objects
          2. 10.9.1.2. Obtaining Information about Registered XML Schemas
        2. 10.9.2. XML Validation
          1. 10.9.2.1. Performing Validation
          2. 10.9.2.2. The IS VALIDATED Predicate
          3. 10.9.2.3. Determining the Schema of a Previously Validated Document
        3. 10.9.3. Compatible XML Schema Evolution
      10. 10.10. Annotated XML Schema Decomposition
      11. 10.11. XML Performance Considerations
        1. 10.11.1. Choosing Correctly Where and How to Store Your XML Documents
        2. 10.11.2. Creating Appropriate Indexes and Using SQL/XML and XQuery Statements Efficiently
        3. 10.11.3. Ensure That Statistics on XML Columns Are Collected Accurately
      12. 10.12. pureXML Restrictions
      13. 10.13. Case Study
      14. 10.14. Summary
      15. 10.15. Review Questions
    17. 11. Implementing Security
      1. 11.1. DB2 Security Model: The Big Picture
      2. 11.2. Authentication
        1. 11.2.1. Configuring the Authentication Type at a DB2 Server
        2. 11.2.2. Configuring the Authentication Type at a DB2 Client
        3. 11.2.3. Authenticating Users at/on the DB2 Server
        4. 11.2.4. Authenticating Users Using the Kerberos Security Service
        5. 11.2.5. Authenticating Users with Generic Security Service Plug-ins
        6. 11.2.6. Authenticating Users at/on the DB2 Client(s)
      3. 11.3. Data Encryption
      4. 11.4. Administrative Authorities
        1. 11.4.1. Managing Administrative Authorities
      5. 11.5. Database Object Privileges
        1. 11.5.1. Schema Privileges
        2. 11.5.2. Table Space Privileges
        3. 11.5.3. Table and View Privileges
        4. 11.5.4. Index Privileges
        5. 11.5.5. Package Privileges
        6. 11.5.6. Routine Privileges
        7. 11.5.7. Sequence Privileges
        8. 11.5.8. XSR Object Privileges
        9. 11.5.9. Security Label Privileges
        10. 11.5.10. LBAC Rule Exemption Privileges
        11. 11.5.11. SET SESSION AUTHORIZATION Statement and SETSESSIONUSER Privilege
        12. 11.5.12. Implicit Privileges
        13. 11.5.13. Roles and Privileges
        14. 11.5.14. TRANSFER OWNERSHIP Statement
      6. 11.6. Label-Based Access Control (LBAC)
        1. 11.6.1. Views and LBAC
        2. 11.6.2. Implementing an LBAC Security Solution
        3. 11.6.3. LBAC In Action
        4. 11.6.4. Column Level Security
      7. 11.7. Authority and Privilege Metadata
      8. 11.8. Windows Domain Considerations
        1. 11.8.1. Windows Global Groups and Local Groups
        2. 11.8.2. Access Tokens
      9. 11.9. Trusted Contexts Security Enhancement
      10. 11.10. Case Study
      11. 11.11. Summary
      12. 11.12. Review Questions
    18. 12. Understanding Concurrency and Locking
      1. 12.1. DB2 Locking and Concurrency: The Big Picture
      2. 12.2. Concurrency and Locking Scenarios
        1. 12.2.1. Lost Updates
        2. 12.2.2. Uncommitted Reads
        3. 12.2.3. Nonrepeatable Reads
        4. 12.2.4. Phantom Reads
      3. 12.3. DB2 Isolation Levels
        1. 12.3.1. Uncommitted Reads
        2. 12.3.2. Cursor Stability
        3. 12.3.3. Read Stability
        4. 12.3.4. Repeatable Reads
      4. 12.4. Changing Isolation Levels
        1. 12.4.1. Using the DB2 Command Window
        2. 12.4.2. Using the DB2 PRECOMPILE and BIND Commands
        3. 12.4.3. Using the DB2 Call Level Interface
        4. 12.4.4. Using the Application Programming Interface
        5. 12.4.5. Working with Statement Level Isolation Level
      5. 12.5. DB2 Locking
        1. 12.5.1. Lock Attributes
          1. 12.5.1.1. Table-Level Lock Modes
          2. 12.5.1.2. Row Lock Modes
        2. 12.5.2. Lock Waits
        3. 12.5.3. Deadlocks
        4. 12.5.4. Lock Deferral
        5. 12.5.5. Lock Escalation
      6. 12.6. Diagnosing Lock Problems
        1. 12.6.1. Using the list applications Command
        2. 12.6.2. Using the force application Command
        3. 12.6.3. Using the Snapshot Monitor
        4. 12.6.4. Using Snapshot Table Functions
        5. 12.6.5. Using the Event Monitor
        6. 12.6.6. Using the Activity Monitor
        7. 12.6.7. Using the Health Center
      7. 12.7. Techniques to Avoid Locking
      8. 12.8. Case Study
      9. 12.9. Summary
      10. 12.10. Review Questions
    19. 13. Maintaining Data
      1. 13.1. DB2 Data Movement Utilities: The Big Picture
      2. 13.2. Data Movement File Formats
        1. 13.2.1. Delimited ASCII (DEL) Format
        2. 13.2.2. Non-Delimited ASCII (ASC) Format
        3. 13.2.3. PC Version of IXF (PC/IXF) Format
        4. 13.2.4. WSF Format
        5. 13.2.5. Cursor
      3. 13.3. The DB2 EXPORT Utility
        1. 13.3.1. File Type Modifiers Supported in the Export Utility
          1. 13.3.1.1. Changing the Column Delimiter
          2. 13.3.1.2. Changing the Character Delimiter
          3. 13.3.1.3. Changing the Date Format
          4. 13.3.1.4. Changing the Code Page
        2. 13.3.2. Exporting Large Objects
        3. 13.3.3. Exporting XML Data
        4. 13.3.4. Specifying Column Names
        5. 13.3.5. Authorities Required to Perform an Export
        6. 13.3.6. Exporting a Table Using the Control Center
        7. 13.3.7. Run an export Command Using the ADMIN_CMD Procedure
      4. 13.4. The DB2 IMPORT Utility
        1. 13.4.1. Import Mode
        2. 13.4.2. Allow Concurrent Write Access
        3. 13.4.3. Regular Commits during an Import
        4. 13.4.4. Restarting a Failed Import
        5. 13.4.5. File Type Modifiers Supported in the Import Utility
          1. 13.4.5.1. Handling Target Tables with Generated and Identity Columns
        6. 13.4.6. Importing Large Objects
        7. 13.4.7. Importing XML Data
        8. 13.4.8. Select Columns to Import
        9. 13.4.9. Authorities Required to Perform an Import
        10. 13.4.10. Importing a Table Using the Control Center
        11. 13.4.11. Run an import Command with the ADMIN_CMD Procedure
      5. 13.5. The DB2 Load Utility
        1. 13.5.1. The Load Process
        2. 13.5.2. The LOAD Command
          1. 13.5.2.1. The MESSAGES, SAVECOUNT, and WARNINGCOUNT Options
          2. 13.5.2.2. Loading from a CURSOR
          3. 13.5.2.3. MODIFIED BY dumpfile and Exception Table
          4. 13.5.2.4. Loading from a Client
          5. 13.5.2.5. Locking Considerations during a Load
          6. 13.5.2.6. The INDEXING MODE Option
        3. 13.5.3. File Type Modifiers Supported in the load Utility
          1. 13.5.3.1. Leaving Free Space in Data and Index Pages
        4. 13.5.4. Loading Large Objects
        5. 13.5.5. Loading XML Data
        6. 13.5.6. Collecting Statistics
        7. 13.5.7. The COPY YES/NO and NONRECOVERABLE Options
        8. 13.5.8. Validating Data against Constraints
        9. 13.5.9. Performance Considerations
        10. 13.5.10. Authorities Required to Perform a Load
        11. 13.5.11. Loading a Table Using the Control Center
        12. 13.5.12. Run a load Command with the ADMIN_CMD Procedure
        13. 13.5.13. Monitoring a Load Operation
          1. 13.5.13.1. Table States
          2. 13.5.13.2. Table Space States
          3. 13.5.13.3. Load Querying
          4. 13.5.13.4. The LIST UTILITIES Command
      6. 13.6. The DB2MOVE Utility
      7. 13.7. The db2relocatedb Utility
      8. 13.8. Generating Data Definition Language
      9. 13.9. DB2 Maintenance Utilities
        1. 13.9.1. The RUNSTATS Utility
        2. 13.9.2. The REORG and REORGCHK Utilities
        3. 13.9.3. The REBIND Utility and the FLUSH PACKAGE CACHE Command
        4. 13.9.4. Database Maintenance Process
        5. 13.9.5. Automatic Database Maintenance
      10. 13.10. Case Study
      11. 13.11. Summary
      12. 13.12. Review Questions
    20. 14. Developing Database Backup and Recovery Solutions
      1. 14.1. Database Recovery Concepts: The Big Picture
        1. 14.1.1. Recovery Scenarios
        2. 14.1.2. Recovery Strategies
        3. 14.1.3. Unit of Work (Transaction)
        4. 14.1.4. Types of Recovery
          1. 14.1.4.1. Crash Recovery
          2. 14.1.4.2. Version Recovery
          3. 14.1.4.3. Roll Forward Recovery
      2. 14.2. DB2 Transaction Logs
        1. 14.2.1. Understanding the DB2 Transaction Logs
        2. 14.2.2. Primary and Secondary Log Files
        3. 14.2.3. Log File States
          1. 14.2.3.1. Active Logs
          2. 14.2.3.2. Online Archive Logs
          3. 14.2.3.3. Offline Archive Logs
        4. 14.2.4. Logging Methods
          1. 14.2.4.1. Circular Logging
          2. 14.2.4.2. Archival Logging
          3. 14.2.4.3. Infinite Active Logging
          4. 14.2.4.4. Log Mirroring
        5. 14.2.5. Handling the DB2 Transaction Logs
      3. 14.3. Recovery Terminology
        1. 14.3.1. Logging Methods versus Recovery Methods
        2. 14.3.2. Recoverable versus Nonrecoverable Databases
      4. 14.4. Performing Database and Table Space Backups
        1. 14.4.1. Online Access versus Offline Access
        2. 14.4.2. Database Backup
          1. 14.4.2.1. Database Backup on DPF Environments
        3. 14.4.3. Table Space Backup
        4. 14.4.4. Incremental Backups
        5. 14.4.5. Backing Up a Database with the Control Center
        6. 14.4.6. The Backup Files
      5. 14.5. Database and Table Space Recovery Using the RESTORE DATABASE Command
        1. 14.5.1. Database Recovery
        2. 14.5.2. Table Space Recovery
        3. 14.5.3. Table Space Recovery Considerations
        4. 14.5.4. Restoring a Database with the Control Center
        5. 14.5.5. Redirected Restore
          1. 14.5.5.1. Classic Redirected Restore
          2. 14.5.5.2. Redirected Restore Using an Automatically Generated Script
      6. 14.6. Database and Table Space Roll Forward
        1. 14.6.1. Database Roll Forward
        2. 14.6.2. Table Space Roll Forward
        3. 14.6.3. Table Space Roll Forward Considerations
        4. 14.6.4. Roll Forward a Database Using the Control Center
      7. 14.7. Recovering a Dropped Table
      8. 14.8. The Recovery History File
      9. 14.9. Database Recovery Using the RECOVER DATABASE Command
      10. 14.10. Rebuild Database Option
        1. 14.10.1. Rebuilding a Recoverable Database Using Table Space Backups
        2. 14.10.2. Rebuilding a Recoverable Database Using Only a Subset of the Table Space Backups
        3. 14.10.3. Rebuilding a Recoverable Database Using Online Backup Images That Contain Log Files
        4. 14.10.4. Rebuilding a Recoverable Database Using Incremental Backup Images
        5. 14.10.5. Rebuilding a Recoverable Database Using the Redirect Option
        6. 14.10.6. Rebuilding a Nonrecoverable Database
        7. 14.10.7. Database Rebuild Restrictions
      11. 14.11. Backup Recovery through Online Split Mirroring and Suspended I/O Support
        1. 14.11.1. Split Mirroring Key Concepts
        2. 14.11.2. The db2inidb Tool
        3. 14.11.3. Cloning a Database Using the db2inidb Snapshot Option
        4. 14.11.4. Creating a Standby Database Using the db2inidb Standby Option
        5. 14.11.5. Creating a Backup Image of the Primary Database Using the db2inidb Mirror Option
        6. 14.11.6. Split Mirroring in Partitioned Environments
        7. 14.11.7. Integrated Flash Copy
      12. 14.12. Maintaining High Availability with DB2
        1. 14.12.1. Log Shipping
          1. 14.12.1.1. Setting Up Log Shipping
        2. 14.12.2. Overview of DB2 High Availability Disaster Recovery (HADR)
          1. 14.12.2.1. HADR Setup
          2. 14.12.2.2. HADR Database States
          3. 14.12.2.3. HADR Synchronization Modes
            1. SYNC
            2. Nearsync
            3. ASYNC
          4. 14.12.2.4. HADR Commands Overview
          5. 14.12.2.5. Setting Up and Initializing HADR
          6. 14.12.2.6. Performing a Takeover—Switching Database Roles
          7. 14.12.2.7. Performing a Takeover—Failover
          8. 14.12.2.8. Summary of the Takeover Behavior with Respect to Database States
          9. 14.12.2.9. The Automatic Client Reroute Feature and HADR
          10. 14.12.2.10. Stopping HADR
          11. 14.12.2.11. The HADR Wizard
      13. 14.13. The Fault Monitor
        1. 14.13.1. The Fault Monitor Facility (Linux and UNIX Only)
        2. 14.13.2. Fault Monitor Registry File
        3. 14.13.3. Setting Up the DB2 Fault Monitor
      14. 14.14. Case Study
      15. 14.15. Summary
      16. Review Questions
    21. 15. The DB2 Process Model
      1. 15.1. The DB2 Process Model: The Big Picture
      2. 15.2. Threaded Engine Infrastructure
        1. 15.2.1. The DB2 Processes
      3. 15.3. The DB2 Engine Dispatchable Units
        1. 15.3.1. The DB2 Instance-Level EDUs
        2. 15.3.2. The DB2 Database-Level EDUs
        3. 15.3.3. The Application-Level EDUs
        4. 15.3.4. Per-Request EDUs
      4. 15.4. Tuning the Number of EDUs
      5. 15.5. Monitoring and Tuning the DB2 Agents
      6. 15.6. The Connection Concentrator
      7. 15.7. Commonly Seen DB2 Executables
      8. 15.8. Additional Services/Processes on Windows
      9. 15.9. Case Study
      10. 15.10. Summary
      11. 15.11. Review Questions
    22. 16. The DB2 Memory Model
      1. 16.1. DB2 Memory Allocation: The Big Picture
      2. 16.2. Instance-Level Shared Memory
      3. 16.3. Database-Level Shared Memory
        1. 16.3.1. The Database Buffer Pools
        2. 16.3.2. The Database Lock List
        3. 16.3.3. The Database Shared Sort Heap Threshold
        4. 16.3.4. The Package Cache
        5. 16.3.5. The Utility Heap Size
        6. 16.3.6. The Catalog Cache
        7. 16.3.7. Database Logging Parameters
        8. 16.3.8. Database Memory
      4. 16.4. Application-Level Shared Memory
        1. 16.4.1. Application Shared Heap
        2. 16.4.2. Application Heap
        3. 16.4.3. Statement Heap
        4. 16.4.4. Statistics Heap
        5. 16.4.5. Application Memory
      5. 16.5. Agent Private Memory
        1. 16.5.1. The Sort Heap and Sort Heap Threshold
        2. 16.5.2. Agent Stack
        3. 16.5.3. Client I/O Block Size
        4. 16.5.4. Java Interpreter Heap
      6. 16.6. The Memory Model
      7. 16.7. Case Study
      8. 16.8. Summary
      9. 16.9. Review Questions
    23. 17. Database Performance Considerations
      1. 17.1. Relation Data Performance Fundamentals
      2. 17.2. System/Server Configuration
        1. 17.2.1. Ensuring There Is Enough Memory Available
        2. 17.2.2. Ensuring There Are Enough Disks to Handle I/O
        3. 17.2.3. Ensuring There Are Enough CPUs to Handle the Workload
      3. 17.3. The DB2 Configuration Advisor
        1. 17.3.1. Invoking the Configuration Advisor from the Command Line
        2. 17.3.2. Invoking the Configuration Advisor from the Control Center
      4. 17.4. Configuring the DB2 Instance
        1. 17.4.1. Maximum Requester I/O Block Size
        2. 17.4.2. Intra-Partition Parallelism
        3. 17.4.3. Sort Heap Threshold
        4. 17.4.4. The DB2 Agent Pool
      5. 17.5. Configuring Your Databases
        1. 17.5.1. Average Number of Active Applications
        2. 17.5.2. Database Logging
        3. 17.5.3. Sorting
        4. 17.5.4. Locking
        5. 17.5.5. Buffer Pool Prefetching and Cleaning
      6. 17.6. Lack of Proper Maintenance
      7. 17.7. Automatic Maintenance
      8. 17.8. The Snapshot Monitor
        1. 17.8.1. Setting the Monitor Switches
        2. 17.8.2. Capturing Snapshot Information
        3. 17.8.3. Resetting the Snapshot Monitor Switches
      9. 17.9. Event Monitors
      10. 17.10. The DB2 Optimizer
      11. 17.11. The Explain Tool and Explain Tables
      12. 17.12. Using Visual Explain to Examine Access Plans
      13. 17.13. Workload Management
        1. 17.13.1. Preemptive Workload Management
        2. 17.13.2. Reactive Workload Management
      14. 17.14. Case Study
      15. 17.15. Summary
      16. 17.16. Review Questions
    24. 18. Diagnosing Problems
      1. 18.1. Problem Diagnosis: The Big Picture
      2. 18.2. How DB2 Reports Issues
      3. 18.3. DB2 Error Message Description
      4. 18.4. DB2 First Failure Data Capture
        1. 18.4.1. DB2 Instance-Level Configuration Parameters Related to FFDC
          1. 18.4.1.1. The DIAGPATH Parameter
          2. 18.4.1.2. The DIAGLEVEL Parameter
          3. 18.4.1.3. The NOTIFYLEVEL Parameter
        2. 18.4.2. db2diag.log Example
        3. 18.4.3. Administration Notification Log Examples
      5. 18.5. Receiving E-mail Notifications
      6. 18.6. Tools for Troubleshooting
        1. 18.6.1. The db2support tool
        2. 18.6.2. The DB2 Trace Facility
        3. 18.6.3. The db2dart Tool
        4. 18.6.4. The INSPECT Tool
        5. 18.6.5. DB2COS (DB2 Call Out Script)
        6. 18.6.6. DB2PDCFG Command
        7. 18.6.7. First Occurrence Data Capture (FODC)
      7. 18.7. Searching for Known Problems
      8. 18.8. Case Study
      9. 18.9. Summary
      10. 18.10. Review Questions
    25. A. 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
      18. Chapter 18
    26. B. Use of Uppercase versus Lowercase in DB2
    27. C. IBM Servers
    28. D. Using the DB2 System Catalog Tables
      1. Db2 System Catalog Tables
      2. How to Find Information in the System Catalog Tables
      3. How to Use the Sysstat Tables to Perform What-If Modeling and Analysis
    29. Resources

    Product information

    • Title: Understanding DB2®: Learning Visually with Examples, Second Edition
    • Author(s):
    • Release date: December 2007
    • Publisher(s): IBM Press
    • ISBN: 9780768681772