Pages

Thursday, January 18, 2024

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.