Performance Tuning for Content Manager

296 Performance Tuning for Content Manager

different tools can be used for this purpose. In this section, we focus on the tools

that either come with the DB2 database system, are members of the DB2 family

of products, or are part of the underlying operating system.

Because of the wide variety of tools that are available and the diversity of data to

collect, we typically stay at a generic level of description and present screen

captures to demonstrate the nature of the data collected. We do not go into detail

about how to use these tools, and instead refer to documentation that can be

studied for details.

Much of the performance-related data may be collected by more than just one

monitoring tool, so we start with an overview of the individual techniques and

tools for monitoring, and close with a summary of the tools that can be used to

monitor the various performance-related concepts.

11.4.1 Basic DB2 monitoring, logging, and tracing interfaces

The retrievable performance-related data in DB2 relies heavily on mechanisms

and interfaces that DB2 provides to access this data. DB2 keeps track of its own

operation, performance, and the applications using it.

You can access this information using two built-in DB2 monitoring interfaces:

 Snapshot monitoring

 Event monitoring

Each interface provides a slightly different view of the data and differs from the

other in the way data is accessed. In this section, we give an overview of these

interfaces and describe what to consider when deciding which interface to use for

your purposes.

DB2 also provides logging and tracing interfaces. You can configure how much

information you want DB2 to provide. These files are useful for troubleshooting.

You can also find valuable hints for tracking down performance problems. From

the performance aspect, we discuss:

 DB2 diagnostic file: db2diag.log

 DB2 CLI and JDBC trace

Snapshot monitoring

As the name already suggests, you can use the snapshot monitor to capture

information about the database and connected applications

at a specific time.

Snapshots are useful for determining the status of a database system. Taken at

regular intervals, they are also useful for observing trends and foreseeing

potential problems.

Chapter 11. Performance monitoring, analysis, and tracing 297

The data that can be captured using snapshot monitoring is structured in

categories (also known as types of monitoring elements in DB2 documentation).

Because snapshot monitoring implies a certain overhead in database manager

processing, snapshot monitoring is not activated by default for most of these

categories. Snapshot monitoring for each category can be toggled on and off

individually (Table 11-1).

Table 11-1 DB2 snapshot categories

Before capturing a snapshot or using an event monitor, determine what data you

need the database manager to gather and set only the corresponding switches.

The following DB2 command can be used to toggle on all switches:

UPDATE MONITOR SWITCHES USING BUFFERPOOL ON LOCK ON SORT ON STATEMENT

ON TIMESTAMP ON TABLE ON UOW ON

To check the status of snapshot monitoring switches, use this DB2 command:

GET MONITOR SWITCHES

When using snapshot monitoring, consider these facts:

 Snapshot monitoring consumes some system resources and can affect

general system performance. We recommend switching it on

if and only if

monitoring actually is done.

 The database manager uses some internal memory heap to store snapshot

data. The size of this heap is always limited and can be configured using the

database manager configuration parameter MON_HEAP_SZ. The amount of

data covered by some snapshot is determined by the load on the database

system as well as by the amount of memory that you allow the database

manager to use for monitoring. You might want to increase the default setting

before starting to activate a snapshot monitor.

Snapshot category Switch Default

Buffer pool activity information BUFFERPOOL OFF

Lock wait, and time-related lock information LOCK OFF

Sorting information SORT OFF

SQL statement information STATEMENT OFF

Table activity information TABLE OFF

Times and timestamp information TIMESTAMP ON

Unit of work information UOW OFF