You are previewing IBM DB2 9.7 Advanced Administration Cookbook.
O'Reilly logo
IBM DB2 9.7 Advanced Administration Cookbook

Book Description

Over 100 recipes focused on advanced administration tasks to build and configure powerful databases with IBM DB2 book.

  • Master all the important aspects of administration from instances to IBM’s newest High Availability technology pureScale with this book and e-book.

  • Learn to implement key security features to harden your database's security against hackers and intruders.

  • Empower your databases by building efficient data configuration using MDC and clustered tables.

In Detail

IBM DB2 LUW is a leading relational database system developed by IBM. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. With lots of new features, DB2 9.7 delivers one the best relational database systems in the market.

IBM DB2 9.7 Advanced Administration Cookbook covers all the latest features with instance creation, setup, and administration of multi-partitioned database.

This practical cookbook provides step-by-step instructions to build and configure powerful databases, with scalability, safety and reliability features, using industry standard best practices.

This book will walk you through all the important aspects of administration. You will learn to set up production capable environments with multi-partitioned databases and make the best use of hardware resources for maximum performance.

With this guide you can master the different ways to implement strong databases with a High Availability architecture.

Table of Contents

  1. IBM DB2 9.7 Advanced Administration Cookbook
    1. IBM DB2 9.7 Advanced Administration Cookbook
    2. Credits
    3. About the Authors
    4. About the Reviewers
    5. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    6. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    7. 1. DB2 Instance—Administration and Configuration
      1. Introduction
      2. Creating and configuring instances for non-partitioned environments
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Updating instances using the db2iuptd command
      3. Creating and configuring a client instance
        1. Getting ready…
        2. How to do it…
        3. How it works...
        4. There's more...
        5. See also
      4. Creating and configuring an instance for multipartitioned environments
        1. Getting ready
        2. How to do it...
          1. Set up NFS for sharing the instance home
          2. Creating the instance owner and fenced user
          3. Set up SSH for client authentication
          4. Install DB2 ESE software with a response file option
          5. Configuring communication for inter-partition command execution
          6. Configuring the nodes
        3. How it works...
        4. There's more...
        5. See also
      5. Starting and stopping instances
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      6. Configuring SSL for client-server instance communication
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Listing and attaching to instances
        1. Getting ready
        2. How to do it...
          1. Listing instances
          2. Attaching to instances
        3. How it works...
        4. There's more...
        5. See also
      8. Dropping instances
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
    8. 2. Administration and Configuration of the DB2 Non-partitioned Database
      1. Introduction
      2. Creating and configuring DB2 non-partitioned databases
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Command preview
          2. Automatic storage
          3. Adaptive self-tuning memory
          4. File and directory permission on database objects
          5. UNIX links
          6. Default codeset
          7. Territory
          8. Collate using
          9. Control files
        5. See also
      3. Using Configuration Advisor
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Main configuration characteristics of OLTP databases
          2. Main configuration characteristics of DSS databases
          3. Main configuration characteristics of mixed processing databases
        5. See also
      4. Creating a database from an existing backup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Roll-forward recovery
          2. Redirected restore
        5. See also
      5. Configuring automatic database maintenance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Backups
          2. Reorgs
          3. Runstats
        5. See also
      6. Managing federated databases—connecting to Oracle and MSSQL
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      7. Altering databases
        1. How to do it…
        2. How it works…
        3. From Oracle to DB2
          1. Startup/shutdown instance
          2. Startup/shutdown database
          3. Database file containers
          4. Log files
        4. How it works...
        5. There's more...
          1. Control files
          2. Quiesce instance/database
          3. Backup and recovery
          4. Standby databases
        6. See also
      8. Dropping databases
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Backup history
          2. Keep safe
        5. See also
    9. 3. DB2 Multipartitioned Databases—Administration and Configuration
      1. Introduction
      2. Creating and configuring a multipartitioned database
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Adding database partitions
        1. Getting ready
        2. How to do it...
          1. Using Control Center
          2. Using the command line
        3. How it works...
        4. There's more…
      4. Creating database partition groups
        1. Getting ready
        2. How to do it…
          1. Using Control Center
          2. Using the command line
          3. Using Control Center
          4. Using the command line
            1. Create the NAV application's table spaces
        3. How it works...
        4. There's more...
        5. See also
      5. Altering database partition groups—adding partitions to database partition groups
        1. Getting ready
        2. How to do it...
          1. Using Control Center
          2. Using the command line
        3. How it works…
        4. There's more…
        5. See also
      6. Managing data redistribution on database partition groups
        1. Getting ready
        2. How to do it...
          1. Using the command line
        3. How it works…
        4. There's more…
      7. The table distribution key and its role in a multipartitioned environment
        1. Getting ready
        2. How to do it...
          1. Using the command line
        3. How it works...
        4. There's more...
          1. Table collocation
      8. Altering database partition groups— removing partitions from a database partition group
        1. Getting ready
        2. How to do it...
          1. Using the command line
        3. How it works...
        4. There's more...
      9. Removing database partitions
        1. Getting ready
        2. How to do it...
          1. Using Control Center
          2. Using the command line
        3. How it works...
        4. There's more...
      10. Converting a non-partitioned database to a multipartitioned database on MS Windows
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      11. Configuring Fast Communication Manager
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
        5. See also
    10. 4. Storage—Using DB2 Table Spaces
      1. Introduction
      2. Creating and configuring table spaces within automatic storage databases
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Using self tuning buffer pools
          2. Physical implementation
          3. Adding a storage path to a manual storage database
      3. Creating and configuring SMS table spaces
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Space allocation
          2. Tables and objects
          3. Limits
          4. Filesystem caching
          5. Limits
        5. See also
      4. Creating and configuring DMS table spaces
          1. Benefits
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Raw partitions
          2. Space allocation
          3. Tables and objects
          4. Filesystem caching
          5. Extent size
          6. Prefetch size
          7. Striping strategy
          8. High water mark
        5. See also
      5. Using system temporary table spaces
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Reorgs
      6. Using user temporary table spaces
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Page size for temporary table space
          2. SMS or DMS?
        5. See also
      7. Altering table spaces and dropping table spaces
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
          1. Container management
          2. Size management
          3. Physical characteristics
          4. Performance characteristics
          5. Dropping table space
        5. See also
      8. Table spaces in a multipartitioned environment
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Distribution maps
          2. Distribution keys
          3. Table space containers
          4. Partition groups
          5. Table space state
          6. Storage paths in an automatic storage-partitioned database
        5. See also
    11. 5. DB2 Buffer Pools
      1. Introduction
      2. Creating and configuring buffer pools
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. Naming convention
            2. Specifying space size for the buffer pool
            3. Memory sizing
            4. Windows 32-bit environments: Address Windowing Extensions (AWE) and Extended Storage (ESTORE)
            5. Hidden buffer pools
          3. See also
      3. Configuring the block-based area
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. Block size and table space extent size
          3. See also
      4. Managing buffer pools in a multipartitioned database
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
          3. See also
      5. Altering buffer pools
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Page size
          2. Buffer pool size
          3. Self-tuning
          4. Partitions
          5. Partition groups
        5. See also
      6. Dropping buffer pools
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. IBMDEFAULTBP
            2. Dependencies
          3. See also
    12. 6. Database Objects
      1. Introduction
      2. Creating and using MDC tables and block-based indexes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Block indexes and aggregate functions in queries
          2. Block size and extents
          3. Using runstats to determine dimension candidates
          4. Restrictions on dimensions
          5. Rollout
          6. Partitioned database
        5. See also
      3. Creating and using materialized query tables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Refresh deferred
          2. Maintained by system/user
          3. Query rewrites
          4. Combining with table partitioning
          5. Replicated MQTs and database partitioning
        5. See also
      4. Implementing table partitioning
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Scalability
          2. Non-partitioned indexes
          3. Partitioned indexes
          4. Storage strategies
          5. Adding a partition to a table
          6. Detach a partition from a table
        5. See also
      5. Using temporary tables
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Created global temporary table
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Restrictions
          2. Recovery
        4. See also
    13. 7. DB2 Backup and Recovery
      1. Introduction
      2. Configuring database logging
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Performing an offline database backup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Backup naming convention
          2. Partitioned database
          3. Recovery from offline backup
        5. See also
      4. Performing a full online database backup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Resource usage
          2. Restrictions
          3. Backup pending
        5. See also
      5. Performing an incremental delta database backup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Table space name change
          2. Recovery history file
        5. See also
      6. Performing an incremental cumulative database backup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Backing up table spaces
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Operations incompatible with online table space backups
        5. See also
      8. Crash recovery
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. How crash recovery works to ensure database integrity
            2. Inspect database
          3. See also
      9. Full database recovery
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Attempting to recover from a backup of database in another logging mode
        5. See also
      10. Database rollforward recovery
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Database state after restore
          2. Point-in-time recovery on a partitioned database
          3. History of restore operations
        5. See also
      11. Incremental restore
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Archive log file not associated with the current log sequence
        5. See also
      12. Recovering table spaces—full and rollforward recovery
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Backup image
          2. Table space with system catalog
          3. Partitioned database
          4. Partitioned table
        5. See also
      13. Redirected restore
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. ABORT
        5. See also
      14. Recovery history file
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Location
          2. List history
          3. PRUNE
          4. Drop database
          5. Restore database
      15. Configuring tape-based backup with IBM Tivoli Storage Manager
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Partitioned environment
        5. See also
      16. db2move and db2look utilities as alternative backup methods
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Using db2move to load data
          2. LOAD with care
        5. See also
    14. 8. DB2 High Availability
      1. Introduction
      2. Setting up HADR by using the command line
        1. Getting ready
        2. How to do it...
          1. Install IBM DB2 ESE on nodedb22
          2. Creating additional directories for table space containers, archive logs, backup, and mirror logs
          3. Setting permissions on the new directories
          4. Configuring archive log and mirror log locations
          5. Configuring LOGINDEXBUILD and INDEXREC parameters
          6. Backing up the primary database
          7. Copying the database backup to nodedb22
          8. Restoring the database NAV on nodedb22
          9. Setting up HADR communication ports
          10. Setting up HADR parameters on the primary database
          11. Setting up HADR parameters on the standby database
          12. Starting HADR on standby database
          13. Starting HADR on primary database
          14. Monitoring HADR
        3. How it works…
        4. There's more…
          1. The hadr_timeout and hadr_peer_window database configuration parameters
        5. See also
      3. Setting up HADR by using Control Center
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Changing HADR synchronization modes
        1. Getting ready
        2. How to do it...
          1. Changing to NEARSYNC synchronization mode
          2. Changing to SYNC synchronization mode
          3. Changing back to ASYNC synchronization mode
        3. How it works...
        4. There's more...
      5. Performing takeover and takeover by force
        1. Getting ready
        2. How to do it...
          1. Using Control Center
          2. Using the command line to perform a takeover
          3. Using the command line to perform a takeover by force
        3. How it works...
        4. There's more...
      6. Using automated client rerouting with HADR
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Opening the standby database in read-only mode
        1. Getting ready…
        2. How to do it…
        3. How it works...
        4. There's more...
      8. Using the DB2 fault monitor
        1. Getting ready
        2. How to do it...
        3. How it works…
        4. There's more...
    15. 9. Problem Determination, Event Sources, and Files
      1. Introduction
      2. Using db2mtrk—DB2 memory tracker
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Using db2pd—DB2 problem determination tool
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Using db2dart—DB2 database analysis and reporting tool command
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Using db2ckbkp—DB2 check backup tool for backup integrity
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      6. Using db2support to collect diagnostic data
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    16. 10. DB2 Security
      1. Introduction
      2. Managing instance-level authorities
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Managing database-level authorities and privileges
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Security administrator authority
      4. Managing object privileges
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
      5. Using roles
        1. Getting ready
        2. How it works...
        3. There's more…
          1. Creating roles using the WITH ADMIN OPTION
          2. SECAD exception
      6. Using table encryption
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Using label-based access control (LBAC) to strengthen data privacy
        1. Getting ready…
        2. How to do it...
          1. Creating security label components
          2. Defining security policies
          3. Creating security labels
          4. Modifying read and write access rules by using exemptions
          5. One more example using the ARRAY security component label
        3. How it works...
        4. There's more…
      8. Auditing DB2
        1. Getting ready
        2. How to do it...
          1. Configuring auditing scopes
          2. Configure audit data path and archive path:
          3. Archiving, formatting, and extracting the audit data:
          4. Using audit policies
        3. How it works...
        4. There's more…
    17. 11. Connectivity and Networking
      1. Introduction
      2. Configuring network communications
        1. Getting ready
        2. How to do it...
          1. Using Control Center
        3. How it works...
        4. There's more...
        5. See also
      3. Cataloging and uncataloging instances and databases
        1. Getting ready
        2. How to do it...
          1. Using Control Center
        3. How it works...
        4. See also
        5. There's more...
      4. Using DB2 Discovery
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Communications with DRDA servers (z/OS and i/OS)
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      6. Monitoring and configuring FCM for optimal performance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    18. 12. Monitoring
      1. Introduction
      2. Configuring and using system monitoring
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Querying system information using table functions
          2. Querying activity information using table functions
          3. Querying data objects information using table functions
          4. Workload management (WLM)
        5. See also
      3. Configuring and using snapshot monitoring
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Table functions and administrative views
          2. Database load
          3. Buffer pool hit ratios
          4. Buffer pool physical reads and writes per transaction
          5. Average sorting time
          6. Sorting time per transaction
          7. Lock wait time
          8. Deadlocks and lock timeouts
          9. Rows read/rows selected
          10. Dirty steal BP clean/transaction
          11. Package cache inserts/transaction
          12. Average log writes/transaction
        5. See also
      4. Configuring and using event monitoring
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Unformatted event table
          2. Using the db2evmonfmt tool for reporting
            1. Table space use
            2. Pruning event monitor tables
            3. Resetting a monitor's counters
            4. Workload management (WLM)
          3. See also
      5. Using Memory Visualizer
        1. Getting ready
        2. How to do it...
        3. There's more...
          1. Self tuning memory management
        4. See also
      6. Using Health Monitor
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Getting recommendations for an alert
          2. Using the command line to check and set configuration
        5. See also
    19. 13. DB2 Tuning and Optimization
      1. Introduction and general tuning guidelines
      2. Operating system tuning
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Virtual storage
          2. Disabling file system caching on table spaces
          3. Maintaining a historic record
        5. See also
      3. Resolving CPU bottlenecks
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Utilities
          2. Context switches
        5. See also
      4. Tuning memory utilization
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. AUTOCONFIGURE
          2. Self-tuning memory in partitioned database environments
        5. See also
      5. Collecting object statistics with the RUNSTAT utility
        1. How to do it...
        2. How it works...
        3. There's more...
      6. Default automatic statistics collection
        1. Collecting statistics using a statistics profile
        2. Background stats collecting (asynchronous)
        3. Real-time stats collecting (synchronous)
        4. Setting priority
        5. Automatic statistics profiling
          1. Partitioned database
        6. See also
      7. Tuning with indexes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Sample output
          2. Recommendations
        5. See also
      8. Tuning sorting
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Sort overflows
          2. Piped sorts
          3. Coding practices
        5. See also
      9. Hit ratios and their role in performance improvement
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Buffer pools
          2. Catalog cache
          3. Package cache
          4. Log buffer
        5. See also
      10. I/O tuning
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Logpath
          2. Diagnostic logs on partitioned databases
          3. Reorgchk
        5. See also
      11. Using logging and nologging modes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Catalog locks
          2. Recovery
          3. LOAD with non-recoverable option
          4. High Availability Disaster Recovery (HADR)
        5. See also
      12. Using parallelism
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. RAID 5 and DB2_PARALLEL_IO
      13. Loading a table
        1. See also
      14. Using EXPLAIN PLAN
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Sample output
          2. Watch out for...
          3. Importing production statistics
        5. See also
      15. Creating a benchmark testing scenario
        1. Getting ready
        2. How to do it...
        3. There's more...
          1. Warm-up run
        4. See also
    20. 14. IBM pureScale Technology and DB2
      1. Introduction
      2. Managing instances, members, and cluster facilities in DB2 pureScale
        1. Getting ready
        2. How to do it...
          1. Creating and managing a DB2 pureScale instance
          2. Managing DB2 pureScale members
            1. Starting pureScale members
          3. Managing the DB2 pureScale caching facilities
            1. Stopping the caching facility
            2. Starting pureScale cluster facilities
        3. How it works…
        4. There's more…
          1. Put pureScale members in maintenance mode
      3. Monitoring DB2 pureScale environments
        1. Getting ready
        2. How to do it…
          1. Monitoring cluster facility memory structures
        3. How it works…
        4. There's more…
      4. High availability in DB2 pureScale environments
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      5. Backup and recovery in DB2 pureScale environments
        1. Getting ready
        2. How to do it…
          1. Performing an offline database backup
          2. Performing an online database backup
          3. Performing a restore and rollforward recovery
          4. Performing database recovery
        3. How it works…
        4. There's more…