Multivalued/Multi-Subvalued Data and Associations

Both UniVerse and UniData® support multivalued and multi-subvalued data and associations, which allow a relationship between a number of multivalued/multi-subvalued fields within a record to be identified. For IBM® InfoSphere® DataStage® 3.1 and later, meta data imported from UniVerse or UniData includes information about whether a field is single-valued, multivalued, or multi-subvalued, and whether it is part of an association. This is determined as follows in both UniVerse and UniData dictionaries:

The value of field 6 indicates the field type. S or '' denotes single-valued, M or MV denotes multivalued, and MS denotes multi-subvalued.
The value of field 7 is the name of the association to which the field belongs.
A field that is multivalued and does not belong to an association is treated as a member of an association whose name is the same name as the field.

UniVerse dictionaries can also contain Pick-style A- and S-type entries which are interpreted differently:

The value of field 4 indicates a multivalued attribute. It contains an entry of the form C;n[;m]... for a controlling attribute or D;n for a dependent attribute. When importing meta data, this is handled like an association with the same name as the controlling attribute. All the controlling and dependent attributes appear as multivalued fields that are members of this association.
The value of field 5 can be used to override the field type. S or '' denotes single-valued, M or MV denotes multivalued, and MS denotes multi-subvalued.

UniVerse, UniData, and Hashed File stage outputs recognize the presence of multivalued and multi-subvalued column meta data. These stages offer the user the option to normalize the data with respect to a single association generating a separate row of column values for each multivalue in an association. The Un-Normalized options deliver all of the values as a dynamic array containing value marks and subvalue marks.

The Data Browser Display... button also offers the Un-Normalized (formatted) option, which formats the data in the grid with each value on a separate line in a similar way to RetrieVe or UniQuery.

The following example shows how records containing multivalued data are displayed for a table with six columns: two single-valued, two multivalued and two multi-subvalued. The multivalued and multi-subvalued fields all belong to a single association.

Column Name	Field No.	Field Type	Association
Key	0	S
Field1	1	S
Field2	2	MV	Assoc1
Field3	3	MV	Assoc1
Field4	4	MS	Assoc1
Field5	5	MS	Assoc1

The sample records contain the following data:

ID:          A
DATA:         BfmCvmGvmNvmRfmDvmHvmOvmSfmEvmIsmKsmMvmPvmTsmVfmFvmJsmLvmQvmUsmW
ID:          X
DATA:          Yfmfmfmfm

The second record is used to illustrate the differences in normalization behavior between UniVerse and UniData when the associated multivalued fields are empty.

The unnormalized view of the table returns the following column values:

Key	Field1	Field2	Field3	Field4	Field5
`A`	`B`	`CvmGvmNvmR`	`DvmHvmMvmS`	`EvmIsmKsmMvmPvmTsmV`	`FvmJsmLvmQvmUsmW`
`X`	`Y`

The unnormalized (formatted) view of the table, only available from the Data Browser, formats the previous row as follows. This is similar to the layout that RetrieVe or UniQuery produce.

Key	Field1	Field2	Field3	Field4	Field5
A	B	C	D	E	F
		G	H	I	J
				K	L
				M	Null
		N	O	P	Q
		R	S	T	U
				V	W
X	Y	Null	Null	Null	Null

The view of the table normalized on Assoc1 will vary slightly between UniData files, UniVerse files, and UniVerse tables.

Key	Field1	Field2	Field3	Field4	Field5
`A`	`B`	`C`	`D`	`E`	`F`
`A`	`B`	`G`	`H`	`I`	`J`
`A`	`B`	`G`	`H`	`K`	`L`
`A`	`B`	`G`	`H`	`M`	`Null`
`A`	`B`	`N`	`O`	`P`	`Q`
`A`	`B`	`R`	`S`	`T`	`U`
`A`	`B`	`R`	`S`	`V`	`W`
`X`	`Y`	`Null`	`Null`	`Null`	`Null`

The result set above will be returned by the UniData stage or the Data Browser during UniData file meta data import, and is equivalent to that returned by the UniData SQL query:

SELECT Key,Field1,Field2,Field3,Field4,Field5 FROM Table1 
UNNEST Field2,Field3,Field4,Field5

The result set returned by the Hashed File stage or the Data Browser during UniVerse file meta data import differs from the above in that the final row will be omitted:

Key	Field1	Field2	Field3	Field4	Field5
`A`	`B`	`C`	`D`	`E`	`F`
`A`	`B`	`G`	`H`	`IsmKsmM`	`JsmL`
`A`	`B`	`N`	`O`	`P`	`Q`
`A`	`B`	`R`	`S`	`TsmV`	`UsmW`

The result set above will be returned by the UniVerse stage or the Data Browser during UniVerse table meta data import, and is equivalent to that returned by the UniVerse SQL query:

SELECT Key,Field1,Field2,Field3,Field4,Field5 
FROM UNNEST Table1 ON Assoc1