Hi Jean-Marc & Lorne,
We actually thought about DB2's federated capability (I think it needs
DB2's Information Integration s/w),
writing a Java program to directly transfer from MS SQL table to DB2, or
using MS SQL's DTS data migration tool
etc but these solutions would work fine, if the table size is not too
huge.
Some of our tables are really huge ( @ 40 GB) and we need to make sure
we would not run into filling up
transaction logs and aslo we need to complete the data migration in a
fixed time-frame on the date of
real production migration. Taking these constraints into consideration, we
though of taking BCP & LOAD
route which seems to be the quickest way of data transfer but we hit on the
issue with floating proint data types.
Any idea of what IBM's recommended way of data transfer from external
databases to DB2 ?
Thanks
Murty
"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cb**********@news-reader4.wanadoo.fr...
"Dave" <ad***@comcast.net> a écrit dans le message de
news:BfiBc.64184$Hg2.53852@attbi_s04... We are trying to migrate a MS SQL server app to DB2 8.1 Linux platform.
Our database has got about 300+tables with total size - 150 GB
We are using MS SQL's BCP utility to extract data from MS SQL's
tables and loading into DB2
using DB2's LOAD utility. There are tons of colums of floating
point types (singe precion & double precision types)
in the database and when extracted using BCP, it generates data only upto 17 digits. In other words, data for
floating point data types is not being copied with 100 %accuracy with
BCP & LOAD approach.
We though of using MS SQL's DTS data migration utitliy but it is
practilcally impossible to migrate huge amounts
of data using DTS, as it is too slow.
Any suggestions are greatly appreciated.
Thanks
Murty
Hi Murty,
Well, if you create a mono column table on MS SQL with float and do the
same on DB2 UDB, inserting values(1.0/3) and select the result, you'll get:
MS: 0.33333333333300003 (tested on MSSQL2000 sp3a)
DB2: +3.333333333333333E-001 (tested on V8.1 FP5 same computer)
So at base, it seems to me you will not get your 100% accuracy.
To transfer data anyway, you could maybe use an OLEDB table function (you
can esaily create one thru the Development Center) and a LOAD from cursor:
1) create OLEDB table function
2) declare c cursor for select * from table (YourOLEDBfunction)
3) Load from c of cursor insert into ...
HTH,
Jean-Marc