CLOB and BLOB can be exported to the same file as the rest of the column data, or the LOB column data can be exported to separate external file. When the LOB column data is exported to 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 an separate external file might be faster than storing the LOB data in the same file as the rest of the column 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 the hexadecimal format. To import data into a table that has columns of these data types, the data in the import file for those column must be in the hexadecimal format.
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 the hexadecimal format.
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
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 the 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.
You can use the SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE
and SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE procedures to write LOB
data to a separate external file. These procedures include the
When you
use these procedures, the location of the LOB data is written to the main
export file. The format of the reference to the LOB stored in the main export
file is
If a LOB column value is NULL, length is written as -1. No data conversion is performed when you export LOB data to an external file. BLOB data is written in binary format and CLOB data is written using the codeset that you specify.
See