In DB2®, you store user data in tables. DB2 supports several types of tables, each of which has its own purpose and characteristics.

DB2 supports the following types of tables:

base table

The most common type of table in DB2. You create a base table with the SQL CREATE TABLE statement. The DB2 catalog table, SYSIBM.SYSTABLES, stores the description of the base table. The table (both its description and its data) is persistent. All programs and users that refer to this type of table refer to the same description of the table and to the same instance of the table.

result table

A table that contains a set of rows that DB2 selects or generates, directly or indirectly, from one or more base tables in response to an SQL statement. Unlike a base table or a temporary table, a result table is not an object that you define using a CREATE statement.

temporary table

A table that is defined by the SQL statement CREATE GLOBAL TEMPORARY TABLE or DECLARE GLOBAL TEMPORARY TABLE to hold data temporarily. Temporary tables are especially useful when you need to sort or query intermediate result tables that contain a large number of rows, but you want to store only a small subset of those rows permanently.

created global temporary table

A table that you define with the SQL CREATE GLOBAL TEMPORARY TABLE statement. The DB2 catalog table, SYSIBM.SYSTABLES, stores the description of the created temporary table. The description of the table is persistent and sharable. However, each individual application process that refers to a created temporary table has its own distinct instance of the table. That is, if application process A and application process B both use a created temporary table named TEMPTAB:

• Each application process uses the same table description.
• Neither application process has access to or knowledge of the rows in the other's instance of TEMPTAB.

declared global temporary table

A table that you define with the SQL DECLARE GLOBAL TEMPORARY TABLE statement. The DB2 catalog does not store a description of the declared temporary table. Therefore, neither the description nor the instance of the table is persistent. Multiple application processes can refer to the same declared temporary table by name, but they do not actually share the same description or instance of the table. For example, assume that application process A defines a declared temporary table named TEMP1 with 15 columns. Application process B defines a declared temporary table named TEMP1 with 5 columns. Each application process uses its own description of TEMP1; neither application process has access to or knowledge of rows in the other application's instance of TEMP1.

materialized query table

A table, which you define with the SQL CREATE TABLE statement, that contains materialized data that is derived from one or more source tables. Materialized query tables are useful for complex queries that run on very large amounts of data. DB2 can precompute all or part of such queries and use the precomputed, or materialized, results to answer the queries more efficiently. Materialized query tables are commonly used in data warehousing and business intelligence applications.

Several DB2 catalog tables, including SYSIBM.SYSTABLES and SYSIBM.SYSVIEWS, store the description of the materialized query table and information about its dependency on a table, view, or function. The attributes that define a materialized query table tell DB2 whether the table is:

• System-maintained or user-maintained.
• Refreshable: All materialized tables can be updated with the REFRESH TABLE statement. Only user-maintained materialized query tables can also be updated with the LOAD utility and the UPDATE, INSERT, and DELETE SQL statements.
• Enabled for query optimization: You can enable or disable the use of a materialized query table in automatic query rewrite.

auxiliary table

A special kind of table that holds large object data and XML data.

clone table

A table that is structurally identical to a base table. You create a clone table by using an ALTER TABLE statement for the base table that includes an ADD CLONE clause. The clone table is created in a different instance of the same table space as the base table, is structurally identical to the base table in every way, and has the same indexes, before triggers, and LOB objects. In the DB2 catalog, the SYSTABLESPACE table indicates that the table space has only one table in it, but SYSTABLESPACE.CLONE indicates that a clone table exists. Clone tables can be created only in a range-partitioned or partition-by-growth table space that is managed by DB2. The base and clone table each have separate underlying VSAM data sets (identified by their data set instance numbers) that contain independent rows of data.

XML table

A special kind of table that holds only XML data. When you create a table with an XML column, DB2 implicitly creates an XML table space and an XML table to store the XML data.

These different types of tables differ in other ways that this topic does not describe.