DB2表空间太大问题 [英] DB2 table space too big problem

问题描述

嗨伙计，


创建大表空间时遇到问题。它报告错误：

SQL1139N表空间的总大小太大


说明：当前表空间的大小太大。 REGULAR表空间的大小限制为0xFFFFFF（16777215）页，而TEMPORARY / LONG表空间的大小限制为2 tera字节（2 TB）。


用户响应：检查诊断日志文件db2diag.log以获取详细信息。减小表空间的大小并更正SQL语句。

表空间是REGULAR，页面大小为4KB。我试图创建一个67GB的表空间。看来这已经超出了尺寸限制。我可以使用32KB缓冲池，但仍有一个限制远远低于我预期的几TB（而TEMPORARY / LONG表空间也有2TB限制）。这是DB2限制吗？请指教。


提前致谢。


Bing

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

推荐答案

Bing Wu写道：

Bing Wu wrote:

嗨伙计，

创建大表空间时遇到问题。它报错：

SQL1139N表空间的总大小太大

说明：当前表空间的大小太大。 REGULAR表空间的大小限制为0xFFFFFF（16777215）页，而TEMPORARY / LONG表空间的大小限制为2 tera字节（2 TB）。
减小表空间的大小并更正SQL语句。

表空间是常规的4KB页面。我试图创建一个67GB的桌面空间。看来这已经超出了尺寸限制。我可以使用
32KB缓冲池，但仍有一个限制远远低于我预期的几TB（而TEMPORARY / LONG表空间有2TB的限制为
）。这是DB2限制吗？请提供建议。

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.

这在SQL Reference，第1卷，附录A - SQL

限制中有记录。


4k 8k 16k 32k

---- ---- ---- ----

常规的最大尺寸DMS

表空间（千兆字节）64 128 256 512

长DMS表空间的最大大小（以兆兆字节为单位）：2

限制是*每个分区*，所以如果你需要超过这些限制

你将需要添加数据分区功能。

祝你好运，



----- =通过Newsfeeds.Com发布，未经审查的Usenet新闻= -----
http://www.newsfeeds.com - 世界排名第一的新闻组服务！

---- - ==超过100,000个新闻组 - 19个不同的服务器！ = -----

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

您好Ian，


非常感谢您提供的信息。还有一个问题是表空间。我有一个小表（每行20个字节），有大量的行（十亿）。在性能方面，哪个更适合表：更少分区中的长DMS表空间或更多分区中的常规表空间？


再次感谢，


Bing

Ian写道：

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:

这在SQL Reference，Volume 1，附录A - SQL
Limits中有记载。 ;。

4k 8k 16k 32k
---- ---- ---- ---- ----
常规DMS表空间的最大尺寸（千兆字节）64 128 256 512

长DMS表空间的最大大小（以兆兆字节为单位）：2

限制为*每个分区*，所以如果你需要超出这些限制
你需要添加数据分区功能。

祝你好运，

----- =通过新闻源发布.Com，Uncensored Usenet News = -----
http://www.newsfeed s.com - 世界排名第一的新闻组服务！
----- ==超过100,000个新闻组--19个不同的服务器！ = -----

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

您是否尝试使用页数或内容创建容器

喜欢x MB或x K？

Db2diag.log包含更多关于

diaglevel设置为4时可用尺寸和尺寸的信息。


要获得更精确的帮助，您应该提供db2level

命令的输出。 （db2版本，平台，......）


短信或DMS？


Ex：
http://publib.boulder.ibm.com/infoce...help/index。 jsp db2 v8 sp4

Sql限制

常规DMS表空间的最大大小（以千兆字节为单位）cg 512


长DMS表空间的最大大小（以兆兆字节计）c 2

临时DMS表空间的最大大小（以兆兆字节为单位）c 2


c显示的数字是架构限制和近似值。

实际限制可能更少。

g有关页面大小的特定值，请参阅表12.


与4k页面尺寸，您可以达到表中指定的64 gig限制

12.


您知道平均行数吗？

对于4 k页，您有16777215到4278189825行的空间，具体取决于

的行大小。


PM


Bing Wu <双** @ biop.ox.ac.uk>一个écritdansle message de

news：bq ********** @ news.ox.ac.uk ...

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，

创建大表空间时遇到问题。它报错：

SQL1139N表空间的总大小太大

说明：当前表空间的大小太大。
REGULAR表空间的大小限制为0xFFFFFF（16777215）页，而TEMPORARY / LONG表空间的大小

限制为2 tera字节（2 TB）。用户响应：检查诊断日志文件db2diag.log以获取详细信息。
减小表空间的大小并更正SQL语句。

表空间是REGULAR，页面大小为4KB。我试图创建一个67GB
的表空间。看来这已经超出了尺寸限制。我可以使用

32KB缓冲池，但仍有一个限制远远低于我的预期值

几TB（而TEMPORARY / LONG表空间有2TB限制）以及）。这是否为DB2限制？
请指教。
提前致谢。

Bing

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

这篇关于DB2表空间太大问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！