× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.