DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI

396 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI

6.1 Introduction

Most IT environments today are complex infrastructures involving heterogeneous

network, hardware, and software components organized in multi-tier

configurations. Business applications share this complex IT infrastructure, which

is managed by IT professionals skilled in their particular domain of expertise, for

example, network administrators, operating system administrators, Web

application server administrators, and database administrators.

As mentioned earlier, users may experience performance problems for reasons

such as network connectivity and bandwidth constraints, system CPU, I/O and

memory constraints, software configuration limitations and constraints,

inadequate systems administration skills, poor application design, and faulty

assumptions about the workload. In this chapter, we address the following

related topics:

1.4, “Problem determination methodology” on page 7 discusses a general

problem determination methodology, and recommends a hypotheses validation

hierarchy that should typically be followed during problem diagnosis of DB2

applications in general.

For the DB2 application environment shown in Figure 6-1 on page 397, the

diagnosis process should sequentially eliminate the cause of the problem as

follows:

1. Network-related - between the client and the Web application server

2. Web application server-related - both system (CPU, I/O, memory) and various

configuration settings

3. Network-related - between the Web application server and the database

server

4. Database server-related - system (CPU, I/O, memory), configuration settings,

and routine DBA maintenance activities such as collecting statistics or

reorganizing tables

5. Application design-related - tables and SQL

Important: Following this sequence is strongly recommended in order to

ensure that the DBA does not expend needless effort on troubleshooting DB2,

when the root cause of the performance problem experienced by the user

potentially exists elsewhere. For example, network bandwidth constraints or

resource contention in the Web application server can manifest as erratic or

poor response times for a user of a DB2 application even when the DB2

system and application is perfectly tuned.

Chapter 6. Problem determination scenarios 397

Figure 6-1 A typical DB2 application environment and hypotheses hierarchy

Table 6-1 on page 398 provides a very high level overview of the resource

constraint conditions associated with the components that the application utilizes

Note: Depending upon the triggering event of the performance problem, it

may be possible to skip certain hypotheses validation altogether. For

example, when an explicit alert about a lock escalation threshold being tripped

is the triggering event, you can ignore hypotheses such as network

connectivity and bandwidth constraints, Web application server constraints,

and CPU and I/O constraints (in both the Web application server and the DB2

database server) as a potential cause of the problem.

Web Application

Server (WAS)

Network

Network

DB2

Database Server

Local

Clients

Remote

Clients

Connectivity

Bandwidth

System CPU

System I/O

System Memory

DB2 related

Hypotheses

Hierarchy

Connectivity

Bandwidth

System CPU

System I/O

System Memory

WAS related

398 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI

in the execution of its functions, along with tools that can be used to investigate

them.

Table 6-1 Typical problem areas associated with DB2 app. performance

For a description of AIX tools and examples of their output, see 5.2.4, “Monitoring

and problem determination tools” on page 363. For Windows tools, see 5.3.4,

“Monitoring and problem determination tools” on page 387. For details on

WebSphere Application Server, refer to the IBM Redbook DB2 UDB/WebSphere

Performance Tuning Guide, SG24-6417.

We focus on DB2-related resource constraint conditions in the following section,

and recommend the hypotheses hierarchy to adopt for problem diagnosis.

Component Resource constraint conditions Tools

Network 򐂰 Connectivity

򐂰 Bandwidth

ping, connect, db2 ping, ftp

System 򐂰 CPU utilization

򐂰 I/O utilization and performance

򐂰 Memory paging

򐂰 vmstat, nmon, uptime,

ruptime

򐂰 iostat, nmon

򐂰 vmstat,nmon,svmon,ipcs

Web

application

server

򐂰 System resource constraints

򐂰 Connections to DB2

򐂰 Configuration parameters

򐂰 as above

򐂰 Resource Analyzer

򐂰 WebSphere tools

DB2

database

server

򐂰 System resource constraints

򐂰 DB2 resource constraints

򐂰 DB2 application design

򐂰 as above

򐂰 DB2 commands & tools

Important: In most cases, the DBA has no jurisdiction over monitoring and

tuning network, system, and Web application server performance drivers,

since they are the responsibility of the appropriate administrator.

The objective here is to make DBAs aware of the critical impact on the

performance of their DB2 environment by the various network, operating

system and Web application server performance drivers, so that they may be

in a position to negotiate more effectively with the appropriate administrators

responsible for these performance drivers.

Note: We used DB2 UDB Version 8.1.1 in all the scenarios discussed in this

section.