Q
Problem solve Get help with specific problems with your technologies, process and projects.

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
from DB2D..DMA.S_EVH_UNA_STL
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

This was last published in June 2004

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close