Flattening OCCURS Clauses

You can use the Client Console or user scripts to control whether items with OCCURS clauses are flattened in the relational database.

The flatten_all_occurs parameter makes the define and redefine command set the value of the bit DIOPT_Flatten_Occurs (1) in the di_options column in the DMS_ITEMS table for all items that have OCCURS clauses. You can set this parameter from the Client Console or by editing the configuration file to specify whether to globally flatten OCCURS clauses for a data source. By using user scripts, you can control this option for individual items.

The DATABridge Client provides two options for handling OCCURS clauses.

Flatten OCCURS to the primary table

Each occurrence of the item is mapped into a separate column in the primary table. Use this method if the number of occurrences is not too large and applications access the occurring items by column name (versus numeric index).

This is the default method for flattening OCCURS clauses and only requires that the above mentioned di_options bit be set in the DMS_ITEM entry for the item with the OCCURS clause.

Flatten OCCURS to a new secondary table

In the secondary table, each of the occurring items are mapped to a single row that contains the keys and all of the occurrences of the item. Use this method to flatten OCCURS clauses that have a large number of occurrences.

To make this happen you need to set the bit DIOPT_FlatSecondary(4096) in the di_options column in the DMS_ITEMS table for any items with an OCCURS clause that you want flattened in this manner. If both this bit and the DIOPT_Flatten_Occurs bit are set, this bit takes precedence.