IDS Forum

Re: unable to update rows

Posted By: MARK COLLINS
Date: Wednesday, 26 December 2012, at 12:35 p.m.

In Response To: Re: unable to update rows (Art Kagel)

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.
>> >
>> >
>>
>>

Messages In This Thread

• unable to update rows
  MARK COLLINS -- Wednesday, 26 December 2012, at 11:38 a.m.
  • Re: unable to update rows
    Jack Parker -- Wednesday, 26 December 2012, at 11:44 a.m.
    • Re: unable to update rows
      MARK COLLINS -- Wednesday, 26 December 2012, at 12:05 p.m.
      • Re: unable to update rows
        Art Kagel -- Wednesday, 26 December 2012, at 12:28 p.m.
  • Re: unable to update rows
    JACQUES RENAUT -- Wednesday, 26 December 2012, at 11:49 a.m.
    • Re: unable to update rows
      MARK COLLINS -- Wednesday, 26 December 2012, at 12:00 p.m.
      • Re: unable to update rows
        Art Kagel -- Wednesday, 26 December 2012, at 12:22 p.m.
        • Re: unable to update rows
          MARK COLLINS -- Wednesday, 26 December 2012, at 12:39 p.m.
          • Re: unable to update rows
            JACQUES RENAUT -- Wednesday, 26 December 2012, at 12:49 p.m.
        • Re: unable to update rows
          MARK COLLINS -- Wednesday, 26 December 2012, at 12:55 p.m.
          • Re: unable to update rows
            Art Kagel -- Wednesday, 26 December 2012, at 1:07 p.m.
            • Re: unable to update rows
              MARK COLLINS -- Wednesday, 26 December 2012, at 1:39 p.m.
              • Re: unable to update rows
                Jack Parker -- Wednesday, 26 December 2012, at 1:47 p.m.
  • Re: unable to update rows
    Art Kagel -- Wednesday, 26 December 2012, at 12:09 p.m.
    • Re: unable to update rows
      MARK COLLINS -- Wednesday, 26 December 2012, at 12:35 p.m.
  • RE: unable to update rows
    Paul Watson -- Wednesday, 26 December 2012, at 12:34 p.m.
  • Re: unable to update rows
    Fernando Nunes -- Wednesday, 26 December 2012, at 1:23 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.