I moved a table from TS with page size 4K to another TS with page size 16K. For some reason the size of the table changes (table gets smaller). Here is the query I use to calculate the size:

db2 “select substr(t.tabschema,1,10)||’.’||substr(t.tabname,1, 20) as table ,char(date(t.stats_time)) as statsdate ,char(time(t.stats_time)) as statstime ,T.CARD as rows_per_tbl, t.fpages as used_pages ,decimal(float(t.npages)/(1024/ (ts.pagesize/1024)),9,2) as used_mb ,decimal(float(t.fpages)/(1024/(ts.pagesize/1024)),9,2) as allocated_mb from SYSCAT.TABLES T ,SYSCAT.TABLESPACES TS where t.tbspace=ts.tbspace and T.tabname=’BS_AAA‘ and T.TYPE=’T’”

size before move (in TS with 4K page size):
TABLE STATSDATE STATSTIME ROWS_PER_TBL USED_PAGES USED_MB ALLOCATED_MB
-------------------------- ---------- --------- ------------- ----------- ----------- ------------
DBA .BS_AAA 12/15/2009 10:35:57 71182908 16249848 63475.96 63475.96


size after move (in TS with 16K page size):
TABLE STATSDATE STATSTIME ROWS_PER_TBL USED_PAGES USED_MB ALLOCATED_MB
------------------------- ---------- --------- ------------- ------------ ---------- ------------
DBA .BS_AAA 12/14/2009 09:59:17 71182908 3571295 55801.48 55801.48

What is wrong here? Is this the query or something else?
Thanks in advance

Without even looking at your query, my first thought would be that you are fitting more rows on the one 16k page than you could on 4 4k pages. As an example, lets say that you have 2 rows on a 4K page that use up say 3000 bytes, but on a 16K page you can fit 10 rows, which is 2 more rows than you could have fit on 4 4K pages. But taking a quick look at your query results, are your pages used correct? The reason I ask is that 55K of 16K pages is a TON more space than 63K of 4k pages.

Dave

Dave,
Take a look at it closer. The OP has 16,249,848 4K pages and 3,571,295 16K pages.

But you are correct that they are fitting more that 4x rows per page on the 16K pages.

Andy

Quite right. I was pulling those nbrs from the MB used col. And there is about a 13% reduction in pages, which does make sense with earlier statement with a few extra rows fitting per page.
Also, are you using compression, that could help with quite a bit more page savings as well?
Dave

I usually look at npages instead of fpages when trying to estimate the size.

Never mind me... I see npages and used / allocated is the same.

More rows per page, like Dave and Andy said.

if you noticed number of npages = number of fpages here.

so it is all this free space at the end of every page, huh?

thanks everyone.