[DB2]表空間之DMS、自動儲存的DMS表空間
1、建立DMS表空間
使用如下語句建立DMS表空間，建立了兩個“容器”，容器型別是檔案，每個檔案的容量是160個page：
create tablespace TBS_DMS2 \
managed by database \
using(file '1' 160, \
file '2' 160)
檢視錶空間容器：
db2 => list tablespace containers for 6
Tablespace Containers for Tablespace 6
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00003/1
Type = File
Container ID = 1
Name = /home/db2inst1/db2inst1/NODE0000/SQL00003/2
Type = File
建立好之後，即可使用ll –h，檢視到上述兩檔案都已經建立好，且檔案都已佔據了160個頁的大小（640K）。
下面，我們在表空間上建立表：
db2 => create table emp3 \
db2 (cont.) => ( \
db2 (cont.) => Empno DECIMAL(4), \
db2 (cont.) => Ename VARCHAR(7), \
db2 (cont.) => Job VARCHAR(8), \
db2 (cont.) => Mamanger_id DECIMAL(4), \
db2 (cont.) => Hiredate DATE, \
db2 (cont.) => Salary DECIMAL(4), \
db2 (cont.) => Comm DECIMAL(4), \
db2 (cont.) => Department_id DECIMAL(2) \
db2 (cont.) => ) \
db2 (cont.) => in TBS_DMS2
DB20000I The SQL command completed successfully.
db2 => insert into emp3 values (7903,'SMITH','Clerk',7788,'1998-10-23',4500,500,20)
DB20000I The SQL command completed successfully.
db2 => insert into emp3 select * from emp3
DB20000I The SQL command completed successfully.
db2 => insert into emp3 select * from emp3
DB20000I The SQL command completed successfully.
…………………………………………………………………………..
db2 => insert into emp3 select * from emp3
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0289N Unable to allocate new pages in table space "TBS_DMS2".
SQLSTATE=57011
其實我們還能夠繼續插入，縮小插入的行數即可：
db2 => insert into emp3 select * from emp3 fetch first 1000 rows only
DB20000I The SQL command completed successfully.
db2 => insert into emp3 select * from emp3 fetch first 1000 rows only
DB20000I The SQL command completed successfully.
db2 => insert into emp3 select * from emp3 fetch first 1000 rows only
DB20000I The SQL command completed successfully.
此時，空間已滿，我們看下，表空間的詳細情況。使用的頁數已經達到了High water mark，不在允許插入記錄。
db2 => list tablespaces show detail
Tablespace ID = 6
Name = TBS_DMS2
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 320
Useable pages = 256
Used pages = 256
Free pages = 0
High water mark (pages) = 256
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 2
2、建立自動儲存的DMS表空間
DB2 V9版本以上，預設建立的表空間管理方式就是：自動儲存的DMS：
db2 => create tablespace TBS4
DB20000I The SQL command completed successfully.
檢視錶空間的資訊和詳細資訊。可以看到新建的表空間，是放在預設的DEMO目錄下，（由資料庫新建的一個）T0000007的資料夾。
db2 => list tablespace containers show detail
……………………………………………..
Tablespace ID = 7
Name = TBS4
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 96
Free pages = 8064
High water mark (pages) = 96
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
db2 => list tablespace containers for 7 show detail
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/DEMO/T0000007/C0000000.LRG
Type = File
Total pages = 8192
Useable pages = 8160
Accessible = Yes
下面我們來看下，能否自動擴容。首先建立一個表
create table emp4 \
( \
Empno DECIMAL(4), \
Ename VARCHAR(7), \
Job VARCHAR(8), \
Mamanger_id DECIMAL(4), \
Hiredate DATE, \
Salary DECIMAL(4), \
Comm DECIMAL(4), \
Department_id DECIMAL(2) \
) \
in TBS4
接下來，從emp3中選取資料，插入到emp4中。
db2 => insert into emp4 select * from emp3
DB20000I The SQL command completed successfully.
然後再大量擴充表空間。
db2 => insert into emp4 select * from emp4
DB20000I The SQL command completed successfully.
db2 => insert into emp4 select * from emp4
DB20000I The SQL command completed successfully.
db2 => insert into emp4 select * from emp4
DB20000I The SQL command completed successfully.
…………………………………………
起初，表空間的容量如下：
Tablespace ID = 7
Name = TBS4
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 768
Free pages = 7392
High water mark (pages) = 768
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
經過反覆插入資料之後，出現的情形如下：
Tablespace ID = 7
Name = TBS4
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 24576
Useable pages = 24544
Used pages = 23840
Free pages = 704
High water mark (pages) = 23840
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
提示：有時候會出現如下提示，說日誌已滿。
db2 => insert into emp4 select * from emp4
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0964C The transaction log for the database is full. SQLSTATE=57011
解決辦法：使用commit或者減小插入的行數，如下所示：
db2 => insert into emp select * from emp fetch first 150000 rows only
DB20000I The SQL command completed successfully.
也可以直接將log的容量改大：（注意，只有當所有的連線斷開後，才能夠生效）
db2 => update db cfg for demo using LOGFILSIZ 50000
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
猜測：進行DML操作時，應該是先寫日誌，如果出現日誌寫失敗的情況，應該是需要進行回滾，資料不會寫進磁碟。
下面進行測試，插入資料之前，先檢視下emp表中的資料。已經有一百多萬行資料。執行insert ino ..select語句，肯定會報錯。報錯後，再去查詢表中的記錄，發現資料並沒有寫入表中。
db2 => select count(*) from emp
1
-----------
1487400
1 record(s) selected.
db2 => insert into emp select * from emp
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0964C The transaction log for the database is full. SQLSTATE=57011
db2 => select count(*) from emp
1
-----------
1487400
1 record(s) selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14600958/viewspace-669418/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DB2支援的三種表空間SMS、DMS、DMS的自動儲存DB2
- 【儲存管理】表空間概念
- db2表空間DB2
- 【儲存管理】建立永久表空間
- innodb表空間儲存結構
- 表在表空間中的儲存情況
- DB2 檢視錶空間是否自動儲存DB2
- 自動undo表空間模式下切換新的undo表空間模式
- 使用oracle procedure儲存過程自動擴充套件表空間空間tablespace_自動化運維Oracle儲存過程套件運維
- DB2表空間增加DB2
- oracle 修改表空間儲存路徑Oracle
- 表空間管理之bigfile表空間設定
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- linux自動增加表空間Linux
- db2檢視錶空間和增加表空間容量DB2
- Oracle表移動表空間Oracle
- oracle 表移動表空間Oracle
- 經驗分享：DB2 V9下使用重定向（部分表空間使用自動儲存）DB2
- 利用可恢復空間分配技術自動分配表空間
- 查詢表空間是否具備自動擴充套件空間套件
- Linux 自動增加oracle 表空間LinuxOracle
- 管理表空間（表空間的屬性）轉貼
- 分析表空間空閒率並收縮表空間
- db2 建立bufferpool，表空間DB2
- 增加自動擴充套件臨時表空間及改變預設表空間套件
- 在資料庫之間移動表空間資料庫
- Oracle查詢表佔磁碟空間大小及移動表空間Oracle
- 刪除表空間和表空間包含的檔案
- MySQL InnoDB 共享表空間和獨立表空間MySql
- 遷移SYSTEM表空間為本地管理表空間
- MySQL InnoDB 共享表空間和獨立表空間MySql
- UNDO表空間自動調優原則
- 透過xtts遷移單例項檔案系統表空間到RAC ASM儲存表空間TTS單例ASM
- 通過xtts遷移單例項檔案系統表空間到RAC ASM儲存表空間TTS單例ASM
- Oracle的邏輯結構(表空間、段、區間、塊)——表空間Oracle
- DB2學習筆記 - 表空間DB2筆記
- Oracle表空間Oracle
- PostgreSQL：表空間SQL