Maintaining data organization and statistics

You can enable DB2® to choose the most efficient access paths by reorganizing your data and collecting accurate statistics.

About this task

DB2 uses the catalog statistics in conjunction with information about database design, and the details of the SQL statement to choose access paths.

Space usage statistics also help DB2 to select access paths that use index or table space access as efficiently as possible. By reducing gaps between leaf pages in an index, or by ensuring that data pages are well-organized, you can reduce the use of inefficient I/O operations.

To ensure that the statistics in the DB2 catalog accurately reflect the organization and content of your data:

1. Invoke the REORG utility to reorganize the necessary tables, including the DB2 catalog table spaces and user table spaces. You can invoke the DSNACCOX stored procedure to determine when reorganization is needed.
2. Invoke the RUNSTATS utility to capture statistics.
3. Rebind the plans or packages that contain affected queries. Specify the PLANMGMT bind option to save previous copies of the packages. You can use the APCOMPARE bind option to detect access path changes for you static SQL statements. For dynamic SQL statements, DB2 uses the newly collected statistics at the next prepare.
4. Capture EXPLAIN information to validate access path changes.
5. In the event of access path regression, use the REBIND command and specify the SWITCH option to revert to a previous access path. This action depends upon the PLANMGMT bind option that was specified when packages were first rebound.

What to do next

Implement a strategy for reorganizing your data and collecting statistics routinely. Routine statistics collection is necessary for maintaining good performance, and is likely to be required at additional times, other than after reorganization.You can also use RUNSTATS profiles and certain stored procedures to automate statistics maintenance.