Re: SSMS and IBM i

Charles! OMG! That's EXACTLY what I have in my monthly reminder.

On Thu, Feb 12, 2026 at 9:50 AM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

You've got the answer, but for reference (originally posted by me almost 20
years ago)...

-- Pull all rows from the table(s) back to MS SQL server and do the where
locally
select * from LINKEDSVR.MYIBMI.MYLIB.MYTBL where locnbr = '00335';

-- Sends the statement to linked server for processing
select * from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
''00335''');

--OPENQUERY() isn't just for SELECTS
delete from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
''00335''');

HTH,
Charles

On Wed, Feb 11, 2026 at 5:45 PM Stephen Landess <steve_landess@xxxxxxxxxxx

wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.