Index creation while Fragment Table

Hi All, Running Informix 12.1.FC4 on Solaris 10 Interesting problem: I have a 15GB table that I want to fragment. The table has 2 detahced indexes, created in a separate index dbspace. Neither index will be used for the fragmentation scheme. The fragmentation seems to go swimmingly for the actual data (as seen by watching onstat -d), however, Informix seems to be creating the index for the scheme **in the indexes dbspace** instead of the table fragment's dbspace(s). This was all confirmed by monitoring onstat -d and watching the indexes dbspace drop to 0 before the error, while the data dbspaces have plenty of pages free. Why would the new index be automatically directed to the separate dbspace? And, is it possible to override this behavior??? Thanks, Michael Hoffman -------------------- alter fragment on table event_queue INIT fragment by expression partition part_1 topic[1] = '1' in ensdb, partition part_2 topic[1] = '2' in bizdb,partition part_rem remainder in ensdb ; ERROR: 212: Cannot add index. 131: ISAM error: no free disk space ---------------

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } When an index is in a separate space on a fragmented table, then the index item will have to contain the long rowid which is the partition number plus the rowid of the row within the fragment. Since the item has a 64 bit pointer rather than the 32 bit pointer that the unfragmrnted table required, the space required to hold the index will need to be larger. Sent from Yahoo Mail for iPad On Wednesday, March 9, 2016, 6:42 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: Hi All, Running Informix 12.1.FC4 on Solaris 10 Interesting problem: I have a 15GB table that I want to fragment. The table has 2 detahced indexes, created in a separate index dbspace. Neither index will be used for the fragmentation scheme. The fragmentation seems to go swimmingly for the actual data (as seen by watching onstat -d), however, Informix seems to be creating the index for the scheme **in the indexes dbspace** instead of the table fragment's dbspace(s). This was all confirmed by monitoring onstat -d and watching the indexes dbspace drop to 0 before the error, while the data dbspaces have plenty of pages free. Why would the new index be automatically directed to the separate dbspace? And, is it possible to override this behavior??? Thanks, Michael Hoffman -------------------- alter fragment on table event_queue INIT fragment by expression partition part_1 topic[1] = '1' in ensdb, partition part_2 topic[1] = '2' in bizdb,partition part_rem remainder in ensdb ; ERROR: 212: Cannot add index. 131: ISAM error: no free disk space --------------- ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The index exists in the index dbspace. You said that. When you ALTER FRAGMENT on the table, the indexes have to be rebuilt because all of the rowids have changed and if the table wasn't fragmented before then the index leaves have to be expanded to hold both the dbspace# and the rowid in the one dbspace. When the index is rebuilt it is rebuild in the same dbspace(s) that it already lives in, not the table's dbspace(s) unless it is attached. This is expected behavior. Next time drop the indexes first and rebuild them after. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Mar 9, 2016 at 7:42 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Hi All, > Running Informix 12.1.FC4 on Solaris 10 > > Interesting problem: I have a 15GB table that I want to fragment. The table > has 2 detahced indexes, created in a separate index dbspace. Neither index > will be used for the fragmentation scheme. > > The fragmentation seems to go swimmingly for the actual data (as seen by > watching onstat -d), however, Informix seems to be creating the index for > the > scheme **in the indexes dbspace** instead of the table fragment's > dbspace(s). > This was all confirmed by monitoring onstat -d and watching the indexes > dbspace drop to 0 before the error, while the data dbspaces have plenty of > pages free. > > Why would the new index be automatically directed to the separate dbspace? > And, is it possible to override this behavior??? > > Thanks, > Michael Hoffman > > -------------------- > alter fragment on table event_queue INIT > fragment by expression > partition part_1 topic[1] = '1' in ensdb, > partition part_2 topic[1] = '2' in bizdb,> partition part_rem remainder in ensdb > ; > > ERROR: > > 212: Cannot add index.> > 131: ISAM error: no free disk space > ---------------> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1141f23c2930c5052da9ac07

Thank you Art & Madison! Ah yes, the *existing* indexes need to be rebuilt with pointers to the new fragments. Overlooking the obvious. :-) Thanks again! See you in Florida. Mike