5.4.12 array statement (specification of an array data format for a table containing repetition columns)

For a table containing repetition columns, you can use the array statement to specify the handling of the array data format and null values specified in the input data file.

Criteria
Specify the array statement to execute data loading on a table containing repetition columns.
When this statement is omitted, the system assumes the FF format for array data in the DAT or fixed-size data format file or the VV format for array data in the binary format file. For the null value option, the system assumes c.
Rules
  1. Specify this control statement for all repetition columns contained in the table. If you need to modify a specification on a column-by-column basis, specify this statement in a column structure information file or in a null value/function information file.
  2. The following terminology is used in the explanation of data loading into a table containing a repetition column:
    Maximum number of elements:
    This is the number of elements specified for a given column during table definition.

    [Figure]

    Specified number of elements:
    This is the number of elements specified in the input data file from the maximum number of elements. You can specify this value in a column structure information file or in a null value/function information file.

    [Figure]

    Current number of elements:
    This is the effective number of elements specified in the input data file.

    [Figure]

Organization of this subsection
(1) Format
(2) Explanation

(1) Format

array [elmtype=array-data-format] [nullset=null-value-option]

(2) Explanation

(a) elmtype=array-data-format

Specify either ff, fv, or vv.

When the elmtype operand is omitted, the array data takes the FF format for a DAT or fixed-size data format file; it takes the VV format for a binary-format file.

For details about the array data formats, see section 5.5.4 Arrayed data format for tables containing repetition columns.

ff
This refers to a fixed field fixed element (FF) format.
For this format, always specify a fixed number of element data items without specifying a current number of elements (the effective number of elements specified in the input data file) in the input data.
Criterion
The FF format is useful for storing data in a repetition column without specifying the current number of elements in the input data.
Rules
  1. The number of element data items specified in the input data must be no greater than the number of elements specified in the table definition.
  2. You can specify the FF format for the following input data formats:
    [Figure]DAT format
    [Figure]Binary format
    [Figure]Fixed-size data format
  3. The utility determines whether or not the specified data is to be stored in the database on the basis of whether the following conditions are met:
    [Figure]Whether specified data represents null values
    [Figure]How null values are to be handled
fv
This refers to a fixed field variable element (FV) format.
For this format, specify the current number of elements along with the element data in the input data. This input data must contain as many element data items as there are elements specified in the table definition, column structure information file, or null value/function information file.
Criterion
Because the FV format allows the processing of data in fixed length, it facilitates the creation and modification of data by UAPs, but at the expense of an increase in file size.
Rules
  1. You can specify the FV format for the following input data formats:
    [Figure]Binary format
    [Figure]Fixed-size data format
  2. The FV format specifies input data in terms of a fixed-length data type. Therefore, for a variable-length data type (VARCHAR, MVARCHAR, or NVARCHAR), the valid data length must be specified at the beginning of each element data item. Following the valid data, the remaining portion of data must be padded with fill characters up to the length defined in the table, so that the input data always has the length defined for the table.
vv
This refers to a variable field variable element (VV) format.
For this format, specify the current number of elements along with the element data in the input data. The number of specified element data items must be equal to the current number of elements.
Criteria
Because the VV format requires the specification of a minimum length of data, the file size can be smaller than in the FV format. However, because it supplies data only in the current number of elements, this format makes the task of data modification by a UAP more complicated when compared with the FF or FV formats.
Rules
  1. For a variable-length data type, specify data equal in length to the valid data length specified at the beginning of each element data item.
  2. You can specify the VV format for the following input data formats:
    [Figure]DAT format
    [Figure]Binary format

You must specify the array data format described previously in one of the following files according to specification units:

Specification unitsFile to be specifiedDescription
TablesControl information fileSpecify with the elmtype operand of the array statement.
ColumnsDAT or fixed-size formatColumn structure information fileSpecify with the elmtype operand.
Binary formatNull value/function information fileSpecify with the elmtype operand.
(b) nullset=null-value-option
[Figure]<<c>>

With the FF array data format, this operand specifies how null value storage is to be handled if null values are specified in an element through the end of the specified elements.

When specified with a format other than the FF format, this option is ignored.

Specify one of the following values for the null value option:

c: Nothing is to be stored in a specified element.

e: Null values are to be stored in a specified element.