How to view the top 10 jobs using the most CPU

The germ of the idea for this post came from a question I was asked. The question was for screen that would show the top ten jobs consuming the most CPU, which would refresh on a regular basis. In previous posts I have written about the parts needed to achieve the desired result, here I am going to put it all together.

How do I get the jobs that are consuming the most CPU? I can get the elapsed CPU percent and CPU time from one of my favorite Db2 for i Table functions, ACTIVE_JOB_INFO.

The statement I will be using is:

01 SELECT JOB_NAME, 02 ELAPSED_CPU_PERCENTAGE, 03 ELAPSED_CPU_TIME 04 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 05 RESET_STATISTICS => 'NO', 06 DETAILED_INFO => 'NONE')) 07 ORDER BY ELAPSED_CPU_PERCENTAGE DESC,ELAPSED_CPU_TIME DESC 08 LIMIT 10

Lines 1 – 3: I only want the following columns in my results:

1. JOB_NAME:  Full job name
2. ELAPSED_CPU_PERCENTAGE:  Percent of processing time attributed to the job during the time interval
3. ELAPSED_CPU_TIME:  Total CPU time spent during the measurement time interval in milliseconds

Line 4: Getting the results from ACTIVE_JOB_INFO.

Line 5: The first parameter I am passing is to tell the Table function not to reset the statistics.

Line 6: I can get various sets of results. The less data I get the faster the Table function will run. By asking for the 'NONE' set of results will include the columns I desire, and return the least number of columns of data.

Line 7: I want the results for the ten jobs using the most CPU, therefore, I want to sort by the elapsed CPU percentage in descending order, and then by the number of elapsed CPU time also in descending sequence.

I need to run the statement twice to get results for a CPU percentage that is not zero. Why? This is the same if I were to use the Work with Active Jobs command, WRKACTJOB. To calculate the elapsed values I need a "base", and the first time this statement is executed becomes the "base". The second time will show the elapsed values when compared to the first.

As I am going to present the results in a subfile on a display file. This is the DDS source for the display file:

01 A DSPSIZ(24 80 *DS3) 02 A INDARA 03 *------------------------------------------------------------------------- 04 A R SFL01 SFL 05 A ZSFLRRN 5S 0H ALIAS(Z_SFL_RRN) 06 A ZJOBNAME 28A O 3 2ALIAS(Z_JOB_NAME) 07 A ZCPUPCT 5 2O 3 31EDTCDE(J) 08 A ALIAS(Z_CPU_PERCENT) 09 A ZCPUSEC 7 0O 3 39EDTCDE(J) 10 A ALIAS(Z_CPU_SECONDS) 11 *------------------------------------------------------------------------- 12 A R CTL01 SFLCTL(SFL01) 13 A SFLSIZ(0010) 14 A SFLPAG(0010) 15 A OVERLAY 16 A LOCK 17 A N30 SFLCLR 18 A 30 SFLDSP 19 A 30 SFLDSPCTL 20 A 1 20'RPGPGM.COM' 21 A DSPATR(HI)

Line 2: I always use the indicator area/data structure with my display files, as it gives me a better way to handle the display file's indicators in my RPG program.

This display file is very simple, with only two record formats:

1. SFL01:  The subfile
2. CTL01:  The subfile control

Lines 5 – 10: I always like to give my display file's fields aliases. This is so I can use the long alias name in my RPG program.

Lines 13 and 14: As I am going to retrieve only ten results my subfile size and subfile page should be just ten records.

Line 17 – 19: I only use one indicator to condition the display of the subfile control and subfile records, as there will always be more records in the subfile. If the indicator is not on the subfile will be cleared.

When I test the subfile it looks like:

RPGPGM.COM OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666- OOOOOOOOOOOOOOOOOOOOOOOOOOOO 666.66- 6,666,666-

The first column is the job name, the second the CPU percentage, and the third the CPU milliseconds.

The basic design of the RPG program is that it will present the subfile of results, pause for 10 seconds, and then repeat. I have not included any way for the program to end. When I am done, I can just end the end the program manually.

The source code for the global definitions of the RPG program is:

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-s PauseSeconds int(10) inz(10) ; 04 dcl-f TESTSFL workstn indds(Ind) sfile(SFL01 : Z_SFL_RRN) alias ; 05 dcl-ds Ind qualified ; 06 SflIndicators ind pos(30) ; 07 end-ds ;

Line 2: To make this an efficient RPG program I am using a Main procedure, as without one the program will execute the RPG cycle at least once. I want the compiled program to use the source statement numbers, and as I call another procedure the program cannot run in the default activation group.

Line 3: This variable definition is for the number of seconds I want the program to pause between refreshes. I put this at the top of the program so it is quick and easy to find if I want to change the pause time in the future.

Line 4: The file definition for the display file. Note its indicator data structure's name is given in the INDDS keyword.

Lines 5 – 7: Indicator data structure.

The Main procedure is:

08 dcl-proc Main ; 09 dcl-pr sleep int(10) extproc('sleep') ; 10 *n uns(10) value ; 11 end-pr ; 12 dcl-s SleepError int(10) ; 13 GetData() ; 14 SleepError = sleep(1) ; 15 dow (*on) ; 16 GetData() ; 17 write CTL01 ; 18 SleepError = sleep(PauseSeconds) ; 19 enddo ; 20 return ; 21 on-exit ; 22 close *all ; 23 end-proc ;

Lines 9 – 11: As I am using the sleep procedure to pause the program. This is its procedure definition.

Line 12: This variable is used to contain the return code if sleep does not complete successfully.

Line 13: This procedure does the initial getting of the data, which creates the "base" when I get the elapsed data.

Line 14: "sleep" the program for a second.

Line 15: Start of a never ending Do-loop, which ends at line 19.

Line 16: Get the data, and load the subfile.

Line 17: I am writing the control record format. If I EXFMT the program will wait for someone to press a key before it continues. The write statement will write the screen and move onto the next statement in the program.

Line 18: "sleep" the program for the desired number of seconds I gave in the PauseSeconds variable in the global definitions.

Line 21: The ON-EXIT section of a procedure is executed whether the procedure completes successfully or not.

Line 22: By using the display file within a procedure I need to close it.

The data is retrieved from ACTIVE_JOB_INFO and written to the subfile by the GetData procedure.

24 dcl-proc GetData ; 25 dcl-ds DataIn qualified dim(10) ; 26 JobName char(28) ; 27 Percent packed(5 : 2) ; 28 Seconds packed(7 : 0) ; 29 end-ds ; 30 dcl-s Count uns(3) ; 31 exec sql DECLARE C0 CURSOR FOR 32 SELECT JOB_NAME, 33 ELAPSED_CPU_PERCENTAGE, 34 ELAPSED_CPU_TIME 35 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 36 RESET_STATISTICS => 'NO', 37 DETAILED_INFO => 'NONE')) 38 ORDER BY ELAPSED_CPU_PERCENTAGE DESC, 39 ELAPSED_CPU_TIME DESC 40 LIMIT 10 41 FOR READ ONLY ; 42 exec sql OPEN C0 ; 43 exec sql FETCH C0 FOR 10 ROWS INTO :DataIn ; 44 exec sql CLOSE C0 ; 45 Ind.SflIndicators = *off ; 46 write CTL01 ; 47 Ind.SflIndicators = *on ; 48 for Count = 1 to 10 ; 49 Z_SFL_RRN = Count ; 50 Z_JOB_NAME = DataIn(Count).JobName ; 51 Z_CPU_PERCENT = DataIn(Count).Percent ; 52 Z_CPU_SECONDS = DataIn(Count).Seconds ; 53 write SFL01 ; 54 endfor ; 55 end-proc ;

Lines 25 – 29: This is the data structure array I will be using to contain the fetched data from ACTIVE_JOB_INFO.

Line 30: This variable will is used as a counter for a For-group.

Lines 31 – 41: The cursor definition to fatch the data I want from ACTIVE_JOB_INFO.

Line 43: I am using a multiple row fetch from the cursor of ten results into the data structure array.

Line 45: By setting off the indicator in the subfile control record format the subfile is cleared.

Line 47: When the indicator is set on when the next time the subfile control record format is written it will display both the subfile and control record formats.

Lines 48 – 54: I am using a For-group to execute the statements in lines 49 – 53 to prepare the data and write to the subfile ten times.

Once the ten subfile records are written control returns to the Main procedure.

When I call the program the following is displayed.

RPGPGM.COM 085110/QUSER/QZDASOINIT .17 1,404 086607/QUSER/QZDASOINIT .08 718 086646/SIMON/DSP01 .05 453 026234/GXXXXXX/GXXXXXXXXX .05 482 024871/BXXXX/BXXXXXX .05 419 084784/QUSER/QZDASOINIT .05 412 025082/RXXXXXXX/EXXXXXXXXXX .03 319 025153/IXXXXX/QXXXXXXXXXX .03 316 086537/QUSER/QZDASOINIT .03 260 083762/QUSER/QZRCSRVS .02 220

As I watch it, I can see that every ten seconds the data is refreshed.

This is a quite simple example. If this was something I was going to move to a production environment I, like you would, make a better looking screen and have a way to exit without having to end the program.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.