DB2 10.5 – How to Compress CLOB Columns

compressiondb2db2-10.5db2-luw

I have a table with a CLOB(1M). If I enable compression (compression yes adaptive) on the the table the CLOB is not compressed. Is there a way to make this work in DB2 10.5?

Best Answer

Historically LOB data are stored in data structures separate from the normal row data, these data structures are not compressed. With Db2 for LUW 9.7 and later you have an option to store part of LOB columns, subject to the row length limitation, inline with data rows. Inline LOB data will be compressed along with the rows they belong to.

CREATE TABLE foo (
  lob_col CLOB (1M) INLINE LENGTH 2000
)

This is only helpful if the majority of your LOB values fall within the inline length limit. Note also that storing LOBs inline can have impact on query performance (fewer rows fit on a page, thus potentially increasing I/O demand) and logging overhead (inlined LOBs are always logged).

Related Solutions

Db2 – How to convert DB2 9.5 to DB2 10.5 online (Linux)

You cannot migrate the database online.

You can use InfoSphere DR or any other replication software for migration with minimal downtime, however, if your application itself does not support transparent connection failover you will still experience a brief outage.

The process in brief would look like so:

Install DB2 10.5.
Create a new database in a DB2 10.5 instance, using db2look.
Install and configure replication software.
Perform a full refresh from the old (V9.5) database to the new one.
Continue mirroring until the new database catches up to the old one.
Initiate application failover.
Stop replication once the application has fully failed over.

DB2 – How to Alter Table from CLOB to VARCHAR

You cannot change a LOB type to a non LOB type and vice versa (I assume LUW here):

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

[...] SET DATA TYPE altered-data-type Start of changeSpecifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837). A LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).End of change.

One way is to unload the data with export:

db2 "export to t.ixf of ixf select ..., cast(varchar_clob as varchar(100)), ... from t"

then drop all foreign key pointing to this table, drop the table, create the new table, load the data back to the table and finally add the foreign keys that where dropped.

An alternative approach is to drop the foreign keys, rename the table, create the new table and use load from cursor. When done drop the renamed table

Best Answer

Related Solutions

Db2 – How to convert DB2 9.5 to DB2 10.5 online (Linux)

DB2 – How to Alter Table from CLOB to VARCHAR

Related Question