Whether Db2 can consider
automatic query rewrite depends on properly defined materialized query
tables, and the values of two special registers.
Before you begin
- The isolation levels of the materialized query tables must be
equal to or higher than the isolation level of the dynamic query being
considered for automatic query rewrite
- You must populate system-maintained materialized query tables
before Db2 considers
them in automatic query rewrite.
About this task
The values of two special
registers, CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES
FOR OPTIMIZATION determine whether Db2 can consider
using materialized query tables in automatic query rewrite.
Procedure
To enable automatic query rewrite:
- Specify ANY for the CURRENT REFRESH AGE special register.
The value in special register CURRENT REFRESH AGE represents
a refresh age. The refresh age of a materialized query
table is the time since the REFRESH TABLE statement last refreshed
the table. (When you run the REFRESH TABLE statement, you update the
timestamp in the REFRESH_TIME column in catalog table SYSVIEWS.) The
special register CURRENT REFRESH AGE specifies the maximum refresh
age that a materialized query table can have. Specifying the maximum
age ensures that automatic query rewrite does not use materialized
query tables with old data. The
CURRENT REFRESH AGE has only two values: 0 or ANY. A value of
0 means that Db2 considers
no materialized query tables in automatic query rewrite. A value of
ANY means that Db2 considers
all materialized query tables in automatic query rewrite.
The CURRENT REFRESH AGE field on installation panel DSNTIP8
determines the initial value of the CURRENT REFRESH AGE special. The
default value for the CURRENT REFRESH AGE field is 0.
- Specify the appropriate value for the CURRENT MAINTAINED
TABLE TYPES FOR OPTIMIZATION special register.
The
refresh age of a user-maintained materialized query table might not
truly represent the freshness of the data in the table. In addition
to the REFRESH TABLE statement, user-maintained query tables can be
updated with the INSERT, UPDATE, MERGE, TRUNCATE, and DELETE statements
and the LOAD utility. Therefore, you can use the CURRENT MAINTAINED
TABLE TYPES FOR OPTIMIZATION special register to determine which type
of materialized query tables, system-maintained or user-maintained, Db2 considers
in automatic query rewrite. The special register has four possible
values that indicate which materialized query tables Db2 considers
for automatic query rewrite:
- SYSTEM
- Db2 considers
only system-maintained materialized query tables.
- USER
- Db2 considers
only user-maintained materialized query tables.
- ALL
- Db2 considers
both types of materialized query tables.
- NONE
- Db2 considers
no materialized query tables.
The CURRENT MAINT TYPES field on installation panel
DSNTIP4 determines the initial value of the CURRENT MAINTAINED TABLE
TYPES FOR OPTIMIZATION special register. the default value for
CURRENT MAINT TYPES is SYSTEM.
Results
The following table summarizes how to use the CURRENT REFRESH
AGE and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special registers
together. The table shows which materialized query tables
Db2 considers
in automatic query rewrite.
Table 1. The
relationship between CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE
TYPES FOR OPTIMIZATION special registers
| Value of CURRENT MAINTAINED
TABLE TYPES FOR OPTIMIZATION |
SYSTEM |
USER |
ALL |
None |
| CURRENT REFRESH AGE=ANY |
All system-maintained
materialized query tables |
All user-maintained materialized query tables |
All materialized query tables (both system-maintained
and user-maintained) |
None |
| CURRENT REFRESH AGE=0 |
None |
None |
None |
None |
