Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde