Getting Active Jobs data using SQL
With IBM i 7.2 released a whole lot of useful Views and Table functions that allowed us to gather, select, and view data that had otherwise only been available via a command or API. I have written about some of them, and today I am going to do so for another: ACTIVE_JOB_INFO, which basically gives me the save information as the Work With Active Jobs command, WRKACTJOB.
ACTIVE_JOB_INFO, found in library QSYS2, is a Table Function, therefore, it used in a slightly different way to a View. Perhaps the most obvious difference is that the Table Function has parameters, where a View does not.
ACTIVE_JOB_INFO has four optional parameters. I have listed them below with their equivalent in the Work Active Job command:
| ACTIVE_JOB_INFO Table Function parameter |
Parameter description | WRKACTJOB command parameter |
| RESET_STATISTICS | If YES restarts the statistics. If NO or not given continue from previous reset. | Reset status statistics (RESET) |
| SUBSYSTEM_LIST_FILTER | List of up to 25 subsystems can be given. If not used all subsystems is assumed. | Subsystem (SBS) |
| JOB_NAME_FILTER | What kinds of job(s) are to be displayed. If not given all jobs and types of jobs is assumed. | No equivalent |
| CURRENT_USER_LIST_FILTER | Up to 10 user profiles can be given. If not given all users is assumed. | No equivalent |
SELECT JOB_NAME,JOB_TYPE,JOB_STATUS,SUBSYSTEM,CPU_TIME FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) C WHERE JOB_NAME LIKE '%SIMON%' ORDER BY CPU_TIME DESC
01 dcl-ds Errors qualified dim(100) ;
02 JobName char(28) ;
03 Subsystem char(10) ;
04 JobType char(3) ;
05 JobStatus char(4) ;
06 end-ds ;
07 dcl-s wkMaxRows packed(3) inz(%elem(Errors)) ;
08 dcl-s wkRtvRows like(wkMaxRows) ;
09 exec sql DECLARE C0 CURSOR FOR
SELECT JOB_NAME,SUBSYSTEM,JOB_TYPE,
JOB_STATUS
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) X
WHERE JOB_STATUS = 'MSGW'
FOR READ ONLY ;
10 exec sql OPEN C0 ;
11 exec sql FETCH NEXT FROM C0
FOR :wkMaxRows ROWS INTO :Errors ;
12 wkRtvRows = SQLER3 ;
13 exec sql CLOSE C0 ;
14 if (wkRtvRows > 0) ;
15 dsply (%char(wkRtvRows) + ' errors found') ;
16 endif ;
SELECT * FROM TABLE(QSYS2.JOB_INFO(
JOB_SUBMITTER_FILTER => '*USER',
JOB_USER_FILTER => '*ALL'
)) X
SELECT * FROM TABLE(QSYS2.JOB_INFO(
JOB_SUBMITTER_FILTER => '*JOB',
JOB_USER_FILTER => '*ALL'
)) X
RPGPGM: Get active Jobs Data using SQL