DB2 10.5 for Linux, UNIX, and Windows

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.

The synopsis table contains all the user table's non-character columns (that is, datetime, Boolean, or numeric columns) and those character columns that are part of a primary key or foreign key definition. As of DB2® Cancun Release 10.5.0.4, the synopsis table for a new column-organized table also includes CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC columns.

The synopsis table stores the minimum and maximum values for each column across a range of rows and uses those values to skip over data that is of no interest to a query during evaluation of certain type of predicates (=, >, >=, <, <=, BETWEEN, NOT BETWEEN, IS NOT NULL, IN, and NOT IN).

The only supported operation against a synopsis table is a select operation. To determine the name of the synopsis table for a particular user table, query the SYSCAT.TABLES catalog view, as shown in the following example:

SELECT tabschema, tabname, tableorg
  FROM syscat.tables
  WHERE tableorg = 'C';

TABSCHEMA    TABNAME                         TABLEORG
------------ ------------------------------- --------
DB2INST1     SALES_COL                       C
SYSIBM       SYN130330165216275152_SALES_COL C

The synopsis table has schema SYSIBM.

The relationship between a user table and its synopsis table is recorded in the SYSCAT.TABDEP catalog view. You can query this catalog view for details, as shown in the following example:

SELECT tabname, bname
  FROM syscat.tabdep
  WHERE dtype = '7';