Performance Tuning for Content Manager

Chapter 2. Content Manager base products 51

Locking

In a database system, typically many applications concurrently work on the same

data. If this is done in an uncontrolled way, this might lead to anomalies that are

known as:

 Lost update

 Uncommitted read

 Non-repeatable read

 Phantom read

It is the responsibility of the database manager to provide protection against these

anomalies to applications. The database manager does this by maintaining locks

on individual database objects such as rows in a table, index entries, or even

whole tables. If a database lock is held on an object by an application, the

database manager takes care that no other application accesses this element in a

way such that one of the above anomalies shows up. This is done by suspending

one of the applications from further execution, which in turn affects transaction

throughput and visible performance. To minimize this impact, applications can

specify the degree of protection that they require against interference with

concurrent applications. This is done using the concept of

isolation levels

.

2.1.4 Routine maintenance

Routine maintenance of your system is essential in maintaining optimum

performance. In this section, we provide an overview of what should be included

as part of routine maintenance (ideally performed during a scheduled

maintenance window during which there is little to no user access to the

database) as well as how often it should be scheduled. Your schedule can be

more or less frequent depending on the amount of changes within your system.

Key points:

 Locking is a concept to protect concurrent applications from interfering with

each other and to protect consistency of data.

 The number and nature of objects that are locked by concurrent

applications generally affects performance in terms of transaction

throughput.

 Applications have a certain influence on the number and nature of locks

that the database manager holds for them, but otherwise, locking is done

implicitly under control of the database manager.

 The database manager uses certain configurable memory pools to store

locks.

 Configuration of these memory pools affects the general locking behavior.

52 Performance Tuning for Content Manager

See Chapter 8, “Tuning DB2 for Content Manager” on page 171 for details about

routine maintenance, including applicable commands.

The following routine maintenance are important:

 Keeping up with the latest software fix packs

 Monitoring system

 Cataloging statistics (runstats and rebind)

 Reorganization tables

 Pruning the log file

 Pruning the diagnostics file

Keeping up with the latest software fix packs

Software bugs are found or reported by customers and new enhancements are

continually being developed for software. DB2 is no exception. It is extremely

important to keep your software up to the latest fix pack level so that you get the

benefit of these software changes. When calls to external support are placed,

one of the first things asked is whether your software is at the latest fix pack level.

If not, you will be asked to update it before they will continue to troubleshoot your

problem so that they do not try to solve a problem that has already been solved.

Monitoring system

Monitoring your system on a regular basis is vital to ensuring that the system is

running in a healthy and performance-oriented way. The database monitor

switches must be turned on for sufficient time to enable DB2 to collect the data.

We recommend regularly taking a DB2 snapshot of the database manager

(instance) and each database, and that you keep the snapshot data in a file, a

spreadsheet, or loaded into a DB2 table for historical and trend analysis. Analyze

the results for immediate issues such as a high number of package cache

overflows or database files closed. Perform a trend analysis to determine

whether a problem is building. Is the buffer pool hit ratio decreasing? Are the sort

overflows steadily increasing? If so, take appropriate corrective action.

Cataloging statistics (runstats and rebind)

Statistical data stored in the system catalogs helps the optimizer choose the best

access plan for queries. Executing the runstats command to update this

statistical data has a tremendous impact on performance and should be one of

the first things done (in conjunction with a rebind command) whenever system

performance is perceived to be poor.

We recommend executing runstats and rebind commands:

 At frequent regular intervals for tables whose content changes continually.

This might be daily or weekly.