Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    7

    Unanswered: Explain plan for MQT

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Care to show the actual statement that failed?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    7
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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'"?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2009
    Posts
    7
    Ah well the !db2 ... was just for testing purpose, but
    Yes the same behaviour without it

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2009
    Posts
    7
    ah well, yes sounds similiar. At least it was not a stupid thinking failure
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •