Full ARRAY and STRUCT support in IMS Database Service Provider

We require IMS Database Service Provider services to support complex segments with (non-nested) ARRAY fields with many instances and many values per instance (which may be of different data types), using un-flattened array representation, with automatic discovery of ARRAY metadata and automatic retrieval of ARRAY contents, without any additional knowledge beyond the ARRAY name provided as part of an SQL query.

Example:

Given an IMS database WMGAT containing an ARRAY field named GD791 with 5 subfields GD791A, GD791B and GD791C the following query will return all instances of all subfields of the array from a segment or segments matching conditions of the WHERE clause:

SELECT GZ.GD791 FROM WMGAT.GR, WMGAT.GT, WMGAT.GZ WHERE GR.ISIN = ? AND GR.GRAG = '9' AND GR.GRBID = '000000000000' AND GT.GTTART = 'V' AND GT.GTID = 'GD791'

The service interface JSON will only contain one section per subfield (not per instance, as with the current approach):

<field name="GD791" originalName="GD791" included="Y" path="response.GD791">

<startPos>0</startPos>

<bytes>24</bytes>

<maxBytes>24</maxBytes>

<applicationDatatype datatype="ARRAY"/>

<field name="GD791A" originalName="GD791A" included="Y" path="response.GD791A">

<startPos>0</startPos>

<bytes>4</bytes>

<maxBytes>4</maxBytes>

<applicationDatatype datatype="INT" precision="4" scale="0"/>

</field>

<field name="GD791B" originalName="GD791B" included="Y" path="response.GD791B">

<startPos>4</startPos>

<bytes>4</bytes>

<maxBytes>4</maxBytes>

<applicationDatatype datatype="INT" precision="4" scale="0"/>

</field>

<field name="GD791C" originalName="GD791C" included="Y" path="response.GD791C">

<startPos>8</startPos>

<bytes>16</bytes>

<maxBytes>16</maxBytes>

<applicationDatatype datatype="CHAR"/>

</field>

</field>

Ideally STRUCT fields will be similarly supported as they are compareable to ARRAY fields with just one single instance.

For ARRAY fields it would be nice to have an option to limit the number of array instances returned in the JSON output …for instance return up to 10 instance even if the ARRAY field may contain for instance up to 100 – so something similar to what the FETCH FIRST x ROW(S) ONLY clause does for the amount of rows being returned.

Last but not least it would be also nice if the IMS DB service provider could handle ARRAYs spanned accross multiple segments. This could be done for instance by a selectable option "combine array instances from all result set rows to a single array". This would require to limit the SELECT list to either just the ARRAY field or besides the array field only fields from higher-level segments and to limit the result set to return only multiple segments of the segment type containing the array but just one single parent segment respectively a single path of parent segments (in the sample above just the same GR and GT segment for all GZ segments, which is in the sample ensured by specifying the full keys of the GR segment and the GT segment in all their parts in the WHERE clause*). This limitation is something you may just document and the user needs to ensure to take care of.

*the IMS JDBC driver combines the key subfields of the keys during the query execution