Querying Data From JDE World or Co-Existance OneWorld XE
Here is a short list of resources and some short examples of how to query or extract data from JD Edwards World or OneWorld XE (ExterpriseOne), this also applies to most databases on an iSeries AS/400.
First a warning. Whenever you have direct SQL access to any database you have the power in one accidental enter key press when you ment to press a backspace, and bam! you’ve wiped out your production address book. And whats more older versions of DB2 do NOT incorporate SQL transactions… so you cant rely on the old ‘begin tran / rollback tran’ safety method. you can however use the block comment method to keep your risky updates/deletes away from the sql interpreter (/* */) until your ready for them.
Additionally, JDE in my experiance is very picky about its data, so think twice before you decide to insert or update, and be prepared to restore your database (or at least that table)…
Ok so heres what you need to get on a AS/400 (iSeries) DB2 database.
- A user account with ODBC access to the AS/400
- A good knowledge of the SQL command language
- IBM Client Access installed on your Windows PC (make sure the ODBC and OleDB drivers are installed)
- if you are not on a windows pc you’ll need to find another driver for accessing your iSeries
- A Query Tool
- DB2 SQL Reference Manual, and a DB2 SQL Programming Manual
- Usually you want to turn on the ‘Translate CSSID’ option in your SQL Connection string or your ODBC connnection profile.
Otherwise youll see what looks like garbage for strings, actually this is the database returning the data back in byte arrays.
Do you know what you want to query from JDE, and want to know what the SQL statement is?
Just post your question here as a comment… I will be more than happy to answer what I can.
2 Responses to “Querying Data From JDE World or Co-Existance OneWorld XE”
Comment from Tony
Time March 8, 2010 at 4:33 pm
i’m not sure i totally understand what you want to do here, but i think you are asking if there is a way to link the table in with the long or ‘alias’ column names
You probably need to create a ‘linked table’ in MSAccess to the jde table … then use an MSAccess ‘query’ to rename the column names
OR
use the JDE Enterprise One ODA driver instead of straight ODBC … that driver has options to output the Alias column names instead of the short column names ( AddressNumber instead of AN8 )
Write a comment
You need to login to post comments!
Comment from dawn500
Time March 8, 2010 at 1:51 pm
Is there anywhere in query to mark to show the text definition of the field instead of the actual field name? I am creating an access 2007 database and pulling information across an odbc for a query in JDE world but everytime it blows my access field names out using the jde names, if I could specify to use the text definition it would be the same as the access database.
thanks,
Dawn