CREATE CLUSTER (Adabas Only)
Function
The CREATE CLUSTER statement is used to combine a number of base tables in one internal table.
Syntax
CREATE CLUSTER CLUSTER NAME DEFINITION clause [ADABAS FILE DEFINITION clause] (CLUSTER 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 definition clause | |
cluster_element | A base table in the described cluster. |
CLUSTER clause syntax:
CREATE TABLE table_specification (ADABAS COLUMN clause [,])
where
table_specification | Identifier of the subtable to be created for this cluster, qualified by the schema identifier. See
Table Specification. |
Adabas column clause | |
Description
A CREATE CLUSTER statement is used to combine a set of base tables into one Adabas file.
Each subtable within the CLUSTER represents either a PE group or an MU field. It is also possible to group MU's together into one single subtable, this assumes that all MUs have the same number of occurrences and that when accessing them, the occurrence number of each MU will be equal.
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 column attributes/table clause SHORTNAME definition may not be specified in this statement.
A CREATE CLUSTER statement will always represent tables of level one as a PE group within Adabas. Following rules apply:
1. Foreign keys reference only primary keys. A subtable 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 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 only to fields of 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, it is assumed 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:
10. 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.
11. All level 0 columns must be grouped within one CREATE TABLE of a cluster.
In the case of a PE data structure containing MU fields only, it is necessary to use an Adabas short name on the SEQNO(1) 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
The CREATE CLUSTER statement is not part of the Standard.
Examples
The following example creates the cluster city_guide:
CREATE CLUSTER city_guide
(
CREATE TABLE states
(
abbreviation CHAR (2) PRIMARY KEY NOT NULL DEFAULT ADABAS,
state_name CHAR (20) UNIQUE NOT NULL DEFAULT ADABAS,
capital CHAR (20) INDEX state_capital,
population INT
),
CREATE TABLE cities
(
state_abbrev CHAR (2) UQINDEX NOT NULL DEFAULT ADABAS,
city_seqno SEQNO (1) NOT NULL,
city_name CHAR (20),
population INT,
PRIMARY KEY (state_abbrev, city_seqno),
FOREIGN KEY (state_abbrev)
REFERENCES states(abbreviation),
UQINDEX city_state (city_name, state_abbrev)
),
CREATE TABLE buildings
(
state_abbrev CHAR (2) UQINDEX NOT NULL DEFAULT ADABAS,
city_seqno SEQNO(1) NOT NULL,
building_seqno SEQNO(2) NOT NULL,
building_name CHAR (20) NOT NULL SUPPRESSION,
height INT NOT NULL SUPPRESSION,
PRIMARY KEY (state_abbrev, city_seqno, building_seqno),
FOREIGN KEY (state_abbrev, city_seqno)
REFERENCES cities (state_abbrev, city_seqno)
),
CREATE TABLE places
(
state_abbrev CHAR (2) UQINDEX NOT NULL DEFAULT ADABAS,
city_seqno SEQNO(1) NOT NULL,
place_name CHAR (20) NOT NULL SUPPRESSION,
FOREIGN KEY (state_abbrev, city_seqno)
REFERENCES cities (state_abbrev, city_seqno)
)
);