The Adabas SQL catalog 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 Server. Also, Predict descriptions already take account of a variety of
Adabas-specific features.
Parts of the ADVANCED Interactive Facilities of Adabas SQL Server must be installed within Natural. For detailed information see the requirements table in the respective part of the Predict Installation documentation.
The following Adabas SQL Server 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 Server, 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.
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 database and tablespace definitions need not be documented with separate Predict objects:
Adabas SQL databases need not be documented with separate Predict objects, because the information to which Adabas database an Adabas SQL tablespace belongs is documented with the file-database link.
The properties of Adabas SQL tablespaces are documented as attributes of Adabas file objects.
The physical implementation of Adabas tables in Adabas can be performed by Predict (with Generate Adabas file or Generate ADACMP/ADAFDU). The description needed by Adabas SQL to address the Adabas files can be generated with the function Generate Adabas table description.
Adabas SQL columns are documented as field objects in Predict.
Adabas SQL Server 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 following Predict functions are subject to SQL security mechanisms:
Function Generate Adabas Table/View and administration functions Purge and Refresh: When the catalog is accessed for the first time, the user ID DBA is used for read access to the catalog.
Generate Adabas Table/View: If a description is generated into a
schema which is not owned by the catalog user, a window appears in which you
can enter the ID and password of the schema owner. In batch mode, use the
command SET SCHEMA_OWNER.
Incorporation and Comparison functions: User must have SELECT privilege for the schema definition_schema which is delivered with Adabas SQL Server.
With the Predict function Generate Adabas Table/View, 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 database name is taken from the catalog. If no database name
exists, a CREATE DATABASE statement is generated. If the ID of the
Predict database object complies with SQL naming conventions, this name is
taken. If not, the database name DB_DBnr is generated.
The available options are described under Adabas Table/Cluster/View in the section Generation in the External Objects in Predict documentation.
If the database/file number is a logical file number or the number of
an anchor file (with a file of type Expanded), the table description is
appended with "MODIFICATION NOT ALLOWED". This has
the result that ALTER statements for this table are rejected. Only
DML (Data Manipulation Language) statements can be executed for tables marked
in this manner; DDL (Data Definition Language) statements are not possible
except DROP TABLE DESCRIPTION.
The generated SQL statements can be saved in a protocol.
With the Predict function Generate Adabas Table/View, a CREATE
VIEW statement is generated from a file of type B.
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 Adabas Table/View, 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 |
|---|---|
Either
|
File of type A (with SQL usage) set to Y. |
|
Master Adabas file exists in Predict (determined using DBnr/Fnr). |
File of type AT. |
The database number is interpreted as a physical Adabas database and a link is created from this database to the Adabas file. The values of the Adabas attributes can either be taken from the tablespace attributes in the catalog, or default values are used which can be adapted to the "real world" with the function Compare Adabas File.
With the Predict function Incorporate Adabas Table/View, a file of type B is documented in Predict from the entry in the Adabas SQL catalog.
Files of type A (SQL), type AT and type B are compared with information contained in the description of the table/view in an Adabas SQL catalog. See the section Comparison in the External Objects in Predict documentation for more details.
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 or B and external object code is EQ.
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.
Programs using embedded SQL must be precompiled with the Adabas SQL precompiler before the host language compiler is executed.
During precompilation, an option is available to create XRef data. The creation of XRef data is controlled by options specified in the Adabas SQL parameters. See the Adabas SQL documentation for a detailed description of these options.
As described in the section Third Generation Languages in this documentation, XRef data is always assigned to members contained in logical libraries. The library containing the member can be specified explicitly or - if no library is specified - the member is assigned to a default library depending on the host language. See table below.
| Host Language | Default-Library |
|---|---|
| C | *SYSCCC* |
| COBOL | *SYSCOB* |
| PL/I | *SYSPLI* |
| FORTRAN | *SYSFOR* |
The following XRef data is stored for programs processed by the Adabas SQL precompiler:
directory information (user ID, terminal ID, date and time of precompilation)
each Copy/Include Code member used in the program
each table name used in certain clauses of DML statements, together with the usage:
| Statement | Clause | Usage |
|---|---|---|
SELECT |
FROM |
Read |
INSERT |
INTO |
Store |
UPDATE |
Update | |
DELETE |
FROM |
Delete |
each column name used in certain clauses of DML statements, together with the usage:
| Statement | Clause | Usage |
|---|---|---|
SELECT |
Read | |
SELECT |
WHERE |
Search |
INSERT |
INTO |
Store |
UPDATE |
<row amendment expression> | Update |
UPDATE |
WHERE |
Search |
DELETE |
WHERE |
Search |