Restrictions, limitations, and unsupported database configurations for column-organized tables

Before you start using column-organized tables, ensure that you have a system with functionality that is compatible.

The database configurations and environment must support column-organized tables. Column-organized tables:
  • Cannot be created in databases in a Db2® pureScale® environment.
  • Must be created in an automatic storage table space that supports reclaimable storage.
  • Must be created in a databases whose code set and collation is UNICODE or ISO8859-1 (Codepage 819) and IDENTITY or IDENTITY_16BIT.
  • Cannot be accessed nor created in XA transactions.
  • Do not support RS or RR isolation levels.
  • Cannot be used with automatic tuning of sort memory.
The following additional restrictions apply to column-organized tables:
  • Schemas that include column-organized tables cannot be transported.
  • Event monitors cannot write results to column-organized tables.
  • Created global temporary tables cannot be column-organized.
  • Declared global temporary tables using NOT LOGGED ON ROLLBACK PRESERVE ROWS cannot be column-organized.
  • Section actuals are not available for column-organized tables.
  • Indexes can be explicitly created on column-organized tables, with a few restrictions:
    • For a list of unsupported clauses in the CREATE INDEX statement, refer to the Rules section for that statement.
    • Concurrent write operations against column-organized tables are not allowed for the CREATE INDEX statement and the REORG INDEX command with REBUILD mode.
    • Some types of index scans are not supported against column-organized tables. For a complete list, see Explain information for column-organized tables.
  • Text indexes cannot be created on column-organized tables.
  • Triggers cannot be created on or be a reference to column-organized tables.
  • If you are using a version of Db2 prior to version 11.5.4, column-organized tables cannot be used as the source for Q Replication (DATA CAPTURE CHANGES are not allowed), unless you are using IBM Integrated Analytics System (IAS) and have enabled IBM Data Replication for Db2 Continuous Availability. For more information on Q Replication, refer to the IBM Data Replication Community Wiki. In Db2 version 11.5.4 and later, column-organized tables can be used as the source for data Q Replication (DATA CAPTURE CHANGES are allowed). For more information on how to replicate column-organized tables (and to turn on DATA CAPTURE CHANGES in Db2), refer to Replicating transactions on Db2 column-organized tables with Db2 V11.5.
  • Label-based access control (LBAC) cannot be used with column organized tables.
  • The RECLAIM EXTENTS parameter is the only REORG TABLE parameter that is supported for column-organized tables.
  • Positioned-delete and update statements are not supported for column-organized tables.
  • The following restrictions apply to a column-organized MQT:
    • MQT's other than shadow tables must reference tables with the same organization as the MQT.
    • The ORGANIZE BY COLUMN must be specified when creating a column-organized MQT, even if the dft_table_org database configuration parameter is set to COLUMN.
    • For a column-organized MQT, the following tables are supported:
      • Shadow tables
      • User-maintained MQTs
      • System-maintained MQTs that are defined with the REFRESH DEFERRED and DISTRIBUTE BY REPLICATION clauses.
  • A column-organized table cannot be a:
    • range-partitioned table
    • multi-dimensional clustered table
    • typed table
  • Columns with the XML data type cannot be included in a column-organized table.
  • Columns with the LONG VARCHAR or LONG VARGRAPHIC data types cannot be included in a column-organized table.
  • Generated columns cannot be added to existing column-organized tables.
  • Columns in a column-organized table cannot be dropped. Columns in a column-organized table cannot be altered except to increase the length of a VARCHAR or VARGRAPHIC column.
  • Enforced check and foreign key constraints are not supported by column-organized tables.
  • The ROW CHANGE TIMESTAMP generated column option cannot be specified for columns in a column-organized table.
  • ISOLATION LEVEL UR could behave differently on Column-organized tables and Row-organized tables.