DMSII Nested OCCURS

The following is an excerpt from a DMSII DASDL showing a GROUP with an OCCURS clause that contains an item with an OCCURS clause.

This example helps to reinforce the previous examples of how DMSII GROUP and OCCURS are mapped to a relational database.

SALES                 DATA SET
 PRODUCT-CODE         ALPHA (10);
 PRODUCT-NAME         ALPHA (20);
 SALES-HISTORY GROUP  OCCURS 5 TIMES %FIVE YEAR HISTORY
   (
   TOTAL-UNITS-SOLD      NUMBER (10); %FOR THE YEAR
   MONTHLY-SALES-AMOUNT  NUMBER (S12,2) OCCURS 12 TIMES;
   );

SH-PRODUCT-CODE-SET     SET OF SALES-HISTORY
   KEY IS
   (
   PRODUCT-CODE
   )
NO DUPLICATES,
INDEX SEQUENTIAL;

When this SALES data set is cloned into the relational database, it is mapped into the following three tables:

• sales
  (primary table, table name derived from datasetname)
• sales_sales_history
  (secondary table, table name derived from datasetname + GROUPOCCURSname)
• sales_monthly_sales_amount
  (secondary table, table name derived from datasetname + OCCURSitemname)

Table 1

This table is named the same as the SALES DMSII data set.

It contains the key item and all non-OCCURS data items. Because the GROUP has an OCCURS clause, none of the GROUP items are included in this table. Assuming there are five records in the DMSII data set, there are five rows in the resulting relational database table.

sales  (table name)
product_code      product_name
------------      ------------
BC99992121        Widget
TR55553440        Mixer
HM44447322        Gadget
PP77778299        Twirler
DG22221163        SuperMix

This table is named the same as the SALES DMSII data set.

It contains the key item and all non-OCCURS data items. Because the GROUP has an OCCURS clause, none of the GROUP items are included in this table. Assuming there are five records in the DMSII data set, there are five rows in the resulting relational database table.

sales  (table name)
product_code      product_name
------------      ------------
BC99992121        Widget
TR55553440        Mixer
HM44447322        Gadget
PP77778299        Twirler
DG22221163        SuperMix

Table 2

This table is named: datasetname + GROUP_OCCURS_name

Assuming there are five records in the DMSII data set, there are 25 rows in this table. Note the addition of index1 to denote the occurrence number of the group.

sales_sales_history (table name)

product_code   index1     total_units_sold
------------   ------     ----------------
BC99992121     1          55543665
BC99992121     2          98075300
BC99992121     3          77476478
BC99992121     4          76593939
BC99992121     5          33728282
TR55553440     1          87548974
TR55553440     2          56722221
TR55553440     3          11910078
TR55553440     4          47589474
TR55553440     5          57987999
HM44447322     1          75533785
HM44447322     2          33673391
HM44447322     3          74904532
HM44447322     4          98724498
HM44447322     5          39875992
   .           .             .    
   .           .             . 

Table 3

This table is named: datasetname + OCCURSitemname

Assuming there are five records in the DMSII data set, there are 300 rows in this table (12 occurrences of monthly_sales_amount for each of 5 occurrences of sales_history for each product code). In the table below, index1 is the subscript of the GROUP OCCURS (1–5) and index2 is the subscript of the monthly sales amount, with subscripts (1–12).

In this example, the OCCURS level of the items MONTHLY-SALES-AMOUNT is 2, while the OCCURS level of the item SALES-HISTORY is 1.

sales_monthly_sales_amount (table name)
product_code   index1   index2   monthly_sales_amount
------------   ------   ------   --------------------
BCS9992121     1        1        1075.36
BCS9992121     1        2        49397.90
BCS9992121     1        3        49375.93
BCS9992121     1        4        22840.97
BCS9992121     1        5        38984.02
BCS9992121     1        6        40039.84
BCS9992121     1        7        33875.93
BCS9992121     1        8        35000.22
BCS9992121     1        9        65876.52
BCS9992121     1        10       20402.55
BCS9992121     1        11       17575.00
BCS9992121     1        12       41938.74
BCS9992121     2        1         . 
BCS9992121     2        2          . 
BCS9992121     2        3          . 
BCS9992121     2        4          . 
BCS9992121     2        5          . 
BCS9992121     2        6          . 
BCS9992121     2        7          . 
BCS9992121     2        8          . 
BCS9992121     2        9          . 
BCS9992121     2        10         . 
BCS9992121     2        11         . 
BCS9992121     2        12         . 
BCS9992121     3        1          . 
     .         .        .          . 
     .         .        .          . 
     .         .        .          . 
     .         .        .          .