DB2數(shù)據(jù)庫中，表和表空間的大小主要受到pagesize和其對應尋址能力限制。本文將為您詳細分析DB2數(shù)據(jù)庫中表和表空間的大小的限制，供您參考，希望對您有所幫助。

在DB2 v8中，頁地址為3個字節(jié)，也就是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個字節(jié)，也就是尋址能力提升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數(shù)據(jù)類型所使用，而在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

...

可以明顯看到頁數(shù)量已經(jīng)接近了最大限制