By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

Max table or database size?

P: n/a
Hi,

We're running DB2 v8.1 on a windows platform and have a database that
is quite large. It basically contains one table with a BLOB field
(each blob is a zip file, maybe 500K to 1MB), and this particular
table is close to 300GB in size. We are not experiencing any
problems, and there is plenty of space on the server, but I was just
wondering if there is limit in DB2 to how big this table can get.
We are guessing it is going to probably top out at around 500GB and
won't grow much larger than that.
Oct 29 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Oct 29, 4:48*pm, shawno <stjacq...@gmail.comwrote:
Hi,

We're running DB2 v8.1 on a windows platform and have a database that
is quite large. *It basically contains one table with a BLOB field
(each blob is a zip file, maybe 500K to 1MB), and this particular
table is close to 300GB in size. *We are not experiencing any
problems, and there is plenty of space on the server, but I was just
wondering if there is limit in DB2 to how big this table can get.
We are guessing it is going to probably top out at around 500GB and
won't grow much larger than that.
how much is the page size of your table spaces ? is your database
partitioned ?
Oct 29 '08 #2

P: n/a
*how much is the page size of your table spaces ? is your database
partitioned *?
Under the 'table space' tab of the 'alter table space' window in
control center, the page size is 4K. Under the 'performance' tab, the
'prefetch automatic' checkbox is selected. This is an SMS database.

Oct 29 '08 #3

P: n/a
On Oct 29, 8:12*am, Saurabh...@gmail.com wrote:
*how much is the page size of your table spaces ? is your database
partitioned *?
Oops, forgot to answer your other question. No, it is not
partitioned.

Oct 30 '08 #4

P: n/a
On Oct 30, 7:02*am, shawno <stjacq...@gmail.comwrote:
On Oct 29, 8:12*am, Saurabh...@gmail.com wrote:
*how much is the page size of your table spaces ? is your database
partitioned *?

Oops, forgot to answer your other question. *No, it is not
partitioned.
Bump, anyone have any input on this one? As you may have guessed, I'm
a developer on a small team and have had to play the role of DBA, even
though I know little about DB2. Thanks.
Nov 12 '08 #5

P: n/a
On Nov 12, 2:03*pm, shawno <stjacq...@gmail.comwrote:
On Oct 30, 7:02*am, shawno <stjacq...@gmail.comwrote:
On Oct 29, 8:12*am, Saurabh...@gmail.com wrote:
*how much is the page size of your table spaces ? is your database
partitioned *?
Oops, forgot to answer your other question. *No, it is not
partitioned.

Bump, anyone have any input on this one? *As you may have guessed, I'm
a developer on a small team and have had to play the role of DBA, even
though I know little about DB2. *Thanks.
You say you have 300GB in a v8.1 database at 4k page size? I thought
the limit for 4k page sizes was 64GB, then 128GB for 8k pages ...
512GB for 32k pages.

Try to upgrade to DB2 V9.1 or V9.5, it has a new tablespace type
(large tablespaces) where you will practically hit no limit.

AFAIK the limit is only the tablespace, not the table size.
Nov 14 '08 #6

P: n/a
You say you are at V8.1, what Fix Pack? Do a db2level command and you
will get the answer.
If you are at V8.1.9 which is V82.2, don't ask!) then you are fine.
Maximum size is for a table because each row needs to be adressed by
an index.

At V8.1.7 or lower, your tablesize limit with a 4k page is 64GB. This
is due to the Row Identifier RID size in an index which takes 4 bytes:
1 b for the row number and 3b for the page no. This FF-FFFFFF, doing
the math gives 64GB.
At V8.2.2 or V8.1.9 DB2 introduces the possibility of putting both
data and indexes in a LARGE tablespace. This gives you large RIDs of 6
bytes: 2b for the row number and 4bytes for the page no. which then
gives you much larger capacity. Approx. 2.8 terabytes for each table.

I just noticed that you tblspace is SMS so th e above does not apply.
You should build the table in a DMS LARGE tablespace and use another
large tablespace for the index if possible. If not put both in the
same large dms tablespace but performance may not be as good as you
would like.

These are table limits for size and index limits for rids. Given that
you are palnning for 300Gb with a possibility to go to 500GB, you
should start this the right way now and not pay the price of redoing
the work later.
Regards, Pierre.
On Nov 14, 4:06*pm, w.l.fisc...@googlemail.com wrote:
On Nov 12, 2:03*pm, shawno <stjacq...@gmail.comwrote:
On Oct 30, 7:02*am, shawno <stjacq...@gmail.comwrote:
On Oct 29, 8:12*am, Saurabh...@gmail.com wrote:
*how much is the page size of your table spaces ? is your database
partitioned *?
Oops, forgot to answer your other question. *No, it is not
partitioned.
Bump, anyone have any input on this one? *As you may have guessed, I'm
a developer on a small team and have had to play the role of DBA, even
though I know little about DB2. *Thanks.

You say you have 300GB in a v8.1 database at 4k page size? I thought
the limit for 4k page sizes was 64GB, then 128GB for 8k pages ...
512GB for 32k pages.

Try to upgrade to DB2 V9.1 or V9.5, it has a new tablespace type
(large tablespaces) where you will practically hit no limit.

AFAIK the limit is only the tablespace, not the table size.
Nov 16 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.