Replication Advice

I am currently working on a utility to snapshot data from multiple databases to a repository database. This can copy an entire scheme or groups of tables. In the case of tables, the data can be appended to an existing table with the addition of date stamp and source columns. This is an 8.1.5 NT environment.

The main problems seem to be performance, ensuring a point-in-time snapshot and complexity.

The options I've considered:

export/import

    pros: supports point in time; supports all datatypes e.g. long     cons: performance -> cannot avoid generating redo; need to write special code to do appends; requires an intermediate file (this is not UNIX); have encountered bugs in exp/imp

replication api:

    pros: presumably can be tuned to perform well; supports point in time; doesn't require an intermediate file

    cons: doesn't support longs; doesn't support append

dump to text and use sqlloader

    pros: can use direct path; can set point in time on export     cons: more complex; requires intermediate file; support for longs with embedded crlf etc could be difficult

pl/sql

    pros: simple to code; can avoid redo     cons: doesn't support longs; cannot get point-in-time

To avoid overload the source servers, I would like to be able to do something like

     for each snapshot source
        set transaction read only
    for each snapshot source
        copy tables

This is not possible with export.

I also cannot see any way of setting a read-only transaction in the source databases while still being able to write to the target database.

An alternative would be too write a client program that opened multiple connections, but I don't especially want to write a pro*c or OCI program, the performance of ADO/odbc is bad and longs cause problems and OO4OLE has problems with longs.

Any suggestions?

    Cheers

        Keith Boulton Received on Sat Feb 09 2002 - 07:15:32 CST