Informix ER & maintenance on historical data
Hi all, I have two Informix istances: IDS 11.50 - root node of ER, replicates some tables to IDS 9.40 - leaf node of ER (read-only). A few tables that are replicated have more than 2 thousand rows, and I have to do massive deletes on them, since a lot of historical data have to be extracted and archived. I have a fast connection (LAN) between the two istances, and there is a lot of space in the SBSpaces dedicated to the ER. How can I proceed? Should I stop the replication, do the maintenance on the root node, delete the data on the leaf node and restart the replication from scratch? Or can I try to do massive delete, hoping that all the commands are being executed also on the secondary server? Thanks
It will be faster overall to stop that one sincle table replicate, delete the rows on both servers in parallel, then reestablish the replicate. There is no need to stop replication completely for all tables. Take a look at my dbdelete utility. It is the fastest way to selectively delete large numbers of rows from a table without encountering long transaction rollback problems. Dbdelete is contained in the package utils2_ak which you can download for free from the IIUG Software Repository. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Feb 7, 2012 at 11:05 AM, Francesco Veroni <checco_vero@libero.it>wrote: > Hi all, > > I have two Informix istances: IDS 11.50 - root node of ER, replicates > some tables to IDS 9.40 - leaf node of ER (read-only). A few tables > that are replicated have more than 2 thousand rows, and I have to do > massive deletes on them, since a lot of historical data have to be > extracted and archived. > > I have a fast connection (LAN) between the two istances, and there is > a lot of space in the SBSpaces dedicated to the ER. How can I proceed? > Should I stop the replication, do the maintenance on the root node, > delete the data on the leaf node and restart the replication from > scratch? Or can I try to do massive delete, hoping that all the > commands are being executed also on the secondary server? > > Thanks > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
If you have only around 2 thousands rows, instead of 2 millions or 2 billions, your "massive" is really a piece of cake of IDS. NO worry at all. Thanks, Frank On Tue, Feb 7, 2012 at 11:05 AM, Francesco Veroni <checco_vero@libero.it>wrote: > Hi all, > > I have two Informix istances: IDS 11.50 - root node of ER, replicates > some tables to IDS 9.40 - leaf node of ER (read-only). A few tables > that are replicated have more than 2 thousand rows, and I have to do > massive deletes on them, since a lot of historical data have to be > extracted and archived. > > I have a fast connection (LAN) between the two istances, and there is > a lot of space in the SBSpaces dedicated to the ER. How can I proceed? > Should I stop the replication, do the maintenance on the root node, > delete the data on the leaf node and restart the replication from > scratch? Or can I try to do massive delete, hoping that all the > commands are being executed also on the secondary server? > > Thanks > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Ahah, sure I made a mistake, it's about 2 millions...however, with Art's suggestion I'm sure I'll be able to complete the activity without problems. Thanks a lot On 7 Feb, 17:30, FRANK <yunya...@gmail.com> wrote: > If you have only around 2 thousands rows, instead of 2 millions or 2 > billions, your "massive" is really a piece of cake of IDS. NO worry at all. > > Thanks, > Frank >
On Feb 7, 10:05 am, Francesco Veroni <checco_v...@libero.it> wrote: > Hi all, > > I have two Informix istances: IDS 11.50 - root node of ER, replicates > some tables to IDS 9.40 - leaf node of ER (read-only). A few tables > that are replicated have more than 2 thousand rows, and I have to do > massive deletes on them, since a lot of historical data have to be > extracted and archived. > > I have a fast connection (LAN) between the two istances, and there is > a lot of space in the SBSpaces dedicated to the ER. How can I proceed? > Should I stop the replication, do the maintenance on the root node, > delete the data on the leaf node and restart the replication from > scratch? Or can I try to do massive delete, hoping that all the > commands are being executed also on the secondary server? > > Thanks I would simply do the delete on both nodes within a begin work without replication; delete .. commit work;