I’ve been involved with the creation of this white paper on load testing in JD Edwards, take a look.
Shows some really good use-case scenarios.
Friday, 2 May 2014
Thursday, 1 May 2014
How big is your F0911? F0911 and oracle compression
This is a quick note on oracle OLTP compression. You’ll need to buy the advanced compression option to use this type of compression, as it’s good for OLTP type applications. The standard compression available with EE is only good for static data, therefore when your tables grow all the time – it’s not a great option. So pony up and pay the money.
How much space are you going to save? In my experience, you can save 89% of your disk by implementing compression on a table like F0911… “What do you mean Shannon?” I hear you asking… Say you had an F0911 that was 50GB (excluding indexes, approx 80 million rows), then it could come down to about 5.5GB!!!! This is amazing. I’ve seen large tables between 11% and 28% of their original size.
What’s that, hold off on your hardware upgrade and buy a database option pack (via Myriad) to solve all your performance problems – what a great idea. 
Of course it may not all be positive. Perhaps more CPU usage do decompress – but who cares about CPU. Disk is king when it comes to better performance on large databases. How are you going to ensure that this means better performance for the end user? Well, that is simple too – get myriad to do some load testing for your with Oracle Application Testing Suite. We could easily benchmark before and after in a isolated environment to ensure that everything will be “alright on the night”.
Let the chart do the talking!
Where are favourites / favorites stores in JD Edwards
They are actually stored in F9000 – as tasks.
They have a TMTASKNM of the username with an @ appended to the front of it. So, a query like:
SELECT * FROM PRODCTL.F9000 WHERE TMTASKNM like ‘@%’ ;
is probably going to give you want you need. This is the items that are stored in their fav are stored in the F9001. So you use the task ID from F9000 and select what that points to in F9001 (TRPARNTTSK) and this is what is in the favs.
This is easy in SQL too:
select * from prodctl.f9000 where tmtaskid in(
select trchildtsk from prodctl.f9001 where trparnttsk in (
select tmtaskid from prodctl.f9000 where tmlngtask like '@%' and tmtasknm like '@%'))
Armed with the above, you could remove certain favourites, or find the missing ones – all within your grasp.
They also seem to have a TMTASKTYPE of 07, so you can probably use that also.
Thursday, 17 April 2014
Stop disconnecting my session, stop windows sessions timing out, stop locking the work station.
I’ve picked up a little assignment on a new 2008R2 machine. I’m running TC’s and all sorts of things and the machine is locking me after 5 minutes of inactivity. Come on! You turn away for a minute to google something and and the screen is locked. Also, it gets better, it logs me out after an hour!!!!
I’ve raised a bunch of calls, but you’d think I was working for the CIA, too secure to change these settings for me. Okay… No worries. I’ve created a little vbscript to help me… Wrong I know, but…
Copy below into “StopLockingMyMachine.vbs – save
set objShell = wscript.createobject("WScript.Shell")
Do until success = True
Success = objshell.AppActivate("something.txt - notepad")
objShell.sendkeys FormatDateTime(Time,4) & " I'm sleeping for 2 minustes Shhhhhh ~"
objShell.SendKeys "%{TAB}"
wscript.sleep 120000
success = false
Loop
Then start->run->notepad something.txt
Double click your StopLockingMyMachine.vbs script.
run your script. Every two minutes it’ll activate the notepad window and type a message into it, then it’ll take you back from whence you came… Oh, this has saved me…
I’d stop it if you were doing lots of sensitive things, but if you are checking TC’s and things, it’s a belter.
manual unicode coversion for JDE part 2: rebuild indexes–just add parallel!
This is used when doing manual unicode conversion index generation. Note that with a couple of tweeks, some parallel – you’ll be getting them done in nottime!
create or replace function jde_unicode_create_index(szOwnerSrc in varchar2,
szTableNameSrc in varchar2,
szOwnerDest in varchar2,
szTableNameDest in varchar2,
errMsg out varchar2)
return integer
authid current_user
is
OW_SP_SUCCESS constant integer := 0;
OW_SP_ERROR constant integer := -20101;
pkName varchar2(30) := null;
cursor ind_name_cur is
select owner, index_name, uniqueness, tablespace_name, ini_trans, max_trans,
initial_extent, next_extent, min_extents, max_extents, pct_increase,
freelists, pct_free
from sys.all_indexes
where table_owner = szOwnerSrc and table_name = szTableNameSrc
order by index_name;
ind_name_rec ind_name_cur%rowtype;
type ColCurTyp is ref cursor;
ind_col_info ColCurTyp;
ind_col_exp ColCurTyp;
cons_col_info ColCurTyp;
sql_stmt1 varchar2(256);
sql_stmt2 varchar2(256);
sql_ind_create varchar2(1024);
sql_ind_rename varchar2(128);
sql_cons_drop varchar2(128);
ind_col_name varchar2(30);
ind_col_pos integer;
ind_col_order varchar2(4);
cons_col_name varchar2(30);
ind_col_expression varchar2(32);
isNotFirstColumn boolean := false;
isNotFirstIndex boolean := false;
ind_count integer := 0;
begin
errMsg := null;
sql_stmt1 := 'SELECT COLUMN_NAME, COLUMN_POSITION, DESCEND FROM SYS.ALL_IND_COLUMNS WHERE INDEX_OWNER = :1 AND INDEX_NAME = :2 ORDER BY COLUMN_POSITION';
sql_stmt2 := 'SELECT COLUMN_EXPRESSION FROM SYS.ALL_IND_EXPRESSIONS WHERE INDEX_OWNER = :1 AND INDEX_NAME = :2 AND COLUMN_POSITION = :3';
isNotFirstColumn := false;
open ind_name_cur;
loop
if (isNotFirstIndex) then
isNotFirstColumn := false;
end if;
sql_ind_create := 'CREATE ';
fetch ind_name_cur into ind_name_rec;
exit when ind_name_cur%notfound;
sql_ind_rename := 'ALTER INDEX ' || ind_name_rec.owner || '.' || ind_name_rec.index_name || ' RENAME TO ' || szTableNameSrc || '_' || ind_count;
ind_count := ind_count + 1;
if (ind_name_rec.uniqueness = 'UNIQUE') then
sql_ind_create := sql_ind_create || 'UNIQUE INDEX ';
else
sql_ind_create := sql_ind_create || 'INDEX ';
end if;
sql_ind_create := sql_ind_create || ind_name_rec.owner || '.' || ind_name_rec.index_name || ' ON ' || szOwnerDest || '.' || szTableNameDest || '(';
open ind_col_info for sql_stmt1 using ind_name_rec.owner, ind_name_rec.index_name;
loop
fetch ind_col_info into ind_col_name, ind_col_pos, ind_col_order;
exit when ind_col_info%notfound;
if (isNotFirstColumn) then
sql_ind_create := sql_ind_create || ', ';
end if;
if (ind_col_order = 'DESC') then
open ind_col_exp for sql_stmt2 using ind_name_rec.owner, ind_name_rec.index_name, ind_col_pos;
fetch ind_col_exp into ind_col_expression;
ind_col_name := substr(ind_col_expression, 2, length(ind_col_expression)-2);
close ind_col_exp;
end if;
sql_ind_create := sql_ind_create || ind_col_name || ' ' || ind_col_order;
isNotFirstColumn := true;
end loop;
close ind_col_info;
isNotFirstIndex := true;
sql_ind_create := sql_ind_create || ')';
if (ind_name_rec.pct_free is not null) then
sql_ind_create := sql_ind_create || ' PCTFREE ' || ind_name_rec.pct_free;
end if;
if (ind_name_rec.ini_trans is not null) then
sql_ind_create := sql_ind_create || ' INITRANS ' || ind_name_rec.ini_trans;
end if;
if (ind_name_rec.max_trans is not null) then
sql_ind_create := sql_ind_create || ' MAXTRANS ' || ind_name_rec.max_trans;
end if;
if (ind_name_rec.initial_extent is not null) or
(ind_name_rec.next_extent is not null) or
(ind_name_rec.min_extents is not null) or
(ind_name_rec.max_extents is not null) or
(ind_name_rec.pct_increase is not null) or
(ind_name_rec.freelists is not null) then
sql_ind_create := sql_ind_create || ' STORAGE (';
end if;
if (ind_name_rec.initial_extent is not null) then
sql_ind_create := sql_ind_create || ' INITIAL ' || ind_name_rec.initial_extent;
end if;
if (ind_name_rec.next_extent is not null) then
sql_ind_create := sql_ind_create || ' NEXT ' || ind_name_rec.next_extent;
end if;
if (ind_name_rec.min_extents is not null) then
sql_ind_create := sql_ind_create || ' MINEXTENTS ' || ind_name_rec.min_extents;
end if;
if (ind_name_rec.max_extents is not null) then
sql_ind_create := sql_ind_create || ' MAXEXTENTS ' || ind_name_rec.max_extents;
end if;
if (ind_name_rec.pct_increase is not null) then
sql_ind_create := sql_ind_create || ' PCTINCREASE ' || ind_name_rec.pct_increase;
end if;
if (ind_name_rec.freelists is not null) then
sql_ind_create := sql_ind_create || ' FREELISTS ' || ind_name_rec.freelists;
end if;
if (ind_name_rec.initial_extent is not null) or
(ind_name_rec.next_extent is not null) or
(ind_name_rec.min_extents is not null) or
(ind_name_rec.max_extents is not null) or
(ind_name_rec.pct_increase is not null) or
(ind_name_rec.freelists is not null) then
sql_ind_create := sql_ind_create || ' )';
end if;
if (ind_name_rec.tablespace_name is not null) then
sql_ind_create := sql_ind_create || ' TABLESPACE '|| ind_name_rec.tablespace_name;
end if;
execute immediate sql_ind_rename;
execute immediate sql_ind_create;
end loop;
close ind_name_cur;
begin
select constraint_name into pkName from sys.all_constraints where owner = szOwnerSrc and table_name = szTableNameSrc and constraint_type = 'P';
exception
when no_data_found then
errMsg := 'Warning: no primary key in table ' || szOwnerDest || '.' || szTableNameDest;
when others then
errMsg := 'Error - ' || sqlerrm;
RAISE_APPLICATION_ERROR(OW_SP_ERROR, errMsg);
return (OW_SP_ERROR);
end;
if (pkName > ' ') then
sql_stmt1 := 'ALTER TABLE ' || szOwnerDest || '.' || szTableNameDest || ' ADD CONSTRAINT ' || pkName || ' PRIMARY KEY (';
sql_stmt2 := 'SELECT COLUMN_NAME FROM SYS.ALL_CONS_COLUMNS WHERE OWNER = :1 AND CONSTRAINT_NAME = :2';
sql_cons_drop := 'ALTER TABLE ' || szOwnerSrc || '.' || szTableNameSrc || ' DROP CONSTRAINT ' || pkName;
open cons_col_info for sql_stmt2 using szOwnerSrc, pkName;
isNotFirstColumn := false;
loop
fetch cons_col_info into cons_col_name;
exit when cons_col_info%notfound;
if (isNotFirstColumn) then
sql_stmt1 := sql_stmt1 || ', ';
end if;
sql_stmt1 := sql_stmt1 || cons_col_name;
isNotFirstColumn := true;
end loop;
close cons_col_info;
execute immediate sql_cons_drop;
sql_stmt1 := sql_stmt1 || ')';
execute immediate sql_stmt1;
end if;
return (OW_SP_SUCCESS);
exception
when others then
errMsg := 'Error - ' || sqlerrm;
RAISE_APPLICATION_ERROR(OW_SP_ERROR, errMsg);
return (OW_SP_ERROR);
end jde_unicode_create_index;
Saturday, 12 April 2014
Quantifying employee / user productivity with ERP analytics
What am I talking about? The ability to see who is your busiest ERP user, what screens they are going to and how often. Working out your end user interactive productivity. Sure, anyone can count the UBE’s that they run, or a quick query to see how machine sales orders they process or how many batches they post – but are those a true reflection of user productivity? No.
We’ve created ERP analytics, where we plug in the google analytic engine to your ERP. This gives us the ability to slice and dice your ERP usage – so you can work out your most productive users or your least productive users – this could be good information.
Add to this the average interactive performance metrics that can come out as well. Add to this alerts if your system starts to slow down. You are being told that the average interactive performance of your ERP is lower than expected – asking you to check things out! All of this is possible with Myriad’s ERP analytics.
This is a “no cost” service, where we apply our proprietary changes to your ERP installation which will being to populate your profile in google analytics. Within days and weeks you can understand what is being run, and how much it’s being used. Imagine upgrade time knowing EXACTLY what applications are being used and how often. Hone in your retrofit efforts!
So you can see from the above that you know how many users are logging in. Where they are logging in from, their browser, their OS everything.
We can tell you real-time – who is logged in – where they are logging in from:
We can tell you what app was used on what day
The slice and dice capabilities are endless.
We can also compare your performance (and usage patterns to other companies (anonymously).
All of this for free? Too good to be true? Well it’s not. Installation and configuration is all free. After two weeks we do a session with you to show you the data and you can see how you’d like to access the data.
Access to the live data only costs a small subscription fee for the number of users that need access to the analytics data.
UBESaveLogFiles=1 none of my UBE’s are creating ERROR log files
I want logs, I need them. Job status of D means nothing to me… I need to know if there were errors. you need to go to the [UBE] section of the JDE.INI and ensure that the following is set to 1.
[UBE]
UBESaveLogFiles=1
Note that the above should ALWAYS be 1
I don’t see how you could be confident with your system if this was not enabled.
Subscribe to:
Posts (Atom)
-
Why am I still doing this. Fixing fat clients is not a lot of fun. Here is a couple of tips for connecting to the database using NTS and... -
Ever wanted to know what that pesky OMW transfer error message code meant, here is the list of errors and what they mean in some meaningful...
-
CK : User Preference Cookies (what is saved in user preferences – Enable Hover Forms etc) GF : Grid Format (added when user adds a fri...