Creating tables from application programs

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

Procedure

To create a table:

Use a CREATE TABLE statement that includes the following elements:
  • The name of the table. See Guidelines for table names.
  • A list of the columns that make up the table. Separate each column description from the next with a comma, and enclose the entire list of column descriptions in parentheses.

    For each column, specify the following information:

    • The name of the column (for example, SERIAL). See Column names.
    • The data type and length attribute (for example, CHAR(8)). See Data types of columns.
    • Optionally, specify a default value, or a constraint on the value. You can use the following values:
      Keyword Result
      NOT NULL Specifies the column cannot contain null values
      UNIQUE The value for each row must be unique, and the column cannot contain null values.
      DEFAULT The column has one of the following Db2-assigned default values:
      • 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 The default value is specified as one of the following values:
      • 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
    • Optionally, specify the partitioning method for the data in the table. Db2 uses size-based partitions by default if you do not specify how to partition the data when you create the table. For more information, see Partitioning data in Db2 tables.
    • Optionally, a referential constraint or check constraint. For more information, see Check constraints and Referential constraints.

Example

For 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);