Incorporating Tables / Views of SQL Database Systems

This document covers the following topics:

Functional Scope

This function is available for the following SQL systems:

  • Adabas D

  • Ingres

  • Informix

  • Oracle

  • Sybase

Note:
The screens and examples in this section are given for Adabas D but are also applicable to the other SQL systems.

Prerequisites

The prerequisites depend on whether you are using your SQL system with Entire Access or Natural SQL Gateway:

  • Entire Access or Natural SQL Gateway must be installed.

  • When using Entire Access, specify the database driver to be used with the dbms parameter in the NATCONF.CFG file. See your Entire Access documentation for more information.

    When using Natural SQL Gateway, specify the SQL server that connects to the target database system.

Restrictions

The following restrictions apply:

  • Ingres
    No rules or procedures are incorporated.

  • Adabas D
    When incorporating Adabas D views, the message "Master missing" does not appear even if the corresponding master file does not exist or is not accessible. All constraints are incorporated as one check constraint of the file object in Predict.

  • Upper/Lower Case
    If the general default parameter Miscellaneous > Upper/lower case > Object ID is set to U and SQL tables and views with mixed-case names are to be incorporated, it is possible that some references are not recognized.

Selecting SQL Tables and Views

The respective Incorporate screen is called with function code I and the corresponding code in a Predict main menu or with the command INCORPORATE <sql-type>. See table below.

SQL System Code Command Word
Adabas D BF ESD-TABLE
Ingres JF INGRES-TABLE
Informix XF INFORMIX-TABLE
Oracle OF ORACLE-TABLE
Sybase YF SYBASE-TABLE

For example, the Incorporate Adabas D Tables/Views screen below is called with command INCORPORATE BF or INCORPORATE ESD-TABLE. 

10:00:30             *****  P R E D I C T  *****             2007-05-31
Plan   0            - Incorporate Adabas D Tables/Views -


Table / View name ....

Select options
  Tables or Views ...*
  Creator name .......

  from DBID .........*
Parameters
Table / View name Name of table / view in the SQL environment. Asterisk notation can be used to specify a range of table/view names.
Select options Determines whether tables or views are to be incorporated.
Tables or Views
T Tables
V Views
Creator name Limits the scope of the function to SQL tables/views with the specified creator.
From DBID Number of the database as defined in the NATCONF.CFG file. This number identifies the SQL system from which tables/views are to be incorporated. Enter an asterisk to display a list of database numbers for selection.

Note:
If you are using more than one SQL system in a network environment, all possible databases are offered for selection, even if they are from another system.

Columns in the List
Creator Creator in the respective SQL system.
Table/view name Name of the table/view.
T
Type of the SQL object:
T SQL table
V SQL view
Remark Either the following remark or blank.
Master missing The specified SQL view cannot be incorporated in Predict until a documentation object for a related SQL table or view has been created.

Note:
This remark is not given when incorporating Adabas D views.

Incorporation Functions for SQL Tables and Views

Valid functions:

Display Masters - Code M

The SQL tables/views a view is related to must be documented in Predict before the SQL view can be incorporated. This subfunction finds all tables/views that must be documented in Predict before table/views can be incorporated.

Incorporate - Code I

Scope of the Incorporation

  • One Predict file object is created for each table or view. The ID of the new file object consists of the creator name and SQL name of the table or view joined by a hyphen.

  • One field object is created for each field in the table or view. The ID of the field is the name of the field in the respective SQL system. For each view that is incorporated, the appropriate parts of the subselect specification in the SQL statement CREATE will be copied to the file and field objects created.

  • One Predict field object (type SP) is created for each composite unique constraint.

  • One Predict field object (type SP) is created for each composite foreign key if there is not already a Predict field object for a composite unique constraint built from the same columns.

  • One Predict file relation object is created for each foreign key.

  • A verification is added and linked to the corresponding field for each check constraint that is defined for one column.

  • Attributes of the corresponding file object are updated for each table check constraint (check expression for more than one column).

  • One trigger object for each trigger that is connected to the file being incorporated. The trigger object is linked to the file object via association "Has TR".

Before the function is executed, enter the following parameters in an additional input screen

Incorporation Options
Comments as abstract
Y String constants assigned with COMMENT ON are incorporated as the abstract of the corresponding Predict object:

  • Comments assigned to the table or view are to be copied to the file object.

  • Comments assigned to a field of the table or view are to be copied to the field object.

Note:
This parameter is not applicable to all SQL systems.

List incorporated code
Y The values of all attributes incorporated for the table or view are to be listed.

Incorporate SQL Tables / Views in Batch Mode

Command: INCORPORATE <sql-type>

See list of codes and command words in the section Selecting SQL Tables and Views.

Enter the parameters on next line in positional or keyword form.

Field Keyword Position
Table/View name TABLE 1
Tables or Views TYPE 2
Creator name CREATOR 3
From DBID DBNR 4
Incorporate comments COMMENT 5
List incorporated code LIST 6
Function SUB-FUNCTION 8

Note:
The keyword ABSTRACT can be used as a synonym for COMMENT.