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/，如需轉載，請註明出處，否則將追究法律責任。