UNLOAD

UNLOAD statement unloads the data from a table in the current database to an output file.

Output file	A character expression that returns the name of the output file and its relative path if necessary
Delimiter	A quoted string (or a CHAR, STRING, or VARCHAR variable) containing a delimiter symbol
Variable	A CHAR, STRING or VARCHAR variable containing an SELECT clause
SELECT clause	A CHAR, STRING, or VARCHAR variable containing an INSERT clause

The UNLOAD statement should include a SELECT statement, which specifies the rows in a database that should be copied to a file. The values in the database selected and copied by the UNLOAD statement are not altered or deleted from the table. To execute the UNLOAD statement, the user must have the SELECT privileges on every column from which the values are selected. (The privileges are set by means of the SQL statement GRANT).

 

The DATABASE or CONNECT statement must precede the UNLOAD statement. You cannot prepare the UNLOAD statement using the PREPARE statement.

Output to a file

Output of the rows selected from a table is performed into an output file, which is specified in a form of a character string enclosed in quotation marks or in the form of a character variable returning such string, the path name can be also included. The output file can contain only ASCII characters (in default U.S. English locale). In other locales, the output file can consist of the characters that belong to the code set of the locale.

 

Each row retrieved from the database is sent to the output file in a form of an output record, each of the output records is terminated by the new line character (ASCII 10). Each value within an output record is stored as string of ASCII characters, the data types of the values correspond to the data types of the columns from which the values have been retrieved.

column data type	retrieved format
CHAR VARCHAR STRING TEXT	All the trailing blanks are truncated from the values of CHAR and TEXT data types, but they are not deleted from the VARCHAR or STRING values. Each literal delimiter sign, a literal backlash (\) or a newline symbol is preceded by one more backlash symbol in the values of the VARCHAR data type. The backlash symbol is also used as the last character in the values of the TEXT data type.
DECIMAL FLOAT INTEGER MONEY SMALLFLOAT SMALLINT	The leading blanks are discarded, the values are written as literals. MONEY values are written without currency symbols. 0 stands for zero values of INT and SMALLINT data types, 0.00 is used for FLOAT, SMALLFLOAT, DECIMAL and MONEY zero values. The values retrieved from columns of SERIAL data type are represented by literal integers.
DATE	The values are written in the mm/dd/yyyy format, if other format is not specified in the DBDATE environment variable settings.
DATETIME INTERVAL	The DATETIME values are written in the format. yyyy-mm-dd hh:mm:ss.fff with all the time units present or as a continuous subset of the time units. The INTERVAL values are written in the format yyyy-mm or dd hh:mm:ss.fff with all the time units present or as a continuous subset of the time units. Both values are written without the DATETIME and INTERVAL keywords. The time units outside the continuous subset of the time units declared by means of the qualifiers are omitted.
BYTE	The values are written in ASCII hexadecimal format. No whitespaces or newline characters are added. The ASCII hexadecimal form of a BYTE value might be very long and thus difficult to edit or print.

The NULL values of any data type are represented by double delimiter symbol without any blank spaces between the delimiters:

12|John|Smith||USA

 

This UNLOAD statement copies values from all the columns of a table which correspond to the condition of a WHERE clause into the unld_file:

 

The content of the unld_file will look like the following text:

1034|TransCorp|05/10/2009|1500|2.984

1034|PlatoCorp|06/23/2009|200|484

 

The formats of the values that are copied to a file are determined by the settings of the DBFORMAT, DBMONEY, or DBDATE environment variables.

DELIMITER clause

Default delimiter which separates one value from another in an output record is a pipe symbol (|). This default setting can be changed by means of the DELIMITER clause of the UNLOAD statement. The delimiter symbol must terminate an input record only if the last value of this record is NULL.

 

Below is an example of the UNLOAD statement with the DELIMITER clause which sets the exclamation mark (!) symbol as the delimiter. The UNLOAD statement copies the data contained in all the columns of the id_num table into a file. This way the table can be populated by loading this file:

 

If the DELIMITER clause is omitted, the delimiter set by the DBDELIMITER environment variable, if it is set, otherwise the default delimiter symbol pipe (|) is used.

These symbols cannot serve as delimiters:

• Hexadecimal numbers (0 through 9, a through f, or A through F)
• NEWLINE or CONTROL-J
• Backlash symbol (\)

Host variables

You cannot substitute host variables with question mark symbols (?) in the SELECT statement, it will lead to the binding problems.

Backlash symbol

Backlash symbol (\) is used as an escape character to indicate that the next character used in the output file should be interpreted as a literal. The backlashes are automatically inserted by the UNLOAD statement to prevent some characters from being interpreted as special characters and not literals.

 

It is inserted in these situations:

• If a backlash character is placed in a value of the CHAR, VARCHAR , STRING or TEXT data types
• If a delimiter character is placed in a value of the CHAR, VARCHAR, STRING or TEXT data types (the delimiter character is either the default delimiter - pipe symbol, or the delimiter specified in the DELIMITER clause)
• If a newline character is placed in a value of the VARCHAR data type
• If a newline character is inserted as the last character of a TEXT value

If the LOAD statement is used to insert the values from the file previously created by the UNLOAD statement into a database, all the automatically inserted backlash symbols are deleted.