DB2 table space too big problem

[Bing Wu]

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

[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! =-----

[Bing Wu]

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! =-----

[PM \(pm3iinc-nospam\)]

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

[Fan Ruo Xin]

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! =-----

[Mark A]

> > 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.

[Bing Wu]

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.

[Mark A]

"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.

[PM \(pm3iinc-nospam\)]

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

[Blair Adamache]

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.

[PM \(pm3iinc-nospam\)]

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.