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