Friday, 18 December 2015

Using OATS and OVR - The TLA edition

OATs is very good for load testing JD Edwards.  I think it's handy to use at all phases of the software lifecycle, but especially for testing a tools release, an upgrade or platform migration.

I've recently been involved in getting OATs working with OVR, this was a bit more of a challenge, as the standard correlation library that comes for JD Edwards does not do any of the OAT's session correlations - therefore, it'll never work "out of the box".

There are a bunch of internal header values used for communicating to OATS that you need to manually keep track of and substitute in your scripts, namely:

  • tid
  • xtoken
  • xdm
  • xdo
  • resourceCacheID
Many of these can be set up in a new correlation library, but some cannot.  It's a little more difficult when the substitutions need to be done on _xdo and also xdo when using correlation libraries.

I'd generally show you a pile of code snippets and screen shots, but I'm struggling with blogging from windows 10 at the moment to blogger, so these are not going to happen.

The process is to search the incoming HTML for something like xdo, and create a variable out of that.  then replace all other instances of xdo in the script, like I said in a perfect world this would work by adding this rule to the correlation library - but the world is not perfect.  The other thing to remember is that this substitution must be done in RAW.

Ensure that your variable rule is something like "&_xdo=(.+?)&_xt=", it needs to be nice and generic to ensure that you are getting the xdo variable.  Note also that if you call a subsequent OV report in the same JDE login session you'll need to refresh this and the xtoken (yes, painful).

Make sure you then prescribe the variable substitution with the following:

Resource cache is done with

I then say replace in all locations.

Wednesday, 16 December 2015

Blogging has been made almost impossible

Wow, this is tough.  I use windows live writer for blogging to blogger.  The reasons are simple, I have a source code formatting plugin which I love, and I like being able to cut and paste pictures easily - not much else to be honest.

I've recently gone to windows 10, and now I cannot blog.

I cannot blog with windows live writer 2011
I cannot blog with windows live writer 2012
I cannot blog with open live writer...

I've enabled the google 2 phase security - I've allowed certain app security - wow - I think that I've done everything possible, but I cannot seem to tear a post out.  I'm about to look for an alternate app, but not too sure how successful I'm going to be with that...  Watch this space.

Thursday, 26 November 2015

oracle olite, old bpel and SOA and the database that would not shrink

wow, this was a beauty.

I've been fighting it for some time now.

I have an old oracle lite database that houses the internals for a BPEL install.  the database is orabpel.odb and exists in D:\product\\OracleAS_1\bpel\system\database\olite.

This was used for an integration project that uses EXCEL to call BSSV WS via BPEL PM.  Pretty cool hey.  BPEL PM might have been over kill, but it was nice and neat!  This has been working for about 5 years, and the client called me and told me it was broken...  Oh dear...

The orabpel database was 4GB!

I cannot do anything with it.

I learned how to connect to the database, this is all done through the magic of polite.ini and
[All Databases]

And odbc.ini (of course)
[ODBC 32 bit Data Sources]
orabpel=Oracle Lite 40 ODBC Driver (32 bit)
WEBTOGO=Oracle Lite 40 ODBC Driver (32 bit)
POLITE=Oracle Lite 40 ODBC Driver (32 bit)
oraesb=Oracle Lite 40 ODBC Driver (32 bit)
orawsm=Oracle Lite 40 ODBC Driver (32 bit)
shannon=Oracle Lite 40 ODBC Driver (Client) (32 bit)
Description=Oracle Lite 40 Data Source
IsolationLevel=Read Committed
Description=Oracle Lite 40 Data Source
IsolationLevel=Read Committed
Description=Oracle Lite 40 Data Source
IsolationLevel=Read Committed
Description=Oracle Lite 40 Data Source
IsolationLevel=Repeatable Read
Description=Oracle Lite 40 Data Source
IsolationLevel=Read Committed
IsolationLevel=Read Committed
CursorType=Forward Only

These two files contain the pointers that BPEL is going to talk to when looking for the internal configuration.

Querying the data was difficult, but finally I used:

Yes, that is the password out of the box, you do not get an option to set it on the standard install.

Cool, we can log in and see that there is 72 tables, 3 are huge, actually about 5.  WORK_ITEM is the worst, but CUBE_SCOPE and CUBE_INSTANCE are terrible.

I cannot drop and of the tables...  I get "out of memory"
[POL-2000] can't allocate more memory

I cannot alter table drop column.
[POL-2000] can't allocate more memory

I cannot truncate.
[POL-2000] can't allocate more memory

I can delete rows, but he commit takes about 1 hour.

I have to create "create table" statements from describe results if I want to create the tables again.

Wow, I'm in a pickle.

I installed another BPEL server with the same software and got a copy of the tables empty - this was very helpful.  Note that when BPEL started, this is when it seemed to create all of the tables - weird.

I then used a series of commands to move data from valid tables (like SUITCASE_BIN) to the empty copy of orabpel.odb.

WFROUTINGSLIP olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFROUTINGSLIP  > d:\temp\WFROUTINGSLIP.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFROUTINGSLIP  < d:\temp\WFROUTINGSLIP.dmp
WFNOTIFICATION olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFNOTIFICATION  > d:\temp\WFNOTIFICATION.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFNOTIFICATION  < d:\temp\WFNOTIFICATION.dmp
WFATTRIBUTELABELUSAGE olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFATTRIBUTELABELUSAGE  > d:\temp\WFATTRIBUTELABELUSAGE.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFATTRIBUTELABELUSAGE  < d:\temp\WFATTRIBUTELABELUSAGE.dmp
WFUSERVACATION olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERVACATION  > d:\temp\WFUSERVACATION.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERVACATION  < d:\temp\WFUSERVACATION.dmp
WFUSERTASKVIEW olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERTASKVIEW  > d:\temp\WFUSERTASKVIEW.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERTASKVIEW  < d:\temp\WFUSERTASKVIEW.dmp
WFUSERTASKVIEWGRANT olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERTASKVIEWGRANT  > d:\temp\WFUSERTASKVIEWGRANT.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERTASKVIEWGRANT  < d:\temp\WFUSERTASKVIEWGRANT.dmp
WFUSERPREFERENCE olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERPREFERENCE  > d:\temp\WFUSERPREFERENCE.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERPREFERENCE  < d:\temp\WFUSERPREFERENCE.dmp
VERSION olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb VERSION  > d:\temp\VERSION.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb VERSION  < d:\temp\VERSION.dmp
WFTASKMETADATA olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFTASKMETADATA  > d:\temp\WFTASKMETADATA.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFTASKMETADATA  < d:\temp\WFTASKMETADATA.dmp
WFTASKDISPLAY olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFTASKDISPLAY  > d:\temp\WFTASKDISPLAY.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFTASKDISPLAY  < d:\temp\WFTASKDISPLAY.dmp
WFTASKTIMER olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFTASKTIMER  > d:\temp\WFTASKTIMER.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFTASKTIMER  < d:\temp\WFTASKTIMER.dmp
WFNOTIFICATIONMESSAGES olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WFNOTIFICATIONMESSAGES  > d:\temp\WFNOTIFICATIONMESSAGES.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFNOTIFICATIONMESSAGES  < d:\temp\WFNOTIFICATIONMESSAGES.dmp
CI_INDEXES olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb CI_INDEXES  > d:\temp\CI_INDEXES.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb CI_INDEXES  < d:\temp\CI_INDEXES.dmp
SCOPE_ACTIVATION olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb SCOPE_ACTIVATION  > d:\temp\SCOPE_ACTIVATION.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb SCOPE_ACTIVATION  < d:\temp\SCOPE_ACTIVATION.dmp
WI_EXCEPTION olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb WI_EXCEPTION  > d:\temp\WI_EXCEPTION.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WI_EXCEPTION  < d:\temp\WI_EXCEPTION.dmp
XML_DOCUMENT olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb XML_DOCUMENT  > d:\temp\XML_DOCUMENT.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb XML_DOCUMENT  < d:\temp\XML_DOCUMENT.dmp
SYNC_TRAIL olload -dump D:\product\\OracleAS_1\bpel\system\database\olite\orabpel.odb SYNC_TRAIL  > d:\temp\SYNC_TRAIL.dmp olload -load D:\product\\OracleAS_1\bpel\system\database\olite\orabpel_works.odb SYNC_TRAIL  < d:\temp\SYNC_TRAIL.dmp

Above are the dump and load commands for each of the tables, I generated these with a excel formula and pasted them into the command window.  this gave me a base copy with my BPEL processes, but not the instance history.

Note that I was unable to delete the history too, as the DB would not process this from the BPEL console.

I tried about 10000 other things before I got this to work.  I had the defragdb.exe fail every time, so this would not get me any space back.  
The following error has occurred:
DBException: [POL-2403] value too large for column

The following error has occurred:
DBException: [POL-3261] there are too many transactions

Note that it was failing for all of the above, which was also painful.  I actually think that changing the odbc.ini to IsolationLevel=Repeatable Read for orabpel fixed the error below:

The following error has occured:
DBException: [POL-3261] there are too many transactions

but it then still failed at the end with:
DBException: [POL-2403] value too large for column

my orabpel database is now 2MB, not 4,191,736MB

AIS based coolness

You are probably aware that we've done quite a bit of bespoke development in the enterprise mobility space.  I've banged on about it enough.  We have a work orders application that does GPS tracking, generic file attachments, powerful search functionality and offline caching...  do I need to say more.  This app would probably cook you breakfast if you set the PO's correctly.

But... I had to demo this app to another client and they were on - fair enough, but the stinko AIS was only released in - we have a problem!

I was not about to install / upgrade their environment just for my demo - they wanted their data on the app - which is also appropriate.  So, I decided to install an AIS server and and a HTML server at and see how it went.  of course I was also going to use the JDBJ bootstrap data source to point to different serialised objects, because I think that different tools serialisation might cause me some problems down the track with the existing PY environment.

A very small amount of hacking had me up and going on the latest tools release (well and using AIS against a enterprise server.  I know you are all going - but but butt...  BUT - it works and it saves me a lot of time.  I've not contaminated the standard environment at all.  I've not had to put on a single ESU - coz all I want is the extra AIS power!

I would recommend this approach to anyone spending time developing in AIS, as the newer tools releases expose a lot more functionality.  Create a little "Island of AIS" [you can use that] so that your developers are not going to need to create depreciated code from the start.

Sunday, 15 November 2015

weblogic and IP address for

All of my blog posts are generally created out of need, this one is another example.  I was helping out a client with a DR test of their JD Edwards and it was all looking great until the automatic start of the JD Edwards weblogic instances did not work.  They went from starting to stopped in server manager.  What!!!  The start procedure has been tested 100 times and is solid as a rock, we use nmstart commands in a startup script.  All nice and neat.

on further analysis of the individual JVM logs, I see the following (amongst the errors)

weblogic.home = D:\oracle\Middleware\Oracle_Home\wlserver\server =

in the servername.out file in domainname\servers\servername\logs dir

It’s trying to use an IP address for the setting.  Tidy darts, I’ll just use windows find to locate where the IP address is specified (surely just an xml file… no, maybe .config, no – maybe .cmd… no… .properties file…  nothing say’s windows find).  Needless to say windows find is the most useless tool that has every been put on the face of this planet.  This is 2012R2 also…

Using the trusty

D:\oracle\Middleware>findstr /S /C:"" *.* | findstr /V /C:".log" |findstr /V /C:".out"

…  I find the following file (of course, not one of the extensions that I’d been expecting…  proprietary binary format)


\_WLS_ADMINSERVER000000.DAT:loadFactorI         thresholdxp?@     ♀ ► ♀t ♦sn

mpt ▬snmp:// ☻t3t ¶t3:// ♣httpst ¶t3://10.38.144

.11:96t ↑cluster-broadcast-securet ¶t3:// ♦ldapt ▬ldap://10.38.1

44.11:96t ◄cluster-broadcastt ¶t3:// ♦httpt ▬

:96t ♣admint ¶t3:// ♦iiopt ▬iiop:// ♣ldapst ¶t3:

// ♥t3st ¶t3:// ♣iiopst ¶t3://

What, this is a persistent filestore under the adminserver which is caching the IP address of the server before DR.  This is painful!  This is the ONLY location it’s specified anywhere.

Hmm, a bit of googling later leaves me quite sure that I cannot do anything with this in a rush, it looks complex.  The one thing that I can do in a rush is to delete the file and restart the machine, see if my scripts work.

So, after taking down the adminServer (it locks the file hard) I backed up the file and removed it. 


My server then started perfectly – but of course, the funny thing?  Look at what it uses now in the persistent store:

weblogic.home = D:\oracle\Middleware\Oracle_Home\wlserver\server =
weblogic.nmservice.RotationEnabled = true

The IP address of the DR machine!! haha.  Monkey see, monkey do.

This must be some sort of bug in the adminserver which is caching the IP address not the hostname of the machine that the server is starting on.  I’m considering raising an SR for this, we’ll see.

Remember, if you are using nmstart and changing IP addresses of your server, you probably want to delete the admin server persistent cache.

32 vs 64 bit app determination and windows which–two bugbears of mine (what’s a bugbear?)


I’ve struggled for this for a while too, in this modern day of transition from 32 to 64 bit, why is not HEAPS easier to tell if a file is 64 bit?  Give me the unix file command any day of the week.  While I’m on a rant, the other thing I dislike about windows is that there is no where command.  If I’m running sqlplus – I want to know which one in the path is being called – not just guess.  This is a common problem on deployment servers where you have e1local that is 64 bit and then you have the 32 bit client component.

Note that one of my prayers have been answered:  which is a command that I use on linux all of the time.  Handy to know what version of sqlplus is being used and therefore what tnsnames to change.  modern windows you can now use where!

C:\Oracle\E1Local\BIN>where sqlplus

It seems that the consensus online us using dumpbin, this is crap.  No everyone has visual studio just lying around for this purpose.  We don’t want to download noddy little tools to do this stuff.

Wait, both rants might be over.  Thanks to this post:





This is really cool, so I can use the tools that are installed on my machine to determine 64 and 32 bit applications by a simple notepad interrogation.

Note that you can also use findstr if you don’t want to involve notepad:

C:\Oracle\E1Local\BIN>findstr /C:"PE" D:\oracle\product\11.2.0\client_1\BIN\sqlplus.exe |more


See the PE then L in the 2nd page of the results.  Remember that the L means 32 bit

Note that after considering the wikipedia definition of bugbear, I might stop using the word.  It does not seem to make a lot of sense the way I use it…  unless you look right to the end of the actual definition In a modern context, the term bugbear may also mean pet peeve

Wednesday, 11 November 2015

JD Edwards and IOT


This is a pretty exciting enhancement, it’s exciting for a number of reasons.  Although to be honest, technically – it’s not that exciting.  It’s the change of thinking that is truly exciting.  Can I write the word exciting anymore, let’s see.

Why do I say that technically it’s not exciting, well most of the cool stuff has already been released in AIS, so the generic ability to call E1 functionality via rest based calls is totally awesome.  This must be the way you choose to integrate anything with JD Edwards moving forward.  It seems to be lighter and easier to develop in than BSSV, error handling is better and more forgiving, especially for a human based interface (mobile, website etc).  Don’t get me wrong, BSSV has it’s place – but it’s no longer that cool.

The technology that is doing the rules based filtering is nice, but fairly rudimentary at this stage, lets wait for some graphical tool to assist us.  Something nice with flows and drag and drop parameters  that’d be nice.  Don’t stop with the flow and the rules either, how about making AIS development graphical – come on – there is a challenge.  I’d like some nice tool to drop 3 XML documents into the associated directories and allow me to call them!  True, I’m getting old.  Although let’s be honest – who calls writing a well formed XML document “cutting code”?

Change of thinking, business opportunity:

So this is where I think that the true value is, I don’t think that you need to look too hard for a use case either – especially if you take inputs from anything and enter them into JD Edwards,

JD Edwards is not going to be a great data warehouse, it’s not going to store 100000’s of rows of asset location data well, of course it will – but it’ll slow everything else down.  You need to design your inputs so that they are meaningful in JD Edwards.  Don’t misinterpret what I’m saying, SAVE ALL DATA – you’ll never know what it’s actually worth.  I’m just saying that saving it into your production JD Edwards database might not not be the most efficient place.  Save the triggering event, raise the work order when the temperature device reads too high….  But don’t save the normal 1 minute trend data for the last year in JD Edwards.  Save this in a data warehouse and show it with CAFE1 if you really need to see it in JD Edwards.  Choose the right place for your data.

Remember, IOT is going to create a lot of data…  More sensors, more data.  keep your data and save your data for a rainy and strategic day for the use case that you cannot visualise today.

I think some fantastic opportunities for this is:

  • weighbridge interfaces
  • quality readings from products – steel / food etc
  • RFID for shipping and identification
  • anything PLC related
  • temperature, moisture (agri business), location, angle, speed, noise,

I think there are fantastic opportunities with CAM and with HS&E, this data could be vital when trying to predict incidents that cause harm and report over them.  Remember you’ll get true data from IOT, no mistakes, always entered on time – accurately.

We’re prototyping some IOT for clients at the moment, if you think that you’ve got a winning use case – we have a winning team that might be able to implement the solution for you at the right price. 

Here are a couple of learning's from my recent adventures in IOT.


The general section of your AIS server needs to point to quite a specific directory – this one:


For me, it’s above.  But I want to give you some context.  This is from downloading the ORCHESTRATION SAMPLES file from MOS


Note the one that is 0.03MB is the one that you want.

Lay this bad boy out on your AIS server.  rename the, or just unzip the par…  Note that you might have to yum install unzip

You need to make sure that you point this AIS configuration about to the right DIR, it as the following folders in it:


Okay, so you have the right DIR.

Note that if like me you get the following error when you try and log into the orchestrator client;  (Authentication error)


You turn on all logging and get the following in the server logs for the AIS server:

09 Nov 2015 12:36:19,224[DEBUG][AIS]AISClientServlet session Exception
http://e1webv3:9221 401 Verifiy WLS Server Basic Authorization Header configuration in domain config.xml, enforce-valid-basic-auth-credentials must be false:
09 Nov 2015 12:41:18,581[DEBUG][AIS]AISClientServlet public
09 Nov 2015 12:41:18,582[DEBUG][AIS]AISClientServlet service method POST
09 Nov 2015 12:41:18,582[DEBUG][AIS]AISClientServlet doPost
09 Nov 2015 12:41:18,583[DEBUG][AIS]AISClientServlet params JDE-JPY920-*ALL-e1webv3:9221-Z1bp4u58flboTpjN-lD1DbZ3t2BH-UH2Li-UnQBYnIzxFhqr2A9P!-488571965
09 Nov 2015 12:41:18,584[DEBUG][AIS]AISClientServlet doPost aisServerhttp://e1webv3:9221
09 Nov 2015 12:41:18,584[DEBUG][AIS]AISClientServlet login path
09 Nov 2015 12:41:18,591[DEBUG][AIS]E1ResUtils getAISVersion buffer
09 Nov 2015 12:41:18,592[DEBUG][AIS]E1ResUtils getAISVersion in
09 Nov 2015 12:41:18,593[DEBUG][AIS]E1ResUtils getAISVersion inputLine EnterpriseOne
09 Nov 2015 12:41:18,619[DEBUG][AIS]Successfully registered the runtime_config Runtime Metric for Using null
09 Nov 2015 12:41:18,619[DEBUG][AIS]Successfully registered runtime_config Mbean
09 Nov 2015 12:41:18,621[DEBUG][AIS]Required Capability List IN: null
You’ll find that this will make your eye’s roll

Note that the fix is to go to your config.xml for the domain that the AIS server is installed in:


And edit the config.xml file – probably make a backup (or not if you are feeling like rebel)


See above, look for the realm entry and paste above that for your domain.  I did it in the wrong place initially and nothing worked.  Restart the AIS server instance from SM and you’ll be able to login.


Cool!  We are in:


Like I said a fairly simple interface for choosing an existing item from the list: (or dir)


Be able to enter the inputs


Add some values and hit go, you’ll see some cools things:

"inputs" : [ {
"name" : "SensorID",
"value" : "123"
}, {
"name" : "Date",
"value" : "101015"
}, {
"name" : "Time",
"value" : "01:35"
}, {
"name" : "VibrationReading",
"value" : "5435"
}, {
"name" : "TemperatureReading",
"value" : "5534"
}, {
"name" : "ShannonsTest",
"value" : "Hello World"
} ]

Nice, json input format, which we can test

And, even nicer, using chrome debug tools, you can get a cURL command

curl "http://e1webv3:9221/jderest/clientservice/orchestrator" -H "Origin: http://e1webv3:9221" -H "Accept-Encoding: gzip, deflate" -H "Accept-Language: en-US,en;q=0.8" -H "User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.80 Safari/537.36" -H "Content-Type: application/x-www-form-urlencoded; charset=UTF-8" -H "Accept: */*" -H "Referer: http://e1webv3:9221/jderest/client/orchestrator.html" -H "Cookie: JSESSIONID=b5D14vor1wgZXx7MrUXPu4xnC_e_rxMQE0fyaAI32wA9U7To9wLj!1811286266; SMCONSOLE_SSID=jYj12SvLtf_HhobsFJGGXn2X-4ZdGG27K2O-3rI6tpxVkhkAb8-p!-1147834967" -H "Connection: keep-alive" --data "orchname=JDE_ORCH_Sample_AddConditionBasedAlert&generic=false&useJson=true&jsonString={"^
" ""inputs"" : [ {"^
" ""name"" : ""SensorID"","^
" ""value"" : ""123"""^
" }, {"^
" ""name"" : ""Date"","^
" ""value"" : ""101015"""^
" }, {"^
" ""name"" : ""Time"","^
" ""value"" : ""01:35"""^
" }, {"^
" ""name"" : ""VibrationReading"","^
" ""value"" : ""5435"""^
" }, {"^
" ""name"" : ""TemperatureReading"","^
" ""value"" : ""5534"""^
" }, {"^
" ""name"" : ""ShannonsTest"","^
" ""value"" : ""Hello World"""^
" } ]"^
"}&name_0=SensorID&value_0=123&name_1=Date&value_1=101015&name_2=Time&value_2=01:35&name_3=VibrationReading&value_3=5435&name_4=TemperatureReading&value_4=5534&name_5=ShannonsTest&value_5=Hello World" --compressed


Of course, this won’t help you – coz the cookie will be dead by the time I post this and this server in not on the internet, but you get the gist of what is being done.

What do you need to do to get this working?

<?xml version="1.0" encoding="UTF-8" ?>
<Orchestration xmlns:xsi="" xsi:noNamespaceSchemaLocation="JDE_Orchestration.xsd">
<inputTypes type="String">
<inputTypes type="Date" dateFormat="Milliseconds">
<inputTypes type="String">
<inputTypes type="Numeric">
<inputTypes type="Numeric">
<orchestrationSteps type="CrossReference">
<orchestrationSteps type="CrossReference">
<orchestrationSteps type="Rule">
<orchestrationSteps type="ServiceRequest">
<orchestrationSteps type="Rule">
<orchestrationSteps type="ServiceRequest">
<orchestrationSteps type="Rule">
<orchestrationSteps type="ServiceRequest">
<orchestrationSteps type="Rule">
<orchestrationSteps type="ServiceRequest">

So we can see from the above document, how the various components are being called (cross reference etc)

P952000 will show you the maps that can be maintained in JDE for cross reference etc:


Note that I get the following error, because I need to load the cross reference data into my F952000 tables

"exception" : "java.lang.Exception",
"timeStamp" : "2015-11-11:20.59.05",
"message" : "Orchestration Aborted: Cross Reference Failure"

Saturday, 24 October 2015

Attending open world

I’m not too sure whether anyone out there (listening to this) is going to oracle open world, but I’m lucky enough to be attending.  Reach out to me if you’d like a demo of google analytics for JDE or perhaps a look at our enterprise mobility solutions.  We could also just talk about how cool 9.2 is until the wee hours of the morning…

I really look forward to see what is out there in the oracle space for the next year.  I’ll be sure to blog anything interesting that I learn.

Tuesday, 20 October 2015

getting OATS to work with OVR in E1–JDE load testing tips

This was not the easiest thing I’ve ever done, but interesting things never are.
I have been doing some load testing, but need to ensure that OVR reports are being load tested at the same time.  OVR, as you know runs on the xmlpserver / xmlpublisher technology from oracle.
Of course, I suspected that out of the box I might have a few issues getting this to work and I was not wrong.
I was getting a lot of errors like this: 0.112 10-20 00:19:23 0.109  Warning Invalid HTTP response code: 500  Internal Server Error , for Url:   



So getting 500’s and 404’s when running, right – we need some fixes.

In general the server maintains cookies or passes header values back and forth to ensure that you have the ability to see it’s resources, xmlp is the same in this respect.  Passing the values that are recorded by OATS are never going to cut the mustard on a replay.  this is where you need to work out what variables that you need to ensure are unique for each session and then create some internal OATS variables for these.

For me this is generally a bit of trial and error, as really there is no way of determining exactly.

Here are some likely candidates for XMLP:

Likely are _xdo and _xtoken, but what you’ll find is _xdo is unique for the report being called, so only xtoken needs to be replaced.

You’ll find this hidden in a jde screen, use a regular expression like this for extraction:

http.solve("xtoken", "_xf=analyze&_xtoken=(.+?)'", "", false,
      Source.Html, 0, EncodeOptions.None)

Then you’ll also need to replace the unique number in the following string”":  and pass this around to all commands that use it.

Note that this is the HTML returned by the command “http://jdebipubprod01.local:9704/xmlpserver/io/zip/allocate-resource-cache” search for allocate-resource-cache


Once both of the above are done, your load tests should start belting the OVR server just like they should.

Friday, 16 October 2015

weblogic cannot start E1 web instance with server manager

This is becoming a little frustrating.

Because weblogic redeploys the application after EVERY restart from the owl_deployment directories under jde_home – if you have any directories locked or explorer open on any of these directories accidently – the application cannot redeploy and will not start.


If you goto http://servername:7001/console and look at the application, you’ll see that it’s running!


You’ll see that it’s running in WLS, but JDE states it’s not.

Remove all of the locks on the runtime dirs, oracle\middleware\user_projects…

Then stop application from wls console

Then start from SM

Thursday, 15 October 2015

Schedule a basic oracle script

This is for me when I need to do this again in a week…

I keep forgetting


c:\windows\syswow64\wscript.exe //e:vbscript d:\myriad\scripts\CheckForCorruptVersionsInProd.vbs

The contents of the script:

The script is totally RAD, it does a query (nice), but also emails results and uses some basic password encryption for prying eyes.

'Username, password and URL settings
'Modify what is required to get this running, username, password (create read only innocuous users)
Const strSmartHost = ""
RecipientNames = ARRAY("", "")
'The password below has been encrypted with a top secret encryption key
'If the JDE password changes, then you'll need to reencrypt the new password
'This can be done by calling EncryptPasswordToFile fCrypt("newPAsswordText","Something99"),"passwordFile"


PasswordString="Password=" & fCrypt(EncryptedPassword, "Something99") & ";"

'EncryptPasswordToFile encrypted,"passwordFile"

Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset
set recset = wscript.createobject("ADODB.Recordset")
Dim cmd
set cmd = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")

Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=e1sys;" & _
"User ID=jde;" & _

Set cmd.ActiveConnection = Oracon
'cmd.CommandText = "Select count(*) from dvfindta.f0101"
cmd.CommandText = "SELECT vrpid, vrvers, vrjd, vrved FROM pd910.f983051 where vrvers not like 'XJDE%' and vrvers not like 'ZJDE%' and vrpodata is null and vrdstnm > ' '"
Set recset = cmd.Execute
'Note that often this is not supported by the DB, so... Need to use a variable
if recset.recordcount = -1 then
end if
emailBody=chr(10) & chr(13) & "*** This Script contains a list of versions that should not have NULL PO's (500 max) ***" & chr(10) & chr(13)
emailBody=emailBody & encrypted & chr(10) & chr(13)
emailBody=emailBody & cmd.CommandText & chr(10) & chr(13)
emailBody = emailBody & "vrpid, vrvers, vrjd, vrved" & chr(10) & chr(13)
while ((i < counter) and (recset.EOF <> true))
for each field in recset.fields
tuple=tuple & """" & field.value & ""","
emailBody=emailBody & chr(10) & chr(13) & tuple
tuple=" "

'wscript.echo emailBody
i=sendMail("WeeklySummary of NULL POs", emailBody,"")

set recset = nothing
set cmd = nothing
set oracon = nothing

function sendMail(subjectText, emailBody, attachment)
on error resume next
Dim iMsg
Dim iConf
Dim Flds
Const cdoSendUsingPort = 2

for each name in RecipientNames
'Create the message object
Set iMsg = CreateObject("CDO.Message")
'Create the configuration object
Set iConf = iMsg.Configuration
'Set the fields of the configuration object to send using SMTP via port 25.
With iConf.Fields
.item("") = cdoSendUsingPort
.item("") = strSmartHost
End With

'Set the message to,from,subject,body properties.
if strlen(attachment) > 1 then
With iMsg
.AddAttachment attachment
.To = name
.From = FromEmailAddress
.Subject = subjectText & " " & now()
.TextBody = chr(1) & " " & emailBody
End With
With iMsg
.To = name
.From = FromEmailAddress
.Subject = subjectText & " " & now()
.TextBody = emailBody
End With
end if
set iMsg = Nothing

end function

Function fCrypt(sPlainText, sPassword)
'This function will encrypt or decrypt a string using the RSA's RC4 algorithm.
Dim aBox(255), aKey(255), sTemp, a, b, c, i, j, k, iCipherBy, sTempswap, iLength, sO
i = 0:j = 0:b = 0
iLength = Len(sPassword)
For a = 0 To 255
aKey(a) = Asc(Mid(sPassword, (a Mod iLength)+1, 1))
aBox(a) = a
For a = 0 To 255
b = (b + aBox(a) + aKey(a)) Mod 256
sTempswap = aBox(a)
aBox(a) = aBox(b)
aBox(b) = sTempswap
For c = 1 To Len(sPlainText)
i = (i + 1) Mod 256
j = (j + aBox(i)) Mod 256
sTemp = aBox(i)
aBox(i) = aBox(j)
aBox(j) = sTemp
k = aBox((aBox(i) + aBox(j)) Mod 256)
iCipherBy = Asc(Mid(sPlainText, c, 1)) Xor k
sO = sO & Chr(iCipherBy)
fCrypt = sO
End Function

Function EncryptPasswordToFile(szPassword, filename)
Set wshShell = CreateObject( "WScript.Shell" )
tmpDirectory = wshShell.ExpandEnvironmentStrings( "%TMP%" )
set wshShell = Nothing
Const ForAppending = 8
logfile=tmpDirectory & "\" & filename & ".txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)


end function

Thursday, 8 October 2015

Cheats way to copy a pathcode, quickly

Quick way to duplicate a pathcode:

Central objects copy, takes about 40 mins.  Honestly, forget import and export and security and indexes and constraints…  Just do this:

select 'TRUNCATE TABLE PP910.' || table_name || ';' from all_Tables where owner = 'PP910' and length(table_name) < 12 and table_name not in ('DATACOUNTS', 'F983051_BAK', 'F98950BK') ;

select 'INSERT INTO PP910.' || table_name || ' SELECT * FROM PD910.' || table_name || '@jde_e1sys ;' from all_Tables where owner = 'PP910' and length(table_name) < 12 and table_name not in ('DATACOUNTS', 'F983051_BAK', 'F98950BK') ;

update pp910.f983051 set vrenhv = 'PP910' ;

Then copy the PD910\include and PD910\source dirs on the dep server to PP910

Fix object librarian

create table jde.f9861srm as select * from ol910.f9861 where sipathcd = 'PD910' ;
delete from ol910.f9861 where sipathcd = 'PP910' ;
update jde.f9861srm set sipathcd = 'PP910' ;
insert into ol910.f9861 select * from jde.f9861srm ;

Full build and deploy.

Improvement on JDE environment audit script

I’ve put a number of iterations of this on my blog, but hopefully they keep getting better.  I’m using the current one to devise a script that keeps a couple of “Systems” in sync.  On a nightly basis I want to replicate a number of data changes from one system to the other.

This uses a left outer join, so that you get all of the JDE tables that are in OL910, but also all of the copies and full package table names – which is really handy.

I use this to identify the tables that have data and then use this for my synchronisation logic.



--I use the below statement to get me a likely list of JDE owners that have JDE tables. This is using the F% criteria, note that it essentially builds the “IN” clause for you.
SELECT '''' || OWNER || ''',', count(1) FROM ALL_TABLES WHERE TABLE_NAME LIKE 'F%' group by owner;

grant all on SY910.DATACOUNTS to public ;

delete from sy910.datacounts ;

insert into SY910.datacounts select table_name,owner,0,0,simd
from all_tables left outer join ol910.f9860 on ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where owner in ('SVMDV01',


select 'update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts
where table_owner in ('SVMDV01',
order by table_name desc ;

--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.SHAE ) WHERE table_name = 'SHAE' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F99410 ) WHERE table_name = 'F99410' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F98TMPL ) WHERE table_name = 'F98TMPL' and table_owner = 'SY910';

Wednesday, 7 October 2015

9.2 release is GA

The next major release of JD Edwards is GA

Oct. 6, 2015: JD Edwards EnterpriseOne Announces Release of Applications and Tools Release 9.2!

Please see the announcement here.

The Oracle Support Document 2062671.1 (JD  Edwards EnterpriseOne 9.2 Certifications) can be found here.

The Oracle Support Document 2054020.1 (Required Components for JD  Edwards EnterpriseOne Tools 9.2) can be found here.

NOTE: Tools Release Maintenance Pack is the first Tools Release for JD Edwards EnterpriseOne Application and Tools Release 9.2.

Cutting through the marketing, go here,P141_SECTION_ID:343,2576

So you get the following snazzy screen:


The tools enhancements are categorised under 4 main headings:

User Defined Objects

User Defined Objects (UDO) are web objects created using the JD Edwards EnterpriseOne web interface. You can create UDOs for your own use, view UDOs others have created, or share your UDOs so others can view and use them. Your ability to perform any of these tasks depends on the permissions you and others have been granted by your administrator.

There are six UDOs that you create from the EnterpriseOne interface:

  • Queries - fields and QBE columns you select from a form and add conditions to in order to make the search criteria more specific.
  • Grid Formats - a selection of the columns you choose to display, the sequence in which you want them displayed, and how information is displayed in your grid.
  • Composite Application Framework - a user interface framework that enables JD Edwards EnterpriseOne applications to integrate with multiple third-party content and applications, as well as other EnterpriseOne applications.
  • One View Reports - a Business Intelligence (BI) Publisher data model, a BI Publisher report, the report definition for the One View Report, which is the metadata for the report and resides in JD Edwards EnterpriseOne.
  • One View Watchlists - a collection of items that match user-defined criteria and contain information to which users have selected to be alerted.
  • EnterpriseOne Pages -the first screen end-users see when they log into EnterpriseOne. They are HTML files and can contain any HTML-enabled functionality, such as interactive process flows, URL links and web pages, company logos, and so on.

My comments: These all exist, but I think that the management has been made more consistent.  See below:

   Object Management Workbench on the Web

Object Management Workbench (OMW) is a system used to manage the development of JD Edwards EnterpriseOne objects on the development client. Available with Tools 9.2 is the new Web OMW which is available on the web client and is used to manage user defined objects (UDO).  UDOs are objects created from the web client.  For a list of these objects, look above

Both OMW systems are defined using the Object Management Configuration application.

This is totally awesome, having a consistent management framework for promoting these objects is great.  Please include things like security in the next release… Please!


Oracle's JD Edwards EnterpriseOne Tools provides security applications, reports, and features to help you protect your company's sensitive application data. EnterpriseOne authentication security ensures that only authenticated users can sign in to EnterpriseOne. Authorization security ensures that EnterpriseOne users have access to only the applications and features that they are authorized to use.

In addition, EnterpriseOne enables you to set up security for developers who use Object Management Workbench (OMW) to add and modify objects for custom applications. Setting up developer security ensures that developers can only perform certain actions in OMW based on pre-defined responsibilities.

EnterpriseOne also includes reports that you can use for security auditing purposes, as well as auditing features for supporting the 21 CFR Part 11 auditing regulations.

With Tools Release 9.2, JD Edwards has removed the 10 character restriction for user ID and password to support more robust security authentication processes. This enhancement enables security officers to define complex password rules that align with their security and compliance requirements. User IDs have also been expanded to improve the end-user experience when signing into JD Edwards.

WOW – this is awesome.  Not so great for myLunch – but great!!!

The following content describes changes to security implemented with Tools Release 9.2.


Security Features

1. User Defined Object Security

Tools 9.1: Not applicable

Tools 9.2: Administrators can enable or disable the user defined object (UDO) features, as well as set up security for viewing, creating, and modifying individual UDOs.

EnterpriseOne provides UDO security features in the Security Workbench to enable administrators to control:

  • Which UDO features are available in EnterpriseOne.
  • Who can create UDOs for their own personal use.
  • Who can request to publish (or share) UDOs with other users.
  • Who can view/use shared UDOs created by other users.
  • Who can modify shared UDOs created by other users.

Action: Enable access to the user defined object security and administration applications, then set up security for user defined object features and user defined objects.

Documentation: JD Edwards EnterpriseOne Security Administration Guide

Managing Security for User Defined Objects

2. Secure FTP (SFTP) for Media Objects

Tools 9.1: Only non-secure FTP is available.

Tools 9.2: Secure FTP (SFTP) for media object access on the HTML Server and Business Services Server is configurable using Server Manager.

Action: Oracle recommends using SSH file transfer protocol, otherwise referred to as Secure FTP (SFTP), for accessing media objects as a more secure alternative to FTP. When EnterpriseOne is configured to use SFTP for media objects, users can securely upload, download, and delete media objects.

Documentation: JD Edwards EnterpriseOne Security Administration Guide (for HTML Server)

JD Edwards EnterpriseOne HTML Server Security

Business Services Server Security

3. Enhanced Database Security

Tools 9.1: All tables are created and shipped with PUBLIC ALL authority.

Tools 9.2: For an installation, all tables delivered by the Platform Pack installer are locked down.

Action: To enable you to maintain the security on the tables, you must set up the security definition using P986117 (signed into JDEPLAN) before running the Installation Workbench.  Any new tables that the Workbenches create (for example the TC Workbench or the Environment Workbench) will be created according to the rules in F986117.

Documentation: JD Edwards EnterpriseOne Security Administration Guide

Managing Data Source Security


Oracle Product Features

Access the Oracle Product Features tool using your Oracle Technology Network login to explore a comprehensive list of features and enhancements introduced with each release of the JD Edwards EnterpriseOne and JD Edwards World products.  View information by release or product area. Use the Compare Releases option to review product changes introduced since your last update.

Tuesday, 6 October 2015

uninstall e1local


okay, I’m having a mare with the e1local database on a fat client.  Things should not take this long.

Sure, I know it’s my fault – hitting shift delete on the c:\E910 dir after stopping the local oracle database.  I know I should not have done it…


Tried using reconfigureMSDEDatabase to get the password – NO

Generating encrypted password...
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...

ERROR:  Could not change the SYSTEM user's password.  The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details.  Will not attempt to change the SYS user's password.
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...

ERROR:  Could not change the SYSTEM user's password.  The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details.  Will not attempt to change the SYS user's password.
Generating encrypted password...
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...

ERROR:  Could not change the SYSTEM user's password.  The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details.  Will not attempt to change the SYS user's password.



  1. Change this file (sqlnet.ora) and set SQLNET.AUTHENTICATION_SERVICES=(NTS).
  2. Restart the OEE E1Local instance.

No – nothing

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release Production on Tue Oct 6 17:11:30 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ORA-01031: insufficient privileges



Changing the path so that e1local was in front of my local client install – NO

tnsping also told me everything was right:

C:\Windows\system32>tnsping e1local

TNS Ping Utility for 64-bit Windows: Version - Production on 06-OCT-2015 17:13:34

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = E1Local)))

OK (10 msec)


Tied of tried to be honest…

Went to delete and install the local engine – look how helpful OUI is!  it’s funny!


Tells me to run a command at the command line.


Which runs

reg.exe DELETE HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_E1Local /f
@echo off

Rem Copyright (c) 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem deinstall.bat - script that calls deinstall_E1Local.bat.
Rem This script calls the deinstall_db.bat script, passing in the
Rem correct parameters.
Rem <other useful comments, qualifications, etc.>
Rem mchaney 04/04/06 - Created Windows version.

set ORACLE_HOME=%~dp0\..

net stop OracleE1LocalTNSListener
net stop OracleMTSRecoveryService
net stop OracleServiceE1LOCAL

%ORACLE_HOME%\deinstall\deinstall_E1Local.bat -silent -paramfile %ORACLE_HOME%\deinstall\response\deinstall_E1Local.rsp


C:\Oracle\E1Local\deinstall>reg.exe DELETE HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_
E1Local /f
The operation completed successfully.
The OracleE1LocalTNSListener service is stopping.
The OracleE1LocalTNSListener service was stopped successfully.

The OracleMTSRecoveryService service is stopping.
The OracleMTSRecoveryService service was stopped successfully.

The OracleServiceE1LOCAL service is stopping...
The OracleServiceE1LOCAL service was stopped successfully.

Checking for required files and bootstrapping ...
Please wait ...
ERROR: Cannot find the file bin\ in the ORACLE_HOME c:\Oracle\E1Lo
cal. You must download the standalone version of the deinstall tool from OTN to
deconfigure/deinstall this Oracle Home.
cannot remove directory for C:\Users\smoir\AppData\Local\Temp\OraDeinstall2015-0
6-10_17-18-54: Permission denied at c:\Oracle\E1Local\deinstall\ lin
e 179
1 file(s) copied.
1 file(s) copied.
The system cannot find the path specified.
1 dir(s) moved.

Will this ever end…  shift delete is coming out again!

Remember that if you are having issues with local oracle, logs are:


Mon Oct 05 12:44:20 2015
c:\Oracle\E1Local\bin\oradim.exe -startup -sid e1local -usrpwd * -log oradim.log -nocheck 0
Mon Oct 05 12:44:28 2015
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\E910_1\PD910\DATA\JDELOCAL_PD910.DBF'

Mon Oct 05 13:14:38 2015
c:\Oracle\E1Local\bin\oradim.exe -startup -sid e1local -usrpwd * -log oradim.log -nocheck 0
Mon Oct 05 13:14:57 2015
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\E910_1\PD910\DATA\JDELOCAL_PD910.DBF'

What tools release can I take for my application release?

I always get confused on what tools release you can take based upon your current application release, so this table is great.  This information changes all of the time, so probably best you go to for the gospel.

JDEdwards OneWorld EnterpriseOne Application Releases
Application Release XE/ERP8 8.10 8.11 8.11 SP1 8.12 9.0 9.1
Tools Release
SP23/24 X X X X X X
  8.93 X X X X X
  8.94 8.94 X X X X
  8.95 8.95 8.95 X X X
  8.9 8.96 8.96 8.96 X X
  8.97 8.97 8.97 8.97 X X
  8.98 8.98 8.98 8.98 8.98 X
  8.98.1 8.98.1 8.98.1 8.98.1 8.98.1 X
  X 8.98.2 8.98.2 8.98.2 8.98.2 X
  X 8.98.3 8.98.3 8.98.3 8.98.3 X
  X 8.98.4
( or earlier)
( or earlier)
( Terminal Release)
( Terminal Release)
  X X X X
  X X X X
  X X X X
  X X X X
  X X X X

Wednesday, 30 September 2015

enter lots of table names into data selection for R98403

Remember to create a vbs script to enter all of your table names into R98403 data selection.  This is an AWESOME time saver:

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
Success = objshell.AppActivate("List Of Values")
'Success = objshell.AppActivate("something.txt - notepad")
wscript.sleep 1000

wscript.sleep 100
wscript.echo "Start data pump"
wscript.sleep 100
Success = objshell.AppActivate("List Of Values")
objshell.sendkeys "+{tab}+{tab}"

objshell.sendkeys "F98840~"
objshell.sendkeys "F98800~"
objshell.sendkeys "F98830~"
objshell.sendkeys "F98810~"
objshell.sendkeys "F594218~"
objshell.sendkeys "F590001~"
objshell.sendkeys "F594213~"
objshell.sendkeys "F590120~"
objshell.sendkeys "F594109~"
objshell.sendkeys "F4096~"
objshell.sendkeys "F0901D~"
objshell.sendkeys "F38111~"
objshell.sendkeys "F594111~"
objshell.sendkeys "F594110~"
objshell.sendkeys "F599312~"
objshell.sendkeys "F62UI13~"
objshell.sendkeys "F56108~"
objshell.sendkeys "F1204~"
objshell.sendkeys "F01131~"
objshell.sendkeys "F56107~"
objshell.sendkeys "F03B16~"
objshell.sendkeys "F3403~"
objshell.sendkeys "F6110~"
objshell.sendkeys "F57150~"
objshell.sendkeys "F470561~"
objshell.sendkeys "F00165~"
objshell.sendkeys "F43199~"

I’ve blogged on this previously, there are small changes that you need to make between tools releases.


Run the script


And in 1 second, you have your many values in data selection!


database link dblink without tnsnames.ora on the server

Wow, another day of learning.  I guess that it all makes sense, but if you create a database link from your client, and you reference a tnsentry that you think is going to exist on the server, then it might not.  Remember database links are going to use the servers tnsnames.ora and sqlnet.ora to try and work out where to go for the data.

create database link "jde_findeva" 
connect to JDE
identified by myp@ss

The link above needs the server tnsnames to know about findeva, however, this could be written with:

connect to JDE
identified by myp@ss

Basically, you’ve done a tnsping on a machine that does know about the database and ripped out everything from (DESCRIPTION onwards.  Removed the spaces and used this to create your database link.  Notice that this is also not a public link, make it private.

Tuesday, 29 September 2015

Mental note–syntax for simple left outer join on oracle

I’m too old school, I use where exists and union, but not enough left outer joins.

select table_name, simd
from all_tables LEFT OUTER JOIN ol910.f9860 ON ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where table_name like 'F98%' and owner = 'PP910';

The example above is great for CNC people (and JDE DBA’s) as it gives you a description of the extremely helpful table names in JDE.  Note that the left outer join helps when there is package build files in the mix – things that are not in object librarian.  Note that this is also fairly timely with another recent post about finding table names or counts – or something.

TABLE_NAME                     SIMD                                                         
------------------------------ ------------------------------------------------------------
F98745 Smart Field Named Mappings
F98750 Forms Design Aid Text Information
F98751 Forms Design Aid Specification Information
F98752 Forms Design Aid/Software Versions Repository Header Info.
F98753 Forms Design Aid/Software Versions Repository Detail Info.
F98760 Report Design Aid Text Information
F98761 Report Design Aid Specification Info
F98762 JDEBLC - Behavior Information
F98950 User Overrides Table
F98950D User Overrides Language Table
F989998 Java Persistent Object Cross Reference Table

This post is really for me to use next time I need to do this.

Monday, 28 September 2015

Work submitted job (WSJ) history for new enterprise server

AS you know WSJ job information is stored in F986110 (not in the system data source) and the next numbers for jobs are controlled by F986111 in the same data source.

When you build a new enterprise server for a client (as a replacement for an old one), generally in the new virtual world – you just build a new machine.  This is an easy way to ensure that production is not affected.  It’s easy to introduce to an existing architecture and then easy to swap out the old.  Quite often your users will want access to their historical UBE’s.

So, you can use the following SQL carefully to do this for you.  Note that this will do the jobs and their execution details.

You’ll need to ensure that the PDF / CSV / log files are copied (physically) to the new server if you want the users to be able to view the output.

TABLE_NAME                     SIMD                                                       
------------------------------ ------------------------------------------------------------
F986110 Job Control Status Master
F986111 Job Number Master File
F986113 Subsystem Job Master
F986114A Audit Repository Detail
F986114 Auditing Information Data Repository

The tables and descriptions are above, this is hand SQL for generating this information.  The only thing that is handy is the ltrim and rtrim to get a match between oracle catalogs and object librarian.

select table_name, simd
from all_tables, ol910.f9860
where ltrim(rtrim(upper(table_name))) = ltrim(rtrim(upper(siobnm)))
and owner = 'SVMAP01';

Change the execution details with the following.  Note that I’m moving from AUBDC-JAP01P and AUBDC-JAP02P to AUPDC-JAP01P and AUPDC-JAP01P.

select * from SVMAP01.F986114;
update svmap01.f986114 set JCEXEHOST = 'AUPDC00-JAP01P' where JCEXEHOST = 'AUBDC00-JAP01P' ;
update svmap02.f986114 set JCEXEHOST = 'AUPDC00-JAP02P' where JCEXEHOST = 'AUBDC00-JAP02P' ;

select * from SVMAP01.F986114A;
update svmap01.f986114A set JDEXEHOST = 'AUPDC00-JAP01P' where JDEXEHOST = 'AUBDC00-JAP01P' ;
update svmap02.f986114A set JDEXEHOST = 'AUPDC00-JAP02P' where JDEXEHOST = 'AUBDC00-JAP02P' ;

select * from SVMAP01.F986113;
update svmap01.f986113 set SSEXEHOST = 'AUPDC00-JAP01P' where SSEXEHOST = 'AUBDC00-JAP01P' ;
update svmap02.f986113 set SSEXEHOST = 'AUPDC00-JAP02P' where SSEXEHOST = 'AUBDC00-JAP02P' ;

Now the actual WSJ information

update svmap01.f986111 set jcjobnbr = (select jcjobnbr + 1 from svmap01.f986111 where jcexehost = 'AUBDC00-JAP01P') where jcexehost = 'AUPDC00-JAP01P';

update svmap02.f986111 set jcjobnbr = (select jcjobnbr + 1 from svmap02.f986111 where jcexehost = 'AUBDC00-JAP02P') where jcexehost = 'AUPDC00-JAP02P';

select count(1), jcexehost from svmap01.f986110 group by jcexehost ;
update svmap01.f986110 set JCEXEHOST = 'AUPDC00-JAP01P' where JCEXEHOST = 'AUBDC00-JAP01P' ;

select count(1), jcexehost from svmap02.f986110 group by jcexehost ;
update svmap02.f986110 set JCEXEHOST = 'AUPDC00-JAP02P' where JCEXEHOST = 'AUBDC00-JAP02P' ;

Now you can goto WSJ and see all of the history.  New jobs will have a continued number too.