SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries

180 SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries

7.1 Collection Services and Database Monitor data

Collection Services allows you to gather performance data with little or no observable impact

on system performance. Its data is analyzed using the IBM Performance Tools for iSeries

licensed program (5722PT1) or other performance report applications, iSeries Navigator

monitors, and the graph history function. If you prefer to view real-time performance data,

system monitors provide an easy-to-use graphical interface for monitoring system

performance. For more information about iSeries Navigator Monitors, see “iSeries Navigator

monitors” in iSeries Performance Version 5 Release 3 on the Web at:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/rzahx/rzahx.pdf

Collection Services collects data that identifies the relative amount of system resource used

by different areas of your system. While you are required to use Performance Tools for

iSeries, it is not required to gather the data.

In most of the analysis when looking for job-level information in this chapter, we use the

Component Report. The Component Report uses Collection Services data to provide

information about the same components of system performance as a System Report, but at a

greater level of detail. The Component Report helps you find the jobs that are consuming high

amounts of system resources, such as CPU, disk, and so on.

7.1.1 Starting Collection Services

To analyze Collection Services data, you must first start Collection Services, which you can

do in one of the following ways:

򐂰 From the PERFORM menu, using GO PERFORM

򐂰 From iSeries Navigator

򐂰 Using Performance Management APIs

򐂰 In V5R3, using the Start Performance Collection (STRPFRCOL) CL command

In the following sections, we explain the details for each of the methods to start Collection

Services to gather data.

Note: While running Collection Services, you should also run Database Monitor on all the

jobs. After we identify the job information of the job or jobs that consume high amounts of

system resources, such as CPU, disk and so on, we can the query the Database Monitor

data to find the jobs as explained in Chapter 6, “Querying the performance data of the

Database Monitor” on page 133.

Note: Starting Collection Services from the PERFORM menu or from iSeries Navigator

requires that you install Performance Tools for iSeries, 5722PT1.