473,320 Members | 1,699 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.

DB2 table space too big problem

Hi Folks,

I have a problem while creating a big table space. It reports error:
SQL1139N The total size of the table space is too big

Explanation: The size of the current table space is too big. The size of a REGULAR table space is limited to 0xFFFFFF (16777215) pages while the size of a TEMPORARY/LONG table space is limited to 2 tera bytes (2 TB).

User Response: Check the diagnostic log file db2diag.log for details. Reduce the size of the table space and correct the SQL statement.
The table space is REGULAR with 4KB page. And I tried to create a 67GB table space. It seems this already exceeds the size limit. I can use the 32KB buffer pool but there is still a limit which is far below my expected a few TB (while a TEMPORARY/LONG table space has a 2TB limit as well). Is this the DB2 limit? Please advice.

Thanks in advance.

Bing

Nov 12 '05 #1
10 9816
Ian
Bing Wu wrote:
Hi Folks,

I have a problem while creating a big table space. It reports error:
SQL1139N The total size of the table space is too big

Explanation: The size of the current table space is too big. The size of
a REGULAR table space is limited to 0xFFFFFF (16777215) pages while the
size of a TEMPORARY/LONG table space is limited to 2 tera bytes (2 TB).

User Response: Check the diagnostic log file db2diag.log for details.
Reduce the size of the table space and correct the SQL statement.

The table space is REGULAR with 4KB page. And I tried to create a 67GB
table space. It seems this already exceeds the size limit. I can use the
32KB buffer pool but there is still a limit which is far below my
expected a few TB (while a TEMPORARY/LONG table space has a 2TB limit as
well). Is this the DB2 limit? Please advice.


This is documented in the SQL Reference, Volume 1, Appendix A - "SQL
Limits".

4k 8k 16k 32k
---- ---- ---- ----
Maximum size of a regular DMS
table space (in gigabytes) 64 128 256 512

Maximum size of a long DMS table space (in terabytes): 2
The limits are *per partition*, so if you need to exceed these limits
you will need to add the data partitioning feature.
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #2
Hi Ian,

Many thanks for your information. One more question re table space. I have a small table (20 bytes per row) with large amount of rows (in billions). In terms of performance, which is better suited for the table: long DMS table spaces in fewer partitions or regular ones in more partitions?

Thanks again,

Bing
Ian wrote:

This is documented in the SQL Reference, Volume 1, Appendix A - "SQL
Limits".

4k 8k 16k 32k
---- ---- ---- ----
Maximum size of a regular DMS
table space (in gigabytes) 64 128 256 512

Maximum size of a long DMS table space (in terabytes): 2
The limits are *per partition*, so if you need to exceed these limits
you will need to add the data partitioning feature.
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----


Nov 12 '05 #3
Did you try to create the container with the number of pages or something
like x MB or x K?
Db2diag.log contains more info on the size available and size requested when
diaglevel is set to 4.

To get more precise help, you should provide the output of the db2level
command. (db2 version, platform, ...)

SMS or DMS?

Ex :
http://publib.boulder.ibm.com/infoce...help/index.jsp db2 v8 sp4
Sql Limits
Maximum size of a regular DMS table space (in gigabytes) c g 512

Maximum size of a long DMS table space (in terabytes) c 2
Maximum size of a temporary DMS table space (in terabytes) c 2

c The numbers shown are architectural limits and approximations. The
practical limits may be less.
g For page size-specific values, see Table 12.

WIth the 4k page size, you may reach the 64 gig limit specified in the table
12.

Do you know the average row size?
WIth 4 k pages, you have room for 16777215 to 4278189825 rows depending on
the row size.

PM

"Bing Wu" <bi**@biop.ox.ac.uk> a écrit dans le message de
news:bq**********@news.ox.ac.uk...
Hi Folks,

I have a problem while creating a big table space. It reports error:
SQL1139N The total size of the table space is too big

Explanation: The size of the current table space is too big. The size of a REGULAR table space is limited to 0xFFFFFF (16777215) pages while the size
of a TEMPORARY/LONG table space is limited to 2 tera bytes (2 TB).
User Response: Check the diagnostic log file db2diag.log for details. Reduce the size of the table space and correct the SQL statement.

The table space is REGULAR with 4KB page. And I tried to create a 67GB table space. It seems this already exceeds the size limit. I can use the
32KB buffer pool but there is still a limit which is far below my expected a
few TB (while a TEMPORARY/LONG table space has a 2TB limit as well). Is this
the DB2 limit? Please advice.
Thanks in advance.

Bing

Nov 12 '05 #4
h

Bing Wu wrote:
Hi Ian,

Many thanks for your information. One more question re table space. I have a small table (20 bytes per row) with large amount of rows (in billions). In terms of performance, which is better suited for the table: long DMS table spaces in fewer partitions or regular ones in more partitions?
The most important thing is the table size. For example, if the table have 1 billion rows, and the rowsize is 20bytes, then the table size is only 20G. In such case, you may win no more benefit by partition this table.
BTW, large tablespace can only hold LOBs data or index, not data.



Thanks again,

Bing

Ian wrote:

This is documented in the SQL Reference, Volume 1, Appendix A - "SQL
Limits".

4k 8k 16k 32k
---- ---- ---- ----
Maximum size of a regular DMS
table space (in gigabytes) 64 128 256 512

Maximum size of a long DMS table space (in terabytes): 2
The limits are *per partition*, so if you need to exceed these limits
you will need to add the data partitioning feature.
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----


Nov 12 '05 #5
> > Hi Ian,

Many thanks for your information. One more question re table space. I
have a small table (20 bytes per row) with large amount of rows (in
billions). In terms of performance, which is better suited for the table:
long DMS table spaces in fewer partitions or regular ones in more
partitions?

You should usually have at least 1 processor for each partition for optimum
performance.
Nov 12 '05 #6
Thanks for comments. Does this mean if I have a 2TB data table, I need 31 partitations and 31 processors to run the database?

Thanks,

Bing

Mark A wrote:

You should usually have at least 1 processor for each partition for optimum
performance.


Nov 12 '05 #7
"Bing Wu" <bi**@biop.ox.ac.uk> wrote in message
news:bq**********@news.ox.ac.uk...
Thanks for comments. Does this mean if I have a 2TB data table, I need 31 partitations and 31 processors to run the database?
Thanks,

Bing

Mark A wrote:

You should usually have at least 1 processor for each partition for optimum performance.

Sorry, I forgot that you needed the partitions because of table size. You
can have multiple partitions for each processor--no problem. It's just that
for "optimum" performance in a parallel environment, it helps to have one
processor per partition.
Nov 12 '05 #8
Is union all/partitionning view an option for 2tb?

If most 'partitions' (tables) are pretty static in nature. (except the last
one that you append to.)
20 bytes rows and 2 tb looks like dw/dss stuff..

PM
Nov 12 '05 #9
The maximum table size (with 32 k pages) is half a terrabyte - so a 2 TB
table (excluding LOBs/long fields) would require 4 partitions.

Bing Wu wrote:
Thanks for comments. Does this mean if I have a 2TB data table, I need
31 partitations and 31 processors to run the database?

Thanks,

Bing

Mark A wrote:

You should usually have at least 1 processor for each partition for
optimum
performance.


Nov 12 '05 #10
But with max 255 rows of 20 bytes (5100) per 32k page that would waste lots
of space.
(approx.)

Additional info, reminder :
For more accurate calculations, see
http://publib.boulder.ibm.com/infoce...help/index.jsp
Space requirements for user table data

PM
"Blair Adamache" <ba*******@2muchspam.yahoo.com> a écrit dans le message de
news:bq**********@hanover.torolab.ibm.com...
The maximum table size (with 32 k pages) is half a terrabyte - so a 2 TB
table (excluding LOBs/long fields) would require 4 partitions.

Bing Wu wrote:
Thanks for comments. Does this mean if I have a 2TB data table, I need
31 partitations and 31 processors to run the database?

Thanks,

Bing

Mark A wrote:

You should usually have at least 1 processor for each partition for
optimum
performance.

Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Stephen Weatherly | last post by:
Could anyone please help me with a problem I am having with my table widths??? If I have 2 images within a td tag, but using CSS relative positioning I position one over the top of the second (I...
7
by: Richard Lawrence | last post by:
Hi, Consider the following: #Content { margin:0px 210px 50px 200px; padding:10px; } If I use it with the following HTML:
12
by: Bob | last post by:
Hello Everyone, I have a series of two column tables that is causing me a problem. The space between column 1 and 2 seems to vary by a small amount from one table to the next. For example,...
1
by: xixi | last post by:
There is one thing I don't understand, I have default tablespace userspace1 is database managed type, with total freepages 506048. another one called tmpxx is DMS type too, with 525120 free pages....
2
by: Oliver Stratmann | last post by:
Hello All! I've got a problem with our DB2/NT 8.1.0 Database. The following SELECT on a big Table (2,5 Million rows) finishes with the Error "Unable to allocate new pages in table space...
8
by: chrisoftoday | last post by:
Hi, I'm having trouble with some white space that's appearing in my table. I can't seem to spot the problem, it'd be great if someone could help. The HTML code (ignore the PHP, it shouldn't be...
2
ramprabu
by: ramprabu | last post by:
Hello, I will give the sample code of html. Here first table only apply border 1 width. other tables are border 0. The problem is border=0 means border was not visible but it takes white border...
1
by: ellenraju | last post by:
When we create an object in oracle we can specify table space, if not it will take default table space, while creating global temporary table how can we specify the table space...and even if we...
1
oranoos3000
by: oranoos3000 | last post by:
hi I have a table with large height and inside of this large table i have only one table with small height in show small height table is shown in middle of space of large table and have...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
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: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.