views:

113

answers:

3

i need to copy the tables and data (about 5 yrs data, 6200 tables) stored in sqlserver, i am using datastage and odbc connection to connect and datstage automatically creates the table with data, but its taking 2-3 hours per table as tables are very large(0.5 gig, 300+columns and about 400k rows).

How can i achieve this the fastes as at this rate i am able to only copy 5 tables per day but within 30 days i need move over these 6000 tables.

+2  A: 

Can you do the transfer of separate tables simultaneously in parallel?

We regularly transfer large flat files into SQL Server and I run them in parallel - it uses more bandwidth on the network and SQL Server, but they complete together faster than in series.

Cade Roux
yes i can do this, but i might be able to do 2-3 separate jobs as settin gup the datastage job, providing the metadata and odbc connection takes some time for every job.Are there any other tools that can help me on this?
+4  A: 

6000 tables at 0.5 Gb each would be about 3 terabytes. Plus indexes. I probably wouldn't go for an ODBC connection, but the question is where is the bottleneck.

You have an extract stage from SQL Server. You have the transport from the SQL Server box to the Oracle box. You have the load.

If the network is the limiting capability, you are probably best off extracting to a file, compressing it, transferring the compressed file, uncompressing it, and then loading it. External tables in Oracle are the fastest way to load data from flat file (delimited or fixed length), preferably spread over multiple physical disks to spread the load and without logging.

Unless there's a significant transformation happening, I'd forget datastage. Anything that isn't extracting or loading is excess to be minimised.

Gary
The only reason i used datastage is that i don't have to create the table structure before loading the data. Do you think extracting the data from sqlserver table and loading it in oracle(after creating the table structure) will be faster? the bottle neck is i need to be able to copy atleast 30 tables per day but right now it takes 3 hrs to load a 400k,300column table. what is the normal time it should take for this kind of table, are there any other tools that can do this as well as automate it?
@nazer555 - creating the tables should be relatively painless. I have generated table creation scripts automatically to create Teradata tables from SQL Server tables and, while there is some up front work in writing the code to generate the appropriate table syntax for the different dialect/index features, it's typically not that big of an advantage to generate table schemas automatically if you're taking a hit on performance by using datastage.
Cade Roux
can we automate the extraction of data in to a file and loading into the oracle table? are there any tools from which i can just do a " insert into(oracle table) select * from (sqlservertable), this will atleast make the process automated as rightnow i am running the DS jobs automatically.
@nazer555 There is always linked server, but I wouldn't do large inserts that way. Can you bcp the data out of SQL Server into a flat file and then use SQL*Loader to load it? This is what I do with SQL Server to Teradata - bcp out, fastload in. I did about 4m rows in 10 minutes.
Cade Roux
can we automate the process of extracting the data into flat file?am sure sqlloader can be automated using shell scripts but what abt this whole process as it will be tedious to do extraction of 6000 flat files.
+1  A: 

Have you thought about scripting out the table schemas and creating them in Oracle and then using SSIS to bulk-copy the data into Oracle? Another alternative would be to use linked servers and a series of "Select * INTO xxx" statements that would copy the schema and data over (minus key constarints), but I think the performance would be quite pitiful with 6000 tables.

Joe Swan
i will do research on SSIS bulk-copy and linker servers,by this atleast i can automate the scripts as right now i am running the jobs manually.Do you know any good articles/commands for the above?