Creating tables

Creating a table provides a logical place to store related data on a DB2® subsystem.

About this task

To create a table, use a CREATE TABLE statement that includes the following elements:
  • The name of the table
  • A list of the columns that make up the table. For each column, specify the following information:
    • The column's name (for example, SERIAL).
    • The data type and length attribute (for example, CHAR(8)).
    • Optionally, a default value.
    • Optionally, a referential constraint or check constraint.
    Separate each column description from the next with a comma, and enclose the entire list of column descriptions in parentheses.
Example: The following SQL statement creates a table named PRODUCT: 
CREATE TABLE PRODUCT
  (SERIAL       CHAR(8)     NOT NULL,
   DESCRIPTION  VARCHAR(60) DEFAULT,
   MFGCOST      DECIMAL(8,2),
   MFGDEPT      CHAR(3),
   MARKUP       SMALLINT,
   SALESDEPT    CHAR(3),
   CURDATE      DATE        DEFAULT);

For more information about referential constraints, see Referential constraints

For more information about check constraints, see Check constraints.

Identifying column defaults and constraining column inputs:
If you want to constrain the input or identify the default of a column, you can use the following values:
  • NOT NULL, when the column cannot contain null values.
  • UNIQUE, when the value for each row must be unique, and the column cannot contain null values.
  • DEFAULT, when the column has one of the following DB2-assigned defaults:
    • For numeric columns, 0 (zero) is the default value.
    • For character or graphic fixed-length strings, blank is the default value.
    • For binary fixed-length strings, a set of hexadecimal zeros is the default value.
    • For variable-length strings, including LOB strings, the empty string (a string of zero-length) is the default value.
    • For datetime columns, the current value of the associated special register is the default value.
  • DEFAULT value, when you want to identify one of the following values as the default value:
    • A constant
    • NULL
    • SESSION_USER, which specifies the value of the SESSION_USER special register at the time when a default value is needed for the column
    • CURRENT SQLID, which specifies the value of the CURRENT SQLID special register at the time when a default value is needed for the column
    • The name of a cast function that casts a default value (of a built-in data type) to the distinct type of a column
  • Data types
    When you create a DB2 table, you define each column to have a specific data type. The data type of a column determines what you can and cannot do with the column.
  • Storing LOB data in a table
    DB2 handles LOB data differently than it handles other kinds of data. As a result, in some cases, you need to take additional actions when you define LOB columns and insert the LOB data.
  • Identity columns
    An identity column contains a unique numeric value for each row in the table. DB2 can automatically generate sequential numeric values for this column as rows are inserted into the table. Thus, identity columns are ideal for primary key values, such as employee numbers or product numbers.
  • Creating tables for data integrity
    To ensure that only valid data is added to your tables, you can use constraints, triggers, and unique indexes. For example, you might need to ensure that all items in your inventory table have valid item numbers and to prevent items without valid item numbers from being added.
  • Creating work tables for the EMP and DEPT sample tables
    Before testing SQL statements that insert, update, and delete rows in the DSN8A10.EMP and DSN8A10.DEPT sample tables, you should create duplicates of these tables. Create duplicates so that the original sample tables remain intact. These duplicate tables are called work tables.
  • Creating created temporary tables
    Use created temporary tables when you need to store data for only the life of an application process, but you want to share the table definition. DB2 does not perform logging and locking operations for created temporary tables. Therefore, SQL statements that use these tables can execute queries efficiently.
  • Creating declared temporary tables
    Use declared temporary tables when you need to store data for only the life of an application process and do not need to share the table definition. The definition of this table exists only while the application process runs. DB2 performs limited logging and locking operations for declared temporary tables.
Parent topic: Creating and modifying DB2 objects from application programs
Related reference:
CREATE TABLE

Related information

Implementing DB2 tables