Comparision of table across sybace12, sybase15 and oracle 11g

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.

Who is Participating?
wilcoxonConnect With a Mentor Commented:
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.
slightwv (䄆 Netminder) Commented:
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.
PearlJamFanaticAuthor Commented:
The problem is some of these tables have millions of records.

slightwv: would you suggest the sql query approach over the informatica profiling approach?
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

slightwv (䄆 Netminder)Connect With a Mentor Commented:
>> 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.
alpmoonConnect With a Mentor Commented:
Do you use Sybase Replication Server? If yes, you can use rs_subcmp utility to compare data in different servers.
mrjoltcolaConnect With a Mentor Commented:
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.
abbas_najafizadehConnect With a Mentor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.