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
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.