Adabas Table/Cluster/View with Adabas SQL Server

This function is used to create table descriptions, cluster descriptions or views in an Adabas SQL Server catalog.

Predict Object(s) Generated Command Result
One Adabas file corresponding to one SQL table CREATE TABLE DESCRIPTION Adds a description of a table in an Adabas SQL Server catalog without creating an Adabas file.
One AT file corresponding to one SQL table
Multiple AT files corresponding to multiple SQL tables CREATE CLUSTER DESCRIPTION Adds description of several base tables containing multiple value fields or periodic groups with variable occurrences in Adabas SQL Server.

From files of type B, a CREATE VIEW statement is generated.

See also section Adabas SQL Server in the Predict and Other Systems documentation.

This document covers the following topics:

Calling the Function

Display the Generate Adabas table/view screen with function code G and object code EQ in a Predict main menu, or with the command GENERATE ESQ. 

09:33:43             *****  P R E D I C T  *****             2007-05-31
Plan   0               - Generate Adabas table/view -


Current VM ................. HOME
File ID .................... *
Contained in DA ............ *


Add table / view ........... Y (Y,N)               Use Vista access-nr ..* Y
Replace table / view ....... N (Y,N)
Drop table / view cascade .. N (Y,N)

List SQL statements ........ Y (Y,N)

Protocol saved in member ... *
               in library .. ASQLIB



Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
      Help  Next  Stop  Last  LnkEl Flip  Print Impl  AdmFi SelFi Prof  Main

Parameters

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

Presettings
The parameters below can be changed in the Modify Adabas Table/View Defaults screen. See Generation Defaults.
Specification DB ID
Y Database ID must be specified.
N Database ID may be specified.
This parameter is set to N at installation.
Count of saved actions Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
Current VM Adabas tables/views can only be generated from Predict file objects contained in databases of type local linked to the Current VM or contained in databases accessible via the network containing the Current VM. This is a read-only field.
File ID

ID of the Predict file object from which the definitions are to be generated.

To generate an Adabas table, the file must be of

  • type A (with parameter SQL usage set to Y) or

  • type AT.

To generate an Adabas view, the file must be of type B.

To generate an Adabas cluster, the file must be of type AT. Enter only one file from the cluster; all files in the cluster will be used for generation.

Contained in DA

Only applicable to tables. Depending on the setting of Specification DB ID in the Modify Adabas table/view defaults screen, entry of a Database ID is mandatory or optional for Adabas files and userviews.

If a database is specified, its file list must contain the specified file and the Database type must be compatible with the database type specified for this database number within the Natural Parameter Module (via NTDB macro).

Enter an asterisk to display a list of databases for selection.

Use Vista access-nr
N Vista access number is not used.
T

Vista access number is used and the system tests whether a Vista element for this file with the same number exists.

If so, and if no database ID is specified and the file contains a Vista access number, an Adabas table/view is generated with the access number taken from the file.

If the file has no Vista access number, an error message is given.

Y

Vista access number is used, but no testing is performed.

If no database ID is specified and the file contains a Vista access number, an Adabas table/view isgenerated with the access number taken from the file.

If the file has no Vista access number, an error message is given.

Add table/view
Y A new Adabas table, view or cluster is created.
If the external object does not exist, this parameter must be set to Y.
Replace table/view
Y If the Adabas table or view already exists, an Adabas statement DROP TABLE DESCRIPTION or DROP VIEW is generated first.
If a cluster already exists for one of the files in the cluster structure, a DROP CLUSTER DESCRIPTION statement is generated.
Drop table/view cascade
Y

For tables: Tables and all dependent views will be dropped. The statement DROP TABLE DESCRIPTION <file-ID> CASCADE is generated first.

For clusters: All tables in the cluster will be dropped. The statement DROP CLUSTER DESCRIPTION <cluster-name> CASCADE is generated first. The cluster-name is taken from the catalog.

Notes:

  1. If the table already exists and Replace table/view and Drop table/view cascade are set to Y, the dependent views are listed. If the purge is confirmed, the table and all dependent views will be deleted and only the table will be generated. Dependent views must then be generated separately.
  2. For clusters, all base tables which belong to the cluster and all dependent views are listed. If the purge is confirmed, all listed objects will be deleted.
List SQL statements
Y Generated SQL statements are listed.
Protocol saved in member

SQL statements generated for the implementation of the database can be saved in a Natural source member (protocol) on the FDIC file.

A member name for the protocol is only required when the Adabas table/view is generated for the first time. SQL statements generated in subsequent generation runs are concatenated to the protocol.

The protocol can be displayed using the Display function of the Administration Implemented File menu.

Protocol saved in library Library where the protocol is to be saved.

Note:
All tables in a cluster are given the same member/library name.

Recommendations when Using Adabas Vista

If using Predict with Vista distributed data processing, we recommend the following:

  • Always enter a Vista access number for Adabas files (Add, Modify file).

  • Set the parameter Specification DB ID in the Modify Adabas table/view defaults to N (database ID may be specified but is not mandatory). If this parameter is set to Y, the user is forced to enter a database ID when generating an Adabas table/view from an Adabas file.

  • Set the parameter Use Vista access-nr in the Modify Adabas table/view defaults screen to T, but do not protect the field.

If you do not want to use Vista:

  • Set the parameter Use Vista access-nr in the Modify Adabas table/view defaults screen to N and deactivate the option by blanking out the preceding X.

The table below illustrates the use of the parameter Use Vista access-nr.

Specification DB ID Use Vista Access Number Result if Database ID is specified
Y N, Y, T [1] If the file is Simple in this database and the Adabas attribute Vista access only is not set to Y: the Adabas table/view is generated with the physical database number and physical file number.
Y N

[2] If the file is defined as partitioned in this database or the Adabas attribute Vista access only=Y: the system checks whether a Vista element for this file and database exists.

If exactly one element exists, the Adabas table is generated with the logical Vista number defined in this Vista element.

If the Vista numbers are used, the CREATE TABLE DESCRIPTION statement is appended by "MODIFICATION NOT ALLOWED" and has the result that an ALTER statement for this table is rejected in Adabas.

If more than one Vista element is found, no Adabas table is generated and an error message is given.

Y T,Y

Same as above, except that if several Vista elements exist for the file and database, the system checks whether the Vista access number of the file is identical to the Vista file number of one of these Vista elements.

If identical, the Adabas table is generated. If not, no Adabas table is generated and an error message is given.

If the Vista numbers are used, the CREATE TABLE DESCRIPTION statement is appended by "MODIFICATION NOT ALLOWED" and has the result that an ALTER statement for this table is rejected in Adabas.

N Y

If the file does not contain Vista access numbers, an error message is given.

If the file does contain Vista access numbers, an Adabas table is generated with the access number taken from the file.

If the Vista numbers are used, the CREATE TABLE DESCRIPTION statement is appended by "MODIFICATION NOT ALLOWED" and has the result that an ALTER statement for this table is rejected in Adabas.

N Y

If the file does not contain Vista access numbers, an error message is given.

If the file does contain Vista access numbers, the system tests whether Vista elements with the same numbers exist for this file. If so, an Adabas table is generated with this access number.

If the Vista numbers are used, the CREATE TABLE DESCRIPTION statement is appended by "MODIFICATION NOT ALLOWED" and has the result that an ALTER statement for this table is rejected in Adabas.

N Y

If the file does not contain Vista access numbers, an error message is given.

If the file does contain Vista access numbers, the system tests whether a Vista element with the same number exists for this file. If so, an Adabas table is generated with this access number.

If the Vista numbers are used, the CREATE TABLE DESCRIPTION statement is appended by "MODIFICATION NOT ALLOWED" and has the result that an ALTER statement for this table is rejected in Adabas.

Generate Adabas Table/View in Batch Mode

Command: GENERATE ESQ

Enter parameters on next line in positional or keyword form. File ID is obligatory, all other parameters are optional. If a parameter is not specified, the default value is taken.

Field Keyword Position
File ID FILE-ID 1
Database ID DATABASE-ID 2
Add table/view ADD-TABLE 3
Replace table/view REPLACE 4
Drop table/view cascade CASCADE 5
List Adabas statement LIST 6
Protocol saved in member MEM 7
Protocol saved in library LIB 8
Use Vista access-nr USE-ACCESS-NR 9