LOB export considerations

When exporting tables with large object (LOB) columns, the default action is to export a maximum of 32 KB per LOB value and to place it in the same file as the rest of the column data. If you are exporting LOB values that exceed 32 KB, you should have the LOB data written to a separate file to avoid truncation.

To specify that LOB should be written to its own file, use the lobsinfile file type modifier. This modifier instructs the export utility to place the LOB data in the directories specified by the LOBS TO clause. Using LOBS TO or LOBFILE implicitly activates the lobsinfile file type modifier. By default, LOB values are written to the same path to which the exported relational data is written. If one or more paths are specified with the LOBS TO option, the export utility cycles between the paths to write each successful LOB value to the appropriate LOB file. You can also specify names for the output LOB files using the LOBFILE option. If the LOBFILE option is specified, the format of lobfilename is lobfilespec.xxx.lob, where lobfilespec is the value specified for the LOBFILE option, and xxx is a sequence number for LOB files produced by the export utility. Otherwise, lobfilename is of the format: exportfilename.xxx.lob, where exportfilename is the name of the exported output file specified for the EXPORT command, and xxx is a sequence number for LOB files produced by the export utility.

By default, LOBs are written to a single file, but you can also specify that the individual LOBs are to be stored in separate files. The export utility generates a LOB Location Specifier (LLS) to enable the storage of multiple LOBs in one file. The LLS, which is written to the export output file, is a string that indicates where the LOB data is stored within the file. The format of the LLS is lobfilename.ext.nnn.mmm/, where lobfilename.ext is the name of the file that contains the LOB, nnn is the offset of the LOB within the file (measured in bytes), and mmm is the length of the LOB (measured in bytes). For example, an LLS of db2exp.001.123.456/ indicates that the LOB is located in the file db2exp.001, begins at an offset of 123 bytes into the file, and is 456 bytes long. If the indicated size in the LLS is 0, the LOB is considered to have a length of 0. If the length is -1, the LOB is considered to be NULL and the offset and file name are ignored.

If you don't want individual LOB data concatenated to the same file, use the lobsinsepfiles file type modifier to write each LOB to a separate file.

Note: The IXF file format does not store the LOB options of the column, such as whether or not the LOB column is logged. This means that the import utility cannot re-create a table containing a LOB column that is defined to be 1 GB or larger.

Example 1
The following example shows how to export LOBs (where the exported LOB files have the specified base name lobs1) to a DEL file:

   db2 export to myfile.del of del lobs to mylobs/
      lobfile lobs1 modified by lobsinfile
      select * from emp_photo

Example 2
The following example shows how to export LOBs to a DEL file, where each LOB value is written to a separate file and lobfiles are written to two directories:

db2 export to myfile.del of del
lobs to /db2exp1/, /db2exp2/ modified by lobsinfile
select * from emp_photo