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.

Authorization

To issue a CREATE TABLE statement, you must:

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

►►─── CREATE TABLE ─┬────────────────┬─ table-identifier ─────────────────────►
                    └─ schema-name. ─┘

         ┌───────── , ─────────┐
 ►─── ( ─▼─ column-definition ─┴─┬───────────────────────────────┬─ ) ────────►
                                 └─ ,CHECK ( search-condition ) ─┘

 ►─┬─────────────────────────────┬────────────────────────────────────────────►
   └─ 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

►►─── column-name  data-type ─────────────────────────────────────────────────►

 ►─┬────────────┬─────────────────────────────────────────────────────────────►
   └─ NOT NULL ─┘

 ►─┬────────────────┬─────────────────────────────────────────────────────────►◄
   └─ WITH DEFAULT ─┘

Parameters

table-identifier

Specifies the name of the table being treated. Table-identifier must be a 1- through 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

Specifies 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 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.

If you do not associate an area with a table, CA IDMS:

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

The IN parameter is a CA IDMS extension of the SQL standard.

segment-name

Identifies the segment associated with the named area.

area-name

Identifies the area to be associated with the table. Area-name 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.

The COMPRESS parameter is valid only if CA IDMS Presspack is installed at your site.

The COMPRESS parameter is a CA IDMS extension of the SQL standard.

USING data-characteristic-table-name

Specifies the data characteristic table CA IDMS Presspack is to use 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, BUILTIN, directs CA IDMS Presspack to use the data characteristic table supplied with the product.

ESTIMATED ROWS estimated-row-count

Indicates the number of rows expected to be stored for the table. Estimated-row-count must be an integer that does not exceed 16,777,214.

CA IDMS uses the estimated row count when determining default index characteristics and estimating statistics.

The ESTIMATED ROWS parameter is a CA IDMS extension of the SQL standard.

TABLE ID table-id-number

Assigns a table ID value for the table being created. The table-id number must be in the range of 1024 through 4095.

NO DEFAULT INDEX

Indicates that the TABLE will have no initially assigned default index. The default index is an index sorted by DBKEY in ascending order in such a way that all TABLE rows can be accessed with the minimum number of I/Os.

Note: For more information about retaining or dropping the default index, see the Usage topic later in this section or the CA IDMS Database Design Guide.

TIMESTAMP timestamp-value

Specifies the value of the synchronization stamp to be assigned to the table. Timestamp-value must be a valid external representation of a timestamp.

Parameters for Expansion of column-definition

column-name

Specifies the name of a column to be included in the table being created. Column-name must be a 1- through 32-character name that follows the conventions for SQL identifiers.

Column-name must be unique within the table being defined.

data-type

Defines 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.

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.

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.

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)

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.

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:

Search-condition cannot include any host variables, routine parameters, local variables, aggregate or user-defined functions, EXISTS predicates, quantified predicates, or subqueries
Each column reference 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 has been 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. You should use care when doing so because 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.

If not specified, the synchronization stamp is automatically set to the current date and time.

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;

More Information

For more information about defining tables, see ALTER TABLE and DROP TABLE.
For more information about implementing indexes, see the CA IDMS Database Design Guide.
For more information about compressing data, see the CA IDMS Presspack User Guide.
For more information about differences between the CREATE TABLE statement in CA IDMS and the SQL standard CREATE TABLE statement.