-DR Lines in a Database Block

In the -DR Lines tab, you describe the SQL objects that constitute the relational database. You specify this information directly in the table or in the call line definition section that opens when you create a line or select a line in the table.

The Comments column is displayed in the table only. 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 were 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 section

The following explanations refer to the fields as they are displayed in the call line definition section.
SQL record type
Select the type of the SQL object. The contents of the list vary according to the Block type that is selected in the Database Block Definition.

The space object is not recognized in SQL SERVER, DBD2/2, and DB2/6000.

Other SQL objects are recognized in ORACLE V7 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 that has the same table code. It is not possible to create an index that is associated with a view because the generated CREATE INDEX command will be rejected when the catalog is updated. 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 objects
Values Description
P: Package Table space (except for SQL SERVER)
T: Table Table
V: View View
I: Index Index
A: Alter table Columns update
K: Primary key Processed upon generation by an ALTER TABLE command (DB2, DATACOM/DB, SQL/DS, ORACLE V7, DB2/2, DB2/6000, and SQL SERVER)
J: Foreign key Processed upon generation by an ALTER TABLE command (DB2, DATACOM/DB, SQL/DS, ORACLE V7, and SQL SERVER)
C: Package Package (ORACLE V7)
E: Function Function (ORACLE V7)
Q: Procedure Procedure (ORACLE V7 and SQL SERVER)
R: Trigger Trigger (ORACLE V7 and SQL SERVER)
External name/Constraint name
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 recognized external name is, in a decreasing order of priority:
  • The external name from this field,
  • The external name from the Structure code field in the Definition of the Segment that defines the corresponding object,
  • The Segment that defines 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 in this field.

The presence of a dot in the external name of a table or view determines the prefixing mode for DB2, DB2/2, and DB2/6000 in the following way:
  • If you enter the external name as PREFIX.NAME, the name is explicitly prefixed by you.
  • If you enter the external name without a dot or a prefix, it is automatically prefixed with your user code (8 characters).
  • If you enter the external name as .NAME, it is generated without the dot so that the DBMS ensures the prefixing.
Referenced Table
This field is significant and required for a J type line only (foreign key). It contains the Segment code of the referenced table. It is 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 and click OK. It is then displayed in the Referenced Table column.

You can specify the table directly from the Referenced Table column.

Two methods are possible:
  • Place the cursor in the column and enter the name directly. To make the input easier, you can open a content assist wizard by clicking Ctrl and the space bar simultaneously. If you start entering a name, the content assist displays the instances that start with this input. If you enter the name of an instance that does not exist and if you validate, the input is discarded.
  • Click the button that appears when you set your cursor in the column. In the selection wizard that opens, select an instance.
Table or View code
Select 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, select a Segment in the list and click OK. It is then displayed in the Table/View column.

You can specify the Segment directly from the Table/View column.

Two methods are possible:
  • Place the cursor in the column and enter the name directly. To make the input easier, you can open a content assist wizard by clicking Ctrl and the space bar simultaneously. If you start entering a name, the content assist displays the instances that start with this input. If you enter the name of an instance that does not exist and if you validate, the input is discarded.
  • Click the button that appears when you set your cursor in the column. In the selection wizard that opens, select an instance.
Note: Only the DB2 or ORACLE Database Blocks can call Segments that contain Large Objects in their composition. The Large Objects are not compatible with any other Database Block types.
When you select a Segment, its code is displayed in this field. It is 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 switches to the editor of the selected Segment.

Key type
In this field, you can select the type of key to be generated.
Table 2. Possible value of the key type
Options Description
U: UNIQUE On an I type line, it generates the UNIQUE command.
0: subschema 10 Data Elements to

9: subschema 9 Data Elements

On a V type line, this value represents the view that is constituted by the selected Data Elements of the subschema in the Segment. Value 0 corresponds to 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, it generates the RESTRICT command (default value for DB2 databases only).

CASCADE command for DB2, DB2/2, DB2/6000, and ORACLE V7 only.

SET NULL for DB2, SQL/DS, DB2/2 and DB2/6000 only.

BLANK: Standard package BODY: BODY package On a C type line (ORACLE V7), it indicates the package type (BODY package or standard package).
A: after B: before On an R type line, it 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 type
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. 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.

Blank: No generation No generation is run.
-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 statement for the generation process.
Order or action code
Indicate the type of the SQL statement 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 table
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.
The following value is specific to an alter table.
Table 5. Order or action code for an alter table only
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 statement (ADD, DROP, or MODIFY) is to be generated for a Data Element that represents a column. For a foreign or primary key, no statement is to be specified.
Data Element/Data Element description
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 with the list of the Data Elements that are called in the Segment. This list can contain Data Elements that are not defined in the repository but created in the -CE Lines tab of the Segment. These Data Elements are identified in this field as Data Element descriptions. Select a Data Element and click OK. It is then displayed in the Data Element column of the table.

You can specify the Data Element directly from the Data Element column.

Two methods are possible:
  • Place the cursor in the column and enter the name directly. To make the input easier, you can open a content assist wizard by clicking Ctrl and the space bar simultaneously. If you start entering a name, the content assist displays the instances that start with this input. If you enter the name of an instance that does not exist and if you validate, the input is discarded.
  • Click the button that appears when you set your cursor in the column. In the selection wizard that opens, select an instance.

Feedback