Explain plan for MQT

Gnugamel · #1 (**permalink**) 11-19-09, 15:36

Hi
This semester i attended to an DB2 course at University, so i am really not very experienced in using the database, sry if this question looks stupid.

This weeks exercise was about Explain plans for simple querys. To achieve this i used the following code(as example):

!db2 explain plan set queryno = 20 for 'SELECt count(*) from lineitem'
!db2exfmt -d tpch -1;

This works fine as long, as the Query does not use any MQT. If the optimizer can use a created MQT i get the following error (with exactly the same command):

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "SET QUERYNO" was found following "explain plan
". Expected tokens may include: "FOR". SQLSTATE=42601

I searched the IBM infocenter but was not able to find the reason for this behaviour, i thought an MQT is treated like a normal table.
Where is my failure?

Thx for help

n_i · #2 (**permalink**) 11-19-09, 15:43

Care to show the actual statement that failed?

Gnugamel · #3 (**permalink**) 11-19-09, 15:58

Well

!db2 explain plan set queryno = 20 for 'SELECt count(*) from lineitem'
on an frehs TPCH database works, after using this create table statement:

create table turnover_mat as (
select rc_name, rs_name, p_type, SUM(l_extendedprice*(1-l_discount)) as turnover, count(*) as counti
from lineitem_full
group by rc_name, rs_name, p_type)
data initially deferred
refresh immediate
enable query optimization
maintained by system;
refresh table TURNOVER_MAT;
"

!db2 explain plan set queryno = 20 for 'SELECt count(*) from lineitem'

fails with the error given above. Dropping the set queryno:
!db2 explain plan for 'SELECt count(*) from lineitem'
it works fine.
this set queryno error always appears if a query would use this MQT

n_i · #4 (**permalink**) 11-19-09, 16:25

I take it you run "!db2 explain plan set queryno = 20 for 'SELECt count(*) from lineitem'" from within CLP. Do you get the same problem if you run just "explain plan set queryno = 20 for 'SELECt count(*) from lineitem'"?