Use the following strategies to configure your Optim™ solution and your database so that archive, delete, and extract, insert, and load processing are as efficient as possible.
Your configuration strategy depends on your needs and the constraints of your database processing. Information presented here is not intended to solve specific performance problems but to aid you in developing a strategy for archive, delete, extract, insert, and load processing.
Database changes that provide maximum optimization for Optim processing can have negative implications for application processing. You must determine for yourself the combination of solutions that best suits the demands upon your particular database. In developing your strategy, consider Optim to be an important database application when you configure your database.
To improve delete performance, remove any unnecessary indexes from database tables. You can also improve delete performance by disabling row comparison and increasing the commit frequency in the delete properties of the archive request. If you do not want to disable row comparison, you can increase performance by excluding large object (LOB) columns from the row comparison. You can also increase the number of database connections for delete processing, which requires that you disable table locks. If you disable table locks, you must ensure that no modification to rows or tables occurs during processing.
If it is not possible or practical to run archive processing during off-peak or maintenance hours, data integrity processing can be used.
A more intricate strategy for processing rows during off-peak hours is to process a single group of rows by running multiple requests. Since archive, delete, and extract processing is light on processor usage, creating multiple requests to process data at one time can significantly increase processing throughput. This strategy can be most beneficial for databases that cannot be modified for Optim processing (for example, by partitioning the database and removing unnecessary indexes).
When you run multiple requests simultaneously, increase the number of database connections for extract, archive, and delete processing. For delete processing, you can also disable row comparison and table locks and increase the commit frequency.
If you are not able to run processing solely during off-peak hours, you can adopt a strategy in which a few rows are processed regularly during normal operating hours. The archiving, deleting, or extracting of a few rows might not affect normal application processing. However, to ensure database integrity during the delete process, you must ensure that table locks and compare row contents are enabled.
An additional way to increase the speed of archive, delete, and extract processing is to divide your database tables into partitions. Depending on the type of data in the tables, rows are sorted by frequency of access and age into the partitions. Since there is less I/O traffic to an infrequently used partition, the speed of processing rows is increased. Additionally, you can more easily disable unnecessary database indexes for a partition that is used infrequently.