DB2 II : performance monitoring, tuning, and capacity planning guide / [Nagraj Alur ... et al.].

Format:

Book

Contributor:

Alur, Nagraj.

International Business Machines Corporation. International Technical Support Organization.

Series:

IBM redbooks.

IBM redbooks

Language:

English

Subjects (All):

Database management.

Computer capacity--Planning.

Computer capacity.

IBM Database 2.

Physical Description:

xx, 640 p. : ill.

Edition:

1st ed.

Other Title:

Database Two Information Integrator

Place of Publication:

San Jose, CA : IBM, International Technical Support Organization, c2004.

Language Note:

English

System Details:

text file

Summary:

This IBM Redbooks publication provides an overview of DB2 Information Integrator V8.2 key performance drivers; best practices to achieve optimal performance; and guidelines for monitoring a DB2 Information Integrator environment for capacity planning, problem diagnosis, and problem resolution. This publication documents procedures for monitoring existing DB2 II implementations for the purposes of capacity planning. It also documents a methodology for routine and exception monitoring of a DB2 II environment for performance problem determination; and describes some commonly encountered performance problem scenarios and the step-by-step approach used in problem determination and resolution.

Contents:

Front cover

Contents

Figures

Tables

Examples

Notices

Trademarks

Preface

The team that wrote this redbook

Become a published author

Comments welcome

Chapter 1. DB2 Information Integrator architecture overview

1.1 Introduction

1.2 Current business trends

1.2.1 From on demand to grid computing

1.2.2 From grid computing to data federation

1.2.3 From data federation to information integration

1.3 IBM's DB2 Information Integration overview

1.3.1 Data consolidation or placement

1.3.2 Distributed access (federation)

1.3.3 DB2 Information Integrator products

1.4 DB2 Information Integrator V8.2

1.4.1 DB2 II V8.2 overview

1.4.2 DB2 II components

1.4.3 Configuring the federated system

1.4.4 Performance considerations

1.5 DB2 Information Integrator topology considerations

1.5.1 Dedicated federated server

1.5.2 Collocated federated server

Chapter 2. Introduction to performance management

2.1 Introduction

2.2 Performance management

2.3 Types of monitoring

2.3.1 Routine monitoring

2.3.2 Online/realtime event monitoring

2.3.3 Exception monitoring

2.4 Problem determination methodology

Chapter 3. Key performance drivers of DB2 II V8.2

3.1 Introduction

3.2 Compilation flow of a federated query

3.3 Execution flow of a federated query

3.4 Key performance drivers

3.4.1 Performance factors

3.4.2 Federated server considerations

3.4.3 Data source considerations

3.4.4 Efficient SQL queries

3.4.5 Hardware and network

Chapter 4. Performance problem determination scenarios

4.1 Introduction

4.2 DB2 II hypotheses hierarchy

4.2.1 DB2 II federated database server resource constraints

4.2.2 DB2 II resource constraints

4.2.3 Federated server or remote data source

4.2.4 Federated server related.

4.2.5 Remote data source related

4.3 Monitoring best practices

4.3.1 Performance considerations

4.3.2 Best practices

4.4 Problem scenarios

4.4.1 Federated test environment

4.4.2 Missing or incorrect statistics/index information

4.4.3 Poorly tuned sort heap and buffer pools

4.4.4 Missing or unavailable MQTs

4.4.5 Incompatible data types on join columns

4.4.6 Pushdown problems

4.4.7 Default DB2_FENCED wrapper option with DPF

Chapter 5. Capacity planning in an existing DB2 II environment

5.1 Introduction

5.2 Capacity planning assumptions

5.3 Capacity planning procedure

5.3.1 Capacity planning procedure overview

5.4 Capacity planning new applications

5.4.1 Model of different profiles of queries

5.4.2 Determine new application workload

5.4.3 Estimate capacity for the new application

Appendix A. DB2 II V8.2 performance enhancements

Introduction

Fenced wrappers

Parallelism enhancements

Intra-partition parallelism in a non-DPF environment

Inter-partition parallelism in a DPF environment with local data

Inter-partition parallelism in a DPF environment without local data

Updating nickname statistics

Cache tables

Informational constraints

Snapshot monitor support

Health Center alerts

Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator

Brief review of the DB2 EXPLAIN facility

db2exfmt overview

EXPLAIN INSTANCE section

SQL STATEMENT section

Access plan graph

OPERATOR DETAILS section

Objects section

Complete db2exfmt output

Federated test environment

db2exfmt examples involving DB2 II

Join of nicknames referencing Oracle and SQL server

INTRA_PARALLEL = YES (intra-partition enabled)

Database Partition Feature (DPF) with FENCED = 'N'

Database Partition Feature (DPF) with FENCED = 'Y'

DB2_MAXIMAL_PUSHDOWN = 'N'.

DB2_MAXIMAL_PUSHDOWN = 'Y'

SQL INSERT/UPDATE/DELETE

Related publications

IBM Redbooks

Other publications

Online resources

How to get IBM Redbooks

Help from IBM

Index

Back cover.

Notes:

"November 2004."

"SG24-7073-00."

Includes bibliographical references and index.

OCLC:

61452918