In the -DR Lines tab, you specify
which SQL objects constitute the relational database. A -DR
line definition section displays the following information:
the type and external name of the object, the referenced table (for
J type lines only), the code of the Segment that represents the Block,
the key type that is supported by the Segment, and the generation
transaction type.
-DR line definition
The
-DR line
definition section opens when a new description line is
created or selected in the
-DR lines list.
Note: You
can complete a new -DR line in the line definition section or directly
in the table.
- 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.
The following 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, and SYBASE. For ALLBASE/SQL,
the notion of space is 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 before V6), INTEREL RDBC, and INTEREL RFM.
Other SQL objects are recognized in ORACLE V7, SYBASE
and SQL SERVER only:
- Package: package of functions and procedures.
- Function.
- Procedure.
- Trigger.
Objects are related to the space that precedes them.
An alter table must follow the table that it
modifies. An index must follow the table that
it is related to. A primary key or foreign
key must follow the table, which has the same table code.
It is not possible to create an index that is 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.
Table 1. List of SQL objectsValues |
Description |
P: Package |
Table space (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/Constraint name
- You enter the name that is used by the user. It is prohibited
for a primary key (K-type line, DB2, DB2/2, DB2/6000, or DATACOM/DB).
It is required for a table space (P type). For all other objects,
this name can be defined at several levels. Upon generation, the external
name taken into account is, in a decreasing order of priority:
- The external name that is 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), this
field contains the constraint name (8 characters maximum). The code
of the referenced table is required to complete this field.
Prefixing
Mode:
- The presence of a dot in the external name of a table or view
determines the prefixing mode for DB2, SQL/DS, DB2/2, and DB2/6000:
- If you enter the external name as PREFIX.NAME,
this name is processed as such by Pacbase and the DBMS, that
is as explicitly prefixed by you.
- If the external name does not contain a dot or a prefix, Pacbase prefixes it with
your user code (8 characters). The DBMS processes it as such, that
is as explicitly prefixed by Pacbase.
- If you enter the external name as .NAME, Pacbase generates it without
the dot so that the DBMS ensures the prefixing.
- Referenced Table
- This data is significant and required for a J type line only (Foreign
key). It contains the Segment code of the referenced Table, that is
to say, the Segment code of the table line that precedes the foreign
key. To enter or modify the content of this field, click Change.
A dialog box then displays the list of the tables that are referenced
in the current Database Block. Select a table. You can delete the
data by clicking the Remove button.
- Table or View code
- Select the code of the Segment that 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 that supports the key. For a table
space, 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. If the list
is large, you can reduce it by entering a string that is used as filter
(the filtering is performed on the code or on the label of the instance).
CAUTION:
The DB2 or ORACLE Blocks can call only Segments that
contain Large Objects in their composition. The LOBs are not compatible
with any other Block types
When you select a Segment, its code
is displayed here, followed by its label. An icon is displayed before
the Segment code. It shows whether the Segment is a monostructure
Segment or not. Clicking the Segment code switches to the editor
of the selected Segment, where you can see its composition, for example.
- Key type
- In this field, you can select the type of key to be generated.
Options |
Description |
U: UNIQUE |
On an I type line: This
value is entered to generate the UNIQUE command. |
From 0: subschema 10 Data Elements to 9:
subschema 9 Data Elements
|
On a V type line: View
of the subschema Data Element selection in the Segment. Value 0 corresponds
to the subschema 10. |
All: All Data Elements |
All the 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 package BODY:
BODY package |
On a C type line (ORACLE
V7): Indicates the package type. BODY package standard package |
A: after B:
before |
On an R type line: Indicates
where the trigger or the rule starts to operate. |
- Generation transaction type
- Use this field to enter CREATE, ALTER or DROP statements.
Options |
Description |
C: Creation (CREATE) |
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) |
This value is possible on table-type
lines only, on all database types, except SQL/400. It generates an ALTER statement. |
D: Cancellation (DROP) |
This value 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 executed by the GPRT procedure. |
- -G Lines
- This button can be used to access the -G Lines wizard
and create -GG lines associated with the description line to specify
generation elements. You can also add comments on the -GC lines that
are associated with the description line.
-K line definition
In the
configuration
key section, select the columns that constitute the table
key or the columns selected in an alter table. Indicate an SQL order
for the generation process.
- Order or action code
- Indicate the type of the SQL command to be generated for the object.
The choices of the list are specific to an index or an alter table
only.
- For an index and an alter table, you have common choices:
For an index key:Options |
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) |
The 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 see another specific choice in the
list:
Options |
Description |
M: Modification of a column (MODIFY) |
The column is to be modified. |
- For a Data Element that belongs to an index, you can select an
ascending or descending order. You can also select the default sort
order so that no sort DDL command is generated and the default DBMS
command is used. For a Data Element that belongs to an alter table,
you can add, delete or modify a column. You can indicate which SQL
order is to be generated for a Data Element that represents a column: ADD, DROP,
or MODIFY order. 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 that constitute
the key (or index) of the table, or the columns of an alter table.
When you click Change, a dialog box opens
and the list of the Data Elements that are called in the Segment is
displayed. This list can contain Data Elements that are not defined
in the repository, as they were created in the -CE Lines tab
of the Segment. These Data Elements are identified in this field as Data
Element descriptions.