Oninit cdi / Archive
Talk to an expert →

Alter fragment online

Posted in 2017

Summary

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.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Storage & Space Management

HUGO PADILLA — — source: IIUG Forums & Mailing Lists

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

↪ replying to HUGO PADILLA
Fernando Nunes — — source: IIUG Forums & Mailing Lists

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

↪ replying to Fernando Nunes
HUGO PADILLA — — source: IIUG Forums & Mailing Lists

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

↪ replying to HUGO PADILLA
Spokey Wheeler — — source: IIUG Forums & Mailing Lists

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. >

↪ replying to Spokey Wheeler
HUGO PADILLA — — source: IIUG Forums & Mailing Lists

Ok, thanks, I will be checking}}

↪ replying to HUGO PADILLA
MARK SCRANTON — — source: IIUG Forums & Mailing Lists

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

Glossary