Materialized Query Tables Wizard (DB2 LUW)

Go Up to IBM DB2 for Linux, Unix, and Windows Object Wizards

A materialized query table (MQT) is a table based on the result of a query. An MQT contains information that is summarized from other tables and can save time when processing dynamic SQL queries. The Materialized Query Table Wizard lets you create a table without knowing the underlying commands.

To Open the Materialized Query Wizard

1. On the Navigator/Explorer, find the datasource where you want to add the new materialized query table.
2. Expand the Schema branch, right-click Materialized Query Tables, and select New.

The table that follows describes the fields you may encounter as you complete the Materialized Query Table Wizard.

Note: These options are only available if the tablespace you selected is a database managed tablespace.

Required Information	Description
Who owns the table?	Choose the owner of the table you’re creating from the drop-down list.
What is the name of the table?	Type the name of the materialized query table you are creating.
Select a tablespace on which to place the table:	OPTIONAL: No selection is the default. But you can select a tablespace that belongs to the new table’s owner if you want.
Specify separate tablespaces for index and long data	OPTIONAL: Lets you separate indexes or long data from the table. Indexes Long data
Specify the query on which this table is based	Write the query you want to use to create the table. Note: Every select list element must have a name (use the AS clause for expressions)
Add the columns belonging to this table Add the columns belonging to the partition key	Click Add, Insert or Edit to add or modify table columns. Click Drop to delete a selected column.
Do you want the table replicated across database partitions?	The default is no, but check the box if you do want to replicate the table. Replicated materialized query tables can help you improve query performance by enabling collocation between tables. Replication is especially helpful when you have joins between large fact tables and small dimension tables. It’s best if tables that are to be replicated are infrequently updated.
Definition Only	Lets you select definition options, Include Column Defaults and Include Identity Column Attributes. When you select the Definition Only option, the new table is treated as if it was a base table rather than a MQT. After you complete the wizard, the Tables Editor opens.
Refreshable	Lets you select refresh options: Immediate: The table you are creating will be refreshed automatically when changes are made to the base table(s). Deferred: Static SQL will not be optimized. Changes to the base table(s) will not be reflected. Query Optimization: Enabled: Queries will be routed to the MQT. Disabled: This is the default. Maintained by: System: This is the default User After you complete the wizard, the Material Query Tables Editor opens.
Would you like extra information regarding SQL changes to this table to be written to the log?	If you opted to replicate the table, you must make a selection here. Yes means you want to receive error messages issued by the DB2 replication programs. Include Longvar Columns means you want to receive error messages for these columns if long varchar data is a part of the table you’re replicating. No
Would you like to skip logging changes made to this table by Insert... and Alter Table operations in the same unit of work in which this table is created?	Initially Not Logged: This is an option that can reduce logging and increase performance, but also means that you cannot recover the table when rolling forward. Logged Initially. This is the default.
What type of table lock would you like when it is being accessed?	Row: This is the default. During a table or index scan, DB2 locks each row that is scanned before determining whether that row is relevant to the query. Table: During a table scan, DB2 locks the table so no data can be added or altered while the query is executed.
What percent of free space to leave for load and reorganization?	-1 is the default.
Do you want data to append to the end of the table?	Yes No: This is the default
Do you want the access plan to this table to be based on existing statistics and optimization level?	Volatile: A volatile table’s contents can vary from empty to huge at run time and can render collected statistics inaccurate. Not Volatile: This is the default.
Enter a comment	Optional