IDS Forum

Re: unable to update rows

Posted By: Art Kagel
Date: Wednesday, 26 December 2012, at 12:09 p.m.

In Response To: unable to update rows (MARK COLLINS)

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

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.