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. This ISN field has a naming convention of ISN_<database id>_<file id>.
Example: The ISN field for file #4 contained within database #12 would be ISN_12_4.
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.
Example:
Since the sample employees table contains 4 top level MU/PEs, CONNX creates 4 rotated tables and 1 root table.
Figure 1: List of SQL tables created by CONNX for the sample employees table
|
EMPLOYEES_ROOT |
The root table |
|
EMPLOYEES_ADDRESS_LINE |
The rotated table that represents the ADDRESS_LINE multi-value field. |
|
EMPLOYEES_INCOME |
The rotated table that represents the INCOME period group, and also the BONUS multi-value field contained within the periodic group. |
|
EMPLOYEES_LANG |
The rotated table that represents the LANG multi-value field. |
|
EMPLOYEES_LEAVE_BOOKED |
The rotated table that represents the LEAVE_BOOKED period group. |
|
EMPLOYEES |
The flattened version of the employees table, discussed in the prior section. |
In Figure 2, you can see that for the physical ADABAS record with an ISN of 1101, there are 3 address lines. Accordingly, three records are returned for this ISN, each with a cnxarraycolumn of 0, 1, and 2 respectively.
Figure 2: Example of rotated array in Microsoft Access
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 such as Microsoft Access and Crystal Reports 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.
Figure 3 illustrates how Access properly links the Root and the rotated table together based on the ISN. The ISN of the vehicles tables cannot be linked to the employees table, because the field names are different.
Figure 3: Auto linking of fields in Microsoft Access
In order to link the Employees to the Vehicles table, drag and drop the personnel_id field from one table to the other. Linking across different tables does not occur automatically.