Database Maintenance Issues

If your query used to run quickly and is now slower, or the speed of the query changes depending on the constants specified in the where clause, your problem can be poor database maintenance.

To optimize performance, set up maintenance procedures that run DBA utilities.

The following features are especially useful in tracking performance problems:

• Optimization
• Modification of table and index structure
• System modification
• Verification

For discussions of maintenance issues, see the chapters "Maintaining Databases," "Maintaining Storage Structures," and "Using the Query Optimizer."

Optimization and Performance

The optimization feature collects statistics that are used by the query optimizer to determine the best query execution plan (QEP) to use for your queries.

Follow these optimization guidelines:

• Periodically run optimization on all your databases to generate statistics for columns that are keys or indexed. List the other columns you need as an argument to this command.
• Run full optimization statistics on columns referenced in where clauses of strategic queries that are having problems.
• For very large tables, create statistics based on sample data.
• When there are significant changes to your data distribution, run optimization on the affected columns.
• Do not collect excessive statistics, because you build up large optimizer tables with unused data.
• Run system modification after every optimization.

To perform optimization, use the optimizedb command or, in VDBA use the Optimize Database dialog.

Table and Index Modification and Performance

You can modify a table or index to:

• Reorganize data on new data pages
• Free deleted record space
• Reduce overflow chains
• Adjust the fill factor

Use the Shrink B-tree Index option (or modify to merge statement) to:

• Reorganize index pages of B-tree tables
• Reduce overflow chains

Use the Change Location option (or modify to relocate statement) to move your tables to balance disk access.

To perform modification, use the modify statement or, in VDBA use the Modify Table Structure and Modify Index Structure dialogs.

System Modification and Performance

The system modification feature modifies system catalogs to predetermined storage structures.

• Run system modification on the iistatistics system catalog after optimization.
• Run system modification on ii_rcommands if you create and update a lot of Report-Writer reports.
• Regularly using system modification reduces overflow in your system catalogs. Run it often if catalog changes are frequent due to development or if you use many create or drop statements in your applications.

To perform system modification, use the sysmod command or, in VDBA, use the System Modification dialog

Verification and Performance

Use the verification utility to:

• Destroy or list unrequired disk files, expired tables, or temporary tables in your database
• Clean up fragmented disk space

To perform verification, use the verifydb command or, in VDBA, use the Verify Database dialog.