Importing LOB Values

You can use the IMPORT TABLE and IMPORT/UPDATE COLUMNS commands to import (or update) LOB values to a target table. Using the relevant Loader syntax rules, you can define which of the columns of a target table the LOB values are exported to or which of the columns are updated.

Note Note

Special syntax rules for handling LOB values are not required if you use PAGES or RECORDS formats.

End of the note.

Structure

Syntax Syntax

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

<column_spec>

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

Example Example

Specify the LOB column name INFO

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

LOB INSTREAM info 'hotel_info.data'

End of the example.

Example Example

Specify the column ID of the LOB column INFO

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

LOB INSTREAM 5 'hotel_info.data'

End of the example.

<lob_code_spec>

With the syntax rule lob_code_spec, you can also specify the code type for all data streams containing LOB values that are to be imported to a column . You can only specify one code type for all the data streams, as the code type has to be specified in the command.

Example Example

Specifying the ASCII code type

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

LOB INSTREAM info 'hotel_info.data' ASCII

End of the example.

Specifying the LOB Data Stream <stream_name>

The data for LOB values is stored in LOB data streams separately from the other data. These stream_name data streams (name and path of the data stream) are always of data carrier type FILE. When you import LOB values, you must distinguish between the following cases:

Each LOB value to be imported is in a separate LOB data stream.
All of a column’s LOB values that are to be imported are in one LOB data stream.

Each LOB Value to be Imported is in a Separate LOB Data Stream

You do not need to use any of the LOB syntax rules in the IMPORT command, as Loader evaluates the DATA data stream. After the corresponding export has been carried out (Exporting LOB Values, Each LOB Value to be Exported is Exported in a Separate LOB Data Stream), the column values are replaced in the DATA data stream by the names of the LOB data streams that are to be imported (if required, the sequential numbering is extended). The LOB data streams are imported entirely as LOB values.

Example Example

The LOB data streams are imported entirely as LOB values

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

hno 1

name 2

zip 3

address 4

info 5 DEFAULT NULL

Content of the DATA 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.

You can also use the LOB INSTREAM [FILE] <column_spec> <lob_column_spec> syntax rule in the IMPORT command if you want the values in the LOB data streams to be imported according to the code type specified. In this case, the DATA data stream contains the names of the LOB data streams to be imported (possibly enhanced with sequential numbering) instead of the column values. The LOB data streams are imported as LOB values.

Example Example

The LOB data streams are imported as LOB values and are read in binary.

IMPORT TABLE hotel.hotel

DATA INSTREAM 'hotel_csv.data'

hno 1

name 2

zip 3

address 4

info 5 DEFAULT NULL

LOB INSTREAM info BINARY

Content of the DATA 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.

All of a Column’s LOB Values that are to be Imported are in One LOB Data Stream

You use syntax rule LOB INSTREAM [FILE] <column_spec> '<stream_name>' to specify the LOB data stream. After the corresponding export has been carried out (Exporting LOB Values, All LOB Values to be Exported are Exported to One LOB Data Stream), the column values are replaced in the DATA data stream by the positions of the LOB values in the LOB data stream.

Example Example

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'

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

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

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

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

End of the example.

You can use the LOB INSTREAM [FILE] <column_spec> '<stream_name>' syntax rule in the IMPORT command if you want the values in the LOB data streams to be imported according to the code type specified. In this case also, only the positions of the LOB values in the LOB data stream are specified in the DATA data stream.

Example Example

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

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

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

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

End of the example.

More Information

Exporting and Importing LOB Values