IBM DB2 9.7 Advanced Administration Cookbook电子书

IBM DB2 9.7 Advanced Administration Cookbook

Table of Contents

IBM DB2 9.7 Advanced Administration Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. DB2 Instance—Administration and Configuration

Introduction

Creating and configuring instances for non-partitioned environments

Getting ready

How to do it...

How it works...

There's more...

Updating instances using the db2iuptd command

Creating and configuring a client instance

Getting ready…

How to do it…

How it works...

There's more...

See also

Creating and configuring an instance for multipartitioned environments

Getting ready

How to do it...

Set up NFS for sharing the instance home

Creating the instance owner and fenced user

Set up SSH for client authentication

Install DB2 ESE software with a response file option

Configuring communication for inter-partition command execution

Configuring the nodes

How it works...

There's more...

See also

Starting and stopping instances

Getting ready

How to do it...

How it works...

There's more...

Configuring SSL for client-server instance communication

Getting ready

How to do it...

How it works...

There's more...

See also

Listing and attaching to instances

Getting ready

How to do it...

Listing instances

Attaching to instances

How it works...

There's more...

See also

Dropping instances

Getting ready

How to do it...

How it works...

There's more…

2. Administration and Configuration of the DB2 Non-partitioned Database

Introduction

Creating and configuring DB2 non-partitioned databases

Getting ready

How to do it...

How it works...

There's more...

Command preview

Automatic storage

Adaptive self-tuning memory

File and directory permission on database objects

UNIX links

Default codeset

Territory

Collate using

Control files

See also

Using Configuration Advisor

Getting ready

How to do it...

How it works...

There's more...

Main configuration characteristics of OLTP databases

Main configuration characteristics of DSS databases

Main configuration characteristics of mixed processing databases

See also

Creating a database from an existing backup

Getting ready

How to do it...

How it works...

There's more...

Roll-forward recovery

Redirected restore

See also

Configuring automatic database maintenance

Getting ready

How to do it...

How it works...

There's more...

Backups

Reorgs

Runstats

See also

Managing federated databases—connecting to Oracle and MSSQL

Getting ready

How to do it...

How it works...

See also

Altering databases

How to do it…

How it works…

From Oracle to DB2

Startup/shutdown instance

Startup/shutdown database

Database file containers

Log files

How it works...

There's more...

Control files

Quiesce instance/database

Backup and recovery

Standby databases

See also

Dropping databases

Getting ready

How to do it...

How it works...

There's more...

Backup history

Keep safe

See also

3. DB2 Multipartitioned Databases—Administration and Configuration

Introduction

Creating and configuring a multipartitioned database

Getting ready

How to do it...

How it works...

There's more...

Adding database partitions

Getting ready

How to do it...

Using Control Center

Using the command line

How it works...

There's more…

Creating database partition groups

Getting ready

How to do it…

Using Control Center

Using the command line

Using Control Center

Using the command line

Create the NAV application's table spaces

How it works...

There's more...

See also

Altering database partition groups—adding partitions to database partition groups

Getting ready

How to do it...

Using Control Center

Using the command line

How it works…

There's more…

See also

Managing data redistribution on database partition groups

Getting ready

How to do it...

Using the command line

How it works…

There's more…

The table distribution key and its role in a multipartitioned environment

Getting ready

How to do it...

Using the command line

How it works...

There's more...

Table collocation

Altering database partition groups— removing partitions from a database partition group

Getting ready

How to do it...

Using the command line

How it works...

There's more...

Removing database partitions

Getting ready

How to do it...

Using Control Center

Using the command line

How it works...

There's more...

Converting a non-partitioned database to a multipartitioned database on MS Windows

Getting ready

How to do it...

How it works...

There's more...

Configuring Fast Communication Manager

Getting ready

How to do it…

How it works...

There's more...

See also

4. Storage—Using DB2 Table Spaces

Introduction

Creating and configuring table spaces within automatic storage databases

Getting ready

How to do it...

How it works...

There's more...

Using self tuning buffer pools

Physical implementation

Adding a storage path to a manual storage database

Creating and configuring SMS table spaces

Getting ready

How to do it...

How it works...

There's more...

Space allocation

Tables and objects

Limits

Filesystem caching

Limits

See also

Creating and configuring DMS table spaces

Benefits

Getting ready

How to do it...

How it works...

There's more...

Raw partitions

Space allocation

Tables and objects

Filesystem caching

Extent size

Prefetch size

Striping strategy

High water mark

See also

Using system temporary table spaces

Getting ready

How to do it...

How it works...

There's more...

Reorgs

Using user temporary table spaces

Getting ready

How to do it...

How it works...

There's more...

Page size for temporary table space

SMS or DMS?

See also

Altering table spaces and dropping table spaces

Getting ready

How to do it…

How it works...

There's more...

Container management

Size management

Physical characteristics

Performance characteristics

Dropping table space

See also

Table spaces in a multipartitioned environment

Getting ready

How to do it...

How it works...

There's more...

Distribution maps

Distribution keys

Table space containers

Partition groups

Table space state

Storage paths in an automatic storage-partitioned database

See also

5. DB2 Buffer Pools

Introduction

Creating and configuring buffer pools

Getting ready

How to do it...

How it works...

There's more...

Naming convention

Specifying space size for the buffer pool

Memory sizing

Windows 32-bit environments: Address Windowing Extensions (AWE) and Extended Storage (ESTORE)

Hidden buffer pools

See also

Configuring the block-based area

Getting ready

How to do it...

How it works...

There's more...

Block size and table space extent size

See also

Managing buffer pools in a multipartitioned database

Getting ready

How to do it...

How it works...

There's more...

See also

Altering buffer pools

Getting ready

How to do it...

How it works...

There's more...

Page size

Buffer pool size

Self-tuning

Partitions

Partition groups

See also

Dropping buffer pools

Getting ready

How to do it...

How it works...

There's more...

IBMDEFAULTBP

Dependencies

See also

6. Database Objects

Introduction

Creating and using MDC tables and block-based indexes

Getting ready

How to do it...

How it works...

There's more...

Block indexes and aggregate functions in queries

Block size and extents

Using runstats to determine dimension candidates

Restrictions on dimensions

Rollout

Partitioned database

See also

Creating and using materialized query tables

Getting ready

How to do it...

How it works...

There's more...

Refresh deferred

Maintained by system/user

Query rewrites

Combining with table partitioning

Replicated MQTs and database partitioning

See also

Implementing table partitioning

Getting ready

How to do it...

How it works...

There's more...

Scalability

Non-partitioned indexes

Partitioned indexes

Storage strategies

Adding a partition to a table

Detach a partition from a table

See also

Using temporary tables

Getting ready

How to do it...

How it works...

Created global temporary table

How to do it...

How it works...

There's more...

Restrictions

Recovery

See also

7. DB2 Backup and Recovery

Introduction

Configuring database logging

Getting ready

How to do it...

How it works...

There's more...

See also

Performing an offline database backup

Getting ready

How to do it...

How it works...

There's more...

Backup naming convention

Partitioned database

Recovery from offline backup

See also

Performing a full online database backup

Getting ready

How to do it...

How it works...

There's more...

Resource usage

Restrictions

Backup pending

See also

Performing an incremental delta database backup

Getting ready

How to do it...

How it works...

There's more...

Table space name change

Recovery history file

See also

Performing an incremental cumulative database backup

Getting ready

How to do it...

How it works...

There's more...

See also

Backing up table spaces

Getting ready

How to do it...

How it works...

There's more...

Operations incompatible with online table space backups

See also

Crash recovery

Getting ready

How to do it...

How it works...

There's more...

How crash recovery works to ensure database integrity

Inspect database

See also

Full database recovery

Getting ready

How to do it...

How it works...

There's more...

Attempting to recover from a backup of database in another logging mode

See also

Database rollforward recovery

Getting ready

How to do it...

How it works...

There's more...

Database state after restore

Point-in-time recovery on a partitioned database

History of restore operations

See also

Incremental restore

Getting ready

How to do it...

How it works...

There's more...

Archive log file not associated with the current log sequence

See also

Recovering table spaces—full and rollforward recovery

Getting ready

How to do it...

How it works...

There's more...

Backup image

Table space with system catalog

Partitioned database

Partitioned table

See also

Redirected restore

Getting ready

How to do it...

How it works...

There's more...

ABORT

See also

Recovery history file

Getting ready

How to do it...

How it works...

There's more...

Location

List history

PRUNE

Drop database

Restore database

Configuring tape-based backup with IBM Tivoli Storage Manager

Getting ready

How to do it...

How it works...

There's more...

Partitioned environment

See also

db2move and db2look utilities as alternative backup methods

Getting ready

How to do it...

How it works...

There's more...

Using db2move to load data

LOAD with care

See also

8. DB2 High Availability

Introduction

Setting up HADR by using the command line

Getting ready

How to do it...

Install IBM DB2 ESE on nodedb22

Creating additional directories for table space containers, archive logs, backup, and mirror logs

Setting permissions on the new directories

Configuring archive log and mirror log locations

Configuring LOGINDEXBUILD and INDEXREC parameters

Backing up the primary database

Copying the database backup to nodedb22

Restoring the database NAV on nodedb22

Setting up HADR communication ports

Setting up HADR parameters on the primary database

Setting up HADR parameters on the standby database

Starting HADR on standby database

Starting HADR on primary database

Monitoring HADR

How it works…

There's more…

The hadr_timeout and hadr_peer_window database configuration parameters

See also

Setting up HADR by using Control Center

Getting ready

How to do it...

How it works...

Changing HADR synchronization modes

Getting ready

How to do it...

Changing to NEARSYNC synchronization mode

Changing to SYNC synchronization mode

Changing back to ASYNC synchronization mode

How it works...

There's more...

Performing takeover and takeover by force

Getting ready

How to do it...

Using Control Center

Using the command line to perform a takeover

Using the command line to perform a takeover by force

How it works...

There's more...

Using automated client rerouting with HADR

Getting ready

How to do it...

How it works...

There's more...

Opening the standby database in read-only mode

Getting ready…

How to do it…

How it works...

There's more...

Using the DB2 fault monitor

Getting ready

How to do it...

How it works…

There's more...

9. Problem Determination, Event Sources, and Files

Introduction

Using db2mtrk—DB2 memory tracker

Getting ready

How to do it...

How it works...

There's more...

Using db2pd—DB2 problem determination tool

Getting ready

How to do it...

How it works...

There's more...

Using db2dart—DB2 database analysis and reporting tool command

Getting ready

How to do it...

How it works...

There's more...

Using db2ckbkp—DB2 check backup tool for backup integrity

Getting ready

How to do it...

How it works...

There's more...

Using db2support to collect diagnostic data

Getting ready

How to do it...

How it works...

There's more...

10. DB2 Security

Introduction

Managing instance-level authorities

Getting ready

How to do it...

How it works...

There's more...

Managing database-level authorities and privileges

Getting ready

How to do it...

How it works...

There's more...

Security administrator authority

Managing object privileges

Getting ready

How to do it...

How it works...

There's more…

Using roles

Getting ready

How it works...

There's more…

Creating roles using the WITH ADMIN OPTION

SECAD exception

Using table encryption

Getting ready

How to do it...

How it works...

There's more...

Using label-based access control (LBAC) to strengthen data privacy

Getting ready…

How to do it...

Creating security label components

Defining security policies

Creating security labels

Modifying read and write access rules by using exemptions

One more example using the ARRAY security component label

How it works...

There's more…

Auditing DB2

Getting ready

How to do it...

Configuring auditing scopes

Configure audit data path and archive path:

Archiving, formatting, and extracting the audit data:

Using audit policies

How it works...

There's more…

11. Connectivity and Networking

Introduction

Configuring network communications

Getting ready

How to do it...

Using Control Center

How it works...

There's more...

See also

Cataloging and uncataloging instances and databases

Getting ready

How to do it...

Using Control Center

How it works...

See also

There's more...

Using DB2 Discovery

Getting ready

How to do it...

How it works...

There's more...

Communications with DRDA servers (z/OS and i/OS)

Getting ready

How to do it...

How it works...

There's more...

Monitoring and configuring FCM for optimal performance

Getting ready

How to do it...

How it works...

There's more...

12. Monitoring

Introduction

Configuring and using system monitoring

Getting ready

How to do it...

How it works...

There's more...

Querying system information using table functions

Querying activity information using table functions

Querying data objects information using table functions

Workload management (WLM)

See also

Configuring and using snapshot monitoring

Getting ready

How to do it...

How it works...

There's more...

Table functions and administrative views

Database load

Buffer pool hit ratios

Buffer pool physical reads and writes per transaction

Average sorting time

Sorting time per transaction

Lock wait time

Deadlocks and lock timeouts

Rows read/rows selected

Dirty steal BP clean/transaction

Package cache inserts/transaction

Average log writes/transaction

See also

Configuring and using event monitoring

Getting ready

How to do it...

How it works...

There's more...

Unformatted event table

Using the db2evmonfmt tool for reporting

Table space use

Pruning event monitor tables

Resetting a monitor's counters

Workload management (WLM)

See also

Using Memory Visualizer

Getting ready

How to do it...

There's more...

Self tuning memory management

See also

Using Health Monitor

Getting ready

How to do it...

How it works...

There's more...

Getting recommendations for an alert

Using the command line to check and set configuration

See also

13. DB2 Tuning and Optimization

Introduction and general tuning guidelines

Operating system tuning

Getting ready

How to do it...

How it works...

There's more...

Virtual storage

Disabling file system caching on table spaces

Maintaining a historic record

See also

Resolving CPU bottlenecks

Getting ready

How to do it...

How it works...

There's more...

Utilities

Context switches

See also

Tuning memory utilization

Getting ready

How to do it...

How it works...

There's more...

AUTOCONFIGURE

Self-tuning memory in partitioned database environments

See also

Collecting object statistics with the RUNSTAT utility

How to do it...

How it works...

There's more...

Default automatic statistics collection

Collecting statistics using a statistics profile

Background stats collecting (asynchronous)

Real-time stats collecting (synchronous)

Setting priority

Automatic statistics profiling

Partitioned database

See also

Tuning with indexes

Getting ready

How to do it...

How it works...

There's more...

Sample output

Recommendations

See also

Tuning sorting

Getting ready

How to do it...

How it works...

There's more...

Sort overflows

Piped sorts

Coding practices

See also

Hit ratios and their role in performance improvement

Getting ready

How to do it...

How it works...

There's more...

Buffer pools

Catalog cache

Package cache

Log buffer

See also

I/O tuning

Getting ready

How to do it...

How it works...

There's more...

Logpath

Diagnostic logs on partitioned databases

Reorgchk

See also

Using logging and nologging modes

Getting ready

How to do it...

How it works...

There's more...

Catalog locks

Recovery

LOAD with non-recoverable option

High Availability Disaster Recovery (HADR)

See also

Using parallelism

Getting ready

How to do it...

How it works...

There's more...

RAID 5 and DB2_PARALLEL_IO

Loading a table

See also

Using EXPLAIN PLAN

Getting ready

How to do it...

How it works...

There's more...

Sample output

Watch out for...

Importing production statistics

See also

Creating a benchmark testing scenario

Getting ready

How to do it...

There's more...

Warm-up run

See also

14. IBM pureScale Technology and DB2

Introduction

Managing instances, members, and cluster facilities in DB2 pureScale

Getting ready

How to do it...

Creating and managing a DB2 pureScale instance

Managing DB2 pureScale members

Starting pureScale members

Managing the DB2 pureScale caching facilities

Stopping the caching facility

Starting pureScale cluster facilities

How it works…

There's more…

Put pureScale members in maintenance mode

Monitoring DB2 pureScale environments

Getting ready

How to do it…

Monitoring cluster facility memory structures

How it works…

There's more…

High availability in DB2 pureScale environments

Getting ready

How to do it…

How it works…

There's more…

Backup and recovery in DB2 pureScale environments

Getting ready

How to do it…

Performing an offline database backup

Performing an online database backup

Performing a restore and rollforward recovery

Performing database recovery

How it works…

There's more…

Index