IBM Support

SE54741 - OSP-DB ADD QSYS2/GET_JOB_INFO UDTF

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 APAR (Authorized Program Analysis Report)

Abstract

OSP-DB ADD QSYS2/GET_JOB_INFO UDTF

Error Description

SQL applications need an SQL interface to extract JOB run      
information.                                                    

Problem Summary

SQL applications need an SQL interface to extract JOB run      
information.                                                    

Problem Conclusion

The QSYS2/GET_JOB_INFO() User Defined Table Function accepts an
input paramter which is the target job name.                    
The UDTF finds the job and returns a single row table to the    
caller.  The columns in the table provide detail about the job.
The input job name can be specified using the qualified or fully
qualified form.                                                
                                                               
Authorization requirements:                                    
The caller must have *JOBCTL user special authority or          
QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage capability.    
If the caller lacks one of those authorities, the UDTF          
invocation will fail with SQLCODE=-443 and SQLSTATE='42501'.    
                                                               
GET_JOB_INFO() table definition:                                
                                                               
V_JOB_STATUS CHAR(10) - the status of the target job.          
  Possible values include *ACTIVE, *OUTQ and *UNKNOWN.          
V_ACTIVE_JOB_STATUS CHAR(4) - The active status of the initial  
thread of the job.                                              
If the job is not active, NULL is returned.  If the job is      
active, the possible values are numerous, and include:          
CNDW  Waiting on handle-based condition.                        
LCKW  Waiting for a lock.                                      
RUN  Currently running in the pool activity level.              
                                                               
V_RUN_PRIORITY INTEGER - The priority at which the job is      
currently running, relative to other jobs on the system. The run
priority ranges from 0 (highest priority) to 99 (lowest        
priority).                                                      
V_SBS_NAME CHAR(10) - The qualified name of the subsystem in    
which an active job is running. The format of the qualified name
is a 10-character simple object name followed by a 10-character
library name. This value is only for jobs whose status is      
*ACTIVE. For jobs with status of *OUTQ or *JOBQ, the value for  
this field is blank.                                            
V_CPU_USED BIGINT - Processing unit time used - total for the  
job. The amount of processing unit time (in milliseconds) that  
the job used across all routing steps.                          
V_TEMP_STORAGE_USED_MB INTEGER - Temporary storage used in      
megabytes. The amount of auxiliary storage (in megabytes) that  
is currently allocated to this job. This is an unsigned        
BINARY(4) value.                                                
V_AUX_IO_REQUESTED BIGINT - The number of auxiliary I/O requests
performed by the job across all routing steps. This includes    
both database and nondatabase paging.                          
V_PAGE_FAULTS BIGINT - The number of times an active program    
referenced an address that is not in main storage during the    
current routing step of the specified job.                      
V_CLIENT_WRKSTNNAME CHAR(255) - The value of the SQL special    
register value or NULL.                                        
V_CLIENT_APPLNAME CHAR(255)   - The value of the SQL special    
register value or NULL.                                        
V_CLIENT_ACCTNG CHAR(255)     - The value of the SQL special    
register value or NULL.                                        
V_CLIENT_PROGRAMID CHAR(255)  - The value of the SQL special    
register value or NULL.                                        
V_CLIENT_USERID CHAR(255)     - The value of the SQL special    
register value or NULL.                                        
                                                               
Example usage:                                                  
                                                               
SELECT * FROM                                                  
TABLE(QSYS2.GET_JOB_INFO('690326/SCOTTF/QPADEV0003')) AS A      
or                                                              
SELECT * FROM TABLE(QSYS2.GET_JOB_INFO('QzdasoinitQuser        
690325')) AS A                                                  

Temporary Fix

Comments

Circumvention


PTFs Available

R710 SI49337 PTF Cover Letter   3298

Affected Modules

         
         

Affected Publications

Summary Information

Status............................................ CLOSED PER
HIPER........................................... No
Component.................................. 5770SS1DB
Failing Module.......................... RCHMGR
Reported Release................... R710
Duplicate Of..............................




System i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2.0"},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG15Q","label":"APARs - OS\/400 General"},"Component":"","ARM Category":[],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"V7R1M0;V7R2M0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 November 2013