About Me

My photo
I work for Fusion5 Australia . Connect with me on linked in here . I'm raising money for a good cause at the moment, follow donate to leukemia foundation

Friday, 29 August 2014

Enhancements to Performance benchmark suite

I’ve been working on performance benchmark lately based upon clients feedback, so things are looking good.

image

It’s now more likely to work with older releases (8.12 etc), as I commoditised some of the DD items that I’m using and don’t used more advanced BSFNs.  If you are on an older release, try and run P55PERFB instead of P55PERFT.  Releasing code for multiple releases is painful, I guess I could do it a little better with “C” and not need a couple of versions, watch this space.

image

We’re close to having the first 400 site running too.  Please, if you are running big unix – it’s be great to have some results from you!  I want to see how the RISC procs chew through the BSFN workload.

The data is coming in and it’s very exciting (in a nerdy way).  We have clients with problems in certain tiers.  Once client had the fastest results in many categories, but the slowest Web by far.  We’re able to tell them to look into the web server and config (or we can) to assist them get this quicker! 

The suite is also providing better reporting back to me, as the old XML document was proving too hard to interpret.

image

Above is a snippet of sample data, what you can see is for the 6 different sites above, we’ve got the average duration and standard deviation for the 3 (of 41) tests as defined by the suite.  We are able to tell who is average and who is above average.  Soon we’ll be able to tell you what technology gives the fastest JD Edwards results.

So, if you want to know how fast your JD Edwards is, please download and run the application:

http://myriad-it.com/download-your-performance-testing-software-now/

You can download the latest version of the software from the link above.

Tuesday, 26 August 2014

JD Edwards, media objects analysis and more

Forgotten media objects is something that I’ve been involved in a number of times.  Leaving them on old deployment servers, not copying the directory for an upgrade – the list goes on.  Poor old media objects are often lost.

Quite often they are pretty important too, like an original PO that you need for payment – etc etc.  Quality results for some materials that have gone wrong.

I was recently asked to audit some media objects at a client and I thought I’d articulate my method and findings (generically and anonymously).

First and foremost, this was part of an upgrade, so feel free to get rid of MO’s that you are not going to need.  This leaves you with TEXT, IMAGE, OLE, LINK & SHORTCUT.

Say goodbye to shortcut items, they generally reference something on a persons desktop – never great.

OLE will point to where the OLE queue is defined in the F98MOQUE – that one is fairly reliable – unless you’ve moved machines or forgotten to change this link.

Then you have TEXT, very easy – it’s all in the BLOB!

IMAGE is kinda like OLE, it uses F98MOQUE to find the dir where they are stored Stored POs

Finally Link is a generic link, not using F98MOQUE, these can be anywhere!  (\\machine\location\text\HQ\document.pdf )

So, how can you determine what is still about and what is gone?  I have a vbscript (i’ve seen some powershell stuff online too).


[sourcecode language='vb' padlinenumbers='false' light='false' firstline='1' highlight='1']

fileSuffix=Left(FormatDateTime(dateStamp, 1),6) & day(dateStamp) & monthName(month(dateStamp)) & hour(dateStamp)
Const ForAppending = 8
logfile="d:\myriad\" & fileSuffix & ".txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)
DIM fso
Set fso = CreateObject("Scripting.FileSystemObject")

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

Oracon.ConnectionString = "DSN=e1prod;" & _
"User ID=jde;" & _
"Password=jde;"

Oracon.Open
Set cmd.ActiveConnection = Oracon
Set cmdInsert.ActiveConnection = Oracon
cmd.CommandText = "Select gdobnm, gdtxky, gdgtfilenm, gdgtmotype, gdgtitnm, gdqunam from proddta.F00165_91 where gdgtmotype in ('2', '5','1')"
'cmd.CommandText = "Select gdobnm, gdtxky, gdgtfilenm, gdgtmotype, gdgtitnm, gdqunam from proddta.F00165_91 where gdgtmotype in ('1')"
Set recset = cmd.Execute

'load the folder map array
Dim moqueue
Set moqueue = CreateObject("scripting.dictionary")
Set cmdf98moque.ActiveConnection = Oracon
cmdf98moque.CommandText = "Select omqunam, omqupath from sys7333.F98MOQUE"
set f98moquerecset=cmdf98moque.execute
while (f98moquerecset.EOF=false)
moqueue(trim(f98moquerecset("OMQUNAM")))=trim(f98moquerecset("OMQUPATH"))
f98moquerecset.movenext
wend
wscript.echo moqueue("AA Iplex Mud Maps")
i=0
while (recset.EOF=false)
'Generally this would be in a function, but it needs to be fast!!
'NOte that type 2 and 1 cld be same function, as they are based on queues
'but i'm doing an upgrade, and it's complicated... this hard code of DIR
'does not really need to be there
if recset("GDGTMOTYPE") = "2" then
filenametotest = "D:\MEDIAOBJ\Oleque\" & trim(recset("GDGTFILENM"))
elseif recset("GDGTMOTYPE") = "1" then
filenametotest = moqueue(trim(recset("GDQUNAM"))) & "\" & trim(recset("GDGTFILENM"))
'wscript.echo "queue (" & trim(recset("GDQUNAM")) & ") path (" & moqueue(trim(recset("GDQUNAM"))) & ")"
else
filenametotest = trim(recset("GDGTFILENM"))
end if
If (fso.FileExists(filenametotest)) Then
'WScript.Echo("File exists! TP(" & recset("GDGTMOTYPE") &"): " & filenametotest)
fileexistsstatus="YES"
Else
fileexistsstatus="NO"
'WScript.Echo("File does not exist! TP(" & recset("GDGTMOTYPE") &"): " & filenametotest)
End If
cmdInsert.CommandTExt = "INSERT INTO PRODDTA.f00165SRM (GDOBNM, GDTXKY, FILEEXISTS) values ('" & _
recset("GDOBNM") & "', '" & _
recset("GDTXKY") & "', '" & _
fileexistsstatus & "')"
'wscript.echo "SQL:" & cmdInsert.CommandTExt
Set insertrecset = cmdInsert.Execute

recset.MoveNext
'wscript.echo recset("gdgtfilenm")
'tuple=" "
wend


set recset = nothing
oracon.close
set oracon = nothing
[/sourcecode]


Remember to create F00165SRM



[sourcecode language='sql' ]
create table proddta.f00165srm as select GDOBNM, GDTXKY, ' ' AS FILEEXISTS from proddta.f00165 where 1=0 ;
[/sourcecode]


This will then tell you which MO’s exist and which don’t.  It loads table F00165SRM with the YES or NO whether the physical file exists.  This will let you know how much trouble you are in if you think you are missing objects.


Note that this script demonstrates some cool things:



  • reading and writing to oracle database from vbscript

  • using a scripting.dictionary object to reference an array using strings as the index, wow – that was cool

  • other random stuff about ADODB, connect strings and other things.

ERP Analytics–see your ERP usage information with google analytics

This is a screencam I’ve done on our ERP analytics suite.  You’ll be able to know exactly what your JDE users are doing any hour of the day, any day of the week.  Very simple and cost effective to get up and running at your site!

Thursday, 21 August 2014

grant select on v$session, actually v_$session

 

grant select on v$session to PRODDTA ;

When you are enabling auditing for JDE, the data owner needs direct access to v$session.  Now, you get an ORA-02030: can only select form fixed tables/views

So then you change it to:

grant select on v_$session to PRODDTA ;

and it works

Wednesday, 20 August 2014

Can JD Edwards upgrades get better? Perhaps 9.2 will provide some project savings with this.

I delivered a presentation at the Australian user group the other day (infocus) on a future plan to make JD Edwards upgrades easier…  Or was it more simple…  Or was it better..  Perhaps all three.

When CNC people get together at the pub, they don’t talk about their families, wife's or girlfriends…  They talk about package builds, JDBj timeouts and enterprise servers.  They trash talk AS/400’s until the bloke that loves them starts crying and goes back to his shandy…  I do however recall one night talking about upgrades and why they are no shipped as ASUs.  Honest…

I thought it would be simple enough to ship the update as one massive ESU, or ASU and then clients could apply that nice and easy.  We no longer have an exact correlation between tools release and application release, so why not just ship us the ASU and not force us into the DD merges, spec merges, control table merges etc – ESU’s are much easier [I know they still merge, but they do not create ol920, dd920 etc).  Or so the psyche tells us.

So it seems that what might be in release with 9.2 is a better way to upgrade. 

Everything will still work the same in terms of all of the steps (Doh!), but instead of using the 9.2 central objects as a base, they’ll use your central objects as the base.  Okay, so lets think about this…  How are they going to know what you’ve modified and hence what to replace…  Well, this is where it starts to get funky, they are going to run a manifest of code changes between 9.1 and 9.2, a total list of changed objects.  Okay, following me?  So then, they are going to determine what of THESE objects your might have had your sticky fingers in and blast some pristine 9.2 code over the top.

Of course this will not take into consideration the ESU’s that you’ve applied, but at the end of the day – this will just mean your mods are closer to 9.2, and that the retrofit will be easier… What’s the less, try and stay code current!

Are you still with me?  So this means that instead of blasting over the top of EVERY object that you’ve modified, they are only going to change the ones that have changed between 9.1 and 9.2 [as per the manifest]. This is going to mean less retrofit and this is also going to mean less testing and perhaps less training – because things are not going to change as much. 

Wow, that is pretty cool.  I’ve never met a CNC person that has ever been concerned about the speed of the R98700 either, and the funny thing about this UBE, is that it generally only gets run once, the first time.  You might run it again if you did not have your modifications identified properly when you ran it the first time (not speaking from experience at all there)…  So perhaps not quicker upgrade, but quicker upgrade project.

The oracle laboratory tells that this might mean some crazy savings for some clients (95%) and reasonable for others (45%).  Save 45% on retrofit, yes please.

This coupled with all of the other nice things that are out there is going to make for a much better upgrade.  We must remember that if this makes it to GA, we need to entice our clients with leaner estimates on retrofit and smaller numbers of objects to be affected.  So, all we need is the manifest and then we’ll be able to do some sort of impact analysis on this to work out what we want to retrofit.  We can then demonstrate downstream savings too, with less change there is less testing, less rework etc.

Please note that this is not GA and I’ll not be held responsible for you entering into contracts on the basis of the information supplied in my blog… haha, my safe harbour statement.

image

A little picture of the planned change

Friday, 15 August 2014

OEL RHEL & WebLogic and java SLOW downs

This has been a very frustrating problem.  I’ve been working on a new client with some significant performance problems.  The site is specified fairly well, it’s got its share of teething issues, but it just NOT performing.  Tests are timing out all over the place.  We load 100 users up and the results graphs are like a saw tooth, 90 seconds, 30 seconds, 90 seconds – unbelievable!

Do you have JD Edwards going slow?  Having slow downs…  CPU not moving, nothing moving – just users getting angry?  Are you linux based web servers?  read on!

Linux based WLS servers (RHEL) and linux enterprise server with windows batch.

Then I find the golden nugget from https://support.oracle.com, it’s like I’ve found “welcome stranger”. 

Search for 1525645.1 on oracle, E1: OS: Linux Servers Hang or Have Delays on Any JAVA Process Affecting Performance (Doc ID 1525645.1)

At the end of the day, you either need to download a patch or fix java.security, for all editions of java on the machine.

Option 1:

securerandom.source=file:/dev/./urandom

Change the line above to equal the above.

Option 2:

To enable this service on Oracle Linux 6:

Install the rngd service with:

sudo yum install rng-tools

(The service may already be installed but not running)

Modify /etc/sysconfig/rngd file (as root), to use this configuration:

EXTRAOPTIONS="-i -o /dev/random -r /dev/urandom -t 10 -W 2048"

(The default value is an empty string: EXTRAOPTIONS="" )

Make the service start at boot with:

sudo chkconfig rngd on


(Re)start the service to take the configuration changes:

sudo service rngd restart
 

Once this fix was applied, WLS would start in 10 seconds, not 5 minutes and the JD web performance was 90% good (there was still a blip every 25 minutes), but I think fixing /usr/bin/java will sort this out.

Thursday, 14 August 2014

Sizing disk for JD Edwards servers

My formulas are simple…

Deployment, OS + 250GB for JDE & apps, packages…

Web Server OS + 50GB for JAS – tmp PDFs are purged often.

Enterprise Server OS + 200 GB for a shed load of PDFs and log files.

Database…  Umm, I won’t put that in writing.

But when I say OS, I also include the swapfile!

My client has 64GB of memory for a number of their servers, that eats into my disk space a lot with a 65GB swap file.  I’m unsure if you still need a swap file of 65GB when you have 64GB of RAM?  Remember to factor in swap when you are building big servers.

clip_image002

So that’s where all my disk space went!

Tuesday, 12 August 2014

Performance Benchmark ESU–Free for the first 20 callers!

How can you know how fast your system is without a benchmark?

 

Introduction:

Myriad continues to innovate with it’s latest software offering, a performance benchmarking utility. With our performance benchmark suite, you can evaluate how fast your ERP is running at any time of the day – any day of the week. That’s not all, we can compare your results with industry standards and let you know where you hit the mark – and more importantly where you do not!

clip_image002

 

Details:

We’ve developed a platform independent suite of applications that run within JD Edwards. You can take these as an ESU or a par file (for non E910 clients) and deploy them to any environment that you’d like to performance test. The suite is made up of reports, business functions, tables and database views.

Once you have installed the code and generated the table, the software can be operated with a single button press! The code will go and calculate performance based upon a fixed set of parameters and logical operations.

The code is able to test your web server performance, BSFN performance, ER performance and finally UBE performance – to the micro-second. This type of accuracy allows you to easily determine if any changes are having a real impact on your system. It also allows you to quantify performance at certain times of the day – when you are busy and when you are not to accurately predict what this means to your end user.

 

Simplicity:

Once installed, basic operations are complete with a single click.

clip_image004

You are then shown graphically how long the various test executions took. You’re able to then make comparisons between days, weeks or months. You’ll be able to determine where your bottlenecks might be with the accurate timings that are coming from the tests. Of source, the more you use this – the more you’ll understand about your site.

 

How fast are we compared to you?

There’s no such thing as a free lunch, or is there? We’ve loaded the ESU with some “phone home” functionality. This allows us to collate performance data from various sites and assist you in your performance metric analysis. The software sends an email to an external address with the results of each test, this allows us to track the application usage and also make changes based upon this data. We are actually adapting and changing our software based upon usage.

clip_image006

 

What’s next?

Our development roadmap has a lot of enhancements planned, you’re going to benefit from being “patch current”.

· We are going to allow you to schedule the tests hourly or daily and then get comparisons.

· We are going to implement exception based reporting, so if the system is too slow – we’ll let you know!

· We’ll prepare recommendation packs of changes that clients have made and the improvements that they got.

· We’ll provide detailed offsite analysis for those who need it

 

What’s the catch?

Nothing, we are passionate about great performing ERP and we are here to help. We ask that the code remains unmodified, you a given an unlimited use license to the code, but we expect that it does not get reverse engineered or modified. If you think of a good enhancement, let us know and we’ll get it into the product.

The product in in beta release at the moment and it’s completely free. Things might change later in the year, but at the moment – first in – best dressed.

clip_image008

It’s not enough

So you want more, well Myriad IT Services specialize in performance and tuning, in fact – that is all some of us do! We offer a number of additional services to augment what has been outlined here:

Offering 1: Performance Benchmark

As outlined here, very granular and exact method of benchmarking site performance.

Offering 2: OATS

Oracle application testing suite. We own OATs and have the ability to script your business critical processes and play them back in the saturation that you require. We can simulate load from different geographical locations, we can simulate batch load and interactive load.

Offering 3: RUI

If you still want more, then we can talk about Oracle’s RUI. Oracle Real User Experience Insight enables enterprises to maximize the value of their business-critical applications by delivering insight into real end-user experiences.

You want it?  email me

Monday, 11 August 2014

JD Edwards default oracle security for PUBLIC

ALL for public, say that it can’t be true!  Quite often it is…  A change is needed, here is a single statement that will do 90% of your job for you:

select 'GRANT SELECT,UPDATE,INSERT,DELETE on ' || owner || '.' || table_name || ' TO JDE_ROLE;' ||
'GRANT SELECT ON '  || owner || '.' || table_name || ' TO JDE_RO;' ||
'REVOKE ALL ON '  || owner || '.' || table_name || ' FROM PUBLIC;'
from all_tables where owner in ('FIADTA','FIACTL') ;

This generates all of the below… 5000ish of them.   You need to run them.

GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F993023 TO JDE_ROLE;GRANT SELECT ON FIADTA.F993023 TO JDE_RO;REVOKE ALL ON FIADTA.F993023 FROM PUBLIC;                                                                                                                                    
GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F99303 TO JDE_ROLE;GRANT SELECT ON FIADTA.F99303 TO JDE_RO;REVOKE ALL ON FIADTA.F99303 FROM PUBLIC;                                                                                                                                   
GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F32943 TO JDE_ROLE;GRANT SELECT ON FIADTA.F32943 TO JDE_RO;REVOKE ALL ON FIADTA.F32943 FROM PUBLIC;                                                                                                                                 
GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F4009T TO JDE_ROLE;GRANT SELECT ON FIADTA.F4009T TO JDE_RO;REVOKE ALL ON FIADTA.F4009T FROM PUBLIC;
   

Then run the results, of course, you’ll need to ensure the following first:

  • JDE_RO is a read only role that I give auditors
  • JDE_ROLE is the role given to all oracle accounts that connect to the back end (usually only 1, like e1user so that connection pooling is efficient)
  • you need to assign JDE_ROLE to the users that connect to the backend – who are they – run the following.  It’ll tell you the oracle usernames, and the # of times they are being used:

select count(1), scsecusr from sy910.f98owsec group by scsecusr ;

COUNT(1)               SCSECUSR  
---------------------- ----------
1                      JDE       
1                      jdero   

Thursday, 7 August 2014

Web version | Full Versions | versions created on Web bulk management

 

They have been around for quite some time, but it's nice to have a refresher on these types of things.

8.12 and 9.0 have have started the functionality of these full versions, thank goodness - nobody enjoyed having to generate versions to convert them the fat versions and then manage the SDLC, although I believe that there is still something missing here.

Essentially when you create or change a "full version", the following places also get the changes:

  1. Central Objects
  2. F983051 Version List Master Entry
  3. Serialized Object Tables
  4. Server Package Specs.

So, I need more detail!

1. Central objects

clip_image002

So It probably only updates the RDASPEC for data selection and sequencing for the UBE (as version overrides are still FAT client only).  Now that is cool, is that a clayton’s checkin?  The check in that is not a check in… Or my other nerdy analogy, is this like vocab overrides (what do you mean your updating central objects without me hitting check in!!!!).

 

2. Easy, F983051

– processing options.

 

3. F989999 and F989998

This is great, this means that everyone can see the version and change it and run it straight away for that pathcode.

 

3.  Server Package Specs

So, the package that is deployed on the server is essentially a copy of central objects “at a point in time”.  So, this does the same as step 1, but alters the F98761DVBF40424 (or equivalent) for the currently deployed package.

So that all makes sense, what about OMW?  What about security for other people to change it, what about your SDLC?

· The web engine automatically does a submit specs, that is nice.  But what about the scheduler?  The specs will be read from the deployed full package, so that should be fine also

· SDLC is hit and miss, you need to manage them like BV’s.  So if people have been creating a bunch of them in PD, you might need to create a project and bring them into the SDLC, something like this will help you a lot (see insert statement down the page): 

· Note that an “install specs” is different to what it used to be, now an install specs updates the specs in the currently deployed package, which circumvents the need to build a package for a new version.  The scheduler will pick up these too.  That is nice.

clip_image004

Just created this, PY web.

clip_image006

Nothing goes to my default project either – which is interesting

 

How Can I identify these easily?

Maybe you can’t!  Sure you can write something like this, pretty much saying give me the version in PY, that are not in DV or UA and are also not in a project (and are not standard), this is probably the full list of versions that are web only in the PY environment.

select * from py910.f983051 t1

where not exists

(select 1 from sy910.f98222@e1sys

where POOMWOBJID = rtrim(ltrim(vrpid))||'|'||rtrim(ltrim(vrvers)))

and t1.vrvers not like 'ZJDE%' and t1.vrvers not like 'XJDE%'

and not exists (select 1

from ua910.f983051 t2

where t2.vrvers = t1.vrvers

and t2.vrpid = t1.vrpid)

and not exists (select 1

from dv910.f983051 t3

where t3.vrvers = t1.vrvers

and t3.vrpid = t1.vrpid);

Wow, that is 500+ for me.   This project is going to save me a lot of time.

Create a project called 'FullVersionsForSDLC', move it up to 26 and then bang out this SQL statement.

clip_image002[4]

…and…

INSERT INTO SY910.F98222@e1sys (POOMWPRJID, POOMWOBJID, POOMWOT, POSRCRLS, POOMWUSER, POOMWMKEY, POENHV, POPATHCD, PODATS, POOMWCHS, POOMWOST, POOMWOVS, POOMWOBSDT, POOMWCRTDT, POOMWAC, POOMWTC, POOMWTCV, POOMWPOS1, POOMWPOS2, POOMWPOS3, POOMWPOS4, POOMWPOS5, POOMWPOD1, POOMWPOD2, POOMWPOD3, POOMWPOD4, POOMWPOD5, POOMWPON1, POOMWPON2, POOMWPON3, POOMWPON4, POOMWPON5, POPID, POMKEY, POUSER, POUPMJ, POUPMT, POTIMEZONES, PODSAVNAME)

SELECT 'FullVersionsForSDLC', rtrim(ltrim(vrpid))|| '|' ||rtrim(ltrim(vrvers)) , 'UBEVER' ,'E910','JDE','VSYDJDE02','PY910','PY910',' ','0','01',' ',0,0,' ',' ',' ','0',' ',' ',' ',' ',0,0,0,0,0,0.000000,0.000000,0.000000,0.000000,0.000000,'P98220','DSBSLSMOIRS','MOIRS',104303,143243.000000,' ',' '

from py910.f983051 t1

where vrpid like 'R%'

and not exists

(select 1 from sy910.f98222@e1sys

where POOMWOBJID = rtrim(ltrim(vrpid))||'|'||rtrim(ltrim(vrvers)))

and t1.vrvers not like 'ZJDE%' and t1.vrvers not like 'XJDE%'

and not exists (select 1

from ua910.f983051 t2

where t2.vrvers = t1.vrvers

and t2.vrpid = t1.vrpid)

and not exists (select 1

from dv910.f983051 t3

where t3.vrvers = t1.vrvers

and t3.vrpid = t1.vrpid);

570 rows inserted.

Commit;

What!! I just populated a project with the 500+ versions that have been created on the web!!! What, you can now promote this and synchronise all of your pathcodes… Today is a good day.

clip_image004[4]

Great…  check in, check out and move up or down!

Tuesday, 5 August 2014

JDBC test harness–oracle based

This is another simple program, very similar to my AS/400 based JDBC text harness – this is using oracle drivers and oracle connections.

It’s also handy when OATs will not connect to your RAC based database and you are getting angry because you’ve tried about 100 variations of the stupid RAC based JDBC connect string.  And you know you’ve had it working before.  Conspiracy theories start swimming in your head “database firewall, must be some sort of firewall”…  But before you flush your credibility down the drain (and get your picture on the network guys community dart board), try and test it yourself!

BTW, JDBC RAC oracle connection string looks like this, 2 node RAC.

Jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=vserv-rh-db32.shannon.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=vserv-rh-db31.shannon.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ERP91DEVSRV.shannon.com)))

first, create two files

dbconnect.java

import java.io.*;
import java.util.*;
import java.sql.*;
   
public class dbconnect
{
    public static void main(String[] args) {
    try {
    Properties props = new Properties();
    props.load(new FileInputStream("dbconnect.properties"));
    String DRIVER = "oracle.jdbc.driver.OracleDriver";
    String URL = "jdbc:oracle:thin:@" + props.getProperty("local_system").trim() + ":1521:" + props.getProperty("serviceName").trim() + "";
    //Connect to iSeries
    Class.forName(DRIVER);
    Connection conn = DriverManager.getConnection(URL, props.getProperty("userId").trim(), props.getProperty("password").trim());
    System.out.println("Creating statement...");
        Statement stmt = conn.createStatement();
    String sql = "SELECT COUNT(1) as RecCount FROM " + props.getProperty("owner").trim() + ".F0101";
        ResultSet rs = stmt.executeQuery(sql);
        //STEP 5: Extract data from result set
        int RecordCount=0;
    while(rs.next())
        {
          //Retrieve by column name
          RecordCount  = rs.getInt("RecCount");
    }
         System.out.print("\nF0101 count: " + RecordCount + "\n");

       rs.close();
       conn.close();
}
catch (Exception e) {
System.out.println(e);
}    
System.out.println("Program worked if there are no errors above!"); // Display the string.
}
}

second (note that there is a slight name change, previous was connect.properties, I did not like that).

dbconnect.properties

#OracleHostName
local_system=e1ent2.mits.local
#I-series UserId, used for login and library list
userId=JDE
#I-series Password
password=S0mP@s5W
#Schema
owner=TESTDTA
#serviceName
serviceName=orcl

Compile and run

you need a jdk to compile, www.oracle.com find java

then does not matter, 64bit or 32 bit…javac and java

make sure that you have the oracle jdbc drivers, ojdbc6.jar or ojdbc5.jar (\\e1dev2\c$\oracle\product\11.2.0\client_1\jdbc\lib) in a dir that is similar to the above you’ll find them (like yoda I type).

compile like last time

C:\Java\jdk1.7.0_67\bin\javac dbconnect.java

or

C:\Java32\jdk1.7.0_67\bin\javac dbconnect.java

Run:

C:\Java\jre7\bin\java -cp .\ojdbc6.jar;. dbconnect

C:\Users\shannonm>C:\Java\jre7\bin\java -cp .\ojdbc6.jar;. dbconnect
Creating statement...

F0101 count: 1130
Program worked if there are no errors above!

Saturday, 2 August 2014

JD Edwards–graphs on forms–easy!

[with respect] JD Edwards forms are traditionally boring…  But you can easily add some sizzle to your forms with graphs!

It’s very easy to add a graph control to a form and then feed it with dynamic data.  Sure – it’s no OVR (One View Reporting), but it’s pretty snazzy and easy to show simple dashboard information graphically.

Note that you can use our demo server for this, but you’ll need to register for a username and password first. http://e91demo1.myriad-it.com/jde/E1Menu.maf

Then use the following to see sample graphs and XML.

http://e91demo1.myriad-it.com/jde/GraphPrototype.maf

I believe that you can use the following to see how simple it is to create. 

image

So this form is really handy for you to being your graphing work.  This should you sample XML and sample graphs for the XML (or graphs and their XML, depending on how your brain works).  So – what do you need to do?  Quite simply put a graph control on your form and feed it the XML.

You can easily write a BSFN or a NER that will generate the XML for your graph and put it on the form. – The XML is easy to read – as seen below

<?xml version="1.0" encoding="utf-8"?>
<!-- This text is only visible when the system is on an incorrect Tools release. -->
<!-- Please contact your system administrator to upgrade to the most recent XYZ service pack. -->
<Graph graphName="bar_basic">
<O1Title text="Week Ending" visible="true"/>
<Y1Title text="Cost Variance (USD)" visible="true"/>
  <LocalRelationalData>
    <Row columnKey="9/1/05" rowKey="Actual Variance" dataValue="1504" />
    <Row columnKey="9/8/05" rowKey="Actual Variance" dataValue="980" />
    <Row columnKey="9/15/05" rowKey="Actual Variance" dataValue="-675" />
    <Row columnKey="9/22/05" rowKey="Actual Variance" dataValue="784" />
    <Row columnKey="9/29/05" rowKey="Actual Variance" dataValue="0" />

    <Row columnKey="9/1/06" rowKey="Actual Variance" dataValue="1504" />
    <Row columnKey="9/8/06" rowKey="Actual Variance" dataValue="980" />
    <Row columnKey="9/15/06" rowKey="Actual Variance" dataValue="-675" />
    <Row columnKey="9/22/06" rowKey="Actual Variance" dataValue="784" />
    <Row columnKey="9/29/06" rowKey="Actual Variance" dataValue="0" />
   
  </LocalRelationalData>
</Graph>

image

And another with some colour

image

<?xml version="1.0" encoding="utf-8"?>
<!-- This text is only visible when the system is on an incorrect Tools release. -->
<!-- Please contact your system administrator to upgrade to the most recent XYZ service pack. -->
<Graph graphName="pie_basic">
  <LocalRelationalData>
    <Row columnKey="January 2005" rowKey="On Time" dataValue=".11" />
    <Row columnKey="January 2005" rowKey="Early" dataValue=".21" />
    <Row columnKey="January 2005" rowKey="Late" dataValue=".05" />
    <Row columnKey="January 2005" rowKey="Past" dataValue=".12"  />
    <Row columnKey="January 2005" rowKey="History" dataValue=".25" />
    <Row columnKey="January 2005" rowKey="Now" dataValue=".03" />
    <Row columnKey="January 2005" rowKey="Then" dataValue=".14" />
    <Row columnKey="January 2005" rowKey="When" dataValue=".09" />
  </LocalRelationalData>
</Graph>

So – sure it’s a little basic, but add some colour to your users lives!  this is a NO charge piece of functionality that is yours. 

Remember that with the power of RIA, you could do some awesome things with 3rd party dashboard providers.  So you could form your data and just post to to a provider that would host your dashboard, then things could start to get super funky!

I’ll post my sample graphs that I’m using in my performance dashboard project shortly.

Friday, 1 August 2014

FDA JD Edwards Method invocation–design tools enhancement

I never thought that it would happen.  Not only is there a new button on the FDA design form, but it also has a new colour.  Has Denver gone mad?  What is Method Invocation!

image

The funny thing is, I cannot find ANYTHING about it.  There is nothing in the Doc, and a few funny cases.  Hmm, let’s watch this space.

image

Anyway, if you find anything on this, let me know with a comment.  I’m guessing it’s related to BSSV. 

Hide e1page items that you do not want to see

Starting Tools Release 9.1.2 with the 4.0 Generator and using tasks. The default is that any time an application has security the actual icon still appears but it is disabled for the user. It is possible to hide the secured icons by adding the global option *hideSecurityBlockedItems to the .dat file. When this option is present, items that are blocked by security will be completely invisible.

The setting works when the dat file uses Tasks. When using syntax runE1App the objects will still display.

That is cool to know that the user will only see the icons that they have access to!