IDS Forum

Re: Data Compression

Posted By: John Miller iii
Date: Thursday, 9 July 2009, at 1:19 p.m.

In Response To: Data Compression (SHAHZAD SALAM KASI)

Just to be clear here, if you run compress by itself, the size of the
table size will
not change. Hence if you execute a command like:

execute function task("table compress", "database","tablename");

That is expected behavior. What compression does is compress
each row in place (if it can). This mean if you have a single 2KB page
with
4 rows of 500 bytes. Compression will keep all of the rows on the same
page
and shrink them. So now you might have 4 rows on the exact same page,
each
of size 250 bytes. The page now has 1000 bytes free for other rows to
consume.

Now if you want to compress the table and tighten up all this free space
and return
the free space to the system. Then you need to add two additional commands
to
your command line. The first is the "repack" command. This will take the
rows
at the end of the table and move them to the free space in the beginning of
the table.
When this command completes all the free space will be at the end of the
table.

The last command to add to the compress command line is the "shrink"
command. This
will remove any free space at the end of the table and return it to the
main ids pool of
free space. It will not attempt to free space below your first extent
size. If you have
large first extents use the new alter table command to change your first
extent size so
shrink can free space up below your first extent size.

In short if you want to compress and make a table as small as you can (but
never smaller than
your first extent size) then run the following commands:

execute function task("table compress repack shrink",
"database","tablename");

One think to be aware of is that the "repack" and "shrink" commands can be
run on table
which are not compressed. If you have a table which you had inserted lots
of rows, then
purged say 80% of these rows and you want to return this purges space back
to IDS. The
consider using the "repack" and "shrink" commands. These run online and
will return the
extra space back to IDS.

execute function task("table repack shrink","dbname","tablename");

Your last question concerns the type of data compression will compress. It
will compress
all data stored in row. It will not compress blob,clob,byte,text data, nor
will it compress
data stored on index pages.

An additional side benefit is that the data stored in the logical log is
also compressed
so the logical log space consumed should also drop. So in you inserted a
500 byte row in
a logged database this row would consume approximately 500 bytes in the
logical logs, If this
table is compressed, then the compressed image of the row is stored in the
database. This
is a double win for those using Enterprise Replication, HDR or MACH.

My last comment is that while repack and shrink come with the base server,
compression

is an add on feature.

John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 07/09/2009 03:59:32 AM:

> [image removed]
>
> Data Compression [16278]
>
> SHAHZAD SALAM KASI
>
> to:
>
> ids
>
> 07/09/2009 04:00 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi,
> When I compress the data in a table, After Compression, the result
mentions
> that 67% Compression has been achieved, but the size of the table remains
the
> same as before compression?
>
> Is there an issue with data compression that it says compression has been

> done, but have'nt?
>
> Is Data Compression compresses any particular type of data or can't
compress
> any particular type of data.
>
> I have run compression on the data having byte data type for one field.
>
> Regads
>
>
>
*******************************************************************************

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Data Compression
  SHAHZAD SALAM KASI -- Thursday, 9 July 2009, at 6:59 a.m.
  • Re: Data Compression
    VIKAS HIVARKAR -- Thursday, 9 July 2009, at 7:47 a.m.
    • Re: Data Compression
      VIKAS HIVARKAR -- Thursday, 9 July 2009, at 7:56 a.m.
      • Re: Data Compression
        SHAHZAD SALAM KASI -- Thursday, 9 July 2009, at 8:52 a.m.
        • Re: Data Compression
          John Miller iii -- Thursday, 9 July 2009, at 1:46 p.m.
          • Re: Data Compression
            SHAHZAD SALAM KASI -- Friday, 10 July 2009, at 1:09 a.m.
  • Re: Data Compression
    Nilesh Ozarkar -- Thursday, 9 July 2009, at 11:34 a.m.
  • Re: Data Compression
    John Miller iii -- Thursday, 9 July 2009, at 1:19 p.m.
  • Re: Data Compression
    GUSTAVO TOBARES -- Thursday, 9 July 2009, at 11:54 p.m.
    • Re: Data Compression
      GUSTAVO TOBARES -- Friday, 10 July 2009, at 9:46 a.m.
    • Re: Data Compression
      Art Kagel -- Friday, 10 July 2009, at 11:21 a.m.
      • Re: Data Compression
        GUSTAVO TOBARES -- Friday, 10 July 2009, at 1:50 p.m.
        • Re: Data Compression
          GUSTAVO TOBARES -- Friday, 10 July 2009, at 1:53 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.