Temporal tables in IBM DB2 database allows us to query table like it would be in specific past time. Query below returns temporal tables paired with their history tables (for system period type tables).

Query

select tabschema as schema_name,
       tabname as table_name,
       historytabschema as history_schemaname,
       historytabname as history_table_name,
       case periodtype
          when 'A' then 'APPLICATION'
          when 'S' then 'SYSTEM'
          end as periodtype,
      begincolname,
      endcolname
from syscat.periods
order by schema_name,
         table_name;

Columns

• period_name - name of the period
• schema_name - temporal table schema name
• table_name - temporal table name
• history_table_schema - history table schema name
• history_table_name - history table name
• period_type - type of period.
  • APPLICATION
  • SYSTEM
• begincolname - period begin column name
• endcolname - period end column name

Rows

• One row represents one temporal table
• Scope of rows: only tables defined as temporal in a database
• Ordered by temporal table schema and table name

Sample results