Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    76

    Unanswered: DB2 V9.5 Tablespace -pages missing and Total pg's not equal to useable pg's

    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

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  3. #3
    Join Date
    Aug 2008
    Posts
    76

    Great !!

    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
    Last edited by deshaipet; 05-02-09 at 16:00.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Please check this link for more info about pages/extents:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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)

  6. #6
    Join Date
    Aug 2008
    Posts
    76

    Great help.....

    Hi Bella -

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


    Thanks again
    pandit

Posting Permissions

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