Slow Query/Best way to link iSeries Db to SQL Server

[sgalambos]

I'm having issues with extremely slow speeds when querying our remote iSeries Db from our local SQL instance. I think it may be related to the driver/connection string setup, on the SQL Side. For Example Running:
SELECT WADOCO,
WAAITM,
WAITM,
WALITM
FROM [linked server]...[F4801]
WHERE WASRST='25'

took 2:14 to return 210 rows in SQL Server Management Studio, the same query using SQL Squirrel with the jt400 driver took .8s

The Provider in SQL is "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider"(installed with Source is the IP of the box, and provider string is formatted as:
"UserID=***;Password=***;Default Collection=plsdta;Force Translate=1;

Any help is greatly appreciated.

Thanks

 

[igor_g]

Do not select directly, use OPENQUERY instead:

SELECT * FROM OPENQUERY(linked_server, 'query')