Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


DB2 tablespace storage

Posted on 2010-08-12
Medium Priority
Last Modified: 2012-05-10
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
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
Question by:cmnt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 46

Expert Comment

by:Kent Olsen
ID: 33423252
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?



Author Comment

ID: 33423368
Hi Kdo i have pasted the link below  
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"
LVL 46

Expert Comment

by:Kent Olsen
ID: 33423495
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.


Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 37

Accepted Solution

momi_sabag earned 1000 total points
ID: 33423511
i agree with Kent
probably that guy have a mistake

there is a very simple way of checking this,
create a 4kb page tablespace and create a single table in that tablespaces like this
create table xxx (col1 int, col2 char(3000))
now start inserting rows into that table
each row takes up one page
if you can get more than 16384 (and you will) then that gui has a mistake

Author Comment

ID: 33423887
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
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 total points
ID: 33423916
That will be very close.

DB2 may reserve a couple of them for its own use, but only the first page or two.


Author Comment

ID: 33423993
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
LVL 46

Expert Comment

by:Kent Olsen
ID: 33424010
Glad to help.  (Especially if it helps promote DB2!)


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question