Materialized query table restrictions

Materialized query tables (MQTs), including shadow tables, are subject to certain restrictions.

Fullselect statements that form part of the definition of MQTs are subject to the following restrictions:
  • Every select element must have a name.
  • A fullselect must not reference any of the following object types:
    • Created global temporary tables
    • Declared global temporary tables
    • Materialized query tables
    • Nicknames that you created by using the DISALLOW CACHING clause of the CREATE NICKNAME or ALTER NICKNAME statement
    • Protected tables
    • Staging tables
    • System catalog tables
    • Typed tables
    • Views that violate any MQT restrictions
    • Views that directly or indirectly depend on protected tables
  • A fullselect must not contain any column references or expressions that involve the following data types:
    • LOB
    • LONG
    • XML
    • Reference
    • User-defined structured types
    • Any distinct type that is based on these data types
  • A fullselect must not contain any column references, expressions, or functions that meet the following criteria:
    • Depend on the physical characteristics of the data, such as DBPARTITIONNUM, HASHEDVALUE, RID_BIT, and RID
    • Depend on changes to the data, such as a row change expression or a row change timestamp column
    • Are defined as EXTERNAL ACTION
    • Are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA
  • A fullselect must not include a CONNECT BY clause.
  • If you specify the MAINTAINED BY FEDERATED_TOOL option for the CREATE TABLE statement, the SELECT clause must not contain a reference to a base table.
  • If you specify the REFRESH IMMEDIATE option for the CREATE TABLE statement, the following restrictions apply:
    • The CREATE TABLE statement for the MQT must not contain duplicate grouping sets.
    • The table must not be a system-maintained columnar MQT.
    • At least one unique key from each table that is referenced must be in the select list.
    • The fullselect must be a subselect. The exception is that UNION ALL is supported in the input table expression of a GROUP BY clause.
    • The input table expressions of a UNION ALL or a JOIN must not contain aggregate functions.
    • The fullselect must not contain any of the following items:
      • Aggregate functions without the fullselect also containing a GROUP BY clause
      • Any expressions that use the result of aggregate functions
      • Built-in functions that depend on the value of a special register
      • Functions that are not deterministic
      • OLAP functions
      • Recursive common table expressions
      • References to global variables
      • References to nicknames
      • References to special registers
      • Sampling functions
      • SELECT DISTINCT statements
      • Subqueries
      • Text functions
  • If you specify the REFRESH IMMEDIATE option for the CREATE TABLE statement and the fullselect contains a GROUP BY clause, the following conditions and restrictions apply:
    • The select list must contain the COUNT() or COUNT_BIG() function.
    • For each nullable column C, if the select list contains the SUM(C) function, the COUNT(C) function is also required.
    • You must include the SUM() or GROUPING() aggregate function. You cannot include any other aggregate function.
    • You cannot specify the HAVING clause.
    • For partitioned database environments, the GROUP BY columns must contain the partitioning key of the materialized query table.
    • Nesting of aggregate functions is not allowed.
  • If you specify the REFRESH IMMEDIATE option for the CREATE TABLE statement and the FROM clause references more than one table, an inner join that does not use the explicit INNER JOIN syntax is the only type of join that is supported.
  • If you specify the DISTRIBUTED BY REPLICATION option for the CREATE TABLE statement, the following restrictions apply:
    • Aggregate functions and the GROUP BY clause are not allowed.
    • The MQT must reference only a single table. It cannot include a join, union, or subquery.
    • You cannot specify the PARTITIONING KEY clause.
    • Unique indexes are not allowed for system maintained MQTs.
  • To create a user-maintained MQT that has a column-organized table as the base table, you must specify the MAINTAINED BY USER clause for the CREATE TABLE statement.
Column-organized MQT and shadow tables are subject to the following additional restrictions:
  • Column-organized MQTs are not supported in Db2® pureScale® environments.
  • An MQT other than a shadow table must reference a table with the same organization as the MQT.
  • You must specify the ORGANIZE BY COLUMN clause when creating a column-organized MQT.
  • The MAINTAINED BY SYSTEM clause is supported for column-organized MQTs only when defined with REFRESH DEFERRED and the DISTRIBUTE BY REPLICATION options.
Shadow tables are subject to the following additional restrictions:
  • MAINTAINED BY REPLICATION must be specified.
  • REFRESH DEFERRED must be specified.
  • REFRESH IMMEDIATE is unsupported.
  • A primary key must be created on the shadow table and must correspond to a primary key or unique constraint on the base table.
  • The CREATE TABLE statement or the fullselect must not reference any of the following object types:
    • Partitioned tables.
    • Multidimensional clustering (MDC) tables.
    • Range-clustered tables (RCTs).
    • Tables containing LONG VARCHAR or LONG VARGRAPHIC columns. These data types have been deprecated since Version 9.5 Fix Pack 1.
    • Tables that are protected with row and column access control (RCAC) or label-based access control (LBAC).
    • Temporal tables.
  • A permission or mask cannot reference a shadow table.
  • A base table can be referenced by only one shadow table.
  • The following restrictions apply to the fullselect in a shadow table definition:
    • The fullselect can reference only one base table; joins are not supported.
    • The base table must be a row-organized table.
    • The subselect can contain only a select-clause and a from-clause.
    • The projection list of the shadow table can reference only base table columns that are valid in a column-organized table. Expressions are not supported in the projection list. You cannot rename the columns that are referenced in the projection list by using the column list or the AS clause.
    • The projection list of the shadow table must include at least one set of enforced unique constraint or primary key columns from the base table.
    • The fullselect cannot include references to a nickname, a typed table, or a view or contain the SELECT DISTINCT clause.
  • A Materialized query table may not be created as a random distribution table.