We are working on a prospect for SQL gateway who is also looking at Treehouse.
This customer has a question about SQL Gateway:
Does the SQL gateway have support for multi value fields and for periodic groups (and MV-fields inside periodic groups).
if so how do we support this ?
Yes. ADASQL v-6.5.2 does support MU/PE and MU within PE. We have tested this with ADASQL-652 on M/F with NATURAL-414+PREDICT-451. Using the SYSTRANS of Natural the DDMs were exported onto a sequential dataset, then FTP on a flat-file, which is then imported on CONNX Data Dictionary under WinXP). While importing the CONNX gives you the option of including the no. of occurences. The Default value is ‘5’.
You can work with multiple value and periodic fields in two ways:
If you create the data dictionary from a SYSTRANS file, when Adabas SQL Gateway found a file with MU/PE fields creates two types of “views” for this file:
1.- A flat file view, which means all the occurences of the MU/PE in the original file are “flattened”, you get a new file with all single fields in the original table and the MU/PE fields repeated as many times as occurences have the original MU/PE field. (ie. if you have a periodic field named “date” - 30 occurs, the flat file contains 30 single fields named “date1”, “date2” … “date30”).
2.- The Gateway creates too “rotated tables”, which means to normalize the file structure. For each file with MU/PE fields the Gateway creates a root table with non repetitive fields plus one table for each MU/PE field. This files are linked within the ISN field of the root table.
My recommendation is to work with the second option (rotated tables), I try this and works fine.
I hope this could help, sorry for my bad english.
Luis B. 