Invoking the explain utility against REFRESH TABLE or SET
INTEGRITY statements enables you to generate access plans that can
be used to self-diagnose performance problems with these statements.
This can help you to better maintain your materialized query tables
(MQTs).
To get the access plan for a REFRESH TABLE or a SET INTEGRITY
statement, use either of the following methods:
- Use the EXPLAIN PLAN FOR REFRESH TABLE or EXPLAIN PLAN FOR SET
INTEGRITY option on the EXPLAIN statement.
- Set the CURRENT EXPLAIN MODE special register to EXPLAIN before
issuing the REFRESH TABLE or SET INTEGRITY statement, and then set
the CURRENT EXPLAIN MODE special register to NO afterwards.
Restrictions
- The REFRESH TABLE and SET INTEGRITY statements do not qualify
for re-optimization; therefore, the REOPT explain mode (or explain
snapshot) is not applicable to these two statements.
- The WITH REOPT ONCE clause of the EXPLAIN statement, which indicates
that the specified explainable statement is to be re-optimized, is
not applicable to the REFRESH TABLE and SET INTEGRITY statements.
Scenario
This scenario shows how you can generate
and use access plans from EXPLAIN and REFRESH TABLE statements to
self-diagnose the cause of your performance problems.
- Create and populate your tables. For example:
create table t (
i1 int not null,
i2 int not null,
primary key (i1)
);
insert into t values (1,1), (2,1), (3,2), (4,2);
create table mqt as (
select i2, count(*) as cnt from t group by i2
)
data initially deferred
refresh deferred;
- Issue the EXPLAIN and REFRESH TABLE statements, as follows:
explain plan for refresh table mqt;
This
step can be replaced by setting the EXPLAIN mode on the SET CURRENT
EXPLAIN MODE special register, as follows: set current explain mode explain;
refresh table mqt;
set current explain mode no;
- Use the db2exfmt command to format the contents
of the explain tables and obtain the access plan. This tool is located
in the misc subdirectory of the instance sqllib directory.
db2exfmt -d dbname -o refresh.exp -1
- Analyze the access plan to determine the cause of the performance
problem. In the previous example, if T is a large table, a table scan
would be very expensive. Creating an index might improve the performance
of the query.