UNLOAD

>>-UNLOAD TO--'filename'--+------------------------+------------>
                          '-DELIMITER--'delimiter'-'
 
                          (1)
>--+-| SELECT Statement |------+-------------------------------><
   '-variable------------------'

Notes:

See page SELECT

Element	Description	Restrictions	Syntax
delimiter	Quoted string to specify the field delimiter character in filename file	See DELIMITER Clause	Quoted String, p. Quoted String
filename	Operating-system file to receive the rows. Default pathname is the current directory.	See UNLOAD TO File.	Quoted String, p. Quoted String
variable	Host variable that contains the text of a valid SELECT statement	Must have been declared as a character data type	Language- specific

Usage

UNLOAD copies to a file the rows retrieved by a query. You must have the Select privilege on all columns specified in the SELECT statement. For information on database-level and table-level privileges, see GRANT.

You can specify a literal SELECT statement or a character variable that contains the text of a SELECT statement. (See SELECT.)

The following example unloads rows whose value of customer.customer_num is greater than or equal to 138, and writes them to a file named cust_file:

UNLOAD TO 'cust_file' DELIMITER '!' 
   SELECT * FROM customer WHERE customer_num> = 138

The resulting output file, cust_file, contains two rows of data values:

138!Jeffery!Padgett!Wheel Thrills!3450 El Camino!Suite 10!Palo Alto!CA!94306!!
139!Linda!Lane!Palo Alto Bicycles!2344 University!!Palo Alto!CA!94301!
    (415)323-5400

UNLOAD TO File

The UNLOAD TO file, as specified by the filename parameter, receives the retrieved rows. You can use an UNLOAD TO file as input to a LOAD statement.

In the default locale, data values have these formats in the UNLOAD TO file.

Data Type	Output Format
BOOLEAN	BOOLEAN values appear as either t for TRUE or f for FALSE.
Character	If a character field contains the delimiter, IBM Informix products automatically escape it with a backslash ( \ ) to prevent interpretation as a special character. (If you use a LOAD statement to insert the rows into a table, backslash escape characters are automatically stripped.)
Collections	A collection is unloaded with its values between braces ( { } ) and a delimiter between each element. For more information, see Unloading Complex Types (IDS).
DATE	DATE values are represented as mm/dd/yyyy (or the default format for the database locale), where mm is the month (January = `1`, and so on), dd is the day, and yyyy is the year. If you have set the GL_DATE or DBDATE environment variable, the UNLOAD statement uses the specified date format for DATE values.
DATETIME, INTERVAL	Literal DATETIME and INTERVAL values appear as digits and delimiters, without keyword qualifiers, in the default format yyyy-mm-dd hh:mi:ss.fff. Time units outside the declared precision are omitted. If the GL_DATETIME or DBTIME environment variable is set, DATETIME values appear in the specified format.
DECIMAL, MONEY	Values are unloaded with no leading currency symbol. In the default locale, comma ( , ) is the thousands separator and period ( . ) is the decimal separator. If DBMONEY is set, UNLOAD uses its specified separators and currency format for MONEY values.
NULL	NULL appears as two delimiters with no characters between them.
Number	Values appear as literals, with no leading blanks. INTEGER, INT8, or SMALLINT zero appear as `0`, and MONEY, FLOAT, SMALLFLOAT, or DECIMAL zero appears as `0.0`.
ROW types (named and unnamed)	A ROW type is unloaded with its values enclosed between parentheses and a field delimiter separating each element. For more information, see Unloading Complex Types (IDS).
Simple large objects (TEXT, BYTE)	TEXT and BYTE columns are unloaded directly into the UNLOAD TO file. BYTE values appear in ASCII hexadecimal form, with no added whitespace or newline characters. For more information, see Unloading Simple Large Objects.
Smart large objects (CLOB, BLOB)	CLOB and BLOB columns are unloaded into a separate operating-system file on the client computer. The CLOB or BLOB field in the UNLOAD TO file contains the name of this file. For more information, see Unloading Smart Large Objects (IDS).
User-defined data types (opaque types)	Opaque types must have an export( ) support function defined. They need special processing to copy data from the internal format of the opaque data type to the UNLOAD TO file format. An export binary support function might also be required for data in binary format. The data in the UNLOAD TO file would correspond to the format that the export( ) or exportbinary( ) support function returns.

For more information on DB* environment variables, refer to the IBM Informix Guide to SQL: Reference. For more information on GL* environment variables, refer to the IBM Informix GLS User's Guide.

In a nondefault locale, DATE, DATETIME, MONEY, and numeric column values have formats that the locale supports for these data types. For more information, see the IBM Informix GLS User's Guide.

UNLOAD

Syntax

Usage

UNLOAD TO File