Store RTF in DB2 CLOB

by

Posted on by

RTF(Rich Text Format) can be stored as DB2 CLOB data.

1. DB2 CLOB Type Overview

DB2 CLOB (Character Large Object) is designed for storing large character data and supports encodings like UTF-8/GBK.

Optional subtypes:

  • CLOB(n): n = number of characters (1~2G), e.g., CLOB(1M) = 1 million characters
  • CLOB: Default 1MB, adjust based on RTF size

2. Pure SQL Implementation Steps

Step 1: Create Table with CLOB Field

CREATE TABLE RTF_DOCS (
    DOC_ID INT PRIMARY KEY,
    DOC_NAME VARCHAR(100) NOT NULL, -- Document name
    RTF_CONTENT CLOB(1M) NOT NULL,  -- CLOB field for RTF text
    CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Creation time (optional)
);

Step 2: Insert RTF Text into CLOB Field

-- Method 1: Insert short RTF text (testing)
INSERT INTO RTF_DOCS (DOC_ID, DOC_NAME, RTF_CONTENT)
VALUES (
    1,
    'test.rtf',
    '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Calibri;}} {\\colortbl ;\\red0\\green0\\blue0;} \\pard\\f0\\fs22 Hello World!\\par}'
);

-- Method 2: Insert external RTF file (DB2 command line)
-- 1. Convert RTF to UTF-8 text file first
-- 2. Import with DB2 IMPORT command
IMPORT FROM '/path/to/test.rtf' OF DEL MODIFIED BY DELPRIORITYCHAR 
INSERT INTO RTF_DOCS (DOC_ID, DOC_NAME, RTF_CONTENT) VALUES (2, 'imported.rtf', ?);

Step 3: Query/Edit RTF in CLOB

-- Query RTF content (display directly as text)
SELECT DOC_NAME, RTF_CONTENT FROM RTF_DOCS WHERE DOC_ID = 1;

-- Update RTF content
UPDATE RTF_DOCS 
SET RTF_CONTENT = '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Arial;}} \\pard\\f0\\fs24 Updated Content!\\par}'
WHERE DOC_ID = 1;

-- Truncate RTF content (view first 100 characters)
SELECT DOC_NAME, SUBSTR(RTF_CONTENT, 1, 100) AS RTF_PREVIEW FROM RTF_DOCS;

Step 4: Delete RTF Record

DELETE FROM RTF_DOCS WHERE DOC_ID = 1;

3. Simplify DB2 CLOB RTF Management with DBBlobEditor

Instead of writing complex SQL/IMPORT commands, DBBlobEditor streamlines RTF management for DB2 CLOB fields:

4. Notes

  • DB2 CLOB fields require a character limit (e.g., CLOB(1M)); default is 1MB if unspecified
  • Use IMPORT/LOAD commands for large RTF files to avoid errors from long character strings in direct INSERT
  • Use SUBSTR() to preview CLOB content during queries to avoid performance issues from full loading

Related Guides