Quick method of determining yearly usage

I was checking the other day whether data had been archived from the production environment.  The easiest way I could think of to do this was to summarise the relevant tables by year, and see how many rows were added for each of the years.

The very simple queries below were used on all of the tables that were in the archive set.  Note that if you were doing this you’d probably be checking the F0911…  Strange that I did not need to!

The only interesting thing about this post is the round command really, allowing you to group by year julian date style

select count(1), round(rpupmj, -3) from uadta.f03b11 group by  round(rpupmj, -3) order by 2 desc; 

select count(1), round(rzupmj, -3) from uadta.f03b14 group by  round(rzupmj, -3) order by 2 desc; 

select count(1), round(ryupmj, -3) from uadta.f03b13 group by  round(ryupmj, -3) order by 2 desc; 

Monitoring a go-live with google analytics and fixing table scans with Application Query Security

A recent large go-live has us monitoring things very closely to ensure that we are not getting problems.  Trying to be a little more proactive on problems

EM12C shows us all of the problem statements.  We are concentrating on the high impact long running statements.  We are able to then track these back to user applications.  This is a little harder than you might think.  Tracing users back trough their session to a web server and then using server manager to find the screens that they have open.  This information and cross reference can generally find the bad statements.

We then use all of the above and the new “Application Query” security to force users to enter at least 1 value into the keyed fields.

This is a a huge Segway from monitoring with google analytics to adding Application Query security for more efficient database queries:

Remember to save it first

Then open it again to configure fields

What I’m doing witht this SQL is finding the first column in all indexes for the F0911.  Then I’m going to make sure that at least one of these is selected for the Application Query Security, so I can be sure that an index is going to be used when people are looking at the F0911.  I don’t want table scans of 65000000 records.

This shows the columns, and then links to DD to help you find the “JDE name” that links to the DD items.

select distinct(column_name), dd1.frowdi,  dd2.frdscr
from all_ind_columns, dd910.f9210 dd1, dd910.f9202 dd2 
where table_name = 'F0911' 
and table_owner = 'PRODDTA' 
and column_position = '1' 
and trim(substr(column_name, 3, 10)) = trim(dd1.frdtai)
and dd1.frdtai = dd2.frdtai;

 

• GLBRE BatchRearEndPostCode                     Batch Rear End Posted Code             
  
• GLSBL Subledger                               Subledger                              
  
• GLCO Company                                 Company Number                         
  
• GLOBJ ObjectAccount                           Object Account                         
  
• GLDCT DocumentType                             Document Type                          
  
• GLOBJ ObjectAccount                           Cost Type                              
  
• GLAID AccountId                               Account ID                             
  
• GLCO Company                                 Company                                
  
• GLICUT BatchType                               Batch Type                             
  
• GLPOST GLPostedCode                             G/L Posted Code                        
  
• GLBC BillCode                                 Bill Code                              
  
• GLJELN JournalEntryLineNo                       Journal Entry Line Number              
  
• GLJELN JournalEntryLineNo                       J/E Line Number                        
  
• GLASID SerialTagNumber                         Serial Number                          
  
• GLRCND ReconciledROrBlank                       Reconciled       
• 
  

                     

The about will help you find the screen:

So, we can force users to only query of keyed columns – nice!

 

What link they are using

And where they are coming from.

Rantings of a impatient CNC consultant

I want to know know where the upgrade is at, and it’s killing me.  Lucky we have the logops table to help us along.  You need to be on an oracle database to see this information.

SELECT SID, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", elapsed_seconds, elapsed_seconds/(ROUND(SOFAR/TOTALWORK*100,2))*((totalwork-sofar)/totalwork*100)
FROM V$SESSION_LONGOPS
where sofar != totalwork

The script above is pretty handy, as it tells me the SID and SERIAL# of the sessions that are taking a long time.  How long the SQL has been running and also how many seconds to go.  This is great.

I can see things like index creation and also constraint creation – as well as complex SQL in the results.

OATS and automated regression testing

I’m finding this story is getting better and better.

A number of distinct things are pushing me in the direction of automated regression testing:

1. It works between tools releases, even 9.1.4 and 9.1.5 which has a large difference in look & feel
2. It’s very easy to use, record and maintain
3. You can import UPK test scripts directly into functional test cases
4. You can take advantage of “testing as a service” easily

Let’s look into these in more detail:

It works between tools releases, even 9.1.4 and 9.1.5 which has a large difference in look & feel

I’ve recorded a number of scripts against an old tools release and then tried running them against the new, and in many instances, this just works.  I guess that the magic is in the identification of the elements on the forms and the fact that much of this does not change.

Let’s look at (for example) if OATs wants to record you clicking the find button, it uses a reference (Path) like the below

or syntax like window:iframe:img identifier

/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:img[@alt='Find (Ctrl+Alt+I)' or @id='hc_Find' or @index='6' or @src='http://e1ent-dnt.mits.local:9081/jde/img/hc_Findmo.gif']

And actually looks like this code in java – which is essentially clicking the image hc_Findmo.gif

			web.image(
					1053,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:img[@alt='Find (Ctrl+Alt+I)' or @id='hc_Find' or @index='6' or @src='http://e1ent-dnt.mits.local:9081/jde/img/hc_Findmo.gif']")
					.click();

The chrome browser can also indicate the exact same information in developer mode

this provides a generic path to the object on the form.  No matter what styling goes on (css etc), the path maintains it’s accuracy between tools releases:

But you can see that the path (unique method of identifying the object on the form) is maintained between the tools release.

So, you’ll find that many of your scripts will also survive.

I was amazed that the fast path continued to work between the tools release, and that is only some nice code that keeps this working.

It’s very easy to use, record and maintain

You can quickly do a lot of testing with some very simple recording.  The power of databanks and simple editing techniques in the scripts allows you to quickly create some generic testing capabilities.  In fact, the combination of SQL scripts (oracle DB connect to find data) and regression testing with dynamic data can allow a generic testing service to test standard functionality.

The tool just records what you do and gives you a pretty easy interface to augment or change and of the tests that you want to do.  Really, very simple.

			web.element(
						1193,
						"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:nobr[@innerText='Regional Info ' and @text='Regional Info ' and @jdeWebGUIPopupMenu='yes' and @disabled='False' and @onblur='null' and @onclick='null' and @ondblclick='null' and @onfocus='null' and @onkeydown='null' and @onkeypress='null' and @onkeyup='null' and @onmousedown='null' and @onmousemove='null' and @onmouseout='null' and @onmouseover='null' and @onmouseup='null' and @tabindex='0' and @index='21']")
						.assertAttributes(
								"Check Regional infor button",
								web.attributes(web.attribute("tag", "NOBR",
										TestOperator.StringExact), web
										.attribute("innerText",
												"Regional Info ",
												TestOperator.StringExact), web
										.attribute("text", "Regional Info ",
												TestOperator.StringExact), web
										.attribute("jdeWebGUIPopupMenu", "yes",
												TestOperator.StringExact), web
										.attribute("disabled", "False",
												TestOperator.StringExact), web
										.attribute("onblur", "null",
												TestOperator.StringExact), web
										.attribute("onclick", "null",
												TestOperator.StringExact), web
										.attribute("ondblclick", "null",
												TestOperator.StringExact), web
										.attribute("onfocus", "null",
												TestOperator.StringExact), web
										.attribute("onkeydown", "null",
												TestOperator.StringExact), web
										.attribute("onkeypress", "null",
												TestOperator.StringExact), web
										.attribute("onkeyup", "null",
												TestOperator.StringExact), web
										.attribute("onmousedown", "null",
												TestOperator.StringExact), web
										.attribute("onmousemove", "null",
												TestOperator.StringExact), web
										.attribute("onmouseout", "null",
												TestOperator.StringExact), web
										.attribute("onmouseover", "null",
												TestOperator.StringExact), web
										.attribute("onmouseup", "null",
												TestOperator.StringExact), web
										.attribute("tabindex", "0",
												TestOperator.StringExact), web
										.attribute("index", "21",
												TestOperator.StringExact)));
				web.addGlobalVerifyText("CheckFormName", "Work With Addresses",
						Source.DisplayContent, TextPresence.PassIfPresent,
						MatchOption.Exact);
			}
			web.textBox(
					1051,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:div[@id='HDR_DIV0_1.1' or @text='Alpha\r\nName' or @index='64']")
					.click();
			{
				think(1.573);
			}
			eone.grid(
					1052,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:EOneGrid[@gridName='Work With Addresses' or @gridId='0_1']")
					.setQBEValue(0, "121");
			{
				think(1.201);
			}
			web.image(
					1053,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:img[@alt='Find (Ctrl+Alt+I)' or @id='hc_Find' or @index='6' or @src='http://e1ent-dnt.mits.local:9081/jde/img/hc_Findmo.gif']")
					.click();
			{
				think(2.464);
			}
			web.element(
					1054,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:div[@text='12121' or @index='99']")
					.click();
			{
				think(1.452);
			}
			web.image(
					1055,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:img[@alt='Find (Ctrl+Alt+I)' or @id='hc_Find' or @index='6' or @src='http://e1ent-dnt.mits.local:9081/jde/img/hc_Findmo.gif']")
					.click();
			{
				think(1.612);
			}
			web.textBox(
					1056,
					"/web:window[@index='0' or @title='Work With Addresses']/web:document[@index='10' or @name='e1menuAppIframe']/web:form[@id='E1PaneForm' or @name='P01012_W01012B' or @index='2']/web:input_text[@name='qbe0_1.0' or @index='2']")
					.click();
			{
				think(0.22);
			}

The above shows the level of code and how easy it is to change something if a developer or ESU has actually changed the internal ID of a field.  The other nice think is that it identifies with a bunch of “OR”s.  So, it checks for index OR name OR id – problem is when things have moved around – the wrong field might begin to get tested.  It’s simple to verify this in E1, especially looking in chome in developer mode.

You can import UPK test scripts directly into functional test cases

This is precisely what you should be doing.  Testing what you train on, train on what you test.  Therefore having UPK scripts create regression test scripts, that can be executed in a hierarchy in a single click is amazing!  You need to change the way that you test.  Your testing people need to be junior developers (java) and work with BA’s from the business.  Or perhaps the BA’s just write the UPK content and the junior developers write and maintain the test scripts.

The automated testing makes the decision to take an ESU MUCH easier, as you can automate an entire CRP / UAT test cycle in days with 0 users.  You can get graphical reporting and understand exactly where the failures occurred.

This is a powerful enhancement that is sure to get more people using OATs test manager and regression testing.

You can take advantage of “testing as a service” easily

Finally and perhaps most importantly, this opens the door to “testing as a service”, which could be completely generic web based testing.  You’d be asked to provide some data ranges and details of modifications – but the OATs regression testing could do the rest.

The test’s could log in, tell you the app, modification history, last to change, dynamically find some data to test with using database queries and then execute a COMPLETE set of testing over a large range of data (let’s imagine address book for example).  Once complete, email a client the summary of the test results.  Too good to be true – no!  This is being done (by Myriad IT) now!

The service is getting better and better all of the time.

Next numbers (F0002) and how they work

This is a pretty simple post that might be helpful.

You all know that in many situations (not all), JD Edwards get’s it’s next numbers from the next numbers table (with me so far?  I hope so).

The next numbers table is generally in your control library. <rant>I must admit, for the life of me I do not understand WHY this is in the control library.  It should be in the business data library / schema / owner.  This should be fixed “post haste”.  There is no reason that this table should not be classed as data. </rant>

But, let’s get back to the lesson.  The number for address book next numbers comes from F0101, NNN001 (when dealing with default behaviour – you can get fancy with NN by type).  The value in the NN

select NNN001 from prodctl.f0002@jde_p2jde where NNSY = '01';

select max(aban8) from proddta.F0101@jde_p2jde where aban8 < 5000000;

So, we learn from the above that the F0002 contains the next available unique number, you consume then and update it immediately so that the next transaction can select it.  Best to use the BSFN X0002 for grabbing NNs, then you don’t need to worry too much about all of the locking.

    NNN001
----------
   4842748 

MAX(ABAN8)
----------
   4842747 

So you can see from the above, in a normal NN situation, the NN has the NEXT value in it, you do not add 1 to the NN, you use the current value and then when you’ve committed your insert of the new AN8, then update the NN.  I also think that JDE does a “SELECT FOR UPDATE” to ensure that the NN table is locked for the duration of the MST (Multi-statement transaction).  This allows NN’s not to get sprayed all over the shop.

SELECT  *  FROM JDE_CRP.CRPCTL.F0002  (UPDLOCK)  WHERE  ( NNSY = '02' )  ORDER BY NNSY ASC FOR UPDATE OF NNN001, NNN002, NNN003, NNN004, NNN005, NNN006, NNN007, NNN008, NNN009, NNN010 

Then also for SQL

UPDATE JDE_CRP.CRPCTL.F0002  SET NNN001=21573840.000000,NNN002=1.000000,NNN003=0.000000,NNN004=0.000000,NNN005=0.000000,NNN006=0.000000,NNN007=0.000000,NNN008=0.000000,NNN009=0.000000,NNN010=0.000000 WHERE CURRENT OF C4E054F0 

Oracle

SELECT PRODCTL.F0002.*, ROWID FROM PRODCTL.F0002  WHERE  ( NNSY = '04' )  ORDER BY NNSY ASC FOR UPDATE OF NNN001, NNN002, NNN003, NNN004, NNN005, NNN006, NNN007, NNN008, NNN009, NNN010 

UPDATE PRODCTL.F0002  SET NNN001=1469670.000000,NNN002=50.000000,NNN003=5038.000000,NNN004=951130.000000,NNN005=434058.000000,NNN006=4695.000000,NNN007=38391.000000,NNN008=2162.000000,NNN009=10628.000000,NNN010=0.000000 WHERE ROWID = :LASTROWID

So select it, lock it while you’ve selected it and then update it ASAP.  Then use the selected VALUE in all your ongoing transactions.

Google analytics for JD Edwards

I talk about this quite a bit, but only because it’s UBER cool.

Look at this page for example.  This is a real time summary of ALL users on ALL web servers.  The current pages per minute and per second. 

How many JDE users are logged in and what applications they are running.

I’m sorry, this is so much cooler than server manager it’s not funny

If you want this plugged into your JD Edwards ERP – please get in contact and we can make it happen.  The implementation is simple.  You’ll have a birds-eye view of your performance “real-time” and COMPLETE historical analysis too.

If you make any changes, you can see the actual end user impact of those changes.

JD Edwards mobile applications–the easy way

There is a new mobile app on the scene, this one is surpassing all of the others and is the new standard based upon the oracle AIS server.

There are over 50 apps available for download and running, and we have the server that can run them.  They’ve been specifically written for a tablet or phone.

Goto google play store or apple “what ever” store and search for JD Edwards.  When you see things like “my work orders” etc, download one to your phone.  #10 below

Install it

Run it

Enter the URL above, http://myais.myriad-it.com:9090

no promises on the above, I’m unsure that you’ll have access to our other environments.  If this proves to be popular, I’d be happy to make sure that there is a demo username and password for accessing the mobile apps.

Username and password that you use for the JDE demo system.  Note that if you are registered for the myriad demo environment, I can get these credentials working fairly easily against the mobility site. http://e91demo.myriad-it.com/jde/E1Menu.maf for registration.

Then Whammo!  Enjoy mobility apps.

We’ve had to do lots of patching (and tuning) to get this thing working.  There are many mandatory ESU’s and builds that need to be done.  Some of the apps require the latest tools release too.

We’ve also been writing our own apps using the AIS server and rest and ionic, which is also going quite nicely.  Watch this space.

a little life lesson on nohup and using it

I would nohup everything in my life if it was possible, it’s a very nice way of ensuring that your jobs continue, even if your session does not.  I do a lot of command line driven processing in E1 where I submit a job and cross my fingers that it’s going to complete.  This is generally running a shed load of SQL in the back ground.  The thing is, if I do not use nohup, I pretty much at the mercy of the network connection between my putty session and my server for a solid commit and knowing that everything has finished nicely.

This is where you cannot write long running scripts without the use of nohup. 

The following linux command could be written like:

./convertBigTables.ksh 

but, it’s too risky for a go-live.  So, what should be done is the following:

nohup ./convertBigTables.ksh > ./uniconvlogs/convertBigTablesnohup.log 2>&1 &

Then you can just

tail –f ./uniconvlogs/convertBigTablesnohup.log 

To follow the progress.  It now does not matter what happens with your network, your processing will continue!