CREATE CLUSTER DESCRIPTION (Adabas only)

 

Function

This statement introduces an existing Adabas file including MU/PE fields to the SQL environment.  CREATE CLUSTER DESCRIPTION enables users to import metadata from Adabas scripts using DDL.

 

Syntax

 

create_cluster_description.bmp

 

cluster name definition clause

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

adabas_file_number

The Adabas file number.

 

CLUSTER DESCRIPTION clause:

cluster_clause.bmp

 

 

table_specification

The base tables (master tables and subtables) of the described cluster.  See Table Specification.

adabas column clause

Defines the columns of the table.  See Adabas Column Clause.

 

Description

CREATE CLUSTER DESCRIPTION defines the description of an existing Adabas file that contains Adabas multiple value fields (MU) and/or periodic groups (PE).

 

Limitations

The DBA can execute this statement for all users. All other users can use this statement only in a schema owned by the user.

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

  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. Not 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 the respective counter values in all Adabas records 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.

The BLOCK SIZE has the following limitations:

  • default value= 7

  • minimum value = 1

  • maximum value = 191

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

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.

 

Adabas SQL Gateway Specifics

 

Example

CREATE CLUSTER DESCRIPTION city_guide
FILE NUMBER 134
(
CREATE TABLE DESCRIPTION states (
abbreviation      SHORTNAME 'AA' PRIMARY KEY DEFAULT ADABAS,
state name        SHORTNAME 'AB' UNIQUE NOT NULL DEFAULT ADABAS,
capital           SHORTNAME 'AC' INDEX,
population        SHORTNAME 'AD'
),
CREATE TABLE DESCRIPTION cities (
state_abbrev      SHORTNAME 'AA'
city segno        SEQNO(1) NOT NULL DEFAULT ADABAS
city name         SHORTNAME 'BA' INDEX NULL SUPPRESSION,
population        SHORTNAME 'BB' NULL SUPPRESSION
PRIMARY KEY (state abbrev, city_seqno),
FOREIGN KEY (state abbrev) REFERENCES states,
UQINDEX ( city name, state abbrev)
),
CREATE TABLE DESCRIPTION buildings (
state_abbrev       SHORTNAME 'AA',
city_segno         SEQNO(1) NOT NULL DEFAULT ADABAS,
building_segno     SEQNO(2) NOT NULL DEFAULT ADABAS,
building name      SHORTNAME 'CA' NOT NULL SUPPRESSION,
height             SHORTNAME 'CB' NOT NULL SUPPRESSION,
PRIMARY KEY (state abbrev, city_segno, building segno),
FOREIGN KEY (state abbrev, city segno) REFERENCES cities
),
CREATE TABLE DESCRIPTION places
state abbrev       SHORTNAME 'AA' NOT NULL,
city_segno         SEQNO(l) NOT NULL DEFAULT ADABAS,
place name         SHORTNAME 'DA' NULL SUPPRESSION,
FOREIGN KEY (state abbrev, city_segno) REFERENCES cities
 

Below is the corresponding Adabas FDT definition:

 

Level Comment

I

Name

I

Length

I

Format

I

Options

I

1

I

AA

I

2

I

A

I

DE, UQ

I states, abbreviations

1

I

AB

I

20

I

A

I

DE, UQ

I states.state_name

1

I

AC

I

20

I

A

I

DE, NC

I states.capital

1

I

AD

I

4

I

F

I

PE

I states.population

1

I

B0

I

 

I

 

I

DE, NU

I cities

2

i

BA

I

20

I

A

I

NU

I cities.city_name

2

i

BB

I

4

I

F

I

NU, MU

I cities.population

2

I

CA

I

20

I

A

I

NU, MU

I buildings.building_name

2

I

CB

I

2

I

F

I

NU, MU

I buildings.height

2

I

DA

I

20

I

A

I

NU, MU

I places.place_name

Type

I

Name

I

Length

I

Format

I

Options

I Parent field(s)

Fmt

Super

I

X1

I

22

I

A

I

NU, UQ, PE

I BA (   1 - 20) A

 

I

 

I

 

I

 

I

 

I AA (   1 - 2) A