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!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post Weblogic Business Services Truncating XML Payload
Next post File Cleanup Using Windows BAT/CMD Files