DB2 .NET Provider Advice

Question

We have an AS400 that we need to communicate with using our .NET applications. We currently develop web applications running on Windows 2003/2008 and have to run the AS400 client install to load the .NET provider on each of our servers running web apps that use the provider.

The provider also is loaded on our development clients through the AS400 client install.

We currently ran into an issue where when we updated our client PCs to Windows 7, we were forced to update our AS400 client access software. This of course required us to change the DLL used in our .NET projects, which will require us to load the new version of the .NET provider on the servers if we ever need to make changes to our apps.

The problem is, if we have many web applications all using this .NET provider and we suddenly load the new version, it will break all the web applications until we can change out the DLL in each project and redeploy them to the server.

I've been struggling with some possible solutions and would like to know which would be the best? Or, if there are any better ideas to get around this issue?

• Linked server - We have a linked server setup through SQL 2005 to the AS400 and it does work, but it is slow and does not work with Entity Framework. This seems to be fast enough for SELECT queries, but Inserting is very slow and the queries require using more complex SQL involving OPENQUERY. I have tried setting up a view to passthrough to the DB2 table, but the queries are either slower than OPENQUERY or they don't work sometimes.

• Web service - I thought about wrapping all DB2 communications through a single web service where when updates come along, we would only have to worry about one point of failure. I'm worried about speed and backward compatibility here.

• DLL wrapper - I also thought about wrapping the DB2 funcitonality in my own class library and then loading it into the GAC. Again, single point of failure. But, I've never messed with the GAC, so I'm not sure what new problems will be introduced and I'll still have backward compatibility to worry about.

• New VM Server - Another option is to setup a new VM application server with the new .NET provider and then move the applications over one by one, then the old server can be discarded.

• Swap DLL - I've not tried this but I assume I could recompile each application on my developer client with the new .NET provider and then swap out the DLL that contains the DB2 access code on each of the applications on the server. (I do already use a wrapper class around the DB2 provider and it is in it's own DLL). The only possible problem here is if our other developers are not yet on the new AS400 client version and they have an application deployed to the server. We would need to update their AS400 client at least.

Thanks!

Answer 1

My gut feeling would be to go for "Web Service". Use something like Ice that has absolutely awesome performance to isolate your problem area. Wrapping your business level requests should be easy task and you wouldn't have to worry about IBM product versions anymore.

Answer 2

We've decided to go with the Linked Server after figuring out how to fix the performance issues. Using OPENQUERY, everything seemed fast except the INSERTS. Using the EXECUTE method made the INSERTS fast as well.

Here are some examples of the queries we're using incase this may help someone else...

Select

SELECT * FROM OPENQUERY(Linked_Server_Name, 'SELECT * FROM table WHERE field=''Data''')

Insert

EXECUTE('INSERT INTO Server.Library.Table (field1, field2) VALUES (1, ''two'')') AT [Linked_Server_Name];

Update

UPDATE OPENQUERY(Linked_Server_Name, 'SELECT * FROM Library.Table WHERE field=''Data''') SET field='New Data' WHERE field='Data'

Delete

DELETE FROM OPENQUERY(Linked_Server_Name, 'SELECT * FROM Library.Table WHERE field=''Data''') WHERE field='Data'

Note: To escape the single quotes within the SQL, use another single quote. It is not a double-quote.