Monitoring and Tuning Database Performance

Why You Need to Monitor
Why You Need to Monitor
Eventually, a database may begin to outgrow its initial allocation of space, with resulting increased I/O and poor response time. If you don't monitor your databases on a regular basis, these conditions can become critical, forcing you to take emergency actions at an inconvenient time.
Suggested Monitoring Schedule
Consider using the following schedule as the basis for monitoring database performance:
Monitoring tool
Monitoring frequency
Information provided
Summary information on the database processing activities for each program recorded in the journal file
IDMSDBAN report 2
Area detail statistics, such as number of logically full pages and number of relocated records
IDMSDBAN report 5
Set detail statistics, such as the number of pages needed to store a chained set
Area space utilization statistics
IDMSDBAN (all reports)
Monthly or as needed
Set statistics, including broken chains, record data, and area data
Keep a history of meaningful statistics so that you can identify abnormal conditions when they arise.
SQL Considerations
Most of the information in this section applies to both SQL and non-SQL defined databases. Text that applies to only one or the other will be noted. In addition, much of the section applies to the physical structures that underlie the database definition. Therefore, one set of terms will be used for these physical entities. For example, chain sets are the physical structure used to implement both SQL linked constraints and non-SQL sets defined with the MODE IS CHAIN clause.