DB2 / SQL SERVER 2005 / .NET

For those of you who are lucky enough to work with DB2 and .NET, you’re probably already aware that you can run into some issues if you’re  not using the correct DB2 Driver.

I’ve been writing some SSIS packages that move some data from a DB2 environment into a SQL Server 2005  Database. I also had to write a few .NET applications that spoke to the DB2 database. Here’s a couple drivers that I used and some of the notes regarding them. I’ve also included a few links that help in regards to development.

Drivers

• Microsoft OLE DB2: DB2OLEDB Driver 
  • This driver is part of the Feature Pack for SQL Server 2005.
  • This Driver worked for most of the time until I needed to work with Large Objects (BLOB/CLOB). Unfortunately I found this after I did a bunch of work –
    • “The OLE DB Provider for DB2 does not currently support the DB2 Large Object (LOB) types.” – Distributed Query Support Using the OLE DB Provider for DB2

• IBM DB2 Driver for ODBC and CLI
  • Please note, I COULD NOT get the IBM web site to work with IE7. I had to use Firefox to download the file.  
    • Link Name: IBM DB2 Driver for ODBC and CLI
    • File Name: v9fp2_win_db2driver_for_odbc_cli.zip
  • Download the file, install and you’ll have the drivers on your machine.
  • The thing is, this worked great for the BLOB/CLOB fields, but when the SSIS Package went to transform the DateTime fields it complained about data loss because DB2 has more DateTime precision than SQL Server 2005.
    • This was solved by using the Microsoft OLE DB2 Provider as it did not complain about the DateTime. The team I worked with knew that we’d lose some millionths of a second, but that was acceptable for the project. Basically the Microsoft Provider didn’t complain about it like the DB2 provider did. The data came through the conversion correctly, so we were fine with it.

Other Useful Information/Tools

• SQLState Error Message Reference
  • Sometimes SSIS can throw some nasty HRESULT errors your way and you’re left scratching your head trying to figure out what went wrong. If the message includes a SQLState, look in this reference, it will probably be in here, or will point you in the right direction.

• WinSQL Lite
  • I like to say its like the SQL Management Studio for DB2. A very nice GUI that allows you to interact with the DB2 system. A lot of “old schoolies” (as I call them) like to use the command line to hammer away at DB2. Me? I like to look at the GUI when I’m looking at tabular data. This tool will make your life 10 times easier when looking for data anomalies.