Getting statistics for jobs that have ended

One of the new additions to Db2 for i, or SQL, in the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, is a new Table function ENDED_JOB_INFO. This Table function exists in the SYSTOOLS library. ENDED_JOB_INFO allows me to retrieve some information about jobs that have, as the name suggests, ended.

ENDED_JOB_INFO has two parameters:

1. START_TIME:  The start of the range of jobs to find. If a job starts on or after this time it will be included in the results. If no value is given the default is the current timestamp less one day.
2. END_TIME:  End of the range. If no value is given the default is '9999-12-30-00.00.00.000000'.

Before I give my examples I recommend you run the following statement to see all of the columns of information that are available:

01 SELECT * 02 FROM TABLE(SYSTOOLS.ENDED_JOB_INFO()) 03 LIMIT 20

Line 1: I want all of the available columns returned in the results.

Line 2: No start or end timestamp is given, therefore, all the available rows will be selected.

Line 3: Displaying all the rows is too much data, therefore, I have used the limit clause to only return the first 20 rows.

The partition I use to write these posts does not have a lot of activity upon it so my results will not be as dramatic as yours will be if you run this on a production partition.

In my first example I want to see all of the jobs I have run in the past 48 hours to determine which ones have consumed the most CPU time. The CPU time is returned in seconds. To retrieve this information I would the following statement:

01 SELECT MESSAGE_TIMESTAMP,FROM_JOB,CPU_TIME,PEAK_TEMPORARY_STORAGE, 02 JOB_INTERFACE 03 FROM TABLE(SYSTOOLS.ENDED_JOB_INFO( 04 START_TIME => CURRENT_TIMESTAMP - 2 DAYS, 05 END_TIME => CURRENT_TIMESTAMP)) 06 WHERE FROM_USER = 'SIMON' 07 ORDER BY CPU_TIME DESC

Line 1 and 2: These are the columns I am selecting for my results. I think the names a self-explanatory, therefore, I will not explain what they are here.

Lines 3 – 5: This is the definition of the Table function. I have used the parameter names, as I would if I used this is a program. I only want to retrieve the results that have happened in the last two days (= 48 hours).

Line 6: I only want the results for all jobs that I ran.

Line 7: And I want them in descending CPU time, which means the one that took the most time comes first.

The results were:

PEAK_ CPU_ TEMPORARY MESSAGE_TIME FROM_JOB TIME _STORAGE -------------------------- --------------------- ----- --------- DDDD-DD-DD 08:41:28.193375 668101/QUSER/QZRCSRVS 0.109 13 DDDD-DD-DD 07:41:46.046482 669125/QUSER/QZRCSRVS 0.100 13 DDDD-DD-DD 15:39:42.943939 669111/QUSER/QZRCSRVS 0.022 9 DDDD-DD-DD 10:18:21.336937 669112/QUSER/QZRCSRVS 0.021 9 DDDD-DD-DD 07:42:46.792763 669124/QUSER/QZRCSRVS 0.020 9 JOB_ INTERFACE FROM_USER -------------- --------- REMOTE COMMAND SIMON REMOTE COMMAND SIMON REMOTE COMMAND SIMON REMOTE COMMAND SIMON REMOTE COMMAND SIMON

The columns I selected return the following information:

• MESSAGE_TIMESTAMP:  The timestamp when the message CPF1164 was sent to the partition's history log, i.e. the time the job started.
• FROM_JOB:  The full job name. As I used ACS's Run SQL Scripts the user for the job is not my user profile, that is in a different column.
• CPU_TIME:  The total amount of CPU seconds used by the job.
• PEAK_TEMPORARY_STORAGE:  The maximum amount of temporary storage, in megabytes, the job used.
• JOB_INTERFACE:  The interface that send the CPF1164 to the history log. There are many possible values that this column contain, and I will refer you to IBM's documentation that I have linked at the bottom of this post.
• FROM_USER:  The real username when the job started.

As you can see I did not run any jobs that consumed much of the CPU or temporary storage.

If I wanted to see the ten jobs which had used the most CPU time in the past 48 hours I would use the following:

01 SELECT MESSAGE_TIMESTAMP,FROM_JOB,CPU_TIME,PEAK_TEMPORARY_STORAGE, 02 JOB_TYPE,JOB_END_CODE,JOB_INTERFACE 03 FROM TABLE(SYSTOOLS.ENDED_JOB_INFO(CURRENT_TIMESTAMP - 2 DAYS, 04 CURRENT_TIMESTAMP)) 05 ORDER BY CPU_TIME DESC 06 LIMIT 10

Lines 1 and 2: The columns I want returned in my results.

Lines 3 and 4: I have not bothered with the parameter names, and just given the values for the time range.

Line 5: Want my results sorted by CPU time in descending order.

Line 6: And only return ten results, which will be the ten "worse offenders".

The results were:

PEAK_ CPU_ TEMPORARY MESSAGE_TIME FROM_JOB TIME _STORAGE -------------------------- ----------------------- -------- --------- DDDD-DD-DD 15:54:58.323969 669114/QUSER/QZDASOINIT 1130.419 256 DDDD-DD-DD 16:25:04.386806 668852/QUSER/QZDASOINIT 33.049 41 DDDD-DD-DD 01:04:58.560175 669177/QUSER/QZDASOINIT 32.652 41 DDDD-DD-DD 16:30:03.135141 668851/QUSER/QZDASOINIT 32.218 41 DDDD-DD-DD 20:34:57.695948 669137/QUSER/QZDASOINIT 32.107 41 DDDD-DD-DD 01:45:03.876730 668941/QUSER/QZDASOINIT 31.352 41 DDDD-DD-DD 15:55:01.056650 669088/QUSER/QZDASOINIT 30.941 41 DDDD-DD-DD 11:05:00.266958 669044/QUSER/QZDASOINIT 30.745 41 DDDD-DD-DD 21:00:04.147785 668892/QUSER/QZDASOINIT 30.691 41 DDDD-DD-DD 05:54:59.997215 669233/QUSER/QZDASOINIT 30.382 41 JOB_ END_ JOB_TYPE CODE JOB_INTERFACE -------- ----- ----------------- BATCH 10 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 10 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR BATCH 0 ODBC OR FILE TXFR

I added two columns in these results that were not on the previous:

• JOB_TYPE:  All of these jobs were batch jobs.
• JOB_END_CODE:  A two-digit code that relates to how the job ended. The column JOB_END_DETAILS gives the description for these codes. I did not include that column in these results due to space constraints.

The JOB_INTERFACE shows the interface used by the job. These were all ODBC jobs.

I know my examples are very simple. As I mentioned earlier the results would be more interesting if you use this on your production partition. It would give you the chance to report on which jobs are the "hogs" in your system.

 

You can learn more about the ENDED_JOB_INFO SQL Table function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.