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.
  • Column-organized tables cannot be the source for data replication (DATA CAPTURE CHANGES not allowed), unless you are using IBM Integrated Analytics System (IIAS) and have enabled IBM Data Replication for Db2 Continuous Availability.
  • 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 BLOB, CLOB, DBCLOB, NCLOB or XML data types 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.