Data Transfer b/w SQL Server 2005 and DB2 UDB.

Discussion:

(too old to reply)

MZeeshan

2009-10-26 01:29:01 UTC

I am trying to setup a scheduled data transfer b/w SQL Server 2005 and IBM
DB2 UDB v9.5 (Unix/Sun Solaris). I am using the latest IBM ODBC drivers v9.7
(used earlier version v8.1 but same result).

The data transfer requirement will eventually be bi-directional although I
am currently testing data import into SQL Server from DB2. One of my
colleagues, a DB2 DBA, has helped me in setting up the ODBC DSN for DB2 and I
was able to find and select tables on the other end when trying to create a
SSIS package using import/export task feature.

That's where the problem is happening: Apart from some cases there is always
a data type mismatch with data types 131, 130, 137 etc. appearing for table
marked for data import.

Has anyone encountered such problem and what's the best way to resolve this
issue? I've also downloaded MSFT OLE DB driver for DB2, however, the DB2 DBA
found it much easier to setup DSN using the IBM client tools.

Thanks a bunch.

--
Regards,
MZeeshan

Todd C

2009-10-26 12:44:05 UTC

Permalink

Your IBM guy may like the ODBC method because it's easy for him, but just
realize that when you go to deploy this, you will need the same ODBC setup on
the server.

Also, SSIS does not interface with ODBC the same way it interfaces with the
same respective native provider pointing to the same source.

My suggestion: install the Microsoft OLE DB for DB2 Provider (you will need
Enterprise SQL, won't work with Standard) and do a side-by-side comparison.
Then YOU decide which to use. Don't let your DB2 DBA decide if his
involvement is one-time setup and your involvement is designing and
maintaining multitudes of SSIS packages.

HTH
=====
Todd C

Post by MZeeshan
I am trying to setup a scheduled data transfer b/w SQL Server 2005 and IBM
DB2 UDB v9.5 (Unix/Sun Solaris). I am using the latest IBM ODBC drivers v9.7
(used earlier version v8.1 but same result).
The data transfer requirement will eventually be bi-directional although I
am currently testing data import into SQL Server from DB2. One of my
colleagues, a DB2 DBA, has helped me in setting up the ODBC DSN for DB2 and I
was able to find and select tables on the other end when trying to create a
SSIS package using import/export task feature.
That's where the problem is happening: Apart from some cases there is always
a data type mismatch with data types 131, 130, 137 etc. appearing for table
marked for data import.
Has anyone encountered such problem and what's the best way to resolve this
issue? I've also downloaded MSFT OLE DB driver for DB2, however, the DB2 DBA
found it much easier to setup DSN using the IBM client tools.
Thanks a bunch.
--
Regards,
MZeeshan

MZeeshan

2009-10-26 20:05:05 UTC

Permalink

I was able to transfer data between SQL Server and DB2. The problem was not
with the connection in fact the table I was using in import/export was empty!

Anyways, I was wrong as IBM driver was not ODBC but OLE DB provider for DB2
and it was right decision to use IBM driver because we have Standard Edition
license and there is no limitation with IBM drivers unlike MSFT (as you've
mentioned in your e-mail).

Thanks anyways.
--
Regards,
MZeeshan

Post by Todd C
Your IBM guy may like the ODBC method because it's easy for him, but just
realize that when you go to deploy this, you will need the same ODBC setup on
the server.
Also, SSIS does not interface with ODBC the same way it interfaces with the
same respective native provider pointing to the same source.
My suggestion: install the Microsoft OLE DB for DB2 Provider (you will need
Enterprise SQL, won't work with Standard) and do a side-by-side comparison.
Then YOU decide which to use. Don't let your DB2 DBA decide if his
involvement is one-time setup and your involvement is designing and
maintaining multitudes of SSIS packages.
HTH
=====
Todd C