473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Max table or database size?

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
6 8043
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
*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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: maceo | last post by:
I have a script that will print out the results of a table and make a calculation of a total of one of the columns. See example: <?php /* Database connection */...
0
by: maceo | last post by:
I have some code that extracts the data from a table and performs a calculation (total time) on one of the columns. Here is the code: <?php /* Database connection */
5
by: Anand | last post by:
Hi all Please help me to find out table size in MS-SQL how can I count or identify, this specific table is using some xyz kb of space of my hdd. thanks
36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
3
by: hobbzilla | last post by:
After running: SELECT , B.rows, O.name FROM (select sum(convert(decimal(10,0),dpages)*8129/1024) , id FROM sysindexes group by id) A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)...
3
by: dunleav1 | last post by:
I am trying to write a sql script to estimate size and count of rows of all tables and indexes within a schema. Here's what I have so far but need a little help. #$1 is database name and $2 is...
5
by: Doomster | last post by:
I have Office 2000. I am creating a database that will record stats from football, basketball, and hockey games. One table will contain info about a game. The game could be a football,...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
1
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.