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- 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- 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
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 the 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.
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
LOBSFILENAME parameter, which specifies the name of the
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 for
examples using each of the import and export procedures.