Comparision of table across sybace12,  sybase15 and oracle 11g

Posted on 2011-04-28
Last Modified: 2012-05-11
I have to come up with a test plan for testing a replication process. The source data lies in syabase12 database. through some confguration the data is replicated to sybase15 database. There are no transformations the data is replicated as it is. From Sybase15 it is further replicated to oracle. Again no transformation is applied the dat is  replicated as it is.

No the objective of testing is that the data is identical in all the three databases. Since there are differences in the available data types in sybase12, sybase15 and oracle11g the datatype in the destination database is not same as the source. For example char in sybase maybe varchar in oracle and such. Also constraints in the form null allowed and not allowed is found to be not proeprly replicated in the destination dbs.

Currently this testing is done manually using informatica IDE. A profile of the tables is taken out in all these three DBs and then the profile is compared. This is very time consuming process as  it sometime takes hours to create a profile of table containing large number of rows.
There are 100 odd table to compare across these three databases.
Can we remove informatica from the picture and test thsi using sql queries and procedures? Please help me devise a strategy.

Question by:PearlJamFanatic
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    I would probably have to think about writing a program to do this.  Do you have a machine that can see/connect to all three databases?

    If so, write a quick program that will connect and select rows from the tables, compare and report.

    Author Comment

    The problem is some of these tables have millions of records.

    slightwv: would you suggest the sql query approach over the informatica profiling approach?
    LVL 26

    Accepted Solution

    You will probably not be able to use sql since sql is different between Sybase and Oracle.  The only way I could see to possibly do this is if Oracle supports db links to non-Oracle (which I don't think they do).

    Why do column definitions vary that much?  For instance, both Sybase and Oracle support char and varchar types.

    I would suggest one of these options:

    1) use bcp and the Oracle equivalent to extract all data from the databases (you'll likely have to play with options to get bcp and oracle-bcp to match output format) then you can just use diff on the output files to locate any issues.  However, differing column definitions may cause problems (I'm specifically thinking char vs varchar).

    2) writing a perl program to retrieve data from each database and compare it.  You can loop over the tables from the "master" (sybase12) by using the results of "select name from sysobjects where type = 'U'".  Then you should be able to select data to compare from all three for the same table.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    >> would you suggest the sql query approach over the informatica profiling approach

    Cannot comment on Informatica.  Never used it.

    I'm just thinking that a 3GL program of some type might be able to loop through the records faster.  If it's faster than the Informatica approach, don't know.

    >>Oracle supports db links to non-Oracle

    They have a product called Heterogeneous Services that connect to non-Oracle databases.  Never used it so cannot comment on the complexity of using it.
    LVL 13

    Assisted Solution

    Do you use Sybase Replication Server? If yes, you can use rs_subcmp utility to compare data in different servers.
    LVL 40

    Assisted Solution

    I make no claims that this will perform better than Informatica, as I have no experience with Informatica. But I do use Embarcadero tools and have always loved them.

    They have heterogenous support with native drivers, as well as ODBC support. Free eval download is available.
    LVL 2

    Assisted Solution

    I had this problem migrating data from sql-server to oracle. oracle tools and sql-server importing tools were very low speed on my milions of records ...
    thus I programmed in to load table records into a dataset in .net and then insert records into oracle.
    this method also was a low speed method ...!
    I tried to increase my performance, then I find dataReader in sqlclient library. dataset method loads all of the records into memory, thus memory allocation and more RAM in computer required.
    but dataReader was a quick fetching data mehtod that loads data into its buffer and when I read the next record, automatically loads another record into its buffer. this method is very quick and memory limits are passed.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now