error in alter fragment
User encountered an ALTER FRAGMENT operation failing with "DBspace full" error when attempting to move a table from a full dbspaceA to empty dbspaceB. The issue was caused by detached indexes on the table also residing in dbspaceA. Solution: drop the indexes before the ALTER FRAGMENT operation, then recreate them (optionally using IN TABLE clause). Trade-off noted: detached indexes offer better performance than IN TABLE indexes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have the following situation:
There are several dbspaces in one instance, one dbspace is called dbspaceA,
other is called dbspaceB.
I have a table called table1 in dbspaceA
There are no free space in the host and the dbspaceA is full. However, the
dbspaceB has free space.
When I run the SQL:
alter fragment on table table1 init in dbspaceB
in the log appears WARNING: DBspace dbspaceA is full and then the SQL rolls
back.
Why is this behaviour ?
My version is 11.50FC8 and runs in HP-UX
Thanks in advance
Roger.
Try to drop the indexes on the table first. I suspect that updating the
index pages is causing some nodes to split and there isn't room for it.
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, May 9, 2012 at 10:12 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Hi,
> I have the following situation:
> There are several dbspaces in one instance, one dbspace is called dbspaceA,
> other is called dbspaceB.
> I have a table called table1 in dbspaceA
> There are no free space in the host and the dbspaceA is full. However, the
> dbspaceB has free space.
> When I run the SQL:
> alter fragment on table table1 init in dbspaceB>
> in the log appears WARNING: DBspace dbspaceA is full and then the SQL rolls
> back.
>
> Why is this behaviour ?
>
> My version is 11.50FC8 and runs in HP-UX
>
> Thanks in advance
>
> Roger.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5299a313c56e904bfa5d45d
Thanks. It works.
The table has two detached indexes in the same dbspace
create unique index idx1.. in dbspaceA
create index idx2 .. in dbspaceA
I dropped the indexes, run the alter fragment and recreated.
This time, I used "create index ...in table" to avoid this issues.
Roger.
Performance for detached indexes is better than IN TABLE indexes, that is
why this is the default. Also, making the indexes IN TABLE will increase
the number of extents in the combined partition and the number of pages
risking running into the 16million page limit or the ~200extent limit
(still a problem until you upgrade to 11.70+) on single partitions if the
table is large.
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, May 9, 2012 at 11:16 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Thanks. It works.
> The table has two detached indexes in the same dbspace
> create unique index idx1.. in dbspaceA
> create index idx2 .. in dbspaceA>
> I dropped the indexes, run the alter fragment and recreated.
> This time, I used "create index ...in table" to avoid this issues.
>
> Roger.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8eb82ec6ec04bfa67045
Thank you very much Roger.
Also looks like the ~17 million page limit per dbspace was reached?.. Have many rows been deleted in the dbspace's tables?.. If so, and possible, would unloading all rows, delete/re-create tables, load rows back in, re-create indexes and update statistics reduce number of pages plus optimize access?