Topic
  • 5 replies
  • Latest Post - ‏2014-02-26T16:00:56Z by NickLawrence
mkoni
mkoni
26 Posts

Pinned topic Variables in MQT's

‏2014-02-21T11:48:57Z |

I read in the SQL Reference that Global Variables are not allowed in MQT's. CURRENT DATE is also not allowed in MQT's.

A lot of my views useing the actual year in their sql-code.

How can I create MQT's without hard-coded the actual year in the select statements of the views?

is there any other way to use a global variables in MQT's? or any idea how can I solve this?

 

lg

  • NickLawrence
    NickLawrence
    62 Posts
    ACCEPTED ANSWER

    Re: Variables in MQT's

    ‏2014-02-26T16:00:56Z  
    • mkoni
    • ‏2014-02-26T07:44:23Z

    yes, you are right,

    but return to my primary question, do I have any chance to use global variables in mqt-queries ?

    What you want to know can be found in the CREATE TABLE documentation.

    http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzhctabl.htm

    In materialized query definition

    ---> The select-statement must not refer to variables or global variables, or include parameter markers. <---

  • NickLawrence
    NickLawrence
    62 Posts

    Re: Variables in MQT's

    ‏2014-02-24T21:32:43Z  

    I'd be interested in seeing a sample query that you'd like the DB2 optimizer to use such an MQT to evaluate.

     

    Typically, MQTs are used to store partial aggregates (or summaries) of data in a fact table (which doesn't change very often) - the optimizer is then allowed to use this (possibly stale) data to speed up query time.

    If the MQT's query could global variables, special registers or non-deterministic functions, I don't follow how the MQT would help your cause?

     

    There's an excellent whitepaper on the value of MQT's that Mike Cain's team published: http://public.dhe.ibm.com/partnerworld/pub/pdf/courses/438a.pdf

     

    It's possible that there is some discussion in there that talks about the kind of thing you need to do - but I'm not sure I understand your use case.

  • mkoni
    mkoni
    26 Posts

    Re: Variables in MQT's

    ‏2014-02-25T09:47:16Z  

    I'd be interested in seeing a sample query that you'd like the DB2 optimizer to use such an MQT to evaluate.

     

    Typically, MQTs are used to store partial aggregates (or summaries) of data in a fact table (which doesn't change very often) - the optimizer is then allowed to use this (possibly stale) data to speed up query time.

    If the MQT's query could global variables, special registers or non-deterministic functions, I don't follow how the MQT would help your cause?

     

    There's an excellent whitepaper on the value of MQT's that Mike Cain's team published: http://public.dhe.ibm.com/partnerworld/pub/pdf/courses/438a.pdf

     

    It's possible that there is some discussion in there that talks about the kind of thing you need to do - but I'm not sure I understand your use case.

    We create MQT's for statistik data of the last 2 years (current year and last year), refreshing daily using 'refresh table'  called from clpgm in jobscheduler.

    since we can use the MQT's like a table we have different reports in webquery that uses the data in the MQT.

  • NickLawrence
    NickLawrence
    62 Posts

    Re: Variables in MQT's

    ‏2014-02-25T16:52:15Z  
    • mkoni
    • ‏2014-02-25T09:47:16Z

    We create MQT's for statistik data of the last 2 years (current year and last year), refreshing daily using 'refresh table'  called from clpgm in jobscheduler.

    since we can use the MQT's like a table we have different reports in webquery that uses the data in the MQT.

    I agree that there are some advantages (from an administrative point of view) with associating a query with a table. But since you are not counting on the DB2 optimizer to use the MQT - it doesn't sound like you are getting a performance benefit from the MQT, over an ordinary table.

    What you want to do could be accomplished by clearing the existing data in an ordinary table, followed by an insert that specifies the query. The application must already be doing the mapping of an end-query to a specific table/mqt, so in theory the application knows how to reload the data?

  • mkoni
    mkoni
    26 Posts

    Re: Variables in MQT's

    ‏2014-02-26T07:44:23Z  

    I agree that there are some advantages (from an administrative point of view) with associating a query with a table. But since you are not counting on the DB2 optimizer to use the MQT - it doesn't sound like you are getting a performance benefit from the MQT, over an ordinary table.

    What you want to do could be accomplished by clearing the existing data in an ordinary table, followed by an insert that specifies the query. The application must already be doing the mapping of an end-query to a specific table/mqt, so in theory the application knows how to reload the data?

    yes, you are right,

    but return to my primary question, do I have any chance to use global variables in mqt-queries ?

  • NickLawrence
    NickLawrence
    62 Posts

    Re: Variables in MQT's

    ‏2014-02-26T16:00:56Z  
    • mkoni
    • ‏2014-02-26T07:44:23Z

    yes, you are right,

    but return to my primary question, do I have any chance to use global variables in mqt-queries ?

    What you want to know can be found in the CREATE TABLE documentation.

    http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzhctabl.htm

    In materialized query definition

    ---> The select-statement must not refer to variables or global variables, or include parameter markers. <---