IDS Forum

Re: Create index errors

Posted By: JACQUES RENAUT
Date: Friday, 25 September 2009, at 10:37 a.m.

In Response To: Create index errors (Mitchell, Jeffrey J.)

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.

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.