数据库插入操作报271错误问题分析

环境:(产品,平台,机型,软件版本,等)

Informix IDS family

问题描述:

应用插入行记录时失败，数据库返回271错.

解答:

-271 错误含义如下:

-271 Could not insert new row into the table.

This problem has many possible causes, including a locked table or a full disk. Check the accompanying ISAM error code for more information.

当遇到271错误时，应用要插入的表遇到了存储限制。可能的Informix存储限制包括：

1) 每个表分片的最大extent个数
2) 每个表分片的最大物理页
3) 表所在数据空间（dbspace）的空闲空间大小

解决方案：

一、检查表的extent是否达到最大值的方法如下：

a) 首先需要参考以下步骤计算某表的最大extent值，每个表的最大值都不同。

Considering the Upper Limit on Extents
Do not allow a table to acquire a large number of extents because an upper limit exists on the number of extents allowed. Trying to add an extent after you reach the limit causes error -136 (No more extents) to follow an INSERT request.
To determine the upper limit on number of extents allowed for a table

1. Run the following oncheck option to obtain the physical address of the object (table or index fragment) for which you wish to calculate extent limits.
oncheck -pt databasename:tablename
Figure 29 shows sample output for oncheck -pt.
Figure 29.
oncheck -pt Output
TBLspace Report for stores7:wbyrne.sfe_enquiry
Physical Address 7002c7
Number of special columns 18
Number of keys 0
Number of extents 65
Number of data pages 960

1. Split the physical address of the table or index fragment into the chunk# (leading digits) and page# (last 5 digits), and then run oncheck -pP chunk# page#, specifying the arguments as hexadecimal numbers by prefixing with 0x.
In Figure 29, the Physical Address is 7002c7. Therefore, chunk# is 0x007 (or 0x07) and the page# is 0x0002c (or 0x2c) in the following oncheck command:
oncheck -pP 0x7 0x2c
Figure 30 shows sample output for oncheck -pp 0x7 0x2c.
Figure 30. oncheck -pp chunk# page# Output
addr stamp nslots flag type frptr frcnt next prev
7002c7 112686 5 2 PARTN 828 1196 0 0
slot ptr len flg
1 24 92 0
2 116 40 0
3 156 144 0
4 300 0 0
5 300 528 0

2. From the output of the oncheck -pP, take the number below the frcnt column and divide it by 8 to obtain the number of additional extents you can have for this object.
In the sample oncheck -pP in Figure 30, the frcnt column shows the value 1196. The following calculation shows the number of additional:
Additional_extents = trunc (frcnt / 8)
= trunc (1196 / 8)
= 149

3. To obtain the maximum number of extents, add the value in the Number of extents line in the oncheck -pt output to the Additional_extents value, as the following formula shows:
Maximum_number_extents = Additional_extents +
Number_of_extents
In the sample oncheck -pt in Figure 29, the Number of extents line shows the value 65. The following calculation shows the maximum number of extents for this table:
Maximum_number_extents = 149 + 65 = 214

b) 使用如下SQL，可检查数据实例内，所有表分配extent的情况

select t.tabname, t.dbsname, i.ti_nextns ext_nums, i.ti_nrows rows
from sysmaster:systabnames t, sysmaster:systabinfo i
where t.partnum =i.ti_partnum and i.ti_nextns >= 150 and i.ti_nrows > 10000
order by 3 desc

c) 当已分配的extent数等于最大值时，可采取如下操作

1、需要重建该表，并指定first extent及next extext size 来避免extent超限。
2、给表分片,规避单片表的extent物理限制

二、 检查表分片使用的物理页数量。

Informix每个表分片的物理页个数为 16,775,134 , 使用oncheck -pt 可以查看表当前物理页的使用量. Number of pages allocated 条目显示了表分片总共的物理页数量，该值不能超过16,775,134 的最大值。达到物理页上限时，需要对表进行分片。

oncheck -pT stores_demo:customer
The following example shows an example of output of the oncheck -pt command:

TBLspace Report for testdb:tab1

Physical Address 2:10
Creation date 10/07/2004 17:01:16
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 14
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 1
Pagesize (k) 4
First extent size 4
Next extent size 4
Number of pages allocated 340
Number of pages used 337
Number of data pages 336
Number of rows 75806
Partition partnum 2097154
Partition lockid 2097154

三、 检查dbspace空间使用量。

a) 使用如下SQL计算每个dbspace的空间使用率

select name dbspace, sum( chksize) allocated, sum( nfree) free, round(((sum(chksize)-sum(nfree))/sum(chksize))*100) pcused
from sysmaster:sysdbspaces d, sysmaster:syschunks c
where d.dbsnum=c.dbsnum
group by 1
order by 4 desc;

b) 如果表所在的dbspace空间使用率接近100% , 需要向该dbspace增加chunk

表空间分配的最小extent是4page，因此数据库无法利用dbspace中小于4page的空闲空间。因此当dbspace空闲空间不为0时，由于extent分配失败，数据也不能插入。