In the -DR line tab, you specify which
SQL objects make up the relational Database (Tables, Views, Index...).
A -DR line definition section displays the
object's type, external name, the code of the Segment representing
the Block, the key type supported by the Segment and the generation
code.
-DR line definition
The
-DR line
definition and
-GG lines sections
opens simultaneously when a new description line is created or selected
in the
-DR lines list.
- SQL record type
- In this field, you can select the type of the SQL object. The
contents of this list vary according to the Block type selected in
the Database Block Definition.
Some SQL objects are not recognized
in some Database Block types:
- the Space object is not recognized in SQL SERVER, NONSTOP SQL,
DBD2/D2, DB2/600, INTEREL RDBC, INTEREL RFM, SYBASE. For ALLBASE/SQL,
the notion of Space has been assimilated to the notion of DBEfileset.
- the Alter Table object is recognized in INTEREL RFM, but you can
only add columns.
- the Foreign Key object is not recognized in, NONSTOP SQL, ORACLE
(versions prior to V6), INTEREL RDBC, INTEREL RFM.
Other SQL objects are recognized only in ORACLE V7, SYBASE
and SQL SERVER:
- Package: package of functions and procedures.
- Function,
- Procedure,
- Trigger,
Objects are related to the Space which precedes them.
An Alter Table must follow the Table it modifies.
An Index must follow the Table to which it is related.
A Primary key or Foreign key must
follow the table holding the same Table code. It is not possible
to create an Index associated with a View ; the generated CREATE INDEX command will be rejected when the catalog
is updated. For RDMS and ALLBASE/SQL, the Primary Key is not generated by an I line ; it is included
in the generation of the Table that precedes it (T line). The type of Index, Primary key or Foreign key must not be modified: these lines
must be deleted, then created with another type. The Table code entered
on Index, Primary key or Alter lines must not be modified.
Values |
Descrition |
P: Package |
Tablespace (except for Interel RDBC, Interel
RFM, Nonstop SQL, Sybase and SQL Server) |
T: Table |
Table |
V: View |
View |
I: Index |
Index |
A: Alter table |
Alter Table: Column updating |
K: Primary key |
RDMS: Primary Key (Processed with the generation
of the table that precedes it.) DB2, Datacom/DB, SQL/DS, Oracle
V6 and V7, DB2/2, DB2/6000, Sybase and SQL Server: Primary key (Processed
with the generation through an ALTER TABLE command.)
|
J: Foreign key |
DB2, Datacom/DB, SQL/DS, Oracle V6 and V7, Sybase
and SQL Server: Foreign key (Processed with the generation through
an ALTER TABLE command.) |
C: Package |
Package (Oracle V7 only) |
E: Function |
Function (Oracle V7 only) |
Q: Procedure |
Procedure (Oracle V7, Sybase, SQL Server) |
R: Trigger |
Oracle V7, Sybase and SQL Server: Trigger |
- External name
- You enter here the name used by the end-user. It is prohibited
for a Primary Key (K-type line, DB2, DB2/2, DB2/6000 or DATACOM/DB).
It is required for a Tablespace (P-type ). For all other objects,
this name may be defined at several levels. Upon generation, the external
name taken into account is, in a decreasing order of priority:
- the external name defined here,
- the one defined in the Structure code field
on the Segment Definition tab, defining the corresponding
object,
- the code of the Segment defining the corresponding object.
For a Foreign Key (J-type line), two separate codes are required:
the constraint name (8 char. maxi) and the Segment code of the reference
Table, that is to say, the segment code of the Table line preceding
the Foreign key. Prefixing Mode The presence of a dot in the external
name of a Table or View will determine the prefixing mode for DB2,
SQL/DS, DB2/2 and DB2/6000:
- If you enter the external name as PREFIX.NAME, this name will be processed as such by the System and the DBMS
i.e. as explicitly prefixed by you.
- If the external name does not contain a dot or a prefix, VA Pac
prefixes it with your user code (8 characters). The DBMS will process
it as such, i.e. as explicitly prefixed by VA Pac.
- If you enter the external name as .NAME, VA Pac generates it without the dot so that the DBMS ensures the
prefixing.
- Table or View code
- Select here the code of the Segment which represents the columns
of a Table, an Alter Table or a View. For an Index, a Primary or
Foreign key, you must select the Segment which supports the key. For
a Tablespace, this field must be left blank. To enter or modify the
contents of this field, click Change. In the
dialog box that opens, you can select a Segment in the list or create
a new Segment in a creation wizard that opens when you click on New Segment.
CAUTION:
Caution : The DB2 or
Oracle Blocks only can call Segments containing Large Objects in their
composition. The LOBs are not compatible with any other Block types
Once you have selected a Segment, the code of this Segment is displayed
here, followed by its label. An icon is displayed before the Segment
code ; it shows whether the Segment is a mono-structure Segment or
not. Clicking the Segment code switch to the selected Segment's
editor so as to view its composition for example.
- Key type
- In this field, you can select the type of key to be generated.
Options |
Description |
U: UNIQUE |
On a I-type line: This value
is entered in order to generate the UNIQUE command. |
From 0: sub-schema 10 Data elements to 9: sub-schema 9 Data elements |
On a V-type line: View of the
sub-schema Data Element selection in the Segment. Value 0 corresponds
to the sub-schema 10. |
All: Data elements |
All Data Elements of the Segment are included
in the View. |
R: restrict C: Cascade S: set null |
On a J-type line: Restrict
(default value for DB2-type Databases only)
Cascade (DB2, SQL/DS,
DB2/2, DB2/6000 and ORACLE V7 only)
S: Set null (DB2, SQL/DS,
DB2/2 and DB2/6000 only)
|
BLANK: Standard packageBODY: BODY package |
On a C-type line (ORACLE V7):
Indicates the package type. BODY package standard package |
A: afterB: before |
On a R-type line: Indicates where the trigger
or the rule starts to operate: After Before |
- Generation transaction type
- This field enables you to enter CREATE, ALTER or DROP statements.
Options |
Description |
C: Creation (CREATE) |
'Creation' is the default value when the line
is created. It generates a CREATE statement. It is the only possible
value for an Alter Table. |
M: Modification (ALTER) |
'Modification' i spossible on Table-type lines only, on all Database types, except SQL/400. It generates
an ALTER statement. |
D: Cancellation (DROP) |
Deletion' generates a DROP statement. For J (Foreign Key) and K (Primary Key) lines, a DROP PRIMARY KEY or DROP FOREIGN KEY command is generated
in an ALTER TABLE command.
|
: No generation |
No generation will be executed via the GPRT
procedure. |
-GG Lines
For Relational
Database Blocks, virtual lines are automatically generated for each
SQL record type (Table, View, Index...). They can be overriden so
that you can modify the characteristics of a column or new lines can
be inserted so as to specify the source of a column in a View.
- To override a column's pre-defined values, you identify
the column by its Data Element code. After identifying the Column,
you click Override and enter another value.
The generated 'Virtual' lines indicate where to place the insertion
lines via '—> COLUMNS INSERTION BEGINNING<--' and '--> COLUMNS
INSERTION END <—'. To override the Column's pre-defined values,
you identify the Column by its Data Element code left-justified between
a '<' sign and a '>' sign. These delimiters must be six positions
apart. After identifying the Column, you enter the desired values.
To complete the generated data, you enter, after LIBFO a space and
the 'ADD' keyword, followed by the data of your choice. Enter the
end mark on the column declaration line because it will not be automatically
generated.
Example: Modification of
a Column format:
G CREATE TABLE (EXTERNAL NAME) IN DATABASE-NAME
G ---> COLUMN INSERTION BEGINNING <---
G LIBFO
G DO10LIBFO FLOAT
G ---> COLUMN INSERTION END <---
G IN DATABASE-TABLESPACE
In this case,
the name will be
DO10LIBFO, the format
will be
FLOAT. This method cannot be
used to delete a Column. For DB2, the comma (separator) is generated
at the end of each line. In order to avoid this generation, a
& must be inserted after the last non-blank character
of the line.
- Source columns:
To insert a Column into a Table, the
same technique as above is used, you must insert line(s) between the
beginning and the end of the insertion area, which is indicated on
virtual lines.
In order to
override a source Column of a
Column in a View, you must create a 'G'-type line formatted as
follows:
FFNNDELCO
FFNNDELCO being the code entered
in the SOURCE COLUMN field.
Example: G CREATE VIEW (VIEW NAME)
G ---> COLUMN INSERTION BEGINNING <---
G <LIBFO>
G DO10LIBFO FLOAT
G ---> COLUMN INSERTION END <---
G AS SELECT ALL
G ---> SOURCE COLUMN INSERTION BEGINNING <---
G <FFNN-DELCO>
G 'X'
G ---> SOURCE COLUMN INSERTION END <---
G FROM (SOURCE TABLES NAME)
You
must manage the Source Table (after the FROM clause).
-K line definition
In the
configuration
key section, you select the Columns making up the Table
key or the Columns selected in an Alter Table and indicate an SQL
order for the generation.
- Order or Action code
- Here, you indicate the type of SQL command to be generated for
the block's object. The choices you find in the dropdown list
are specific to an Index or an Alter table only.
- For an Index, you can choose between the following options:
Table 1. For an Index keyOptions |
Description |
No order or add a column (ADD) |
No DDL sort command is generated (default value) |
A: Ascending order or deletion of a
column (DROP) |
Tthe column is sorted in ascending order |
D: Descending order or deletion of a
column (DROP) |
The column is sorted in descending order. |
- For an Alter table, you can choose between the following options:
Table 2. For an Alter tableOptions |
Description |
No order or add a column (ADD) |
Default value : the Column is to be added. |
A: Ascending order or deletion of a
column (DROP) |
The Column is to be deleted. |
D: Descending order or deletion of a
column (DROP) |
The Column is to be deleted. |
M: Modification of a column (MODIFY) |
The Column is to be modified. |
- For a Foreign or Primary key, no orders are to be specified.
- Data element/data element description
- In this section, you can select the Data Element which will make
up the key (or Index) of the Table, or the columns of an Alter Table.
When you click Change, a dialog box opens
and displays a list of the Data elements called in the Segment. You
can find in the list Data elements which are not defined in the Repository,
as they have been created in the -CE Lines tab
of the Segment; they are identified in this field as Data
element descriptions.