Create index errors

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.

Apologies if I'm teaching Grandma to suck eggs, but have you confirmed the number of extents that are being used? Those figures look suspiciously like the initial values used when creating a table. Simon

Those are the initial/next sizes when creating the table. And when I loaded the table, it allocated one "next" extent. The problem isn't with the table, per se - it's when I'm creating the index. Jeff Mitchell -----Original Message----- From: Plugge, Joe R. Sent: Thursday, September 24, 2009 4:02 PM To: Mitchell, Jeffrey J. Subject: FW: Create index errors [17165] -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SIMON COYNE Sent: Thursday, September 24, 2009 3:58 PM To: ids@iiug.org Subject: Re: Create index errors [17165] Apologies if I'm teaching Grandma to suck eggs, but have you confirmed the number of extents that are being used? Those figures look suspiciously like the initial values used when creating a table. Simon ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Sorry - bad bit of reading on my part! Could it be number of extents/extent size for the index?

I have two tables for which this is true. All of the single column indexes built correctly, and none of them take up more than two extents. In the case of the failing indexes (two per table), there are three or four columns in the index, and the last column is a varchar. Even if I remove the varchar column from the index (still leaving a multi-column index), the index creation fails with a "no more extents" message. Apologies for not clarifying all of this in my original post. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SIMON COYNE Sent: Thursday, September 24, 2009 4:16 PM To: ids@iiug.org Subject: Re: Create index errors [17167] Sorry - bad bit of reading on my part! Could it be number of extents/extent size for the index? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

What does onstat -d tell you? Are you running short of space in the dbspace you're creating your index in? --EEM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mitchell, Jeffrey J. Sent: Thursday, September 24, 2009 4:27 PM To: ids@iiug.org Subject: RE: Create index errors [17168] I have two tables for which this is true. All of the single column indexes built correctly, and none of them take up more than two extents. In the case of the failing indexes (two per table), there are three or four columns in the index, and the last column is a varchar. Even if I remove the varchar column from the index (still leaving a multi-column index), the index creation fails with a "no more extents" message. Apologies for not clarifying all of this in my original post. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SIMON COYNE Sent: Thursday, September 24, 2009 4:16 PM To: ids@iiug.org Subject: Re: Create index errors [17167] Sorry - bad bit of reading on my part! Could it be number of extents/extent size for the index? ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Nope - as a matter of fact, the first attempt went to a dbspace with over 15 million free pages. The second was to an empty dbspace with over 6 million free pages. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Everett Mills Sent: Thursday, September 24, 2009 4:31 PM To: ids@iiug.org Subject: RE: Create index errors [17169] What does onstat -d tell you? Are you running short of space in the dbspace you're creating your index in? --EEM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mitchell, Jeffrey J. Sent: Thursday, September 24, 2009 4:27 PM To: ids@iiug.org Subject: RE: Create index errors [17168] I have two tables for which this is true. All of the single column indexes built correctly, and none of them take up more than two extents. In the case of the failing indexes (two per table), there are three or four columns in the index, and the last column is a varchar. Even if I remove the varchar column from the index (still leaving a multi-column index), the index creation fails with a "no more extents" message. Apologies for not clarifying all of this in my original post. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SIMON COYNE Sent: Thursday, September 24, 2009 4:16 PM To: ids@iiug.org Subject: Re: Create index errors [17167] Sorry - bad bit of reading on my part! Could it be number of extents/extent size for the index? ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, As a quick work around, you could fragment the index across several dbspaces Regards Mark > To: ids@iiug.org > From: JJMitchell@west.com > Subject: RE: Create index errors [17170] > Date: Thu, 24 Sep 2009 17:33:20 -0400 > > Nope - as a matter of fact, the first attempt went to a dbspace with over 15 > million free pages. The second was to an empty dbspace with over 6 million > free pages. > > Jeff Mitchell > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Everett > Mills > Sent: Thursday, September 24, 2009 4:31 PM > To: ids@iiug.org > Subject: RE: Create index errors [17169] > > What does onstat -d tell you? Are you running short of space in the > dbspace you're creating your index in? > > --EEM > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Mitchell, Jeffrey J. > Sent: Thursday, September 24, 2009 4:27 PM > To: ids@iiug.org > Subject: RE: Create index errors [17168] > > I have two tables for which this is true. All of the single column > indexes > built correctly, and none of them take up more than two extents. > > In the case of the failing indexes (two per table), there are three or > four > columns in the index, and the last column is a varchar. Even if I remove > the > varchar column from the index (still leaving a multi-column index), the > index > creation fails with a "no more extents" message. > > Apologies for not clarifying all of this in my original post. > > Jeff Mitchell > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > SIMON > COYNE > Sent: Thursday, September 24, 2009 4:16 PM > To: ids@iiug.org > Subject: Re: Create index errors [17167] > > Sorry - bad bit of reading on my part! > > Could it be number of extents/extent size for the index? > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Join the all-new Windows Live Messenger family http://get.live.com

Did it up to the limits of informix ? See $INFORMIXDIR/release/en_us/0333/ids_unix_relnotes_xx.xx.txt (xx instead of IDS version number)

* The table is actually in two extents (first size is 9,000,000 and next is 900,000). The index extent build base on the table's extent size..It may be the dbspace has not enough pages to store index data. Please use "oncheck -pe" to check it has enough continuous pages. This is just speculation and do not represent the facts.

Tried it. No difference. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mark Tyrer Sent: Friday, September 25, 2009 1:41 AM To: ids@iiug.org Subject: RE: Create index errors [17181] Hi, As a quick work around, you could fragment the index across several dbspaces Regards Mark > To: ids@iiug.org > From: JJMitchell@west.com > Subject: RE: Create index errors [17170] > Date: Thu, 24 Sep 2009 17:33:20 -0400 > > Nope - as a matter of fact, the first attempt went to a dbspace with over 15 > million free pages. The second was to an empty dbspace with over 6 million > free pages. > > Jeff Mitchell > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Everett > Mills > Sent: Thursday, September 24, 2009 4:31 PM > To: ids@iiug.org > Subject: RE: Create index errors [17169] > > What does onstat -d tell you? Are you running short of space in the > dbspace you're creating your index in? > > --EEM > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Mitchell, Jeffrey J. > Sent: Thursday, September 24, 2009 4:27 PM > To: ids@iiug.org > Subject: RE: Create index errors [17168] > > I have two tables for which this is true. All of the single column > indexes > built correctly, and none of them take up more than two extents. > > In the case of the failing indexes (two per table), there are three or > four > columns in the index, and the last column is a varchar. Even if I remove > the > varchar column from the index (still leaving a multi-column index), the > index > creation fails with a "no more extents" message. > > Apologies for not clarifying all of this in my original post. > > Jeff Mitchell > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > SIMON > COYNE > Sent: Thursday, September 24, 2009 4:16 PM > To: ids@iiug.org > Subject: Re: Create index errors [17167] > > Sorry - bad bit of reading on my part! > > Could it be number of extents/extent size for the index? > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Join the all-new Windows Live Messenger family http://get.live.com ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Checked them - we aren't hitting any of the limits. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NETSKY LIAO Sent: Friday, September 25, 2009 1:48 AM To: ids@iiug.org Subject: Re: RE: Create index errors [17182] Did it up to the limits of informix ? See $INFORMIXDIR/release/en_us/0333/ids_unix_relnotes_xx.xx.txt (xx instead of IDS version number) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

We tried building the table/indexes in a completely empty dbspace. Still, the multi-column indexes failed. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NETSKY LIAO Sent: Friday, September 25, 2009 2:03 AM To: ids@iiug.org Subject: Re: RE: Create index errors [17183] * The table is actually in two extents (first size is 9,000,000 and next is 900,000). The index extent build base on the table's extent size..It may be the dbspace has not enough pages to store index data. Please use "oncheck -pe" to check it has enough continuous pages. This is just speculation and do not represent the facts. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

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.

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.

It is because the free space in the dbspace(s) in which you are trying to build the index is so fragmented that the engine cannot build it with a small number of large extents it has to use the fragmented pieces that it finds (see the oncheck -pe report to see the free extents that are available). Either add a new chunk to the dbspace, use a different dbspace with from large free extents, or reorganize the existing tables and indexes so that the remaining free space is coalesced into one or a few bigger extents. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Sep 24, 2009 at 4:45 PM, Mitchell, Jeffrey J. <JJMitchell@west.com>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. > > --000e0ce0b0f2b0ec99047484867c