Importing and Exporting LONG Values

You can use EXPORT/IMPORT TABLE and EXPORT/IMPORT/UPDATE COLUMNS commands to export LONG values to data streams, import them to target table or modify the LONG values in a table.

Using the appropriate syntax rules, you can define which data streams the LONG values are exported to or which of a target table’s columns the LONG values are imported to. 

Special syntax rules for handling LONG values are not required if you are using the RECORDS or PAGES formats.

Syntax

<export_lobcolumn_spec> ::= LOB <outstream_lob_spec>
<outstream_lob_spec>    ::= OUTSTREAM [FILE] <column_spec> '<stream_name>' [<lob_code_spec>]

<import_lobcolumn_spec> ::= LOB <instream_lob_spec>
<instream_lob_spec>     ::= INSTREAM [FILE] <column_spec> <lob_code_spec>
                          | INSTREAM [FILE] <column_spec> '<stream_name>' [<lob_code_spec>]

<column_spec>          ::= <column_name> | <column_id>
<column_name>          ::= <identifier>
<column_id>            ::= <unsigned_integer>

<lob_code_spec>         ::= <code_spec> | BINARY

<stream_name>

Name and path of the data stream

This is a data stream of data carrier type FILE.

See also: Data Stream

<column_name>

Name of column

See the SQL Reference Manual, Column Name (column_name)

<column_id>

Column ID

See the SQL Reference Manual, Unsigned Integer (unsigned_integer)

Examples

EXPORT TABLE hotel.hotel
  DATA OUTSTREAM 'hotel_csv.data'
  LOB OUTSTREAM info 'hotel_info.data'

The values of the INFO LONG columns are exported to the data stream hotel_info.data.

EXPORT TABLE hotel.hotel
  DATA INSTREAM 'hotel_csv.data'
  LOB INSTREAM info 'hotel_info.data'

The values from the hotel_info.data  data stream are imported to the INFO LONG column.

Explanation

The data for LONG values is stored separately from the other data in LONG data streams. These stream_name data streams are always the data carrier type FILE.

Exporting LONG Values

You use syntax rule LOB OUTSTREAM [FILE] <column_spec> '<stream_name>' [<lob_code_spec>] to control the export of the LONG values.

?     Each of a column’s LONG values is exported to a separate LONG data stream.

?     All of a column’s LONG values can be exported to a separate LONG data stream.

If, when you are exporting LONG values, you specify more LONG data streams than there are LONG output columns in the column list, the Loader ignores the surplus LONG data streams.

For an explanation of the column specification (column_spec) and the data stream (stream_name), see the section on Exporting LONG Values.

Importing LONG Values

You can use syntax rule LOB INSTREAM [FILE] <column_spec> <lob_code_spec> or LOB INSTREAM [FILE] <column_spec> '<stream_name>' [<lob_code_spec>] to control the import of the LONG values.

?     The LONG values can be imported from more than one LONG data stream.

?     All the LONG values can be imported from one LONG data stream.

For an explanation of the two syntax rules for column specification (column_spec) and for the data stream (stream_name), see the section on Importing LONG Values.

<lob_code_spec>

You can also specify the code attribute for all data streams containing LONG values that are to be exported/imported to a column by specifying the syntax rule lob_code_spec. Only one code attribute can be specified for all the data streams, as the code attribute has to be specified in the command.

The LONG values from the INFO column are imported from more than one LONG data stream.

Specifying the code attribute ASCII

IMPORT TABLE hotel.hotel
  DATA INSTREAM 'hotel_csv.data'
    hno      1
    name     2
    zip      3
    address  4
    info     5 DEFAULT NULL

  LOB INSTREAM info ASCII

Content of the data stream hotel_csv.data:

"10","Congress","20005","155 Beechwood St.","'hotel_info.data001'"
"20","Long Island","11788","1499 Grove Street","?"
"30","Regency","20037","477 17th Avenue","'hotel_info.data002'"

The LONG values from the INFO column are imported from one LONG data stream.

Specifying the code attribute ASCII

IMPORT TABLE hotel.hotel
  DATA INSTREAM 'hotel_csv.data'
    hno      1
    name     2
    zip      3
    address  4
    info     5 DEFAULT NULL

  LOB INSTREAM info 'hotel_info.data' ASCII

Content of the data stream:

"10","Congress","20005","155 Beechwood St.","1-915"
"20","Long Island","11788","1499 Grove Street","?"
"30","Regency","20037","477 17th Avenue","916-1074"

See also:

Code Specification