Querying Data From JDE World or Co-Existance OneWorld XE
5 April 2006Here 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.
No comments yet
Leave a Reply
You must be logged in to post a comment.