Database replication: Conflict resolution for very large tables
A DBA on IFMX 9.4/AIX planned timestamp-based Enterprise Replication on multi-million-row tables and worried about the manual's warning against adding the CRCOLS shadow columns via ALTER TABLE, fearing a costly table rebuild and downtime. Madison Pruet clarified the warning only concerns manually adding the two columns yourself; "ALTER TABLE xxx ADD CRCOLS" is supported and, absent UDTs, blobs, LVARCHARs or smartblobs, is done in place. Andrew Hamm explained in-place alters use row version numbers so the table isn't rewritten. The poster accepted this as the solution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We are running Informix 9.4 (on AIX) on two different sites, one in Washington DC area and the other in Ashville, NC. We are plannign to start replicating a selected set of tables between the two sites. Most of these tables are in the order of several millions rows. We are planning to use timestamp conflict resolution which will require two shadow columns for each of these tables. According to Informix Enterprise Replication Guide, these columns should NOT be added via alter table command. But that they should be added using create table command WITH CRCOLS. Question: How cirtical is this warning? My guess, this warning is to avoid conflict for some unfinished replication transaction, either still in memory or still wondering in the network. It will taked extra work to create these tables, add the shadow columns, and migrate data to the new tables. This will prolong the system downtime, which we cannot afford. But if there is a danger in using "alter table add CRCOLS", we will ahve to make time. Any help would be appreciated. Thnaks, chariya peterson
I think that they mean that you should not add them by manually adding the two columns. You can use the alter table to add the CRCOLS by using "alter table xxx ADD CRCOLS" "Chariya Peterson" <Chariya.Peterson@noaa.gov> wrote in message news:3FF9CFCC.39E9FF84@noaa.gov... > > We are running Informix 9.4 (on AIX) on two different sites, one in > Washington DC area and the other in Ashville, NC. We are plannign to > start replicating a selected set of tables between the two sites. Most > of these tables are in the order of several millions rows. We are > planning to use timestamp conflict resolution which will require two > shadow columns for each of these tables. > > According to Informix Enterprise Replication Guide, these columns > should NOT be added via alter table command. But that they should be > added using create table command WITH CRCOLS. > > Question: How cirtical is this warning? My guess, this warning is to > avoid conflict for some unfinished replication transaction, either still > in memory or still wondering in the network. It will taked extra work > to create these tables, add the shadow columns, and migrate data to the > new tables. This will prolong the system downtime, which we cannot > afford. But if there is a danger in using "alter table add CRCOLS", we > will ahve to make time. > > Any help would be appreciated. > > Thnaks, > chariya peterson > >
Oh yes --- If there are no UDTs, blobs, LVARCHARS, or smartblobs, then the adding of the CRCOLS is done inplace. "Chariya Peterson" <Chariya.Peterson@noaa.gov> wrote in message news:3FF9CFCC.39E9FF84@noaa.gov... > > We are running Informix 9.4 (on AIX) on two different sites, one in > Washington DC area and the other in Ashville, NC. We are plannign to > start replicating a selected set of tables between the two sites. Most > of these tables are in the order of several millions rows. We are > planning to use timestamp conflict resolution which will require two > shadow columns for each of these tables. > > According to Informix Enterprise Replication Guide, these columns > should NOT be added via alter table command. But that they should be > added using create table command WITH CRCOLS. > > Question: How cirtical is this warning? My guess, this warning is to > avoid conflict for some unfinished replication transaction, either still > in memory or still wondering in the network. It will taked extra work > to create these tables, add the shadow columns, and migrate data to the > new tables. This will prolong the system downtime, which we cannot > afford. But if there is a danger in using "alter table add CRCOLS", we > will ahve to make time. > > Any help would be appreciated. > > Thnaks, > chariya peterson > >
Madison Pruet wrote: > Oh yes --- If there are no UDTs, blobs, LVARCHARS, or smartblobs, > then the adding of the CRCOLS is done inplace. Madison, Thanks for your quick response. Would you please clarify "... is done in place". Does it mean I can use "ALter table ... add CRCOLS" or do I have to use "CREATE table ... with CRCOLS" ? We do not (yet) us UDT, blobs, LVARCHARS nor smartblobs. Thanks, chariya
comp wrote: > > Would you please clarify "... is done in place". Does it > mean I can use "ALter table ... add CRCOLS" or do I have to > use "CREATE table ... with CRCOLS" ? "in place" is a specific term that applies to table changes. Originally, if you added a column to a table, the engine would re-write the table so that each record was extended with the new column. This takes time, consumes log space and also locks the table. As an improvement (maybe 5 years ago) the internal table descriptors and ALTER mechanism was improved to try to avoid this rewriting of the table. Each row is marked with a "version" number of the tables schema. Each version number represents a specific shape that the table had. Each of these shapes is stored in some secret tables. If you just add a column for example, then existing rows still point to their own shape, but new rows or updated rows are (re)written with the new official schema of the table, and they will get the latest version number. This means that the entire table does not need rewriting just because you make a managable change. The engine takes care to understand each row according to it's version number and will make allowances for new or removed or changed columns, and it will rewrite the row with the newest schema if the row is updated. Eventually all the old rows might disappear, and at that time the old secret schema info is removed from the secret tables. Not all table changes are "in-place alterable"; and some of these make you question the wisdom of why, however most useful ones are. The complete set of rules is specified extremely clearly in one of the engine manuals - please don't ask me which one though ;-/ Adding the two CDR ghost columns falls into this class, so the table will not be rewritten if you add them using the syntax Madison mentioned.
Chariya Peterson wrote: > Any help would be appreciated. Thnak you for all the replies. They are really helpful and wll surely save us a lot of down time. chariya > > Thnaks, > chariya peterson