Table Create Option

The Table Create screen automatically generates all statements with the SQL CREATE TABLE statement including:
The Table Create screen automatically generates all statements associated with the SQL CREATE TABLE statement including:
From the Table Create screen, you can:
  • Use object selection lists to retrieve the name of the database or tablespace.
  • Create auxiliary tables for LOB columns by specifying the AUXILIARY option in the Table Type field.
  • Copy, replicate, delete, and move columns within the table definition and copy column definitions into the table from other tables.
  • View the affects that different data type decisions will have on the total row size. A dynamic row size indicator appears showing the current row size in the Row Size field.
  • Column Explode and Column Type screens are provided for entering column data.
  • Create primary and foreign key rules as part of the table create (or table alter) process. Table and column selection panels make selecting the right columns easy.
When you finish creating your table, execute, edit, or cancel the DDL.
Create a Table
The Table Create facility offers help in creating a table accurately and efficiently. However, these screens can camouflage the easy process of creating a table. This section summarizes the required and optional steps for creating a table.
Follow these steps:
  1. Go to the Table Create screen.
  2. Enter data in the required table information fields (Table and Creator), and optionally update other fields.
  3. Enter the required column fields (Column Name and Column Type), and optionally update other fields.
  4. Press F3 (End) to process the creation.
Table Definition
The table definition fields appear on the top part of the Table Create screen (Table Create). These fields determine the storage characteristics of the table as well as any table comments or labels. You can temporarily hide these fields using the H (Header) command. If the H (Header) command is used, only the Column Definition section appears. For more information about the fields, press F1 (Help).
You can also specify whether the logging of SQL INSERT, UPDATE, and DELETE operations on the table is augmented by additional information.
Column Definition
The Column Definition section of the Table Create screen defines the attributes of each column in the table.
The Table Column List is the scrollable region on the Table Alter, Table Create (see Table Create for an example), and Table Template screens. It lets you maintain the columns in your table, insert new columns, and delete existing columns using simple line commands and by directly entering data into the displayed fields within the list. You can define up to 750 columns per table.
If you are creating, altering, or templating a materialized query table (MQT), a column list is not available. However, to view or change column list information, you can change Table Type to REGULAR. When you are finished, you can enter MQT for Table Type to redisplay the MQT panel.
From the Table Column List, you can:
  • Specify line commands to define your columns. For detailed information about valid commands, see the online help.
  • Specify the numeric place of the column in the table.
  • Designate the unique name for the column. You can use column selection criteria to retrieve column names from other tables.
  • Identify whether the column is a built-in or user-defined data type (UDT). To select from a list, enter selection criteria. Masking can be used. For a detailed list of valid types, see the online help.
  • Specify the length of the column for DECIMAL, CHAR, VARCHAR, GRAPHIC, VARG, BLOB, CLOB, and DBCLOB data types. For DECIMAL, this value is its precision.
    The Scale of the column is also displayed in this field, following the size.
    Indicate whether the column can contain a null value. To access a selection list, enter
    in the N column.
  • Indicate the type of default to be used. Valid values depend on the columns data type and the setting of the null indicator (N column). To access a selection list, enter
    in the D column.
  • Specify a subtype for a character string column. This option only applies to columns whose data types are CHAR, VARCHAR, and LONG VARCHAR. For more information about valid values, see the IBM DB2
    SQL Reference
  • Indicate the position of the column if the column is part of a Primary key.
The following fields can be viewed using the horizontal scrolling keys. If the horizontal scrolling mode is set to Column (the default), the Comments field will not be displayed until the entire field can be displayed.
  • Schema
    Specifies the high level qualifier of the column type (see the Column Type field). If Column Type is DISTINCT, this displays the schema of the distinct type. Otherwise, the value is SYSIBM. When creating a table, the initial default value for schema can be set using the DEF (object definition defaults) primary command.
  • Label
    Contains any label information (up to 30 characters) for the column. The default is none.
  • Comments 1-50
    Column comment information, up to 50 characters. To edit or enter more than the 50 characters displayed on the Table Screen, use the E (EXPLODE) line command. The Explode Screen lets you enter and edit the full 254 characters supported by DB2. Any editing changes made in this field affect the first 50 characters of the comment. All other characters after the first 50 characters are not affected. The default is none.
    Specifies the name of an edit routine that transforms or translates a field to or from its stored condition. If blank, the column does not have a field procedure. You can add or alter this field. The default is none.
  • FLDPARM 1-50
    Field procedure parameters. You can enter or modify a parameter list that will be passed to the FLDPROC routine. Allows entry of up to 50 characters as the parms to pass to the field procedure. This field can be up to 254 characters. See the Comments 1-50 field description for more information about entering more than 50 characters of information.
Column Default Value Screen
The Column Default Value Specifications screen lets you specify an initial (default) value for a new column on an altered or newly created table. The value you specify is dependent on the size of the column and the column's data type. User-defined data types are supported.
A default value can also be specified for an existing column when the Column Default Indicator (D field) is set to
(Constant). For all Default Types (with the exception of C), the default value specified is used only when the table is unloaded. When the default type is C, the value specified is used by DB2 and will remain in effect for new rows inserted and all existing rows updated.
To access the Column Default Value Specifications screen, enter
in the CMD field for a newly created column on the Table Create, Table Template, or Table Alter screen.
To provide additional information for user-defined data types (UDTs), this screen also displays the source schema, data type (including length and for decimal values scale), null indicator, and default type.
Inserting Columns from Other Tables
You can insert columns from other tables, even if the column or table name is unknown. You can list the columns currently defined in the SYSIBM.SYSCOLUMNS table.
Enter selection criteria to limit the number of columns displayed. If a column is selected for inclusion into the new table, all attributes of the columns are copied into the table definition. The column copy feature makes it very easy to create standard column definitions that are reused by different tables.
Insert a New Column
You can insert a column.
Follow these steps:
  1. Insert a new column (or overwrite an existing column) on the Table Create, Alter, or Template screens.
  2. Type in the column name.
    Press Enter.
    The Column Type selection panel appears. The possible data values are displayed. This information can be different depending on what version of DB2 you are running. The Creator/Schema type defaults to SYSIBM.
    For a description of the fields and possible column types, see Column Definition.
  3. Select a column type and press Enter.
    A pop-up dialog box provides you with possible ranges and default values for the column type you selected.
  4. Enter the column size and press Enter.
Insert a User-Defined Column
You can insert a user-defined column.
Follow these steps:
  1. Insert a new column (or overwrite an existing column) from the Table Create, Table Alter, or Table Template screens.
  2. Specify a new column name and press Enter. The Column Type selection panel appears. All column type values default to those provided within SYSIBM.
  3. In the Creator field, replace SYSIBM with an asterisk (*) and press Enter.
  4. Press F8 (Down) to scroll past the SYSIBM defaults to view valid user-defined data types.
    For all user-defined data types, the schema, source schema, source type, and owner are displayed.
  5. Select the user defined data type you want to use for your column type.
Confirming the Table Creation
When all information has been entered, press F3 (End). A Creation confirmation screen will appear. Accept, edit, or reject the generated DDL.