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