If querying from SSMS try using OPENQUERY( around your SQL)
SQL Format Examples
Assuming your linked server is named AS400, the database is MyCatalog, the schema/library is SYSIBM, and the table is MYTABLE (names are illustrative):
Four-Part Name (simple but slow for complex queries): This format is easy to write but often results in the entire remote table being copied over the network to the local SQL Server for processing.
SELECT * FROM [AS400].[MyCatalog].[SYSIBM].[MYTABLE] WHERE MYCOLUMN = 'somevalue';
OPENQUERY (recommended for performance): This method executes the query directly on the IBM i server, returning only the filtered results to the SQL Server, minimizing network traffic. The query inside OPENQUERY must be valid SQL for the IBM i (DB2 for i) database.
SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM MyCatalog.SYSIBM.MYTABLE WHERE MYCOLUMN = ''somevalue''');
Performance Considerations and Best Practices
Performance with linked servers can be an issue due to SQL Server's lack of knowledge about remote indexes and statistics.
-Use OPENQUERY for Filtering: Always filter data on the IBM i side as much as possible using OPENQUERY to reduce the amount of data transferred across the network.
-Minimize Data Transfer: Avoid SELECT * and only retrieve necessary columns.
-Avoid Joins on the SQL Server Side (if possible): Joining large remote tables with local tables often performs poorly. If necessary, pull remote data into a local temporary table first, then join.
Configure the OLE DB Provider: Ensure the linked server provider (like IBMDADB2 or IBMDASQL) is correctly configured in SSMS, specifically by enabling "Allow inprocess" under Server Objects > Linked Servers > Providers.
-Analyze Execution Plans: Use the SQL Server Management Studio (SSMS) performance tools to analyze the query execution plan. This will confirm whether filtering is happening remotely or if the entire table is being pulled locally (indicated by a high-cost Remote Scan operation).
-Consider Alternative ETL: For large-scale, routine data integration, an ETL (Extract, Transform, Load) process or data replication might be a more performant and reliable solution than ad-hoc linked server queries.
Hopefully this works for you.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
------------------------------
message: 2
date: Thu, 12 Feb 2026 00:45:33 +0000
from: Stephen Landess <steve_landess@xxxxxxxxxxx>
subject: SSMS and IBM i
Background:
As part of my current assignment, I have been tasked with migrating our legacy JDE data from the IBM i system to SQL Server so that it will be available for query purposes after the conversion project is completed and the IBM i system is retired.
To that end, I have lately been using SQL Server 2022 with SQL Server Management Studio (version 22) and SQL Server Migration Assistant for DB2.
By trial and error and with the assistance of Google, I set up a linked server in SQL Server that is configured using the IBM DASQL OLE DB provider.
During the data migration project I have found SSMS to be a very useful tool for querying and analyzing IBM i data, since it provides the ability to execute several queries at once, providing multiple result set grids which are simultaneously viewable in the SSMS query window.
ACS Run SQL Scripts provides a similar capability, but it either puts each query in a separate tab (or separate windows, if so configured),
so the result sets are not simultaneously viewable unless you drag the windows around and tile them manually.
However, today I found that the F43121 query takes almost 2 minutes to run when run from SSMS, as opposed to a couple of seconds when run using ACS Run SQL Scripts.
When I look at the IBM i QZDASOINIT job behind the SSMS query it appears that even though I'm using a view (F4311V1) which is selecting data on fields for which I have an index built, it still seems to be performing a full table scan before it returns a result set.
Examples:
-----------------------------------------
This is the SQL Server query:
-----------------------------------------
DECLARE @DOC1 decimal(8,0);
DECLARE @DOC2 decimal(8,0);
SET @DOC1 = 382144;
SET @DOC2 = 382584;
select
'F4301' AS FILENAME
, PHDCTO
, PHDOCO
, PHMCU
, PHTRDJ
FROM [backup].[backup].[ifsprod].[F4301]
WHERE PHDOCO IN ( @DOC1, @DOC2)
ORDER BY PHDOCO ;
SELECT
'IFS_POLN' as FILENAME
, PDDCTO
, PDDOCO
, PDMCU
, CAST(PDLNID/1000 as numeric(7,3)) as PDLNID
, PDLNTY
, PDLITM
, PDLTTR
, PDNXTR
, PDTRDJ
, PDSRC4
, IFS_LNID
FROM [backup].[backup].[migration2].[ifs_POLN]
WHERE PDDOCO IN ( @DOC1, @DOC2)
ORDER BY PDDOCO , PDLNID;
Select
'F4311' as FILENAME
, PDDOCO
, PDDCTO
, PDMCU
, CAST(PDLNID/1000 as numeric(7,3)) as PDLNID
, PDLITM
, PDLNTY
, PDNXTR
, PDLTTR
, CAST(PDUORG/100 as numeric(9,2)) as PDUORG
, CAST(PDUREC/100 as numeric(9,2)) as PDUREC
, CAST(PDUOPN/100 as numeric(9,2)) as PDUOPN
, CAST(PDPRRC/10000 AS NUMERIC(9,4)) AS PDPRRC
FROM [BACKUP].[BACKUP].[IFSPROD].[F4311LA]
WHERE PDDOCO IN ( @DOC1, @DOC2 )
ORDER BY PDDOCO , PDLNID;
SELECT
'F43121' as FILENAME
, PRDCTO
, PRDOCO
, PRMCU
, CAST(PRLNID/1000 as numeric(7,3)) as PRLNID
, PRLTTR
, PRNXTR
, CAST(PRUOPN/100 as numeric(9,2)) as PRUOPN
, CAST(PRAOPN/100 as numeric(9,2)) as PRAOPN
, prmatc
, prrcdj
, pRAID
FROM [backup].[backup].[migration2].[F43121V1]
WHERE PRDCTO <> 'OT'
AND PRDOCO IN ( @DOC1, @DOC2 )
and PRMATC = '1'
AND PRUOPN <> 0
AND PRAOPN <> 0
ORDER BY PRDOCO , PRLNID;
GO
--------------------------------------------------------------
This the Run SQL Scripts version of my query:
--------------------------------------------------------------
DROP variable SJL1.@DOC1;
DROP variable SJL1.@DOC2;
Create variable SJL1.@DOC1 NUMERIC(8,0) DEFAULT(382144);
Create variable SJL1.@DOC2 NUMERIC(8,0) DEFAULT(382584);
select
'F4301' AS FILENAME
, PHDCTO
, PHDOCO
, PHMCU
, PHTRDJ
FROM backup.ifsprod.F4301
WHERE PHDOCO IN ( SJL1.@DOC1 , SJL1.@DOC2 )
ORDER BY PHDOCO ;
SELECT
'IFS_POLN' as FILENAME
, PDDCTO
, PDDOCO
, PDMCU
, CAST(PDLNID/1000 as numeric(7,3)) as PDLNID
, PDLNTY
, PDLITM
, PDLTTR
, PDNXTR
, PDTRDJ
, PDSRC4
, IFS_LNID
FROM backup.migration2.ifs_POLN
WHERE PDDOCO IN ( SJL1.@DOC1 , SJL1.@DOC2 )
ORDER BY PDDOCO , PDLNID ;
Select
'F4311' as FILENAME
, PDDOCO
, PDDCTO
, PDMCU
, CAST(PDLNID/1000 as numeric(7,3)) as PDLNID
, PDLITM
, PDLNTY
, PDNXTR
, PDLTTR
, CAST(PDUORG/100 as numeric(9,2)) as PDUORG
, CAST(PDUREC/100 as numeric(9,2)) as PDUREC
, CAST(PDUOPN/100 as numeric(9,2)) as PDUOPN
, CAST(PDPRRC/10000 AS NUMERIC(9,4)) AS PDPRRC
FROM BACKUP.IFSPROD.F4311LA
WHERE PDDOCO IN ( SJL1.@DOC1 , SJL1.@DOC2 )
ORD7ER BY PDDOCO , PDLNID ;
SELECT
'F43121' as FILENAME
, PRDCTO
, PRDOCO
, PRMCU
, CAST(PRLNID/1000 as numeric(7,3)) as PRLNID
, PRLTTR
, PRNXTR
, CAST(PRUOPN/100 as numeric(9,2)) as PRUOPN
, CAST(PRAOPN/100 as numeric(9,2)) as PRAOPN
, prmatc
, prrcdj
, pRAID
FROM backup.migration2.F43121V1
WHERE PRDCTO <> 'OT'
AND PRDOCO IN ( SJL1.@DOC1 , SJL1.@DOC2 )
and PRMATC = '1'
AND PRUOPN <> 0
AND PRAOPN <> 0
ORDER BY PRDOCO , PRLNID;
Best Regards,
Steve Landess
(512) 289-0387
midrange.com
