NPAGES, FPAGES question

db2udbgirl · #1 (**permalink**) 08-13-07, 23:05

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 ?

Marcus_A · #2 (**permalink**) 08-13-07, 23:27

I would expect that FPAGES >= NPAGES. After a reorg, then FPAGES = NPAGES.

stolze · #3 (**permalink**) 08-14-07, 06:38

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.

grofaty · #4 (**permalink**) 08-14-07, 06:53

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

stolze · #5 (**permalink**) 08-15-07, 11:21

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech