5.5.4 Arrayed data format for tables containing repetition columns

This section describes the method for specifying arrayed data in an input data file for tables containing repetition columns.

The format that the input data file takes depends on the input data format (DAT format, binary format, or fixed-size data format), on the arrayed data format (FF, FV, or VV), and whether the data consists of a variable-length data type.

Organization of this subsection
(1) Specification format
(2) Handling of null values for columns and elements
(3) Relationship between the number of elements in input data and the number of elements specified in table definition
(4) Specification example

(1) Specification format

The table below shows the methods for specifying arrayed data in an input data file for tables containing repetition columns.

Table 5-35 Methods for specifying arrayed data in an input data file for tables containing repetition columns

DescriptionFile formatArrayed data format
FFFVVV
Specifying current number of elements in input data--Not specifiedSpecified
Method for specifying current number of elements----For DAT format, specify in characters. For fixed-size data or binary format, specify in 2-byte numeric values (hexadecimal).
Number of element data items specified in input data--A number less than or equal to the number of elements specified in table definition. See (3) Relationship between the number of elements in input data and the number of elements specified in table definition.Current number of elements
Number of elements stored in database--Depends on how null values are treated. See (2) Handling of null values for columns and elements.Current number of elements
Specifiable input data formatsDAT formatSpecifiableNot specifiableSpecifiable
Binary formatVariable length not specifiableSpecifiable
Fixed-size data formatSpecifiableNot specifiable (because all row data are fixed length)
Null value specification method (for elements)DAT formatSpecify by using consecutive delimiters as in normal columns. See section 5.5.1 DAT format.--
Binary formatSpecify null-equivalent values in null-value/information file as in normal columns.
Fixed-size data formatSpecify null-equivalent values in column structure information file as in normal columns.--
Null-value specification method (for columns)--Specify a null value in all elements. Either a null value can be assigned to all elements or a column can be specified as a null value. See (2) Handling of null values for columns and elements.Specify 0 as the current number of elements.
Number of elements stored in database--Depends on how null values are treated. See (2) Handling of null values for columns and elements.Stores as many data items as there are specified current elements.
Variable-length data type specification methodDAT formatSame as normal column--Same as normal column
Binary formatNot specifiableSpecify valid data length (no fill characters).
Fixed-size data formatSpecify data equal to table definition length and fill the remainder.--
Element data delimiters for DAT-format files--Same as column delimiters--Same as column delimiters
Element data error checking--As with normal columns, perform data-type specific checking; for FF and VV formats, check the current number of elements. For FF and VV formats, specify data for the part other than the effective elements, but do not check the data following the effective element data.

--: Not applicable

(a) DAT format

In a DAT-format file, you can specify arrayed data in FF or VV format.

FF format
Specify as many data items as there are specified elements by separating each data item by a delimiter. If you omit a column structure information file or the number of elements in the column structure information file, the system assumes the maximum number of elements as the specified number of elements.
VV format
Specify the current number of elements in the range from 0 to the maximum number of elements at the beginning of the element data, then specify as many data items as there are current elements by separating each data item by a delimiter.
(b) Binary format

In the binary format, you can specify arrayed data in FF, FV, or VV format.

FF format
Specify in the format shown in Table 5-29 Specification format of column data and parameters (binary format) as many data items as there are elements specified. If you omit the null value/function information file itself or the number of elements in the null value/function information file, the system assumes the maximum number of elements as the specified number of elements.
FV format
Specify at the beginning of the element data the current number of elements (as a two-byte binary number), and then specify in the format shown in Table 5-29 Specification format of column data and parameters (binary format) as many data items as there are elements specified. If you omit the null value/function information file itself or the number of elements in the null value/function information file, the system assumes the maximum number of elements as the specified number of elements.
VV format
Specify at the beginning of the element data the current number of elements (as a two-byte binary number), and then specify in the format shown in Table 5-29 Specification format of column data and parameters (binary format) as many data items as there are current elements.
(c) Fixed-size data format

In the fixed-size data format, you can specify arrayed data in FF or FV format.

If you omit the column structure information file or null value/function information file or the number of elements in these files, the system assumes the maximum number of elements as the specified number of elements.

If you specify data in binary format, and elements specified in VARCHAR, NVARCHAR, or MVARCHAR are less than the defined length, you need to pad the data with fill characters up to its defined length.

FF format
Specify in the format shown in Table 5-29 Specification format of column data and parameters (binary format) as many data items as there are elements specified.
FV format
Specify at the beginning of the element data the current number of elements (as a two-byte binary number), and then specify in the format shown in Table 5-29 Specification format of column data and parameters (binary format) as many data items as there are elements specified.
(d) Specification examples of arrayed data in fixed-size data format

The figure below shows specification examples of arrayed data in fixed-size data format.

These examples are based on columns of CHAR(3) ARRAY[5]. The 3-byte null is specified as the null value.

Figure 5-18 Specification examples of arrayed data in fixed-size data format

[Figure]

(e) Specification examples of arrayed data in variable-length data type

When specifying a variable-length data type (VARCHAR, MVCHAR, or NVARCHAR) in the fixed-size data or binary format, always specify the defined length of data in the FF or FV format.

Pad the valid data with any fill characters at the end. In the VV format, data is not padded with fill characters.

The figure below shows specification examples of arrayed data in variable-length data format. These examples are based on columns of VARCHAR(3) ARRAY[3]. The 3-byte character null is specified as the null value.

Figure 5-19 Specification examples of arrayed data in variable-length data format

[Figure]

(2) Handling of null values for columns and elements

A repetition column can have a null value as a column or a null value as an element. The database load utility can store either type of data in a database.

When data is loaded into a repetition column, element null values in the input data file can occur in the following pattern. The figure below shows input data for a repetition column and the available types of null value.

Figure 5-20 Input data for a repetition column and available types of null value

[Figure]

In the example shown in Figure 5-20, you can specify how to handle null values in the null value option (with the null value option in the array statement or with the nullset operand in a column structure information file and a null value/function information file) as follows: treat the entire column as a null value if all element data are null values; or, do not treat the consecutive null values at the trailing end of the element data as valid data, so that they are not stored in the database (not included in the number of elements).

The table below shows the handling of null values by the database load utility (pdload) when data is loaded into a repetition column.

Table 5-36 Handling of null values by the database load utility (pdload) during data loading into a repetition column

ItemHandling of null values
FF formatFV and VV format
Null value testing methodDAT formatTested as with a regular column (such as by reading consecutive delimiters); the elements passing the test are treated as null values.
Binary formatTested as with a regular column (by specifying null-equivalent values in a column structure information file); the elements passing the test are treated as null values.
Fixed-size data formatTested as with a regular column (by specifying null-equivalent values in a null value/function information file); the elements passing the test are treated as null values.
Storing null values in databaseSpecifying a null value as a columnIntermediate null values are stored in the database. For the treatment of consecutive trailing null values or when all specified elements are null values, see (a) Handling of null values in FF format.If the specified number of elements is 0, the system stores the column null values. Even if all element data is null values, the system assigns null values equal to the current number of elements without treating them as column null values.
Specifying a null value as an elementIntermediate null values are stored in the database. For the treatment of consecutive trailing null values or when all specified elements are null values, see (a) Handling of null values in FF format.If both intermediate null values and consecutive trailing null values are within the current number of elements specified in input data, the system stores the null values in the database.
(a) Handling of null values in FF format

With the FF format, the current number of elements is not specified in input data. Therefore, if there are consecutive trailing null values, you can use the following operands to treat the null values as invalid element data so that they are not stored in the database:

In a column structure information file or a null value/function information file, specifications are made for each column; in the array statement in a control information file, specifications are made for each table. If you specify all three options, the specifications in the column structure information file or the null value/function information file take precedence.

If the nullset operand is omitted, the system assumes nullset=c.

The table below shows null value option specification values and the methods for storing null values in a database.

Table 5-37 Null value option specification values and methods for storing null values in a database using the FF format

Null value typeNull value option specification value
c (default)e
Intermediate null valueThe system stores a null value in applicable elements.The system stores a null value in applicable elements.
Consecutive trailing null values (preceded by at least one valid element data item)The system treats applicable elements (consecutive trailing null values) as invalid data and does not store them in database.The system stores a null value in applicable elements (consecutive trailing null values).#
Consecutive trailing null values (all element data null)The system treats the column as null.The system stores a null value in all elements.#
Note
When using the FF format, the number of element data items in the input data file may be less than the number of elements specified in the table definition, in some cases. In this case, no data is specified beyond the current number of elements in the input data file. This means that the element data is not present, not that it has null values; therefore, the system does not store null values in the database, even when nullset=e is specified.
[Figure]
#: Specifying nullset=e causes null values equal to the number of element data items in the input file to be stored in the database. Therefore, you should specify nullset=c for database storage efficiency. You can use the nullset=e operand to control the number of null values.

(3) Relationship between the number of elements in input data and the number of elements specified in table definition

For the FF or FV format, you can specify a desired number of element data items in the input data up to the maximum number of elements specified in the table definition. You can use the following operand to specify the number of element data items (current number of elements) in the input data:

DAT format and fixed-size data format
element operand in the column structure information file
Binary format
element operand in the null value/function information file

If the number of elements in the input data is equal to the number of elements specified in the table definition, there is no need to specify the previous operand, in which case the system assumes the number of elements specified in the table definition.

The table below shows what is and what is not stored in the database when the number of elements specified for a repetition column in the input data differs from the number of elements specified in the table definition.

Table 5-38 What is stored and what is not stored in the database if the number of elements specified for a repetition column in input data differs from the number of elements specified in table definition

Number of repetition element data items in input data: Number of repetition elements specified in table definitionArrayed data format for repetition column
FF formatFV format (current number of elements specified in data)VV format (current number of elements specified in data)
Number of elements in input data > number of elements in table definitionErrorErrorError
Number of elements in input data = number of elements in table definitionData is stored normallyData stored normally (element data equal to the number of elements specified in input data is stored in database)Data is stored normally
Number of elements in input data > number of elements in table definitionData is stored normally (data equal to the number of elements specified in element operand is stored in database; any subsequent elements are not stored)Data is stored normally (element data equal to the number of elements specified in input data is stored in database)Data is stored normally

(4) Specification example

(a) DAT format

The figure below shows a specification example of DAT-format data.

Table definition:

CREATE TABLE T1 (C1 INTEGER, C2 CHAR(1) ARRAY[10])

Figure 5-21 Specification example of DAT-format data (repetition column)

[Figure]

(b) Binary format

The figure below shows a specification example of binary-format data.

Table definition:

CREATE TABLE T1 (C1 CHAR(4), C2 CHAR(10), C3 CHAR(5) ARRAY[10])

Figure 5-22 Specification example of binary-format data (repetition column)

[Figure]

(c) Fixed-size data format

The figure below shows a specification example of data in fixed-size data format.

Table definition:

CREATE TABLE T1 (C1 CHAR(4), C2 CHAR(10),
            C3 VARCHAR(5) ARRAY[10])

Figure 5-23 Specification example of data in fixed-size data format (repetition column)

[Figure]