Exporting LOB Values

You can export LOB values in data streams with the EXPORT TABLE and EXPORT COLUMNS commands. Using the appropriate Loader syntax rules, you can define the data streams to which the LOB values are exported.

Note Note

Special syntax rules for handling LOB values are not 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>]

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

<lob_code_spec> ::=
  <code_spec>
| BINARY

End of the code.

<column_spec>

You can use the column name (column_name) or the column ID (column_id) for a LOB column that is to be exported. The column ID reflects the position of the column in the column list.

Example Example

Specify the LOB column name INFO

EXPORT TABLE hotel.hotel

DATA OUTSTREAM 'hotel_csv.data'

LOB OUTSTREAM info 'hotel_info.data'

End of the example.

Example Example

Specify the column ID of the LOB column INFO

EXPORT TABLE hotel.hotel

DATA OUTSTREAM 'hotel_csv.data'

LOB OUTSTREAM 5 'hotel_info.data'

End of the example.

<lob_code_spec>

Using the syntax rule lob_code_spec, you can specify the code type for each data stream containing LOB values that are to be exported to a column . Only one code type may be specified in the command for each data stream created.

Example Example

Specify the ASCII code type

EXPORT TABLE hotel.hotel

DATA OUTSTREAM 'hotel_csv.data'

LOB OUTSTREAM info 'hotel_info.data' ASCII

End of the example.

Specifying the LOB Data Stream <stream_name>

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. When you export LOB values, you must distinguish between the following cases:

Each LOB value to be exported is exported to a separate LOB data stream
All LOB values to be exported are exported to one LOB data stream

Each LOB Value to be Exported Is Exported to a Separate LOB Data Stream

You use the export command to assign the name of a LOB data stream for each LOB value that is to be exported. After you have specified the LOB column in the export command, you specify the name of the LOB data stream with a number of placeholders for sequential numbering of the generated LOB data streams. If there is no value in a data record for the LOB column you want to export (the value is an empty character string), an empty LOB data stream is generated for this LOB value. Using the unique name generated in this way, the individual LOB data streams can be assigned to the corresponding data record in the target table.

Note Note

Supply a sufficient number of numeric characters at the end of the LOB data stream name as a placeholder. If the upper limit is reached while the data is being exported, but there are still values left to be exported, Loader generates an error message and terminates the export command.

End of the note.

Example Example

EXPORT TABLE hotel.hotel

DATA OUTSTREAM 'hotel_csv.data'

LOB OUTSTREAM info 'hotel_info.data###'

Loader generates the LOB data stream with the name hotel_info.data.001 for the first LOB value to be exported, the data stream hotel_info.data.002 for the second, and so on.

Contents 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.

See also: Importing LOB Values, Each LOB Value to be Imported Is in a Separate LOB Data Stream

All LOB Values to be Exported are Exported to One LOB Data Stream

In the command for exporting data, you specify the name of a LOB data stream for each LOB column. This data stream contains the LOB values of this LOB column to be exported. If there is no value in a data record for the LOB column you want to export (the value is an empty character string), the position specification for this LOB value is generated as follows: the starting position is the end position of the preceding LOB value in the column plus 1; the end position is the end position of the preceding LOB value in the column. This means that if there is an empty value, the starting position is always one value larger than the end position. The starting and end positions of the generated LOB value in the LOB data stream enable the LOB values to be assigned to data records.

Example Example

EXPORT TABLE hotel.hotel

DATA OUTSTREAM 'hotel_csv.data'

LOB OUTSTREAM info 'hotel_info.data'

Content of the data stream hotel_csv.data (excerpt):

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

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

"30","Regency","20037","477 17th Avenue","995-1157"

End of the example.

See: Importing LOB Values, All of a Column’s LOB Values the are to be Imported are in One LOB Data Stream

More Information

Exporting and Importing LOB Values