Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: NPAGES, FPAGES question

    Hello, Can you pls help me to understand the difference between NPAGES and FPAGES parameter present in syscat.tables.

    Info.Center says the following info
    NPAGES - Total number of pages on which the rows of the table exist
    FPAGES - Total number of pages

    Does it mean FPAGES is equal to the total # of allocated pages for a table while NPAGES refer to # of used pages for a table ?

    Also does FPAGES > NPAGES always ?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would expect that FPAGES >= NPAGES. After a reorg, then FPAGES = NPAGES.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    One could have pages associated with a table that do not contain any rows (e.g. after a DELETE operation). Those pages are counted by FPAGES but not NPAGES. Here is a way to test that:
    Code:
    $ db2 "create table a ( a int )"
    $ db2 "begin atomic declare i int; set i = 0; while ( i < 100000 ) do set i = i + 1; insert into a values(i); end while; end"
    $ db2 "runstats on table stolze.a"
    $ db2 reorg table a
    $ db2 "runstats on table stolze.a"
    $ db2 "select fpages, npages from sysstat. tables where tabname = 'A'"
    
    FPAGES               NPAGES
    -------------------- --------------------
                     374                  374
    
      1 record(s) selected.
    
    $ db2 "delete from a"
    $ db2 "runstats on table stolze.a"
    $ db2 "select fpages, npages from sysstat. tables where tabname = 'A'"
    
    FPAGES               NPAGES
    -------------------- --------------------
                     374                    0
    
      1 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Stolce,
    why is there runstats before and after reorg? I always do runstats only after reorg.
    Code:
    $ db2 "runstats on table stolze.a"
    $ db2 reorg table a
    $ db2 "runstats on table stolze.a"
    What does FPAGES=374 and NPAGES=0 means? Pages are created but are empty (without data). So disk space is occupied.
    Thanks,
    Grofaty

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The first runstats is not necessary. Also, the runstats is completely unrelated to the reorg here. I did the runstats to update the statistics - not to reorganize the table data (which I didn't care about). However, I fiddled around with it a bit more and that messed up my statistics.

    The FPAGES=374 means that there are 374 pages allocated for the table. And NPAGES=0 means that 0 pages allocated for the table contain data. DB2 did not allocate pages just for the fun of it. I had 100,000 INSERT statements. But I run a DELETE to remove all 100000 rows, so no data is in the table any longer.

    What you have to know is that DB2 allocates pages for a table when it is needed, i.e. when all existing pages don't have enough room to accommodate a new or changed row. However, when a page becomes empty, it is not deallocated/deassociated from the table. That would be just an enourmous waste of resources in most scenarios. Therefore, you have to run REORG to reclaim this space.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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