You are previewing DB2 9 for Developers.
O'Reilly logo
DB2 9 for Developers

Book Description

Written by an "in-the-trenches" consultant, this guide hails the newest version of DB2 as a major release, highlighting the large number of totally new features, most notably the addition of XML capabilities. Packed with the information DB2 developers and administrators need to know when implementing version 9, the discussion covers upgrading from prior releases of DB2, converting relational data to XML data, and how DB2 supports industry standard schemas. With detailed examples and useful scripts, users learn how to develop DB2 XML applications, design an XML database, and tune the DB2 XML hybrid database. Extensive information on DB2 optimization of SQL and XML is a particularly useful feature of the book and includes a rundown on import and export utilities that database managers can use in their own installations. Going far beyond the standard manuals and certification guides, this is a truly comprehensive resource that teaches the "why" and "how" of a well-designed and well-implemented DB2 database.

Table of Contents

  1. Copyright
    1. Dedication
  2.  
  3. Foreword
  4. 1. DB2 9 Product Overview
    1. DB2 Express-C
    2. DB2 Express
    3. DB2 Workgroup Server Edition
    4. DB2 Personal Edition
    5. DB2 Enterprise Server Edition
    6. Database Enterprise Developer Edition
    7. DB2 Developer Workbench
    8. Information Management Products
      1. WebSphere Federation Server
      2. WebSphere Replication Server
      3. WebSphere Data Event Publisher
    9. Summary
  5. 2. DB2 9 Enhancements
    1. Native XML Data Store Support
    2. Application Development Enhancements
      1. Enhanced .NET Integration
      2. DB2 Developer Workbench
      3. JDBC Enhancements
    3. Performance and Scalability Enhancements
      1. Row Compression
      2. Table Partitioning
      3. Materialized Query Table Enhancements
      4. Larger Record Identifiers
      5. Statistical Views
      6. Faster Data Loading Capabilities
    4. Manageability Enhancements
      1. Alter Table Statement
      2. Improved Maintenance Policies
      3. Database Administration
    5. Installation and Fix Pack Enhancements
    6. Backup and Recovery Enhancements
    7. Security Enhancements
    8. Problem Determination and Troubleshooting Enhancements
    9. Summary
  6. 3. XML Evolution and Revolution
    1. XML Introduction and Historical Perspective
      1. SGML, HTML, and XML
    2. Components of XML
      1. XML Namespaces
      2. XSLT
    3. XML-Only Databases
    4. XML’s Emergence As a Data-Interchange Mechanism
    5. Initial XML Relational Database Implementations
      1. Early XML Support in DB2
    6. DB2 9: The Revolution
      1. Relational and XML Integration
      2. New DB2 9 Architecture
    7. Summary
  7. 4. The Path to DB2 9 XML Capabilities
    1. XQuery and XPath
      1. Creating Queries in XQuery
    2. XPath Data Model
    3. DB2 9 Hybrid Architecture
      1. Components of DB2 9 XML Support
      2. DB2 9 Optimizer Extensions
      3. XML Schema Repository
    4. DB2 9 Native XML Storage Architecture
    5. Querying DB2 9 XML Data
      1. DB2 9 Methods of Querying XML Data
        1. XQuery-Only
        2. XQuery That Invokes SQL
        3. SQL-Only
        4. SQL/XML Functions That Execute XQuery Expressions
    6. Summary
  8. 5. Developer Workbench: Developing Applications
    1. Getting Started with DWB
      1. Creating a Project
    2. Creating an SQL Procedure
      1. Deploying a Stored Procedure
      2. Debugging a Stored Procedure
      3. Exporting a Stored Procedure
    3. Switching Workspaces
    4. Topology View
    5. Working with XML and DWB
      1. Building XML Schema
      2. Registering XML Schemas
      3. Generating XML Documents from an XML Schema
      4. Inserting an XML Document into an XML Column
    6. Building an XQuery
    7. Summary
  9. 6. DB2 9 and Service Oriented Architecture
    1. Service Oriented Architecture
    2. Web Service Functions and Protocols
      1. SOAP
      2. WSDL
      3. UDDI
    3. SOA and DB2 9
    4. DB2 Example
    5. Summary
  10. 7. Logical and Physical Design
    1. Logical Design
      1. Business Model
        1. Business Entities
      2. Conceptual Model
      3. Logical Model
      4. Business Rules
      5. Entities and Relationships
        1. Entity
        2. Relationship
        3. Relation
        4. Functional Dependencies
        5. Key
        6. Primary Key
        7. Candidate Key
        8. Foreign Key
        9. Cardinality
          1. One-to-One Relationship (A → B and B → A)
          2. One-to-Many Relationship (A → B but B not → A)
          3. Many-to-Many Relationship (A Not → B and B Not → A)
        10. Special Entity Relationships
          1. Characteristic Entity
          2. Associative Entity
      6. Normalization
        1. First Normal Form (1NF)
        2. Second Normal Form (2NF)
        3. Third Normal Form (3NF)
        4. Boyce-Codd Normal Form (BCNF)
        5. Domain/Key Normal Form (DK/NF)
      7. Unified Modeling Language
      8. A Word About Modeling Tool Support for XML
      9. Logical Design Outputs
    2. Physical Design
      1. Denormalization
        1. Reverse Engineering
        2. Forward Engineering
      2. Creation of Indexes
      3. Creation of Table Spaces and Tables
      4. Table Space Breakout Strategy
      5. Buffer Pool Strategy
    3. Summary
  11. 8. Tuning Buffer Pools
    1. Creating Buffer Pools
    2. Altering Buffer Pools
    3. Dropping Buffer Pools
    4. Monitoring Buffer Pool Performance
      1. Monitor Switches
      2. Snapshot Commands
      3. DB2 Event Monitors
      4. Determining Buffer Pool Efficiency and Effectiveness
        1. Index Hit Ratio
        2. Overall Hit Ratio
        3. Physical Pages Read per Minute
        4. Asynchronous Pages Read per Request
        5. Asynchronous and Synchronous Read Percentages
        6. I/O Performance Measurements: ORMS, ARMS, and SRMS
    5. Monitoring and Tuning Tables, Buffer Pools, and Table Spaces
      1. Table Activity
      2. Table Space Activity
      3. Assigning Table Spaces to Buffer Pools: General Concepts
      4. Optimum Buffer Pool Assignments
      5. Sizing Buffer Pools
      6. Block Prefetch I/O: Special Considerations
    6. Summary
  12. 9. The Way to DB2: The Optimizer
    1. The DB2 Optimizer
      1. Program Preparation
    2. DFT_QUERYOPT Parameter
      1. Optimization Class 0
      2. Optimization Class 1
      3. Optimization Class 2
      4. Optimization Class 3
      5. Optimization Class 5
      6. Optimization Class 7
      7. Optimization Class 9
      8. Optimization Class Recommendations
    3. SQL/XML Predicate Coding Best Practices
    4. Partition Elimination
    5. Creating Indexes over XML Data
    6. SQL and XML Explain Facilities
      1. DB2 Visual Explain
      2. db2exfmt
        1. db2exfmt Output
        2. Creating Explain Tables for Use with db2exfmt
    7. DB2 Design Advisor
    8. RUNSTATS
    9. Summary
  13. 10. Utilities
    1. The XML Data Type and the IMPORT Command
      1. XML FROM path
      2. XMLVALIDATE
      3. XMLPARSE
      4. IMPORT Example
      5. XML Data Specifier (XDS)
    2. The XML Data Type and the EXPORT Command
      1. XML TO xml-path
      2. XMLFILE filename
      3. XMLSAVESCHEMA
      4. MODIFIED BY
      5. Exporting XML Data
      6. The db2look Command and XML Support
      7. The db2move Command and XML Support
    3. Reorganizing Indexes and Tables
      1. Index Reorganization Options
        1. INDEXES ALL FOR TABLE table-name
      2. Table Reorganization Options
        1. TABLE table-name
        2. INDEX index-name
        3. INPLACE
        4. USE tablespace-name
        5. INDEXSCAN
      3. Additional REORG Information
      4. REORG Tips and Techniques
      5. REORG Examples
    4. Backup Database Utility
      1. BACKUP Command Options
        1. DATABASE database-alias
        2. TABLESPACE tablespace-name
        3. USE TSM
        4. OPEN num-sessions SESSIONS
        5. TO dir/dev
        6. LOAD library-name
        7. WITH num-buffers BUFFERS
        8. BUFFER buffer-size
        9. PARALLELISM n
        10. UTIL_IMPACT_PRIORITY priority
        11. COMPRESS
        12. EXCLUDE LOGS
        13. INCLUDE LOGS
        14. WITHOUT PROMPTING
      2. BACKUP Example
    5. Recover Database Command
      1. RECOVER Command Options
        1. DATABASE database-alias
        2. TO isotime
        3. END OF LOGS
        4. USING HISTORY FILE history-file
        5. OVERFLOW LOG PATH log-directory
        6. COMPRLIB lib-name
        7. COMPROPTS options-string
        8. RESTART
      2. RECOVER Examples
    6. Restore Database Utility
      1. RESTORE Command Options
        1. DATABASE source-database-alias
        2. CONTINUE
        3. ABORT
        4. REBUILD WITH
        5. TABLESPACE tablespace-name
        6. ONLINE
        7. HISTORY FILE
        8. COMPRESSION LIBRARY
        9. LOGS
        10. INCREMENTAL
        11. USE TSM
        12. OPEN num-sessions SESSIONS
        13. USE XBSA
        14. FROM directory/device
        15. LOAD shared-library
        16. TAKEN AT date-time
        17. TO target-directory
        18. DBPATH ON target-directory
        19. ON path-list
        20. INTO target-database-alias
        21. LOGTARGET directory
        22. NEWLOGPATH directory
        23. WITH num-buffers Buffers
        24. BUFFER buffer-size
        25. REPLACE HISTORY FILE
        26. REPLACE EXISTING
        27. REDIRECT
        28. GENERATE SCRIPT script
        29. WITHOUT ROLLING FORWARD
        30. PARALLELISM n
        31. COMPRLIB name
        32. COMPROPTS string
        33. WITHOUT PROMPTING
      2. RESTORE Examples
    7. DB2 9 Data Compression and the INSPECT Utility
      1. Compression-Related INSPECT Command Options
        1. ROWCOMPESTIMATE
        2. TABLE NAME table-name
        3. SCHEMA schema-name
        4. TBSPACEID n OBJECTID n
        5. RESULTS
        6. KEEP
        7. File-name
      2. INSPECT Example
    8. Migrate Database Utility
      1. Migration Tips and Techniques
    9. LOAD Utility
      1. The LOAD Command
      2. Load Command Options
        1. CLIENT
        2. FROM filename/pipename/device/cursorname
        3. OF filetype
        4. LOBS FROM lob-path
        5. MODIFIED BY filetype-mod
        6. METHOD
        7. SAVECOUNT n
        8. ROWCOUNT n
        9. WARNINGCOUNT n
        10. MESSAGES message-file
        11. TEMPFILES PATH temp-pathname
        12. INSERT
        13. REPLACE
        14. RESTART
        15. TERMINATE
        16. INTO table-name
        17. insert-column
        18. NORANGEEXC
        19. NOURNIQUEEXC
        20. STATISTICS USE PROFILE
        21. STATISTICS NO
        22. COPY NO
        23. COPY YES
        24. NONRECOVERABLE
        25. WITHOUT PROMPTING
        26. DATA BUFFER buffer-size
        27. SORT BUFFER buffer-size
        28. CPU_PARALLELISM n
        29. DISK_PARALLELISM n
        30. FETCH_PARALLELISM
        31. INDEXING MODE
        32. DEFERRED
        33. ALLOW NO ACCESS
        34. ALLOW READ ACCESS
        35. SET INTEGRITY PENDING CASCADE
        36. LOCK WITH FORCE
        37. SOURCEUSEREXITexecutable
      3. LOAD Command Examples
      4. Load from Cursor
    10. Summary
  14. 11. Monitoring
    1. Monitoring
      1. Online Monitoring
      2. Exception-Based Monitoring
    2. Snapshot Monitoring
      1. Monitor Switches
        1. Instance-Level Switches
        2. Application-Level Switches
      2. Classic Snapshots
        1. Database Manager Snapshot
        2. Database Snapshot
        3. Buffer Pool Snapshot
        4. Table Snapshot
        5. Lock Snapshot
        6. Application Snapshot
          1. Application Snapshot on AGENTID
        7. Dynamic SQL Snapshot
      3. SQL Administrative Routines and Convenience Views
    3. Health Center and Health Monitor
    4. Activity Monitor
    5. db2pd Utility
      1. db2pd Examples
    6. Memory Tracker
    7. Event Monitoring
      1. Event Monitor Creation
        1. CREATE EVENT MONITOR Command Options
        2. Event Condition Options
        3. Output Options
        4. Table Options
        5. Event Monitor Scope Options
      2. Event Monitor Catalog Tables
      3. Write-to-Table Event Monitors
        1. General Considerations for Write-to-Table Event Monitors
        2. DB2 Event Analyzer
    8. Summary
  15. 12. Performance
    1. DB2 Memory Model
      1. Instance Memory
      2. Database Shared Memory
      3. Agent Private Memory
        1. DB2 Process Model
    2. DB2 Self-Tuning Memory Manager
      1. STMM Tuning Modes
      2. Using STMM in an HADR Configuration
    3. Instance-Level Tuning Considerations
      1. Shared Sort Performance
        1. Piped and Non-Piped Sorts
    4. Database-Level Tuning Considerations
      1. Sort Performance
      2. Hash Join Performance
      3. Locking Performance
        1. Locklist High Water Mark
        2. Lock Escalations
        3. Exclusive Lock Escalations
        4. Deadlocks Detected
      4. Resolving Lock Contention Using the Activity Monitor
      5. Buffer Pool Performance
        1. Buffer Pool Combined Hit Ratio
        2. Buffer Pool Index Hit Ratio
        3. Buffer Pool Data Hit Ratio
        4. Asynchronous Read Milliseconds
        5. Synchronous Read Milliseconds
        6. Asynchronous Pages Read per Request
        7. Physical Pages Read per Minute
        8. Asynchronous Write Milliseconds
        9. Overall Read Milliseconds
        10. Asynchronous Read Ratio
        11. Synchronous Write Milliseconds
      6. Page-Cleaning Activity
        1. Dirty Page Steal Cleaner Triggers
        2. No Victim Buffers Available
      7. Prefetching Performance
      8. Logging Performance
    5. Summary
  16. 13. OS Monitoring: Tips and Techniques
    1. Monitoring Methodology
    2. Scenario #1: High CPU and Disk Utilization
      1. Scenario #1 Description
      2. Corrective Actions
    3. Scenario #2: SQL Gone Wild
    4. Scenario #3: Monitoring DB2 on AIX and Linux
      1. iostat
    5. Summary
  17. 14. Problem Determination
    1. Connectivity Problems
    2. Performance and Application Problems
    3. DB2 Code (Defect) Problems
    4. DB2 Problem-Determination Aids
      1. db2diag Tool
        1. The db2diag.log File
          1. Log Entry Format
        2. SQLCA Entries in File db2diag.log
      2. DB2 Administration Notification Log
      3. System Logs
      4. CLI Trace
      5. DB2 Trace
      6. Dumps
      7. Dr. Watson
      8. Traps
        1. db2xprt
      9. db2pd
      10. db2pdcfg
      11. db2cos
    5. Sending Information to DB2 Support
      1. db2support Examples
    6. Summary
  18. A. DB2 Information Sources on the Web
  19. B. SQL and XQuery Limits
  20. C. New Environment Variables in DB2 9
    1. New DB2 9 Environmental Variables
      1. DB2_COPY_NAME
      2. DB2RCMD_LEGACY_MODE
      3. DB2_OPT_MAX_TEMP_SIZE
      4. DB2_ENABLE_AUTOCONFIG_DEFAULT
      5. DB2_MAX_LOB_BLOCK_SIZE