SQL0290N 表空间状态为： 停顿的独占的处理

今天一个同事报告一个问题，表都不能使用了ITPUB个人空间S h Qt3S?M ~
检查了一下，发现
d[*X#_VY[y
a0问题 db2 => select * from testITPUB个人空间AZW)ja%[0_E9~

t*z?!L;AF8g.~fR0ACTNO ACTKWD ACTDESCITPUB个人空间ov3e*e!Kro l
------ ------ --------------------ITPUB个人空间k_;^$Bx&kjMDGc%o
SQL0290N Table space access is not allowed. SQLSTATE=55039ITPUB个人空间#Y(og/q3w$|Eq.w U
其他表也不能使用ITPUB个人空间7k"y0jY*h0Sn3v-iP+q
ITPUB个人空间I |L4@*}5Oz
在db2cc里查看表空间状态为： 停顿的独占ITPUB个人空间~qg`
oqu^
ITPUB个人空间KI pRFJ(~8KR8Z

l5|H2G&t w4g~'A0解决方法：
P#Y[:g:U[v0ITPUB个人空间4]YP
xkP7i U _;t
到命令行状态，首先connect 到需要处理的数据库ITPUB个人空间b?4De(c)i0] t

a8`Z/e~"J"i3@01、db2 => list tablespaces show detail 显示表空间状态ITPUB个人空间*}-Un"k X

9MV$s
WP$d#H0Tablespaces for Current DatabaseITPUB个人空间
mN,G Ob,S
ITPUB个人空间*gdRw` eozFg
Tablespace ID = 0ITPUB个人空间gzW{:CK J
Name = SYSCATSPACE
%B/E4M\ac)zX7X2L0Type = System managed spaceITPUB个人空间I2e&S,xw k
Contents = Any data
4wf+o#Z]zW0State = 0x0000ITPUB个人空间yD oc:UN#d
Detailed explanation:ITPUB个人空间#g*f,xB7{
NormalITPUB个人空间Ivl+u7L;M4N
Total pages = 2519
z4Z)e(en@Ts(EZL0Useable pages = 2519ITPUB个人空间%A QrD*C2G-sC
Used pages = 2519
N.c'O Lh$i_k0Free pages = Not applicableITPUB个人空间w0v;H(D)K Bx-k0j
High water mark (pages) = Not applicable
dL`$K}8BY0Page size (bytes) = 4096ITPUB个人空间!] K S BNT
Extent size (pages) = 32ITPUB个人空间AcJ9T8\7H0R(]k
Prefetch size (pages) = 32
9D&o*_p5u%P1Y9Vo0Number of containers = 1
8u$_y'dz0ITPUB个人空间N6fn_}Qyd
Tablespace ID = 1
`4T y-psG0Name = TEMPSPACE1ITPUB个人空间6WiQI fM4BJ~
Type = System managed spaceITPUB个人空间H&c~7NVnS@
Contents = System Temporary dataITPUB个人空间 zK.[6v2d x&X,|1`
State = 0x0000
+@;iK {!? t~0Detailed explanation:
*c$g MnRl9U6l&O]JM0NormalITPUB个人空间/sM w3D^&Wl
Total pages = 1ITPUB个人空间3gr0WRoA#]
Useable pages = 1ITPUB个人空间 N
Q#\;[!_+m"R
Used pages = 1
eAE ~%O0Free pages = Not applicable
?BEP9W3q:|-{0High water mark (pages) = Not applicable
N5?v;MR0Page size (bytes) = 4096
;J7?_(q
{r0Extent size (pages) = 32ITPUB个人空间8qQEe [(W'Y:{wC
Prefetch size (pages) = 32ITPUB个人空间6^$^_v+{|U)RL2E
Number of containers = 1ITPUB个人空间*v8]9}n8n

oa4d-z} k[0Tablespace ID = 2
:p?EU"m"S|0Name = USERSPACE1ITPUB个人空间Z0j!X0t0T,E
Type = System managed space
3i)Ve4R4F
P|(M}0Contents = Any dataITPUB个人空间+s^+S2^C+^%]fw.c
State = 0x0004 这个代码意义就是“停顿的独占”，正常状态为0x0000，ITPUB个人空间)u%X*\!]dE/F"]
非0就是有问题，都可以用下面方法解决。
Wk+O'~1V(C#BK0Detailed explanation:ITPUB个人空间#jj/I.n;LQo Gz&R
Quiesced: EXCLUSIVEITPUB个人空间+}c.v"N$P
Total pages = 687ITPUB个人空间*[-Who(b C|
sn'E
Useable pages = 687
u"bgYd8v zm T G0Used pages = 687
%e1sfVX4X5wG n0Free pages = Not applicable
,BQ$k+X p2\cP"soD0High water mark (pages) = Not applicable
+m0j']7u `\*[5e5sx |0Page size (bytes) = 4096
k$LI.p!I0Extent size (pages) = 32
7D3|s(e!}?~0Prefetch size (pages) = 32ITPUB个人空间F0r'`:c)c[pa
Number of containers = 1
a;]c,C&ji;nL0Number of quiescers = 1 注意下面几行ITPUB个人空间mH#I'Ix;M&b2|
Quiescer 1:
5_ B!cR1H6k:?0Tablespace ID = 2
x @+tc6zhX"O?K0Object ID = 50 “object id是造成死锁表的id”ITPUB个人空间 FB1pP"B_

+DE3]W(n0ITPUB个人空间6c/g)QZmE.O)s
}8V
2、db2 => select tabname from syscat.tables where tableid=50 通过所住的id号找出是哪个表ITPUB个人空间] L4us+a E0L.^B+J

WJT f3n@x Q0TABNAME ITPUB个人空间|t5aXK(RjR%x
--------------------------------------------------------------------------------------------------------------------------------ITPUB个人空间\zC-eF#`2^6JnpG+o
SYSINDEXEXTENSIONPARMS
*z9Y%r&k#~h_9?6MF0TEST 就是这个表
*eqg9c9?XG0
4zo{&Y F*d s_[02 record(s) selected.
^ Y7b5~iPv1kq0ITPUB个人空间#N]7L\c(W
ITPUB个人空间8M!Y@l~[;v"r%sU
3、db2 => quiesce tablespaces for table test reset 执行该命令清除错误的状态ITPUB个人空间!g6kRWfs7Z7s
DB20000I The QUIESCE TABLESPACES command completed successfully.
6{!t[KC}0

Jy%w
dS$kk04、重新select，问题解决
;Yy0yB)h~0
(Q.{!t4yKU!mx05、比较常见导致改种问题的原因是，非正常的中止正对表进行的操作，导致对表空间的使用没有正常的释放。