Which tables are compressed tables ?
I need to uncompress all compressed tables in a database. How can i list which tables are compressed ? Regards Jimmy Jonsson / Plexor AB
Original post:
I need to uncompress all compressed tables in a database. How can i list which
tables are compressed ?
Regards
Jimmy Jonsson / Plexor AB
Response:
I think what you are looking for is in the sysmaster:syscompdicts or
sysmaster:syscompdicts_full table. I believe those tables would have 1 row for
each compressed table/fragment in the system. Onstat -g ppd also prints a
portion of that, but I think onstat -g ppd only prints the partnum for the
currently active tables/fragments, but I think the sysmaster tables should
have records for any compressed table/fragments whether or not the table is
currently in use. I have not played around with compression a lot so I didn't
verify this, but I believe it's what the doc states anyway. Hope this helps.
Jacques Renaut
IBM Informix Advanced Support
APD Team
Here is a select that will let you know which tables are compressed, along
with telling you which tables
have the new version 12.10 feature Auto Compression enabled.
select T.*
,decode( bitand(P.flags, 134217728), 134217728, "Yes" , "No" ) as
Compressed
,decode( bitand(P.flags2, 1 ) , 1, "Enabled" , "Disabled" )
as Auto_Compressed -- Version 12.10 auto compression
from sysmaster:systabnames T, sysmaster:sysptnhdr P
where P.partnum = T.partnum
and
(
bitand(P.flags, '0x08000000') > 0
or bitand(P.flags2, 1 ) > 0 -- Version 12.10 auto compression
);
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/29/2013 01:57:31 PM:
> From: Jacques Renaut/Lenexa/IBM@IBMUS
> To: ids@iiug.org,
> Date: 03/29/2013 01:58 PM
> Subject: Re: Which tables are compressed tables ? [29919]
> Sent by: ids-bounces@iiug.org
>
> Original post:
>
> I need to uncompress all compressed tables in a database. How can i
> list which
> tables are compressed ?
>
> Regards
> Jimmy Jonsson / Plexor AB
>
> Response:
>
> I think what you are looking for is in the sysmaster:syscompdicts or
> sysmaster:syscompdicts_full table. I believe those tables would have1 row
for
> each compressed table/fragment in the system. Onstat -g ppd also prints a
> portion of that, but I think onstat -g ppd only prints the partnum for
the
> currently active tables/fragments, but I think the sysmaster tables
should
> have records for any compressed table/fragments whether or not the table
is
> currently in use. I have not played around with compression a lot soI
didn't
> verify this, but I believe it's what the doc states anyway. Hope this
helps.
>
> Jacques Renaut
> IBM Informix Advanced Support
> APD Team
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>