Adabas SQL Gateway

#### Search Results

Search

# Adabas SQL Gateway

The ConnX Data Dictionary (CDD) contains all the necessary information on Adabas tables and views. This information can be documented in Predict, and from this documentation a table or view can be created with a Predict generation, incorporation or administration function.

This document covers the following topics:

- General Information

- Documenting Adabas SQL Gateway in Predict

- Naming Conventions for Adabas SQL Gateway

- Creating Adabas Cluster Tables Using Program CXXGTD

- Generating, Incorporating and Comparing Adabas SQL Objects

- Administrating Adabas SQL Gateway Objects

## General Information

Predict supports the following SQL statements:

- `CREATE TABLE DESCRIPTION`

- `CREATE CLUSTER DESCRIPTION`

- `CREATE VIEW`

- `DROP TABLE DESCRIPTION`

- `DROP CLUSTER DESCRIPTION`

- `DROP VIEW`

Note
The statements `CREATE TABLE DESCRIPTION` and `CREATE CLUSTER DESCRIPTION` are supported instead of `CREATE TABLE` and `CREATE CLUSTER` to pass existing data structures to the Adabas SQL Gateway. Also, Predict descriptions already take account of a variety of Adabas-specific features.

#### Prerequisites

Natural SQL Gateway must be installed within Natural. For detailed information see the requirements table in the respective part of the Predict Release Notes documentation.

When a connection to a server is established, a user ID and password may be required. The user ID and password can be changed within a Predict session by executing the command SET CATALOG_USER. This allows you to overwrite the previously entered user ID and password. This user ID and the password are then also used for every other connection to an SQL server.

## Documenting Adabas SQL Gateway in Predict

The following Adabas SQL Gateway objects can be documented in Predict:

- Adabas Tables

- Adabas Views

- Indexes

- Unique elements

- Primary and foreign keys

- Referential constraints

### Documenting Adabas Tables

There are two methods of documenting Adabas tables:

#### With Files of Type A - with Adabas SQL usage set to Y

If an Adabas file corresponds exactly to a base table in Adabas SQL Gateway, it can be documented as a file of type A (SQL).

The Adabas file must not contain groups structures or multiple value fields. Rotated fields are not supported with this method.

This method is retained for reasons of compatibility with earlier Predict versions.

#### With Files of Type AT

Tables can also be documented with files of type AT (Adabas cluster table). Files of this type can be understood as userviews to an Adabas file.

Files of type AT have the following additional attribute:

Table level |
0 | Only "flat" structures are permitted (no MU or PE fields).
1 | For defining multiple fields and periodic groups.
2 | For defining multiple fields within a periodic group.

There are two methods of documenting periodic groups and multiple value fields in AT files:

- If the occurrences of PE/MU fields are fixed, you can use rotated fields in the AT file.

- If the occurrences of PE/MU fields are variable, use subtables (AT files at level 1 or level 2).

### Documenting Adabas Views

Adabas views can be documented with files of type B or CX.

In files of type B only Adabas tables or views can be referenced. In files of type CX any SQL file can be referenced.

#### Subselect Clauses and Expressions in Field Definitions

The documentation of views is supported by an additional editor to specify the part of the subselect clause starting from the first `FROM` clause.

The selection clause of the subselect is documented by the specification of the field list of the view. The specified list of tables/views in the first `FROM` clause of the subselect is generated by Predict and will be updated if a field from an additional table/view is added to the view. Correlation names can be added to the tables and views in the list (using functions of the editor). The remaining part of the subselect clause is left unchanged.

The expression used to define Adabas SQL fields can contain complex expressions. Fields that are defined not only by a single column name but use either a constant or a more complex expression are called derived fields. A special editor is provided for the specification of the expression of derived fields.

In the field expression and in the subselect clause, comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.

### Documenting Adabas SQL Columns

Adabas SQL columns are documented as field objects in Predict.

Adabas SQL Gateway has fields with data type SEQNO. These fields are documented in Predict with fields of type QN. This data type is used for documenting occurrences of MU or PE fields:

- SEQNO(0) corresponds to the ISN of the underlying Adabas C table

- SEQNO(1) corresponds to the index of a multiple-value or periodic field.

- SEQNO(2) corresponds to a multiple-value field within a periodic group.

For fields of this type, the column Occ represents an individual occurrence of a PE or MU field, and not the maximum number of occurrences.

These fields can be given a name in a table description and can be selected. However, they are only necessary if you want to perform a search operation using individual occurrences. If an AT file contains more than one MU fields or fields from more than one periodic group, it is only possible to address the same occurrence by means of a SEQNO(1) or SEQNO(2) field.

### Documenting Indexes

- The following attributes of indexes can be specified:

  - index name,

  - definition of index

- If the index consists of only one column, the field documenting the column is marked as a descriptor with descriptor type D.

- If the index consists of multiple columns, it is documented as a field with field type SP (superfield) and descriptor type D.

### Documenting Unique Elements

Unique elements are documented as Fields with descriptor type D, unique option U and suppression option R.

### Documenting Primary and Foreign Keys

#### Primary Keys

A primary key always includes an index with descriptor option P.

#### Foreign Keys

Foreign keys are documented as follows:

- If the foreign key consists of only one column, the field documenting the foreign key is marked as a descriptor with descriptor type F or E.

- If the foreign key consists of multiple columns, it is documented as a field with field type SP (superfield) and descriptor type F or E. The descriptor type F means the field is a foreign key and an index. E means the field is a foreign key without an index.

### Documenting Referential Constraints

Referential constraints are documented as file relations of type R (referential constraint). The relation is established between a unique element and a foreign key. Primary and foreign key must belong to different subtables. The subtables themselves must belong to the same Adabas file.

## Naming Conventions for Adabas SQL Gateway

Adabas SQL naming conventions have to be followed when creating or maintaining Predict objects for Adabas SQL. The following rules apply:

- Valid identifiers are from 2 to 32 characters long, must start with an alpha character (A - Z) and may be followed by either an alpha, a digit or underscore. Identifiers must obey these rules if Predict maintenance and generation functions are to be applied.

- The identifier of a table or view must be given in qualified form: the schema identifier (maximum length 32 characters), a delimiter and the table/view name (maximum length 32 characters). A hyphen is used as a delimiter (not a period as in SQL). An example: SYSSAG-SYSCOLUMNS. Hyphens in names are treated as follows:

  - When a table/view is generated from a Predict table/view object the hyphen will be transformed into a period (.).

  - Because hyphens are used as delimiters, only one hyphen can occur in the SQL identifier. Column names must not contain a hyphen.

  - The hyphen can be used as a minus sign or negative sign in the field expression or the subselect clause and must then be preceded by a blank.

#### Correlation Names

Correlation names can be defined in the subselect clause of a view. If a correlation name is defined for a table/view in the subselect clause, all references (in field expressions as well as in the field editor of the view) to columns of the table/view must be qualified with the correlation name. If no correlation name is defined for a table/view in the subselect clause, all references to columns of the table/view must be fully qualified with creator-tablename-columnname (for example: SYSSAG-SYSCOLUMNS-COLNAME).

#### Index Names

Index names must be fully qualified: schema, delimiter, index name.

## Creating Adabas Cluster Tables Using Program CXXGTD

The program `CXXGTD` creates Adabas cluster tables for a given Adabas File.

`CXXGTD` is located in library SYSDIC. When started, you have to specify the name of the documented Adabas file for which you want to define the cluster tables. This file has to be documented in Predict.

The program uses 2 workfiles (each having LRECL=80):

- Work file 1 contains the directives that control the layout of the resulting structure.

  The directives are described below in Input File Language Syntax.

  Note
  The directives `SYSTRANS`, `DATABASE`, `PRIMARY` and `FOREIGN` are ignored.

  Primary and Foreign keys will always be built using `SEQNO` columns. Work file 1 must contain at least a `SCHEMA` directive.

- Work file 2 will contain the resulting structure in a syntax that can be loaded into Predict using the program `CXXEX`. After a successful load, use the Predict function Generate SQL Create Statements to generate the `CREATE TABLE/CLUSTER DESCRIPTION` statements.

### Input File Language Syntax

The input file is used to provide information to program `CXXGTD`. All aspects of the generation can be controlled using the provision of an input file. The file consists of directives. Each directive is started by a new line and is terminated by an end of line. The order of the directives is of no significance. The program `CXXGTD` is only able to process one Adabas file at a time.

#### Comments

Comments are delimited by the appearance of a # character in the first column and by a new line.

#### Schema Identifier

In order to specify the schema identifier, the following directive is used:

`SCHEMA schema_name`

For example:

    SCHEMA production_schema

#### File Specification

The source Adabas file is specified by the following syntax:

`FILE file_nr [DDM=ddm_name] [[cluster_name] table_name]`

Where:

`file_nr` | specifies the Adabas file from which the description statement will be generated.
`table_name` | optionally specifies the table name which will be used either for the master table or for the single table if there is no cluster.
`cluster_name` | optionally specifies the cluster name which will be used in the generated statement. If both a cluster name and a table name are present, then this forces the generation of a `CREATE CLUSTER DESCRIPTION` statement, even if such a statement is not strictly necessary.

For example:

    FILE 32 cluster_employees employees

#### Subtable Specification

A subtable can be built around a PE group, a single MU field or a number of MU fields which act in parallel. The subtable directive is able to express either of these possibilities and to enable the optional specification of an identifier for the resulting subtable. In the first two cases, only a single field is required. In the third case, numerous MU fields can be specified in the form of a list separated by commas. The syntax is as follows:

`short_name [, short_name]... SUBTABLE [table_name]`

Where:

`short_name` | is as it suggests.
`table_name` | represents the intended table identifier for the subtable.

For example:

    AB SUBTABLE

Would map the field `AB` to a subtable and would generate a table name accordingly.

    AC SUBTABLE Employee_number

Would map the field `AC` to a subtable but would use the name as indicated.

    AE, AF SUBTABLE Employee_status

Would map the two MU fields `AE` and `AF` to the single subtable Employee_status.

#### Rotated Field Interpretation

If a field is to be interpreted as a rotated field, then the following syntax applies:

`short_name ROTATE value`

Where:

`short_name` | is as it suggests.
`value` | is the fixed number of occurrences to be rotated e.g. 12 in our example above.

For example:

    MA ROTATE 12

Would map the twelve occurrences of the field `MA` to twelve individual SQL columns.

#### Longalpha Field Interpretation

If an MU field of type character is to be interpreted as a longalpha field, then the following syntax applies:

`short_name LONGALPHA value`

Where:

`short_name` | is as it suggests.
`value` | is the number of bytes which are to be considered in the character field.

For example:

    LT LONGALPHA 512

Would map the field `LT` to a character column of 512 characters.

#### Long Name Specification

A column identifier for a particular field can be specified as follows:

`NAME short_name [index] column_name`

Where:

`short_name` | is as it suggests.
`index` | optionally refers to the occurrence number of an MU if the field is rotated.
`column_name` | is a valid SQL identifier representing the intended column identifier.

For example:

    NAME MA 3 BONUS_MARCH

#### Field Suppression

If a field is not to be included in a description, then its suppression is specified as follows:

`SUPPRESS short_name`

Where:

`short_name` | is as it suggests.

For example:

    SUPPRESS BA

#### Data Type Generation

In general, program `CXXGTD` does not generate the explicit data type declaration for a column in the resulting generated statement. The explicit data type declaration is however not strictly necessary as Adabas SQL Gateway can operate on a minimum create table/cluster statement, i.e. the server will fill in any missing information automatically. In such a case, the resulting generated statement can be significantly longer. The forced generation of the column’s data type is helpful for documentation purposes. The syntax is as follows:

`DATATYPE`

#### Error Handling

Should program `CXXGTD` encounter an error condition during execution, then it will generally terminate with an exit code and in addition an error message. The possible exit codes are:

`0` | Successful completion.
`4` | Warning generated.
`8` | Error detected.

The error text can be found in `SYSOUT`.

## Generating, Incorporating and Comparing Adabas SQL Objects

### Generate Table Description, Cluster Description

With the Predict function Generate Connx Dictionary , a `CREATE TABLE DESCRIPTION` statement or a `CREATE CLUSTER DESCRIPTION` statement is generated from a file of type A (with SQL usage set to Y) or from a file of type AT. See table below:

Constellation | Generated Command | Note
--- | --- | ---
One Adabas file corresponding to one SQL table | `GENERATE TABLE DESCRIPTION` |
One AT file corresponding to one SQL table
Multiple AT files corresponding to multiple SQL tables | `GENERATE CLUSTER DESCRIPTION` | Specify only one file in the cluster. All files in the cluster will be used for generation.

The statements add descriptions of multiple tables in an Adabas SQL catalog without creating an Adabas file (the standard SQL statements `CREATE TABLE/CLUSTER` generate both an Adabas file and a description in an Adabas SQL catalog).

This description contains the following:

- a list of fields in the file

- details of descriptors and superdescriptors, unique constraints, primary and foreign keys

- database name and file number. This information is used to access the Adabas file.

The available options are described under Adabas Table/Cluster/View in the section Generation in the External Objects in Predict documentation.

The generated SQL statements can be saved in a protocol.

### Generate View

With the Predict function Generate Connx Dictionary, a `CREATE VIEW` statement is generated from a file of type B or CX.

The available options are described under Adabas Table/Cluster/View in the section Generation in the External Objects in Predict documentation.

### Incorporate Table

With the Predict function Incorporate Connx Dictionary, a file of type A (with SQL usage set to Y) or a file of type AT is documented in Predict from the entry in the Adabas SQL catalog. The constellation in the catalog determines which file type is incorporated in Predict:

Constellation | Incorporated Object
--- | ---
Base table | File of type AT.
View | File of type CX.

### Incorporate View

With the Predict function Incorporate Connx Dictionary, a file of type B or CX is documented in Predict from the entry in the Adabas SQL catalog.

## Administrating Adabas SQL Gateway Objects

The following administration functions are valid for Adabas table descriptions. Enter function code L and object code FI in any Predict main menu or the command `ADMINISTRATE FILE`. File type is A (SQL), AT, B or CX and external object code is ZD.

For more information see the section Files in the section Administration of External Objects in the External Objects in Predict documentation.

### Disconnect Implementation

Deletes the generation protocol and the generation pointer from the Predict File object to the Adabas table description, but the table description is left intact.

With files of type AT, all files used for generation are disconnected in a single operation.

### Display Implementation

Displays documentation data, generation options and generated table description of specified file ID(s).

### Rename Implementation

Moves the generation protocol to another member and/or library.

### Purge Implementation

Deletes Adabas table descriptions and all dependent views.

A `DROP TABLE DESCRIPTION`, `DROP CLUSTER DESCRIPTION` or `DROP VIEW` statement will delete the definition from the Adabas SQL catalog and any statements referencing this table / view are marked as invalid.

When a table / view is dropped, all dependent views are dropped too.

Note
The function is equivalent to the function Generate with Replace table/view set to Y.

### Select Implementation

Selects Adabas table/view for further processing.

### On this page

- General Information
- Documenting Adabas SQL Gateway in Predict
- Naming Conventions for Adabas SQL Gateway
- Creating Adabas Cluster Tables Using Program CXXGTD
- Generating, Incorporating and Comparing Adabas SQL Objects
- Administrating Adabas SQL Gateway Objects