How do I fix a broken connection to DB2 from a web application?

Question

I support some old web applications, VBScript-based ASP for the UI and VB6 COM modules for the business and data access layers. Last weekend, I installed DB2 Connect Enterprise Edition v8 fixpack 14 on several Windows 2000 servers, and one of the web apps errors out on null data when it calls the built in VBScript function FormatNumber. This numeric data is retrieved by a SQL Server query, but the only way the SQL Server column is populated is with the calculated results returned from a DB2 query earlier in a progression through several pages.

When I installed DB2 Connect EE, one of the components loaded was MDAC 2.7. I followed corporate instructions and had the installation save an ODBC System Data Source, which reported a good connection when I tested it after the install.

For what it's worth, the project references in the production VB6 modules pointed to MDAC 2.5. I have tried recompiling and deploying to COM on my test server new versions of the VB6 modules referencing MDAC 2.7. My development environment is Windows XP Pro, with MDAC 2.8 and DB2 Connect EE v9.5 installed. When I deployed the updated VB6 dlls, the CreateObject fails to instantiate the classes with the error message that "The class does not support automation or the requested interface".

I've rolled the DB2 Connect install back and have reinstall v8 of the DB2 runtime client, which was the previous environment. The problem, however, persists.

Answer 1

I don't really get the picture for how things are connected together - where is the SQL Server and where is the DB2.
There are forums on IBM's site for helping out specifically with DB2 Connect EE, wwhich I think is a pretty pricey product (not sure tho).

One way I have seen people do it is configure a SQL server as the data gateway. You can define DB2 as a linked server, and then perform SQL queries through the SQL server in order to get to DB2. Apps need only to be able to connect to SQL Server, not directly to DB2. They get to DB2 indirectly. Depending on the load on the system this may or may not be feasible for you. You can even do joins across data stored separately in DB2 and SQL with this approach.

It's one more option in the toolbox, along with replication, data federation, and so on. I found that it reduces the variability in connectivity.