Store images in DB2 BLOB

by

Posted on by

DB2 BLOB Type Features

  • Native large object type for DB2
  • Supports large image sizes (up to 2GB per BLOB)
  • Distinct from CLOB (character large object) and NCLOB (national character large object)

Step 1: Create a Table with BLOB Column

CREATE TABLE image_storage (
    image_id INT PRIMARY KEY,
    image_name VARCHAR(100),
    image_blob BLOB,
    upload_time TIMESTAMP
);

Step 2: Insert Images into DB2 BLOB

Method 1: Using SQL (DB2 Command Line)

INSERT INTO image_storage (image_id, image_name, image_blob, upload_time)
VALUES (
    1,
    'product_photo.jpg',
    BLOB(FROM_FILE('/path/to/product_photo.jpg')),
    CURRENT_TIMESTAMP
);

Method 2: Using DBBlobEditor

Insert Single Image to DB2 BLOB

  1. Connect to your DB2 database in DBBlobEditor
  2. Double click the “image_storage” table to open it
  3. Click “+” button to add a record, and then Edit image stored in DB2 BLOB field

Batch Insert Images to DB2 BLOB

Step 3: Retrieve and Restore DB2 BLOB to Image

Method 1: Using SQL (DB2 Command Line)

-- Query BLOB data
SELECT image_blob 
FROM image_storage 
WHERE image_id = 1;

-- Restore to file (DB2 Command Line)
EXPORT TO /path/to/exported_photo.jpg 
FROM (SELECT image_blob FROM image_storage WHERE image_id = 1) 
FORMAT BINARY;

Method 2: Using DBBlobEditor

Retrieve and Restore Single Image

  1. Connect to your DB2 database in DBBlobEditor
  2. Navigate to the target table and column
  3. Double click to View image stored in DB2 BLOB field
  4. Click menu “File” -> “Save” to save BLOB data as a local image file

Batch Export DB2 BLOB to Images

DB2 Specific Notes

  • Configure BLOB storage paths in DB2 configuration (db2cfg)
  • Optimize large image storage with table space separation
  • Avoid indexing BLOB columns (impacts performance)

Related Guides