17.2.5 Specification patterns for the column structure information options

(a) When the sort order of field data items in input data files matches the order of the column definitions for the table to be processed

There is no need to specify a column structure information file if both of the following conditions are satisfied:

• The number of field data items in input data files matches the number of column definitions for the table to be processed.

• The sort order of field data items in input data files matches the order in which the columns appear in the table to be processed.

(b) When the sort order of field data items in input data files does not match the order of the column definitions for the table to be processed

The following figure shows the information to specify in the column structure information options when the sort order of field data items in input data files does not match the order of the column definitions for the table to be processed.

Information to be specified in the column structure information options

adbcolumninfo  -n C1  -r 2
adbcolumninfo  -n C2  -r 3
adbcolumninfo  -n C3  -r 1

(c) When the number of field data items in input data files is less than the number of column definitions for the table to be processed

The following figure shows the information to specify in the column structure information options when the number of field data items in input data files is less than the number of column definitions for the table to be processed.

#

For details about the actual value to be stored in the target column when the input data is an empty string, see 17.8 Value that is stored for empty-string input data.

Information to be specified in the column structure information options

adbcolumninfo  -n C1  -r 2
adbcolumninfo  -n C2  -r empty_string
adbcolumninfo  -n C3  -r 1

(d) When the number of field data items in input data files is greater than the number of column definitions for the table to be processed

The following figure shows the information to specify in the column structure information options when the number of field data items in input data files is greater than the number of column definitions for the table to be processed.

Information to specify in the column structure information options

adbcolumninfo  -n C1  -r 2
adbcolumninfo  -n C2  -r 4
adbcolumninfo  -n C3  -r 1

Note:

Field 3 data is not subject to data import.

(e) When a specific field data item in an input data file is stored in multiple columns of the table to be processed

The following figure shows the information to specify in the column structure information options when a specific field data item in the input data file is stored in multiple columns of the table to be processed.

Information to specify in the column structure information options (Example 1)

adbcolumninfo  -n C1  -r 1
adbcolumninfo  -n C2  -r 1
adbcolumninfo  -n C3  -r 2

Information to specify in the column structure information options (Example 2)

adbcolumninfo  -n C1
adbcolumninfo  -n C2  -r 1
adbcolumninfo  -n C3  -r 2

Note:

Field 1 data is stored in column C1 both when 1 is specified in the -r option for column C1, and when the -r option is omitted.

(a) When the sort order of field data in the input data files matches the order of the column definitions for the table to be processed

The following figure shows the information to be specified in the column structure information options when the sort order of field data in the input data files matches the order of the column definitions for the table to be processed.

Information to be specified in the column structure information options

set adb_import_input_record_size = sum-of-the-lengths-in-bytes-of-field-1-data-through-field-3-data-and-of-line-break-codes
 
adbcolumninfo  -n C1  -p start-position-of-field-1-data,
                         length-of-field-1-data
adbcolumninfo  -n C2  -p start-position-of-field-2-data,
                         length-of-field-2-data
adbcolumninfo  -n C3  -p start-position-of-field-3-data,
                         length-of-field-3-data

(b) When the sort order of field data in the input data files does not match the order of the column definitions for the table to be processed

The following figure shows the information to be specified in the column structure information options when the sort order of field data in the input data files does not match the order of the column definitions for the table to be processed.

Information to be specified in the column structure information options

set adb_import_input_record_size = sum-of-the-lengths-in-bytes-of-field-1-data-through-field-3-data-and-of-line-break-codes
 
adbcolumninfo  -n C1  -p start-position-of-field-2-data,
                         length-of-field-2-data
adbcolumninfo  -n C2  -p start-position-of-field-3-data,
                         length-of-field-3-data
adbcolumninfo  -n C3  -p start-position-of-field-1-data,
                         length-of-field-1-data

(c) When the number of field data items in the input data files is fewer than the number of column definitions for the table to be processed

The following figure shows the information to be specified in the column structure information options when the number of field data items in the input data files is fewer than the number of column definitions for the table to be processed.

#

For details about the actual value to be stored in the target column when the input data is an empty string, see 17.8 Value that is stored for empty-string input data.

Information to be specified in the column structure information options

set adb_import_input_record_size = sum-of-the-lengths-of-field-1-data-and-field-2-data
 
adbcolumninfo  -n C1  -p start-position-of-field-2-data,
                         length-of-field-2-data
adbcolumninfo  -n C3  -p start-position-of-field-1-data,
                         length-of-field-1-data

Note:

The adbcolumninfo operand is not specified for C2.

(d) When the number of field data items in the input data files is greater than the number of column definitions for the table to be processed

The following figure shows the information to be specified in the column structure information options when the number of field data items in the input data files is greater than the number of column definitions for the table to be processed.

Information to be specified in the column structure information options

set adb_import_input_record_size = sum-of-the-lengths-in-bytes-of-field-1-data-through-field-4-data-and-of-line-break-codes
 
adbcolumninfo  -n C1  -p start-position-of-field-2-data,
                         length-of-field-2-data
adbcolumninfo  -n C2  -p start-position-of-field-4-data,
                         length-of-field-4-data
adbcolumninfo  -n C3  -p start-position-of-field-1-data,
                         length-of-field-1-data

Note:

Field 3 data is not subject to data import.

(e) When specific field data in the input data files is to be stored in multiple columns of the table to be processed

The following figure shows the information to be specified in the column structure information options when specific field data in the input data files is to be stored in multiple columns of the table to be processed.

Information to be specified in the column structure information options

set adb_import_input_record_size = sum-of-the-lengths-of-field-1-data-and-field-2-data
 
adbcolumninfo  -n C1  -p start-position-of-field-1-data,
                         length-of-field-1-data
adbcolumninfo  -n C2  -p start-position-of-field-1-data,
                         length-of-half-of-field-1-data
adbcolumninfo  -n C3  -p start-position-of-field-2-data,
                         length-of-field-2-data