DB2 10.5 for Linux, UNIX, and Windows

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

Set the SORTHEAP database configuration parameter and the SHEAPTHRES_SHR database configuration parameter to a value other than AUTOMATIC.
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.
The database must be single partition and use the UNICODE code set and IDENTITY collation.
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 creating 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:

Issue the CREATE TABLE statement, specifying the ORGANIZE BY COLUMN clause and creating a primary key on the column-organized table.
Populate the table by issuing the LOAD command.
Important: When populating a column-organized table with data for the first time, it is strongly recommended that the majority of the data be added by using a single large load operation, because the column compression dictionaries are created on the basis of the first table load operation.

Example

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

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;

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';