This document covers the following topics:
Predict offers enhanced support for the following SQL systems:
Adabas D
Ingres
Informix
Sybase
The following SQL objects and attributes can be documented in Predict. Not all attributes are applicable for all SQL systems.
Tables and Views
Columns
Referential constraints
Unique constraints
Check constraints
Stored procedures
Triggers
The following table gives an overview of how different SQL objects are documented in Predict.
| SQL Object | Documented in Predict with File of Type |
|---|---|
| Adabas D Table, View | BT, BV |
| Ingres Table, View | JT, JV |
| Informix Table, View | XT, XV |
| Sybase Table, View | YT, YV |
| Other SQL systems | X |
The documentation of views is supported by the Natural-based
Subquery Editor in Predict to specify the part of the subselect clause starting
from the first FROM clause.
The selection clause of the subselect clause 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 clause 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 editor functions). The remaining part of the subselect clause is left
unchanged.
The expression used to define 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. These derived fields can be edited with the Subquery Editor.
Comment lines (starting with * or **) and remarks within a line (starting with /*) are allowed in derived field expressions and subselect clauses.
| SQL Object | Valid for | Documented in Predict with Object of Type | Note | |||
|---|---|---|---|---|---|---|
| BT,BV | JT,JV | XT,XV | YT,YV | |||
| Trigger | Y | Y | Y | Trigger | "Triggers" are referred to as "Rules" in Ingres. | |
| Table Check Constraint | Y | Y | Y | Attribute of file | ||
| Column Check Constraint | Y | Y | Y | Y | Verification of status SQL | A "Column check constraint" is referred to as "Integrity" in Ingres and "Rule" in Sybase. |
| Stored Procedure | (Y) | Y | Y | Y | Program of type R | |
| Column | Y | Y | Y | Y | Field | |
| Common Key | Y | Field | ||||
| Primary Key | Y | Y | Y | Y | Field | |
| Foreign Key | Y | Y | Y | Y | Field | |
| Unique constraint | Y | Y | Y | Y | Field | |
| Referential constraint | Y | Y | Y | Y | File Relation of type R | |
Note:
Objects marked with (Y) can be documented in Predict but are
not included in the respective CREATE statement.
SQL columns are documented as field objects.
SQL keys are documented as follows:
If the key consists of only one column, the field documenting the column is marked as a descriptor with descriptor type P, E or K.
If the key applies to multiple columns, it is documented as a field of type SP (superfield) with descriptor type P, E or K. The descriptor types have the following meaning:
E
Field is a foreign key.
K
Field is a common key.
P
Field is a primary key. This always implies that the field
has a unique constraint.
Unique constraints are documented as follows:
If the unique constrain applies to only one column, the field documenting the column is marked U in column Unique option.
If the unique constraint applies to multiple columns, it is documented as a field of type SP (superfield) with descriptor type P, E or K, and U in column Unique option. The descriptor types have the following meaning:
E
Field is a unique foreign key.
K
Field is a unique common key.
P
Field is a primary key. This always implies that the
field has a unique constraint.
Common keys (columns that are frequently joined between two tables or views) are documented in Predict with a file relation of type K. The two fields for which the relationship is to be established must have descriptor type K.
Common keys are only applicable to Sybase.
Referential constraints are documented as file relations of type R (referential constraint). A relationship is established between a unique key and a foreign key. Unique and foreign key can belong to the same or to different tables.
Check expressions for single columns are documented with verifications of status SQL. The check expression is stored as the rule of the Verification.
Check expressions can be edited with the Predict Rule Editor.
Comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
A table check expression is a check expression that applies to more than one column. A table check expression is an attribute of a file.
To edit table check expressions, enter Y in the field Trigger of the corresponding file object.
Comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
See the section Trigger in the Predefined Object Types in Predict documentation
Stored procedures are documented the procedure code of programs of
type R (SQL procedure) and language S (SQL). If the trigger of a file of type
XT, YT or JT contains the text EXECUTE procedure_name, and the procedure_name
corresponds to a program of type R and language Q, then the procedure code of
the program object is included in the generated CREATE
statement.
Special naming conventions apply to the following objects in Predict:
SQL file types. See table below.
Fields linked as children to these file types
Constraint names
Correlation names
Procedure/Function name
The file IDs must be fully qualified.
A fully qualified ID consists of three parts:
Creator of up to 8 characters
Hyphen to separate creator from table/view name
Table/view name. The maximum length depends on the SQL system. See table below.
Fully qualified IDs may not exceed 32 characters. For SQL objects where the table/view name may not exceed 18 characters, the maximum length of the fully qualified ID in Predict is 27.
The permitted characters listed in the table below apply to creator and table/view name.
| Convention | File Type | |||
|---|---|---|---|---|
| BT, BV | JT, JV | XT, XV | YT, YV | |
| Maximum length of table/view name | 18 | 24 | 18 | 30 |
| Upper case | ||||
| Upper/lower case | Y | Y | Y | Y |
| "_" allowed at first position | Y | Y | ||
| "#" allowed at first position | Y | |||
| "$" allowed at first position | Y | |||
| "@" allowed at first position | Y | |||
| "_" allowed from second position | Y | Y | Y | Y |
| "#" allowed from second position | Y | Y | Y | |
| "$" allowed from second position | Y | Y | Y | |
| "@" allowed from sec. position | Y | Y | Y | |
| Numbers allowed from second position | Y | Y | Y | Y |
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: SYSIBM-SYSCOLUMNS-COLNAME).
It is possible to incorporate SQL tables and views that have delimited identifiers. These tables and views can then be renamed with Predict maintenance functions, and retrieval functions can be applied to them. It is strongly advisable to rename delimited identifiers for the following reasons:
The only Predict functions that can be applied without restriction to objects with delimited identifiers are Incorporate and Rename.
If identifiers contain special characters such as blank or asterisk, results of retrieval functions are unpredictable.
Views can only be generated if the subselect clause and the column expressions do not contain references to delimited identifiers enclosed by quotation marks.
The following table gives an overview of the CREATE
statements that can be generated from Predict objects with the function
Generate SQL CREATE Statement. These SQL statements are stored as Natural
members.
If a CREATE statement is not available for a
particular SQL system, a corresponding clause is generated in the CREATE
TABLE or CREATE VIEW statement if applicable.
| CREATE STATEMENT | File Type | |||||
|---|---|---|---|---|---|---|
| BT,BV | D,E | JT,JV | X | XT,XV | YT,YV | |
| TABLE, VIEW | Y | Y | Y | Y | Y | Y |
| INDEX | Y | |||||
| DEFAULT | Y | Y | ||||
| RULE | Y | Y | Y | |||
| PROCEDURE | Y | Y | Y | |||
| TRIGGER | Y | Y | Y | |||
| LABEL ON | Y | |||||
| COMMENT ON | Y | |||||
The statements can be punched to an operating system member for further processing, for example execution with an interactive SQL tool or a user program.
For more information see the section Generation in the External Objects in Predict documentation.
The following rules apply when generating a DDM for Natural from an SQL file object:
The file must be linked via "Contains FI" to a database of a compatible type:
| File Type | Compatible Database Type | ||
|---|---|---|---|
| BT, BV | Adabas D Table, View | B | Adabas D Handler |
| JT, JV | Ingres Table, View | J | Ingres Handler |
| XT, XT | Informix Table, View | X | Informix Handler |
| YT, YV | Sybase Table, View | Y | Sybase Handler |
Files documenting both tables and views must be linked to a database of the corresponding type.
The file must be linked via "Contains FI" to a database of which the database number is defined in the NATCONF.CFG file of type OSQ. For more information see Data Definition Module in the section Generation in the External Objects in Predict documentation.
Incorporation of tables and views of SQL systems is subject to security mechanisms of the respective system.
Tables and views of the following SQL systems can be incorporated.
Adabas D
Ingres
Informix
Sybase
Access is required to the following in the respective SQL system:
For more information see the section Incorporation in the External Objects in Predict documentation.
You can display and purge the generation protocols created by Predict from the function Generate SQL CREATE statement. You cannot process objects in the external SQL environment with administration functions.
For more information see the section Administration of External Objects in the External Objects in Predict documentation.