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 with indexes

You will likely get requests to evaluate SQL queries and suggest indexes for best performance. Let's see together how it's done. You can use the GUI for this case, so we'll be doing it on the command line.

We will use DB2's db2advis utility. There are many ways you can use it. In our case, we have SQL commands with recommendations for indexes.

Getting ready

Now, before you can perform any EXPLAIN PLAN or performance analysis, you need to have fresh statistics on tables and indexes or reasonably recent ones; otherwise, the results may not correspond to your expectations.

How to do it...

If you're not connecting as instance owner, you will need a user with create table authority to run the EXPLAIN.DDL utility.

As instance owner, grant access to the database to the user who will perform SQL evaluations. That user should have profile values set and can execute DB2 from the shell or Windows command line:
```
db2 connect to uatdb
db2 grant createtab, connect on database to user rpellet...
```

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 with indexes

Getting ready

How to do it...