Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2008

Tip: Looking for answers? Try searching our database.

Row compression in a table...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Damir - 03 Apr 2008 09:42 GMT
Greetings!
I am trying to compress a table in my database, but for some reason the
compression doesn't work.

Here are the executed commands and their results:

>db2 alter table DB2ADMIN.TR_GEN_INSTR_ARH compress yes
DB20000I  The SQL command completed successfully.

>db2 reorg table DB2ADMIN.TR_GEN_INSTR_ARH resetdictionary
DB20000I  The REORG command completed successfully.

>db2 "SELECT avgrowsize, avgcompressedrowsize,
>pctpagessaved,avgrowcompressionratio, pctrowscompressed  FROM SYSCAT.TABLES
>WHERE tabschema = 'DB2ADMIN' and tabname='TR_GEN_INSTR_ARH'"

AVGROWSIZE AVGCOMPRESSEDROWSIZE PCTPAGESSAVED AVGROWCOMPRESSIONRATIO
PCTROWSCOMPRESSED
---------- -------------------- ------------- ------------------------ ------------------------
      223                    0             0            +0,00000E+000
+0,00000E+000

The table itself is range partitioned (by the date column "dat_val")
(allthough this shouldn't affect the row compression), and there are
alltogether five partitions:
DATAPARTITIONNAME     DATAPARTITIONID     PARTITIONOBJECTID
TR_GEN_INSTR_2002         0                                       4
TR_GEN_INSTR_2003         1                                       5
TR_GEN_INSTR_2004         2                                       6
TR_GEN_INSTR_2005         3                                       7
TR_GEN_INSTR_2006         4                                       8

The INSPECT ROWCOMPESTIMATE command shows something rather strange:
-all is fine for the above five partitions, with space savings estimated
around 80%, but then the command output mentions another three table objects
with IDs 9,10 and 11 for which it says that "No dictionary can be built as
row compression is not supported for this table". These objects don't exist,
nor do I see them in the SYSCAT.DATAPARTITIONS view...

What is happening here??

Thanks for any help,
   Damir
Damir - 03 Apr 2008 09:48 GMT
OK, please forget this post, I just forgot to run the runstats utility to
refresh the statistics on the table after performing the compression :-)

Regards,
   Damir
Knut Stolze - 03 Apr 2008 10:13 GMT
> Greetings!
> I am trying to compress a table in my database, but for some reason the
[quoted text clipped - 7 lines]
>>db2 reorg table DB2ADMIN.TR_GEN_INSTR_ARH resetdictionary
> DB20000I  The REORG command completed successfully.

I would have expected a RUNSTATS step here.  Otherwise, your statistics
wouldn't be correct anyways.

>>db2 "SELECT avgrowsize, avgcompressedrowsize,
>>pctpagessaved,avgrowcompressionratio, pctrowscompressed  FROM
[quoted text clipped - 6 lines]
>        223                    0             0            +0,00000E+000
> +0,00000E+000

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.