Shannon's JD Edwards CNC Blog: July 2016

Friday 29 July 2016

How fast is a JDE full package in AWS cloud.

The amazing thing about cloud is that you should get the same performance as me if we choose the same specification on disk, database and EC2 – as everything is more of a commodity than ever.

I have a deployment server, M4 large

RDS oracle database, m3.2xlarge – no dedicated IOPS

Full client starts 15:00

Client finishes 16:07!!! 1 hour and 7 minutes

Server is linux, m3.xlarge

Server starts 16:08

finished 17:17 – 1 hour and 9 minutes.

So… I have a full package build completed, enterprise server and client in 2 hours and 16 minutes. Try and tell me what bottlenecks I have in AWS and what is letting me down – I’ll tell you – NOTHING!

Friday 22 July 2016

Dates as strings… What? We are JDE–our dates are 6 digit numbers…

Houston, we have a problem.

I got some development done recently, and some of the date fields were incorrectly defined as strings. I guess I cannot be too critical, as the data from the spreadsheet that was going to be loaded into the file was a string… But… Well, read between the lines, it was unexpected.

Now we have 40+ DD items, 6 tables and an entire suite of code (including mobile applications) that have been coded on the basis that the dates are strings. unfortunately, this is not going to work.

my two DD items are

I can find the tables that are using these with the following oracle SQL:

>select table_name, column_name from all_tab_columns where owner = 'CRPDTA' and (column_name like '__55RPDATE' or column_name like '__55SCDATE');

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
F55ACCCH                       AM55SCDATE
F55ACCCH                       AM55RPDATE
F55ACCRC                       CA55SCDATE

So, these tables have 100,000+ rows of data, so I’m going to do a little bit of cheating.

I’m going to create a temp column at the end of the table as a date

Date in JDE is defined as NUMBER(6) in oracle

alter table crpdta.F55accch add ( DATETEMP number(6)) ;

I’m going to populate this column with the date value of the string

select am55scdate, to_date(am55scdate, 'YYYY-MM-DD'), to_number(concat('1',to_char(to_date(am55scdate, 'YYYY-MM-DD'),'YYDDD'))) from crpdta.f55accch where AM55SCDATE like '20%';

select am55scdate
--,to_date(am55scdate, 'YYYY-MM-DD')
--,to_number(concat('1',to_char(to_date(am55scdate, 'YYYY-MM-DD'),'YYDDD')))
from crpdta.f55accch where AM55SCDATE not like '20%';

select am55scdate
,to_date(am55scdate, 'DD/MM/YYYY')
,to_number(concat('1',to_char(to_date(am55scdate, 'DD/MM/YYYY'),'YYDDD')))
from crpdta.f55accch where AM55SCDATE not like '20%' and am55scdate > ' ';

update crpdta.F55accch set DATETEMP = to_number(concat('1',to_char(to_date(am55scdate, 'YYYY-MM-DD'),'YYDDD'))) where AM55SCDATE like '20%';
update crpdta.F55accch set DATETEMP = to_number(concat('1',to_char(to_date(am55scdate, 'DD/MM/YYYY'),'YYDDD'))) where AM55SCDATE like '1%';
commit;

I’m going to clear the problem column

update crpdta.F55accch set am55scdate = '';
commit;

I’m then going to change the data type of the problem column

alter table crpdta.F55accch modify am55scdate number(6);

Finally copying the data from the temp column and dropping the temp column.

update crpdta.F55accch set am55scdate = datetemp;

alter table crpdta.F55ACCCH drop column datetemp;

Although I could do this a little more simply, at least the desc of the table will have the correct column sequence, it’s the little things that matter sometimes.

Monday 18 July 2016

0–zero downtime upgrade 9.1 to 9.2–work with me!

This is going to be a soapbox post, but sometimes you need to get up there and do that.

I think that being agile and dynamic is important, so I think that upgrading your ERP regularly is important too.

One of the main problems with regular upgrades is the time it takes to test, the people that do the testing, everything about UAT seems to be painful. Also the big bang nature of JD upgrades, where everything needs to be done at once. Do you think it would be nice to be able to upgrade a portion of JDE to 9.2 (AP for example), do all the testing and training and then choose the next module for retrofit testing and training. Keeping both 9.1 and 9.2 active at any point in time.

I think it’s an option to do a hybrid blue / green deployment model for JD Edwards, and especially the upgrade between 9.1 and 9.2.

What do I mean by blue / green deployment. I mean have both application releases talking to a single database. Be live on 9.1 and 9.2 at the same time and control the processes that run on 9.2 carefully. Slowly migrating the users from 9.1 to 9.2 when the system is ready. NO big bang. Users come over when the retrofitting is done and the testing is complete. This could be done with an additional URL or it could even be done with some really smart menus (call the 9.2 application from 9.1 menu as a link as opposed to an app) [if your cookies and sites were set up correctly].

I still advocating testing, lots of testing… But I’m also advocating making the transition more gentle with more production based testing and better monitoring from the IT team to ensure that things are not going wrong. There are holes in all IT testing, UAT testing has problems. Integrations are not complete, data sets can be different – there are always issues with testing.

So lets say I worked out which tables are changed between 9.1 and 9.2 – Imagine I created a list for you.

('F04514WF','F07600','F07601','F078504','F07855','F30UI004','F30UI008','F30UI012','F31B03E','F41UI001','F42565','F427UI01','F4801Z1','F54HS01','F54HS01M','F54HS02','F54HS02M','F54HS03','F54HS03M','F54HS06','F54HS06M','F740018A','F74405','F74B200','F74L920','F74RUI31','F74RUI41','F74S72','F74S79','F75I100','F75I10A','F75I15A','F75I20A','F75I344Y','F75I396B','F75Z0005','F7608B','F76B016')

You then determined if you had data in any of these tables. You then did some smarts to retrofit the TC (table conversion) logic into a TER or temp table trigger (like 21CFR11 auditing of sorts) and made the 9.1 transactions populate the additional columns in 9.2 – for example. You could also have a test in your trigger to say “if 9.2 system user then ignore trigger, else fire), because you only want your TC trigger to fire when 9.1 connects, as 9.2 will be native with the new table columns.

okay, you still with me? We’ve got 2 systems, 2 DD’s, 2 OL’s, 2 server maps – two path codes, but only one set of data and control.

There would need to be a moratorium on development in 9.1 during the project, but that could be managed.

There would need to be some synchronisation of system tables between the systems (although I’d be tempted to use views – as I don’t think that any of them are changing).

You’d need to be very careful with UDO’s, they might cause some problems between the two releases.

You would need to be careful with single threaded queues too, as they would exist on two systems.

But, at the end of the day – you could have 9.2 and 9.1 running against a single data source, which would allow you to gradually migrate functionality without big bang.

Technology like AWS would make this very easy too, as you could scale up and scale down the environments as modules moved over from one system to the other.

Eventually when everyone was using 9.2, you could phase out the use of 9.1 altogether, using DNS changes or what ever you wanted to do.

Your investment in this is table triggers for the data tables that have changed and have data, also some investment in ensuring things like passwords and default printers and things are kept in synch too. You need to keep some additional machines up and running while you have two environments running, but that is it.

You could also put all of this procedure through change control and the standard JDE SDLC (apart from DB triggers and things) to ensure that environments would concurrently in UA and PY.

I think it could work – what about you?

2 weeks of AWS production activity

You don’t just go live in AWS like you would “on prem”, you adapt to your user base and load.

We are in the adaption phase for one of our clients who has recently gone live with JD Edwards in AWS. Exciting times for all involved.

Adaption involves the nexus of performance and cost effectiveness. What do I mean by that, well – what is the lowest spec environment that I can run up to provide the same end user experience. This will save the client money. I can then convert this spec to a “reserved instance” metric – which will save even more money.

Look at the CPU specs for the environment over the last 2 weeks.

Wow, some of the web servers jumped around a bit – but this was a problem with stuck threads in WLS, we’ve sorted this and can see that the average CPU utilisation for all enterprise and web tiers is very low on average.

So now we know how things look from a CPU POV, let’s look at the application performance so that if we make changes – we have a measuring stick – or at least something to measure against.

So we can see from above that evaluating information from over 90000 ERP pages, we start to form a pattern of performance. We can of course drill down to the applications themselves:

This gives us a great idea of the interactive performance. Batch is really easy too, as we have F986114 to carve up for that.

The combination of google analytics and F986114 allows us to change the underlying technology with confidence and ensure that we provide a consistent level of service to our customers whilst improving the price point for the hosted environment.

We are going to fine tune this environment, as we have all of the information that we need. We might change the host type for the web servers to get a little more bang for buck and also the enterprise servers. At this stage, the DB server (which is RDS) might also be made a little smaller, as we are not even close to stretching that. Watch this space.

Wednesday 13 July 2016

too much whitespace in your strings

Have you ever gone to a QBE and typed in what you thought was a great Query By Example and not been given any results… Then after a bunch of searching you find that you’ve loaded all of your data with a heap of whitespace at the end of the string.

So as above, I use and * and I see everything. Doh, using EXCEL and SQLDeveloper to load a bunch of data and I’ve got all my fields padded.

I then generate the following SQL to trim all of the NCHAR fields:

SELECT 'update crpdta.' || table_name || ' set ' || column_name || ' = trim(' || column_name ||' );' from all_tab_columns
where table_name in ('F55ACCRM','F55ACCCH','F55ACCRC') and data_type = 'NCHAR';

This creates a pile of updates to mod the data:

update crpdta.F55ACCAM set AMJOBN = trim(AMJOBN );
update crpdta.F55ACCAM set AMPID = trim(AMPID );
update crpdta.F55ACCAM set AMUSER = trim(AMUSER );
update crpdta.F55ACCAM set AM55ITMTY = trim(AM55ITMTY );

but only if the DD type is VARCHAR (type 20). If it’s type 2 (CHAR), the you will need to LPAD

See that the screen above is type 2 – CHAR / NCHAR. Loads of wasted space!

SELECT 'update crpdta.' || table_name || ' set ' || column_name || ' = rpad(' || column_name ||', ' || data_length/2 ||' );' from all_tab_columns
where table_name in ('F55ACCAM') and data_type = 'NCHAR';

update crpdta.F55ACCRC set CA55ITMCID = rpad(CA55ITMCID, 40 );
update crpdta.F55ACCRC set CA55PAID = rpad(CA55PAID, 20 );
update crpdta.F55ACCRC set CAJOBN = rpad(CAJOBN, 10 );
update crpdta.F55ACCRC set CAPID = rpad(CAPID, 10 );
update crpdta.F55ACCRC set CAUSER = rpad(CAUSER, 10 );
update crpdta.F55ACCRC set CA55RENYR = rpad(CA55RENYR, 1 );
update crpdta.F55ACCRC set CA55DETCOR = rpad(CA55DETCOR, 1 );

Note that I’m dividing by two, because the character length is half the byte length, because there are “2 bytes for eeevvvrrryyyy char” Sung to the same tune of beach boys “Two girls for eeeevvvrrryy boy” in their legendary Surf City song.

Of course, JDE knows all of this. If your pacakge build and deploy have occurred over the correct DD items, then the SQL Statement will include the correct amount of spaces. BUT. If there are mistakes, or you’ve had to perform emergency DD surgery – then the runtime size of the data items is probably wrong. (Like me for example).

got a new deployment server, getting this when trying to install an ESU?

Basically the executable for the ESU installer is telling me porkies. So when I look closer, I see that the ESU history has not been moved into the registry of the replacement deployment server.

Okay, this is going to be easy, I need to get a export of the following registry key from the old machine:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\JDEdwards

lucky me found one, we must have thought of this previously.

So, from there, verify the contents. You might need to change some paths in the .reg file. Edit the file in notepad:

See above, using H:\\ blah for the install location, I need to change all of these to the new location on my new server.

Please take a backup (of the reg) before you do this if you are reading this…

then double click your modified .reg file, which will ensure that the ESU registry history is back!

Then, when you run the installer, you get a message about a successful install!

Tuesday 12 July 2016

jde julian date conversion in excel

want to turn 1/09/2014 into 114244 – the jde julian date?

=(YEAR(G4)-1900)&TEXT(G4-DATE(YEAR(G4),1,0),"000")

I used this when I had a pile of data I was inserting into a jde table with sqldeveloper import.

Monday 11 July 2016

sqldeveloper install fail

go here http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

download the 64 bit version (that include JDK 8)

expand and run

Unable to launch the

What!!!

goto D:\oracle\sqldeveloper\bin

open sqldeveloper.conf

IncludeConfFile ../../ide/bin/ide.conf

#Set our usage tracking URI
AddVMOption -Dide.update.usage.servers=http://www.oracle.com/webfolder/technetwork/sqldeveloper/usage.xml

#Disable the AddinPolicyUtils
AddVMOption -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true

#Draw performance change
AddVMOption -Dsun.java2d.ddoffscreen=false

#font performance
AddVMOption -Dwindows.shell.font.languages=

AddVMOption -Doracle.ide.startup.features=sqldeveloper

AddJavaLibFile ../lib/oracle.sqldeveloper.homesupport.jar
AddVMOption -Doracle.ide.osgi.boot.api.OJStartupHook=oracle.dbtools.raptor.startup.HomeSupport

#Configure some JDBC settings

AddVMOption -Doracle.jdbc.mapDateToTimestamp=false
AddVMOption -Doracle.jdbc.autoCommitSpecCompliant=false

# The setting below applies to THIN driver ONLY for others set this to false.
# Refer to OracleDriver doc. for more info.
AddVMOption -Doracle.jdbc.useFetchSizeWithLongColumn=true

AddVMOption -Dsun.locale.formatasdefault=true
AddVMOption -Dorg.netbeans.CLIHandler.server=false

IncludeConfFile sqldeveloper-nondebug.conf

setJavaHome D:\oracle\jdk\jre\bin

The above line is wrong – WTF

Change it to be the following 32 bit install: (you need to download and install it)

SetJavaHome C:\Program Files (x86)\Java\jdk1.8.0_77

Set to above (note that this is 32 bit)

C:\Program Files (x86)\Java\jdk1.8.0_77\jre>java -version
java version "1.8.0_77"
Java(TM) SE Runtime Environment (build 1.8.0_77-b03)
Java HotSpot(TM) Client VM (build 25.77-b03, mixed mode, sharing)

What is shipped:

D:\oracleClient\product\11.2.0\client_1\sqldeveloper\jdk\jre\bin>.\java -version

java version "1.8.0_60"
Java(TM) SE Runtime Environment (build 1.8.0_60-b27)
Java HotSpot(TM) 64-Bit Server VM (build 25.60-b23, mixed mode)

So the program is shipped with a JDK that does not work!!!

Ahh, no longer 1.5.5

Wednesday 6 July 2016

To kill a thread or not to kill a thread… tis the WLS question

I was looking into a stuck thread issue at a client. WLS console was reporting everything as “healthy”, but there was a problem. See the screen shots below, indicating that not only was there some excessive CPU usage, there was also some crazy network traffic. Lucky this is not metered (hopefully it was intra VPC).

This was on Linux, so I was able to use top to find the PID that is stuck, generally this is java for WLS

Use ps to find the thread that is spinning it’s wheels, you’ll generally get that from the time on the CPU, see below – there are two troublesome threads.

>ps -eT |grep 1577 |more

1577 2983 ? 00:00:00 java

1577 2984 ? 1-04:12:25 java

1577 2985 ? 00:01:15 java

1577 2986 ? 00:00:50 java

1577 2987 ? 00:07:41 java

1577 2988 ? 1-04:12:50 java

1577 2989 ? 00:01:10 java

Note that the part in yellow is the time on the CPU – there is a problem with those two threads. Can I work out anything else about these threads?

Also can you kill a WLS thread and the JVM survives? To be honest without detailed development knowledge of the program and the type of thread that is spinning, this is not recommended behaviour. Also, I think that you might find it hard to kill (or a command to kill it).

You can also use the following command to see how complicated the thread structure is:

pstree –p 1577

java(1577)-+-{java}(1578)

|-{java}(1579)

|-{java}(1580)

|-{java}(1581)

|-{java}(1582)

|-{java}(1583)

|-{java}(1584)

|-{java}(1585)

|-{java}(1586)

|-{java}(1587)

|-{java}(1588)

|-{java}(1589)

|-{java}(1590)

Now to the WLS console to see if I can find out anymore:

So WLS thinks things are fine, but it also thinks that there are only 27 execute threads.

But, if you do a “Dump Thread stacks”, you get the attached: A 90Kb file with loads of threads in wait and sleep, but two that are not!

I believe that I’ve locked the two internal JVM threads that are each sitting on a single CPU (Hence consuming 50% of the 4 core machine).

Worker Thread - OWVirtual_Thread_Group4 Assigned To com.jdedwards.runtime.virtual.OWVirtual@16e99e46" RUNNABLE

java.lang.Object.hashCode(Native Method)

java.util.Hashtable.hash(Hashtable.java:239)

java.util.Hashtable.get(Hashtable.java:434)

com.jdedwards.jas.BaseServlet.unregisterServletInfo(Unknown Source)

com.jdedwards.jas.ServletProxy.callServerComponent(Unknown Source)

com.jdedwards.jas.ServletProxy.servletProxy(Unknown Source)

com.jdedwards.jas.ServletProxy.dispatch(Unknown Source)

com.jdedwards.jas.net.NetConnection.callJAS(Unknown Source)

com.jdedwards.jas.net.NetManager.processRequest(Unknown Source)

com.jdedwards.jas.net.NetManager.callJAS(Unknown Source)

com.jdedwards.jas.net.NetManager.nextpage(Unknown Source)

com.jdedwards.runtime.model.BusinessView.fetchNextPage(Unknown Source)

com.jdedwards.runtime.vtcomponent.form.VTFetchManager.rtFetchOnePage(Unknown Source)

com.jdedwards.runtime.vtcomponent.form.VTFetchManager.runtimeFetchStartPage(Unknown Source)

com.jdedwards.runtime.vtcomponent.form.VTFetchManager.runtimeFetch(Unknown Source)

com.jdedwards.runtime.engine.form.FindBrowseEngine.fetchDetailData(Unknown Source)

com.jdedwards.runtime.engine.form.FindBrowseEngine.processFind(Unknown Source)

com.jdedwards.runtime.vtcomponent.form.VTForm.processFind(Unknown Source)

com.jdedwards.runtime.engine.form.FindBrowseEngine.initializeForm(Unknown Source)

com.jdedwards.runtime.vtcomponent.form.VTForm.windowDidShow(Unknown Source)

com.jdedwards.runtime.virtual.OWVirtual.showWindow(Unknown Source)

com.jdedwards.runtime.virtual.OWVirtual.showForm(Unknown Source)

com.jdedwards.runtime.er.OWShortcut.launchShortcut(Unknown Source)

com.jdedwards.runtime.virtual.SystemEventProcessor.processVTSystemEvent(Unknown Source)

com.jdedwards.runtime.virtual.OWVirtual.processVTEvent(Unknown Source)

com.jdedwards.runtime.virtual.OWVirtual.processEventLoop(Unknown Source)

com.jdedwards.runtime.virtual.OWVirtual.run(Unknown Source)

com.jdedwards.base.util.ThreadPool$WorkerThread.run(Unknown Source)

AND

" Worker Thread - OWVirtual_Thread_Group0 Assigned To com.jdedwards.runtime.virtual.OWVirtual@4396116a" RUNNABLE

com.jdedwards.jas.BaseServlet.unregisterServletInfo(Unknown Source)