asked on

DB2 tablespace storage

Hi i am new to the DB2 world and i am trying to figure out how DB2 stores data in containers/tablespaces
in all the forums / documnetation from IBM i have read for a 4k pagesize
Page size Row size limit Column count limit Maximum capacity (DMS tbsp)
4 KB 4005 500 64g

also its mentioned that each tablespace can have a maximum of 16,384 pages so when i do the calculation i get this
16,384 * 4 = 65536 KB of data which is = 0.0625 GB
as per math 0.0625 should be the max capactiy of the DMS tablespace and not 64 G
however
16,384*4*1024 = 67108864 Bytes
and 67108864KB = 64 G(this works for 4k,8k,16k and 32k tablespaces also)
i am not sure whats happening here and am really confused am i missing a factor of 1024 somewhere
all help is really appreciated
Thanks
KK

Kent Olsen

Hi cmnt,

Most of that looks correct, except for the maximum page count. 16,384 is woefully small.

DB2 stores data (rows) in blocks. One of the unbending rules is that data does not span blocks so a row MUST be smaller than the block size. Since DB2 uses a few bytes of each block for its own controls a 4K (4096 byte) block can hold, at most, a 4005 byte row.

If the rows are fairly short, the block size may not have much impact on system performance, depending upon how the database is used. But if you have large rows, you should consider increasing the block size. One of the worst configurations possible is to have a row size that is 1/2 the block size. When the DB2 overhead is figured in, a 4K block can hold only 1 2K row so nearly 2K is wasted. Even worse, I/O at the physical level gets you only one row every time you read a block. Doubling the block size (to 8K) gets you 3 rows per I/O. A 16K block gets you 7 rows, etc.

I'm not sure where the 16,384 page count came from. Can you provide a link?

Kent

cmnt

ASKER

Hi Kdo i have pasted the link below
http://www.ibm.com/developerworks/data/library/techarticle/0212wieser/index.html
once you open it up go down 1/4 of the way under tablespace setting you will see a chart from which i did my calculatuions just below the chart its mentioned
"Table spaces are limited to 16384 pages, so choosing a larger page size will increase the capacity of the table space"
Thanks

Kent Olsen

Hi cmnt,

I'm not sure what you're seeing.

About 1/4 the way down is Table 1 in section "Table Space Settings". It clearly states that a database with 16K pages has an upper limit of 256GB.

Kent

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmnt

ASKER

i agree with you kent that's its mentioned 16K pages has an upper limit of 256GB. and also mobi that that guy could have made a mistake
i will try the example you mentioned above
so for a 4 k tablespace the upper limit is 64 gigs that means 67108864 KB , if i divide this no by 4 i get 16,777,216 then does it imply that 16,777,216 is the maximum no of pages for a 4 k tablespace ??
i understand that the # of pages is not a very big deal its just that i want to settle some mathematical confusion

Thanks for your valuable time

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmnt

ASKER

thanks Kent and momi for your prompt response i was really confused with the mathematical calculations i was tryign to do
i guess there was a typo in the IBM article i was referenceing
Thanks

Kent Olsen

Glad to help. (Especially if it helps promote DB2!)

Kent