Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL iQuery Documentation - UDF and UDTF
has no ratings.
Published: 17 Jun 2019
Revised: 21 Jun 2019 - 1742 days ago
Last viewed on: 22 Mar 2024 (744 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
  12. GET CPUCNT, CURLIB, ENV, SRLNBR, SYSNAME
  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.Job_Attr - UDTF and VIEW Published by: Bob Cozzi on 17 Jun 2019 view comments

Job Attributes

The iQuery.Job_Attr() 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.

UDF Return Type Description
JOB_ATTR TABLE Returns a one-row resultSet that contains most job attribute properties.

Parameters

table( iQuery.job_attr( fully-qualified JOB name or * for current job ))

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.

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_Number varchar(6) The 6-digit job number (as text)
Job_Date date The date for the job "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.
JobD_Name varchar(10) The job description used for the job
JobD_Lib varchar(10) The job description library used by the job.
Mode_Name varchar(8) The Job Mode.
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
LANGUAGE_ID varchar(3) The 3-character language attribute under which the job is running.
COUNTRY_ID varchar(2) The 2-character country code.
CCSID int The job CCSID
DFTCCSID int If the job CCSID = 65535 then this contains the fallback or default job CCSID to use.
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) The Job Number of the job that submitted "this" job.
JOB_SWITCHES varchar(8) The current UPSI Job Switch values
JOBLOG_OUTPUT varchar(10) The job log output option. The values can be *SYSVAL, *JOBLOGSVR, *JOBEND, *PND.
ALLOW_MULTIPLE_THREADS char(1) Indicates if the job support multithreaded processing. The value can be 0 or 1
ROUTING_DATA 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).

Example

select * from table( iQuery.job_attr()) myJob';

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

WRKACTJOB and QUSRJOBI API Comparison
Job Type Field 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

 JOB_ATTR VIEW

There is also a JOB_ATTR VIEW that you can query instead of using the JOB_ATTR() UDTF. When the current job attributes are desired, use the iQuery.JOB_ATTR view. It contains all the columns from the UDTF without the need to use the UDTF itself. Making querying the information a little easier.

There are a few column names that have been expande and System Column Names were added to create 10-character column alias names.

Column System
Colun
Type Description
JOB JOB varchar(28) The fully qualified job name whose attributes are returned.
Job_Status JOBSTATUS varchar(10) The job status (active, held, wait, etc.)
Job_Type JOBTYPE varchar(1) Interactive, Batch, Autostart etc. Job indicator. See table below for details.
Job_SubType JOBSUBTYPE varchar(1) The subtype for the job See table below for more information.
Job_Name JOBNAME varchar(10) The 10-character job name.
Job_User JOBUSER varchar(10) The User Profile of the job.
Job_Number JOBNBR varchar(6) The 6-digit job number (as text)
Job_Date JOBDATE date The date for the job "Job Date"
Date_Entered_System ENTDATE timeStamp The date/time when the job entered the system.
Date_Job_Active STARTDATE timeStamp The date/time when the job because active.
JobD_Name JOBDNAME varchar(10) The job description used for the job
JobD_Lib JOBDLIB varchar(10) The job description library used by the job.
Mode_Name JOBMODE varchar(8) The Job Mode.
LOGCLPGM LOGCLPGM varchar(10) The Log CL Program attribute (*YES or *NO)
BRKMSGHANDLING BRKMSGHDL varchar(10) The break message handling attribute. The values can be *NORMAL, *NOTIFY, *HOLD
STSMSGHANDLING STSMSGHDL varchar(10) The status message handling attribute. The value can be *USRPRF, *SYSVAL, *NONE, *NORMAL
DECFMT DECFMT char(1) Job numeric (decimal point) format. Returned values include: Blank, J, or I 
DATESEP DATESEP char(1) Job Date separator character.
DATEFMT DATEFMT char(4) Job Date format.
TIMESEP TIMESEP char(1) Time separator character.
TIME_ZONE TIMEZONE varchar(10) Time Zone property
LANGUAGE_ID LANGID varchar(3) The 3-character language attribute under which the job is running.
COUNTRY_ID CNTRYID varchar(2) The 2-character country code.
CCSID CCSID int The job CCSID
DFTCCSID DFTCCSID int If the job CCSID = 65535 then this contains the fallback or default job CCSID to use.
SBMJOB_NAME SBMJOB varchar(10) The Name component of the job that submitted "this" job.
SBMJOB_USER SBMJOBUSER varchar(10) The User Profile component of the job that submitted "this" job.
SBMJOB_NBR SBMJOBNBR varchar(6) The Job Number of the job that submitted "this" job.
JOB_SWITCHES SWITCHES varchar(8) The current UPSI Job Switch values
JOBLOG_OUTPUT JOBLOGOUT varchar(10) The job log output option. The values can be *SYSVAL, *JOBLOGSVR, *JOBEND, *PND.
ALLOW_MULTIPLE_THREADS MULTITHD char(1) Indicates if the job support multithreaded processing. The value can be 0 or 1
ROUTING_DATA RTGDATA varchar(80) The Routing Data for the job.

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

COMMENTS