医院表空间
页大小为 4KB 的 SMS 表空间最大只能是 64GB？
最近买了牛新庄的《循序渐近DB2---DBA系统管理、运维与应用案例》一书，看到P86页时，书上说页大小为 4KB 的 SMS 表空间最大只能是64GB，请问，是这样吗？公司用的就是 DB2 V8.2，目前数据库已经55GB了，那不是马上数据库就不能用了？...最近买了牛新庄的《循序渐近DB2---DBA系统管理、运维与应用案例》一书，
看到P86页时，书上说页大小为 4KB 的 SMS 表空间最大只能是64GB，
请问，是这样吗？
公司用的就是 DB2 V8.2，目前数据库已经55GB了，那不是马上数据库就不能用了？
收起
看到P86页时，书上说页大小为 4KB 的 SMS 表空间最大只能是64GB，
请问，是这样吗？
公司用的就是 DB2 V8.2，目前数据库已经55GB了，那不是马上数据库就不能用了？
收起
查看其它 9 个回答irobot_w的回答
DB2的世界中，表和表空间的大小主要受到pagesize和其对应寻址能力限制。
在DB2 v8中，页地址为3个字节，也就是2的24次方可用，就是16,777,216页可以被寻址，基于这个限制得到如下表空间和表大小的限制：
# of pages Page size Limit of table / tablespace
16,777,216 4 K 64 GB
16,777,216 8 K 128 GB
16,777,216 16 K 256 GB
16,777,216 32 K 512 GB
在DB2 v9中，页地址扩展为4个字节，也就是寻址能力提升4倍，具体的限制如下所示：
# of pages Page size Limit of table / tablespace
536,870,912 4 K 2 TB
536,870,912 8 K 4 TB
536,870,912 16 K 8 TB
536,870,912 32 K 16 TB
注意：在 DB2 v8中，large类型的表空间只是为LOB和LONG数据类型所使用，而在DB2 v9中没有类似的限制，默认的表空间类型就是large，如果从DB2 v8升级到v9就需要手动的把表空间从regular转换为large
ALTER TABLESPACE tablespace_name CONVERT TO LARGE
DB2 v8中的典型报错
多所有容器扩容
db2 " ALTER TABLESPACE tablespace-name EXTEND (ALL 1000000)"
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL1139N The total size of the table space is too big. SQLSTATE=54047
对其中一个容器扩容
db2 " ALTER TABLESPACE tablespace-name EXTEND (FILE '/dir/filename' 3000000)"
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL1139N The total size of the table space is too big. SQLSTATE=54047
加容器
db2 " ALTER TABLESPACE tablespace-name ADD (FILE '/dir/filename' 500000)"
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL1139N The total size of the table space is too big. SQLSTATE=54047
通过检查可以看到
LIST TABLESPACES SHOW DETAIL
...
Tablespace ID = 8
Name = tablespace-name
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 16388000
Useable pages = 16387840
Used pages = 16387840
Free pages = 0
High water mark (pages) = 16387840
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 4
Minimum recovery time =2009-06-26-04.47.15.000000
...
可以明显看到页数量已经接近了最大限制
在DB2 v8中，页地址为3个字节，也就是2的24次方可用，就是16,777,216页可以被寻址，基于这个限制得到如下表空间和表大小的限制：
# of pages Page size Limit of table / tablespace
16,777,216 4 K 64 GB
16,777,216 8 K 128 GB
16,777,216 16 K 256 GB
16,777,216 32 K 512 GB
在DB2 v9中，页地址扩展为4个字节，也就是寻址能力提升4倍，具体的限制如下所示：
# of pages Page size Limit of table / tablespace
536,870,912 4 K 2 TB
536,870,912 8 K 4 TB
536,870,912 16 K 8 TB
536,870,912 32 K 16 TB
注意：在 DB2 v8中，large类型的表空间只是为LOB和LONG数据类型所使用，而在DB2 v9中没有类似的限制，默认的表空间类型就是large，如果从DB2 v8升级到v9就需要手动的把表空间从regular转换为large
ALTER TABLESPACE tablespace_name CONVERT TO LARGE
DB2 v8中的典型报错
多所有容器扩容
db2 " ALTER TABLESPACE tablespace-name EXTEND (ALL 1000000)"
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL1139N The total size of the table space is too big. SQLSTATE=54047
对其中一个容器扩容
db2 " ALTER TABLESPACE tablespace-name EXTEND (FILE '/dir/filename' 3000000)"
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL1139N The total size of the table space is too big. SQLSTATE=54047
加容器
db2 " ALTER TABLESPACE tablespace-name ADD (FILE '/dir/filename' 500000)"
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL1139N The total size of the table space is too big. SQLSTATE=54047
通过检查可以看到
LIST TABLESPACES SHOW DETAIL
...
Tablespace ID = 8
Name = tablespace-name
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 16388000
Useable pages = 16387840
Used pages = 16387840
Free pages = 0
High water mark (pages) = 16387840
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 4
Minimum recovery time =2009-06-26-04.47.15.000000
...
可以明显看到页数量已经接近了最大限制
浏览1267