DB2 UDB ESE V8 non-DPF performance guide for high performance OLTP and BI / [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.

Transaction systems (Computer systems).

Online data processing.

IBM Database 2.

Physical Description:

xxiv, 504 p. : ill.

Edition:

1st ed.

Other Title:

Database two Universal Database Enterprise Server Edition version eight non-Database Partitioning Feature performance guide for high performance OLTP and BI

Place of Publication:

[S.l.] : IBM, International Technical Support Organization, c2004.

Language Note:

English

System Details:

text file

Summary:

This IBM Redbooks publication provides detailed information on implementing high performance OLTP and BI applications in DB2 UDB ESE V8 environments involving AIX and Windows 2000 platforms. It is aimed at a target audience of DB2 application developers and database administrators (DBAs). We provide an overview of the architecture of a DB2 UDB V8 non-DPF environment from a performance perspective, and describe key performance drivers in OLTP, BI, and mixed workload environments. This book’s primary focus is a single partition (non-DPF) environment, and we provide best practices to achieve optimal application and system performance in OLTP, BI, and mixed workload environments. Finally, we discuss some of the commonly encountered problems faced by a DBA when managing a DB2 environment, and describe techniques for problem diagnosis using typical problem scenarios.

Contents:

Front cover

Contents

Figures

Tables

Examples

Notices

Trademarks

Preface

The team that wrote this redbook

Become a published author

Comments welcome

Chapter 1. Introduction to performance management

1.1 Introduction

1.2 Performance management

1.3 Types of monitoring

1.3.1 Routine monitoring

1.3.2 Online/realtime event monitoring

1.3.3 Exception monitoring

1.4 Problem determination methodology

Chapter 2. DB2 UDB architecture overview

2.1 Introduction

2.2 Main components of DB2

2.2.1 High level overview of main DB2 components

2.2.2 High level overview of DB2 architecture and processes

2.2.3 Process model

2.2.4 Memory model

2.3 Single user transaction/query flow

2.3.1 Transaction flow with connection concentrator enabled

2.3.2 Transaction flow with connection concentrator disabled

2.4 Multi-user (concurrent) transaction/query flow

2.5 Key performance knobs

2.5.1 Configuration Advisor and AUTOCONFIGURE

2.5.2 Database Manager (DBM) configuration parameters

2.5.3 Database (DB) configuration parameters

2.5.4 DB2 registry and environment variables

2.6 Performance monitoring facilities

2.6.1 CLI/ODBC/JDBC trace

2.6.2 Database System Monitor

2.6.3 DB2 administration notification log

2.6.4 db2batch

2.6.5 db2diag.log

2.6.6 DB2 Performance Expert

2.6.7 Design Advisor

2.6.8 Explain and Visual Explain

2.6.9 Heath Monitor and Health Center

2.6.10 Memory Tracker

2.6.11 Memory Visualizer

Chapter 3. Application design and system performance considerations

3.1 OLTP and BI characteristics

3.1.1 OLTP characteristics

3.1.2 BI characteristics

3.2 Key performance drivers

3.3 Application design considerations

3.3.1 Table design

3.3.2 MDC design considerations

3.3.3 MQT/AST design considerations.

3.3.4 Index design

3.3.5 Table space design

3.3.6 Writing efficient SQL

3.3.7 Concurrency

3.4 System environment considerations

3.4.1 I/O placement considerations

3.4.2 Log considerations

3.4.3 Monitor switch settings

3.4.4 Connection considerations

3.4.5 Buffer pool considerations

3.4.6 Locking considerations

3.4.7 Package cache considerations

3.4.8 Catalog cache considerations

3.4.9 Sort considerations

3.4.10 Other memory considerations

3.4.11 Miscellaneous considerations

Chapter 4. Command and utility considerations

4.1 Introduction

4.2 Backup

4.2.1 Brief description

4.2.2 Performance considerations

4.2.3 Best practices

4.3 Export

4.3.1 Brief description

4.3.2 Performance considerations

4.3.3 Best practices

4.4 Import

4.4.1 Brief description

4.4.2 Performance considerations

4.4.3 Best practices

4.5 Load

4.5.1 Brief description

4.5.2 Performance considerations

4.5.3 Best practices

4.6 Reorg

4.6.1 Brief description

4.6.2 Performance considerations

4.6.3 Best practices

4.7 Restore

4.7.1 Brief description

4.7.2 Performance considerations

4.7.3 Best practices

4.8 Runstats

4.8.1 Brief description

4.8.2 Performance considerations

4.8.3 Best practices

Chapter 5. Operating system considerations

5.1 Introduction

5.2 AIX platform

5.2.1 Operating system considerations

5.2.2 Memory considerations

5.2.3 Disk and filesystem considerations

5.2.4 Monitoring and problem determination tools

5.3 Windows platform

5.3.1 Operating system considerations

5.3.2 Memory considerations

5.3.3 Disk and filesystem considerations

5.3.4 Monitoring and problem determination tools

Chapter 6. Problem determination scenarios

6.1 Introduction

6.2 DB2 hypotheses hierarchy.

6.2.1 DB2 database server system resource constraints

6.2.2 DB2 system resource constraints

6.3 Exception event scenarios

6.3.1 Lock waits due to default LOCKTIMEOUT value (OLTP)

6.3.2 Poor SQL performance due to missing indexes (OLTP)

6.3.3 Poor SQL performance due to unused MQTs (BI)

6.4 Routine monitoring scenarios

6.4.1 Deteriorating space utilization conditions (BI)

6.4.2 Deteriorating buffer pool hit ratios (OLTP)

6.5 Online/Realtime monitoring scenarios

6.5.1 Lock contention (OLTP)

Appendix A. DB2 UDB ESE Version 8 performance enhancements

A.1 Introduction

A.2 Application-related performance enhancements

A.2.1 Multidimensional clustering

A.2.2 MQT enhancements

A.2.3 Compression of NULLS and DEFAULT

A.2.4 Load enhancements

A.3 System-related performance enhancements

A.3.1 Prefetching enhancements

A.3.2 Faster page cleaners

A.3.3 Connection concentrator

A.3.4 Type 2 indexes

A.3.5 Stored procedures and UDFs thread-based model

A.3.6 DMS container enhancements

A.3.7 RUNSTATS enhancements

A.3.8 Logging enhancements

A.3.9 Manageability enhancements

A.4 DB2 UDB Version 8.1.4

A.4.1 Backup compression

A.4.2 Range-clustered tables

A.4.3 Direct I/O support on AIX

A.4.4 Asymmetric index splitting

A.4.5 Buffer pool memory allocation

A.4.6 Page cleaning enhancements

A.4.7 Lock deferral

A.4.8 Improved sort performance

Appendix B. Workloads used in the scenarios

B.1 Introduction

B.2 DTW workload

B.3 EBIZ database

B.4 Trade2 database and application

B.5 WebSphere Performance Tools (WPT)

Related publications

IBM Redbooks

Other publications

Online resources

How to get IBM Redbooks

Help from IBM

Index

Back cover.

Notes:

"April 2004."

Includes bibliographical references and index.

OCLC:

62771955