<< Bulk import and export requirements | File format for input and output >>

Bulk import and export of large objects

<< Bulk import and export requirements | File format for input and output >>

Derby Server and Administration Guide

otherwise, the import operation will be rolled back. You cannot use the REPLACE
parameter if the table has triggers enabled.

Restrictions on tables

You cannot use import procedures to import data into a system table or a declared
temporary table.

Bulk import and export of large objects

You can import and export large objects (LOBs) using the Derby system procedures.

Importing and exporting CLOB and BLOB data

CLOB and BLOB data can be exported to the same file as the rest of the column data, or
the LOB column data can be exported to a separate external file. When the LOB column
data is exported to a separate external file, reference to the location of the LOB data is
placed in the LOB column in the main export file.

Importing and exporting LOB data using a separate external file might be faster than
storing the LOB data in the same file as the rest of the column data:

· The CLOB data does not have to be scanned for the delimiters inside the data
· The BLOB data does not need to be converted to hexadecimal format

Importing and exporting other binary data

When you export columns that contain the data types CHAR FOR BIT DATA, VARCHAR
FOR BIT DATA, and LONG VARCHAR FOR BIT DATA, the column data is always
exported to the main export file. The data is written in hexadecimal format. To import
data into a table that has columns of these data types, the data in the import file for those
columns must be in hexadecimal format.

Importing LOB data from a file that contains all of the data

You can use the SYSCS_UTIL.SYSCS_IMPORT_TABLE and
SYSCS_UTIL.SYSCS_IMPORT_DATA procedures to import data into a table that
contains a LOB column. The LOB data must be stored in the same file as the other
column data that you are importing. If you are importing data from a file that was exported
from a non-Derby source, the binary data must be in hexadecimal format.

Importing LOB data from a separate external file

You can use the SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE
and SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedures to
import LOB data that is stored in a file that is separate from the main import file. These
procedures read the LOB data using the reference that is stored in the main import file. If
you are importing data from a non-Derby source, the references to the LOB data must be
in the main import file in the format lobsFileName.Offset.length/. This is the same method
that the Derby export procedures use to export the LOB data to a separate external file.

Exporting LOB data to the same file as the other column data

You can use the SYSCS_UTIL.SYSCS_EXPORT_TABLE and
SYSCS_UTIL.SYSCS_EXPORT_QUERY procedures to write LOB data, along with rest
of the column data, to a single export file.

CLOB column data is treated same as other character data. Character delimiters are
allowed inside the CLOB data. The export procedures write the delimiter inside the data
as a double-delimiter.

BLOB column data is written to the export file in hexadecimal format. For each byte of
BLOB data, two characters are generated. The first character represents the high nibble
(4 bits) in hexadecimal and the second character represents the low nibble.