Determining whether query rewrite occurred

You can use EXPLAIN to determine whether Db2 has rewritten a user query to use a materialized query table.

About this task

Begin program-specific programming interface information. When Db2 rewrites the query, the PLAN TABLE shows the name of the materialized query that Db2 uses. The value of the TABLE_TYPE column is M to indicate that the table is a materialized query table.

Example

Consider the following user query:

SELECT YEAR, AVG(QUANTITY * PRICE) AS AVGVAL
  FROM TRANSITEM TI, TRANS T
  WHERE TI.TRANSID = T.ID
  GROUP BY YEAR;

If Db2 rewrites the query to use a materialized query table, a portion of the plan table output might look like the following table.

Table 1. Plan table output for an example with a materialized query table
PLANNO	METHOD	TNAME	JOIN_TYPE	TABLE_TYPE
1	0	TRANSAVG	-	M
2	3	2	-	?

The value M in TABLE_TYPE indicates that Db2 used a materialized query table. TNAME shows that Db2 used the materialized query table named TRANSAVG.You can also obtain this information from a performance trace (IFCID 0022). End program-specific programming interface information.