CREATE TABLE

The CREATE TABLE data description statement defines a table in the dictionary. Tables defined with the CREATE TABLE statement are called base tables.
idmscu19
The CREATE TABLE data description statement defines a table in the dictionary. Tables defined with the CREATE TABLE statement are called base tables.
This article describes the following information:
2
2
Key Benefit
CREATE TABLE supports ISO standard DDL for defining unique and referential constraints. This enables better integration of third party frameworks and tools with CA IDMS. If needed, CA IDMS creates an index to enforce the constraint.
Authorization
To issue a CREATE TABLE statement, you must do the following:
  • Own the schema where the table is being defined or hold the CREATE privilege on the named table
  • Hold the USE privilege on the area where rows of the named table are stored
Syntax
Following is an example of the CREATE TABLE syntax:
  ►►─── CREATE TABLE ─┬────────────────┬─ 
table-identifier
 ──────────────────────────►                     └─ 
schema-name
. ─┘         ┌──────────── , ─────────────────┐ ►─ ( ─▼──┬─ column-definition ──────┬──┴──┬────────────────────────────┬── ) ──────►          ├─ unique-constraint ──────┤     └─ ,CHECK (search-condition)─┘              └─ referential-constraint ─┘                                    ►─┬─────────────────────────────┬─────────────────────────────────────────────────►    └─ IN 
segment-name
.
area-name
 ─┘    ►─┬─────────────────────────────────────────────────────────────┬─────────────────►    └─ COMPRESS ─┬──────────────────────────────────────────────┬─┘                 └─ USING ─┬─ BUILTIN ────────────────────────┬─┘                           └─ 
data-characteristic-table-name 
─┘  ►─┬──────────────────────────────────────┬────────────────────────────────────────►    └─ ESTIMATED ROWS 
estimated-row-count
 ─┘  ►─┬─────────────────────────────┬─────────────────────────────────────────────────►    └─ TABLE ID 
table-id-number
 ──┘    ►─┬─────────────────────────────┬─────────────────────────────────────────────────►    └─ NO DEFAULT INDEX ──────────┘  ►─┬─────────────────────────────┬─────────────────────────────────────────────────►◄    └─ TIMESTAMP 
timestamp-value
 ─┘    
Expansion of column-definition
 
Following is an example of the syntax for Expansion of column-definition:
►►─── 
column-name
  
data-type
 ────────────────────────────────────────────────────────►    ┌──────────────────────────────────────────────────────────────────┐  ►─▼──┬───────────────────────────────────────────────────────────┬───┴──────────────►◄       ├─NOT NULL──────────────────────────────────────────────────┤       ├─WITH DEFAULT──────────────────────────────────────────────┤       └─┬─────────────────────────────┬──┬─ UNIQUE ────────────┬──┘         └─ CONSTRAINT
constraint-name ┘  ├─ 
PRIMARY KEY ───────┤                                          
└─ references-clause
 ─┘  
Expansion of unique-constraint 
Following is an example of the syntax for Expansion of unique-constraint:
                                                                 ┌────── , ──────┐ ►►────┬──────────────────────────────┬──┬─── UNIQUE ────┬──── ( ─▼─ 
column-name ─┴─ 
)─────►◄       └─CONSTRAINT
constraint-name
──┘  └─ PRIMARY KEY ─┘  
Expansion of referential-constraint 
Following is an example of the syntax for Expansion of referential-constraint:
                                                        ┌──────── , ───────────┐ ►►──┬──────────────────────────────┬── FOREIGN KEY ──( ─▼─ 
foreign-key-column
 ─┴─
)──
references-clause
─►◄     └─CONSTRAINT 
constraint-name
 ──┘    
Expansion of references-clause 
Following is an example of the syntax for Expansion of references-clause:
                                                                  ►►──── REFERENCES ─┬────────────────┬── 
referenced-table
 ──┬─────────────────────────────────┬──────────►◄                    └─ 
schema-name
. ─┘                      │      ┌───────── , ─────────┐    │                                                            └── ( ─▼─ 
referenced-column
 ─┴─ 
)─┘         
Parameters
Following is a list of the parameters for CREATE TABLE:
  • table-identifier
    The name of the table being treated.
    Table-identifier
    must be a 1- to 18-character name that follows the conventions for SQL identifiers.
    Table-identifier
    must be unique among the table, view, function, procedure and table procedure identifiers within the schema associated with the table.
  • schema-name
    The schema to be associated with the table.
    Schema-name
    must identify a schema defined in the dictionary.
    If you do not specify
    schema-name
    , it defaults to the following:
    • The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  • column-definition
    Defines a column to be included in the table.
    Columns are included in the table in the order they are specified.
    The list of column definitions together with the CHECK parameter (if specified) must be enclosed in parentheses. Multiple column definitions must be separated by commas.
    Expanded syntax for
    column-definition
    is shown immediately following the CREATE TABLE syntax. Descriptions for these parameters are located at the end of this section.
  • CHECK (search-condition)
    Specifies criteria to be used to restrict the data that can be stored in the table. CA IDMS stores a new row in the table only if the value of
    search-condition
    is true for the row.
    For expanded
    search-condition
    syntax, see Expansion of Search-condition. Restrictions on the use of
    search-condition
    in the CHECK parameter are discussed in "Usage" later in this section.
  • IN
    Specifies the area to be used for storing rows of the table. It is a CA IDMS extension of the SQL standard.
    If you do not associate an area with a table, CA IDMS does the following:
    • Uses the default area, if any, for the schema associated with the table
    • Returns an error if the schema does not have a default area
  • segment-name
    The segment associated with the named area.
  • area-name
    The area to be associated with the table.It must identify an area defined in the dictionary.
  • COMPRESS
    Specifies that data in the table is to be compressed before being stored in the database. Note the following:
    • It is valid only if CA IDMS Presspack is installed at your site.
    • It is a CA IDMS extension of the SQL standard.
  • USING
    data-characteristic-table-name
    The data characteristic table, CA IDMS Presspack, is used to compress data in the table.
    Data-characteristic-table
    must identify a data characteristic table created by CA IDMS Presspack. If
    data-characteristic-table
    is not specified, the default directs CA IDMS Presspack to use the data characteristic table supplied with the product.
    Default:
    BUILTIN
  • ESTIMATED ROWS
    estimated-row-count
    The number of rows expected to be stored for the table. It is a CA IDMS extension of the SQL standard. 
    CA IDMS uses the estimated row count when determining default index characteristics and estimating statistics.
    Limits:
    Integer up to 16,777,214.
  • TABLE ID
    table-id-number
    The table ID value for the table being created.
    Limits:
    1024 to 4095.
  • NO DEFAULT INDEX
    Indicates that the TABLE is not initially assigned a default index. The default index is an index sorted by DBKEY in ascending order. All TABLE rows can be accessed with the minimum number of I/Os.
    For more information about retaining or dropping the default index, see "Usage" later in this section or Administrating Database Design.
  • TIMESTAMP
    timestamp-value
    The value of the synchronization stamp to be assigned to the table. It must be a valid external representation of a timestamp.
  • unique-constraint
    Allows the use of a column-list when defining UNIQUE keys.
Parameters for Expansion of column-definition
Following is a list of the parameters for Expansion of column-definition:
  • column-name
    A unique name of a column included in the table being created that follow the conventions for SQL identifiers.
    Limits
    :1 to 32 characters .
  • data-type
    The data type for the named column. For expanded
    data-type
    syntax, see Expansion of Data-type.
  • NOT NULL
    Indicates the column cannot contain null values. Note the following:
    • If you do not specify NOT NULL, the column is defined to allow null values.
    • If you specify NOT NULL
      without
      WITH DEFAULT, an INSERT statement must specify a value for the column.
  • PRIMARY KEY
    All unique values in the columns that make up the primary key. A PRIMARY KEY must be defined as NOT NULL. A table can only have one PRIMARY KEY.
  • UNIQUE
    All unique values in the columns that make up the unique key. A UNIQUE key must be defined as NOT NULL. A table can have multiple UNIQUE keys.
  • CONSTRAINT
    (Optional). Defines the constraint-name. If the constraint-name is not coded, a system-created value is generated. A constraint name has to be unique in the schema, whether it is unique, primary key, or relational constraint.
  • WITH DEFAULT
    Directs CA IDMS to store the default value in the named column if no value for the column is specified when a row is inserted.
    Default:
    The default value for a column is based on its data type:
Column data type
Default value
CHARACTER
Blanks
VARCHAR
A character string literal with a length of zero (that is, '')
GRAPHIC
Double-byte blanks
VARGRAPHIC
A double-byte character string literal with a length of zero
DATE
The value in the CURRENT DATE special register
TIME
The value in the CURRENT TIME special register
TIMESTAMP
The value in the CURRENT TIMESTAMP special register
All numeric data types
0 (zero)
Parameters for Expansion of referential-constraint
Following is a list of the parameters for Expansion of referential-constraint:
  • CONSTRAINT
    (Optional). Defines the constraint-name. If the constraint-name is not coded, a system-created value is generated. A constraint name has to be unique in the schema, whether it is unique, primary key, or relational constraint.
  • (
    foreign-key-column
    )
    Specifies one or more columns that make up the foreign key in the referencing table. It must identify a column in the referencing table and must be unique within the list of column names.
    Limits:
    1 to 32 columns
Parameters for Expansion of references-clause
Following is a list of the parameters for Expansion of references-clause:
  • schema-name
    The schema associated with the referenced table.
    If you do not specify schema-name, it defaults to the following:
    • The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  • referenced-table
    The referenced table in the constraint. It must identify a base table defined in the catalog.
  • (
    referenced-column
    )
    Specifies one or more non-null columns that make up a unique key in the referenced table, as defined by a CREATE CALC or CREATE INDEX statement.
    It must identify a column in the referenced table and must be unique within the list of column names. The columns must be named in the same order in which they are named in the CREATE CALC or CREATE INDEX statement that defines the unique key.
    You must specify the same number of referenced columns as the number of columns included in the foreign key of the referencing table. The corresponding referenced and foreign-key columns must have the same data type, length, precision, and scale.
    When (referenced-column) is omitted the referenced table must be defined with a primary key, and the primary key column list of the referenced table will be used as the referenced columns.  
Usage
Tables in the SYSTEM Schema
You cannot define a table in the SYSTEM schema.
Tables in System Areas
You cannot associate a table with a system area supplied with CA IDMS.
Maximum Row Length
When defining the columns in a table, you must ensure that the total number of bytes required for all columns in the table does not exceed the maximum allowed.
The total number of bytes allowed for all columns included in a table defined with the COMPRESS option is 32,760. If the table is defined without the COMPRESS option, the total number of bytes allowed for all columns is limited by the database page size and the size of the page reserve. The length of all columns must be less than or equal to (
page-size
-
page-reserve
- 40).
The number of bytes used for each column is determined by the column data type. Columns that allow null values take one additional byte each.
Each linked clustered referential constraint where the table is the referencing table reduces the total number of bytes allowed for columns by 12. Each linked clustered referential constraint in which the table is the referenced table or linked indexed referential constraint where the table is the referencing or the referenced table reduces the total by 8 bytes.
A CALC key defined on a table also reduces the total number of bytes allowed for columns by 8.
Overwriting Generated Indexes
Generated indexes are created to enforce UNIQUE and PRIMARY KEY constraints defined with CREATE TABLE syntax.  They are generated with default index values.  To override default values for a generated index, you can use CREATE UNIQUE INDEX, but only while the table is empty.   After the table is populated you need to use ALTER INDEX.   If you use CREATE UNIQUE INDEX to change default values, internally the generated index will be dropped prior to creating the new one.
Recommended Row Length
The absolute maximum row length for an uncompressed table is (
page-size
-
page-reserve
- 40). The recommended maximum row length is 30% of the absolute maximum.
Restrictions on search-condition
In the CHECK parameter of a CREATE TABLE statement note the following:
  • Search-condition
    cannot include any host variables, routine parameters, local variables, aggregate or user-defined functions, EXISTS predicates, quantified predicates, or subqueries
  • Each column referenced in
    search condition
    must identify a column in the table being defined
Default Indexes
The default index for a table is stored in the same area as the table. CA IDMS uses the default index to cluster rows of the table when no other clustered index, CALC key, or clustered referential constraint is defined for the table.
For such a table, the default index improves processing efficiency. CA IDMS uses the default index instead of an area sweep to locate rows of the table for retrieval.
The ESTIMATED ROWS Parameter with Large Tables
To enable CA IDMS to choose optimal attributes for indexes on a large table, you should supply an estimated number of rows in the table definition or specify index block characteristics yourself.
If you do not specify ESTIMATED ROWS, and, if you do not update statistics after the table is loaded, CA IDMS calculates index characteristics using an estimated row count of 1000.
Omitting NOT NULL and WITH DEFAULT
If you omit both NOT NULL nor WITH DEFAULT, the column is assigned a null value if no value is specified for the column on an INSERT statement.
Specifying a Synchronization Stamp
When defining or altering a table, you can specify a value for its synchronization stamp. If not specified, the synchronization stamp is automatically set to the current date and time. 
Use care when specifying a value for the synchronization stamp. The purpose of the stamp is to enable the detection of discrepancies between an entity and its definition. If explicitly specified, you must set the synchronization stamp to a new value following a change so that the change is detectable by the runtime system.
 
Defining a Referential Constraint
When defining a Referential Constraint through CREATE TABLE the same rules must be followed as defining one through CREATE CONSTRAINT
On creation of a referential constraint, an index will automatically be generated on the foreign key columns.
Referenced keys specified must be NOT NULL.
Enforcing Indexes
When a unique or primary key constraint is defined on a Create Table, a unique index is created to enforce the constraint, as follows:
  • It is marked as an enforcing generated index.
  • It is created with default options.
  • If a constraint name is not specified one is generated.
If more than one unique constraint is defined on the same columns, only one enforcing index is created. A primary key constraint is used over a unique constraint, whether named or unnamed. A named unique constraint is used over an unnamed constraint. Otherwise the first constraint name encountered is used.
If the enforcing index is for a primary key, the replacement index is marked as only a primary key index when the original primary key is dropped.
An enforcing index can be altered, but cannot be changed to non-Unique.
An enforcing index can only be dropped after a replacement enforcing index has been defined. A replacement enforcing index may be defined by creating a unique index on the same columns as an existing enforcing index. When the columns match, the new index is also marked as an enforcing index, but is not a generated index. Either index may then be dropped, but not both. When a replacement index is created for a generated index it is automatically be dropped.
An enforcing index can only be dropped after the constraint is dropped, using Alter Table Drop Constraint.  If the index is generated it is dropped as part of the drop constraint command.   If the index is not generated it is marked as not enforcing, and can be dropped using Drop Index.
Example
Defining a Base Table
The following CREATE TABLE statement defines the EMPLOYEE table in the DEMO_LIB schema. The table includes 16 columns. The CHECK parameter in the table definition restricts the values that can be stored in the STATUS column. Data in the table is stored in a compressed form in the EMP_SPACE area. The expected number of rows for the table is 350.
create table demo_lib.employee    (emp_id           integer        not null,    manager_id        integer,    emp_fname         varchar(20)    not null,    emp_lname         varchar(20)    not null,    dept_id           integer        not null,    proj_id           varchar(10),    street            varchar(40)    not null,    city              character(20)  not null,    state             character(2)   not null,    zip_code          character(9)   not null,    phone             character(10),    status            character(1),    ss_number         integer        not null,    start_date        date           not null,    termination_date  date,    birth_date        date,    check (status in ('A', 'S', 'L', 'T')))    in demoseg.emp_space    compress    estimated rows 350;