UNLOAD - Unloading Data

UNLOAD - Unloading Data

You use the UNLOAD command to unload data and/or definitions from a Mimer SQL database to a file.

Syntax

where file-format-spec is:

Usage

Any ODBC-based SQL interpreter or with the MIMLOAD program.

For information on MIMLOAD, see EXPTOLOAD - Convert Old Format Export Files.

Description

The UNLOAD command generates data and/or definitions and places the result in a single file. You can use the options ALL (default), DEFINITIONS or DATA to specify the information you want.

When generating the data and definitions, a data description header is created before information is written to the file. If information from several tables is generated, a data description header for each data section is created.

A data description header contains escaping information and column separator information. For more information, see Data Description Headers and Files.

Data Escape Mode

UNLOAD generates data in escaped mode. This means that the data description header includes the data escape mode option.

When using data escape mode, the following characteristics are enabled, from the UNLOAD perspective:

Data from a specific table is ended by the escape sequence '\_' to mark end-of-table.
NULL values are indicated by the escape sequence '\-'.
BLOB and BINARY columns are unloaded in HEX code with a leading '\x' escape sequence for each byte.
BLOB, CLOB and NCLOB columns are unloaded so that the value length is given in front of the value as in the following CLOB example: '11:Abracadabra'
For CHAR, NCHAR, CLOB and NCLOB columns, the escape sequence '\x' is used only when there is binary data, such as ISO control codes, new-line characters, etc. in the data.
The '\u' escape sequence is used only when Unicode data is to be written to Latin1 files.

For information on escape sequences, see Escape Character Sequences.

The STDOUT Option

When you use the STDOUT option, generated output is written to the standard output stream. See Using STDIN/STDOUT/STDERR.

The AS Option

By using the AS option together with a file name specification, you can select the character set of the generated file. You can choose: LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.

UTFxxBE and UTFxxLE means UTFxx format with big or little endian byte order. UTFxx without endian notion means that the common endian for the current platform is assumed.

The character set used, if you do not use the AS option, is the default used in your host operating system.

For more information, see File Format Specifications.

The LOG Option

You can generate a log of the operation using the LOG option. The log file will include warnings and progress information about the operation. If you do not use the LOG option, warnings and progress information are suppressed.

The STDERR Option

When you use the LOG STDERR option, informational messages are written to the standard error stream. See Using STDIN/STDOUT/STDERR.

The USING and FROM Options

To specify the information to be unloaded, you use the USING or FROM options.

With the USING option, an SQL statement, such as SELECT * FROM T1; or a CALL to a procedure with parameter markers (?) for output parameters only, can be used to specify the source.

By using an SQL statement to form the source for the export operation, there are many possibilities available to format and customize the output.

With the FROM option, one or several databanks, tables or schemas can be used to form the source for the export operation. If using the FROM CURRENT USER option, the current ident is exported.

If tables are joined in the SQL statement used, and definitions are generated, a new table that is a reflection of the result of the join is defined. The default name of the new table is table1.

Error Management

The UNLOAD command runs until a major error is encountered. Minor problems are reported as warnings if LOG is enabled. If a fatal error occurs, an error message is displayed and the operation is aborted.

Examples

The following example will export the table details, with all related definitions, to a file:
 UNLOAD DEFINITIONS TO 'table_t.def' FROM TABLE details;
 
The following example will export the CREATE statement for table details together with all data in the table to a file in UTF16 format. A log file is used:
 UNLOAD TO 'table_t.all' AS UTF16
    LOG 'table_t.log'
    USING SELECT * FROM details;
Data Description Headers and Files

Data description headers and files are used to describe the data that follows.

The following table describes data description header elements:

Element

Usage

Description

#data

Required

Data description header start identifier.

escape mode

Optional

Indicates that the data is escaped, i.e. that some elements of the data are tagged for secure recognition at LOAD.

See the table below. When using UNLOAD, data escape mode is always used.

column separator 'x'

Optional

Indicates which character is the column separator when reading the data.

The default is the comma character (,).

If this option is not used, LOAD assumes that the comma character is the column separator.

text qualifier 'x'

Optional

Indicates which character is the qualifier for text strings in the data.

The default is the double quote character (").

If this option is not used, LOAD assumes the double quote character as the text qualifier or unqualified data.

null indicator 'x'

Optional

Indicates which character is the NULL value if found in a data field.

If this option is not stated, LOAD assumes the empty string, i.e. two consecutive column separators, as a NULL value.

In data escape mode, '\-' is treated as the NULL value.

using insert-statement

Optional

The SQL INSERT statement that indicates where, and in what way, data should be loaded.

This statement is used in the situation where the LOAD statement itself does not include a USING clause.

;

Required

Data description header end identifier.

As shown in the table above, the characters used to specify column separators, text qualifiers and NULL indicators must be enclosed in single quotes. If you use a single quote to specify a column separator, text qualifier or NULL indicator, you must enter it twice, for example, you would specify a single quote as a column separator as ''''.

Data Description Header Examples

For data unloaded from a Mimer SQL database using UNLOAD, the data description header generated could look as follows:
 #data escape mode using insert into t (c) values (?);
 
The example above implies the following for LOAD:

The column separator is the comma character (default).
Text strings are presumed to be unqualified or qualified with the double quote character.
Data escaping is assumed (see the table below).
The USING statement in the header will be used if no USING clause is given in the LOAD statement.

The following is another example of a data description header where all optional elements mentioned above, except data escape mode, are used:
 #data
 column separator ':'
 text qualifier '!'
 null indicator '§'
 using insert into t1 (c1,c2,c3) values (?,?,?);
 
In the example above, the table t1 and the columns c1, c2 and c3 are supposed to exist when starting the data load. Specific characters for column separator, text qualifier and null indicator are defined.

Escape Character Sequences

If data escape mode is specified, the back-slash character (\) is used as the escape character. The character following the escape character can be one of 'x', 'u', '-' or '_'. See the following table for a description of valid escape character sequences:

Escape character sequence

Usage Description

Example

\x (lower case letter 'x')

Preceding a hexadecimal byte value. A HEX value is assumed to be two HEX value digits, i.e. 0-F.

\x1A

\u (lower case letter 'u')

Preceding a unicode value. A Unicode value is assumed to be eight HEX value digits, i.e. 0-F.

\u12345678

\- (dash)

Null value

\-

\_ (underscore)

End of table, including end of stream or file

\_

Note: If you do not use data escape mode, end of file is treated as end of table. This means that such a data file only can contain data for one table.

File Format Specifications

The various file formats that can be used are described briefly in the following table:
File Format

Description

latin1

ISO 8859-1, i.e. ISO's 8-bit single-byte coded graphic character set for Western languages.
 utf8

utf16

utf32
Unicode Transformation Formats, standard character encoding schemes in accordance with ISO 10646.

For more information, see http://www.unicode.org
 utf16be
UTF16 format with big endian byte order.
 utf16le
UTF16 format with little endian byte order.
 utf32be
UTF32 format with big endian byte order.
 utf32le
UTF32 format with little endian byte order.

Element	Usage	Description
#data	Required	Data description header start identifier.
escape mode	Optional	Indicates that the data is escaped, i.e. that some elements of the data are tagged for secure recognition at LOAD. See the table below. When using UNLOAD, data escape mode is always used.
column separator 'x'	Optional	Indicates which character is the column separator when reading the data. The default is the comma character (,). If this option is not used, LOAD assumes that the comma character is the column separator.
text qualifier 'x'	Optional	Indicates which character is the qualifier for text strings in the data. The default is the double quote character ("). If this option is not used, LOAD assumes the double quote character as the text qualifier or unqualified data.
null indicator 'x'	Optional	Indicates which character is the NULL value if found in a data field. If this option is not stated, LOAD assumes the empty string, i.e. two consecutive column separators, as a NULL value. In data escape mode, '\-' is treated as the NULL value.
using insert-statement	Optional	The SQL INSERT statement that indicates where, and in what way, data should be loaded. This statement is used in the situation where the LOAD statement itself does not include a USING clause.
;	Required	Data description header end identifier.

Escape character sequence	Usage Description	Example
\x (lower case letter 'x')	Preceding a hexadecimal byte value. A HEX value is assumed to be two HEX value digits, i.e. 0-F.	\x1A
\u (lower case letter 'u')	Preceding a unicode value. A Unicode value is assumed to be eight HEX value digits, i.e. 0-F.	\u12345678
\- (dash)	Null value	\-
\_ (underscore)	End of table, including end of stream or file	\_

File Format	Description
latin1	ISO 8859-1, i.e. ISO's 8-bit single-byte coded graphic character set for Western languages.
utf8 utf16 utf32	Unicode Transformation Formats, standard character encoding schemes in accordance with ISO 10646. For more information, see http://www.unicode.org
utf16be	UTF16 format with big endian byte order.
utf16le	UTF16 format with little endian byte order.
utf32be	UTF32 format with big endian byte order.
utf32le	UTF32 format with little endian byte order.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se