Administering IBM Db2 database

As the database administrator, you must set up and maintain your IBM® Db2® database to store all of your system data and ensure that you have configured the database for optimal performance.

Db2 database performance

You can configure the following aspects of Db2 to improve the database performance:
Applying the latest fix packs
Apply the latest Db2 fix packs to the Db2 database on the Db2 server and the Db2 client on the application server because they might contain some bug fixes and performance enhancements. You can download the latest Db2 fix pack from the Download Db2 Fix Packs.
Note: The database and client must be at the same fix pack level to avoid any conflicting database version issues.
Tuning the operating system
Tuning the operating system configuration in the areas of memory utilization and file caching can maximize the performance of the Db2 database.
Tuning the storage for performance
You can tune the storage to be able to store and retrieve data quickly and efficiently. You must understand the advantages and disadvantages of choosing a particular data placement strategy to achieve this goal. The placement of data can directly affect the performance of the Product Master system that uses the database. The overall goal is to spread the database effectively across as many disks as possible to try to minimize I/O wait.
Tuning the database manager and the database configuration
You can configure parameters in Db2 both at the instance level and the database level.

Db2 connection parameters

To optimize the number of database connections to your Db2 database, determine the number of required connections to the database and add 10 more connections to that number to act as your buffer connections and account for any Db2 background processes.

Db2 table spaces

Data that is entered into Product Master gets stored in the underlying database and is organized in different table spaces in the database. Because large amounts of data transactions occur, you must ensure that there is enough free space in all the table spaces of the database. You can further improve the performance and availability of the database by monitoring the free space available to table spaces and by using temporary tables.

Configuration parameters for Db2 databases

To ensure optimal performance of your database, you can configure database manager parameters and database configuration parameters.

Db2 server optimization

You can optimize the database server for better response time, output, and availability of the server. To ensure optimal performance, you configure your database lock wait, memory, and buffer pool parameters to meet the system requirements.
You can maintain optimal performance of a Db2 database in the following ways:
  • Use sufficient agents for the workload.
  • Restrict Db2 from closing and opening unnecessary files.
  • Prevent extended lock waits.
  • Manage Db2 sort memory conservatively and do not mask sort problems with large values SORTHEAP.
  • Analyze table access activity and identify tables with unusually high rows that are read per transaction or overflow counts.
  • Analyze the performance characteristics of each table space to improve the performance of the table spaces with the slowest read times, longest write times, highest physical I/O read rates, worst press ratios, and access attributes that are inconsistent with expectations.
  • Create multiple buffer pools, and make assignments of table spaces to buffer pools such that access attributes are shared.
  • Examine Db2 SQL statement Event Business Monitor information to discover which SQL statements are consuming the largest proportions of computing resources, and take corrective actions.
  • Reevaluate configuration and physical design settings after high-cost SQL is eliminated.

Db2 database health check

You must check the health of the database systems at regular intervals to ensure high availability of the systems. You can use the Db2 Health Center to monitor the state of a set of health indicators by defining the warning and threshold values for the indicators. If the current value of a health indicator crosses the acceptable operating range that is defined by its warning and threshold values, the health monitor generates a health alert.

For example, you can monitor the time that is taken to run database queries. To do so, you can define the time limits within which the queries must be run. If any query needs more time to be run, the health monitor sends an alert. You can also configure the alert settings so that the alert reaches specific users. Db2 has a set of predefined values for the health monitors. You can use the Health Center to customize these values as required.

You can perform the following tasks by using the Db2 Health Center:
  • View the status of the database environment.
  • View alerts for an instance or a database.
  • View detailed alert information and the recommended actions.
  • Configure health monitor settings for an object.
  • Set the recipients for alerts.
  • Review alert history for an instance.