Search code examples
sqldb2ibm-midrangedb2-400

DB2 create staging table for MQT(Materialized Query Table) for incremental refresh

How can I create a staging table for an MQT (Materialized Query Table)?

The goal is to use an incremental refresh (instead of a full refresh)

This is with DB2 on an IBMi

I was following this article https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.sample.doc/doc/clp/s-tbast-db2.html

I am able to create an MQT

When I try to follow the example to create the staging table I receive an error

For example,

MQT creation for XXLIB.SALES:

create table XXLIB.SALES as 
(select Company, Territory, SUM(Sales) 
From XXLIB.HISTORY Where Company = 1 Group by Company, Territory)
data initially deferred refresh deferred maintained by user

Then for the staging XXLIB.SALES_S

CREATE TABLE XXLIB.SALES_S FOR XXLIB.SALES PROPAGATE IMMEDIATE;

Here is the error message

SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token . was not valid. Valid tokens: . Cause . . . . . : A syntax error was detected at token .. Token . is not a valid token. A partial list of valid tokens is . This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token .. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

Processing ended because the highlighted statement did not complete successfully

Solution

  • Unfortunately, Db2 for IBM i doesn't support all of the MQT functionality that Db2 for LUW does..

    Db2 for i lacks system maintained MQTs along with incremental user maintained ones.

    You'd need to roll your own solution on Db2 for i using triggers on the base table.

    Your trigger could update the MQT immediately or write to your own "staging" table. Note This table is staging only in the sense that that's how your refresh process uses it. You have to manually create it yourself, as far as Db2 for i is concerned, it's a completely stand alone table.

    Personally, I've never bother with MQTs, instead an encoded vector index (EVI) with computed columns meets every need I've every considered. (Note that Db2 LUW doesn't have EVIs)

    EVI's are system maintained, so the data is always up to date.

    CREATE ENCODED VECTOR INDEX sales_fact_location_id_evi 
ON sales_fact(sale_location_id ASC) 
INCLUDE(SUM(sale_amount_measure))

    The following IBM doc, Accelerated analytics - faster aggregations using the IBM DB2 for i encoded vector index (EVI) technology, talks about using EVIs where other RDBMS might use a MQT.

    added
    Here's an EVI that would take the place of the MQT you're trying to create...

    CREATE ENCODED VECTOR INDEX XXLIB.SALES 
ON XXLIB.HISTORY(Company, Territory)
INCLUDE(SUM(Sales))

    The only downside to an EVI vs. a MQT, is that you can directly reference an MQT in a query, instead of depending on the DB to implicitly use it. With an EVI, you are dependent on the DB to implicit use it.