IBMi (AS400) fans only ' As an alternative to WRKACTJOB: How to get Active Jobs data using SQL ACTIVE_JOB_INFO table function

#IBMiSample

The ACTIVE_JOB_INFO table function returns one row for every active job. You can use it to get the same information as the WRKACTJOB command in an alternate mode.

Look at the following examples:

a. List all jobs of  user VDOTEST

SELECT JOB_NAME, SUBSYSTEM, SUBSY00001, AUTHO00001, JOB_TYPE,

FUNCT00001, FUNCTION, JOB_STATUS,  MEMOR00001                

FROM TABLE(ACTIVE_JOB_INFO())                                

WHERE JOB_NAME LIKE '%VDOTEST%'                              

b. List all jobs of  subsystem QBATCH

SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_TOTAL_DISK_IO_COUNT,

ELAPSED_CPU_PERCENTAGE                                           

FROM TABLE(QSYS2.ACTIVE_JOB_INFO(                                

SUBSYSTEM_LIST_FILTER => 'QBATCH')) A                            

ORDER BY ELAPSED_TOTAL_DISK_IO_COUNT DESC                        

c. List all jobs temporary storage

SELECT JOB_NAME, AUTHORIZATION_NAME, TEMPORARY_STORAGE,     

SQL_STATEMENT_TEXT                                          

FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) A

WHERE JOB_TYPE <> 'SYS' and SUBSYSTEM = 'QINTER'            

ORDER BY TEMPORARY_STORAGE DESC                             

d. List all jobs and decompose the JOB_NAME field into separate columns.

SELECT SUBSTR(JOB_NAME,1,6) AS JOB_NUMBER,                      

SUBSTR(JOB_NAME,8,POSSTR(SUBSTR(JOB_NAME,8),'/')-1) AS JOB_USER,

SUBSTR(SUBSTR(JOB_NAME,8),POSSTR(SUBSTR(JOB_NAME,8),'/')+1)     

AS JOB_NAME FROM TABLE (QSYS2.ACTIVE_JOB_INFO()) AS A           

bye.

                     

I appreciate all the comments made on this blog.