By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,703 Members | 1,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,703 IT Pros & Developers. It's quick & easy.

Transferring Data from Sql Server 2000 to IBM db2 udb

P: n/a
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Turn off autocommit by calling the StartTransaction method.

ODBC may be faster, but given the need to move data across the network from
Windows to AIX, I suspect that any performance gain in using a lower level
database API will be totally swamped by the network delays and time spend in
the network code. In addition, as you probably have a one-off migration, the
additional coding time is also a factor.

"Lyn Duong" <ly**@tablimited.com.au> wrote in message
news:8d**************************@posting.google.c om...
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn

Nov 12 '05 #2

P: n/a
Lyn Duong wrote:
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn


Lyn,

As a wildcard option...

You **could** try using FreeTDS, ( www.freetds.org ) and see if it will
compile on AIX. I've never tried it, but it might compile on AIX. Of
course you have to have a c-compiler on the system, and a few other
goodies to compile it. FreeTDS is the use of the Sybase Tabular Data
Stream library, which is typically used in Linux environments to get
data from a SQL-Server system. We have all manner of scripts running
on our Linux boxes connected with FreeTDS to our SQL-Server systems to
pull data. FreeTDS is the Sybase netlib library, so will be faster than
ODBC, but again, much depends on your ability to get FreeTDS to compile
on AIX--there may be a precompiled binary out there somewhere you can
use too. Chances are you won't be able to compile it on AIX, but you
**could** set up a Linux box, pull the data over with freebcp, which
comes with freetds, and then mount the drive with the data on AIX, and
load it. FreeTDS takes about 5 minutes to install from scratch, and
the config file looks a lot like an INI file, which also takes about
5 minutes to configure.

There is also jTDS at http://jtds.sourceforge.net/ . Again, much
depends on whether or not you can get this installed in your
environment. It might be more of an option than FreeTDS as it is
supposed to be platform independent.

Nov 12 '05 #3

P: n/a
Hi

If you cann't change the commit frequence (try db2cli.ini), by setting
autocoomit option.

Else do an export from MS SQL in CSV-file format, and use DB2 LOAD utility.
It's very fast.

/Kim
"Lyn Duong" <ly**@tablimited.com.au> wrote in message
news:8d**************************@posting.google.c om...
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn

Nov 12 '05 #4

P: n/a
Bob
Hi Lyn,

I suggest you try StarQuest Data Replicator
(http://www.starquest.com/Productfolder/infoSQDR.html), it is designed to
replicate data between SQL Server and DB2, and it's a very fast replication
solution.

Bob

"Lyn Duong" <ly**@tablimited.com.au> a écrit dans le message de
news:8d**************************@posting.google.c om...
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn

Nov 12 '05 #5

P: n/a
no, we have the need to run this transfer of data on a daily
basis..about 1million records per night so timings are important

i know how to set the autocommit off on db2 side but in ms sqlserver
2000 dts designer, how do i then tell the package to commit and where
is this StartTransaction Method ??

Thanks

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cc**********@ngspool-d02.news.aol.com>...
Turn off autocommit by calling the StartTransaction method.

ODBC may be faster, but given the need to move data across the network from
Windows to AIX, I suspect that any performance gain in using a lower level
database API will be totally swamped by the network delays and time spend in
the network code. In addition, as you probably have a one-off migration, the
additional coding time is also a factor.

"Lyn Duong" <ly**@tablimited.com.au> wrote in message
news:8d**************************@posting.google.c om...
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn

Nov 12 '05 #6

P: n/a
Even at the risk of looking like I'm trying to upsell I have to ask:
have you looked into DB2 II. If you regularly talk to the SQL Server
there maybe value.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7

P: n/a
Have you considered using II or DProp hetrogenous replication?
"Lyn Duong" <ly**@tablimited.com.au> wrote in message
news:8d**************************@posting.google.c om...
Hi all

I'm trying to transfer data from sqlserver 2000 to ibm db2 AIX 8.1.4
using dts packages
This works when I use db2 ole db drivers but the problem is that the
transfer is very slow as the rows are committed 1 by 1. I want to use
th odbc driver but it doesn't seem to work.

Has anyone experienced this? any ideas to speed up the transfer or
use odbc driver instead of ole db ?

Thanks
Lyn

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.