Handling DMSII OCCURS

An OCCURS clause is a DMSII construct that describes the number of times an item is present or repeats within a data set. Because relational databases do not support the OCCURS construct, these clauses generate additional tables, which can degrade the performance of update processing.

You can control how items with an OCCURS clause are mapped on an item by item basis by flattening OCCURS. See Flattening OCCURS Clauses.

Default OCCURS Handling

If you don't flatten OCCURS, DATABridge Client creates a new table for each data item that contains an OCCURS clause. The keys from the data item’s parent data set are used as keys in the new table. In addition, a new key (named index1) is created to establish a unique composite key for each recurring data item.

For example, a DMSII data set has a data item with an OCCURS clause will result in two relational database tables:

  • The first table (called the primary table) is named using the lowercase form of the DMSII data set name with all hyphens changed to underscores. It contains the key items as well as all data items that do not have OCCURS clauses.
  • The second table (called the secondary table) is named by appending an underscore and the data item name to the primary table name. This table contains all of the OCCURS items; however, each table has a unique key created by index1. (Names that exceed the character limit are truncated. If the truncation results in a duplicate item names, the last characters of the name are changed to digits).

Handling OCCURS items this way can significantly degrade the performance of update processing if the number of occurrences is large. The storage required to hold the keys of the secondary table items can also be substantial. For example, an OCCURS 100 TIMES clause can turn a single DMSII update into 101 relational database updates. See DMSII DASDL with OCCURS for an example of a DMSII data set that has a data item with an OCCURS clause.