Creating column-organized tables

Create column-organized tables to store the data from a single column together on a set of data pages instead of storing the data for complete rows together on a set of data pages.

Before you begin

  1. Set the SORTHEAP database configuration parameter and the SHEAPTHRES_SHR database configuration parameter to a value other than AUTOMATIC.
  2. Set the DB2_WORKLOAD registry variable to ANALYTICS before you create the database. This setting establishes an optimal default configuration when you use the database for analytic workloads. For more information, see System environment variables.
  3. The database can be single partition or multi-partition, use the UNICODE or ISO8859-1 (Codepage 819) code set, and IDENTITY or IDENTITY_16BIT collation.
  4. To address the resource needs of the LOAD command, set the util_heap_sz (utility heap size) database configuration parameter to at least 1,000,000 pages and AUTOMATIC.

About this task

By creating column-organized tables in star schema data marts, you can benefit from significant improvements to storage, query performance, and ease of use through simplified design and tuning.


Restrictions

  • Review any applicable restrictions in CREATE TABLE statement.
  • Review the page size-specific limits for column-organized tables in Database manager page size-specific limits.
  • Constraints
    • ENFORCED check and foreign key (referential integrity) constraints are not supported on column-organized tables. These constraints are supported as informational (NOT ENFORCED) constraints.
    • You cannot specify the WITH CHECK OPTION when you create a view that is based on column-organized tables.

Creating column-organized tables might result in the automatic creation of tables that store metadata.

Procedure

To create a column-organized table:

  1. Issue the CREATE TABLE statement, specifying the ORGANIZE BY COLUMN clause and creating a primary key on the column-organized table.
  2. Populate the table by running the LOAD command.
    Important: When you populate a column-organized table with data for the first time, it is recommended that most of the data is added by using a single large load operation. The single large load is needed because the column compression dictionaries are created based on the first table load operation.

Example

The following example shows you how to create a column-organized table called JTNISBET.STAFF.

  1. Issue the CREATE TABLE statement as follows:
    CREATE TABLE JTNISBET.STAFF (
  ID SMALLINT NOT NULL,
  NAME VARCHAR(9),
  DEPT SMALLINT,
  JOB CHAR(5),
  YEARS SMALLINT,
  SALARY DECIMAL(7,2),
  COMM DECIMAL(7,2) )
 ORGANIZE BY COLUMN;
  2. Populate the JTNISBET.STAFF table with data by using the LOAD command, as in the following example:
    LOAD FROM /TEST/DATA.DEL OF DEL MODIFIED BY COLDEL,
     REPLACE INTO JTNISBET.STAFF;
The following query returns a list of all column-organized tables in the database:
SELECT
    SUBSTR(TABNAME, 1, 24) AS TABNAME,
    SUBSTR(TABSCHEMA, 1, 24) AS TABSCHEMA
FROM SYSCAT.TABLES
WHERE TABLEORG = 'C';