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 analytic 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.

If the workload is transactional in nature, traditional row-organized tables with index access are more suitable.

For mixed workloads, which include a combination of analytic query processing and 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 Restrictions, limitations, and unsupported database configurations for column-organized tables.

Support for column-organized tables is also available in partitioned database environments. For more information, see Column-organized tables in partitioned database environments.

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, you can create tables with a specific table organization without having to specify the ORGANIZE BY COLUMN or the ORGANIZE BY ROW clause. To create these tables, 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.