My understanding is that DB2 for I does not support the REFRESH IMMEDIATE option.

Is there a way to have the system automatically maintain MQT tables?   To me running the REFRESH TABLE command is not acceptable because it clears reloads all of the rows in the table?     

Suggestions?

 CREATE OR REPLACE TABLE
   xxxx (c1, c2, c3) AS
    (SELECT C1,  SUM(C2), COUNT( C3 )
     FROM MY TABLE GROUP BY C1
     )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER
     ENABLE QUERY OPTIMIZATION  ;            

------------------------------
Brian Ellis
------------------------------

Dear Brian

I would understand your expressed notion if your server still uses spinning hard disks and your base tables for MQT are very large (say, larger than a few TB). But is it not convenient for you to manually refresh MQT once a day during the low workload period of the day?  

If your server uses all SSD/Flash Disk and you have more than 1 core CPU power in your server/LPAR, MQT may not be sufficiently useful any longer and I suggest you try the following actions and try your query over the base table to see if your query performance is better than refreshing MQT: 

1) Install DB2 SMP option and enable it.  There are a few ways to enable it but I suggest you just enable it for your query session by preceding your query statement with this statement SET CURRENT DEGREE = 'N' ; (I suggest you specify the number of CPU cores assigned to your server/LPAR plus 2 - e.g. if you have 2 allocated CPU cores, set N = 2+2 = 4 and then try increasing it by one at a time to see if it still delivers better runtime)  

2) Create an index with C1+C2+C3 over the base table.  If circumstance is fortunate, DB2 will access only the index and the performance improvement can be good.   Indexes are also maintained by the system.    Actually you may want to run your SELECT C1,  SUM(C2), COUNT( C3 )  FROM MY TABLE GROUP BY C1 from Run SQL Script session and display its Visual Explain graph to check if DB2 suggests you create the index as Encoded Vector Index (EVI) or not (by clicking on the icon representing "MY TABLE" and look at Index Advise information on the right panel of the VE window). 

You may try one method at a time to see how much improvement each delivers and decide if you want to do both or not.  If your query already uses any existing index(es), method 1 would be enough.  Otherwise, you need to create new index(es).

------------------------------
Satid S
------------------------------

Original Message:
Sent: Wed March 11, 2026 02:28 PM
From: Brian Ellis
Subject: Materialized Query Table (MQT)

My understanding is that DB2 for I does not support the REFRESH IMMEDIATE option.

Is there a way to have the system automatically maintain MQT tables?   To me running the REFRESH TABLE command is not acceptable because it clears reloads all of the rows in the table?     

Suggestions?

 CREATE OR REPLACE TABLE
   xxxx (c1, c2, c3) AS
    (SELECT C1,  SUM(C2), COUNT( C3 )
     FROM MY TABLE GROUP BY C1
     )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER
     ENABLE QUERY OPTIMIZATION  ;            

------------------------------
Brian Ellis
------------------------------

Hi Brian.

There are several ideas on the IBM IDEAS portal for implementing the REFRESH IMMEDIATE support on DB2 for IBM i. If you think this would be valuble, please vote for one of all of these ideas.

Another suggestion would be to use an EVI (encoded vector index) for storing the accumulations by key - this is method 2 in the response from Satid. We have used this method with great success and see no performance issue, but it can be tricky to make the database engine use the index, if you make it too complicated, e.g. as a sparse index with a WHERE clause.

In your case, you could create an EVI index like this:

create encoded vector index MYLIB.MYEVIINDEX
  on MYLIB.MYTABLE ( C1 asc )
  include ( sum( C2 ), count( C3 ) )

Hope this helps.

Best regards,
Christian

------------------------------
Christian Jorgensen
IT System Administrator | CEAC member
Network of Music Partners A/S
------------------------------

Original Message:
Sent: Wed March 11, 2026 02:28 PM
From: Brian Ellis
Subject: Materialized Query Table (MQT)

My understanding is that DB2 for I does not support the REFRESH IMMEDIATE option.

Is there a way to have the system automatically maintain MQT tables?   To me running the REFRESH TABLE command is not acceptable because it clears reloads all of the rows in the table?     

Suggestions?

 CREATE OR REPLACE TABLE
   xxxx (c1, c2, c3) AS
    (SELECT C1,  SUM(C2), COUNT( C3 )
     FROM MY TABLE GROUP BY C1
     )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER
     ENABLE QUERY OPTIMIZATION  ;            

------------------------------
Brian Ellis
------------------------------

The INCLUDE part of CREATE ENCODED VECTOR INDEX statement was an enhancement in IBM i 7.1 and Kent Milligan explains its benefit in his DB2 for i Blog here: New Query optimizations for EVIs at  https://db2fori.blogspot.com/2012/10/boom-another-ibm-i-71-technology-refresh.html      

And Scott Forstie also published an article on EVI Only Access (avoiding table access for better performance) feature.  Strangely, its URL is no longer found by Google search. So, I attach its PDF file herewith.

 

------------------------------
Satid S
------------------------------

Original Message:
Sent: Thu March 12, 2026 02:23 AM
From: Christian Jorgensen
Subject: Materialized Query Table (MQT)

Hi Brian.

There are several ideas on the IBM IDEAS portal for implementing the REFRESH IMMEDIATE support on DB2 for IBM i. If you think this would be valuble, please vote for one of all of these ideas.

Another suggestion would be to use an EVI (encoded vector index) for storing the accumulations by key - this is method 2 in the response from Satid. We have used this method with great success and see no performance issue, but it can be tricky to make the database engine use the index, if you make it too complicated, e.g. as a sparse index with a WHERE clause.

In your case, you could create an EVI index like this:

create encoded vector index MYLIB.MYEVIINDEX
  on MYLIB.MYTABLE ( C1 asc )
  include ( sum( C2 ), count( C3 ) )

Hope this helps.

Best regards,
Christian

------------------------------
Christian Jorgensen
IT System Administrator | CEAC member
Network of Music Partners A/S

Original Message:
Sent: Wed March 11, 2026 02:28 PM
From: Brian Ellis
Subject: Materialized Query Table (MQT)

My understanding is that DB2 for I does not support the REFRESH IMMEDIATE option.

Is there a way to have the system automatically maintain MQT tables?   To me running the REFRESH TABLE command is not acceptable because it clears reloads all of the rows in the table?     

Suggestions?

 CREATE OR REPLACE TABLE
   xxxx (c1, c2, c3) AS
    (SELECT C1,  SUM(C2), COUNT( C3 )
     FROM MY TABLE GROUP BY C1
     )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER
     ENABLE QUERY OPTIMIZATION  ;            

------------------------------
Brian Ellis
------------------------------

Db2 for i doesn't have the ability to maintain the data in an MQT.  For simpler MQT definitions like your example, it wouldn't be too hard to create a trigger on the base table to keep the data in the MQT current.

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Wed March 11, 2026 02:28 PM
From: Brian Ellis
Subject: Materialized Query Table (MQT)

My understanding is that DB2 for I does not support the REFRESH IMMEDIATE option.

Is there a way to have the system automatically maintain MQT tables?   To me running the REFRESH TABLE command is not acceptable because it clears reloads all of the rows in the table?     

Suggestions?

 CREATE OR REPLACE TABLE
   xxxx (c1, c2, c3) AS
    (SELECT C1,  SUM(C2), COUNT( C3 )
     FROM MY TABLE GROUP BY C1
     )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER
     ENABLE QUERY OPTIMIZATION  ;            

------------------------------
Brian Ellis
------------------------------