DB2 Version 10.1 for Linux, UNIX, and Windows

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

For a table "mytable" that contains a single XML column, and the following IMPORT command:

   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.