data migration question - MS SQL to DB2

[Dave]

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

[Lorne Sunley]

On Sun, 20 Jun 2004 15:42:25 UTC, "Dave" <ad***@comcast. net> wrote:

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

Have you tried to use DB2's "federated" database capability. You
should be able to use that to see the MSSQL tables from within DB2
just use insert... select.. to move the data inot native dB2 tables.

--
Lorne Sunley

[Jean-Marc Blaise]

"Dave" <ad***@comcast. net> a écrit dans le message de
news:BfiBc.6418 4$Hg2.53852@att bi_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.3333333333330 0003 (tested on MSSQL2000 sp3a)
DB2: +3.333333333333 333E-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 (YourOLEDBfunct ion)
3) Load from c of cursor insert into ...

HTH,

Jean-Marc

[Murty]

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.6418 4$Hg2.53852@att bi_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.3333333333330 0003 (tested on MSSQL2000 sp3a)
DB2: +3.333333333333 333E-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 (YourOLEDBfunct ion)
3) Load from c of cursor insert into ...

HTH,

Jean-Marc

[Mark A]

"Murty" <ad***@comcast. net> wrote in message
news:DLqBc.8319 2$HG.69871@attb i_s53...

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

Is there some way to cast the data into a different data type within MS SQL
Server so that it will export correctly?

In general, since this a BCP problem you might ask the question on a MS SQL
Server newsgroup or forum. There are some good forums on www.dbforums.com

[Serge Rielau]

Have you considered using teh migration toolkits for MS SQL Server?
You can find it of the DB2 homepage and it's free.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Buck Nuggets]

"Murty" <ad***@comcast. net> wrote in message news:<DLqBc.831 92$HG.69871@att bi_s53>...

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 ?

I don't think there's a single recommended method - it all probably
depends on a variety of factors. As you pointed out - load is going
to be the fastest. And as someone else mentioned - you can load from
a cursor - which I assume could point to SQL Server with the right
software.

But there are two other custom software scenarios to consider:

#1 create a custom app to export the float from sql server into a
format you can load from db2.

#2 copy the data from sql server to db2 transactionally . This will
take longer, though insertion rates of 1000+ rows / second is
reasonable on modern small servers. You can get around the
transaction log limitations several ways:
a. declare the table not logged initially
b. commit every 10-100k rows

[Serge Rielau]

Dave,

Take a look at "SQL Server books online" at the "float and real"
datatype section.
It states that float has a precision of 15 digits.
But SQL Server represents 53. Why that is is beyond me.
It challenges everything my highschool math teacher ever taught me...
Either way I don't believe you will loose precision when moving to DB2.
If, however you think you do you could transfer the data as binary (8
Byte). On the DB2 side a simple C-UDF should do to convert the
VARCHAR(8) FOR BIT DATA back into FLOAT (out = *((double *) &in)).
On the SQL Server side whatever can be used to expose the internal
structure of the float.
That way you should end up with exactly the same data in DB2 as in SQL
Server, even though DB2 will still only display significant digits (as
it should).

Cheers
Serge

PS: Watch those little/big endians if you move from Intel to Unix

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab