Query below lists all MQTs (materialized query tables) in IBM Db2 database.

Query

select tab.tabschema as schema_name,
    tab.tabname as table_name,
    tab.create_time,
    tab.alter_time
from syscat.tables tab
where tab.type = 'S'
    and tab.tabschema not like 'SYS%'
order by tab.tabschema, tab.tabname

Columns

• schema_name - schema name
• table_name - table name
• create_time - date the table was created
• alter_time - date the table was last modified by using an ALTER statement

Rows

• One row represents one MQT in the database
• Scope of rows: all MQTs in the database
• Ordered by schema and name

Sample results