CONNX creates two styles of SQL tables to represent ADABAS Files that contain MUs or PEs. 1) a flattenedable, and 2) a root table, and one or more rotated tables

Flattened Table

CONNX creates a single flattened table, where each occurrence of an MU or PE is a separate column. The sample ADABAS Employees table contains two period groups, and three multi value fields. One of the period groups contains a multi value field.

 

Figure 1: List of MUs and PEs in the Employees table

ADDRESS_LINE	Multi-value field
INCOME	Period group (This group contains a multi-value field called BONUS.)
BONUS	Multi-value field (within the INCOME period group)
LANG	Multi-value field
LEAVE BOOKD	Periodic Group

 

ADABAS currently has a limit of 191 occurrences for any MU or PE group. If we were to use that limit to fully flatten out the employees table, it would have 37,842 columns. This is primarily because there is a multi value field within a period group, which effectively squares the number of columns in the table. Obviously, a table with 37,842 columns is not very meaningful. Additionally, CONNX has limit of 30,000 columns for any SQL table.

In most cases, even though the maximum # of occurrences possible is 191, the actual number is far less. During the import, CONNX displays a dialog that allows you to specify the number of occurrences to be flattened for every MU and PE within the table being imported. For example, you will get the following dialog for the employees table:

 

Figure 2: Max repeat count dialog displayed during import of the employees table

 

By default, CONNX flattens the first 5 occurrences of each periodic group and multi value field. If you need more occurrences flattened, adjust the max repeat count during the import process on the dialog above.

After the import complete, you will see a flattened table, where the occurrences of each MU and PE are represented as separate columns.

 

Figure 3: Flattened Employee Table