WHERE clause not being passed over database link

We are experiencing a performance issue using SQL Server 2000 (SP3), MDAC 2.8, and a database link that uses a Neon Shadow Direct ODBC driver to access DB2 on OS/390. The database link is working, but we are experiencing poor performance on this table.

Basically, the NEON ODBC Client driver Log file shows that the WHERE clause is not being passed to the database, and all the filtering is taking place on the SQL Server. This table has 78,000 rows and therefore this type of performance, two to three minutes, is unacceptable. There is a secondary index on the field in the WHERE clause "eventnumber" in DB2. Here is the query we are running in T-SQL:

Select remarks
where eventnumber = '00142003'
This query will use an index when executed on the mainframe DB2 OS/390. However based on the ODBC log file from Neon Shadow Direct, the WHERE clause is never passed. What would cause it not to be passed?

Unfortunately, I am not familiar with this product and can offer little advice. I apologize. I would contact the vendor's tech support and find out if this is a known issue and how this problem can be resolved. Another option would be to try another driver to support the linked server from IBM or from Microsoft via Host Integration Server.

One word of caution with respect to performance when accessing the DB2 system: performance may not be as fast as you desire from the client application, through SQL Server to DB2 and back. You have a number of hops to complete the client request before the front end application times out. Further, based on the volume of transactions and the subsequent data the linked server may be stressed out that can also negatively impact performance. Therefore, it may be advantageous to load the data to the SQL Server on a regular basis, i.e. daily or weekly, and query this data directly rather then incurring the additional hops and accessing the via the DB2 system. Good luck!

For More Information

Dig Deeper on Microsoft SQL Server Installation