14.2.1 Explanation of the specification format and options

Organization of this subsection

(1) Specification format
(2) Relationships among options
(3) Explanation of options

(1) Specification format

adbexport
  -u authorization-identifier
  [-p password]
  [-k {enclosing-character|none}]
  [-s {delimiter|tab}]
  [-g output-interval-for-data-export-progress-message]
  [-z export-option-file-name]
  {-q SQL-statement-file-name|-n target-table[ -c chunk-ID]}
  [--compress GZIP]
  [--with-column-name]
  output-data-path-file-name

To Page Top

(2) Relationships among options

The options to be specified depend on what type of information you want to export with the adbexport command. The table below shows the relationships among the option specifications for each data export pattern. For details about the options, see the descriptions of the -q, -n, and -c options in (3) Explanation of options.

Table 14‒1: Option specifications for the data export patterns
No.	Data export pattern	-q option	-n option	-c option
1	Exporting all data from a table^#1	N	Y	N
2	Exporting only the data that was imported during a specific period of time (data in a specified chunk)^#2	N	Y	Y
3	Exporting the retrieval results from a `SELECT` statement^#1	Y	N	N

Legend:

Y: The option is specified.

N: The option is omitted.

Note:

The adbexport command results in an error when any of the following combinations of options is specified:

• The -q and -n options are specified together

• The -q and -c options are specified together

• Only the -c option is specified

#1

Data included in a wait status chunk cannot be exported.

#2

This data export pattern is applicable only to multi-chunk tables.

To Page Top

(3) Explanation of options

• -u authorization-identifier

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

Specifies the authorization identifier of the HADB user who executes the adbexport command.

In this option, specify the authorization identifier of an HADB user who has the following privileges:

When exporting a dictionary table or system table

Specify the authorization identifier of an HADB user who has the CONNECT privilege.
When exporting a base table or viewed table that was defined by the HADB user

Specify the authorization identifier of an HADB user who has the CONNECT privilege and the EXPORT TABLE privilege for the table to be exported.

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.

• -p password

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

Specifies the password for the authorization identifier that was 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 adbexport 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.

• -k {enclosing-character|none}

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

Specifies an enclosing character to be used to enclose field data in the output data file. If this option is omitted, the double quotation mark (") is assumed to be the enclosing character. If none is specified for this option, data without enclosing characters is output.

A single-byte character must be specified as the enclosing character. A character string consisting of two or more bytes cannot be specified. None of the following characters can be specified as 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 or datetime data is defined for a table that is to be exported, do not specify any character that is used in that numeric or datetime data as the enclosing character. If such a character is specified as the enclosing character, a conversion error might occur when data that was exported is imported.

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:

adbexport ... -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:

adbexport ... -k "|" ...
If character string data contains the enclosing character, two consecutive instances of the enclosing character are output.

Important

If none is specified for this option, data of all data types is output without enclosing characters to the output data file. The output data file containing data without enclosing characters might not be able to be used as an input data file of the adbimport command. The following shows examples.

Example 1: If data A,B,C is stored in a CHARACTER type column:: The output data is A,B,C. If you import this data with a comma ( , ) specified as the delimiter, the imported data is handled as separate data A, B, and C. In other words, the data is not handled as a single data unit A,B,C. Therefore, the data might not be stored in the table as expected or an import error might occur.

Example 2: If zero-byte length data is stored in a VARCHAR type column:: The output data is a zero-byte length character string. If this data is imported without using enclosing characters, the data is handled as a null value. Therefore, the data is handled as different data from the original one.

Example 3: If data AB\nC including the line break code (\n) is stored in a CHARACTER type column:: The output data is AB\nC. If this data is imported, it is handled as separate data AB and C. In other words, the data is not handled as a single data unit AB\nC. Therefore, the data might not be stored in the table as expected or an import error might occur.

• -s {delimiter|tab}

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

Specifies the delimiter that is to be used to delimit field data in the output 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 the tab character or a single-byte character can be specified as a delimiter. Except for the tab character, 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 (uppercase letters A to Z, lowercase 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 for the delimiter because they might also be used as character codes in table data:

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:

adbexport ... -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:

adbexport ... -s "|" ...

• -g output-interval-for-data-export-progress-message

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

Specifies the interval (in units of 1,000 rows) at which the data export processing progress message is to be output. For example, if 2 is specified in this option, the KFAA80205-I progress message is output each time 2,000 rows of data are exported.

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

• -z export-option-file-name

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

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

For details about the export options, see 14.2.2 Format of export options.

Make sure that the export option file name is unique from all other file names. If the export option file has the same name as another file, data in the file might become corrupted. For details about the files whose names must differ from the export option file name, see 14.8 Notes.

• -q SQL-statement-file-name

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

Specifies the absolute path name of the SQL statement file. When this option is specified, the -c option cannot be specified.

An SQL statement file is used to specify a SELECT statement for data retrieval when you intend to export that statement's retrieval results.

The following shows an example of an SQL statement file.

Example of SQL statement file

/*COMMENT*/ SELECT * FROM "TABLE01"
WHERE "C1" < 3000

The specification rules for the SQL statement file are as follows:

The maximum size of the SQL statement that can be specified in an SQL statement file is 16,000,000 bytes.
Only one SQL statement can be specified in an SQL statement file.
No dynamic parameters can be specified in an SQL statement file.
To create an SQL statement file, you must use the character encoding set in the ADBLANG environment variable for the machine on which the HADB server is installed.
All code characters in the range from X'00' to X'1F' are converted to single-byte spaces.
The SQL statement can contain line break codes. Use X'0A' (LF) or X'0D0A' (CRLF) as the line break code. Each line break code in the SQL statement is converted to a single-byte space, and multiple consecutive line break codes are concatenated for interpretation.
All characters specified between /* and the first */ that follows /* in an SQL statement file are treated as a comment. For details about specifying comments, see Comments in Rules for separators in SQL writing conventions in SQL Basics in the manual HADB SQL Reference.
Only one semicolon (;) can be specified at the end of the SQL statement. The specified semicolon is converted to a single-byte space for interpretation. A character string that contains single-byte spaces, tabs, or line breaks can only be specified after the specified semicolon.

Important: If a work table might be created during retrieval processing (such as when the ORDER BY clause, GROUP BY clause, or DISTINCT is specified), it might take a while after the adbexport command is executed before a progress message or an output count message is output.

The SQL statement specified in an SQL statement file can retrieve the following tables:

Base tables or viewed tables for which the EXPORT TABLE privilege has been granted

If you are retrieving a base table or a viewed table that you own, you can omit the schema name. If you are retrieving a base table or a viewed table owned by another HADB user, you must specify the schema name.
Dictionary tables and system tables

If you will be retrieving a dictionary table or a system table, specify "MASTER" as the schema name.

Note that the SQL statement that is actually executed is output to the message log file.

• -n table-to-be-processed

Specifies the table from which data is to be exported in the format schema-name.table-identifier. 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.

If you want to export only a chunk of data from a multi-chunk table, specify the -c option together with this option.

You can specify the following tables in this option:

Base tables or viewed tables for which the EXPORT TABLE privilege has been granted

If you are retrieving a base table or a viewed table that you own, you can omit the schema name. If you are retrieving a base table or a viewed table owned by another HADB user, you must specify the schema name.
A dictionary table or system table

If you will be retrieving a dictionary table or a system table, specify "MASTER" as the schema name.

• -c chunk-ID

~<integer>((1 to 9,223,372,036,854,775,807))

Specifies a chunk ID to export only a chunk of data from a multi-chunk table. If you specify this option, you can output data contained in the chunk of the specified ID even if that chunk is in wait status.

When you specify this option, you must specify a multi-chunk table in the -n option. When this option is specified, the -q option cannot be specified.

If the specified chunk ID does not exist in the table to be processed, the output data count will be zero.

The following explains how to determine the desired chunk ID:

Search system tables for STATUS_CHUNKS

Search system tables for STATUS_CHUNKS, and check the chunk IDs. For details, see Checking the information about all chunks in a table based on a table name in Searching system tables in System Tables in the HADB Setup and Operation Guide.

Execute the adbdbstatus command

Execute the following command to output the usage information for DB areas, tables, and indexes, and then check the chunk IDs (Chunk_ID) (the results are output in CSV format). For details about the items that are output as usage information for DB areas, tables, and indexes, see 13.7.2 List of items that are output as usage information for DB areas, tables, and indexes.

adbdbstatus -d used -c table -n base-table-name

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

• --compress GZIP

Specify this option if you want to compress the output data file and then export it as a GZIP-format file.

• --with-column-name

Specify this option if you want to output the column name in the first line in each output data file.

The column name to be output is determined by the option specified with this option. The following table describes the rules for output column names.

Table 14‒2: Column name that is output
No.	Option specified with this option	Column name that is output
1	-n option (to output data of the table to be processed)	Column name of the table to be processed
2	-q option (to output the result of searching for the SQL statement in the `SQL-statement-file`)	Table column names derived as a result of a query expression in an SQL statement specified in the `SQL-statement-file` (retrieval item column names) Example: If the following SQL statement is specified in the `SQL-statement-file`, column names `C1`, `MAX_C2`, and `MIN_C2` are output. SELECT "C1",MAX("C2") AS "MAX_C2",MIN("C2") AS "MIN_C2" FROM "T1" GROUP BY "C1" For details about the naming rules for columns in derived tables, see Rules in Specification format and rules for the SELECT statement in the manual HADB SQL Reference.

Column names are output in the same format as columns of VARCHAR type data. For details about the output format of VARCHAR type data, see 14.5.3 Output format for field data.

If multiple output data files are specified, the column name is output in the first line in every output data file. However, the column name is not output in files for which no data is output.

Important: Do not specify this option if you want to use the output data file as an input data file of the adbimport command. If a file that was output with this option specified is used as an input data file of the adbimport command, the column name is also stored as table data.

• output-data-path-file-name

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

Specifies the absolute path name of the output data path file. If the path name contains a space, enclose the entire path name in single quotation marks (').

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

The HADB server does not add an extension to output data files. The output data file is created as specified for the absolute path of the output data file. Therefore, when you specify the --compress GZIP option, we recommend that you specify the file extension .gz at the end of the absolute path of the output data file. Specifying the file extension .gz at the end of the path makes it easier to identify the exported file as a GZIP-format file.

To improve the performance of data export processing, we recommend that you specify multiple output data file storage locations. However, if it is important to store the exported data exactly in the order of the retrieval results, specify only one output data file storage location.

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

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

▪ Example of output data path file (if the --compress GZIP option is not specified)

/mnt/diska/wwwww/exp1.csv
/mnt/diskb/xxxxx/exp2.csv
/mnt/diskc/yyyyy/exp3.csv
/mnt/diskd/zzzzz/exp4.csv

This example specifies four output data file storage locations to export data to four output data files.

▪ Example of output data path file (if the --compress GZIP option is specified)

/mnt/diska/wwwww/exp1.csv.gz
/mnt/diskb/xxxxx/exp2.csv.gz
/mnt/diskc/yyyyy/exp3.csv.gz
/mnt/diskd/zzzzz/exp4.csv.gz

This example specifies four storage locations for output data files to export data to four output data files (files compressed in GZIP format).

If the --compress GZIP option is specified, we recommend that you specify the file extension .gz at the end of the absolute path of the output data file as shown in the preceding example. Specifying the file extension .gz at the end of the path makes it easier to identify the exported file as a GZIP-format file.

Important

When the adbexport command is executed, empty files are created at all output data file storage locations specified by the output data path file. Data is then exported into each file. Note that even if there is no data to be exported, a file with a size of zero bytes will be created.

If the --compress GZIP is specified and there is no data to be exported, a file (GZIP format) with a size of zero bytes will be created.
When multiple output data files are specified, the number of data items output for each file varies. Consequently, a file containing zero data items might be created.
If the output data file storage locations specified by the output data path file contain a file with the same name as an output data file, the adbexport command will terminate in an error. If this happens, move or delete the existing file, and then re-execute the adbexport command.

Make sure that no file names used by any programs other than the adbexport command are the same as the output data path file name or any of the output data file storage locations specified by the output data path file. If there is any duplication of names, data in the output data path file and output data files might be corrupted by other programs. For details about the files whose names must differ from the names of the output data path file and output data file storage locations, see 14.8 Notes.

To Page Top