CONNX Data Integration Suite 14.8.0 | Reference Guide | SQL Grammar | Primary SQL Commands | CREATE CLUSTER DESCRIPTION (Adabas Only)
 
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 CLUSTER NAME DEFINITION clause FILE NUMBER adabas_file_number (CLUSTER DESCRIPTION clause[,])
where
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 syntax:
CREATE TABLE table_specification (ADABAS COLUMN clause[,])
where
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.
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
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.
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.
Adabas SQL Gateway Specifics
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
Examples
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