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.
Procedure
To ensure that the statistics in the DB2
catalog accurately reflect the organization and content of your data:
- 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.
- Invoke the RUNSTATS utility to capture statistics.
- 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.
- Capture EXPLAIN information to validate access path changes.
- 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.