Hi friends -

I created a automatic storage tablespace with a pagesize of 16 K and initial size of 64 MB.

create large tablespace test_tbsp1 pagesize 16k managed by automatic storage autoresize yes initialsize 64 M increasesize 64 M maxsize 2 G no extent size 16 prefetchsize 16 bufferpool test_bp1 file system caching dropped table recovery on

so, the number of pages should be (initialsize/pagesize)= (64 MB /16 K) = 4096 pages

========

$ db2pd -db SMPT1 -tablespace 3

Database Partition 0 -- Database SMPT1 -- Active -- Up 0 days 04:48:13

Tablespace 3 Configuration:
Address Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x070000006E46AAA0 DMS Large 16384 16 No 16 2 2 Off 9 0 15 TEST_TBSP1

Tablespace 3 Statistics:
Address TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM State MinRecTime NQuiescers
0x070000006E46AAA0 4032 3888 48 0 3840 48 0x00000000 0 0

Tablespace 3 Autoresize Statistics:
Address AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x070000006E46AAA0 Yes Yes 0 0 No 0 None No

Containers:
Address ContainNum Type TotalPgs UseablePgs StripeSet Container
0x070000006E46B500 0 File 448 432 0 /db2/databases/SMPT1/SPTH7/inst4/NODE0000/SMPT1/T0000003/C0000000.LRG
0x070000006E46B660 1 File 448 432 0 /db2/databases/SMPT1/SPTH8/inst4/NODE0000/SMPT1/T0000003/C0000001.LRG
0x070000006E46B7C0 2 File 448 432 0 /db2/databases/SMPT1/SPTH9/inst4/NODE0000/SMPT1/T0000003/C0000002.LRG
0x070000006E46B920 3 File 448 432 0 /db2/databases/SMPT1/SPTH5/inst4/NODE0000/SMPT1/T0000003/C0000003.LRG
0x070000006E46BA80 4 File 448 432 0 /db2/databases/SMPT1/SPTH1/inst4/NODE0000/SMPT1/T0000003/C0000004.LRG
0x070000006E46BBE0 5 File 448 432 0 /db2/databases/SMPT1/SPTH2/inst4/NODE0000/SMPT1/T0000003/C0000005.LRG
0x070000006E46BD40 6 File 448 432 0 /db2/databases/SMPT1/SPTH3/inst4/NODE0000/SMPT1/T0000003/C0000006.LRG
0x070000006E46BEA0 7 File 448 432 0 /db2/databases/SMPT1/SPTH4/inst4/NODE0000/SMPT1/T0000003/C0000007.LRG
0x070000006E46C000 8 File 448 432 0 /db2/databases/SMPT1/SPTH6/inst4/NODE0000/SMPT1/T0000003/C0000008.LRG

========


db2 list tablespaces show detail

Tablespace ID = 3
Name = TEST_TBSP1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4032
Useable pages = 3888
Used pages = 48
Free pages = 3840
High water mark (pages) = 48
Page size (bytes) = 16384
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 9

=======

From the above command ouput it is clear that the total number of pages is 4032.

Useable pages are only 3888


Questions :

1) Why are the total number of pages not equal to 4096 ?

2) Why are the total pages not equal to useable pages ?


...


Thanks
Pandith

I just posted on developerworks:
developerWorks : Information Management : DB2 for Linux, UNIX, and Windows : DB2 V9.5 Tablespace -pages missing and ...


The database was created using 9 storage paths so your tablespace has 9 containers.

Data is allocated by extents
9 (containers) X 16 (extent size) = 144

4096 / 144 = 28.44 = 28 (rounding down)

28 * 144 = 4032

This is where 4032 is coming from.



useable pages = total pages - some overhead. They will never be the same for DMS tablespaces

Hi Bella -

Thanks a lot Bella.. it was of great help.. Can you please explain it in more detail for understanding ?? Sorry for the trouble....


Thanks
Pandith

Please check this link for more info about pages/extents:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Actually, take a look at this one first:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

The previous one has a link to it as well.


Basically, in a DMS tablespace, the following # of extents are reserved/overhead:
- 1 extent per container plus
- 3 extents per tablespace

So, in your tablespace with 9 containers, the overhead is 12 extents

useable pages = total pages - ((1 extent X # of containers) + any partial extents)
free pages = useable pages - 3 extents


I believe DB2 wants all containers to have the same size (for performance reasons) and therefore total pages is 4032 instead of 4096. As you can see from the db2pd output, TotalPgs is 448 per container (or 28 extents). If you add one extent to each container, then total pages would be 4176 which exceeds the initial size you specified (4096)

Hi Bella -

Great !!! thanks a lot for all your help. Now, it is very clear to me.


Thanks again
pandit