CREATE TABLE DESCRIPTION (Adabas Only)
Function
This option enables users to import metadata from Adabas using SQL.
Syntax
CREATE TABLE DESCRIPTION TABLE NAME DEFINITION clause FILE NUMBER adabas_file_number (ADABAS COLUMN clause[,])
where
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 | |
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. 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.
If a PE data structure only contains MU fields, use an Adabas short name on the PE-subtable SEQNO(l).
Warning:
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.
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:
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