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.

Integer parameters

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

Date/time parameters

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

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.

Synonyms

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.