Schema changes on high availabilty DBs 
Author Message
 Schema changes on high availabilty DBs

My company is trying to resolve the best method of implementing
schema changes on tables with 5-6 million records and 6 indexes,
on a database that requires the minimum of down time.

I have suggested the following scheme -

1 create new_table with schema changes
2 Unload all data in existing table <  certain creation_time
3 Load data into new table and re-index
4 Lock data base
5 rename existing table old-table
6 rename new_table table
7 unlock database
8 unload all records from old-tablwe => creation_time

The users of these tables do not need to look back at
previous records at the critical time, only insert new records,
and every record has a created_date field, which is date ( year
to second).

Have I overlooked anything, or could it be as easy as this ?



Wed, 18 Jun 1902 08:00:00 GMT
 Schema changes on high availabilty DBs

Depending on the schema change you may be able to get a way with using
"In-Place Alter Table"

An in place ALTER TABLE can be used in the following:
a.    Adding / Dropping a column anywhere in the table,
b.    Changing the length of the Column,
c.    Changing the type of a column.

old data is stored in its existing format until it is updated whilst new
data is added in the new format. By doing this a new copy of the table is
not created and it is only unavailable for the time it takes the system to
update the table definition. If you wish to force the table change to
existing data run a dummy update on the table where you set the value of the
column to its self.

NB this only works with 7.3x or later.

for more info see INFORMIX Part Number 000-6379 Book Number
502-57561-999999-1, Version 05-99, volume 1 pg 4-6
(Training Manual from the Managing and Optimising Informix Dynamic Server
Databases)

Adam Birch


Quote:
> My company is trying to resolve the best method of implementing
> schema changes on tables with 5-6 million records and 6 indexes,
> on a database that requires the minimum of down time.

> I have suggested the following scheme -

> 1 create new_table with schema changes
> 2 Unload all data in existing table <  certain creation_time
> 3 Load data into new table and re-index
> 4 Lock data base
> 5 rename existing table old-table
> 6 rename new_table table
> 7 unlock database
> 8 unload all records from old-tablwe => creation_time

> The users of these tables do not need to look back at
> previous records at the critical time, only insert new records,
> and every record has a created_date field, which is date ( year
> to second).

> Have I overlooked anything, or could it be as easy as this ?



Sun, 01 Jun 2003 19:13:18 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Where are High Availabilty Systems discussed ??

2. Change Management: Quest Schema Mangler or Embarcadero Change Mangler

3. replicate SP changes between DBs

4. Change props on all dbs on a server

5. Changing Servers/DBs on the fly

6. General availabilty of ODBC drivers

7. Progress v6 (on Unix) ODBC drivers availabilty on MS-Windows

8. Get query plan on dbs a for dbs b

9. Tracking Table Schema Changes

10. View don't get updated when underlying table schema changes

11. Could not complete cursor operation, because the table schema changed after cursor was declared


 
Powered by phpBB® Forum Software