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
--------       ----------
1201           jan12
 .              .
 .              .
 .              .
1250           feb12

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
--------      ------    ----------
1201          1         00007390
1201          2         00001293
1201          3         00007748
1201          4         00009856
1201          5         00003736
1201          6         00002278
1201          7         00004327
1201          8         00009463
1201          9         00008638
1201          10        00008954
1202          1         00001754
1202          .         00005309
1202          .         00004537
1202          10        00005940
1203          1         00005430
1203          .         00005309
1203          .         00004537
1203          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.