How CONNX Handles Adabas Periodic Groups and Multi-Value Fields
CONNX creates two styles of SQL tables to represent ADABAS files that contain MUs or PEs: a flattened table, 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.
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 number 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.
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.
After the import completes, you will see a flattened table, where the occurrences of each MU and PE are represented as separate columns.
Rotated Tables
CONNX creates rotated (non-root) tables, one for each top level MU and PE within the ADABAS file.
After CONNX performs the import, in addition to the root table, there will be new tables for every MU, PE, and MUPE (MU within a PE). CONNX has converted the ADABAS file hierarchical structure into relational tables. SQL calls can be made to these relational tables.
CONNX uses the ISN number as the primary unique key for all ADABAS tables. The ISN is always the first field of any CONNX ADABAS table. The naming convention for the ISN field is ISN_database id_file id.
Each rotated table will have a unique primary key that combines the ISN number with at least one array occurrence (psuedo) column.
All rotated tables have at least one extra pseudo column called CNXARRAYCOLUMN.
For MU or PE tables, CNXARRAYCOLUMN is a zero-based numeric field that indicates which occurrence of the MU/PE is represented by the record. CNXARRAYCOLUMN will be inserted into the table right after the ISN number.
If the rotated table happens to be a MUPE table, there will be two extra psuedo columns for every row - CNXARRAYCOLUMN and CNXARRAYCOLUMN_2:
CNXARRAYCOLUMN is the first array occurrence column. It refers to the specific PE row.
CNXARRAYCOLUMN_2 is the second array occurrence column. It refers to the specific MU occurrence within the Periodic Group of which it is a child of. It will be inserted into the table right after CNXARRAYCOLUMN.
The type of rotated table determines how many fields the primary unique key will contain:
The primary unique key for MU and PE rotated tables always contains two fields: the ISN and CNXARRAYCOLUMN.
The primary unique key for MUPE rotated tables always contains three fields: the ISN, the CNXARRAYCOLUMN, and the CNXARRAYCOLUMN_2.
In order to retrieve data from both MU/PE fields, and non-MU/PE fields, you must join the "ROOT" version of the table with the appropriate rotated representations of the desired MU and PE fields using the ISN field. Many GUI query tools attempt to guess which fields link tables together based on field names, and whether they are a part of a unique index. When you drag and drop the root table and a rotated table into the query designer of any of these GUI query tools, they correctly link the tables together based on name of the ISN field. Most importantly, the tools do not erroneously link unrelated tables together, because the name of the ISN field varies between Adabas files.
In order to link two tables, drag and drop a field from one table to the other. Linking across different tables does not occur automatically.