Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
SQL iQuery Documentation - UDF and UDTF
has no ratings.
Published: 21 Jun 2019
Revised: 06 Sep 2019 - 1552 days ago
Last viewed on: 06 Dec 2023 (3730 views) 
  1. SQL iQuery - RUNiQRY CL Command
  2. CSV() - Read and Parse Comma Separated Values File
  3. CSV_VAL() Extract CSV Column Value
  4. CSV_XXXX() Extract CSV Column Value as xxxx
  5. CSV_COUNT - Retrieve Field Count
  6. ObjExist - Check Object Exits
  7. DTAARA - Data Area
  8. encode_URL Encode URL for the Web
  9. encode_TAG Encode HTML TAG Content
  10. FLDLIST - List the Fields of a Table
  11. FROMHEX - Fold 2-hex Chars into 1 Char
  13. MD5_HASH - UDF - Return MD5 Hash
  14. ifsStat - Get IFS File Attributes
  15. ifsExists - Check if IFS File Exists
  16. ifsFile - Query Contents of IFS Text File
  17. ifsDIR - List IFS Directory Entires
  18. DEPFILE - List Dependent Database Relations
  19. JOB - Get Job Name Component
  20. Job_ATTR - Job Attributes
  21. JOB_DATE Retrieve the Job Date
  22. JOBLOG - Write Message to Joblog
  23. Library List UDF and Procedures
  24. MBRLIST - Member List
  25. MCHINFO Machine Type and Model Number and OS version
  26. OSVER and OSVRM Get IBM i Version/Release
  27. PrintPDF - Print a PDF to a PDF Compatible Printer
  28. CPYTOPDF - Copy SPOOLED File to PDF file
  29. OBJ_LIST List Object in a Library
  30. LIB_LIST List Descriptions of Libraries on the Library List
  31. OBJ_EXPORTS List Exported Items from an Object
  32. OBJ_STRUCT Retrieve Objects Components
  33. RTVCMDD Retrieve Command Definition
  34. RTVJOBA - Retrieve Job Attributes
  35. RTVJOBD - Retrieve Job Description
  36. RTVLASTSPLF - Retrieve ID of Last SPOOLED File for this Job
  37. RTVxxx - Retrieve Various Objects Description Table Functions
  38. WATCHLIST - List Current Watches
  39. Polymorphic Qualified Name Syntax

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

iQuery.rtvjoba UDTF Published by: Bob Cozzi on 21 Jun 2019 view comments

Retrieve Job Attributes

The iQuery.RTVJOBA() UDTF returns a one row result that contains the attributes for the specified Job or the current job, if no job id is specified. The information returned is similar to that which is returned by the QUSRJOBI API for formats JOBI0300 and JOBI0400. Unlike the iQuery.JOB_ATTR() UDTF, this function is faster, and JOB_ATTR() actually calls RTVJOBA() to create its resultSet.


iQuery.rtvjoba( fully-qualified JOB name or * for current job, internal job ID binary(16) )

The Fully-Qualified Job Name parameter may contain a 3-part qualified job name (job number/user/jobname) whose attributes are to be returned. The optional * may be specified to indicate the curent job's attributes are to be returned. This is the default if no value is specified.

The optional Internal-Job ID parameter may be used instead of the qualified job name parameter. If specified the qualified job name parameter is ignored. This parameter accepts the unique 16-byte binary JOB ID to more quickly find the Job Attributes. Use this when you're doing something like a lateral join to the RTVJOBA UDTF using another view or something like the IBM QSYS2.ACTIVE_JOB_INFO UDTF.

Result Set Columns

Column Type Description
JOB varchar(28) The fully qualified job name whose attributes are returned.
Job_Status varchar(10) The job status (active, held, wait, etc.)
Job_Type varchar(1) Interactive, Batch, Autostart etc. Job indicator. See table below for details.
Job_SubType varchar(1) The subtype for the job See table below for more information.
Job_Name varchar(10) The 10-character job name.
Job_User varchar(10) The User Profile of the job.
Job_Nbr varchar(6) The 6-digit job number (as text)
JOB_DATE date The date under which the job is running "job date".
Date_Entered_System timeStamp The date/time when the job entered the system.
Date_Job_Active timeStamp The date/time when the job because active.
JOB_INTID binary(16) The internal job Identifier
JOBQ_NAME varchar(10) Job Queue from which the job was launched.
JOBQ_LIB varchar(10) Job Queue Library from which the job was launched.
JOBQ_STATUS varchar(10) The Job Status on the Job Queue.
JOBQ_PTY varchar(2) The Job Priority on the Job Queue.
OUTQ_NAME varchar(10) The Output Queue Name assigned to SPOOLED output from this job.
OUTQ_LIB varchar(10) The Output Queue Library name.
OUTQ_PTY varchar(2) The Priority of SPOOLED output from this job.
PRTDEV varchar(10) The Printer Device Name associated with this job.
JobD_Name varchar(10) The job description used for the job
JobD_Lib varchar(10) The job description library used by the job.
JOBLOG_OUTPUT varchar(10) The LOGOUTPUT option used when the joblog is created. The value can be *SYSVAL, *JOBLOGSVR, *JOBEND, *PND.
JOBLOG_PENDING char(1) The Joblog Pending attribute.
Mode_Name varchar(8) The Job Mode.
INQMSGRPY varchar(10) Inquiry Message Repy status
LOGCLPGM varchar(10) The Log CL Program attribute (*YES or *NO)
BRKMSGHDL varchar(10) The break message handling attribute. The values can be *NORMAL, *NOTIFY, *HOLD
STSMSGHDL varchar(10) The status message handling attribute. The value can be *USRPRF, *SYSVAL, *NONE, *NORMAL
DECFMT char(1) Job numeric (decimal point) format. Returned values include: Blank, J, or I 
DATESEP char(1) Job Date separator character.
DATEFMT char(4) Job Date format.
TIMESEP char(1) Time separator character.
TIME_ZONE varchar(10) Time Zone property
DayOfWeek varchar(4) The day of the week for the job. *MON, *TUE, etc.
LANGUAGE_ID varchar(3) The 3-character language attribute under which the job is running.
COUNTRY_ID varchar(2) The 2-character country code.
CHAR_ID_CTRL varchar(10) Character identifier control.
CCSID integer The job CCSID
DFTCCSID integer If the job CCSID = 65535 then this contains the fallback or default job CCSID to use.
JOB_GRPPRF varchar(10) Group Profile under which job is running.
JOB_CURUSRPRF varchar(10) Current User Profile under which job is running.
SBMJOB_NAME varchar(10) The Name component of the job that submitted "this" job.
SBMJOB_USER varchar(10) The User Profile component of the job that submitted "this" job.
SBMJOB_NBR varchar(6) Submitter's Job Number (the Job Number of the job that submitted "this" job)
SBMJOB_MSGQ varchar(10) Submitter's message queue name.
SBMJOB_MSGQ_LIB varchar(10) Submitter's message queue library.
JOB_SUBSYSTEM varchar(10) Subsystem name in which job is running.
JOB_SUBSYSTEM_LIBRARY varchar(10) Subsystem library in which job is running.
END_SEV integer Job end severity level (0 to 99)
LOG_LEVEL integer Log level (1, 2, 3, 4)
LOG_SEV integer Log level severity (0 to 99)
LOG_MSGTEXTLVL varchar(10) Log level Message Text option. *MSG, *SECLVL, or *NOLIST.
JOB_SWITCHES varchar(8) The current UPSI Job Switch values
MULTITHREAD char(1) Indicates if the job support multithreaded processing. The value can be 0 or 1
END_STATUS varchar(10) Status of job end condition.
EXIT_KEY char(1) Exit key used to end job (F3).
CANCEL_KEY char(1) Cancel key used to end job (F12).
DEVNAME varchar(10) Job device named
CLIENT_IP varchar(15) IP v4 of client device
RTGDATA varchar(80) The Routing Data for the job.

The following example returns the job attributes for the current job (the one running the SQL statement).


select * from table( iQuery.RTVJOBA() ) thisJob';

The Following is a table that identifies the Job Type and SubType columns returned by this UDTF.

If the Job Type Is Job_Type Job_Subtype
ASJ (Autostart) A blank
BCH (Batch) B blank
BCI (Batch immediate) B D
EVK (Started by a program start request) B E
INT (Interactive) I blank
M36 (System i™ Advanced 36® machine server) B F
MRT (Multiple requester terminal) B T
PJ (Prestart job) B J
PDJ (Print driver job) W P
RDR (Reader) R blank
SYS (System) S or X blank
SBS (Subsystem monitor) M blank
WTR (Writer) W blank
blank (Alternative user subtype--not an active job) B U


Return to midrangenews.com home page.
Sort Ascend | Descend