My current project has a need to read and update DB2 data in addition to MS Sql Server data. The primary server is Sql Server, DB2 is additional.
Our selected way of doing this is by creating a linked server on Sql Server (2008 R2). As you will see, there are plenty of things I don’t know, so if you know fixes or workarounds, I would appreciate a comment.
- All data access via one driver, no messing with the DB2 drivers on the development machines or the web servers
- Stored procedures can operate against data in either database or both databases
- Simple configuration mistakes or oversights lead to working but slow queries
- Developing queries that are reasonably optimized can be complicated
- Even with this configuration, we cannot update both databases within a transaction without installing MSDTC
We recently upgraded our DB2 environment from 9.x to 10.0. There are differences between the 2 in a linked server environment. I mention this because my observations below are based on the 10.0 environment and you may not have the same experience if you use a different version or a different driver for the link.
Passing the where clause to DB2
If you do not take care when constructing your queries, the where clause is not even sent to DB2. In that case, the entire remote table is retrieved and then handled locally. I try to avoid joins on DB2 tables and queries that do not send a where clause. If you take care to match data types so that the where clauses are handled correctly and sent to DB2, you get much better performance.
Queries by integer values are simple. Sql server has no problem sending over a where clause containing greater than, less than, or equals clauses. It seems to handle inline values and parameterized values equally well.
When I refer to passing values inline, I am referring to a query similar to
select * from db2server.CompanyData.MySchema.MyDb2Table where Id = 1
When I refer to parameterized values, this could be in a stored procedure using a value that was passed in, or it could be done in a query like this one
declare @Id int
select @Id = 1
select * from db2server.CompanyData.MySchema.MyDb2Table where Id = @Id
Datetime values are interesting. I haven't been able pass inline values across successfully. Parameterized values work great for the "date" type. I haven't been successful at passing values for the Sql Server types "datetime" or "datetime2". We don't need this functionality at the moment as we are just using "date" fields, but it seems like it would probably come up at some point. I have not experimented with the "time" data type.
Character values (char, varchar, nchar) get more interesting. In order to get Sql Server to attempt to send the where clause with the character values, we have to set "Collation Compatible" to true in "Server Objects/Linked Servers/(link name)/Properties/Server Options." This tells Sql Server to assume that the collations are compatible or at least compatible enough.
Parameters work well for passing character values. Specifying the character values inline seems to result in problems matching the character set on the DB2 side.
In our case, the collation on DB2 is case-sensitive while the Sql Server collation (the default) is case-insensitive. As long as the developers understand this, it isn't hard to account for it. All of our access to the DB2 tables is done via stored procedures. In one case where we would like to a case-insensitive search on DB2, the data is already upper-cased in the database, so all we need to do is convert our parameter to upper-case before sending it.
We use synonyms to take some of the sting out of moving to different environments. If we were able to name the linked server the same on every environment, we might not need this. We had been stuck editing our stored procedures when we moved from development to QA and then again to move to production. As you can imagine, this would occasionally result in errors or changes being missed.
We created a stored procedure that would create synonyms for each of the DB2 tables we need to access, based on a passed-in parameter. In our stored procedures we now reference the synonym instead of linked server naming format.
There is the overview of my Sql Server/DB2 linked server experience. I would love to hear of other experiences or good places to find additional resources.