CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE '/db2home/catalog_data'
35400)
USER TABLESPACE MANAGED BY DATABASE USING (FILE '/db2home/db2_data'
65536);
db2inst1@DB2:/db2home/db2inst2> db2pd -d leo -tablespaces
Database Partition 0 -- Database LEO -- Active -- Up 0 days 00:00:14
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID
BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x00002B72E49E2B20 0 DMS Regular 4096 32
Yes 32 1 1 Off 1 0 31
SYSCATSPACE
0x00002B72E49E3FE0 1 SMS SysTmp 4096 32 Yes 32 1
1 On 1 0 31 TEMPSPACE1
0x00002B72E49E94E0 2 DMS Large 4096 32 Yes 32 1
1 Off 1 0 31 USERSPACE1
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs
FreePgs HWM Max HWM State MinRecTime NQuiescers
PathsDropped
0x00002B72E49E2B20 0 35400 35360
35296 0 64 35296 35296 0x00000000 0
0 No
0x00002B72E49E3FE0 1 1 1 1 0
0 0 0 0x00000000 0 0 No
0x00002B72E49E94E0 2 65536 65504 96 0
65408 96 96 0x00000000 0 0 No
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize
LastResize LRF
0x00002B72E49E2B20 0 No No 0
0 No 0 None No
0x00002B72E49E3FE0 1 No No 0 0 No
0 None No
0x00002B72E49E94E0 2 No No 0 0 No
0 None No
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs
PathID StripeSet Container
0x00002B72E0CA19E0 0 0 File 35400
35360 - 0 /db2home/catalog_data
0x00002B72E0CA1C20 1 0 Path 1 1
- 0
/db2home/db2inst2/db2inst1/NODE0000/SQL00001/SQLT0001.0
0x00002B72E49EA9A0 2 0 File 65536 65504
- 0 /db2home/db2_data
db2inst1@DB2:/db2home/db2inst2>
通过以上内容，可以看到，syscatspace的auto resize是no，总共可用的page是35360页，已经使用了35296， free
64页；
3、不断的创建表，观察syscatspace的使用状况：
db2inst1@DB2:/db2home/db2inst2> cat createtable.sh
db2 connect to leo
i=2
while [ $i -lt 1000 ]
do
db2 "create table test"${i}" (id int)" >> /dev/null
let i=i+1
done
echo "1000 done"
db2inst1@DB2:/db2home/db2inst2>
观察syscatspace的状况：
>
db2pd -d leo -tablespaces
Database Partition 0 -- Database LEO -- Active -- Up 0 days 00:04:04
Tablespace Configuration:
Address Id Type Content PageSz
ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg
Name
0x00002B72E49E2B20 0 DMS Regular 4096
32 Yes 32 1 1 Off 1 0 31
SYSCATSPACE
0x00002B72E49E3FE0 1 SMS SysTmp 4096 32 Yes
32 1 1 On 1 0 31
TEMPSPACE1
0x00002B72E49E94E0 2 DMS Large 4096 32 Yes
32 1 1 Off 1 0 31
USERSPACE1
0x00002B72E50C01A0 3 DMS Large 4096 4 Yes
4 1 1 Off 1 0 3 SYSTOOLSPACE
Tablespace Statistics:
Address Id TotalPgs UsablePgs
UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime
NQuiescers PathsDropped
0x00002B72E49E2B20 0
35400 35360 35360 0 0 35360 35360
0x00000000 0 0 No
0x00002B72E49E3FE0 1
1 1 1 0 0 0 0
0x00000000 0 0 No
0x00002B72E49E94E0 2
65536 65504 5920 64 59520 5984 5984
0x00000000 0 0 No
0x00002B72E50C01A0 3
8192 8188 12 24 8152 36 36
0x00000000 0 0 No
Tablespace Autoresize Statistics:
Address Id AS AR
InitSize IncSize IIP MaxSize LastResize LRF
0x00002B72E49E2B20 0 No No 0 0 No
0 None No
0x00002B72E49E3FE0 1 No No 0 0 No
0 None No
0x00002B72E49E94E0 2
No No 0 0 No 0
None No
0x00002B72E50C01A0 3 No Yes 0
-1 No None None No
Containers:
Address TspId ContainNum Type TotalPgs
UseablePgs PathID StripeSet Container
0x00002B72E0CA19E0 0 0 File 35400 35360
- 0 /db2home/catalog_data
0x00002B72E0CA1C20 1
0 Path 1 1 - 0
/db2home/db2inst2/db2inst1/NODE0000/SQL00001/SQLT0001.0
0x00002B72E49EA9A0
2 0 File 65536 65504 - 0
/db2home/db2_data
0x00002B72E50F2400 3 0 File 8192
8188 - 0
/db2home/db2inst2/db2inst1/NODE0000/SQL00001/SYSTOOLSPACE
>
可以看到，表空间已经耗尽。
5、查看db2diag.log和db2inst1.nfy
db2inst1.nfy：
2014-01-16-22.43.55.124986 Instance:db2inst1 Node:000
PID:13191(db2agent (LEO) 0) TID:3099584832
Appid:*LOCAL.db2inst1.140116144357
buffer pool services sqlbAllocateExtent Probe:840 Database:LEO
ADM6044E The DMS table space "SYSCATSPACE" (ID "0") is full. If this is
an
autoresize or automatic storage DMS tablespace, the maximum table space
size
may have been reached or the existing containers or storage paths cannot
grow
any more. Additional space can be added to the table space by either adding
new
containers or extending existing ones using the ALTER TABLESPACE SQL
statement.
If this is an autoresize or automatic storage DMS table space, additional
space
can be added by adding containers to an autoresize table space or by adding
new
storage paths to an automatic storage database.
db2diag.log:
2014-01-16-22.43.55.124870+480 E4198823E1118 LEVEL: Error
PID : 13191 TID : 47771725850944PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : LEO
APPHDL : 0-165 APPID: *LOCAL.db2inst1.140116144357
AUTHID : DB2INST1
EDUID : 205 EDUNAME: db2agent (LEO) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent,
probe:840
MESSAGE : ADM6044E The DMS table space "SYSCATSPACE" (ID "0") is full.
If
this is an autoresize or automatic storage DMS tablespace,
the
maximum table space size may have been reached or the
existing
containers or storage paths cannot grow any more. Additional
space
can be added to the table space by either adding new containers
or
extending existing ones using the ALTER TABLESPACE SQL statement.
If
this is an autoresize or automatic storage DMS table space,
additional space can be added by adding containers to an
autoresize
table space or by adding new storage paths to an automatic
storage
database.
发现已经进行报警，表空间已经full；
6、检查数据库的使用状况：
>
db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = LEO
Database
name = LEO
Local database directory =
/db2home/db2inst2
Database release level =
d.00
Comment =
Directory entry
type = Indirect
Catalog database partition number =
0
Alternate server hostname =
Alternate server port
number =
>
db2 activate db leo
DB20000I The ACTIVATE DATABASE command completed
successfully.
>
能够正常激活数据库；
连接数据库：
>
db2 connect to leo
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID =
DB2INST1
Local database alias = LEO
>
列出所有的表：
>
db2 list tables
Table/View Schema Type Creation
time
------------------------------- --------------- -----
--------------------------
TEST10 DB2INST1
T 2014-01-16-22.43.07.949961
TEST11
DB2INST1 T
2014-01-16-22.43.07.968367
TEST12 DB2INST1
T 2014-01-16-22.43.07.986508
TEST13
DB2INST1 T
2014-01-16-22.43.08.005140
TEST14 DB2INST1
T 2014-01-16-22.43.08.023109
TEST15
DB2INST1 T
2014-01-16-22.43.08.040807
TEST16 DB2INST1
T 2014-01-16-22.43.08.059969
TEST17
DB2INST1 T
2014-01-16-22.43.08.078975
TEST18 DB2INST1
T 2014-01-16-22.43.08.097114
TEST19
DB2INST1 T
2014-01-16-22.43.08.114972
TEST2 DB2INST1
T 2014-01-16-22.43.07.582190
TEST20
DB2INST1 T
2014-01-16-22.43.08.208747
TEST21 DB2INST1
T 2014-01-16-22.43.08.232961
TEST22
DB2INST1 T
2014-01-16-22.43.08.251564
TEST23 DB2INST1
T 2014-01-16-22.43.08.270803
TEST24
DB2INST1 T
2014-01-16-22.43.08.289793
TEST25 DB2INST1
T 2014-01-16-22.43.08.306978
TEST26
DB2INST1 T
2014-01-16-22.43.08.325669
TEST27 DB2INST1
T 2014-01-16-22.43.08.342915
TEST28
DB2INST1 T
2014-01-16-22.43.08.362350
TEST29 DB2INST1
T 2014-01-16-22.43.08.380533
TEST3
DB2INST1 T
2014-01-16-22.43.07.802778
TEST30 DB2INST1
T 2014-01-16-22.43.08.398687
TEST31
DB2INST1 T
2014-01-16-22.43.08.416487
TEST32 DB2INST1
T 2014-01-16-22.43.08.435517
TEST33
DB2INST1 T
2014-01-16-22.43.08.455622
TEST34 DB2INST1
T 2014-01-16-22.43.08.473739
TEST35
DB2INST1 T
2014-01-16-22.43.08.491922
TEST36 DB2INST1
T 2014-01-16-22.43.08.510685
TEST37
DB2INST1 T
2014-01-16-22.43.08.528941
TEST38 DB2INST1
T 2014-01-16-22.43.08.637314
TEST39
DB2INST1 T
2014-01-16-22.43.08.654560
TEST4 DB2INST1
T 2014-01-16-22.43.07.824853
TEST40
DB2INST1 T
2014-01-16-22.43.08.675128
TEST41 DB2INST1
T 2014-01-16-22.43.08.696427
TEST42
DB2INST1 T
2014-01-16-22.43.08.716837
TEST43 DB2INST1
T 2014-01-16-22.43.08.736772
TEST44
DB2INST1 T
2014-01-16-22.43.08.754915
TEST45 DB2INST1
T 2014-01-16-22.43.08.772595
TEST46
DB2INST1 T
2014-01-16-22.43.08.791772
TEST47 DB2INST1
T 2014-01-16-22.43.08.810503
TEST48
DB2INST1 T
2014-01-16-22.43.08.828838
TEST49 DB2INST1
T 2014-01-16-22.43.08.846706
TEST5
DB2INST1 T
2014-01-16-22.43.07.854681
TEST50 DB2INST1
T 2014-01-16-22.43.08.865061
TEST51
DB2INST1 T
2014-01-16-22.43.08.884072
TEST52 DB2INST1
T 2014-01-16-22.43.08.901929
TEST53
DB2INST1 T
2014-01-16-22.43.08.920345
TEST54 DB2INST1
T 2014-01-16-22.43.08.938841
TEST55
DB2INST1 T
2014-01-16-22.43.08.955252
TEST56 DB2INST1
T 2014-01-16-22.43.09.041091
TEST57
DB2INST1 T
2014-01-16-22.43.09.060137
TEST58 DB2INST1
T 2014-01-16-22.43.09.081601
TEST59
DB2INST1 T
2014-01-16-22.43.09.100590
TEST6 DB2INST1
T 2014-01-16-22.43.07.874425
TEST60
DB2INST1 T
2014-01-16-22.43.09.131198
TEST61 DB2INST1
T 2014-01-16-22.43.09.154389
TEST62
DB2INST1 T
2014-01-16-22.43.09.172146
TEST63 DB2INST1
T 2014-01-16-22.43.09.190823
TEST64
DB2INST1 T
2014-01-16-22.43.09.209063
TEST65 DB2INST1
T 2014-01-16-22.43.09.226674
TEST66
DB2INST1 T
2014-01-16-22.43.09.245167
TEST67 DB2INST1
T 2014-01-16-22.43.09.263571
TEST68
DB2INST1 T
2014-01-16-22.43.09.280678
TEST69 DB2INST1
T 2014-01-16-22.43.09.298816
TEST7
DB2INST1 T
2014-01-16-22.43.07.894526
TEST70 DB2INST1
T 2014-01-16-22.43.09.316942
TEST71
DB2INST1 T
2014-01-16-22.43.09.334792
TEST72 DB2INST1
T 2014-01-16-22.43.09.352891
TEST73
DB2INST1 T
2014-01-16-22.43.09.372251
TEST74 DB2INST1
T 2014-01-16-22.43.09.535501
TEST75
DB2INST1 T
2014-01-16-22.43.09.555934
TEST76 DB2INST1
T 2014-01-16-22.43.09.575945
TEST77
DB2INST1 T
2014-01-16-22.43.09.594624
TEST78 DB2INST1
T 2014-01-16-22.43.09.611096
TEST79
DB2INST1 T
2014-01-16-22.43.09.630839
TEST8 DB2INST1
T 2014-01-16-22.43.07.912784
TEST80
DB2INST1 T
2014-01-16-22.43.09.648722
TEST81 DB2INST1
T 2014-01-16-22.43.09.667974
TEST82
DB2INST1 T
2014-01-16-22.43.09.685542
TEST83 DB2INST1
T 2014-01-16-22.43.09.703873
TEST84
DB2INST1 T
2014-01-16-22.43.09.722815
TEST85 DB2INST1
T 2014-01-16-22.43.09.740763
TEST86
DB2INST1 T
2014-01-16-22.43.09.759283
TEST87 DB2INST1
T 2014-01-16-22.43.09.775975
TEST88
DB2INST1 T
2014-01-16-22.43.09.794736
TEST89 DB2INST1
T 2014-01-16-22.43.09.815167
TEST9
DB2INST1 T
2014-01-16-22.43.07.931058
TEST90 DB2INST1
T 2014-01-16-22.43.09.833327
TEST91
DB2INST1 T
2014-01-16-22.43.09.851643
TEST92 DB2INST1
T 2014-01-16-22.43.09.899610
91 record(s) selected.
>
能够列出；
查询表中记录：
>
db2 "select * from test10"
ID
-----------
0 record(s) selected.
>
能够查询；
创建新表：
db2inst1@DB2:/db2home/db2inst2> db2 "create table leo(id int)"
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 "SYSCATSPACE".
SQLSTATE=57011
db2inst1@DB2:/db2home/db2inst2>
无法创建新表；
且在以上操作过程中，db2diag.log中，一直在输出error：
2014-01-17-13.07.48.794034+480 I4289233E2215 LEVEL: Severe
PID : 3720 TID : 46958517414208PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : LEO
APPHDL : 0-48 APPID: *LOCAL.db2inst1.140117050752
AUTHID : DB2INST1
EDUID : 31 EDUNAME: db2agent (LEO) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbDMScheckObjAlloc,
probe:835
MESSAGE : ZRC=0x85020021=-2063466463=SQLB_END_OF_CONTAINER
"DMS Container space full"
7、查看表空间的属性
可以通过快照视图sysibmadm.TBSP_UTILIZATION得到表空间的属性信息，包括是否是自动扩展，每次扩展多少（后续还会继续调查此处内容）
>
db2 describe table sysibmadm.TBSP_UTILIZATION
Data type
Column
Column name schema Data type name
Length Scale Nulls
------------------------------- ---------
------------------- ---------- ----- ------
SNAPSHOT_TIMESTAMP
SYSIBM TIMESTAMP 10 6 Yes
TBSP_ID SYSIBM BIGINT 8
0 Yes
TBSP_NAME SYSIBM VARCHAR
128 0 Yes
TBSP_TYPE SYSIBM
VARCHAR 10 0 Yes
TBSP_CONTENT_TYPE
SYSIBM VARCHAR 10 0 Yes
TBSP_CREATE_TIME SYSIBM TIMESTAMP 10
6 No
TBSP_STATE SYSIBM VARCHAR
256 0 Yes
TBSP_TOTAL_SIZE_KB SYSIBM
BIGINT 8 0 Yes
TBSP_USABLE_SIZE_KB
SYSIBM BIGINT 8 0 Yes
TBSP_USED_SIZE_KB SYSIBM BIGINT 8
0 Yes
TBSP_FREE_SIZE_KB SYSIBM
BIGINT 8 0 Yes
TBSP_UTILIZATION_PERCENT
SYSIBM DECIMAL 5 2 Yes
TBSP_TOTAL_PAGES SYSIBM BIGINT 8
0 Yes
TBSP_USABLE_PAGES SYSIBM
BIGINT 8 0 Yes
TBSP_USED_PAGES
SYSIBM BIGINT 8 0 Yes
TBSP_FREE_PAGES SYSIBM BIGINT 8
0 Yes
TBSP_PAGE_TOP SYSIBM
BIGINT 8 0 Yes
TBSP_PAGE_SIZE
SYSIBM INTEGER 4 0 No
TBSP_EXTENT_SIZE SYSIBM INTEGER 4
0 No
TBSP_PREFETCH_SIZE SYSIBM
BIGINT 8 0 Yes
TBSP_MAX_SIZE
SYSIBM BIGINT 8 0 Yes
TBSP_INCREASE_SIZE SYSIBM BIGINT 8
0 Yes
TBSP_INCREASE_SIZE_PERCENT SYSIBM
SMALLINT 2 0 Yes
TBSP_LAST_RESIZE_TIME
SYSIBM TIMESTAMP 10 6 Yes
TBSP_LAST_RESIZE_FAILED SYSIBM SMALLINT 2
0 Yes
TBSP_USING_AUTO_STORAGE SYSIBM
SMALLINT 2 0 Yes
TBSP_AUTO_RESIZE_ENABLED
SYSIBM SMALLINT 2 0 Yes
DBPGNAME SYSIBM VARCHAR 128
0 No
TBSP_NUM_CONTAINERS SYSIBM
BIGINT 8 0 Yes
REMARKS
SYSIBM VARCHAR 254 0 Yes
DBPARTITIONNUM SYSIBM SMALLINT 2
0 Yes
31 record(s) selected.
8、解决方法：
扩展SYSCATSPACE：
使用命令alter
tablespace的extend或者resize的命令进行（这样做，可以避免表空间的rebalance，但前提是表空间所在的存储上，必须有足够的空间）
扩展前表空间容器的大小：
db2inst1@DB2:/db2home/db2inst2> ls -rtlh /db2home/catalog_data
-rw------- 1 db2inst1 db2iadm1 139M 2014-01-17
13:42 /db2home/catalog_data
db2inst1@DB2:/db2home/db2inst2>
进行扩展：
db2inst1@DB2:/db2home/db2inst2> db2 "alter tablespace SYSCATSPACE extend
(file '/db2home/catalog_data' 1M)"
DB20000I The SQL command completed successfully.
扩展完毕后表空间容器的大小：
>
ls -rtlh /db2home/catalog_data
-rw------- 1 db2inst1 db2iadm1 140M 2014-01-17
13:46 /db2home/catalog_data
9、创建新表，验证扩展后的表空间可用：
db2inst1@DB2:/db2home/db2inst2> db2 "create table leo(id
int)"
DB20000I The SQL command completed successfully.
db2inst1@DB2:/db2home/db2inst2> db2 "select * from leo"
ID
-----------
0 record(s) selected.
db2inst1@DB2:/db2home/db2inst2>
结论：
1、创建数据库的时候，如果设定了CATALOG 表空间为DMS类型但并没有显式指定auto
resize属性为yes，则创建出来的数据库的SYSCATSPACE表空间的auto resize属性是关闭的，不能自动扩展；
2、SYSCATSPACE表空间满了之后，与SYSCATSPACE表空间相关的操作将失败（例如创建表）；
3、可以使用alter
tablespace命令对SYSCATSPACE表空间进行extend（建议不要增加新的容器，以避免表空间的rebalance）。
