EnterpriseOne (E1) Batch Job Runtimes
We had someone ask this question the other day:
Can you tell me what jobs run for over 30 minutes?
Well, that seemed simple enough…
- Take the last action time and subtract the submit time and your good:
SELECT JCJOBQUE as JobQueue, JCENHV as Environment, JCUSER as User, substr(jcfndfuf2,0,locate('_',jcfndfuf2) + locate('_',substr(JCFNDFUF2,posstr(jcfndfuf2,'_')+1))) as JobName, concat(concat(JCSBMDATE,'-'),JCSBMTIME) as Submitted, concat(concat(JCACTDATE,'-'),JCACTTIME) as Ended, (jcacttime - jcsbmtime) as Runtime FROM svm811/f986110 order by Runtime
Ok, I did a little more than that to make it look good. Anyway, the “Runtime” column will be numeric with a length up to 6 digits. So, it could be read in this format: HHMMSS.
Not, too bad, right? Well, the problem comes in when you have a job that runs over 235959. The following is a very lengthy and ugly piece of SQL. It can probably be done more simply so, if you know of a better way, please let me know. Believe it or not but, I split up the lines a little so it would be easier to read.
1: SELECT JCJOBQUE as JobQueue,
2: JCENHV as Environment,
3: JCUSER as User,
4: substr(jcfndfuf2,0,locate('_',jcfndfuf2) +
5: locate('_',substr(JCFNDFUF2,posstr(jcfndfuf2,'_')+1))) as JobName,
6: concat(concat(JCSBMDATE,'-'),JCSBMTIME) as Submitted,
7: concat(concat(JCACTDATE,'-'),JCACTTIME) as Ended,
8: timestampdiff(4,cast(
9: (timestamp(date(char(jcactdate+1900000)),
10: time(
11: insert(insert(right(char(digits(jcacttime)),6),3,0,'.'),6,0,'.'))))
12: -(timestamp(date(char(jcsbmdate+1900000)),
13: time(
14: insert(insert(right(char(digits(jcsbmtime)),6),3,0,'.'),6,0,'.'))))
15: as char(22))) as Runtime
16: FROM svm811/f986110
17: order by Runtime
What the heck did I do? Well, lets go through it (IBM V5R4 SQLReference):
- Lines 1-3: Basic stuff to make the result make more sense.
- Lines 4-7: Remove the extra junk on the job name
(I only want the UBE and the version). - Line 8: Start the timestampdiff function.
The first argument determines the interval that you
want the result in. The second is required to be cast
to CHAR(22) datatype. - Line 9: Start the LastActivity date portion of the timestamp.
- Line 10: Start the LastActivity time portion of the timestamp.
- Line 11: First create a 6 digit numeric value. Then create a char
datatype in the format: HH.MM.SS - Line 12: Start the Submit date portion of the timestamp.
- Line 13: Start the Submit time portion of the timestamp.
- Line 14: Essentially the same as Line 11.
- Line 15: Finish the Cast function and return a nice name.
- Line 16: The table that we use.
- Line 17: Give it some order.
4 Responses to “EnterpriseOne (E1) Batch Job Runtimes”
This SQL doesn’t work quite correctly. I’m not sure about where you live, but where I live there are only 60 seconds in a minute, 60 seconds in an hour and 24 hours in a day. Your calculation for Runtime conveniently ignores these facts, resulting in wildly incorrect results (your calculation assumes 100 seconds per minute, and 100 minutes per hour).
For instance, a job starts at 5:00:28 AM and ends at 8:10:39 PM. According to your calculations, the runtime is 31,011 seconds (8 hours, 36 minutes, 51 seconds). In fact, the runtime for this job is 11,411 seconds (3 hours, 10 minutes, 11 seconds).
An easier solution would be to convert the JDE time to a Time the AS/400 understands, then use built-in functions to calculate the elapsed time. Or you could do all the calculations in SQL, but to get correct results you’d better remember how many hours, minutes and seconds are in a day.
Just for reference, you can’t subtract JDE dates to get number of days, either, since there are only 365 days in a year (not the 999 allowed by JDE dates).
By Bill, St. Louis, MO on Mar 5, 2008
Hmmm…
Thanks for the comment, I think
First of all, I’m not sure if I should take this as an insult or just a comment from an over worked information technology colleague. Either way, the first paragraph was fairly harsh.
Now, lets get to the concerns you pointed out between your attempts at put-downs.
Job start time = 5:00:28 AM
Job end time = 8:10:39 PM
The answer to this is actually 15 hours, 10 minutes, 11 seconds.
Assuming this was a typo, lets look at the following:
Job start time = 5:00:28 PM
Job end time = 8:10:39 PM
The answer that you would come up with in the SQL is 31011. Looking at this
result from
an EnterpriseOne point-of-view, this would translate to 3 hours, 10 minutes, 11
seconds. Which just so happens to be what you came up with.
If you read the whole post, you may have seen that that is exactly what I did in
the second SQL.
I used the TIMESTAMP function as in lines 9
and 12 above.
This issue was the whole reason for the second SQL. “the problem comes in
when you have a job that runs over 235959″, meaning 11:59:59 PM.
Thanks for the comment Bill. I’m not sure if you were having a bad day when
you saw this SQL or what but, next time when you comment on my blog don’t insinuate
incompetence. I make mistakes, just like the next guy, and appreciate it when
they are pointed out with some CLASS.
By stewart on Mar 5, 2008
I think you need to be careful when you say “Take the last action time and subtract the submit time “.. that is only accurate if the job never waited in a queue. I’ve had jobs where the submit time is 10 hours from the last action time, but the job really only took 2 minutes to run, but was stuck waiting in the queue..
The only way I found to know for sure is look at the joblog, which gives you an accurate start time. Of course, that means there’s no easy way to query it..
Thoughts?
Just found this, I’m enjoying the blog!
By Bob Goyetche on Jul 10, 2008
Hey, Bob.
You are correct. That is a pretty big assumption that the job would not have waited in the queue. Unfortunately, like you said, there isn’t really any other way to easily query it.
Any suggestions for a better way to do it would be greatly appreciated.
Thanks for the comment!
By stewart on Jul 10, 2008