-DR Lines in a Database Block

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 objects
Values 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.

Feedback