Informix ER & maintenance on historical data

[Francesco Veroni]

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

[Art Kagel]

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.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[FRANK]

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 <checc...@libero.it> wrote:

[Francesco Veroni]

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

[mpruet]

I would simply do the delete on both nodes within a
begin work without replication;
delete ..
commit work;