Creating Compressed Tables with LOB Datatypes

You need not compress all columns in a table.

1. Select a compression level to determine the table's compression strategy:

   Option	Description
   Compression level	Strategy
   1 – 9, where 9 provides the best compression ratio but heaviest CPU usage	Higher compression ratio (ZLib algorithm)
   100 or 101	Lower CPU usage and execution time (FastLZ algorithm)

2. Create a table with LOB compression using:

   create table table_name (
   column_name data_type
   [compressed = compression_level | not compressed]
   ...
   )
   [with lob_compression = compression_level

   The compressed = parameter controls column-level compression; with lob_compression = controls table-level compression.

create table mail(user_name char(10),
mailtxt text compressed = 5,
photo image compressed = 1,
reply_mail text compressed = 9,
attachment image compressed = 100)
lock datarows
with lob_compression = 0