smaloney
Member
I'm a SQLServer DBA and have inherited about 50 SQLServer/SSIS jobs that do very complicated SQL to extract data from E1 on an AS400 using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider" and places this data into SQLServer tables.
There is talk of migrating from AS400/JDE to WIN/SQLServer/JDE.
Will all of that SQL in these SQLServer SSIS jobs ( which I'm guessing is DB2 SQL syntax? ), work "as is", or will I need to rewrite this using native SQLServer TSQL?
Here is an example of the SQL I see:
SELECT
SDADDJ as "Transaction Date",
SDTRDJ as "Order Date",
SDDCTO as "Order Type",
SDLNTY as "Line Type",
SDDOCO as "Order Number",
trim(SDMCU) as "Branch",
SDZON as "Zone",
SC90.JDEUDC(SDZON,GRAPHIC(UX'0034003000200020',4,13488),GRAPHIC(UX'005a004e',2,13488),3,01,'PRODCTL') as "Zone Description",
Case
WHEN CC.ABAN8 is null then '99999999'
ELSE CC.ABAN8 end as "Ship To Address",
SDAN8 as "Customer Number",
SDSHAN as "Customer Sold To Number",
SDITM as "Short Item",
Case
WHEN IMLITM is null then '00000000'
ELSE IMLITM end as "Long Item",
SDSOQS as "Quantity",
float(SDAEXP)/100 AS "Ext Price",
float(SDECST)/100 as "Ext Cost",
float(COUNCS)/10000*SDSOQS as "Current Cost"
FROM
FWMPRDDTA.F42119 F42119
LEFT OUTER JOIN SALES.COST07 COST07
ON
F42119.SDITM = COST07.COITM AND
F42119.SDMCU = COST07.COMCU
LEFT OUTER JOIN SALES.Cube_Prod CP
ON
F42119.SDLITM = CP.IMLITM
LEFT OUTER JOIN FWMPRDDTA.CUBE_CUSTOMER2 CC
ON
F42119.SDSHAN = CC.ABAN8
WHERE
SDDGL >= 104001
AND SDSOQS <> 0
AND trim(SDGLC) IN( 'IN20', 'NS40', 'NS42', 'WR30', 'OS04', 'SV01', 'SV02', 'SV04', 'IN30', 'IN40' )
AND trim(SDDCTO) IN( 'SC', 'SD', 'SF', 'SO', 'SP', 'SR', 'SV', 'SZ', 'S1', 'S2', 'SM' )
AND trim(SDKCOO) IN('00001', '00006' )
and (SDAEXP <> 0 or trim(SDMCU) = '60')
There is talk of migrating from AS400/JDE to WIN/SQLServer/JDE.
Will all of that SQL in these SQLServer SSIS jobs ( which I'm guessing is DB2 SQL syntax? ), work "as is", or will I need to rewrite this using native SQLServer TSQL?
Here is an example of the SQL I see:
SELECT
SDADDJ as "Transaction Date",
SDTRDJ as "Order Date",
SDDCTO as "Order Type",
SDLNTY as "Line Type",
SDDOCO as "Order Number",
trim(SDMCU) as "Branch",
SDZON as "Zone",
SC90.JDEUDC(SDZON,GRAPHIC(UX'0034003000200020',4,13488),GRAPHIC(UX'005a004e',2,13488),3,01,'PRODCTL') as "Zone Description",
Case
WHEN CC.ABAN8 is null then '99999999'
ELSE CC.ABAN8 end as "Ship To Address",
SDAN8 as "Customer Number",
SDSHAN as "Customer Sold To Number",
SDITM as "Short Item",
Case
WHEN IMLITM is null then '00000000'
ELSE IMLITM end as "Long Item",
SDSOQS as "Quantity",
float(SDAEXP)/100 AS "Ext Price",
float(SDECST)/100 as "Ext Cost",
float(COUNCS)/10000*SDSOQS as "Current Cost"
FROM
FWMPRDDTA.F42119 F42119
LEFT OUTER JOIN SALES.COST07 COST07
ON
F42119.SDITM = COST07.COITM AND
F42119.SDMCU = COST07.COMCU
LEFT OUTER JOIN SALES.Cube_Prod CP
ON
F42119.SDLITM = CP.IMLITM
LEFT OUTER JOIN FWMPRDDTA.CUBE_CUSTOMER2 CC
ON
F42119.SDSHAN = CC.ABAN8
WHERE
SDDGL >= 104001
AND SDSOQS <> 0
AND trim(SDGLC) IN( 'IN20', 'NS40', 'NS42', 'WR30', 'OS04', 'SV01', 'SV02', 'SV04', 'IN30', 'IN40' )
AND trim(SDDCTO) IN( 'SC', 'SD', 'SF', 'SO', 'SP', 'SR', 'SV', 'SZ', 'S1', 'S2', 'SM' )
AND trim(SDKCOO) IN('00001', '00006' )
and (SDAEXP <> 0 or trim(SDMCU) = '60')