Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: posted by mistake.sorry

    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
    Last edited by MarkhamDBA; 12-15-09 at 12:06.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I usually look at npages instead of fpages when trying to estimate the size.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Never mind me... I see npages and used / allocated is the same.

    More rows per page, like Dave and Andy said.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •