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:
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.
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.
The following Adabas SQL Gateway objects can be documented in Predict:
There are two methods of documenting Adabas tables:
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.
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 |
|
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).
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.
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.
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.
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.
Unique elements are documented as Fields with descriptor type D, unique option U and suppression option R.
A primary key always includes an index with descriptor option P.
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.
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.
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 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 must be fully qualified: schema, delimiter, index name.
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.
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 are delimited by the appearance of a # character in the first column and by a new line.
In order to specify the schema identifier, the following directive is used:
SCHEMA schema_name
For example:
SCHEMA production_schema
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 |
For example:
FILE 32 cluster_employees employees
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.
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.
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.
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
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
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
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.
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.
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.
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. |
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.
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.
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.
Displays documentation data, generation options and generated table description of specified file ID(s).
Moves the generation protocol to another member and/or library.
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.
Selects Adabas table/view for further processing.