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