Online Documentation for SQL Manager for DB2

Creating/editing MQ Table


Use the Edit tab of MQ Table Editor to create/edit a materialized query table and specify its definition.

 

MQ Table Editor - Editing MQ Table definition

 

 

Schema

Use the drop-down list to select the schema for the new materialized query table.

 

Name

Enter a name for the new MQ table. Note that the name must not identify a table, view or alias described in the catalog.

 

 

Storage

 

Tablespace

Use the drop-down list to identify the table space where the MQ table will be created.

 

Index

Define the table space in which any indexes on the table will be created.

 

Long data

Specify the table space in which the values of any long columns (LONG VARCHAR, LONG VARGRAPHIC, LOB data types, distinct types with any of these as source types, or any columns defined with user-defined structured types with values that cannot be stored inline) will be stored.

 

 

Data capture

This option indicates whether extra information regarding SQL changes to this MQ table will be written to the log or not.

 

Lock

Specify whether lock is to be applied to a Row or to the entire Table while the MQ table data is being modified.

 

Managed By...

 

RadioButton System

If this option is selected, the MQ table will be a system managed table.

 

RadioButton Database

If this option is selected, the MQ table will be a database managed table.

 

Optimization

The summary table can be used for query optimization under appropriate circumstances. Select the appropriate option to enable or disable optimization for the MQ table.

 

Refresh Mode

Select the way how the data in the MQ table is maintained.

 

RadioButton Deferred

The data in the table can be refreshed at any time with the REFRESH TABLE statement used. The data only reflects the result of the query as a snapshot at the time the REFRESH TABLE statement is processed.

 

RadioButton Immediate

Changes made to the underlying tables as part of a DELETE, INSERT or UPDATE are cascaded to the summary MQ table.

 

CheckBox Volatile

This option specifies that cardinality of the MQ table is volatile.

 

CheckBox Not logged

This option specifies that changes made to the column are not to be logged.

 

CheckBox Restrict on drop

Check this option to restrict dropping the MQ table.

 

CheckBox Append

This option specifies that new rows are appended at the end of table data.

 

If necessary, specify the PCTFree value for the MQ table. PCTFree is the percentage of each page that is to be left as free space.

 

The Body area introduces the query that is used for the definition of the MQ table and to determine the data included in the table. If necessary, you can use the SQL Builder button to run Query Builder for visual SQL building.