Thursday, July 10, 2008

Legacy Connectivity in a 64 bit world, Part 3 of 5

The first hint of real trouble came when we started looking at the existing DB/2 connectivity.

The second data source type (of four types) is DB/2. Currently, the application uses 32 bit ODBC drivers dating from the late 1990s. They were written by Wall Data, which was acquired by NetManage in 1999. NetManage's web site doesn't seem to acknowledge that these drivers existed or that NetManage supplies any sort of database connectivity software. One hint as to the age of the drivers: The drivers refer to AS/400, which was renamed to "iSeries" about, oh, forever ago.

It turns out that there are a number of 64 bit OLEDB drivers for DB/2 available, including Microsoft's own DB/2 driver, which used to be part of it's "Host Integration Server 2004" product, but is now freely downloadable. IBM's got drivers too.

My initial problem wasn't finding drivers, it was getting the linked server to talk to the DB2 instance. No one involved understood what to supply as a connection string. I found several resources on appropriate connection strings via google. When I configured the linked server with my best guesses, all I got was "invalid parameter" errors, which did not provide any sort of hints as to what the actual problem was.

Eventually, I found that the current IBM DB/2 driver would not talk to the DB/2 instance in question because the instance was so old (the version dates to the mid-1990's) and modern IBM drivers can't handle anything that old. (There was some sort of seismic shift in the communications layers during the intervening years and the new drivers just don't "habla the espanol", so to speak.)

The vendor that controls the DB/2 is not going to upgrade for us; we are only one of their customers. So, the next thing on my hit-list was the Microsoft drivers.

Eventually, I found the correct way to get the Microsoft drivers to connect to the DB/2 instance, which is to use Microsoft's setup program (the "Data Access Tool") and not to try to construct the strings manually. Unfortunately, the drivers won't do anything but connect because Microsoft's drivers look for certain packages to be installed on the DB/2 instance. The instance in question did not have those packages, so I could not even issue SELECT statements. The vendor that controls the DB/2 instance will not install these package. Due to the age of the instance, I'm not sure if these packages would operate properly If I were in their position, I wouldn't install them either.

We don't get very much data from this vendor, and they actually prefer to FTP the data via a secure channel. So, the actual solution will be to move from remote queries to loading text files which are FTP'ed to us.

No comments: