Sybase IQ to oracle load data

vio2011
vio2011 used Ask the Experts™
on
Hello!

I want to import a Sybase IQ 15 table to Oracle 11g.
How create a connection to Sybase IQ and verify that all data it's imported correctly.
I want to know that each record from Sybase IQ has same value the with imported record from Oracle.How verify this efficiently? The table has several billions of records and I have approximatively 100 tables to import in Oracle database.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
When I have a huge migration like this, I opt to extract the data into flat files, then import into the new database, then extract again from the new database into a new set of flat files, and compare the flat files.

This is called "round tripping", and the advantage is it does not tax the database for comparison.

As long as you order the columns the same, theoretically in the end, you should have identical flat files that you can use command line utilities / diff utilities to compare. And theoretically, a checksum of the file should match.

Use bcp to extract from Sybase, and sqlloader to import to from Oracle. Then use PL/SQL to export from Oracle again.

Author

Commented:
1.Oracle has ability to connect a Sybase IQ 15 database?
2.What compare tools it's recommended for huge files?
Top Expert 2009

Commented:
>>1.Oracle has ability to connect a Sybase IQ 15 database?

Yes, either via the ODBC connector or a specific connector, which is possible additional $$.
But I wasn't recommending a connector, I recommend doing it without a connector, given the requirement for comparison / auditing.


>>2.What compare tools it's recommended for huge files?

Which OS are you using?

For UNIX I've used md5sum for checksum, and the other command line utils: diff, uniq

If there is binary data, it might be a bit more complicated.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
They are text files. The OS it's Windows Server 2003 R2.
I want to give a chance to ODBC connector for Sybase IQ.
My idea is following:
-import data to oracle 11g from Sybase IQ via an ODBC connection
2. I want to know it it's the possibility in Oracle to connect to a Sybase IQ table
and to write following code for example:

select o.c1,s.c1,o.c2,s.c2 
from oracle_table o inner join sybase_remote_table  s
on o.c1=s.c1
where o.c2!=s.c2 

Open in new window


to view import errors.
Top Expert 2009
Commented:
Yes, it can connect to Sybase via the transparent gateway feature.

But with a billion+ rows, you may end up with a huge distributed join that may run for days, or possibly fail. You'll need a huge temp segment for sure.

I have not had good performance with huge distributed joins, even Oracle to Oracle, must less to a heterogenous connection. That is why I suggest doing the compare offline.

But if want to try, first download the gateways pack for your version of Oracle.

11gR2 is here http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html


Author

Commented:
Oracle gateway pack permits the connection to Sybase IQ?
Top Expert 2009

Commented:
Why don't you download the pack and look into the docs and see?

Author

Commented:
thanks for suggestions.
Top Expert 2009

Commented:
No need to close question so soon, you may get other answers. Good luck.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial