CREATE TABLE DESCRIPTION (Adabas only)

 

Function

This option enables users to import metadata from Adabas using SQL..

 

Syntax

 

create_table_description.bmp

 

 

 

table name definition clause

Identification of the table to be created. Optionally with specification of the assigned Adabas database.  See Table Name Definition.

adabas_database_number

The Adabas database number.  This is optional.  The Adabas database number can also be determined by the catalog used in the table name.

adabas_file_number

The Adabas file number.

adabas column clause

See Adabas Column Clause.

 

Description

CREATE TABLE DESCRIPTION specifies already existing Adabas files to the SQL environment in the catalog.

The statement consists of a table specification and a list of table elements . If the  table specification contains a schema identifier, then the table identifier will be explicitly qualified, otherwise CONNX uses the current default schema identifier.

Compiling the statement does not create the table. Other statements can not reference the table specified in the CREATE TABLE DESCRIPTION statement until the statement has been successfully executed.

If the statement is invoked statically, then during pre-compilation, the schema need not exist in the catalog. For successful execution, however, the schema must exist in the catalog, regardless of how it is invoked.

This statement may also use the technique of rotating a MU/PE's fields into base columns. This allows each element of a MU/PE field, to be referenced as a separate column within a base table.

Because this statement executes on an existing Adabas file, you can specify minimal information; the SQL compiler will generate  the rest. You must be specify the column identifier and the Adabas short name for this column; all other information will be generated from the underlying Adabas file.

 

Limitations

The DBA can execute this statement for all users. All other users can use this statement only in a schema that they own.

The following rules apply:

  1. Foreign keys reference only unique constraints. A sub-table contains exactly one foreign key.

  2. The same rules apply for the columns, constraints and indexes of the master table as for a CREATE TABLE DESCRIPTION statement.

  3. Columns which are not an element of a foreign key and not of a SEQNO type are called data columns. The limitations under rules 4 - 7 apply to data columns in sub-tables.

  4. The data columns of a level 1 table correspond either to MU fields which do not lie within a PE group, or to fields within a single PE group.

  5. The data columns of a level 2 table correspond to MU fields within a specific PE group - the group containing those fields which the data columns in the referenced table correspond to.

  6. No more than one data column may correspond to each field (with rotated fields, each subscript counts as its own field).

  7. With parallel MU fields, CONNX assumes that in all Adabas records, the respective counter values are the same.

  8. For x=1 or x=2, a unique constraint of a level x table encompasses the elements of the foreign keys and a column of the type SEQNO(x). Other unique constraints on subtables are not allowed.

  9. For indexes to subtables, the same rules apply as for level-0 tables, plus the following additional constraints:

    HAVING UNIQUE INDEX is not allowed. In order to model the Adabas UQ option, UQINDEX is used.

    Note:
    A unique constraint is defined as either a UNIQUE or PRIMARY KEY constraint.

  10. All level 0 columns must be grouped within one CREATE TABLE of a cluster.

Note: If a PE data structure only contains MU fields, use an Adabas short name on the PE-subtable SEQNO(l).

Caution: This statement is not subject to transaction logic. An implicit COMMIT will be performed after successful execution of this statement. If an error is detected during execution of this statement, an implicit ROLLBACK will be performed. Therefore, before executing this statement, it is strongly recommended to complete any open transaction containing INSERT, UPDATE and/or DELETE statements by issuing an explicit COMMIT or ROLLBACK statement.

 

ANSI Specifics

This statement is not part of the ANSI standard.

The column default value of "ADABAS" is not part of the SQL standard.

 

Adabas SQL Server Specifics

The following examples define and create the table CONTRACT. The Adabas file already exists but there is no table definition in the catalog.

Example

Detailed format

 

CREATE TABLE DESCRIPTION contract DATABASE NUMBER 1 FILE NUMBER 21

(contract-id             integer             SHORTNAME AA

                              index ind_contract not null unique,

price                 NUMERIC (13,3)         SHORTNAME AB not null,

date_reservation         INTEGER             SHORTNAME AD,

date_booking             INTEGER             SHORTNAME AG,

date_cancellation        INTEGER             SHORTNAME AH,

date_deposit             INTEGER             SHORTNAME AJ,

amount_deposit        NUMERIC                SHORTNAME BA,

date_payment             INTEGER             SHORTNAME BB,

amount_payment        NUMERIC                SHORTNAME BE,

id_customer              INTEGER             SHORTNAME CA not null,

id_cruise             INTEGER                SHORTNAME CD not null);

 

 

Minimal format

 

CREATE TABLE DESCRIPTION contract

FILE NUMBER 21

        (contract_id          SHORTNAME AA,

price                 SHORTNAME AB,

id_cruise             SHORTNAME CD);

 

 

The effect of the minimal format on the catalog differs in some points from detailed format: First, there will be no entry for the index IND CONTRACT in the catalog, but this has no effect on the DML processing for which only the Adabas file structure is relevant. Second, the columns with NUMERIC data type will have the scale 0, since no scale information is held by the Adabas file structure.

The next example shows how the elements of an MU may be rotated into base columns. The examples is for a table containing bonuses for each month of the current year.

CREATE TABLE DESCRIPTION sales-bonuses

DATABASE CNXDB1

FILE NUMBER 15

( id         SHORTNAME "AA",

surname      SHORTNAME "AB",

first name   SHORTNAME "AC",

jan bonus    SHORTNAME "AD" ( 1 ),

feb_bonus    SHORTNAME "AD" ( 2 ),

mar bonus    SHORTNAME "AD" ( 3 ),

apr_bonus    SHORTNAME "AD" ( 4 ),

may_bonus    SHORTNAME "AD" ( 5 ),

jun_bonus    SHORTNAME "AD" ( 6 ),

jul bonus    SHORTNAME "AD" ( 7 ),

aug_bonus    SHORTNAME "AD" ( 8 ),

sept bonus   SHORTNAME "AD" ( 9 ),

oct bonus    SHORTNAME "AD" ( 10 ),

nov_bonus    SHORTNAME "AD" ( 11 ),

dec_bonus    SHORTNAME "AD" ( 12 ));

164