Column-organized tables

DB2® column-organized tables add columnar capabilities to DB2 databases, which include data that is stored with column organization and vector processing of column data. Using this table format with star schema data marts provides significant improvements to storage, query performance, and ease of use through simplified design and tuning.

If most tables in your database are going to be column-organized tables, set the DB2_WORKLOAD registry variable to ANALYTICS before you create the database. Doing so helps to configure memory, table organization, page size, and extent size, and enables workload management. For more information about this setting, see "System environment variables".

If the workload is entirely an analytics or OLAP workload, the recommended approach is to put as many tables as possible into column-organized format. These workloads are characterized by nonselective data access (that is, queries access more than approximately 5% of the data), and extensive scanning, grouping, and aggregation.

The process of inserting data into column-organized tables, or updating previously inserted data, is optimized for large insert transactions. As a result, workloads that are transactional in nature should not use column-organized tables. Traditional row-organized tables with index access are generally better suited for these types of workloads. With column-organized tables, performance is optimal if 100 or more rows are impacted by each insert or update transaction.

For mixed workloads, which include a combination of analytic query processing and very selective access (involving less than 2% of the data), a mix of row-organized and column-organized tables might be suitable.

The process of loading data has some differences for column-organized tables. For more information, see Loading data into column-organized tables.

You can establish a configuration that is optimal for analytic workloads and set the default table organization to COLUMN automatically by setting the DB2_WORKLOAD registry variable to ANALYTICS.

If you cannot create your database and have it auto-configured by setting the DB2_WORKLOAD registry variable to ANALYTICS, you must create your database and optimally configure it for analytic workloads. For more information, see Creating and setting up your database configuration for analytic workloads.

Support for column-organized tables is available in specific system and database configurations. For more information, see Supported system and database configurations for column-organized tables.

Parallel processing must be enabled to access and query column-organized tables. For more information, see Enabling parallel processing for column-organized tables.

To create a column-organized table, you can specify the ORGANIZE BY COLUMN clause on the CREATE TABLE statement. Alternatively, if you want to create tables with a specific table organization without having to specify the ORGANIZE BY COLUMN or the ORGANIZE BY ROW clause, you can change the default table organization by setting the dft_table_org database configuration parameter.

On Windows operating systems, if you are running more than one socket, set performance variable DB2_RESOURCE_POLICY to AUTOMATIC.