[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- innodb表空間儲存結構
- db2 建立bufferpool，表空間DB2
- 16、表空間 建立表空間
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- 表空間利用率及表空間的補充
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- DB2建立資料庫，建立表空間DB2資料庫
- KingbaseES的表空間
- Oracle表空間Oracle
- oracle 表空間Oracle
- PostgreSQL 表空間SQL
- PostgreSQL：表空間SQL
- mysql之 表空間傳輸MySql
- UNDO表空間空間回收及切換
- 表空間集自包含檢查
- 每天自動統計表空間的使用情況
- 2.5.5 使用自動Undo管理: 建立 Undo 表空間
- oracle表空間的整理Oracle
- MySQL 中的共享表空間與獨立表空間如何選擇MySql
- SYSAUX表空間清理之SM/OPTSTATUX
- undo表空間容量
- 增加oracle表空間Oracle
- Configure innodb 表空間
- 表空間限額
- 3.2. 表空間
- 只讀表空間
- oracle temp 表空間Oracle
- Ora-01536：超出了表空間users的空間限量
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 臨時表空間和回滾表空間使用率查詢
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- dbms_lob儲存過程導致臨時表空間100%儲存過程
- 獲取表空間DDL
- Innodb：Undo 表空間巨大
- Tablespace表空間刪除
- MySQL InnoDB表空間加密MySql加密