Start of change

SPOOLED_FILE_INFO table function

The SPOOLED_FILE_INFO table function returns a list of spooled files on the system.

This information is similar to what is returned by the Work with Spooled Files (WRKSPLF) CL command and the Open List of Spooled Files (QGYOLSPL) API.

Authorization: The caller must have:
  • *EXECUTE authority to the library containing the output queue, and
  • *USE authority to the output queue containing the spooled file.
Read syntax diagramSkip visual syntax diagramSPOOLED_FILE_INFO( USER_NAME => user-name,STARTING_TIMESTAMP => starting-timestamp,ENDING_TIMESTAMP => ending-timestamp,STATUS => status,JOB_NAME => job-name,OUTPUT_QUEUE => output-queue,USER_DATA => user-data,FORM_TYPE => form-type,SYSTEM_NAME => system-name)

The schema is QSYS2.

Filtering on multiple values of user-name, output-queue, or user-data is slower.

Additionally, filtering on status is slower.

user-name
A character string containing user profile names to filter on. The list can contain up to 20 values separated by blanks.
Can be one of the following special values:
*ALL
The list is not filtered by user profile.
*CURRENT
Spooled files owned by the current user profile are included in the list. This is the default.
starting-timestamp
A timestamp value for the earliest spooled file to return. If the parameter is omitted or the null value, all spooled files with a create timestamp less than or equal to ending-timestamp are returned.
ending-timestamp
A timestamp value for the latest spooled file to return. If the parameter is omitted or the null value, all spooled files with a create timestamp greater than or equal to starting-timestamp are returned.
status
A character string containing the spooled file status to filter on. The list can contain one or more status values separated by blanks.
*ALL
Spooled files are included in the list regardless of the current status. This is the default.
*CLOSED
The file has been completely processed by a program, but SCHEDULE(*JOBEND) was specified. The job that produced the spooled file has not finished.
*DEFERRED
This spooled file has been deferred from printing.
*FINISHED
This spooled file is no longer in the system. These spooled files are included in the list of spooled files only if the qualified job name is specified.
*HELD
The file has been held.
*MESSAGE
This file has a message that needs a reply or needs an action to be taken.
*OPEN
The file has not been completely processed and is not ready to be selected by a writer.
*PENDING
This file is pending (waiting) to be printed.
*PRINTER
The file has been completely sent to the printer, but the print complete status has not been sent back.
*READY
The file is available to be written to an output device by a writer.
*SAVED
The file has been written and then saved. This file remains saved until it is released.
*SENDING
This spooled file is being sent or has been sent to a remote system.
*WRITING
This file is currently being produced by the writer on an output device.
job-name
A character string containing qualified job name.
Can be one of the following special values:
*
The current job.
*ALL
All jobs matching the other criteria are returned. This is the default.
output-queue
A character string containing the name of the output queue to filter on. An output queue name must be in the format library-name/outq-name. The list can contain up to 20 values separated by blanks.
*LIBL or *CURLIB can be specified for the library name.
Instead of a list of qualified output queue names, the following special value can be used:
*ALL
Spooled files are included in the list regardless of the output queue. This is the default.
user-data
A character string containing the value of the user-specified data or file name for spooled files to filter on.
Can be the following special value:
*ALL
Spooled files are included in the list regardless of the value for user-specified data. This is the default.
form-type
A character string containing the form type value to filter on.
Can be one of the following special values:
*ALL
Spooled files are included in the list regardless of the value for form type. This is the default.
*STD
Only files that specify the standard form type are included in the list.
system-name
A character string containing the name of the system where the job that created the spooled file ran.
Can be one of the following special values:
*ALL
The list is not filtered based on job system name. This is the default.
*CURRENT
Only spooled files created on the current system are to be returned.

The result of the function is a table containing rows with the format shown in the following table. All columns are nullable.

Table 1. SPOOLED_FILE_INFO table function
Column Name Data Type Description
SPOOLED_FILE_NAME VARCHAR(10) The name of the spooled file.
SPOOLED_FILE_NUMBER INTEGER The number of the spooled file.
STATUS VARCHAR(15) The status of the file.
CLOSED
The file has been processed completely by a program, but SCHEDULE(*JOBEND) was specified. The job that produced the spooled file has not finished.
DEFERRED
Printing of the file has been deferred.
DELETED
This spooled file is no longer in the system. These spooled files are included in the list of spooled files only if the qualified job name is specified.
HELD
The file has been held.
MESSAGE WAITING
This file has a message which needs a reply or an action to be taken.
OPEN
The file has not been processed completely and is not ready to be selected by a writer.
PENDING
This file is pending (waiting) to be printed.
PRINTING
The file has been completely sent to the printer, but the print complete status has not been sent back.
READY
The file is available to be written.
SAVED
The file has been written and then saved. This file remains saved until it is released.
SENDING
This spooled file is being sent or has been sent to a remote system.
WRITING
This file currently is being produced by the writer on an output device.
OUTPUT_PRIORITY INTEGER The priority of the spooled file. The priority ranges from 1 (highest) to 9 (lowest).

Contains the null value when STATUS is DELETED.

CREATION_TIMESTAMP TIMESTAMP(0) The timestamp, based on local job time, when the file was opened.
USER_DATA VARCHAR(10) The user-specified data that describes the file.

Contains the null value if there is no user-specified data or STATUS is DELETED.

SIZE BIGINT The spooled file size in bytes. The size of the spooled file is the data stream size plus the spooled file's attributes, plus the "overhead" storage used to store the spooled file's data stream.

Contains the null value if the size is not available or STATUS is DELETED.

TOTAL_PAGES INTEGER The total number of pages or number of records for the spooled file.

Contains the null value if the total number of pages is not available or STATUS is DELETED.

COPIES INTEGER The number of copies remaining to print.

Contains the null value when STATUS is DELETED.

QUALIFIED_JOB_NAME VARCHAR(28) The qualified job name of the job that owns the spooled file.
JOB_NAME VARCHAR(10) The name of the job that owns the spooled file.
JOB_USER VARCHAR(10) The name of the user that owns the spooled file.
JOB_NUMBER VARCHAR(6) The number of the job that owns the spooled file.
FILE_AVAILABLE VARCHAR(8) The schedule of the spooled file.
*IMMED
The spooled file is schedule immediate. A spooling writer can process the spooled file immediately.
*FILEEND
The spooled file is schedule file end. A spooling writer cannot process the spooled file until it has been closed.
*JOBEND
The spooled file is schedule job end. A spooling writer cannot process the spooled file until the job of the spooled file has ended.
FORM_TYPE VARCHAR(10) Spooled file form type. The type of form to load in the printer to print this file.

Contains the null value when STATUS is DELETED.

OUTPUT_QUEUE_LIBRARY VARCHAR(10) The library where the output queue is located.

Contains the null value when STATUS is DELETED.

OUTPUT_QUEUE VARCHAR(10) The name of the output queue in which the spooled file is located.

Contains the null value when STATUS is DELETED.

ASP_NUMBER INTEGER The auxiliary storage pool in which the spooled file resides.

Contains the null value when STATUS is DELETED.

SYSTEM VARCHAR(8) The name of the system where the job that created the spooled file ran.
INTERNET_PRINT_PROTOCOL_JOB_ID INTEGER The IPP job identifier assigned by the system based on the output queue to which the file was added or moved. This value ranges from 1 to 2147483647 and is not guaranteed to be unique for a given output queue.

Contains the null value when STATUS is DELETED.

Example

  • List all the spooled files for the current job.
    SELECT * FROM TABLE(QSYS2.SPOOLED_FILE_INFO(JOB_NAME => '*'));
    
End of change