DB2 10.5 for Linux, UNIX, and Windows

Table organization

You can store the data for your tables with column or row organization. Choosing the right table format provides significant improvements to storage, query performance, and tuning. You can use column-organized tables, row-organized tables, or a mix of both to better fit the type of workload.

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.
Synopsis tables
A synopsis table is a column-organized table that is automatically created and maintained by the system to store metadata for an associated user-defined column-organized table.
Supported system and database configurations for column-organized tables
Before you start using column-organized tables, ensure that you have a system and database configuration that is supported.
Creating and setting up your database configuration for analytic workloads
If DB2_WORKLOAD is not set to =ANALYTICS, you should create and optimally configure your database for analytic workloads.
Enabling parallel processing for column-organized tables
Certain operations on column-organized tables such as access and query processing, require enabling of intraquery parallelism and intrapartition parallelism.
Setting the default table organization
You can set the default table organization for user-defined tables to row or column. The default table organization is used when you create tables without specifying the ORGANIZE BY COLUMN or the ORGANIZE BY ROW clause for the CREATE TABLE statement. The default table organization setting is row.
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.
INSERT, UPDATE, and DELETE (including MERGE) statement restrictions for column-organized tables
Some options on the INSERT, UPDATE, and DELETE statements are not supported for column-organized tables in DB2Version 10.5.
Loading data into column-organized tables
Although loading data into column-organized tables is very similar to loading data into row-organized tables, you should be aware of the few exceptions and configure your database to handle the additional resources that are required.
Scenario: Achieving high speed analytics over growing volumes of column-organized data
This scenario describes an active data warehouse that is used for reporting and analysis. The scenario is built by using an IBM® Cognos® and DB2 stack on Intel hardware. The three-tier stack includes a web interface front end, Cognos Business Intelligence (BI) 10.2, a BI server with Cognos Dynamic Cubes running on the middle tier, and a relational database tier that uses DB2 Version 10.5.

Related concepts:

Tables