UNLOAD

Copies data from the database tables into a file.

Syntax

UNLOAD TO filename [ DELIMITER delimiter]
{
  select-statement
|
  select-string
}
  1. filename is a string expression containing the name of the file the data is written to.
  2. delimiter is the character used as the value delimiter. Default is | pipe or DBDELIMITER environment variable when set.
  3. select-statement is static SELECT statement.
  4. select-string is string expression containing the SELECT statement.

Usage

The UNLOAD instruction serializes into a file the SQL data produced by a SELECT statement.

A data file used by LOAD or UNLOAD instructions looks like this (when the delimiter is the pipe), and the record has an INTEGER, VARCHAR and DATE fields:
102|Mike|12/24/2020|
192|Tom|04/29/2019|
The UNLOAD command cannot be used in a PREPARE statement. However, the UNLOAD command accepts a string literal in place of a static SELECT statement: 
UNLOAD TO file-name
    select-string

The filename after the TO keyword identifies an output file in which to store the rows retrieved from the database by the SELECT statement. In the default (U.S. English) locale, this file contains only ASCII characters. (In other locales, output from UNLOAD can contain characters from the codeset of the locale.)

The UNLOAD statement must include a SELECT statement (directly, or in a variable) to specify what rows to copy into filename. UNLOAD does not delete the copied data.

A single character delimiter instruct UNLOAD to write data in the default format. When using "CSV" as delimiter specification, the UNLOAD instruction will write the data in CSV format. If the DELIMITER clause is not specified, the delimiter is defined by the DBDELIMITER environment variable. If the DBDELIMITER environment variable is not set, the default is a | pipe. The field delimiter can be a blank character. It cannot be backslash or any hexadecimal digit (0-9, A-F, a-f). If the delimiter specified in the UNLOAD command is NULL, the runtime system will use the default delimiter or DBDELIMITER if the variable is defined.

When using a select-string, do not attempt to substitute question marks (?) in place of host variables to make the SELECT statement dynamic, because this usage has binding problems.

At this time, data type description of the output file fields is implicit; in order to create the fetch buffers to hold the column values, the UNLOAD instruction uses the current database connection to get the column data types of the generated result set. Those data types depend on the type of database server. For example, IBM® Informix® INTEGER columns are integers of 4 bytes, while the Oracle INTEGER data type is actually a NUMBER(10,0) type. Therefore, be aware when using this instruction that if your application connects to different kinds of database servers, you may get data conversion errors.

Default UNLOAD format

A set of values in the output representing a row from the database is called an output record. A NEWLINE character (ASCII 10) terminates each output record.

The UNLOAD statement represents each value in the output file as a character string based on the current locale, depending on the data type of the database column:

Table 1. Default UNLOAD format
Data type Output Format
CHAR, VARCHAR, TEXT Trailing blanks are dropped from CHAR and TEXT (but not from VARCHAR) values. A backslash ( \ ) is inserted before any literal backslash or delimiter character and before a NEWLINE character in a character value.
DECIMAL, FLOAT, INTEGER, MONEY, SMALLFLOAT, SMALLINT Values are written as literals with no leading blanks. MONEY values are represented with no leading currency symbol. Zero values are represented as 0 for INTEGER or SMALLINT columns, and as 0.00 for FLOAT, SMALLFLOAT, DECIMAL, and MONEY columns.
DATE Values are written in the format month/day/year unless some other format is specified by the DBDATE environment variable.
DATETIME

DATETIME values are formatted year-month-day hour:minute:second.fraction or a contiguous subset, without DATETIME keyword or qualifiers. Time units outside the declared precision of the database column are omitted.
INTERVAL INTERVAL values are formatted

year-month

or

day hour:minute:second.fraction

or a contiguous subset, without INTERVAL keyword or qualifiers. Time units outside the declared precision of the database column are omitted.
BYTE BYTE Values are written in ASCII hexadecimal form, without any added blank or NEWLINE characters. The logical record length of an output file that contains BYTE values can be very long, and thus might be very difficult to print or to edit.

NULL values of any data type are represented by consecutive delimiters in the output file, without any characters between the delimiter symbols.

The backslash symbol (\) serves as an escape character in the output file to indicate that the next character in a data value is a literal. The UNLOADstatement automatically inserts a preceding backslash to prevent literal characters from being interpreted as special characters in the following contexts:

  • The backslash character appears anywhere in the value.
  • The delimiter character appears anywhere in the value.
  • The NEWLINE character appears anywhere in a value.

CSV UNLOAD format

The CSV (comma separated values) format is similar to the standard format when using a simple comma delimiter, with the following differences:

  • A comma character generates a quoted output value, and the comma is written as is (not escaped).
  • A " double-quote character generate quoted output value and the quote in the value is doubled.
  • NEWLINE characters generate a quoted output value, and the NEWLINE is written as is (not escaped).
  • Backslash \ characters are written as is in the output value (i.e. not escaped).
  • Leading and trailing blanks are not truncated in the output value.
  • No ending delimiter is written at the end of the output record.

Example

MAIN
  DEFINE var INTEGER
  DATABASE stores
  LET var = 123
  UNLOAD TO "items.unl"
    SELECT * FROM items WHERE item_num > var 
END MAIN