摘要:同事反馈:[jcc][t4][102][10040][3.57.82]非原子批处理失败。尽管已提交批处理,但该批处理的成员至少发生了一个异常。使用getNextException()检索已批处理的特定元素的异常。ERRORCODE=-4228,SQLSTATE=null查看db2diag.log日志:2012-11-20-10.00.33.551000+480E27658279F1143LEVEL:ErrorPID:2276TID:3796PROC:db2syscs。示例:DB2NODE:000DB:NCTESTAPPHDL:0-49794APPID:20.10.80.247.48084.121120015251AUTHID:NCTESTEDUID:3796DUNAME:db2agent0FUNCTION:DB2UDB,buffertools服务,sqlbAllocate Extent,probe:840MESSAGE:ADM6044EDMS表空间“NNC_INDEX01”已满,如果这是自动保存或自动存储DMS表,可能已达到最大表空间大小,但现有容器或存储路径不会再增长。通过使用ALTERTABLESPACESQL语句添加新容器或扩展现有容器,可以将其他空间添加到表空间。如果这是自动存储或自动存储DMS表空间,则可以通过将容器添加到自动存储空间或将新存储路径添加到自动存储器数据库来添加额外的空间。2012-11-20-10.00.33.551000+480I27659424F1147LEVEL:警告PID:2276TID:3796PROC:db2syscs.exeINSTANCE:DB2NODE:000DB:NCTESTAPPHDL:0-49794APPID:20.10.80.247.48084.121120015251AUTHID:NCTESTEDUID:3796DUNAME:db2agent0FUNCTION:DB2UDB,bufferpoolservices,sqlbObtainDataExtent,探针:800MESSAGE:ZRC=0x8502021=-2063466463=SQLB_ END_ OF_ CONTAINER“DMSContainerspacefull”数据#1:对象描述符,PD_ TYPE_ SQLB_ OBJECT_ DESC,88bytesObj:{pool:5;obj:11020;类型:1}父级={4;1360}lifeLSN:0000000005AC4D6Etid:000extentAnchor:78560initEmpPages:0poolPage0:0poolflags:102objectState:27lastSMP:0pageSize:4096extentSize:32bufferPoolID:2partialHash:705429509bufferPool:0x0000001a7837a0pdef:0x00000001a804160从上述错误中可以看出,NNC_INDEX01表空间已满。查看NNC_INDEX01表空间用法:db2=˃listtablespaceshowdetail发现可用页面数已更改为0。要查看错误,需要绑定变量+Batch。insertintoIC_ATP_F。该表具有主键索引,索引表空间必须用于批量插入。
同事反馈问题:
[jcc][t4][102][10040][3.57.82] 非原子批处理出现故障。虽然已经提交了批处理,但是该批处理的某个成员至少发生了一个异常。
使用 getNextException() 来检索已经过批处理的特定元素的异常。 ERRORCODE=-4228, SQLSTATE=null
查看db2diag.log日志:
2012-11-20-10.00.33.551000+480 E27658279F1143 LEVEL: Error
PID : 2276 TID : 3796 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NCTEST
APPHDL : 0-49794 APPID: 20.10.80.247.48084.121120015251
AUTHID : NCTEST
EDUID : 3796 EDUNAME: db2agent (NCTEST) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:840
MESSAGE : ADM6044E The DMS table space "NNC_INDEX01" (ID "5") 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.
2012-11-20-10.00.33.551000+480 I27659424F1147 LEVEL: Warning
PID : 2276 TID : 3796 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NCTEST
APPHDL : 0-49794 APPID: 20.10.80.247.48084.121120015251
AUTHID : NCTEST
EDUID : 3796 EDUNAME: db2agent (NCTEST) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbObtainDataExtent, probe:800
MESSAGE : ZRC=0x85020021=-2063466463=SQLB_END_OF_CONTAINER
"DMS Container space full"
DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes
Obj: {pool:5;obj:11020;type:1} Parent={4;1360}
lifeLSN: 000000005AAC4D6E
tid: 0 0 0
extentAnchor: 78560
initEmpPages: 0
poolPage0: 0
poolflags: 102
objectState: 27
lastSMP: 0
pageSize: 4096
extentSize: 32
bufferPoolID: 2
partialHash: 705429509
bufferPool: 0x000000001a7837a0
pdef: 0x000000001a804160
从上述错误来看,是表空间NNC_INDEX01表空间被占满了。
看下NNC_INDEX01表空间使用情况:
db2 => list tablespaces show detail
发现可用页数已经变成了0。
查看报错的地方,是一个绑定变量+Batch的操作。insert into IC_ATP_F,这个表有一个主键索引,而批量插入的时候肯定会用到索引表空间。
添加一个容器,alter tablespace nnc_index01 add (file '\db2\nnc_index01_2' 204800)
再次执行,问题解决。