User Maintained Materialized table

[Epyfox]

Hello,

I was wondering if anybody could help me with defining a refreshable, user-maintained materialized table.

The documentation states that it is possible to update an user-defined materilized table "by using the LOAD utility, the INSERT, UPDATE, and DELETE statements, as well as the REFRESH TABLE statement".

But when I use the "refresh table"-statements I get the message:
"SQL0159N The statement references "DB2.TESTING1" which
identifies a "USER MAINTAINED SUMMARY TABLE" rather than
a "SUMMARY TABLE".

I have defined the base table as:
CREATE TABLE testing (
id int NOT NULL,
name varchar(20)
);

And the materialized table as:
CREATE TABLE testing1 AS
(
SELECT * FROM testing
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER;

Thanks you very much,

Erol

 

[juliane26]

It might be irritating, but define your table as you did using 'maintained by system' instead of 'maintained by user' and carefully read the documentation again ...


then after a 'refresh table' you can select from it, but you will always need another refresh table to apply changes.

 

[Epyfox]

Hello juliane6,

thank you for your reply but I need a "maintained by user"
because I will have to update the materialized table directly in certain cases.
Sorry, I was not clear on this point.

So I need to use the "refresh table"-statement to fill the
table with data from the underlying tables and then change
some of that data with update-statements.

I would rather not use Delete and insert statements.

Thank you

 

[juliane26]

well, user maintained tables are updateble summary tables. it is like a copy of your table, you can update it and force the optimizer to use it instead of the underlying base table(s).

>>> this is a very risky scenario since not consistent, which i personally would not like to use !!! <<<

you better make sure you know what you do.
to get initially data you have to enter an insert statement, refresh is then of course not possible.

if you need a copy of your table as base for another table, how about
CREATE TABLE LIKE statement and then filling/updating it using SQL ? That appears to me to be more useful and a clean solution.

 

[Epyfox]

Hi,

I could use SQL to update and fill the table.
But the 2 base tables are updated by an import every 15
minutes and completely refreshed by a Load each day.
If I don't use the "refresh table"-command I will have to
truncate the materialized table and insert it with a query
every 15 minutes. And then update the fields that need to
be updated (depending on the data).

I do not know how this will compare performance-wise to
the refresh table command. The materialized table is the
most heavily used table in the application.

Erol

 

[juliane26]

If you are accessing your new table directly using SQL, and it is a big table and important, did you consider replication then columnwise ? Depending on your updates you might even be able to automate those as well.

Or consider MERGE ? Not sure whether that helps in your special case.

Anyway, seems you have to live with that. No refresh for user managed MQTs.

 

[Epyfox]

Hmmm, I'll look into that. It could work. Thank you.