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.
The Comments column is displayed
only in the table of the tab. It does not correspond to a field of
the call line definition section. It contains an asterisk on the call
lines that have been assigned -G lines. These secondary-level -G lines
have been entered with the -G Lines button
in the call line definition section. If you hover your cursor on the
asterisk, the -G lines of the call line are displayed in a tooltip.
If you click the button that is associated with the asterisk, the
-G lines are displayed in their tabular editor, where you can modify
them. You can also display and modify the -G lines from the Edit
comments choice in the contextual menu of the call line.
-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
that is 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 that is 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.
Table 2. Possible value of the key typeOptions |
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.
Table 3. Possible
values of the generation transaction typeOptions |
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 run by the GPRT procedure. |
- -G Lines
- This button can be used to access the -G Lines wizard
and create -GG lines that are 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 that are 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.
- The following values are common to an index and an alter table.
Table 4. Order or action code for an index key and an alter tableOptions |
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. |
- The following value is specific to an alter table.
Table 5. Order
or action code for an alter table onlyOptions |
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 constitutes
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.