程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2數據庫中表空間的大小限制

DB2數據庫中表空間的大小限制

編輯: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中的典型報錯

多所有容器擴容

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

...

可以明顯看到頁數量已經接近了最大限制

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved