EnterpriseOne UBE Runtime Performance Audit For IBM iSeries

The following is a SQL query that I use to audit the performance of UBEs. Stick the results in a Excel spreadsheet and analyze to your hearts desire.

SELECT 
JCJOBQUE as JobQueue,
JCENHV as Environment,
JCUSER as User,
substr(jcfndfuf2,0,locate('_',jcfndfuf2) +
 locate('_',substr(JCFNDFUF2,posstr(jcfndfuf2,'_')+1))) as JobName,
 date(char(JCSBMDATE+1900000)) as SubmitDate,
 SUBSTR(RIGHT('0' || right(repeat('0',6) || JCSBMTIME,6), 6), 1, 2) || ':' ||
 SUBSTR(RIGHT(right(repeat('0',6) || JCSBMTIME,6), 4), 1, 2) || ':' ||
 RIGHT(right(repeat('0',6) || JCSBMTIME,6), 2) as SubmitTime,
 date(char(JCACTDATE+1900000)) as EndDate,
 SUBSTR(RIGHT('0' || right(repeat('0',6) || JCACTTIME,6), 6), 1, 2) || ':' ||
 SUBSTR(RIGHT(right(repeat('0',6) || JCACTTIME,6), 4), 1, 2) || ':' ||
 RIGHT(right(repeat('0',6) || JCACTTIME,6), 2) as EndTime,
timestampdiff(2,char(
 timestamp(date(char(JCACTDATE+1900000)),
 TIME(SUBSTR(RIGHT('0' || right(repeat('0',6) || JCACTTIME,6), 6), 1, 2) || ':' ||
 SUBSTR(RIGHT(right(repeat('0',6) || JCACTTIME,6), 4), 1, 2) || ':' ||
 RIGHT(right(repeat('0',6) || JCACTTIME,6), 2))
 )-
 timestamp(date(char(JCSBMDATE+1900000)),
 TIME(SUBSTR(RIGHT('0' || right(repeat('0',6) || JCSBMTIME,6), 6), 1, 2) || ':' ||
 SUBSTR(RIGHT(right(repeat('0',6) || JCSBMTIME,6), 4), 1, 2) || ':' ||
 RIGHT(right(repeat('0',6) || JCSBMTIME,6), 2))
 ))
) AS RUNTIME_SEC
FROM SVM812.F986110
WHERE JCSBMDATE>116121
ORDER BY 9 DESC

Here is the one for SQL Server.

Have fun!

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com