DMSII DASDL with OCCURS

The following excerpt from a DMSII DASDL shows how an OCCURS clause is defined.

ORDERS                    DATA SET
(
     ORDER-ID                      ALPHA (4);
     ORDER-DATE                      ALPHA (5);
     ORDER-ITEM  OCCURS 10 TIMES     NUMBER (8);
);
     BY-ORDER-ID SET OF ORDERS
     KEY IS
(
     ORDER-ID
)
     NO DUPLICATES,
     INDEX SEQUENTIAL;

The OCCURS clause allows access by subscripting (indexing) within an application program. Because relational databases do not allow subscripting (indexing), the DATABridge Client maps the subscript into an additional key. The OCCURS items, then, are available by row.

When this ORDERS data set is cloned into the relational database, it is mapped into the following two tables. These tables show how the DMSII OCCURS clause appears in a relational database.

Table 1

This table is named the same as the ORDERS DMSII data set, and it contains the key item plus all non-OCCURS items. Assuming the ORDERS DMSII data set has 50 records, this table has 50 rows.

orders (table name)
order_id       order_date
--------       ----------
9801           jan98
 .              .
 .              .
 .              .
9850           feb98

Table 2

This table name combines the DMSII data set name and the name of the data item which has an OCCURS clause. It contains all the occurrences of the OCCURS data item ORDER-NUM.

Continuing with the example from Table 1 with 50 records (rows), this table has 500 total rows. For every order_id key (50 total), there are ten OCCURS items (as declared in the DASDL on the previous page).

orders_order_item (table name)
order_id      index1    order_item
--------      ------    ----------
9801          1         00007390
9801          2         00001293
9801          3         00007748
9801          4         00009856
9801          5         00003736
9801          6         00002278
9801          7         00004327
9801          8         00009463
9801          9         00008638
9801          10        00008954
9802          1         00001754
9802          .         00005309
9802          .         00004537
9802          10        00005940
9803          1         00005430
9803          .         00005309
9803          .         00004537
9803          10        00006587
  .           .             .
  .           .             .
  .           .             .

You can prevent the additional table from being created if you set the Flatten OCCURS option. Doing so will also help you conserve disk space and improve performance.