Defining a Database Using SQL › Creating a Table

Creating a Table

You create a table by issuing the CREATE TABLE statement and adding appropriate clauses to describe each column associated with the table.

Things You Can Specify

1. Table name, using a schema qualifier unless you have specified a default schema name in the SET SESSION statement

   Note: For more information about session management statements, see the CA IDMS SQL Reference Guide.

2. Column names
3. Data type for each column
4. Optionally a default value and a null specification for each column
5. Optionally a check constraint to limit the values allowed in a column or columns
6. An area in which the table's rows will be stored (unless you want them stored in the default area for the schema)
7. Data compression
8. An estimate of the number of rows for the table
9. Physical attributes, including a table ID number and a synchronization timestamp.

Specifying Physical Attributes

When defining or altering a table, you can specify physical attributes that are normally generated automatically. Specifying explicit values for this information, allows you to create tables that have identical physical attributes and can therefore be accessed through a single schema definition.

Since a table's synchronization stamp is updated each time an associated index, calc key or referential constraint is added or removed, the synchronization stamp must be set after adding or removing these associated entities.

Care should be exercised when specifying a specific timestamp, since its purpose is to enable the detection of discrepancies between a table and its definition. If explicitly specified, the timestamp should always be set to a new value following a definitional change so that the change is detectable to the run time system.

Compressing

The COMPRESS option in the table definition statement specifies that data be compressed when it is stored in the database and decompressed when it is retrieved from the database.

To use the COMPRESS option, you must have CA IDMS Presspack installed at your site.

Note: See the CA IDMS Presspack User Guide for information about CA IDMS Presspack.

Estimated rows

When you create a new table, it is useful to specify the number of rows you expect to be stored in the table. CA IDMS/DB uses this information to:

• Optimize host language statements that reference the table and are compiled before the table is loaded and the UPDATE STATISTICS statement has been executed for it
• Calculate index characteristics

Example

In the following example, the EMPLOYEE table is defined and associated with the PROD schema. The table includes 15 columns. The check parameter restricts the values that can be inserted in the EMP_ID and STATUS columns. The data in this table will be stored in the EMP.EMPREG area and the expected number of rows for the table is 500.

create table prod.employee
                  (emp_id            unsigned numeric      not null,
                   manager_id        unsigned numeric              ,
                   emp_fname         char(20)              not null,
                   emp_lname         char(20)              not null,
                   dept_id           unsigned numeric      not null,
                   street            char(40)                      ,
                   city              char(20)              not null,
                   state             char(02)              not null,
                   zip_code          char(09)              not null,
                   phone             char(10)                      ,
                   status            char                  not null,
                   ss_number         unsigned decimal(9,0) not null,
                   start_date        date                  not null,
                   termination_date  date                          ,
                   birth_date        date                          ,
         check ( (emp_id between 0 and 8999) and
                 (status in ('A', 'S', 'L', 'T') ) )
         in emp.empreg
         estimated rows 500;