OPEN_FILES table function

The OPEN_FILES table function returns a list of files (*FILE objects) that are open in all threads for a job.

This information is similar to what can be accessed through the Display Job (DSPJOB) CL command and by the List Open Files (QDMLOPNF) API.

Authorization: None required to see information for jobs where the caller's user profile is the same as the job user identity of the job for which the information is being returned. To see information for other jobs, the caller must have *JOBCTL special authority.

The schema is QSYS2.

job-name: A character or graphic string expression that identifies the qualified name of a job. The special value of '*' indicates the current job.

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

Table 1. OPEN_FILES table function
Column Name	Data Type	Description
LIBRARY_NAME	VARCHAR(10)	The name of the library that contains the open file. Contains the null value if the file is an inline data file.
FILE_NAME	VARCHAR(10)	The name of the file that is open. For an unnamed inline data file, contains the value QINLINE.
FILE_TYPE	VARCHAR(7)	The type of file that is open. BSCF Binary Synchronous Communications (BSC) file CMNF Communications file DDMF Distributed Data Management file DKTF Diskette file (spooled and non-spooled) DSPF Display file ICFF Intersystem Communications Function file LF Logical file MXDF Mixed file PF Physical file PRTF Printer file (spooled and non-spooled) SAVF Save file TAPF Tape file *INLINE Inline data file
MEMBER_NAME	VARCHAR(10)	If FILE_TYPE is physical (PF) or logical (LF), the name of the database member. If multiple member processing is being performed, the value is *ALL. Contains the null value for a DDM file, an inline data file, and a device file.
DEVICE_NAME	VARCHAR(10)	The name of the last program device used for an I/O operation if FILE_TYPE is a device file (BSCF, CMNF, DKTF, DSPF, ICFF, MXDF, PRTF, SAVF, or TAPF). If the file is a spooled file, the value is *SPOOL. Contains the null value for a device file when no I/O operation has been performed, a database physical or logical file, a DDM file, and an inline data file.
RECORD_FORMAT	VARCHAR(10)	The name of the last record format that was used for an I/O operation to the file. Contains the null value if no record format name was used or no I/O operations have been performed.
ACTIVATION_GROUP_NAME	VARCHAR(10)	The name of the activation group to which the open file is scoped. DFTACTGRP The file is scoped to the default activation group. NEW The file is scoped to a *NEW activation group. Contains the null value for a file scoped to the job, not a specific activation group.
ACTIVATION_GROUP_NUMBER	INTEGER	The number of the activation group to which the open file is scoped. Contains the null value for a file scoped to the job, not a specific activation group.
THREAD_ID	BIGINT	The thread handle assigned by the system which identifies the thread in which the file was opened.
OPEN_OPTION	VARCHAR(6)	The type of open operation that was performed. ALL The file was opened for all operations (input, output, update, and delete). INPUT The file was opened for input operations only. OUTPUT The file was opened for output operations only.
SHARED_OPENS	BIGINT	The number of times the file was opened for shared processing. Contains the null value for open operations that are not shared.
WRITE_COUNT	BIGINT	The number of successful write operations. If record blocking is not in effect for the file, this is the number of records. If record blocking is in effect for the file, this is the number of record blocks.
READ_COUNT	BIGINT	Number of successful read operations. If record blocking is not in effect for the file, this is the number of records. If record blocking is in effect for the file, this is the number of record blocks.
WRITE_READ_COUNT	BIGINT	The number of successful write/read operations.
OTHER_IO_COUNT	BIGINT	Number of successful I/O operations of the following types: update delete change end-of-data force end-of-data force end-of-volume release record lock acquire or release program device
RELATIVE_RECORD_NUMBER	BIGINT	Relative record number of the last record referred to by an I/O or open operation for database files. Contains the null value for nondatabase files and database files on which no I/O operations have been performed.
IASP_NAME	VARCHAR(10)	The auxiliary storage pool (ASP) device name where the library is stored. If the library is in the system ASP or one of the basic user ASPs, contains *SYSBAS. Contains the null value if the file is an inline data file or if the name of the ASP device cannot be determined.
IASP_NUMBER	INTEGER	The number of the auxiliary storage pool (ASP) from which the system allocates storage for the library. 1 indicates the system ASP. Contains the null value if the file is an inline data file or if the number of the ASP device cannot be determined.

Example

List all the database files that are open for job 429467/QUSER/QZDASOINIT.

SELECT * FROM TABLE(QSYS2.OPEN_FILES('429467/QUSER/QZDASOINIT'))
  WHERE FILE_TYPE IN ('PF', 'LF');