IDS Forum

RE: Create index errors

Posted By: Mitchell, Jeffrey J.
Date: Friday, 25 September 2009, at 10:43 a.m.

In Response To: Re: Create index errors (JACQUES RENAUT)

Actually, I think we've found a work-around.

Even though the indexes (nor the table) are fragmented, I tried building the indexes with a pdqpriority set, and the indexes built successfully.

Thanks to all for your input.

Jeff Mitchell

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACQUES RENAUT
Sent: Friday, September 25, 2009 9:37 AM
To: ids@iiug.org
Subject: Re: Create index errors [17192]

>From looking at the code there appears to be 3 reasons a 136 error would be
reported. First would be if the partition page you were trying to add pages to
was exceeding either 1,048,575 (if it was the tablespace tablespace) or
16,777,215 pages (this case it should likely be the 16 million page case).
This seems unlikely as your other posts indicate your dbspaces don't even have
that much free space. Second reason is the partition page itself didn't have
enough free space on it to allocate enough room for a new extent structure
(which is 8 bytes). If this is a detached index, this also seems unlikely as
the partition page for the new index would be fairly empty. The third is when
the code is trying to realloc a structure in memory and we got a null pointer
on the realloc call. You haven't mentioned if you were getting any unable to
allocate more memory messages in your MSGPATH file or were in any sort of out
of memory condition. Since none of those conditions sound too reasonable, I'm
left to wonder how much space do you have in your temp dbspaces (DBSPACETEMP)?
There is definately going to be a sort partition that needs to be created for
the index build and I wonder if the partition that is growing too big is
actually a temp table for the sort. Perhaps you should try monitoring your
DBSPACETEMP usage while the index is building and if you see it use ~16
million pages that could be the issue. If that is the problem, it seems like
some possible options would be to fragment the index, or maybe (not as sure on
this suggestion) would be have more then 1 dbspace listed in your DBSPACETEMP
so that sort would then use more then 1 partition page.

You wrote:
We're running Informix 10.00.FC8 on AIX 5.3.0.0.

I'm trying to build a multi-column index on a table with just over 45 million
rows, and I'm receiving the following errors:

212: Cannot add index.

136: ISAM error: no more extents

At first I thought it was because the last column in the index was a varchar,
so I removed it, but it didn't make any difference.

Then I tried to create the index (without the varchar column) in an empty
dbspace (with three 6GB chunks), but that made no difference either.

More notes:

* The table is actually in two extents (first size is 9,000,000 and next is
900,000).

* I have several single-column indexes that built correctly - none with more
than two extents.

* This database (created before I started here) has all tables and indexes in
the same dbspace - but all indexes use the "in dbs1" clause.

Thanks,

[cid:image003.jpg@01CA3D2D.F7201CE0]Jeffrey J. Mitchell
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0500 | Cell 402-321-7443 |
jjmitchell@west.com<mailto:jjmitchell@west.com>

This electronic message transmission, including any attachments, contains
information from West Corporation which may be confidential or privileged. The
information is intended to be for the use of the individual or entity named
above. If you are not the intended recipient, be aware that any disclosure,
copying, distribution or use of the contents of this information is
prohibited.

If you have received this electronic transmission in error, please notify the
sender immediately by a "reply to sender only" message and destroy all
electronic and hard copies of the communication, including attachments.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Create index errors
  Mitchell, Jeffrey J. -- Thursday, 24 September 2009, at 4:45 p.m.
  • Re: Create index errors
    SIMON COYNE -- Thursday, 24 September 2009, at 4:58 p.m.
    • FW: Create index errors
      Mitchell, Jeffrey J. -- Thursday, 24 September 2009, at 5:03 p.m.
  • Re: Create index errors
    SIMON COYNE -- Thursday, 24 September 2009, at 5:15 p.m.
    • RE: Create index errors
      Mitchell, Jeffrey J. -- Thursday, 24 September 2009, at 5:27 p.m.
      • RE: Create index errors
        Everett Mills -- Thursday, 24 September 2009, at 5:30 p.m.
        • RE: Create index errors
          Mitchell, Jeffrey J. -- Thursday, 24 September 2009, at 5:33 p.m.
          • RE: Create index errors
            Mark Tyrer -- Friday, 25 September 2009, at 2:40 a.m.
            • Re: RE: Create index errors
              NETSKY LIAO -- Friday, 25 September 2009, at 2:48 a.m.
              • Re: RE: Create index errors
                NETSKY LIAO -- Friday, 25 September 2009, at 3:02 a.m.
                • RE: RE: Create index errors
                  Mitchell, Jeffrey J. -- Friday, 25 September 2009, at 8:59 a.m.
              • RE: RE: Create index errors
                Mitchell, Jeffrey J. -- Friday, 25 September 2009, at 8:58 a.m.
            • RE: Create index errors
              Mitchell, Jeffrey J. -- Friday, 25 September 2009, at 8:58 a.m.
  • Re: Create index errors
    JACQUES RENAUT -- Friday, 25 September 2009, at 10:37 a.m.
    • RE: Create index errors
      Mitchell, Jeffrey J. -- Friday, 25 September 2009, at 10:43 a.m.
  • Re: Create index errors
    Art Kagel -- Saturday, 26 September 2009, at 8:47 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.