DB2 BLOB Tools - Import, Export, View and Edit

• Import file data to DB2 BLOB
• Export DB2 BLOB data to files
• View and edit BLOB data stored in DB2
• View and edit image stored in DB2 BLOB field
• View PDF stored in DB2 BLOB field
• Batch export DB2 query results BLOB data to files
• Batch update DB2 table BLOB data from files
• Batch insert DB2 table BLOB data from files

DB2 BLOB - type family and size limits

Type in IBM DB2	Maximum size
BLOB(n)	up to 2 GB (2,147,483,647 bytes)
BLOB (no length)	defaults to only 1 MB
BLOB FILE	stores a file reference, data stays in the file system

BLOB versus CLOB 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.

BLOB 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 BLOB 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