SQLServer SSIS jobs / Conversion from AS400/JDE to WIN/SQLServer/JDE

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')
 
You will have to re-write most of these since DB2/400 SQL and Microsoft T-SQL have some slight differences. But the majority of the SQL Statement will come over as-is and shouldn't be hard to re-write.

Of course, if you're moving from DB2/400 to SQL Server - then it should be easier to move data from SQL Server to SQL Server than from iSeries DB2/400 to SQL Server. Just a thought.
 
You will have to re-write most of these since DB2/400 SQL and Microsoft T-SQL have some slight differences. But the majority of the SQL Statement will come over as-is and shouldn't be hard to re-write.

Of course, if you're moving from DB2/400 to SQL Server - then it should be easier to move data from SQL Server to SQL Server than from iSeries DB2/400 to SQL Server. Just a thought.

Thanks for your response.

I've noticed that a lot the SSIS jobs currently have a "data conversion" step in them where dollars for example are divided by 100 or units or hours are / 1000. It leads me to believe there are no scaled numbers and numeric data is stored as integers in JDE on the AS400.

Will this also be true in the SQLServer version of JDE database?
 
Correct. JDE has a Data Dictionary that interprets the data - so a number 100100 might be $1001.00 or might be $10.01 based on how the data dictionary interprets the data. No decimals are held at the database level, and its important to understand the data dictionary prior to making assumptions on how a value is interpreted.
 
I believe database level decimals are supported. The File Decimals property of the DD item honors the actual decimal precision of the underlying database column. Most stock JDE DD items utilize the display decimal paradigm, but the alternative is available. And needed if mapping to an external table with existing decimal precision.

Craig
 
Again, thanks so much for your replies.

I see a lot of these calls to SC90.JDEUDC where hex codes are passed. When I make the same call using quoted strings, I get the same exact answer?

Is there a good reason to use hex codes instead of quoted strings?

I'm trying to get prepared to convert all the DB400 SQL to SQLServer TSQL and am trying to understand what these things do.

Thanks

SELECT DISTINCT
SC90.JDEUDC(
SDZON,
GRAPHIC( UX'0034003000200020', 4, 13488 ),
GRAPHIC( UX'005a004e', 2, 13488 ),
3,
01,
'PRODCTL'
),
SC90.JDEUDC(
SDZON,
'40',
'ZN',
3,
1,
'PRODCTL'
)
FROM FWMPRDDTA.F42119

 
Its difficult to say, as "SC90.JDEUDC" is not a standard JD Edwards table. FMWPRDDTA.F42119 IS a standard table.
 
"SC90.JDEUDC"

That actually looks like a SQL function someone created to do a UDC lookup. The last parm being the library where F0005 is located.

Craig
 
Back
Top