ADMIN_JOB_FETCH stored procedure
The SYSPROC.ADMIN_JOB_FETCH stored procedure retrieves SYSOUT from JES spool and returns the SYSOUT.
Environment
The load module for ADMIN_JOB_FETCH, DSNADMJF, must reside in an APF-authorized library. ADMIN_JOB_FETCH runs in a WLM-established stored procedure address space, and all libraries in this WLM procedure STEPLIB DD concatenation must be APF-authorized.
The load module for ADMIN_JOB_FETCH, DSNADMJF, must be program controlled if the BPX.DAEMON.HFSCTL FACILITY class profile has not been set up. For information on how to define DSNADMJF to program control, see installation job DSNTIJRA.
Authorization
To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges on each package that the stored procedure uses:
- The EXECUTE privilege on the package for DSNADMJF
- Ownership of the package
- PACKADM authority for the package collection
- SYSADM authority
Syntax
The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:
Option descriptions
- user-ID
- Specifies the user ID under which SYSOUT is retrieved.
If user-ID is NULL, password must also be NULL. If the user-ID and password values are NULL, the login process uses the primary authorization ID of the process.
You can specify NULL for this parameter in the following circumstances:
- The authorization ID that is associated with the stored procedure address space has daemon authority.
- The authorization ID that is associated with the stored procedure address space does not have daemon authority but is authorized to the BPX.SRV.userid SURROGAT class profile, where 'userid' is the authorization ID of the stored procedure. For more information about how the RACF® security administrator can authorize the authorization ID that is associated with the stored procedure address space to a SURROGAT class profile, see Defining servers to process users without passwords or password phrases.
Daemon authority is given to any superuser that is permitted to the BPX.DAEMON FACILITY class profile. If the BPX.DAEMON FACILITY class profile is not defined, all superusers have daemon authority.
This is an input parameter of type VARCHAR(128).
- password
- Specifies the password associated with the input parameter user-ID.
The value of password is passed to the stored procedure as part of payload, and is not encrypted. It is not stored in dynamic cache when parameter markers are used.
If password is NULL, user-ID must also be NULL. If the user-ID and password values are NULL, the login process uses the primary authorization ID of the process.
You can specify NULL for this parameter in the following circumstances:
- The authorization ID that is associated with the stored procedure address space has daemon authority.
- The authorization ID that is associated with the stored procedure address space does not have daemon authority but is authorized to the BPX.SRV.userid SURROGAT class profile, where userid is the authorization ID of the stored procedure.
This is an input parameter of type VARCHAR(100).
- job-ID
- Specifies the JES2 or JES3 job ID whose SYSOUT data sets are to be retrieved.
This is an input parameter of type CHAR(8) and cannot be null.
- return-code
- Provides the return code from the stored procedure. Possible values are:
- 0
- The call completed successfully.
- 12
- The call did not complete successfully. The message output parameter contains messages describing the error.
This is an output parameter of type INTEGER.
- message
- Contains messages describing the error encountered by the stored procedure. If no error
occurred, then no message is returned.
The first messages in this area are generated by the stored procedure. Messages that are generated by Db2 might follow the first messages.
This is an output parameter of type VARCHAR(1331).
Example
The following C language sample shows how to invoke ADMIN_JOB_FETCH:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/******************** DB2 SQL Communication Area ********************/
EXEC SQL INCLUDE SQLCA;
int main( int argc, char *argv[] ) /* Argument count and list */
{
/****************** DB2 Host Variables ****************************/
EXEC SQL BEGIN DECLARE SECTION;
/* SYSPROC.ADMIN_JOB_FETCH parameters */
char userid[129]; /* User ID */
short int ind_userid; /* Indicator variable */
char password[101]; /* Password */
short int ind_password; /* Indicator variable */
char jobid[9]; /* Job ID */
short int ind_jobid; /* Indicator variable */
long int retcd; /* Return code */
short int ind_retcd; /* Indicator variable */
char errmsg[1332]; /* Error message */
short int ind_errmsg; /* Indicator variable */
/* Result set locators */
volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc1;
/* Result set row */
long int rownum; /* Sequence number of the */
/* table row */
char text[4097]; /* A row in SYSOUT data set */
EXEC SQL END DECLARE SECTION;
/******************************************************************/
/* Assign values to input parameters to fetch the SYSOUT of a job */
/* Set the indicator variables to 0 for non-null input parameters */
/******************************************************************/
strcpy(userid, "USRT001");
ind_userid = 0;
strcpy(password, "N1CETEST");
ind_password = 0;
strcpy(jobid, "JOB00100");
ind_jobid = 0;
/******************************************************************/
/* Call stored procedure SYSPROC.ADMIN_JOB_FETCH */
/******************************************************************/
EXEC SQL CALL SYSPROC.ADMIN_JOB_FETCH
(:userid :ind_userid,
:password :ind_password,
:jobid :ind_jobid,
:retcd :ind_retcd,
:errmsg :ind_errmsg);
/******************************************************************/
/* Retrieve result set when the SQLCODE from the call is +446, */
/* which indicates that result sets were returned */
/******************************************************************/
if (SQLCODE == +466) /* Result sets were returned */
{
/* Establish a link between the result set and its locator */
EXEC SQL ASSOCIATE LOCATORS (:rs_loc1)
WITH PROCEDURE SYSPROC.ADMIN_JOB_FETCH;
/* Associate a cursor with the result set */
EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :rs_loc1;
/* Perform fetches using C1 to retrieve all rows from the */
/* result set */
EXEC SQL FETCH C1 INTO :rownum, :text;
while(SQLCODE==0)
{
EXEC SQL FETCH C1 INTO :rownum, :text;
}
EXEC SQL CLOSE C1;
}
return(retcd);
}
Output
This stored procedure returns the following output parameters, which are described in Option descriptions:
- return-code
- message
In addition to the preceding output, the stored procedure returns one result set that contains the data from the JES-managed SYSOUT data set that belong to the job ID specified in the input parameter job-ID.
The following table shows the format of the result set returned in the created global temporary table SYSIBM.JES_SYSOUT:
| Column name | Data type | Contents |
|---|---|---|
| ROWNUM | INTEGER | Sequence number of the table row |
| TEXT | VARCHAR(4096) | A record in the SYSOUT data set |
