Tweet
Our environment consists of an AS/400 Model 825 and several SQL Server 2000 servers. The main catalog tables (Store Master, Item Master, Vendor Master tables etc.) are on the AS/400. Currently we are replicating these tables from the AS/400 to the SQL Servers so that our applications can join these major catalog tables to the application specific tables on SQL Server. Now we are beginning to replicate large EI Summary tables built on the AS/400 to SQL Server. Instead of replicating, I just want to leave them where they are and perform my joins from SQL Server to the AS/400 Linked Server, but the performance is terrible. Can anyone direct me to where I can find information on developing applications that need to join SQL Server tables with AS/400 tables? We are using the iSeries Access OLE DB provider (V5R2) to create a Linked Server in SQL Server 2000, but the performance on the AS/400 tables and joins with SQL Server Data is so much worse than the SQL Server that we are about to make a decision to move off the AS/400/iSeries completely. I have been the only one digging in my flukes and supporting the AS/400, but I am beginning to have my doubts. Maybe Microsoft SQL Server really did "outdo" IBM Rochester as was predicted. I have been able to perform OPENQUERY Transact-SQL statements to the Linked AS/400 server, but no four-part names so that I can perform joins with SQL Server tables. Also, there must be some tuning that can be done on the OLE DB provider and the AS/400 or the SQL Server joins or Linked Server definition that must be able to enhance performance. I can't be the only one that tried to do this. Any help or direction would be much appreciated! Caleb Mitsvotai Panda Restaurant Group Rosemead, California 435-691-1470