In the -DR Lines tab, you specify
which SQL objects make up the relational database (tables, views,
index). A -DR line definition section displays
the type and external name of the object, 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
open 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, and 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 before V6), INTEREL RDBC, and 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 which has 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.
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
- You enter the name 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 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. maximum) 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
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 (eight 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.
- Table or View code
- Select 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 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 or create a Segment
in a creation wizard that opens when you click New Segment.
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
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 in order 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 packageBODY:
BODY package |
On a C type line (ORACLE V7):
Indicates the package type. BODY package standard package |
A: afterB: 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. |
-GG Lines
In the -GG lines, you can modify the information
contained in the Block Description lines before they are generated.
Depending on the type of description line, lines are automatically
displayed, in order to guide you when you enter the physical information
of the Block. These lines are identified by 'VIRT' or 'GUID'
in the last field of the table of the -GG lines. Collectively, these
lines are called 'virtual' lines. The lines identified by 'VIRT'
are generated. To modify these lines, you must override them and create
new lines between beginning and end insertion lines. When you select
a virtual line and click Override, the 'VIRT'
or 'GUID' line is changed into a 'FromVIRT' or 'FromGUID'
line. Then it can be modified. You can also edit the FromGUID and
FromVIRT lines by selecting them and pressing the F2 key. You can
create lines anywhere in the table. However if you want them to be
taken into account at generation time, you must identify them with
a G Type.
In
the table, you find the following data-entry fields:
- Type
- When you click this field, you can select a line type among:
- Description
- This field must be used to enter the generation elements or comments
to be generated. Its content depends on the type of Block and type
of line selected.
For relational
Database Blocks, virtual lines are automatically generated for each
SQL record type (table, view, index). These lines can be overridden
so that you can modify the characteristics of a column, insert new
lines to specify the source of a column in a view.
- To Modify the characteristics of a column, you must insert
one or more lines between the beginning and the end of the insertion
area. Enter <DELCO> on the first line, then
the new description on the following lines.
- To insert a column into a table, the same technique is
used as mentioned previously . You must insert one or more lines between
the beginning and the end of the insertion area, which is indicated
on virtual lines.
- To override a source column of a column in a view, you
must select the line. Then click Override and
create one or more lines between the beginning and the end of the
insertion area. A formatted 'G' type is required: <FFNN-DELCO>,
where FFNN is the Segment code of the source
and DELCO the new Data Element code.
-K line definition
In the
configuration
key section, select the columns making up the table key
or the columns selected in an alter table and indicate an SQL order
for the generation process.
- Order or action code
- Here, you 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 belonging to an index, you find 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 belonging to an alter table, as you can
see in both previous tables, you can add, delete or modify a column.
Here, you can indicate which SQL order is to be generated for a Data
Element representing 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 which makes up
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 called in the Segment is displayed.
In this list, you can find Data Elements which 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.