DB2 physical database design for the OLTP environment: Database Operations and Maintenance

After the database system enters the production environment, the focus will be on the routine maintenance of the database system. Daily operations include performance management, problem diagnosis, and maintenance. They must continue to meet service level agreements. The physical database design for the OLTP environment should include a schedule for operation and maintenance tasks. This section provides a summary of such activities. Recovery Plan

After the database system enters the production environment, the focus will be on the routine maintenance of the database system. Daily operations include performance management, problem diagnosis, and maintenance. They must continue to meet service level agreements. The physical database design for the OLTP environment should include a schedule for operation and maintenance tasks. This section provides a summary of such activities. Recovery Plan

After the system enters the production environment, the focus will be on the daily maintenance of the system. Daily operations include performance management, problem diagnosis, and maintenance. They must continue to meet service level agreements. The physical database design for the OLTP environment should include a schedule for operation and maintenance tasks. This section provides a summary of such activities.

Recovery Policy

As part of the overall RAS policy, the recovery policy plays an important role in meeting your RAS goals. Although redundancy exists in many layers, it is important to understand business requirements when defining recovery point objective (RPO) and recovery time objective (RTO.

RPO defines the acceptable amount of data loss. RTO defines the maximum time for recovery from disasters (such as disk faults, hardware faults, or operation errors. Log Files, backup image hold, and how many copies of log files and backup images are retained, which are closely linked to RTO and RPO.

Store two or more log files and copies of the backup image for your key business data. For backup images, in addition to retaining multiple copies of the same backup image, multiple generations of images should also be retained. Use a multi-generation backup image to restore data at a time point before the last backup. If RTO is defined in units of hours, please restore the database backup and roll the log forward. This may be enough. However, if RTO is defined in seconds or minutes, you must use high availability software such as HADR.

Creating a recovery policy is just the beginning. Test a policy. Exercise your disaster recovery plan on a regular basis. The frequency depends on the key nature of the business application. The database continues to grow over time. Any RPO or RTO goals that can be met in the last drill may not be met today. Adjust physical design aspects (such as faster I/O devices) to restore and disperse these devices across more spindle. The utility provided by the DB2 database product helps you design recovery policies. Utilities include online backup and snapshot backup.

You can perform online backup when the database is available. They have the lowest vigilance and are intended to run simultaneously with other client activities. Snapshot Backup features use the fast replication technology of storage devices to achieve almost real-time backup. This feature requires support from the operating system or storage system.

Maintenance window

In order for the database system to run efficiently, regular maintenance operations (including updating statistics, reorganizing tables and indexes, and performing backups) must be performed ). DB2 supports automatic maintenance and online maintenance operations. When running the online maintenance operation, you can continue to access the database while performing the maintenance operation. When the workload is lightweight, if you select a maintenance window, you can use throttling for online maintenance operations, use idle resources, and improve performance. In addition, this reduces the possibility of conflicts with common application processing because maintenance operations get specific locks, which may lead to application waiting.

Performance monitoring and Tuning

As the database size and features change, you can adjust your database before the problem occurs through regular monitoring to actively address your business needs. DB2 database products provide many tools, such as db2pd commands, system-defined management views and routines, and IBM InfoSphere Optim Performance Manager. The db2pd command is a popular non-intrusive command line tool that can be used to monitor performance. System-defined views and routines provide easy-to-use application programming interfaces through SQL. IBM InfoSphere Optim Performance Manager is a Web Console that you can use to isolate and analyze typical database Performance problems. As a database administrator, you can perform any necessary operations based on the matrix reported by the monitoring tool. If you cannot meet your business goals of response time and throughput, you can perform the following operations, increase more memory, increase the size of the buffer pool, move the table to the tablespace with the associated buffer pool, create a new index, or create a materialized view.

Test Environment

Changes in the database must be tested for functionality, performance, and stability before they can be deployed in the production environment. A typical change is the irregular adoption of new DB2 features or database tuning. There is a certain risk of directly modifying the production environment. These changes may affect the availability of the business system.

You can create a testing environment that contains representative data and workload and test these changes, instead of using your production environment. The size of the test environment does not have to be the same as that of the production system. It can be a small subset of the production environment with a smaller dataset. DB2 provides many tools to create a parallel test system, including the IBM InfoSphere Optim tool.