Exporting and Importing LOB Values

You can use Loader EXPORT/IMPORT TABLE and EXPORT/IMPORT/UPDATE COLUMNS commands to export LOB values to data streams, import them to target tables, or modify the LOB values in a table. Using the appropriate syntax rules, you can define which data streams the LOB values are exported to or which columns of a target table the LOB values are imported to.

Note Note

No special syntax rules for handling LOB values are required if you use the PAGES or RECORDS formats.

End of the note.

Structure

Syntax Syntax

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

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

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

<lob_code_spec> ::=
  <code_spec>
| BINARY

End of the code.

The data for LOB values is stored separately from the other data in LOB data streams. These stream_name data streams (name and path of the data stream) are always of data carrier type FILE.

Exporting LOB Values

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

When you are exporting LOB values, if you specify more LOB data streams than there are LOB output columns in the column list, the Loader ignores the surplus LOB data streams. For an explanation of the column specification (column_spec) and the data stream (stream_name), see the section on Exporting LOB Values.

Importing LOB 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 LOB values.

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 LOB Values.

<lob_code_spec>

You can also specify the code attribute for all data streams containing LOB 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.

Example

Example Example

EXPORT TABLE hotel.hotel

DATA OUTSTREAM 'hotel_csv.data'

LOB OUTSTREAM info 'hotel_info.data'

End of the example.

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

Example Example

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

LOB INSTREAM info 'hotel_info.data'

End of the example.

The values of the hotel_info.data data stream are imported to the INFO LOB column.

Example Example

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

Specification of 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 (excerpt):

"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'"

End of the example.

Example Example

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

Specification of 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 hotel_csv.data (excerpt):

"10","Congress","20005","155 Beechwood St.","1-915"

"20","Long Island","11788","1499 Grove Street","?"

"30","Regency","20037","477 17th Avenue","916-1074"

End of the example.

More Information

Code Specification

Loader Commands and SQL Statements