Relational Database Split Tables

A split table occurs when a DMSII data set record requires more than one table in the relational database to hold the data. Split tables occur in the following circumstances:

  • When a table mapped from a DMSII data set has more than the maximum number of columns allowed by the relational database. The maximum_columns parameter in the configuration file allows you to reduce this value.
  • When a relational database table’s record size exceeds the Microsoft SQL Server maximum length (8032 for SQL Server).

When the DATABridge Client software reaches the point where one of the above conditions is satisfied, it stops adding columns to the table (named the same as the DMSII data set). It then starts a new table containing the same keys as in the original record of the primary table, and proceeds to place the remaining data items in this secondary table. The column and record size counters are set to zero before starting to fill the new table. Note that there is always the possibility of having multiple splits for data sets that have a large number of columns. The flattening of OCCURS items can easily lead to split tables.

Note: When a DMSII data set is split into more than one relational database table, a WARNING message appears during the dbutility define command. In addition, the set for the data set is duplicated for each table; therefore, each split table duplicates the original keys.