unable to update rows
Users on IDS 11.50 (HP-UX) hit -271/-136 "no more extents" errors when updating rows, even though the target table had only 93 extents (well under the ~237 limit for 2K pages), no special columns, no attached indexes, and plenty of free dbspace. Art Kagel explained what shares the partition header page (basic info, attached index keys, special columns, extents) and the 2^24-page partition limit, then suggested checking triggers. That was it: an UPDATE trigger wrote to an audit table that had maxed out at 228 extents; reorganising that audit table was the fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.fc6
HP-UX 11.31 PA-RISC
Users are reporting that they get -271/-136 errors when they try to update an
existing row. -271 is "Could not insert new row into the table", and -136 is
"ISAM error: no more extents". The table is in one dbspace, which has over 2GB
free, and there are two indexes in a second dbspace, also with over 2GB free.
finderr indicates that the problem might be the number of extents, and gives a
formula based on page size for the theoretical maximum number of extents for a
table (237 in a 2K page). The table currently has only 93 extents.
Finderr also indicates that the actual max number of extents may be less than
the theoretical max if there are a lot of other objects with large numbers of
extents, filling of the tablespace tablespace.
How do I look at the tablespace tablespace info for this dbspace? I have done
an oncheck -pt and -pT for the table, but did not see anything that would
indicate the problem.
Thanks in advance.
Do an oncheck -pe into a file, grep that for your table. Sounds like =
you need to reorg the table.
cheers
j.
On Dec 26, 2012, at 11:38 AM, MARK COLLINS wrote:
> IDS 11.50.fc6=20
> HP-UX 11.31 PA-RISC=20
>=20
> Users are reporting that they get -271/-136 errors when they try to =
update an=20
> existing row. -271 is "Could not insert new row into the table", and =
-136 is=20
> "ISAM error: no more extents". The table is in one dbspace, which has =
over 2GB=20
> free, and there are two indexes in a second dbspace, also with over =
2GB free.=20
> finderr indicates that the problem might be the number of extents, and =
gives a=20
> formula based on page size for the theoretical maximum number of =
extents for a=20
> table (237 in a 2K page). The table currently has only 93 extents.=20
>=20
> Finderr also indicates that the actual max number of extents may be =
less than=20
> the theoretical max if there are a lot of other objects with large =
numbers of=20
> extents, filling of the tablespace tablespace.=20
>=20
> How do I look at the tablespace tablespace info for this dbspace? I =
have done=20
> an oncheck -pt and -pT for the table, but did not see anything that =
would=20
> indicate the problem.=20
>=20
> Thanks in advance.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Original post:
IDS 11.50.fc6
HP-UX 11.31 PA-RISC
Users are reporting that they get -271/-136 errors when they try to update an
existing row. -271 is "Could not insert new row into the table", and -136 is
"ISAM error: no more extents". The table is in one dbspace, which has over 2GB
free, and there are two indexes in a second dbspace, also with over 2GB free.
finderr indicates that the problem might be the number of extents, and gives a
formula based on page size for the theoretical maximum number of extents for a
table (237 in a 2K page). The table currently has only 93 extents.
Finderr also indicates that the actual max number of extents may be less than
the theoretical max if there are a lot of other objects with large numbers of
extents, filling of the tablespace tablespace.
How do I look at the tablespace tablespace info for this dbspace? I have done
an oncheck -pt and -pT for the table, but did not see anything that would
indicate the problem.
Thanks in advance.
Response:
Could you post the oncheck -pt output?
Jacques Renaut
IBM Informix Advanced Support
APD Team
Jacques,
Here is the output from oncheck -pt:
TBLspace Report for my_db:theowner.my_table
Physical Address 11:6504131
Creation date 01/14/2012 07:58:59
TBLspace Flags 800801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 910
Number of special columns 0
Number of keys 0
Number of extents 93
Current serial value 114729
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 59832
Next extent size 16
Number of pages allocated 57344
Number of pages used 57341
Number of data pages 57326
Number of rows 114652
Partition partnum 6360351
Partition lockid 6360351
Extents
Logical Page Physical Page Size Physical Pages
0 13:1417393 880 880
880 13:1418337 53504 53504
54384 7:3518776 8 8
54392 13:622083 8 8
54400 7:3234544 8 8
54408 13:3572125 8 8
54416 13:3576868 8 8
54424 13:3579072 8 8
54432 10:3863141 8 8
54440 13:1753943 8 8
54448 13:1755833 8 8
54456 13:1267745 8 8
54464 13:1762440 8 8
54472 10:4451910 8 8
54480 13:1810795 8 8
54488 13:1762663 8 8
54496 13:1752975 16 16
54512 13:1241267 16 16
54528 13:3112718 16 16
54544 13:3614460 16 16
54560 13:3602612 16 16
54576 13:3584300 16 16
54592 13:3183329 16 16
54608 13:3404296 16 16
54624 13:3600956 16 16
54640 13:3675577 16 16
54656 13:3692885 16 16
54672 13:1949986 16 16
54688 13:3676169 16 16
54704 13:1918795 16 16
54720 13:3855532 16 16
54736 13:3865369 16 16
54752 13:3886923 32 32
54784 13:3937671 32 32
54816 13:4022689 32 32
54848 13:4005625 32 32
54880 13:4043609 32 32
54912 13:4085045 32 32
54944 13:4096925 32 32
54976 13:4115852 32 32
55008 13:4112273 32 32
55040 13:1241711 32 32
55072 13:4185974 32 32
55104 13:4211094 32 32
55136 13:4215254 32 32
55168 13:3884487 32 32
55200 13:4314266 32 32
55232 13:4282174 32 32
55264 13:4359550 64 64
55328 13:4443091 64 64
55392 13:4525171 64 64
55456 13:4538307 64 64
55520 13:4650789 64 64
55584 13:4679978 64 64
55648 13:4807723 64 64
55712 13:4787419 64 64
55776 13:4886502 64 64
55840 13:4930351 64 64
55904 13:5319068 64 64
55968 13:5263316 64 64
56032 13:5344668 64 64
56096 13:4617613 64 64
56160 13:4619799 64 64
56224 13:5527252 64 64
56288 13:5450900 128 128
56416 13:5567194 128 128
56544 13:5853036 128 128
56672 13:6115386 128 128
56800 13:6239346 128 128
56928 13:5091179 128 128
57056 7:4647717 8 8
57064 7:4660316 8 8
57072 7:4666652 8 8
57080 7:4674252 8 8
57088 7:4669144 8 8
57096 7:4677524 8 8
57104 7:4659117 8 8
57112 7:4667916 8 8
57120 7:4687796 8 8
57128 7:4691916 8 8
57136 7:4695064 16 16
57152 7:4706302 16 16
57168 7:4702336 16 16
57184 7:4757139 16 16
57200 7:4932413 16 16
57216 7:4882113 16 16
57232 7:4873349 16 16
57248 7:4845273 16 16
57264 7:4892843 16 16
57280 7:4695120 16 16
57296 7:4858961 16 16
57312 7:4980557 16 16
57328 7:4994409 16 16
Index ix123_32 fragment partition indexdbs in DBspace indexdbs
Physical Address 4:706359
Creation date 12/23/2012 10:17:48
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 910
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 946
Next extent size 4
Number of pages allocated 946
Number of pages used 798
Number of data pages 0
Number of rows 0
Partition partnum 4198987
Partition lockid 6360351
Extents
Logical Page Physical Page Size Physical Pages
0 4:856813 946 946
Index pk_my_tableid fragment partition indexdbs in DBspace indexdbs
Physical Address 4:706360
Creation date 12/23/2012 10:17:51
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 910
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 854
Next extent size 4
Number of pages allocated 854
Number of pages used 834
Number of data pages 0
Number of rows 0
Partition partnum 4198988
Partition lockid 6360351
Extents
Logical Page Physical Page Size Physical Pages
0 4:501519 854 854
Jack,
Did the oncheck -pe. It and the oncheck -pt both show that the table has 93
extents, as does a SELECT against sysmaster:sysextents. So, everything is in
agreement there. Just trying to see why 93 extents is causing a problem, when
the theoretical (I know) max extents is 237 for a 2K page. So I was hoping to
be able to print the TBLSpace tablespace page for this particular table.
I should be able to get that based on the partnum, shouldn't I?
OK, there are four sets of information that all have to fit on the table's
partition (tablespace tablespace) header page (prior to v11.70):
1. Basic table info (all the stuff shown in the sysactptnhdr table in
sysmaster which is fixed size)
2. Info about attached index keys
3. Info about any "special" columns which includes varchar, lvarchar,
UDTs, blobs (dumb and smart)
4. Extents
The more of #'s 2 & 3 objects that you have in the table the fewer extents
a table can have (at least before 11.70). The maximum number for a table
with no special columns and only detached indexes (or none at all) is 237
for a table in a dbspace with 2K pages (more than double that for tables in
dbspaces with wider pages). How much space is taken up by each "special"
column is not documented and the space taken by attached index keys
descriptions is also not documented anywhere that I am aware of, but I
would guess it is about 64 bytes - the same as represented by the "part*"
columns in the sysindexes view. However, unless you have some very old
indexes inherited from a version prior to 7.30, it is unlikely that you
have attached indexes. If you have many VARCHAR and LVARCHAR columns it is
possible that, for this particular table, 93 is the maximum number of
extents.
It is also possible that you are actually hitting the 16million page
problem rather than running out of extents. How many pages does the table
have in it? A single table partition is limited to 2^24 pages or
16,777,216 pages. The solution to this is one of two options:
1. Move the table to a dbspace with wider pages, or
2. Partition or fragment the table into multiple partitions each of
which can hold 2^24 pages.
If you really are hitting an extent limit these are the options:
1. Reorg the table into fewer extents. There are several ways
2. Move it to a dbspace with wider pages so that the extent list is
bigger
3. Partition or fragment the table into multiple partitions each of
which could have the same maximum number of extents
4. Upgrade to v11.70 which permits additional extent table pages to be
attached to the partition header page expanding the maximum number of
extents to 32767 which with extent doubling turns out to be virtually
unlimited (given the 2^24 pages limit).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Dec 26, 2012 at 11:38 AM, MARK COLLINS <markc@myfastmail.com> wrote:
> IDS 11.50.fc6
> HP-UX 11.31 PA-RISC
>
> Users are reporting that they get -271/-136 errors when they try to update
> an
> existing row. -271 is "Could not insert new row into the table", and -136
> is
> "ISAM error: no more extents". The table is in one dbspace, which has over
> 2GB
> free, and there are two indexes in a second dbspace, also with over 2GB
> free.
> finderr indicates that the problem might be the number of extents, and
> gives a
> formula based on page size for the theoretical maximum number of extents
> for a
> table (237 in a 2K page). The table currently has only 93 extents.
>
> Finderr also indicates that the actual max number of extents may be less
> than
> the theoretical max if there are a lot of other objects with large numbers
> of
> extents, filling of the tablespace tablespace.
>
> How do I look at the tablespace tablespace info for this dbspace? I have
> done
> an oncheck -pt and -pT for the table, but did not see anything that would
> indicate the problem.
>
> Thanks in advance.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d0447882d910a8e04d1c47e1c
Mark:
Curiouser and curiouser. You have no attached index keys, no special
columns, only 93 extents (admittedly they are tiny extents besides the
second extent), and fewer than 58,000 pages allocated. A reorg after
setting the table's NEXT SIZE to something reasonable like 10,000 pages
(20000K) would certainly help performance if nothing else, but I see no
reason why this table is returning -136 errors!
As an initial work around, I would actually forgo the usual reorg
techniques and unload, drop, rebuild (with EXTENT SIZE and NEXT SIZE set),
and reload to eliminate whatever quirk in the table's internal structure is
triggering this obvious bug. I would also take a recent archive and sent
it to IBM with a PMR so they can track down the bug. Finally, I would
suggest upgrading to 11.70.<latest> in case this bug was accidentally fixed
in a later release.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Dec 26, 2012 at 12:00 PM, MARK COLLINS <markc@myfastmail.com> wrote:
> Jacques,
>
> Here is the output from oncheck -pt:
>
> TBLspace Report for my_db:theowner.my_table
>
> Physical Address 11:6504131
>
> Creation date 01/14/2012 07:58:59
>
> TBLspace Flags 800801 Page Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 910
>
> Number of special columns 0
>
> Number of keys 0
>
> Number of extents 93
>
> Current serial value 114729
>
> Current SERIAL8 value 1
>
> Current BIGSERIAL value 1
>
> Current REFID value 1
>
> Pagesize (k) 2
>
> First extent size 59832
>
> Next extent size 16
>
> Number of pages allocated 57344
>
> Number of pages used 57341
>
> Number of data pages 57326
>
> Number of rows 114652
>
> Partition partnum 6360351
>
> Partition lockid 6360351
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 13:1417393 880 880
>
> 880 13:1418337 53504 53504
>
> 54384 7:3518776 8 8
>
> 54392 13:622083 8 8
>
> 54400 7:3234544 8 8
>
> 54408 13:3572125 8 8
>
> 54416 13:3576868 8 8
>
> 54424 13:3579072 8 8
>
> 54432 10:3863141 8 8
>
> 54440 13:1753943 8 8
>
> 54448 13:1755833 8 8
>
> 54456 13:1267745 8 8
>
> 54464 13:1762440 8 8
>
> 54472 10:4451910 8 8
>
> 54480 13:1810795 8 8
>
> 54488 13:1762663 8 8
>
> 54496 13:1752975 16 16
>
> 54512 13:1241267 16 16
>
> 54528 13:3112718 16 16
>
> 54544 13:3614460 16 16
>
> 54560 13:3602612 16 16
>
> 54576 13:3584300 16 16
>
> 54592 13:3183329 16 16
>
> 54608 13:3404296 16 16
>
> 54624 13:3600956 16 16
>
> 54640 13:3675577 16 16
>
> 54656 13:3692885 16 16
>
> 54672 13:1949986 16 16
>
> 54688 13:3676169 16 16
>
> 54704 13:1918795 16 16
>
> 54720 13:3855532 16 16
>
> 54736 13:3865369 16 16
>
> 54752 13:3886923 32 32
>
> 54784 13:3937671 32 32
>
> 54816 13:4022689 32 32
>
> 54848 13:4005625 32 32
>
> 54880 13:4043609 32 32
>
> 54912 13:4085045 32 32
>
> 54944 13:4096925 32 32
>
> 54976 13:4115852 32 32
>
> 55008 13:4112273 32 32
>
> 55040 13:1241711 32 32
>
> 55072 13:4185974 32 32
>
> 55104 13:4211094 32 32
>
> 55136 13:4215254 32 32
>
> 55168 13:3884487 32 32
>
> 55200 13:4314266 32 32
>
> 55232 13:4282174 32 32
>
> 55264 13:4359550 64 64
>
> 55328 13:4443091 64 64
>
> 55392 13:4525171 64 64
>
> 55456 13:4538307 64 64
>
> 55520 13:4650789 64 64
>
> 55584 13:4679978 64 64
>
> 55648 13:4807723 64 64
>
> 55712 13:4787419 64 64
>
> 55776 13:4886502 64 64
>
> 55840 13:4930351 64 64
>
> 55904 13:5319068 64 64
>
> 55968 13:5263316 64 64
>
> 56032 13:5344668 64 64
>
> 56096 13:4617613 64 64
>
> 56160 13:4619799 64 64
>
> 56224 13:5527252 64 64
>
> 56288 13:5450900 128 128
>
> 56416 13:5567194 128 128
>
> 56544 13:5853036 128 128
>
> 56672 13:6115386 128 128
>
> 56800 13:6239346 128 128
>
> 56928 13:5091179 128 128
>
> 57056 7:4647717 8 8
>
> 57064 7:4660316 8 8
>
> 57072 7:4666652 8 8
>
> 57080 7:4674252 8 8
>
> 57088 7:4669144 8 8
>
> 57096 7:4677524 8 8
>
> 57104 7:4659117 8 8
>
> 57112 7:4667916 8 8
>
> 57120 7:4687796 8 8
>
> 57128 7:4691916 8 8
>
> 57136 7:4695064 16 16
>
> 57152 7:4706302 16 16
>
> 57168 7:4702336 16 16
>
> 57184 7:4757139 16 16
>
> 57200 7:4932413 16 16
>
> 57216 7:4882113 16 16
>
> 57232 7:4873349 16 16
>
> 57248 7:4845273 16 16
>
> 57264 7:4892843 16 16
>
> 57280 7:4695120 16 16
>
> 57296 7:4858961 16 16
>
> 57312 7:4980557 16 16
>
> 57328 7:4994409 16 16
>
> Index ix123_32 fragment partition indexdbs in DBspace indexdbs
>
> Physical Address 4:706359
>
> Creation date 12/23/2012 10:17:48
>
> TBLspace Flags 801 Page Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 910
>
> Number of special columns 0
>
> Number of keys 1
>
> Number of extents 1
>
> Current serial value 1
>
> Current SERIAL8 value 1
>
> Current BIGSERIAL value 1
>
> Current REFID value 1
>
> Pagesize (k) 2
>
> First extent size 946
>
> Next extent size 4
>
> Number of pages allocated 946
>
> Number of pages used 798
>
> Number of data pages 0
>
> Number of rows 0
>
> Partition partnum 4198987
>
> Partition lockid 6360351
>
> Extents
>
> Logical Page Physical Page Size Physical Pages
>
> 0 4:856813 946 946
>
> Index pk_my_tableid fragment partition indexdbs in DBspace indexdbs
>
> Physical Address 4:706360
>
> Creation date 12/23/2012 10:17:51
>
> TBLspace Flags 801 Page Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 910
>
> Number of special columns 0
>
> Number of keys 1
>
> Number of extents 1
>
> Current serial value 1
>
> Current SERIAL8 value 1
>
> Current BIGSERIAL value 1
>
> Current REFID value 1
>
> Pagesize (k) 2
>
> First extent size 854
>
> Next extent size 4
>
> Number of pages allocated 854
>
> Number of pages used 834
>
> Number of data pages 0
>
> Number of rows 0
>
> Partition partnum 4198988
>
> Partition lockid 6360351
>
> Ext
The data in sysactptnhdr, sysextents, and sysptnkey contain the data from
each partition's tablespace tablespace page, also the info from the first
two of these tables is what is reported in the oncheck -pt/T reports which
you already have.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Dec 26, 2012 at 12:05 PM, MARK COLLINS <markc@myfastmail.com> wrote:
> Jack,
>
> Did the oncheck -pe. It and the oncheck -pt both show that the table has 93
> extents, as does a SELECT against sysmaster:sysextents. So, everything is
> in
> agreement there. Just trying to see why 93 extents is causing a problem,
> when
> the theoretical (I know) max extents is 237 for a 2K page. So I was hoping
> to
> be able to print the TBLSpace tablespace page for this particular table.
>
> I should be able to get that based on the partnum, shouldn't I?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d0447f05e475e7204d1c4c2cb
How many pages are allocated ? you might have hit the hard limit of 16M
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK
COLLINS
Sent: Wednesday, December 26, 2012 10:39 AM
To: ids@iiug.org
Subject: unable to update rows [29130]
IDS 11.50.fc6
HP-UX 11.31 PA-RISC
Users are reporting that they get -271/-136 errors when they try to update
an
existing row. -271 is "Could not insert new row into the table", and -136 is
"ISAM error: no more extents". The table is in one dbspace, which has over
2GB
free, and there are two indexes in a second dbspace, also with over 2GB
free.
finderr indicates that the problem might be the number of extents, and gives
a
formula based on page size for the theoretical maximum number of extents for
a
table (237 in a 2K page). The table currently has only 93 extents.
Finderr also indicates that the actual max number of extents may be less
than
the theoretical max if there are a lot of other objects with large numbers
of
extents, filling of the tablespace tablespace.
How do I look at the tablespace tablespace info for this dbspace? I have
done
an oncheck -pt and -pT for the table, but did not see anything that would
indicate the problem.
Thanks in advance.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Art,
Thanks for the detailed breakdown of what goes into that info. I
took an internals class over a decade ago, but I can't find the book
from that so I can't refresh my memory. This is good info for
future problems.
We are nowhere near the maximum. This table is around 500,000
pages. Also, no special column types. There are two detached
indexes. So it's very confusing why we're stuck at 93 extents. I
was initially confused by the output from finderr, which says "The
upper limit of extents per table depends on the page size of the
dbspace it is in, as well as how much space is consumed by other
entries on its tblspace page." Based on you info, I can now see
that "other entries on its tblspace page" must refer to the attached
indexes and special columns.
We are trying to get free time from the users to rebuild the table.
>> OK, there are four sets of information that all have to fit on the table's
>> partition (tablespace tablespace) header page (prior to v11.70):
>>
>> 1. Basic table info (all the stuff shown in the sysactptnhdr table in
>>
>> sysmaster which is fixed size)
>>
>> 2. Info about attached index keys
>>
>> 3. Info about any "special" columns which includes varchar, lvarchar,
>>
>> UDTs, blobs (dumb and smart)
>>
>> 4. Extents
>>
>> The more of #'s 2 & 3 objects that you have in the table the fewer extents
>> a table can have (at least before 11.70). The maximum number for a table
>> with no special columns and only detached indexes (or none at all) is 237
>> for a table in a dbspace with 2K pages (more than double that for tables in
>> dbspaces with wider pages). How much space is taken up by each "special"
>> column is not documented and the space taken by attached index keys
>> descriptions is also not documented anywhere that I am aware of, but I
>> would guess it is about 64 bytes - the same as represented by the "part*"
>> columns in the sysindexes view. However, unless you have some very old
>> indexes inherited from a version prior to 7.30, it is unlikely that you
>> have attached indexes. If you have many VARCHAR and LVARCHAR columns it is
>> possible that, for this particular table, 93 is the maximum number of
>> extents.
>>
>> It is also possible that you are actually hitting the 16million page
>> problem rather than running out of extents. How many pages does the table
>> have in it? A single table partition is limited to 2^24 pages or
>> 16,777,216 pages. The solution to this is one of two options:
>>
>> 1. Move the table to a dbspace with wider pages, or
>>
>> 2. Partition or fragment the table into multiple partitions each of
>>
>> which can hold 2^24 pages.
>>
>> If you really are hitting an extent limit these are the options:
>>
>> 1. Reorg the table into fewer extents. There are several ways
>>
>> 2. Move it to a dbspace with wider pages so that the extent list is
>>
>> bigger
>>
>> 3. Partition or fragment the table into multiple partitions each of
>>
>> which could have the same maximum number of extents
>>
>> 4. Upgrade to v11.70 which permits additional extent table pages to be
>>
>> attached to the partition header page expanding the maximum number of
>>
>> extents to 32767 which with extent doubling turns out to be virtually
>>
>> unlimited (given the 2^24 pages limit).
>>
>> Art
>>
>> Art S. Kagel
>> Advanced DataTools (www.advancedatatools.com)
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
>> other organization with which I am associated either explicitly,
>> implicitly, or by inference. Neither do those opinions reflect those of
>> other individuals affiliated with any entity with which I am affiliated nor
>> those of the entities themselves.
>>
>> On Wed, Dec 26, 2012 at 11:38 AM, MARK COLLINS <markc@myfastmail.com> wrote:
>>
>> > IDS 11.50.fc6
>> > HP-UX 11.31 PA-RISC
>> >
>> > Users are reporting that they get -271/-136 errors when they try to update
>> > an
>> > existing row. -271 is "Could not insert new row into the table", and -136
>> > is
>> > "ISAM error: no more extents". The table is in one dbspace, which has over
>> > 2GB
>> > free, and there are two indexes in a second dbspace, also with over 2GB
>> > free.
>> > finderr indicates that the problem might be the number of extents, and
>> > gives a
>> > formula based on page size for the theoretical maximum number of extents
>> > for a
>> > table (237 in a 2K page). The table currently has only 93 extents.
>> >
>> > Finderr also indicates that the actual max number of extents may be less
>> > than
>> > the theoretical max if there are a lot of other objects with large numbers
>> > of
>> > extents, filling of the tablespace tablespace.
>> >
>> > How do I look at the tablespace tablespace info for this dbspace? I have
>> > done
>> > an oncheck -pt and -pT for the table, but did not see anything that would
>> > indicate the problem.
>> >
>> > Thanks in advance.
>> >
>> >
>> >
>> >
*******************************************************************************
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>> >
>>
>>
Art, Yes, the DROP/CREATE/LOAD was the approach we were going to use. Good to hear that you think this is the better option. We'll have to see about that PMR. And, 11.70.whatever is on the list of things for the next year. Thanks again. Mark
Original Post: Art, Yes, the DROP/CREATE/LOAD was the approach we were going to use. Good to hear that you think this is the better option. We'll have to see about that PMR. And, 11.70.whatever is on the list of things for the next year. Thanks again. Mark Response: Before you go the pmr route, could you check your email, I sent an email rather then responding to the forum about getting some further output. Jacques Renaut IBM Informix Advanced Support APD Team
To add to the weirdness, even though the users are getting the -271/-136 errors, the database is actually updating the rows with the correct data. I was also thinking it odd that the error popped up on UPDATEs instead of INSERTs, but I realize that index changes could cause that to happen. Regardless, the dbspace with the detached indexes has plenty of space, and we're nowhere near any hard limits that I can think of.
Jacques, Thanks, I have replied to that message.
Hmm, I am finally remembering that I actually saw a similar problem last year and it was resolved on the forum here. Is it possible that there is an UPDATE TRIGGER on this table that is maybe logging the update to another table and it is actually that audit table that has run out of extents or hit the maximum number of pages and not the one you are updating? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 26, 2012 at 12:55 PM, MARK COLLINS <markc@myfastmail.com> wrote: > To add to the weirdness, even though the users are getting the -271/-136 > errors, the database is actually updating the rows with the correct data. I > was also thinking it odd that the error popped up on UPDATEs instead of > INSERTs, but I realize that index changes could cause that to happen. > Regardless, the dbspace with the detached indexes has plenty of space, and > we're nowhere near any hard limits that I can think of. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3022399924cf6204d1c54dfb
Not sure if the problem still persists, but running the following query
will tell you hoa many extents your tables have and how many more they can
grow:
SELECT
t.dbsname, t.tabname,
pt.nextns ext_current,
pt.nextns + trunc(pg_frcnt / 8) ext_max,
trunc(pg_frcnt / 8) ext_free,
d.name,
pt.nptotal,
pt.npused ,
pt.npdata
FROM
sysmaster:systabnames t,
sysmaster:syspaghdr p,
sysmaster:sysptnhdr pt,
sysmaster:sysdbstab d
WHERE
pt.partnum = t.partnum AND
p.pg_partnum =
sysmaster:partaddr(sysmaster:partdbsnum(t.partnum),1) AND
p.pg_pagenum = sysmaster:partpagenum(t.partnum) AND
t.dbsname NOT IN ('sysmaster') and
d.dbsnum = sysmaster:partdbsnum(t.partnum)
ORDER by 4, 1, 2
Add filter(s) to restrict to your table.
It will also show you how many pages it's using.
If your table if fragmented be carefull withe the filters. Also consider
the triggers as Art mentioned.
Regards.
On Wed, Dec 26, 2012 at 4:38 PM, MARK COLLINS <markc@myfastmail.com> wrote:
> IDS 11.50.fc6
> HP-UX 11.31 PA-RISC
>
> Users are reporting that they get -271/-136 errors when they try to update
> an
> existing row. -271 is "Could not insert new row into the table", and -136
> is
> "ISAM error: no more extents". The table is in one dbspace, which has over
> 2GB
> free, and there are two indexes in a second dbspace, also with over 2GB
> free.
> finderr indicates that the problem might be the number of extents, and
> gives a
> formula based on page size for the theoretical maximum number of extents
> for a
> table (237 in a 2K page). The table currently has only 93 extents.
>
> Finderr also indicates that the actual max number of extents may be less
> than
> the theoretical max if there are a lot of other objects with large numbers
> of
> extents, filling of the tablespace tablespace.
>
> How do I look at the tablespace tablespace info for this dbspace? I have
> done
> an oncheck -pt and -pT for the table, but did not see anything that would
> indicate the problem.
>
> Thanks in advance.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf300fb151e34e4004d1c587e2
DING!!! DING!!! DING!!! WE HAVE A WINNER. Yes, there is an UPDATE trigger on this table. And yes, the audit table to which it writes has an excessive number (228) of extents. That's the table we need to reorg (although it wouldn't hurt to do the base table, too). Thank you for all the help. >> Hmm, I am finally remembering that I actually saw a similar problem last >> year and it was resolved on the forum here. Is it possible that there is >> an UPDATE TRIGGER on this table that is maybe logging the update to another >> table and it is actually that audit table that has run out of extents or >> hit the maximum number of pages and not the one you are updating? >> >> Art
Good to hear. I've been in and out - glad to hear somebody got you the = proper clue. j. On Dec 26, 2012, at 1:39 PM, MARK COLLINS wrote: > DING!!! DING!!! DING!!! WE HAVE A WINNER.=20 >=20 > Yes, there is an UPDATE trigger on this table. And yes, the audit = table to=20 > which it writes has an excessive number (228) of extents. That's the = table we=20 > need to reorg (although it wouldn't hurt to do the base table, too).=20= >=20 > Thank you for all the help.=20 >=20 >>> Hmm, I am finally remembering that I actually saw a similar problem = last=20 >>> year and it was resolved on the forum here. Is it possible that = there is=20 >>> an UPDATE TRIGGER on this table that is maybe logging the update to = another=20 >>> table and it is actually that audit table that has run out of = extents or=20 >>> hit the maximum number of pages and not the one you are updating?=20 >>>=20 >>> Art=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
Related threads
- RE: transfer via comp.databases.informix
- dbimport hangs
- Assert Failed errno 271 ISAM ERR -12803
- Syslocks table