DB2 CLOB Tools - Import, Export, View and Edit

In DB2, CLOB (Character Large Object) is a data type used to store large amounts of character – based data, such as long text documents, XML data, or any other data that consists of a large number of characters. More: DB2 CLOB.

DB2 CLOB - type family and size limits

Type in IBM DB2	Maximum size
`CLOB(n)`	up to 2 GB, counted in characters
`DBCLOB(n)`	double-byte character set, same 2 GB ceiling
`CLOB FILE`	data stays in the file system, the table holds a reference

CLOB versus BLOB in IBM DB2

BLOB holds raw bytes and DB2 never converts them; CLOB is decoded with the database code page and its length counts characters. Images, archives and encrypted blobs can only go into a BLOB.

What limits you first

DB2 keeps LOB data in a separate LOB tablespace, and db2 export only writes a lob locator unless you pass a lob to directory. Miss it and the export "succeeds" while the actual data is never written.

CLOB from the command line

Export: db2 export to out.del of del lob to ./lobs/
Import: db2 import from in.del of del lobs from ./lobs/

Things that bite with CLOB in IBM DB2

A BLOB column declared without a length defaults to 1 MB - larger values are truncated silently
Exporting without lob to produces a file full of 100-byte locators instead of data
Use db2move to copy BLOBs between databases - it handles the LOB tablespace and files for you