Home
AS400 to IBM i
Db2

Getting Job information using SQL

Getting Job information using SQL, job_info table function, job_info
Getting Job information using SQL, job_info, table function
Getting Job information using SQL

The JOB_INFO table function returns one row for each job meeting the selection criteria.It gives us information similar to what returned from WRKUSRJOB, WRKSBSJOB, and WRKSBMJOB CL commands and the QUSLJOB i.e. List Job API. The function is available in QSYS2 schema/library.

Let me run a simple SQL query to demonstrate how this JOB_INFO table function works, the most important thing here is table function has some optional parameters. For now, i am running it without any optional parameter and further once we discuss its optional parameter we will be including them as selection criteria in this table function query.

SELECT * FROM TABLE(QSYS2.JOB_INFO())

This gives me the the information of jobs.

JOB_NAME                      JOB_N00001  JOB_USER    JOB_NUMBER  JOB_I00001    JOB_STATUS  JOB_TYPE  JOB_T00001 
279383/EASYCLASS/QPAD024843   QPAD024843  EASYCLASS     279383    YES             ACTIVE      INT     INTERACTIVE

JOB_S00001  JOB_DATE    JOB_D00001  JOB_D00002  JOB_A00001       SUBMI00001                    SUBMI00002  SUBMI00003  SERVE00001
QINTER      2024-02-02  QGPL        EASYCLASS   -                -                             -           -           -         

JOB_E00001           JOB_S00002           JOB_A00002           JOB_E00002           JOB_E00003  COMPL00001  JOB_E00004
2024-02-02-02.48.59  -                    2024-02-02-02.48.59  -                          30     -          -         

 JOB_Q00001  JOB_Q00002  JOB_Q00003  JOB_Q00004  JOB_Q00005           JOB_M00001  JOB_M00002  ALLOW00001     PEAK_00001  
 -           -           -                   -   -                            -    -             NO                  15  
 
 DEFAU00001      MAXIM00001      MAXIM00002      TIME_SLICE   JOB_S00003  ROUTI00001
        30                -               -          2,000    00000000   QCMDI     
        
 CCSID   CHARA00001  SORT_00001  SORT_00002  LANGU00001  COUNTRY_ID  DATE_00001  DATE_00002  TIME_00001  DECIM00001  TIME_00002
   273   *JOBCCSID   -           *HEX           ENU          US         *MDY         /           :         J         Q0000UTC  
   
 MESSA00001  MESSA00002  MESSA00003  LOG_C00001  STATU00001  INQUI00001  BREAK00001  JOB_L00001  JOB_L00002  OUTPU00001  OUTPU00002
        4           0     *SECLVL       *YES      *NORMAL     *RQD        *NORMAL    *JOBLOGSVR     NO              5    QGPL      
        
OUTPU00003  SPOOL00001  PRINT00001  PRINT00002  PRINT_TEXT                      DEVIC00001  DEVIC00002     DDM_C00001  MODE_NAME
EASYCLASS    *DETACH    PRT01        *PRTHDR    -=* http://pub400.com *=-       QPAD024843  *ENDJOB          *KEEP     -        

 MODE_NAME  UNIT_00001                INTER00001       
 -          -                          ù * *c ¿*´C{*¢*

Optional paramaters to JOB_INFO() table function

There are 5 optional parameters to the JOB_INFO() table function which are described as follows:

  1. Job_Status_Filter
  2. Job_Type_Filter
  3. Job_Subsystem_Filter
  4. Job_User_Filter
  5. _Submitter_Filter
Parameter to Job_Info() Description Possible Values Default Value
Job_Status_Filter Status of the job *ALL, *ACTIVE, *JOBQ, *OUTQ *ALL
Job_Type_Filter Type of the job *ALL, *BATCH, *INTERACT *ALL
Job_Subsystem_Filter Subsystem *ALL, Subsystem Name *ALL
Job_User_Filter User *ALL, *USER, UserName, USER(special register) *USER
Job_Submitter_Filter Type of submitted job *ALL, *JOB(Jobs submitted from this job), *USER(Jobs submitted from this user profile), *WRKSTN(jobs submitted from this workstation) *ALL

Note: If this parameter is not specified on a table function SQL query in where clause then by default a value of *ALL is used. Important info: Only one of these filter criteria(Job_Subsystem_Filter and Job_Submitter_Filter) can have value other than the '*ALL' value. For Job_Submitter_Filter, if the value specified is other than *ALL than we must specify *ALL for Job_User_Filter criteria in the Job_Info table function(). These are some types of restrictions to this table function.

Columns returned in Job_Info() table function

here, i am listing only the names of those columns that were listed in a table returned from this Job_info() table function and all are Nullable columns.

  • JOB_NAME: Varchar(28)/qualified job name.
  • JOB_NAME_SHORT: Varchar(10)/name of the job.
  • JOB_USER: Varchar(10)/user profile that started the job.
  • JOB_NUMBER: Varchar(6)/job number of the job.
  • JOB_INFORMATION: Varchar(12)/returned value NO/YES, tells if job information avialble for this job. NO means job not accessible and in this case all the returned columns from this table function will be NULL value other thane JOB_NAME column.
  • JOB_STATUS: Varchar(6)/status of the job(jobq, active, outq).
  • JOB_TYPE: Varchar(3)/type of the job(ASJ(autostart), BCH(batch), BCI(batch immediate), EVK(started by a procedure start request), INT(interactive), M36(advanced 36 server job), MRT(multiple requester terminal), PDJ(print driver job), PJ(prestart job), RDR(spool reader), SBS(subsystem monitor), SYS(system), WTR(spool writer)).
  • JOB_TYPE_ENHANCED: Varchar(28)/the combined job type and job subtype values(Alternate_Spool_User(batch-alternate spool user), Autostart(autostart job), Batch(batch job), Batch_Immediate(batch immediate job), BATCH_MRT(batch-System/36 MRT jobs), Comm_Procedure_Start_Request(communication job- procedure start request job), Interactive(interactive job), Interactive_Group(interactive job-part of group), Interactive_Sysreq(interactive job- part of system request pair), Interactive_Sysreq_And_Group(interactive job-part of system request pair and group), Prestart(prestart job), Prestart_Batch(prestart batch job), Prestart_Comm(prestart communications job), Reader(reader job), Subsystem(subsyetm job, System(system jobs), Writer(writer jobs-spool and print writers)).
  • JOB_SUBSYSTEM: Varchar(10)/name of the subsystem for the job. It contains the NULL value if the Job_Type is 'SYS', Job_Status is 'JOBQ' or 'OUTQ' or if the job has no subsystem.
  • JOB_DATE: Varchar(10)/the date assigned to job in *ISO format.It can contain special value as well like SYSVAL means system data and if Job_Status is OUTQ then it contains NULL value.
  • JOB_DESCRIPTION_LIBRARY: Varchar(10)/name of library containing JOBD.
  • JOB_DESCRIPTION: Varchar(10)/name of the JOBD used for this job.
  • JOB_ACCOUNTING_CODE: Varchar(15)/an identifier assigned to the job by the system to collect what resources job has used when the job accounting is active.
  • SUBMITTER_JOB_NAME: Varchar(28)/qualified job name of the submitter's job.
  • SUBMITTER_MESSAGE_QUEUE_LIBRARY: Varchar(10)/name of the library containing the message queue.
  • SUBMITTER_MESSAGE_QUEUE: Varchar(10)/name of the message queue where the system sends a completion message when the batch jobs end.
  • SERVER_TYPE: Varchar(30)/Type of server represented by the job. All IBM supplied servers have their server type begin with QIBM_. Some names of server type like QIBM_OS400_QZBS_SVR_DATABASE(Database server), QIBM_DLFM(Datalink File Manager, QIBM_DHCP(Dynamic Host Configuration Protocol (DHCP) server), QIBM_DNS(Domain Name System (DNS) server), QIBM_FTP(File Transfer Protocol (FTP) server), QIBM_HTTP_xxxxx (where xxxxx is the name of the server instance) (HTTP server), QIBM_TELNET_SERVER(TELNET Server) etc
  • JOB_ENTERED_SYSTEM_TIME: TIMESTAMP(0)/the timestamp when the job was placed on the system.
  • JOB_SCHEDULED_TIME: TIMESTAMP(0)/the timestamp when the job is scheduled to become active. It contains NULL if the job is not a scheduled job.
  • JOB_ACTIVE_TIME: TIMESTAMP(0)/the timestamp the job start to execute on the system. Conatins NULL if the job is not active.
  • JOB_END_TIME: TIMESTAMP(0)/the timestamp the job completed execution on the system. Conatins NULL if the job has not ended.
  • JOB_END_SEVERITY: SMALLINT/the message severity level of escape messages that can cause a batch job to end.
  • COMPLETION_STATUS: VARCHAR(8)/the completion status of the job(Abnormal/Normal), contains NULL value if job is not completed.
  • JOB_END_REASON: VARCHAR(60)/the most recent action that caused job to end.(Job ended due to a device error, Job ended due to a signal, Job ended due to a unhandled error, Job ended due to CPU limit being exceeded, Job ended due to disconnect time interval being exceeded, Job ended due to inactivity time interval being exceeded, Job ended due to the message severity level being exceeded, Job ended due to the storage limit being exceeded, Job ended while it was still on a job queue, Job ended abnormally, Job ending immediately, Job ending in normal manner, Job ending normally after a controlled end was requested, System ended abnormally), contains NULL value if job not ended.
  • JOB_QUEUE_LIBRARY: Varchar(10)/name of the library containing the job queue. contains the NULL if the JOB_STATUS is OUTQ or if the job is not on a jobq and the job is not a batch job that was started from a jobq.
  • JOB_QUEUE_NAME: Varchar(10)/name of the jobq queue that the job is currently on. contains the NULL if the JOB_STATUS is OUTQ or if the job is not on a jobq and the job is not a batch job that was started from a jobq.
  • JOB_QUEUE_STATUS: Varchar(9)/the status of this job on jobq(Held(the job is being held on the jobq), RELEASED(the job is ready to be selected), SCHEDULED(the job will run as scheduled)). contains NULL if the job is not on a jobq .
  • JOB_QUEUE_PRIORITY: SMALLINT/The scheduling priority of the job compared to other jobs on the same job queue. The highest priority is 0 and the lowest is 9. contains NULL value is Job_Status is not JOBQ.
  • JOB_QUEUE_TIME: TIMESTAMP(0)/The timestamp when the job was put on the jobq.
  • JOB_MESSAGE_QUEUE_MAXIMUM_SIZE: SMALLINT/The max size in MB that the job msgq can become and the range is 2MB to 64MB.
  • JOB_MESSAGE_QUEUE_FULL_ACTION: VARCHAR(8)/The action to take when the message queue is full.(*NOWRAP(do not wrap, this cause job to end), *PRTWRAP(wrap the msgq), *WRAP(wrap to beiginning to start filling again))
  • ALLOW_MULTIPLE_THREADS: VARCHAR(3)/indicates whether this job allows multiple user threads(NO, YES).
  • PEAK_TEMPORARY_STORAGE: INTEGER/the max amount of Auxiliary storage (in MB) the job has used.
  • DEFAULT_WAIT: INTEGER/the default max time in seconds that a thread in job wait for a system instruction.
  • MAXIMUM_PROCESSING_TIME_ALLOWED: INTEGER/the max processing unit time in Milliseconds that the job can use.
  • MAXIMUM_TEMPORARY_STORAGE_ALLOWED: INTEGER/the max amount of ausxiliary storage in MB that the job can use.
  • TIME_SLICE: INTEGER/the max amount of CPU time in Milliseconds provided to each thread in this job.
  • JOB_SWITCHES: CHAR(8)
  • ROUTING_DATA: VARCHAR(80)
  • CCSID: INTEGER/ccsid used for this job.
  • CHARACTER_IDENTIFIER_CONTROL: VARCHAR(9).
  • SORT_SEQUENCE_LIBRARY: VARCHAR(10)/name of the library that contain the sort sequence table.
  • SORT_SEQUENCE_NAME: VARCHAR(10)/name of the sort sequence table associated with this job.
  • LANGUAGE_ID: VARCHAR(3).
  • COUNTRY_ID: CHAR(2).
  • DATE_FORMAT: CHAR(4)/(*DMY, *JUL(Julian format - year and day), *MDY, *YMD).
  • DATE_SEPARATOR: CHAR(1).
  • TIME_SEPARATOR: CHAR(1).
  • DECIMAL_FORMAT: VARCHAR(6)/(*BLANK(use period for decimal point, comma for 3-digit grouping character, zero suppresses to the left of the decimal point), J(Use a comma for decimal point, period for 3 -digit grouping character, zero suppression character is in 2nd position rather than in 1st position to the left of the decimal notation), I(use comma for decimal point, period for 3-digit grouping character, zero suppresses to the left of the decimal point)).
  • TIME_ZONE_DESCRIPTION_NAME: VARCHAR(10)/the name of the time zone descrption that is used to calculate local job time.
  • MESSAGE_LOGGING_LEVEL: SMALLINT/the type of information that is logged(0(no message logged), 1(all messages sent to job external msgq with severity greater or equal to message log severity. means job start, job end and job completion status), 2(level 1 information and high level message sent to PGM msgq of the program), 3(level 1 and level 2 information and commands run by CL prorgam), 4(all request message and message with a severity greater or equal to message log severity, commands run by CL program)).
  • MESSAGE_LOGGING_SEVERITY: SMALLINT/The values range from 0 through 99.
  • MESSAGE_LOGGING_TEXT: VARCHAR(7)/the level of message text that is written in the job log.(*MSG(only the message text is written to the job log), *NOLIST(if the job ends normally, no job log is produced and joblog is produced only when job ended abnormally with job end code 20 or higher and the message text and help both logged in the job log), *SECLVL(both the message text and help(cause and recovery) of the error message are written to the job log)
  • LOG_CL_PROGRAM_COMMANDS: VARCHAR(4)/(*NO,*YES).
  • STATUS_MESSAGE: VARCHAR(7)/(*NONE,*NORMAL).
  • INQUIRY_MESSAGE_REPLY: VARCHAR(8)/specify how the job answers inquiry messages(*RQD(the job requires an answer for any enquiry messages that occur while the job is running), *DFT(the system uses the default message reply to answer any inquiry messages issued while this job is running), *SYSRPL(the system reply list is checked to see if there is an entry for an enquiry message issued while this job was running)).
  • BREAK_MESSAGE: VARCHAR(7)/(*HOLD,*NORMAL, *NOTIFY).
  • BREAK_MESSAGE: VARCHAR(10)/specifies how the job log will be produced after the job completes.(*JOBEND(the joblog will be produced by the job itself), *JOBLOGSVR(the joblog will be produced by a job log server), *PND(Job log will not be produced)).
  • JOB_LOG_PENDING: VARCHAR(3)/(NO, YES).
  • OUTPUT_QUEUE_PRIORITY: SMALLINT/the output priority for spooled output files that this job produced. 0 is highest and 9 is lowest priority.
  • OUTPUT_QUEUE_LIBRARY: VARCHAR(10)/the name of the library that contains the default output queue.
  • OUTPUT_QUEUE_NAME: VARCHAR(10)/the name of the default output queue that is used for spooled output produced by this job.
  • SPOOLED_FILE_ACTION: VARCHAR(7)/(*DETACH, *KEEP)
  • PRINTER_DEVICE_NAME: VARCHAR(10)
  • PRINT_KEY_FORMAT: VARCHAR(7)/specified whether border and header information is provided when the Print key is pressed(*NONE, *PRTBDR, *PRTHDR, *PRTALL).
  • PRINT_TEXT: VARCHAR(30)/the line of text that is printed at the bottom of each page for the job.
  • DEVICE_NAME: VARCHAR(10)
  • DEVICE_RECOVERY_ACTION: VARCHAR(13)/the action taken for interactive jobs when an I/O error occurs(*DSCENDRQS, *DSCMSG, *ENDJOB, *ENDJOBNOLIST, *MSG) .
  • DDM_CONVERSATION: VARCHAR(5)/(*DROP, *KEEP)
  • MODE_NAME: VARCHAR(8)/(*BLANK)
  • UNIT_OF_WORK_ID: CHAR(24)/The unit of work ID is used to track jobs across multiple systems.
  • INTERNAL_JOB_ID: BINARY(16)/The internal job identifier..

    • CL commands and their equivalent Job_Info() SQL Queries

    CL Command Job_Info() SQL query
    WRKSBMJOB SBMFROM(*USER) SELECT * FROM TABLE(QSYS2.JOB_INFO( JOB_SUBMITTER_FILTER => '*USER', JOB_USER_FILTER => '*ALL' ))
    WRKSBSJOB SBS(QBATCH) USER(*ALL) SELECT * FROM TABLE(QSYS2.JOB_INFO( JOB_SUBSYSTEM_FILTER => 'QBATCH', JOB_USER_FILTER => '*ALL' ))
    WRKUSRJOB USER(EASYCLASS) STATUS(*ACTIVE) JOBTYPE(*ALL) SELECT * FROM TABLE(QSYS2.JOB_INFO( JOB_USER_FILTER => 'EASYCLASS', JOB_STATUS_FILTER => '*ACTIVE' )) WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR')
    WRKUSRJOB USER(*) STATUS(*ALL) JOBTYPE(*INTERACT) SELECT * FROM TABLE(QSYS2.JOB_INFO( JOB_TYPE_FILTER => '*INTERACT' )) WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR')
    WRKUSRJOB USER(*) STATUS(*ALL) JOBTYPE(*ALL) SELECT * FROM TABLE(QSYS2.JOB_INFO( )) WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR')
    WRKUSRJOB USER(*) STATUS(*OUTQ) JOBTYPE(*ALL) SELECT * FROM TABLE(QSYS2.JOB_INFO( JOB_STATUS_FILTER => '*OUTQ' )) WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR')
    WRKUSRJOB USER(*ALL) STATUS(*JOBQ) JOBTYPE(*BATCH) SELECT * FROM TABLE(QSYS2.JOB_INFO( JOB_USER_FILTER => '*ALL', JOB_STATUS_FILTER => *JOBQ', JOB_TYPE_FILTER =>'*BATCH' )) WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR')

    We can see in where clause we have JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR') which is set by IBM to ignore these job types when fetching this data using job_info() table function where SBS denotes Subsystem monitors, SYS denotes System, RDR denotes spool readers and WTR denotes spool writers

    Examples of using Job_Info() table function

    How to find all the interactive jobs on the IBM i system?

    SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_TYPE_FILTER => '*INTERACT'))

    How to find all the batch jobs on the IBM i system?

    SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_TYPE_FILTER => '*BATCH'))

    How to find jobs submitted by the user EASYCLASS that have not been started?

    SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER   => 'EASYCLASS',
                                   JOB_STATUS_FILTER => '*JOBQ'))

    How to find jobs submitted by the user EASYCLASS that are active?

    SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER   => 'EASYCLASS',
                                   JOB_STATUS_FILTER => '*ACTIVE'))

    How to find jobs submitted by the user EASYCLASS that are ended?

    SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER   => 'EASYCLASS',
                                   JOB_STATUS_FILTER => '*OUTQ'))

    Post a Comment

    © ‧ AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain