Second Derivative Community Forum

So What is the Deal?

(here are the links to free open source software for 1. adding warehouse management portal to your JD Edwards system (http://www.randrinc.com/2d_randr/orderportal/catalog_presentation/by_product/0/JD+Edwards+Version+of+WMP/0/0/0/0/0) and 2. adding an Information Portal running on a linux server accessing the data from your JDE system (http://www.randrinc.com/2d_randr/orderportal/catalog_presentation/by_product/0/Info+Portal+for+JD+Edwards/0/0/0/0/0)

If you have the dubious pleasure of needing to access data from JD Edwards World Database on the AS400 or iSeries from your Linux server (and I would guess this applies to Unix and AIX as well), then I hope this saves you some of the frustrations that we went through.

First to clarify things, this is not an OS400 thing. This is a JD Edwards thing. (or JD Freaking Edwards as I like to call it). We are not having any trouble getting data out of DMAS, MAPICS, Wynne Systems Rentalman or VAI System 2000.

Short Background. The iSeries (note, any iSeries reference also applies to the AS/400) stores its DB2 data in EBCDIC while most other system store data in ASCII or UTF8. The data is normally in CCSID 37, which is the default. We use the jt400.jar file in our projects and don't have a problem accessing data through our java applications on Linux servers (Red Hat and Ubunttu). Here is a link to download a copy or the jt400.jar. https://sourceforge.net/projects/jt400 or you can download from our server at http://www.randrinc.com/2d_randr/files/jt400.jar.

The problem with JDE is that some of the fields in their tables are stored in CCSID 65535. (WT..?!?! Yeah, go figure.) CCSID 65535 tells the iSeries to NOT convert the data but leave it in hexidecimal. And they were terribly inconsistent, so who knows what the heck they were thinking. Here is a definition of CCSID 65535 “A CCSID of 65535 indicates that the data is hex and should not be converted.” from the following article: http://www.itjungle.com/mpo/mpo091103-story03.html

So for example, in the address file (F0116) the city and address line1 are CCSID 65535 but address line 2 and 3 are normal CCSID 37. To see this on the iSeries use the following command: DSPFFD FILE(library/filename).

Getting the Data
You have to use SQL to retrieve the data (I will explain why below). And it is pretty simple, here are some example:

ifnull(CAST(MCDC AS CHAR(40) CCSID 37),'** not found **') as WAREHOUSE_NAME

CAST(CCCO AS CHAR(5) CCSID 37) as COMP_NO,
CAST(DRKY AS CHAR(10) CCSID 37) as CUST_CLASS,

Here is what we had to do getting info out of the F0005 (the everything but the kitchen sink table, which I guess I can understand since the older RPG had a 25 file limit in programs. But what is with right aligning alpha fields?!?!)

select
CAST(DRKY AS CHAR(10) CCSID 37) as DIV_NO, - cast converts the data
DRDL01 as DIV_NAME
from
[LIB2].F0005
where
STRIP(DRSY) = '41' AND – strip removes all those leading blanks
STRIP(DRRT) = 'S1'
order by 2

These examples are from our Information Portal, which we are working to get up to source forge around the middle of 2008. Our Information Portal Query Manager replaces [LIB2] with the JDE library, so when the query actually runs [LIB2]. F0005 becomes RDRDTA.F0005.

What if you don't use SQL?
What if you are not using SQL? We ran into the biggest problem with our Data Migration Portal (which should be up on source forge second quarter of 2008). We use hibernate to access the data. From my Eclipse environment on the desktop (windows) we did NOT have any problems. We wrote the following method to handle the conversion of the CCSID 65535 fields (java):

public static final String convertEBCDIC37ToUnicode(String ebcdic37String) {
//String encoding = "Cp1047";
String encoding = "Cp037";
byte[] ebcdic;
String reconsituted = null;
try {
ebcdic = ebcdic37String.getBytes();
reconsituted = new String( ebcdic, encoding );
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return reconsituted;
}
The program works great when running it from my desktop My Eclipse but the minute we deployed to our Linux server it did not work.

Well after doing quite a bit of research I decided that I was definitely up a creek without a paddle and I was not going to rewrite the Data Migration Portal to use sql instead of hibernate, soooo....

Solution:

1.Created new tables on the iSeries that did NOT define the fields as CCSID 65535 and had different column names, e.g. SDDOCO became XDDOCO in my new file.

2.Created a program on the iSeries that read the files I was after (F4211 and F0311).

3.In the program I also put the logic that pulled name out of F0101, address out of F0116, and catalog information out of F0005.

4.In the program I used move or movel for the alpha fields and z-add for the numeric.

5.Note, I tried just doing cpyf on the files, but the copies had the same field definitions. I also tried leaving the field names the same and just doing a read of the one file and output to the new, but those did not work. I think I tried eval statements, but I honestly can't remember. Move and z-add worked and by then I had wasted so much time I just wanted to kill somebody.

6.Hibernate accesses the data in my new files from the Linux server without any problems.

7.I had moved the updates to the files to an sql statement in my earlier experiments, so I just left it that way.

Ann Richmond
ann@randrinc.com

OMG I haven't had to support JDE or a AS400 for 18 months now! for the 1st 3 months I was in withdrawl mode & checking for AS400/JDE related jobs after a while that faded now you would have to give a labotamy to work on JDE or One World again.

Thanks for the trip down memory lane

Cary

More JD Edwards stuff.

We have developed a warehouse management portal that runs on the AS400/iSeries. We have a version that runs against JD Edwards.

It is up on sourceforge.net and available for free download if you are interested.

Here is the link to our web site, that explains about it:

http://www.randrinc.com/2d_randr/orderportal/custom/CUSTOM_PAGE_20/Randr+Inc+2nd+Derivative+Warehouse+Management+Portal

Also a link which includes all the warehouse management portal documentation for the JDE version, including an overview brochure.:

http://www.2d-warehousemanagementportal.com/2d_randr/orderportal/custom/CUSTOM_PAGE_19/WMP+For+JD+Edwards

I hope someone can use it, as it works really well. We also have versions developed to interface with DMAS and VAI System 2000 also up on Sourceforge.net.

We will be rewriting the warehouse management portal into java and it will be part of our eBusiness Suite Transaction Portal (of which there are several industry versions on source forge), but don't know if I will get it done this year or not.

Also our Information Portal for JDE should be usable for anyone with JDEdwards. It provides you will more flexible, real time reporting over the web, pulling the data from your iSeries. I put the source and war file bundled with the warehouse management portal. So download that from sourceforge to get the Information Portal.

Here is a link that explains about the Information Portal.

http://www.randrinc.com/2d_randr/orderportal/custom/CUSTOM_PAGE_14/Randr%2C+Inc.+2nd+Derivative+Information+Portal

Let me know if you have any questions.
Thanks
Ann Richmond