Creating and Editing Materialized Query Tables

Go Up to Creating and Editing Database Dependent Objects

A materialized query table is a table that, like a materialized view, is defined based upon the result of a query addressing one or more tables. While the query on which a view is based is run whenever the view is referenced, a materialized query table stores the query results as data that you can work with instead of the data that is in the underlying tables.

Materialized query tables can significantly improve query performance, especially for complex queries.

The following database platforms support Materialized Query Tables:

• IBM DB2 for LUW 5.x, 6.x, 7.x, 8.x, 9.x, and 10.x

Note: The DB2 Materialized Query Table Wizard and DB2 Materialized Query Table Editor share the same options, except for Attachment Bindings options which are present only in the editor.

Add a Materialized Query Table

1. In the Data Model Explorer, expand the Physical Main Model, right-click the Materialized Query Table node, and then click New Materialized Query Table.
2. Complete the DB2 Materialized Query Table Wizard and then click Finish to create the materialized query table.

Tip: Once you have created the materialized query table, you can edit it by right-clicking the materialized query table you want to change, and then clicking Edit Materialized Query Table.

Name page/tab

The name of the materialized query table is derived from the information entered here, in the format of owner. name where:

• owner is the owner of the materialized query table.
• name is the name of the materialized query table.

Location page/tab

• What is the table tablespace: To avoid the performance overhead created by logging changes to the data, create materialized query tables in a table space that is defined as NOT LOGGED.
• Refresh Preferences: Determines when the materialized query table is update when its base tables are updated.
• Query Optimization: If enabled, DB2 can consider the materialized query table in automatic query rewrite. When query optimization is enabled, DB2 is more restrictive of what can be selected in the full select for a materialized query table.

SQL page/tab

Enter the entire select statement for the materialized view, starting with the keyword SELECT.

Definition page/tab

Enter or edit a definition or description for the materialized view. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.

DDL page/tab

Displays the CREATE SUMMARY TABLE AS... (SELECT...) statement needed to build the node group. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the materialized query table.

Attachment Bindings tab

Bind an external piece of information, or attachment to the materialized query table. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

Notes

• The materialized query table is not populated when first created. You must explicitly populate the materialized query table using the REFRESH TABLE statement for system-maintained materialized query tables or by using the LOAD utility, INSERT statement or the REFRESH TABLE statement for user-maintained materialized query tables.

See Also

• Attaching External Documents to the Data Model
• Creating and Editing Materialized Views