LOB and XML file behavior when importing and exporting
LOB and XML files share certain behaviors and compatibilities that can be used when importing and exporting data.
- Export
- When exporting data, if one or more LOB paths are specified
with the LOBS TO option, the export utility will cycle between the paths to
write each successive LOB value to the appropriate LOB file. Similarly, if
one or more XML paths are specified with the XML TO option, the export utility
will cycle between the paths to write each successive XQuery and XPath Data
Model (XDM) instance to the appropriate XML file. By default, LOB values
and XDM instances are written to the same path to which the exported relational
data is written. Unless the LOBSINSEPFILES or XMLINSEPFILES file type modifier
is set, both LOB files and XML files can have multiple values concatenated
to the same file.
The LOBFILE option provides a means to specify the base name of the LOB files generated by the export utility. Similarly, the XMLFILE option provides a means to specify the base name of the XML files generated by the export utility. The default LOB file base name is the name of the exported data file, with the extension .lob. The default XML file base name is the name of the exported data file, with the extension .xml. The full name of the exported LOB file or XML file therefore consists of the base name, followed by a number extension that is padded to three digits, and the extension .lob or .xml.
- Import
- When importing data, a LOB Location Specifier (LLS) is compatible with an XML target column, and an XML Data Specifier (XDS) is compatible with a LOB target column. If the LOBS FROM option is not specified, the LOB files to import are assumed to reside in the same path as the input relational data file. Similarly, if the XML FROM option is not specified, the XML files to import are assumed to reside in the same path as the input relational data file.
Export examples
In the following example, all LOB values are written to the file /mypath/t1export.del.001.lob, and all XDM instances are written to the file /mypath/t1export.del.001.xml:
EXPORT TO /mypath/t1export.del OF DEL MODIFIED BY LOBSINFILE
SELECT * FROM USER.T1
In the following example, the first LOB value is written to the file /lob1/t1export.del.001.lob, the second is written to the file /lob2/t1export.del.002.lob, the third is appended to /lob1/t1export.del.001.lob, the fourth is appended to /lob2/t1export.del.002.lob, and so on:
EXPORT TO /mypath/t1export.del OF DEL LOBS TO /lob1,/lob2
MODIFIED BY LOBSINFILE SELECT * FROM USER.T1
In the following example, the first XDM instance is written to the file /xml1/xmlbase.001.xml, the second is written to the file /xml2/xmlbase.002.xml, the third is written to /xml1/xmlbase.003.xml, the fourth is written to /xml2/xmlbase.004.xml, and so on:
EXPORT TO /mypath/t1export.del OF DEL XML TO /xml1,/xml2 XMLFILE xmlbase
MODIFIED BY XMLINSEPFILES SELECT * FROM USER.T1
Import examples
IMPORT FROM myfile.del of del LOBS FROM /lobpath XML FROM /xmlpath
MODIFIED BY LOBSINFILE XMLCHAR replace into mytable
If "myfile.del"
contains the following data: mylobfile.001.lob.123.456/
The
import utility will try to import an XML document from the file /lobpath/mylobfile.001.lob,
starting at file offset 123, with its length being 456 bytes.The file "mylobfile.001.lob" is assumed to be in the LOB path, as opposed to the XML path, since the value is referred to by a LOB Location Specifier (LLS) instead of an XML Data Specifier (XDS).
The document is assumed to be encoded in the character codepage, since the XMLCHAR file type modifier is specified.