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

The CLOB data does not have to be scanned for the delimiters inside the data
The BLOB data does not need to be converted into a 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 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.

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

Exporting LOB data to a separate external file from the other column data

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 lobFileName parameter, which specifies the name of external file for the LOB data.

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 lobsFileName.Offset.length/.

Offset is the position in the external file in bytes
length is the size of the LOB column data in bytes

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 Examples of bulk import and export for examples using each of the import and export procedures.