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
10  9907 
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! =-----
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! =-----
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.a c.uk> a écrit dans le message de
news:bq******** **@news.ox.ac.u k... 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
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! =-----
> > 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.
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.
"Bing Wu" <bi**@biop.ox.a c.uk> wrote in message
news:bq******** **@news.ox.ac.u k... 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.
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
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 am placing a transparent gif
over the top of a normal gif) then the width of my table is large enough
to accommodate both images side by side
My browsers (both IE ad Firefox) do not appear take into account the
relative positioning of my...
|
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:
|
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, the space between the first and second columns changes like
this: Address: <space> 222 North B Street. You can see this at
www.usernomics.com/ergonomic-products-keyboards.html .
|
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. When
I create a new table (did not specify on which tablespace), why the
table not created on the userspace1 but instead on tmpxx? what is the
rule to create a table on tablespace without specify in which
tablespace
if I have both tablespace...
|
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 "TEMPSPACE1".". Sometimes some rows are
displayed.
We tried all the hints of the Docs but it won't work.
Can anybody give me a hint in which way this problem should be analyzed?
I'm sorry ti bother You, but this is the last idea i've had.
| | |
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 an issue) I'm using to
create the table is...
<table align="center" cellpadding="5" width="650" border="1">
<tr>
<td width="100">
|
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 space. I want to remove this space. How can I solve?
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
|
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 create temp table without mentioning table space where the structure of the temp table stored and when we are trying to insert data into it which table space it will use....?
I faced one problem earlier that..I can describe the table but when iam...
|
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 blank space befor and after small table
I want small table appear in top of space of large table
and blank space is appear in bottom of small table inside large table
what do I have to do?
thanks alot
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
