IBM DB2 9.7 Advanced Administration Cookbook

IBM DB2 9.7 Advanced Administration Cookbook

Overview of this book

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.

IBM DB2 9.7 Advanced Administration Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

DB2 Instance—Administration and Configuration

Introduction

Creating and configuring instances for non-partitioned environments

Creating and configuring a client instance

Creating and configuring an instance for multipartitioned environments

Starting and stopping instances

Configuring SSL for client-server instance communication

Listing and attaching to instances

Dropping instances

Administration and Configuration of the DB2 Non-partitioned Database

Introduction

Creating and configuring DB2 non-partitioned databases

Using Configuration Advisor

Creating a database from an existing backup

Configuring automatic database maintenance

Managing federated databases—connecting to Oracle and MSSQL

Altering databases

Dropping databases

DB2 Multipartitioned Databases—Administration and Configuration

Introduction

Creating and configuring a multipartitioned database

Adding database partitions

Creating database partition groups

Altering database partition groups—adding partitions to database partition groups

Managing data redistribution on database partition groups

The table distribution key and its role in a multipartitioned environment

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

Removing database partitions

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

Configuring Fast Communication Manager

Storage—Using DB2 Table Spaces

Introduction

Creating and configuring table spaces within automatic storage databases

Creating and configuring SMS table spaces

Creating and configuring DMS table spaces

Using system temporary table spaces

Using user temporary table spaces

Altering table spaces and dropping table spaces

Table spaces in a multipartitioned environment

DB2 Buffer Pools

Introduction

Creating and configuring buffer pools

Configuring the block-based area

Managing buffer pools in a multipartitioned database

Altering buffer pools

Dropping buffer pools

Database Objects

Introduction

Creating and using MDC tables and block-based indexes

Creating and using materialized query tables

Implementing table partitioning

Using temporary tables

Created global temporary table

DB2 Backup and Recovery

Introduction

Configuring database logging

Performing an offline database backup

Performing a full online database backup

Performing an incremental delta database backup

Performing an incremental cumulative database backup

Backing up table spaces

Crash recovery

Full database recovery

Database rollforward recovery

Incremental restore

Recovering table spaces—full and rollforward recovery

Redirected restore

Recovery history file

Configuring tape-based backup with IBM Tivoli Storage Manager

db2move and db2look utilities as alternative backup methods

DB2 High Availability

Introduction

Setting up HADR by using the command line

Setting up HADR by using Control Center

Changing HADR synchronization modes

Performing takeover and takeover by force

Using automated client rerouting with HADR

Opening the standby database in read-only mode

Using the DB2 fault monitor

Problem Determination, Event Sources, and Files

Introduction

Using db2mtrk—DB2 memory tracker

Using db2pd—DB2 problem determination tool

Using db2dart—DB2 database analysis and reporting tool command

Using db2ckbkp—DB2 check backup tool for backup integrity

Using db2support to collect diagnostic data

DB2 Security

Introduction

Managing instance-level authorities

Managing database-level authorities and privileges

Managing object privileges

Using roles

Using table encryption

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

Auditing DB2

Connectivity and Networking

Introduction

Configuring network communications

Cataloging and uncataloging instances and databases

Using DB2 Discovery

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

Monitoring and configuring FCM for optimal performance

Monitoring

Introduction

Configuring and using system monitoring

Configuring and using snapshot monitoring

Configuring and using event monitoring

Using Memory Visualizer

Using Health Monitor

DB2 Tuning and Optimization

Introduction and general tuning guidelines

Operating system tuning

Resolving CPU bottlenecks

Tuning memory utilization

Collecting object statistics with the RUNSTAT utility

Default automatic statistics collection

Tuning with indexes

Tuning sorting

Hit ratios and their role in performance improvement

I/O tuning

Using logging and nologging modes

Using parallelism

Loading a table

Using EXPLAIN PLAN

Creating a benchmark testing scenario

IBM pureScale Technology and DB2

Introduction

Managing instances, members, and cluster facilities in DB2 pureScale

Monitoring DB2 pureScale environments

High availability in DB2 pureScale environments

Backup and recovery in DB2 pureScale environments

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Tuning sorting

Sorting occurs at two levels in the database. One is shared sort memory, located in the database global memory, and the other is at the agent level, called the private sort memory.

Getting ready

Monitor sorting activity on your database, as discussed in the previous chapter, by using snapshots, and compare sorting activity with the load on your database.

See how much sorting is done throughout a normal day's activity. In the following SQL, you can even identify the partition's sorting activity. You will see if there is enough sorting activity to make tuning worthwhile.

How to do it...

Average sort time: If average sort time per sort is long, we can start thinking about tuning.
```
SELECT DBPARTITIONNUM, TOTAL_SORT_TIME / (TOTAL_SORTS + 1)
FROM   SYSIBMADM.SNAPDB;
```
Sort time per transaction: If the value of SortsPerTransaction is greater than five, there might be too many sorts per transaction:
```
SELECT DBPARTITIONNUM, TOTAL_SORTS / ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS + 1)
FROM ...
```

IBM DB2 9.7 Advanced Administration Cookbook

IBM DB2 9.7 Advanced Administration Cookbook

Overview of this book

Related Content you might be interested in

Current Title:

IBM DB2 9.7 Advanced Administration Cookbook

Tuning sorting

Getting ready

How to do it...