Best approach for migrating 2.5 billion rows
John Dargan asked about copying 2.5 billion rows in 1,934 tables from AIX to Linux using Enterprise Replication plus export and the high performance loader for the largest tables. Paul Watson asked about the outage window and versions, and mentioned flat files or external tables. Art Kagel endorsed ER (needs a unique key; add REPLCHECK, set up bidirectionally). Henri Cujass suggested copying active records first.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
In my organization we have a production AIX machine with multiple Informix databases, 1,934 tables and over 2.5 billion records. We want to copy all of this data to Informix databases on a new Linux machine. I am currently planning to use Enterprise Replication for most of the tables. For the largest tables (including ones that have blob data) I'm planning to export the data and import it with the high performance loader. Once we have 99% of the data on the new machine, I'm planning to use Enterprise Replication for 100% of the tables until the moment that we do our final production cutover to the new machine. I'm wondering if the approach I'm describing here seems like the best approach? Or is there a better solution? ------------------------------ John Dargan ------------------------------
How large is your outage window ? If the window is large enough then logically flat filing everything and reloading is easy. ER will work fine subject to ER requirements, I would add replcheck so the consistency checking is faster. External tables will work well as well. ER also give you an opportunity to change the physical schema Cheers Paul
John: This is indeed a good approach as long as your tables all have a primary key constraint, a unique key constraint, or at the least a unique index that ER can use. Ideally you should add REPLCHECK to any table that has more than a trivial number of rows and non-trivial inserts, updates, and deletes against it. That will significantly speed up the initial copy and table sync/resync if needed. Recommendation: set up the replication bidirectionally so that after you cut over to the new server, if you run into any performance or application issues that did not show up during testing you can quickly fail back to the AIX server and any data processed on the Linux server will have been synced back. Also this gives you the advantage of being able to move you application suite one or two apps at a time so you can tune the new system if needed while affecting a minimum of users. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Oh and what versions ?
Hi John, that sounds like a good concept. If I have to copy / reorg very large tables and will have a minimum maintenance window, I try to copy the "active" (new) records at first. The "older" records will move during production time in a relaxed process, later. It's the character of the most very large (fact) tables, that they have a history/archive function with a timestamp column and 99,9% will not change anymore. Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion henri.cujass@leolo.com ------------------------------