17.2.1 Explanation of the specification format and options

(1) Specification format

adbimport
    -u authorization-identifier
  [-p password]
  [-d]
  [-k {enclosing-character|none}]
  [-s {delimiter |tab}]
  [-g output-interval-for-data-import-processing-progress-messages]
  [-w {temporary-work-file-storage-directory-name
        |name-of-directory-path-file-specifying-temporary-work-file-storage-locations}]
  [-z import-option-file-name]
  [-e]
  [--force]
  [-b]
  [--status wait]
  [-m comment-to-be-set-in-chunk]
  [-f {csv|fix}]
  [-r column-structure-information-file-name]
  table-to-be-processed
  input-data-path-file-name

Important

You must specify the table to be processed and the input data path file name in this order as the last options. Make sure that the table to be processed is specified before the input data path file name.

To Page Top

(2) Relationship between data import patterns and options to be specified (types of chunks to which data is to be added)

The combination of options you need to specify depends on the data import pattern you want to perform.

When data is imported to a multi-chunk table, the chunk to which data is to be added varies depending on the specification of the -d and -b options. If the -b option is specified together with the --status wait option, chunks in wait status are created and then data is added.

Note

For details about chunks, see Managing data in data-import units (chunks) in the HADB Setup and Operation Guide.

The following table shows the relationships among data import patterns and options to be specified (types of chunks to which data is to be added).

For details about the options, see the descriptions of -d, -b, and --status wait in (3) Explanation of options.

Table 17‒1: Relationship between data import patterns and options to be specified (types of chunks to which data is to be added)

No.	Data import pattern		Options to specify			Chunk to which data is to be added when data is imported to a multi-chunk table #1
			-d option	-b option	--status wait option
1	Importing data to a newly defined table		N	N	N	Current chunk
2	Importing data to an existing table without using background import		N	N	N	Current chunk
3	Importing data to an existing table in the background#2	Including chunks for data that has been imported to the target of processing by data manipulation SQL statements (placing the chunks in normal status)	N	Y	N	Chunks in the normal status that were newly created during background import processing
4		Excluding chunks for data that has been imported from the target of processing by data manipulation SQL statements (placing the chunks in wait status)#3	N	Y	Y	Chunks in the wait status that were newly created during background import processing#4
5	Importing new data to an existing table after all existing data has been deleted		Y	N	N	Newly created chunks (because data is imported in creation mode, all the existing chunks are deleted).

Legend:

Y: The option is specified.

N: The option is omitted.

Note:

Specifying the -d option together with the -b option will result in an error.

Specifying the --status wait option without specifying the -b option will result in an error.

#1

If the -m option is specified, a comment can be set in the chunk to which data is to be added.

#2

For a single-chunk table, an error results (background import processing cannot be applied to a single-chunk table).

#3

The PURGE CHUNK and TRUNCATE TABLE statement can be executed.

#4

Chunks in wait status cannot be current chunks.

To Page Top

(3) Explanation of options

In option explanations, options indicated with [Multi-chunk table] can be specified when data is imported to a multi-chunk table. Those options cannot be specified when data is imported to a single-chunk table.

• -u authorization-identifier

~<character string>((1 to 100 bytes))

Specifies the authorization identifier of the HADB user who executes the adbimport command. You must have the following two privileges to execute the adbimport command:

• CONNECT privilege

• IMPORT TABLE privilege for the table to be processed

For this option, specify an authorization identifier in the range from 1 to 100 bytes. Note that the byte count (1 to 100 bytes) does not include the double quotation marks used to enclose the authorization identifier.

Important

If the character string used as the authorization identifier includes any lowercase letter or a backslash (\), make sure that you check the rules for specifying authorization identifiers. For the rules for specifying authorization identifiers, see 1.4.2 Rules for specifying authorization identifiers and passwords.

Important

The authorization identifier that is specified in the -u option must not be changed while the re-execute facility that starts from the processing to create B-tree indexes and text indexes is being used. Do not delete a user who is executing the adbimport command or revoke that user's IMPORT TABLE privilege while data is being imported.

If the executing user is deleted or the user's privileges are revoked by mistake, take either of the following corrective actions:

• As the same user, make the adbimport command executable and re-execute this command. For details about the corrective action to take, see the topic Managing HADB users or Managing access privileges in Unscheduled Operations in the HADB Setup and Operation Guide.

• As a different user who has REBUILD INDEX privileges for the table to be processed, execute the adbidxrebuild command with the --force option specified.

  For details about how to execute the adbidxrebuild command with the --force option specified and related notes, see (2) Executing the adbidxrebuild command with the --force option specified in 17.10.7 If an error occurs during re-execution of the adbimport command.

• -p password

~<character string>((1 to 255 bytes))

Specifies the password for the authorization identifier that is specified in the -u option.

Important

If the password includes a character that has a special meaning in the OS or shell, such as a double quotation mark (") or vertical bar (|), make sure that you check the rules for specifying passwords. For the rules for specifying passwords, see 1.4.2 Rules for specifying authorization identifiers and passwords.

If the adbimport command is executed with this option omitted, a message prompting the user to enter a password is displayed. In an environment in which a password cannot be entered from the standard input, such as when the command is executed in the background, make sure that you do not omit this option.

• -d

Specifies that data import processing is to be performed in creation mode. If this option is omitted, the command performs data import processing in addition mode.

In creation mode, the command deletes all existing data from the table and then imports the input data. In addition mode, the command imports the input data while retaining any data that already exists in the table.

When you import data to a newly defined table, there is no need to specify this option.

• -k {enclosing-character|none}

~<character string>((1 byte))<<">>

Consider specifying this option for input data files in CSV format. Do not specify this option for input data files in fixed-length format.

Specifies the character that is to be used as the enclosing character for demarcating field data in the input data files. If this option is omitted, the double quotation mark (") is assumed to be the enclosing character.

If no enclosing character is used for the field data in the input data files, specify none in this option.

The enclosing character must be a single-byte character. A character string of 2 or more bytes cannot be specified. None of the following characters are permitted to be the enclosing character:

• Space, tab, asterisk (*)

• The character that is used as the delimiter (the value of or default value for the -s option)

If numeric data, datetime data, or binary data is defined for a table that is to be imported, do not specify any character used in that numeric, datetime, or binary data as the enclosing character. If such a character is specified as the enclosing character, an input data conversion error might occur.

Important

• If you specify a single quotation mark (') as the enclosing character, enclose it in double quotation marks (").

  Example: If you specify a single quotation mark (') as the enclosing character:

  adbimport ... -k "'" ...

• If you specify as the enclosing character a special character used by the shell, such as the vertical bar (|), enclose it in either double quotation marks (") or single quotation marks (').

  Example: If you specify the vertical bar (|) as the enclosing character:

  adbimport ... -k "|" ...

• When you specify this option, either omit the -f option or specify csv in the -f option. If fix is specified in the -f option, the adbimport command will result in an error.

• If you specify an enclosing character in this option, and if a character that is the same as that enclosing character exists in the field data, specify the enclosing character twice in succession in the field data.

  If you specify none in this option, no enclosing character for input data is used in any data type. Therefore, there is no need to specify the character twice in succession in the field data.

• You cannot specify none in this option if the field data contains a character that is the same as the delimiter or if you want to treat line-ending code as data.

• The following table shows the relationship between input data and the data to be stored according to the value specified in this option.

  Table 17‒2: Relationship between input data and the data to be stored

  No.	Input data	Data to be stored
  		When the enclosing character (double quotation mark (")) is specified in the -k option	When "none" is specified for the -k option
  1	ABC,DEF	ABC DEF	ABC DEF
  2	"ABC""","DEF"	ABC" DEF	"ABC""" "DEF"
  3	"ABC,DEF"	ABC,DEF	"ABC DEF"
  4	"ABC,DEF	--	"ABC DEF
  5	"ABC","DEF\n"	ABC DEF\n	"ABC" "DEF "

  Legend:

  --: A logical error occurs.

• -s {delimiter|tab}

~<character string>((1 byte))<<,>>

Consider specifying this option for input data files in CSV format. Do not specify this option for input data files in fixed-length format.

Specifies the character that is to be used as the delimiter for delimiting field data in the input data files. To specify the tab character as the delimiter, specify tab. If this option is omitted, the comma (,) is assumed to be the delimiter.

Only tab or a single-byte character can be specified as the delimiter (except for tab, a character string consisting of two or more bytes cannot be specified). None of the following characters can be specified as the delimiter:

• Alphanumeric characters (upper-case letters A to Z, lower-case letters a to z, numeric characters 0 to 9), underscore (_), double quotation mark ("), asterisk (*)

• The character that is used as the enclosing character (the value of or default value for the -k option)

None of the following characters is suitable as the delimiter because the character codes representing these characters might also be used in the input data to represent special meanings:

• Signs (+, -), forward slash (/), colon (:), period (.), vertical bar (|), backslash (\), [, ], (, ), {, }, swung dash (~)

Important

• If you specify a single quotation mark (') as the delimiter, enclose it in double quotation marks (").

  Example: If you specify a single quotation mark (') as the delimiter:

  adbimport ... -s "'" ...

• If you specify as the delimiter a special character used by the shell, such as the vertical bar (|), enclose it in either double quotation marks (") or single quotation marks (').

  Example: If you specify the vertical bar (|) as the delimiter:

  adbimport ... -s "|" ...

• When you specify this option, either omit the -f option or specify csv in the -f option. If fix is specified in the -f option, the adbimport command will result in an error.

• -g output-interval-for-data-import-progress-messages

~<integer>((0 to 1,000,000,000))<<0>>(in 1,000 rows)

Specifies the interval at which the data import progress messages are to be output. For example, if you specify 2 in this option, a progress message (KFAA80205-I) is output each time 2,000 rows of data have been imported.

If this option is omitted or 0 is specified in this option, no progress message is output.

• -w {temporary-work-file-storage-directory-name|name-of-directory-path-file-specifying-temporary-work-file-storage-locations}

~<OS path name>((2 to 518 bytes))

Specifies a storage location for the temporary work files that are created during data import processing, expressed as either of the following:

1. Absolute path name of the directory that stores temporary work files

2. Absolute path name of a file (directory path file) that specifies directories for storing temporary work files

If frequent input/output operations are performed on the temporary work files stored under a single directory, performance might be adversely affected. We recommend that you store temporary work files in directories on multiple disks by using method 2 so that the workload of input/output operations will be distributed among disks. The following shows an example specification using method 2 (specifying a directory path file).

Example of directory path file

/mnt/diska/wwwww
/mnt/diskb/xxxxx
/mnt/diskc/yyyyy
/mnt/diskd/zzzzz

The following is a guideline for the number of directories to be specified:

Guideline-for-number-of-directories-to-be-specified = A × B

A: Number of data storage threads

Use the following formula to determine the value:

value-of-the-import-option adb_import_rthd_num - 1

B: Number of B-tree and text indexes defined for the table subject to import processing

If the number of B-tree and text indexes is not known, use the following SQL statement to determine the number of B-tree and text indexes defined for the table:

SELECT "N_INDEX"-"N_RANGE_INDEX"
    FROM "MASTER"."SQL_TABLES"
        WHERE "TABLE_SCHEMA"='authorization-identifier'
           AND "TABLE_NAME"='name-of-table-to-be-imported'

• A maximum of 255 directories can be specified in a directory path file.

• The directories specified in the directory path file are used as the storage locations of temporary work files in the order specified. When the last directory specified in the directory path file is used (in this example, /mnt/diskd/zzzzz), the first directory (in this example, /mnt/diska/wwwww) is then used again.

The following specification rules apply to this option (for both methods 1 and 2):

• Do not specify a forward slash (/) at the end of a path name.

For details about how to estimate the sizes of the temporary work files that will be created under the directories specified in the -w option, see the topic Estimating the size of the temporary work file for executing the adbimport command in the HADB Setup and Operation Guide. If this option is omitted, temporary work files are created under $DBDIR/ADBWORK.

Provide a dedicated directory to store the temporary work files. If a directory storing other files is used to store temporary work files, data in the existing files might be corrupted. For details about the files whose names must differ from the temporary work file storage directory name, see 17.11 Notes.

If the disk that stores temporary work files runs out of free space, specify another disk with a larger capacity as the storage destination. For details about the actions to take if the disk that stores temporary work files runs out of free space, see Steps to take in the event of a shortage of disk space for storing temporary work files during command execution in Command-related problems in Troubleshooting in the HADB Setup and Operation Guide.

• -z import-option-file-name

~<OS path name>((2 to 510 bytes))

Specifies the absolute path name of the import option file that contains the import options. If this option is omitted, all import options are set to their default values because no import option file is specified.

For details about the import options, see 17.2.2 Format of import options.

Make sure that the import option file name differs from all other file names. If the import option file has the same name as another file, data in the file might be lost. For details about the files whose names must differ from the import option file name, see 17.11 Notes.

• -e

Normally, you will omit this option.

Specifies that data import processing is to be canceled if a logical error is detected in the input data.

If you specify this option and a logical error is detected in the input data, the transaction is rolled back (no data is imported). In this case, the return code is 8.

If you omit this option and a logical error is detected in the input data, only normal data is imported. The data in which the logical error occurred is not imported. In this case, the return code is 4.

• --force

Specifies that the adbimport command is to be executed forcibly after all data has been deleted from the table. Consider using this option after the adbimport command was canceled and you want to delete all table data and re-execute data import processing.

When you re-execute the adbimport command without specifying this option, any data stored in the table during previous data import processing is retained and only the remaining data is imported during re-execution.

On the other hand, if you re-execute the adbimport command with this option specified, all data stored in the table is deleted and then data import processing is performed from the beginning.

Important

• When you specify this option, make sure that you also specify the -d option (creation mode). If the -d option is omitted, the adbimport command will result in an error.

• When you re-execute the adbimport command with this option and with the -d option specified, all data stored in the table is deleted. Therefore, do not specify this option if you want to release a table from non-updatable status. For details about how to release a table from non-updatable status, see Steps to take when a base table becomes non-updatable in Problems related to base tables in Troubleshooting in the HADB Setup and Operation Guide.

To execute the adbimport command with this option specified:

1. Execute the adbexport command to output to a file the data stored in the table.

   As needed, output data from each chunk separately.

2. Use the OS's rm command or a similar method to delete the temporary work files that were created the last time the adbimport command was executed.

   Delete the files under the directories listed below. If there are any files for which you do not have write or read privileges, you might not be able to execute the adbimport command.

   • If the -w option was specified during execution of the previous command that was canceled: The temporary work file storage directories specified in the -w option

   • If the -w option was omitted during execution of the previous command that was canceled: The $DBDIR/ADBWORK directory

3. Using the files that were output in step 1 as input data files, execute the adbimport command with the -d and --force options specified.

• -b [Multi-chunk table]

Specifies that data is to be added by background import. When this option is specified, the command adds the data to a new chunk.

• If only this option is specified, chunks in normal status are created and then data is added.

• If this option is specified together with the --status wait option, chunks in wait status are created and then data is added.

Specify this option for a multi-chunk table. An error results if this option is specified for a single-chunk table.

The first time you import data to a multi-chunk table, do not specify this option. If this option is specified at such a time, the data will be placed in a new chunk, not the one created during table definition, thereby wasting a chunk.

For details about the chunk to which data is added, see (2) Relationship between data import patterns and options to be specified (types of chunks to which data is to be added).

• --status wait [Multi-chunk table]

Specifies that chunks in wait status are to be created and then data is to be added by background import. Data added to chunks in wait status is excluded from processing by data manipulation SQL statements (note that PURGE CHUNK and TRUNCATE TABLE statements can be executed).

Specify this option for a multi-chunk table. An error results if this option is specified for a single-chunk table.

Important

When you specify this option, make sure that you also specify the -b option. If the -b option is omitted, the adbimport command will result in an error.

• -m comment-to-be-set-in-chunk [Multi-chunk table]

~<character string>((1 to 1,024 bytes))

Specifies a comment that is to be set in the chunk to which data is to be added.

Specify this option for a multi-chunk table. An error results if this option is specified for a single-chunk table.

A comment that has already been set in the chunk to which data is to be added will be overwritten.

For details about the chunk to which data is added, see (2) Relationship between data import patterns and options to be specified (types of chunks to which data is to be added).

The following rules apply to specifying this information:

• The specified comment is case sensitive.

• If the comment contains a character that has a special meaning in the OS or shell, such as a double quotation mark (") or vertical bar (|), specify the escape character (\) immediately before that character. Alternatively, enclose the entire comment in single quotation marks (').

  Example 1: -m abc\|def

  Example 2: -m 'abc|def'

  In both examples, abc|def is set as the comment.

• If a character string is enclosed in double quotation marks (") and then the entire string is enclosed in single quotation marks ('), both the character string and the double quotation marks are treated as a comment.

  Example: -m '"abcdef"'

  In this example, "abcdef" is set as a comment.

• The character encoding specified in the HADB server's ADBLANG environment variable is used for the character string set as a comment.

• -f {csv|fix}

Specifies the format of the input data files. When this option is omitted, csv is assumed.

csv: CSV format

fix: Fixed-length format

For details about the CSV format, see 17.5 Format of input data files (CSV format specifications).

For details about the fixed-length format, see 17.6 Format of input data files (fixed-length format specifications).

Important

When the input data files are in the fixed-length format (when you specify fix in this option), make sure that you specify the -r option. If the -r option is omitted, the adbimport command will result in an error.

• -r column-structure-information-file-name

~<OS path name>((2 to 510 bytes))

Specifies the absolute path of the column structure information file in which the column structure information options are specified

A column structure information file is used to specify the relationship between the table to which import processing is being applied and the input data. For details about the specification format of the column structure information options, see 17.2.3 Format of column structure information options.

Whether this option needs to be specified (whether a column structure information file is needed) depends on the file format of the input data files, as well as the sort order of field data and the number of field data items in the input data files. The following table shows the relationship between the input data files and the -r option.

Table 17‒3: Relationship between input data files and the -r option

No.	Input data file format	Field data in the input data files	-r option specification
1	CSV format	Satisfies the following two conditions: The field data sort order is the same as the column structure order in the table to which import processing is applied. The number of field data items is the same as the number of column definitions in the table to which import processing is applied.	O
2		The field data sort order is different from the column structure order in the table to which import processing is applied.	R
3		The number of field data items is different from the number of column definitions in the table to which import processing is applied.	R
4		The field data contains binary data in binary format.	R
5	Fixed-length format	Satisfies the following two conditions: The field data sort order is the same as the column structure order in the table to which import processing is applied. The number of field data items is the same as the number of column definitions in the table to which import processing is applied.	R
6		The field data sort order is different from the column structure order in the table to which import processing is applied.	R
7		The number of field data items is different from the number of column definitions in the table to which import processing is applied.	R
8		The field data contains binary data in binary format.	R

Legend:

R: You must specify the -r option. (A column structure information file is required for importing data.)

O: Specification of the -r option is optional. (Data can be imported without a column structure information file.)

Make sure that the column structure information file name is unique among all other file names. If the specified column structure information file name duplicates an existing file name, data in the column structure information file might be lost. For details about the files whose names must differ from the name of the column structure information file, see 17.11 Notes.

Important

When you omit specification of this option, you need to specify for the input data file a file that satisfies the following conditions:

• A file in CSV format

• A file in which the field data sort order is the same as the column structure order in the table to which import processing is applied

• A file in which the number of field data items is the same as the number of column definitions in the table to which import processing is applied

• table-to-be-processed

Specifies the table into which data is to be imported.

The following rules apply to specifying this option:

• Specify in the format schema-name.table-identifier.

  If the user owns the table, the schema name can be omitted. If another HADB user owns the table, the schema name must be specified.

• For details about the specification rules that apply when a schema name or table identifier includes any lowercase letter or backslash (\), see 1.4.3 Table name specification rules.

• The user can specify only a table for which the user has the IMPORT TABLE privilege.

• A viewed table cannot be specified.

• input-data-path-file-name

~<OS path name>((2 to 510 bytes))

Specifies the absolute path name of the input data path file. If a path name contains a space, the entire path name must be enclosed in single quotation marks (').

For the input data path file, specify the storage location of the input data files, expressed as an absolute path consisting of 2 to 510 bytes. The characters permitted in path names are the same as for the input data path file name.

If frequent input operations are performed on the input data files stored under a single directory, performance might be adversely affected. We recommend that you specify directories on multiple disks as input data file storage locations to distribute the workload of input operations among disks.

The following shows an example of an input data path file.

Example of input data path file

/mnt/diska/wwwww/imp1.csv
/mnt/diskb/xxxxx/imp2.csv
/mnt/diskc/yyyyy/imp3.csv
/mnt/diskd/zzzzz/imp4.csv

This example specifies the storage locations of four input data files.

Make sure that the input data path file name and the input data file storage locations specified in the file are unique from all other file names. If the input data path file or any of the input data file storage locations in the file has the same name as another file, data in the input data path file or input data files might become corrupted. For details about the files whose names must differ from the names of the input data path file and input data file storage locations, see 17.11 Notes.

To Page Top