Stupid question regarding enterprise replication
Q: can Enterprise Replication copy a table to a differently named table in the same database (IDS 9.30, HP-UX)? Madison Pruet answered flatly "no". Others suggested doing it with triggers instead. The poster's real goal was re-fragmenting a huge table without long downtime; Art Kagel gave a full recipe: create the target table RAW with the new fragmentation, block updates, load with parallel dbcopy (-F, commit every N rows) per fragment, add insert/update/delete triggers on the source to keep it current, convert to standard, build indexes/constraints, run dostats, then swap names in a short window and fix foreign keys (utils2_ak from IIUG). Another poster described a home-grown trigger/transaction-table replication scheme.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS9.30HC5, HP-UX 11i I know it's somewhere in TFM but while I'm looking perhaps someone knows- Can a table be replicated using ER to another table of a different name (but obviously with the same structure) in the same database (non mode ansi)? (e.g. replicate stores7@myserver:customer to stores7@myserver:client) TIA Lazy Malc
iiug@perrior.net wrote: > IDS9.30HC5, HP-UX 11i > > I know it's somewhere in TFM but while I'm looking perhaps someone > knows- > Can a table be replicated using ER to another table of a different > name (but obviously with the same structure) in the same database (non > mode ansi)? > (e.g. replicate stores7@myserver:customer to stores7@myserver:client) > > TIA > > Lazy Malc no
> -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of Madison Pruet > Sent: Tuesday, January 13, 2009 9:57 AM > To: informix-list@iiug.org > Subject: Re: Stupid question regarding enterprise replication > > iiug@perrior.net wrote: > > IDS9.30HC5, HP-UX 11i > > > > I know it's somewhere in TFM but while I'm looking perhaps someone > > knows- > > Can a table be replicated using ER to another table of a different > > name (but obviously with the same structure) in the same database (non > > mode ansi)? > > (e.g. replicate stores7@myserver:customer to stores7@myserver:client) > > > > TIA > > > > Lazy Malc > no But it should be fairly easy using triggers. --EEM > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list
On 13 Jan, 16:08, "Everett Mills" <eemi...@nationalbeef.com> wrote: > > -----Original Message----- > > From: informix-list-boun...@iiug.org [mailto:informix-list- > > boun...@iiug.org] On Behalf Of Madison Pruet > > Sent: Tuesday, January 13, 2009 9:57 AM > > To: informix-l...@iiug.org > > Subject: Re: Stupid question regarding enterprise replication > > > i...@perrior.net wrote: > > > IDS9.30HC5, HP-UX 11i > > > > I know it's somewhere in TFM but while I'm looking perhaps someone > > > knows- > > > Can a table be replicated using ER to another table of a different > > > name (but obviously with the same structure) in the same database > (non > > > mode ansi)? > > > (e.g. replicate stores7@myserver:customer to > > stores7@myserver:client) > > > > > > TIA > > > > Lazy Malc > > no > > But it should be fairly easy using triggers. > > --EEM > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list Ah! good catch. Thought of that but probably not possible. We have an inordinately big table we're wanting to fragment differently but we can't have extended downtime at all on it. So the idea was to create a type RAW duplicate of the table, fragmented as required, and then halt processing for the 5 hours required to copy the data over and then reallow processing on the original table. We can then change the duplicate table to type STANDARD and fiddle about with creating the indexes on the duplicate (23 indexes) and associated UPDATE STATS that will take over 20 hours to implement - downtime we don't have available to us hence not being able to do it on the existing table. After all the setting up and honing the fragmentation and general fiddling on the duplicate we'd want to replicate any changes onto it from the original table that may have occurred in the intervening period and then just do a RENAME TABLE to bring it live. I guess we could use triggers to unload any new or altered rows during the interim and then write a script that somehow duplicates the changes to the new tble? How do other people do it - I'm sure this is a not uncommon scenario?
Many many many many many years ago i wrote a trigger / stored procedure based replication mechanism. I effectively created my own "replication table" that was populated by the trigger through stored procedures when rows were insterted, updated or deleted to various tables in the database. The table had a number of columns, predominately ... row id, table affected, transaction type, data Periodically I unloaded this table and had a 4gl program that loaded the transaction table into a seconded database and replayed the transactions. I could check I had not missed any work by checking / maintaining the row id from the transaction table while not exactly what you want the concept may give you some ideas
Malc: I would do this essentially as you suggest with some modifications: - Create the new or target table with the desired fragmentation scheme in RAW mode - Block transactions on the original or source table - Use multiple copies of my dbcopy utility - one per target fragment with appropriate WHERE clause to match the target table's fragmentation expression. - This way you are loading the table as quickly as possible avoiding any lock contention. - Add INSERT, UPDATE, and DELETE filters to the original source table so that the target table is kept up-to-date - Reenable updates on the source table - Convert the target table to standard/logged mode - Build indexes on the target table with slightly modified names from the source table's indexes - Add required constraints on the target table - Run dostats on the target table to create proper statistics - When you have another downtime window - Rename the source table to a temporary name - Rename the target table to the source table's original name - Drop the triggers on the source table - Drop and recreate any FOREIGN KEY constraints that reference the original table to point to the new table - myschema -d <database> -t <source table> -F will list any foreign key constraints that reference that table - Renable updates to the new table - When system load permits and you are certain all is well with the new table - Drop the original table - Optionally rename the indexes on the new table Note: dbcopy (with -F) is at least at fast as INSERT INTO... SELECT ... FROM with the advantage that it commits every N rows and so does not create long transaction problems during large copies. (NB: Calculate how many rows the logical logs can hold safely concurrently and divide that by the number of copies of dbcopy you will be running concurrently to determine the commit block size - use -f <maxrows> ) Dbcopy and myschema are contained in the package utils2_ak which can be downloaded from the IIUG Software Repository (www.iiug.org/software) or the Oninit web site (www.oninit.com/utilities) Art On Tue, Jan 13, 2009 at 11:25 AM, <iiug@perrior.net> wrote: > On 13 Jan, 16:08, "Everett Mills" <eemi...@nationalbeef.com> wrote: > > > -----Original Message----- > > > From: informix-list-boun...@iiug.org [mailto:informix-list- > > > boun...@iiug.org] On Behalf Of Madison Pruet > > > Sent: Tuesday, January 13, 2009 9:57 AM > > > To: informix-l...@iiug.org > > > Subject: Re: Stupid question regarding enterprise replication > > > > > i...@perrior.net wrote: > > > > IDS9.30HC5, HP-UX 11i > > > > > > I know it's somewhere in TFM but while I'm looking perhaps someone > > > > knows- > > > > Can a table be replicated using ER to another table of a different > > > > name (but obviously with the same structure) in the same database > > (non > > > > mode ansi)? > > > > (e.g. replicate stores7@myserver:customer to > > > > stores7@myserver:client) > > > > > > > > > > TIA > > > > > > Lazy Malc > > > no > > > > But it should be fairly easy using triggers. > > > > --EEM > > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > Ah! good catch. Thought of that but probably not possible. > We have an inordinately big table we're wanting to fragment > differently but we can't have extended downtime at all on it. > So the idea was to create a type RAW duplicate of the table, > fragmented as required, and then halt processing for the 5 hours > required to copy the data over and then reallow processing on the > original table. We can then change the duplicate table to type > STANDARD and fiddle about with creating the indexes on the duplicate > (23 indexes) and associated UPDATE STATS that will take over 20 hours > to implement - downtime we don't have available to us hence not being > able to do it on the existing table. After all the setting up and > honing the fragmentation and general fiddling on the duplicate we'd > want to replicate any changes onto it from the original table that may > have occurred in the intervening period and then just do a RENAME > TABLE to bring it live. > I guess we could use triggers to unload any new or altered rows during > the interim and then write a script that somehow duplicates the > changes to the new tble? > How do other people do it - I'm sure this is a not uncommon scenario? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.