Prev: Upgrade from DB2 LUW 9.5 to 9.7 failed
Next: Cardinality per partition
From: hsn_ on 17 Jun 2010 11:24
when i select data about table compression from table function, i got
correct data:

db2 => select tabname,compress_attr,bytes_saved_percent,rows_sampled
from table(
admin_get_tab_compress_info_v97('ALEXA',NULL,'REPORT')) where
object_type='DATA'


TABNAME
COMPRESS_ATTR
BYTES_SAVED_PERCE
NT ROWS_SAMPLED
--------------------------------------------------------------------------------
------------------------------------------------ -------------
-----------------
-- ------------
DOMAINS
Y
30 1976751
IMPORT
N
0 0
RANKS
Y
14 117041508

when i select from ADMINTABCOMPRESSINFO view i got same good
information too.

but for some reason i didnt get information about compression
alexa.ranks table from system catalog view:

db2 => select tabname,compression,avgrowcompressionratio from
syscat.tables wher
e tabschema='ALEXA'

TABNAME
COMPRESSION
AVGROWCOMPRESSIONRA
TIO
--------------------------------------------------------------------------------
------------------------------------------------ -----------
-------------------
-----
DOMAINS

R +1,50078
E+000
IMPORT

N +0,00000
E+000
RANKS

R +0,00000
E+000
RANKS_TODAY

N -1,00000
E+000

i did runstats on all tables.but still without result. is compression
info in syscat.tables deprecated and no longer updated?
From: Helmut Tessarek on 25 Jun 2010 19:12
I talked to a colleague and here is his answer:

-----
The statistics retrieved from ADMIN_GET_TAB_COMPRESS_INFO are not necessarily
the same or equivalent to what RUNSTATS generates. Let me explain. The stats
that the admin function returns in REPORT mode are compression stats that are
collected only at the time the very first compression dictionary is created.
This information is stored within the table which is very different from
runstats which processes a table and then updates the catalogs accordingly.
Additionally, each invocation of runstats has the possibility of generating a
new set of stats.

Anyway, the correct data to collect is the following (this will show the 'big'
picture and allow a better understanding)

db2 "select card,
npages,
fpages,
stats_time,
pctfree,
compression,
avgrowsize,
avgcompressedrowsize,
avgcompressionratio,
pctrowscompressed,
pctpagessaved
from syscat.tables where ..... "

plus

db2 " select tabname, data_object_p_size, dictionary_size, large_rids from
table(sysproc.admin_get_tab_info('<schema>', '<name>')) "

db2 "select * from
table(sysproc.admin_get_tab_compress_info('<schema>','<tabname.>', '<exec
mode>')) "

Thanks.
-----

Please collect the output of the 3 sql statements mentioned above.

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/
 | 
Pages: 1
Prev: Upgrade from DB2 LUW 9.5 to 9.7 failed
Next: Cardinality per partition