Re: MS SQL 2012 OLEDB2 connection to the IBM i.

Here is my script


DECLARE @MyDatetime datetime = '1900-01-01 00:00:00'

delete from DmStagingVD.dbo.DMFPRODUCTENTITY;

insert into DmStagingVD.dbo.DMFPRODUCTENTITY (

[DISPLAYPRODUCTNUMBER],
[ECORESPRODUCTTRANSLATION_LANGUAGEID],
[ECORESPRODUCTTRANSLATION_NAME],
[ITEMID],
[PRODUCTSUBTYPE],
[PRODUCTTYPE],
[ABCREVENUE],
[BOMUNITID],
[CUSTVENDEXTERNALITEM_EXTERNALITEMID],
[DATEBLOCKED],
[DEFAULTORDERTYPE],
[ECORESPRODUCTTRANSLATION_DESCRIPTION],
[ECORESSTORAGEDIMENSIONGROUP_NAME],
[ECORESTRACKINGDIMENSIONGROUP_NAME],
[EXIMPRODUCTGROUPTABLE_IN_PRODUCTGROUP],
[GROSSDEPTH],
[GROSSHEIGHT],
[GROSSWIDTH],
[HEIGHT],
[INTRACODE],
[INVENTTABLEMODULEINVENT_PDSPRICPRECISION],
[INVENTTABLEMODULEPURCH_UNITID],
-- [INVENTTABLEMODULEINVENT_UNITID],
[INVENTTABLEMODULESALES_PDSPRICPRECISION],
[INVENTTABLEMODULESALES_UNITID],
[ITEMBUYERGROUPID],
[ITEMTYPE],
[LOWESTQTY],
[MATCHINGPOLICY],
[MODELGROUPID],
[NAMEALIAS],
[NETWEIGHT],
[PBAINVENTITEMGROUPID],
[PDSVENDORCHECKITEM],
[PHANTOM],
[PRIMARYVENDORID],
[PRODFLUSHINGPRINCIP],
[REQGROUPID],
[SEARCHNAME],
[USEALTITEMID],
[USEEANSTANDARDBARCODE],
[ZEROPRICEVALID],
[RECVERSION],
[PARTITION],
[RECID]
)
(
Select
[impn],
'EN-US',
RTRIM(imdsc) + ', ' ,
-- + substring(imdsc2,1,20) ,

[impn],
'PRODUCT',
'ITEM',
[imabc],
[IMUTMS],
[IMVDPN],
-- Date blocked
@MyDatetime,
-- default order type
CASE
WHEN [IMCODE] = 'IR' THEN 'PURCHASE ORD'
ELSE 'PRODUCTION'
END,
-- Product translation description
CASE When
(SELECT REEDSC FROM [DEVELOP].[S1022466].[KBM400MFG].[FKITEXTD] A2
WHERE A1.IMCO = A2.IMCO
AND A1.IMPN = A2.IMPN
and reline = 1
and RESUBL = 0) is null then ' '
ELSE
(SELECT REEDSC FROM [DEVELOP].[S1022466].[KBM400MFG].[FKITEXTD] A2
WHERE A1.IMCO = A2.IMCO
AND A1.IMPN = A2.IMPN
and RELINE = 1
and RESUBL = 0)
END,
-- WHMGMT or SALES
'WHMGMT',
-- dimension tracking group name
'NONE',
-- [EXIMPRODUCTGROUPTABLE_IN_PRODUCTGROUP]
[IMPRFG],
-- [GROSSDEPTH]
CASE When
( SELECT PFIDPT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN )is null then 0
ELSE
( SELECT PFIDPT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN )
END,
-- [GROSSHEIGHT]
CASE When
( SELECT PFIHGT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN ) is null then 0
ELSE
( SELECT PFIHGT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN )
END,
-- [GROSSWIDTH]
CASE When
( SELECT PFIWDT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN ) is null then 0
ELSE
( SELECT PFIWDT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN )
END,
-- height
[IMHGHT],
-- intracode
[IMCCOD],
-- [INVENTTABLEMODULEINVENT_PDSPRICPRECISION]
4,
-- [INVENTTABLEMODULEPURCH_UNITID]
[IMUTMS],
-- [INVENTTABLEMODULESALES_PDSPRICPRECISION]
4,
-- [INVENTTABLEMODULESALES_UNITID]
[IMUTMS],
-- [ITEMBUYERGROUPID]
[IMBUY],
-- item type
'ITEM',
-- lowest qty ('MinMax')
0,
-- matching policy
'THREE-WAY',
-- model group id
'STANDARD',
-- name alias
substring([IMDSC],1,20),
-- net weight
CASE WHEN
(SELECT PFPCWT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN ) is null then 0
ELSE
(SELECT PFPCWT FROM [DEVELOP].[S1022466].[KBM400CSTM].[IMF929V]
WHERE PFCO# = A1.IMCO
AND PFPN = A1.IMPN )
END,

-- [PBAINVENTITEMGROUPID]
'STANDARD',
-- [PDSVENDORCHECKITEM]
'Warning ',
--only',
-- Phantom
[IMPHAN],
-- Primary vendor
[IMPVND],
-- product flushing principle
'FINISH',
-- Regular group ID
'MinMax',
-- Serach name
substring([IMDSC],1,20),
-- Use alt item ID
'NEVER',
-- use EAN Standard barcode
'YES',
-- zero price valid
'YES',
-- [RECVERSION],
1,
-- [PARTITION],
1,
-- [RECID]
1

from [DEVELOP].[S1022466].[KBM400MFG].[FKITMSTR] A1
where A1.IMCO = 5
and A1.IMACD = '1'
and A1.IMSTS = 'A'
and A1.IMPN not like 'DEV14.%'
and A1.IMPN not like 'DEV15.%'
and A1.IMPN not like 'DEV16.%'
and A1.IMPN not like 'DEV17.%'
and A1.IMPN not like 'DEV23.%'
and A1.IMPN not like 'DMULL.%'
and A1.IMPN not like 'D1390.%'
and A1.IMPN not like 'D1490.%'
and A1.IMPN not like 'D1590.%'
and A1.IMPN not like 'D1690.%'
and A1.IMPN not like 'D1790.%'
and A1.IMPN not like 'D1990.%'
and A1.IMPN not like 'D2090.%'
and A1.IMPN not like 'D2390.%'
and A1.IMPN not like 'PS.%'

)

GO


On Mon, Sep 8, 2014 at 2:41 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Note that the OPENQUERY function is the key to doing just about anything
quickly...as that passes through the SQL statement to the IBM.

You can do
insert openquery(...)
delete openquery(...)
update openquery(...)

http://msdn.microsoft.com/en-us/library/ms188427.aspx

Charles



On Mon, Sep 8, 2014 at 2:34 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

The format matters...

From my notes:
Linked Server name in MS SQL Server: FALCONPROD
IBM iSeries system name: MYIBM
Library: HLTHPRDFILE
Table: LOCATP

-- Pull all rows from the table(s) back and does the where locally
select * from FALCONPROD.MYIBM.HLTHPRDFIL.LOCATP where locnbr =

'00335';

-- Sends the statement to linked server for processing
select * from openquery(FALCONPROD, 'select * from customp where

locnbr =

''00335''');

HTH,
Charles


On Mon, Sep 8, 2014 at 2:14 PM, Darryl Freinkel <
dfreinkel@xxxxxxxxxxxxxxxxx> wrote:

I have connected a SQL 2012 to the AS/400 and the connection is there.
However, I noticed that when I run scripts against the AS/400, they

are

slow. Looking at what it is doing, I see either



1. SQL 2012 is reading all records in the database into memory

and

then doing the selection in SQL 2012. We have 60 million records. All

I

want
is 2 records.

2. The AS/400 is ignoring the where and is reading all records
sequentially to find the requested records.



This is the first time I am doing this. What am I missing?



I am on V5R4.



TIA



Darryl Freinkel

Assignment 400 Group, Inc.





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

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

list

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

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