DB2数据库中表空间的大小限制

数据库
DB2数据库中,表和表空间的大小主要受到pagesize和其对应寻址能力限制。本文将为您详细分析DB2数据库中表和表空间的大小的限制,供您参考,希望对您有所帮助。

DB2数据库中,表和表空间的大小主要受到pagesize和其对应寻址能力限制。本文将为您详细分析DB2数据库中表和表空间的大小的限制,供您参考,希望对您有所帮助。

在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中的典型报错#p#

多所有容器扩容

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
 
通过检查可以看到#p#

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

...

可以明显看到页数量已经接近了最大限制
 

责任编辑:段燃 来源: ITPUB
相关推荐

2010-11-03 11:26:39

DB2表空间

2010-09-01 09:13:29

DB2表空间

2010-09-01 14:46:16

DB2表空间

2010-11-01 11:45:06

DB2管理页大小

2010-08-18 09:18:10

DB2求剩余数据库空间

2011-03-11 16:02:03

DB2数据库安装

2010-11-03 16:32:10

DB2创建数据库

2010-11-01 13:45:16

DB2数据库的优势

2010-08-31 17:34:46

DB2

2010-08-25 10:50:48

DB2数据库

2010-07-27 09:45:35

DB2数据库设计

2010-09-01 13:38:41

DB2数据复制

2010-08-26 16:15:25

DB2数据库管理

2010-09-30 11:49:21

DB2数据库权限

2010-11-03 16:21:18

DB2数据库授权

2010-11-01 11:30:41

DB2数据库权限

2010-07-28 10:13:06

DB2查询Table

2009-04-01 14:07:44

表空间备份恢复

2015-10-29 15:06:36

DB2重定向恢复数据

2009-07-06 17:34:26

远程复制DB2
点赞
收藏

51CTO技术栈公众号