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
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
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)
Database Server
System CPU
System I/O
System Memory
DB2 related
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
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,
򐂰 iostat, nmon
򐂰 vmstat,nmon,svmon,ipcs
򐂰 System resource constraints
򐂰 Connections to DB2
򐂰 Configuration parameters
򐂰 as above
򐂰 Resource Analyzer
򐂰 WebSphere tools
򐂰 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

Get DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.