Hitachi

Hitachi Advanced Database Command Reference


17.2.5 Specification patterns for the column structure information options

This subsection explains specification patterns for the column structure information options for each combination of the contents of input data files and the column definition for the table to be processed.

Note

For specification examples of column structure information options, see 17.3.2 Importing table data in CSV format (when the column structure information file is specified) or 17.3.3 Importing table data in fixed-length format.

Organization of this subsection

(1) When the file format of the input data files is CSV

This subsection explains the specification pattern for column structure information options when the file format of the input data files is CSV.

(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.

Figure 17‒6: 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

[Figure]

(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.

Figure 17‒7: 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

[Figure]

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.

Figure 17‒8: When the number of field data items in input data files is smaller than the number of column definitions for the table to be processed

[Figure]

#

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.

Figure 17‒9: 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

[Figure]

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.

Figure 17‒10: When a specific field data item in the input data file is stored in multiple columns of the table to be processed

[Figure]

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.

(2) When the file format of the input data files is fixed-length

This subsection explains the specification pattern for column structure information options when the file format of the input data files is fixed-length.

(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.

Figure 17‒11: 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

[Figure]

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.

Figure 17‒12: 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

[Figure]

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.

Figure 17‒13: 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

[Figure]

#

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.

Figure 17‒14: 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

[Figure]

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.

Figure 17‒15: When specific field data in the input data files is to be stored in multiple columns of the table to be processed

[Figure]

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