Re: Can you reorg a Table without stopping the DB?

Another approach: Requirements: Table must have a unique index. You need enough disk for a new table Procedure: 1. Create a new table with the same schema. 2. Create update, insert, and delete triggers on the old table. The update trigger should update the new table /same row (unique idx). The insert trigger should insert the new row into the new table. The delete, well I think you get the idea. Caution: make sure no apps are running when you create the triggers. If any are running, they will get a "table has been changed" error message. 3. Write a cheap application that selects one row at a time from the old table and inserts it into the new one (singleton trx's). 4. With the triggers already in place, run the cheap application to start copying the rows over into the new table. When you write the copy application, you will need to trap for duplicates in a unique index (-239 informix error) and if you use constraints, trap for the duplicate error there as well (don't remember the #). This works quite well. We have used it a number of times on large very active tables. Downtime is minimal - once in the beginning to create the triggers, once at the end to drop the triggers, drop the old table, rename the new one. You'll have to think this process through to see how it's possible that this could work. You can even kill the copy application and start it up later with no loss of data. Because you trap for dups, you don't need to restart where you left off. The process will quickly go thru the "already copied" rows and won't do inserts that don't error-out until it gets to where it left off. This little trick was conceived and created by George Palmer (no relation to Arnold). He called it a stealth move. 1. > Sorry, got on this thread late, so if this has already been said, my > apologies. > > I think what Jason may be asking, ultimately, is how to keep the > access > to the table up with minimum downtime, while changing the table. > > The best way I know of is to lock the original table in share mode so > nobody can update the table. Revoke all but Select permission on the > table, so you are assured that no changes take place. Create a new > table, new_table, with whatever organization you want. Select data > from > old_table, insert into new_table. Create indexes and permissions on > new_table and whatever else you need, such as triggers and such. Drop > > old_table, or rename old_table something else. Rename new_table to > old_table_name. > > Using this method your users will still be able to at least access the > > table to Select, but no changes will be allowed, for the duration of > your load. > > This assumes you have the space available for 2 copies of the table in > > question. > > HTH. > > > The engine HAS to be up to re-org a table. Its the application that > > > will > > most likely have to be shut-down. > > > > Chuck Ludwigsen > > > > Jason Berryhill <jason_berryhill@hp.com> wrote in article > > <33D8D53E.5946@hp.com>... > > > Greetings to INFORMIX practitioners: > > > > > > Can anyone tell me if it is possible to do a reorg. on a table > > within IN > > > FORMIX without shutting down the database? I.E. keeping it online > > > and > > > working. > > > > > > > > > Thanks all, > > > > > > Jason Berryhill > > > > > -- > * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * > ADDRESS ALTERED TO FOIL SPAMMERS: Remove "*NO-SPAM*" to reply. > > Cosmo Lee Multi-User Computer Systems Brooklyn, NY > > "JUST SAY 'NO' TO SPAM" > * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *