DB2 SMS tablespace 容器大小不等测试

看到官方文档有这句：

引用

When all space in a single container in an SMS table space is allocated to tables, the table space is considered full, even if space remains in other containers。
The SMS table space is full as soon as any one of its containers is full. Thus, it is important to have the same amount of space available to each container.

就是说，无论SMS有多少个容器，只有其中一个满了，就这个表空间都满了

测试：

--建立两个大小不一的文件系统
[root@localhost mnt]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/tmp/test1_s.img      4.9M   22K  4.6M   1% /mnt/test1_s
/tmp/test1_big.img    9.7M  1.1M  8.1M  12% /mnt/test1_big

--建立SMS表空间
db2 => create tablespace SMSFULLTEST managed by system USING ('/mnt/test1_s/','/mnt/test1_big/') 
DB20000I  The SQL command completed successfully.

--表空间信息
[db2inst2@localhost db2dump]$ db2pd -alldbs -tablespace 4

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:46:48 -- Date 04/28/2011 04:20:45

Tablespace 4 Configuration:
Address    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x9D140B50 SMS  Regular 4096   32       Yes  64       1     1         Off 2        0          31           SMSFULLTEST

Tablespace 4 Statistics:
Address    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        Max HWM    State      MinRecTime NQuiescers PathsDropped
0x9D140B50 2308       2308       2308       0          0          0          0          0x00000000 1303934864 0          No          

Tablespace 4 Autoresize Statistics:
Address    AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x9D140B50 No  No  0           0           No  0                    None                       No  

Containers:
Address    ContainNum Type    TotalPgs   UseablePgs PathID     StripeSet  Container 
0x9D0EE960 0          Path    1155       1155       -          0          /mnt/test1_s
0x9D0EEB60 1          Path    1153       1153       -          0          /mnt/test1_big


--向里面塞入数据
db2 => create table SMSFULL1 as (select * from syscat.tables) definition only in SMSFULLTEST
DB20000I  The SQL command completed successfully.
db2 => insert into SMSFULL1 select * from syscat.tables
DB20000I  The SQL command completed successfully.
db2 => insert into SMSFULL1 select * from SMSFULL1
DB20000I  The SQL command completed successfully.
db2 => insert into SMSFULL1 select * from SMSFULL1
DB20000I  The SQL command completed successfully.

--省略

db2 => insert into SMSFULL1 select * from SMSFULL1
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0968C  The file system is full.  SQLSTATE=57011

--表空间很快满了

--文件系统情况，看到其中一个100%
[root@localhost test1_big]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/tmp/test1_s.img      4.9M  4.7M     0 100% /mnt/test1_s
/tmp/test1_big.img    9.7M  5.7M  3.6M  62% /mnt/test1_big


--再看看两个目录下的文件情况
[root@localhost test1_big]# du -sh *
4.6M    SQL00002.DAT
1.0K    SQLTAG.NAM
[root@localhost test1_big]# cd -
/mnt/test1_s
[root@localhost test1_s]# du -sh *
4.6M    SQL00002.DAT
8.0K    SQL00002.LB
8.0K    SQL00002.LBA
1.0K    SQLTAG.NAM

--可以看到SMS是平均的吧数据分配到每个容器当中




--附上diag log信息
2011-04-28-04.10.14.243363+480 E1389098G1271      LEVEL: Error (OS)
PID     : 7398                 TID  : 1983900560  PROC : db2sysc 0
INSTANCE: db2inst2             NODE : 000         DB   : SAMPLE
APPHDL  : 0-7620               APPID: *LOCAL.db2inst2.110427193357
AUTHID  : DB2INST2
EDUID   : 1453                 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, oper system services, sqloseekwrite64, probe:40
MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
          DIA8312C Disk was full.
CALLED  : OS, -, pwrite                           OSERR: ENOSPC (28)
DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes
  File Handle              = 26
  File System Block Size   = 1024 bytes
  File System Type         = ext3
  File Handle Flags :
    Require Sector Align   = No
    DIO/CIO Mode           = Yes
    Raw Block Device       = No
    Reserved Handle        = No
    Flush On Close         = No
    Thread-Level Lock      = No
    Write-through Mode     = Yes
    File Not Tracked       = Yes
DATA #2 : unsigned integer, 4 bytes
131072
DATA #3 : signed integer, 8 bytes
4718592
DATA #4 : signed integer, 4 bytes
-1
DATA #5 : String, 105 bytes
Search for ossError*Analysis probe point after this log entry for further
self-diagnosis of this problem.

2011-04-28-04.10.14.371039+480 I1390370G2589      LEVEL: Error (OS)
PID     : 7398                 TID  : 1983900560  PROC : db2sysc 0
INSTANCE: db2inst2             NODE : 000         DB   : SAMPLE
APPHDL  : 0-7620               APPID: *LOCAL.db2inst2.110427193357
AUTHID  : DB2INST2
EDUID   : 1453                 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100
CALLED  : OS, -, pwrite                           OSERR: ENOSPC (28)
DATA #1 : String, 146 bytes
A total of 5 analysis will be performed :
 - User info
 - ulimit info
 - Target file info
 - I/O attempt
 - File system

 Target file handle = 26
DATA #2 : String, 184 bytes
  Real user ID of current process       = 503
  Effective user ID of current process  = 503
  Real group ID of current process      = 500
  Effective group ID of current process = 500
DATA #3 : String, 370 bytes
Current process limits (unit in bytes except for nofiles) :
  mem     (S/H) = unlimited / unlimited
  core    (S/H) = unlimited / unlimited
  cpu     (S/H) = unlimited / unlimited
  data    (S/H) = unlimited / unlimited
  fsize   (S/H) = unlimited / unlimited
  nofiles (S/H) = 65534 / 65534
  stack   (S/H) = 10485760 / unlimited
  rss     (S/H) = unlimited / unlimited
DATA #4 : String, 260 bytes
Target File Information :
  Size               = 4718592
  Link               = No
  Reference path     = N/A
  Type               = 0x8000
  Permissions        = rw-------
  UID                = 503
  GID                = 500
  Last modified time = 1303935014
  DATA #5 : String, 33 bytes
I/O attempt not implemented yet.
DATA #6 : String, 372 bytes
File System Information of the target file :
  Block size        = 1024 bytes
  Total size        = 5073920 bytes
  Free size         = 0 bytes
  Total # of inodes = 1280
  FS name           = /dev/loop0
  Mount point       = /mnt/test1_s
  FSID              = 1792
  FS type name      = ext2
  DIO/CIO mount opt = None
  Device type       = N/A
  FS type           = 0xe
CALLSTCK:
  [0] 0xB322C481 /home/db2inst2/sqllib/lib32/libdb2osse.so.1 + 0xB8481
  [1] 0xB322C3B5 ossLogSysRC + 0x97
  [2] 0xB321E6D9 /home/db2inst2/sqllib/lib32/libdb2osse.so.1 + 0xAA6D9
  [3] 0xB321D1D8 ossErrorAnalysis + 0x28
  [4] 0xB5D48928 sqloSystemErrorHandler + 0x61C
  [5] 0xB4AB5E29 sqloseekwrite64 + 0x46B
  [6] 0xB4AB578C sqloWriteBlocks + 0xBC
  [7] 0xB466AB72 _Z15sqlbWriteBlocksP16SqlbOpenFileInfoPvxjjPjP12SQLB_GLOBALS + 0x40
  [8] 0xB51149C0 _Z18sqlbSMSDirectWriteP20SQLB_DIRECT_WRITE_CB + 0x284
  [9] 0xB515DB15 _Z15sqlbDirectWriteP20SQLB_DIRECT_WRITE_CB + 0x109

2011-04-28-04.10.14.376525+480 E1392960G804       LEVEL: Error
PID     : 7398                 TID  : 1983900560  PROC : db2sysc 0
INSTANCE: db2inst2             NODE : 000         DB   : SAMPLE
APPHDL  : 0-7620               APPID: *LOCAL.db2inst2.110427193357
AUTHID  : DB2INST2
EDUID   : 1453                 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbSMSDirectWrite, probe:99
MESSAGE : ADM6017E  The table space "SMSFULLTEST" (ID "4") is full. Detected on
          container "/mnt/test1_s" (ID "0").  The underlying file system is
          full or the maximum allowed space usage for the file system has been
          reached. It is also possible that there are user limits in place with
          respect to maximum file size and these limits have been reached.

2011-04-28-04.10.14.377416+480 I1393765G505       LEVEL: Error
PID     : 7398                 TID  : 1983900560  PROC : db2sysc 0
INSTANCE: db2inst2             NODE : 000         DB   : SAMPLE
APPHDL  : 0-7620               APPID: *LOCAL.db2inst2.110427193357
AUTHID  : DB2INST2
EDUID   : 1453                 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbSMSDirectWrite, probe:825
MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
          DIA8312C Disk was full.