Alter fragment online
Posted in 2017
User asked about using ALTER FRAGMENT ONLINE to move tables between dbspaces in IDS 11.7 without downtime. Initial response clarified that ALTER FRAGMENT ONLINE only applies to ATTACH/DETACH operations. User then found that ALTER FRAGMENT ON TABLE ... INIT IN <new_dbspace> can move storage between dbspaces.
Topics: Storage & Space Management
We are planning to do a replacement of tables between dbspaces in IDS 11.7 We will try with alter fragment online in order to do the movement. the cuestions are ¿ this movement really do not affect the use of the table being moved? ¿ is there risk to do it ? ¿ or is better to ask for a time window to do it? Thanks a lot
ALTER FRAGMENT ONLINE only applies to ATTACH/DETACH of fragments to afragmented table using INTERVAL or LIST clauses.
Doesn't seem to fit your needs.
If data is (mostly) static or historical you can reduce downtime by copying
data (INSERT INTO ... SELECT FROM...) into new tables and then switch them.
For live data you can consider some complex schema involvng triggers.
Another (a bit crazy and nothing I've ever did) option could be consider
enterprise replication involving a different instance ( TAB1@SOURCE ->
TABTEMP@WORK -> TAB1TEMP@SOURCE). Not sure if this could work....
Regards.
On Wed, Feb 8, 2017 at 12:31 AM, HUGO PADILLA <hugo_padilla@terra.com.mx>
wrote:
> We are planning to do a replacement of tables between dbspaces in IDS 11.7
>
> We will try with alter fragment online in order to do the movement.
>
> the cuestions are ¿ this movement really do not affect the use of the table
> being moved? ¿ is there risk to do it ? ¿ or is better to ask for a time
> window to do it?
>
> Thanks a lot
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a113fd9a28876860547fa7c28
I checked the documentation on alter fragment for version 11.7 and find that
can be used to move the storage from a dbspace to another in this way:
ALTER FRAGMENT ON TABLE <tablename> INIT IN <new_dbspace>
http://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.sqls.doc/id
s_sqs_0084.htm
I think it could be usefull to accomplish this work
Just be sure that you have enough logical log space!
> On 8 Feb 2017, at 16:48, HUGO PADILLA <hugo_padilla@terra.com.mx> wrote:
>
> I checked the documentation on alter fragment for version 11.7 and find that
> can be used to move the storage from a dbspace to another in this way:
>
> ALTER FRAGMENT ON TABLE <tablename> INIT IN <new_dbspace>>
>
>
http://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.sqls.doc/id
s_sqs_0084.htm
>
> I think it could be usefull to accomplish this work
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Ok, thanks, I will be checking}}
AND - typically best to drop indexes before using ALTER FRAGMENT ... INIT. Otherwise as data rows are moved the indexes must be updated to reflect new rowid(s) and will have a big performance impact (and contribute to Spokey's logical logs of course). Thanks - Mark Scranton mark@markscranton.com