Defining tables
Designing tables that many applications will use is a critical task. Table design can be difficult because you can represent the same information in many different ways. “Chapter 4. Designing objects and relationships” covers some of the issues that you need to consider when you make decisions about table design.
You create tables by using the SQL CREATE TABLE statement. At some point after you create and start using your tables, you might need to make changes to them. The ALTER TABLE statement lets you add and change columns, add or drop a primary key or foreign key, add or drop table check constraints, or add and change partitions. Carefully consider design changes to avoid or reduce the disruption to your applications. 
If you have DBADM (database administration) authority, you probably want to control the creation of DB2 databases and table spaces. These objects can have a big impact on the performance, storage, and security of the entire relational database. In most cases, you also want to retain the responsibility for creating tables. After designing the relational database, you can create the necessary tables for application programs. You can then pass the authorization for their use to the application developers, either directly or indirectly, by using views.
However, if you want to, you can grant the authority for creating tables to those who are responsible for implementing the application. For example, you probably want to authorize certain application programmers to create tables if they need temporary tables for testing purposes.
Some users in your organization might want to use DB2 with minimum assistance or control. You can define a separate storage group and database for these users and authorize them to create whatever data objects they need, such as tables. You can read more about authorization in “Authorizing users to access data” on page 328.
Types of tables
In DB2, you store user data in tables. DB2 supports the following types of tables:
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.
A table that contains a set of rows that DB2 selects or generates, directly or indirectly, from one or more base tables.
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.
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's instance of TEMP1.
A table that you define with the SQL CREATE TABLE statement. 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 (which you can read about in “Defining a materialized query table” on page 222).
A special kind of table that holds only large object data. You can read more about auxiliary tables in “Defining large objects” on page 268.
Base tables, temporary tables, and materialized query tables differ in many ways that this book does not describe. 
Table definitions
The table name is an identifier of up to 128 characters. You can qualify the table name with an SQL identifier, which is a schema. Remember that most organizations have naming conventions to ensure that objects are named in a consistent manner. When you define a table that is based directly on an entity, these factors also apply to the table names.
You can create base tables, temporary tables, auxiliary tables, or materialized query tables. You can read about creating auxiliary tables in “Defining large objects” on page 268. You can read about creating materialized query tables in “Defining a materialized query table” on page 222.
Defining a base table
To create a base table that you have designed, use the CREATE TABLE statement. When you create a table, DB2 records a definition of the table in the DB2 catalog. Creating a table does not store the application data. You can put data into the table by using several methods, such as the LOAD utility or the INSERT statement.
Example: The following CREATE TABLE statement creates the EMP table, which is in a database named MYDB and in a table space named MYTS:
CREATE TABLE EMP
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
DEPT CHAR(3) ,
HIREDATE DATE ,
JOB CHAR(8) ,
EDL SMALLINT ,
SALARY DECIMAL(9,2) ,
COMM DECIMAL(9,2) ,
PRIMARY KEY (EMPNO))
IN MYDB.MYTS;
The preceding CREATE TABLE statement shows the definition of multiple columns. You will learn about column definition in more detail in “Defining columns and rows in a table” on page 223.
Defining a temporary table
Temporary tables are especially useful when you need to do both of the following activities:
Sort or query intermediate result tables that contain large numbers of rows
Identify a small subset of rows to store permanently
You can use temporary tables to sort large volumes of data and to query that data. Then, when you have identified the smaller number of rows that you want to store permanently, you can store them in a base table. The two types of temporary tables in DB2 are the created temporary table and the declared temporary table. The following sections describe how to define each type.
Defining a created temporary table
Sometimes you need a permanent, sharable description of a table but need to store data only for the life of an application process. In this case, you can define and use a created temporary table. DB2 does not log operations that it performs on created temporary tables, so SQL statements that use them can execute more efficiently. Each application process has its own instance of the created temporary table.
Example: The following statement defines a created temporary table, TEMPPROD:
CREATE GLOBAL TEMPORARY TABLE TEMPPROD
(SERIALNO CHAR(8) NOT NULL,
DESCRIPTION VARCHAR(60) NOT NULL,
MFGCOSTAMT DECIMAL(8,2) ,
MFGDEPTNO CHAR(3) ,
MARKUPPCT SMALLINT ,
SALESDEPTNO CHAR(3) ,
CURDATE DATE NOT NULL);
Defining a declared temporary table
Sometimes you need to store data for the life of an application process, but you don't need a permanent, sharable description of the table. In this case, you can define and use a declared temporary table.
Unlike other DB2 DECLARE statements, DECLARE GLOBAL TEMPORARY TABLE is an executable statement that you can embed in an application program or issue interactively. You can also dynamically prepare the statement.
When a program in an application process issues a DECLARE GLOBAL TEMPORARY TABLE statement, DB2 creates an empty instance of the table. You can populate the declared temporary table by using INSERT statements, modify the table by using searched or positioned UPDATE or DELETE statements, and query the table by using SELECT statements. You can also create indexes on the declared temporary table. The definition of the declared temporary table exists as long as the application process runs.
Example: The following statement defines a declared temporary table, TEMP_EMP. (This example assumes that you have already created the TEMP database and corresponding table space for the temporary table.)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP
(EMPNO CHAR(6) NOT NULL,
SALARY DECIMAL(9,2) ,
COMM DECIMAL(9,2));
All names of declared temporary tables must have SESSION as a qualifier.
At the end of an application process that uses a declared temporary table, DB2 deletes the rows of the table and implicitly drops the description of the table.
Defining a materialized query table
Materialized query tables improve the performance of complex queries that operate on very large amounts of data. Using a materialized query table, DB2 pre-computes the results of data that is derived from one or more tables. When you submit a query, DB2 can use the results stored in a materialized query table rather than compute the results from the underlying source tables on which the materialized query table is defined. If the rewritten query is less costly, DB2 chooses to optimize the query by using the rewritten query, a process called automatic query rewrite. 
To take advantage of automatic query rewrite, you must define, populate, and periodically refresh the materialized query table. You use the CREATE TABLE statement to create a new table as a materialized query table.
Example: The following CREATE TABLE statement defines a materialized query table named TRANSCNT. TRANSCNT summarizes the number of transactions in table TRANS by account, location, and year:
CREATE TABLE TRANSCNT (ACCTID, LOCID, YEAR, CNT) AS
(SELECT ACCOUNTID, LOCATIONID, YEAR, COUNT(*)
FROM TRANS
GROUP BY ACCOUNTID, LOCATIONID, YEAR )
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION;
The fullselect, together with the DATA INITIALLY DEFERRED clause and the REFRESH DEFERRED clause, defines the table as a materialized query table.
Defining a table with table-controlled partitioning
Before Version 8, when you defined a partitioning index on a table in a partitioned table space, you specified the partitioning key and the limit key values in the PART clause of the CREATE INDEX statement. This type of partitioning is known as index-controlled partitioning. Because the index is created separately from the associated table, you cannot insert data into the table until the partitioning index is created.
Version 8 introduces a new method, called table-controlled partitioning, for defining table partitions. You can use table-controlled partitioning instead of index-controlled partitioning. However, DB2 supports both methods in Version 8.
With table-controlled partitioning, you identify column values that delimit partition boundaries with the PARTITION BY clause and the PARTITION ENDING AT clause of the CREATE TABLE statement. When you use this type of partitioning, an index is not required for partitioning.
Example: Assume that you need to create a large transaction table that includes the date of the transaction in a column named POSTED. You want to keep the transactions for each month in a separate partition. To create the table, use the following statement:
CREATE TABLE TRANS
(ACCTID ...,
STATE ...,
POSTED ...,
... , ...)
PARTITION BY (POSTED)
(PARTITION 1 ENDING AT ('01/31/2003'),
PARTITION 2 ENDING AT ('02/28/2003'),
...
PARTITION 13 ENDING AT ('01/31/2004'));