Show Materialized Query Table columns

 

You can display materialized query tables associated with another table using iSeries™ Navigator.

To display materialized query tables, follow these steps:

  1. In the iSeries Navigator window, expand the system that you want to use.

  2. Expand Databases and the database that you want to work with.

  3. Expand Schemas and the schema that you want to work with.

  4. Right-click a table and select Show Materialized Query Tables.

Table 1. Columns used in Show materialized query table window
Column name Description
SQL Name The SQL name for the materialized query table
Schema Schema or library containing the materialized query table
Partition Partition detail for the index. Possible values:

  • <blank>, which means For all partitions

  • For Each Partition

  • specific name of the partition
Owner The user ID of the owner of the materialized query table.
Short Name System table name for the materialized query table
Enabled Whether the materialized query table is enabled. Possible values are:

  • Yes

  • No
If the materialized query table is not enabled, it cannot be used for query optimization. It can, however, be queried directly.
Creation Date The timestamp of when the materialized table was created.
Last Refresh Date The timestamp of the last time the materialized query table was refreshed.
Last Query Use The timestamp when the materialized query table was last used by the optimizer to replace user specified tables in a query.
Last Query Statistics Use The timestamp when the materialized query table was last used by the statistics manager to determine an access method.
Query Use Count The number of instances the materialized query table was used by the optimizer to replace user specified tables in a query.
Query Statistics Use Count The number of instances the materialized query table was used by the statistics manager to determine an access method.
Last Used Date The timestamp when the materialized query table was last used.
Days Used Count The number of days the materialized query table has been used.
Date Reset Days Used Count The year and date when the days-used count was last set to 0.
Current Number of Rows The total number of rows included in this materialized query table at this time.
Current Size The current size of the materialized query table.
Last Changed The timestamp when the materialized query table was last changed.
Maintenance The maintenance for the materialized query table. Possible values are:

  • User

  • System
Initial Data Whether the initial data was inserted immediately or deferred. Possible values are

  • Deferred

  • Immediate
Refresh Mode The refresh mode for the materialized query table. A materialized query table can be refreshed whenever a change is made to the table or deferred to a later time.
Isolation Level The isolation level for the materialized query table.
Sort Sequence The alternate character sorting sequence for National Language Support (NLS).
Language Identifier The language code for the object.
SQL Statement The SQL statement that is used to populate the table.
Text The text description of the materialized query table.

 

Parent topic:

Optimizing query performance using query optimization tools