SYSFILES view
The SYSFILES view contains information about database files. Additional information is available in the QSYS2.SYSTABLES view.
The information returned is similar to the detail seen from the Display File Description (DSPFD) command and the Retrieve Database File Description (QDBRTVFD) API.
- *EXECUTE authority to the library containing the file, and
- *OBJOPR authority to the file.
The following table describes the columns in the view. The schema is QSYS2.
Column Name | System Column Name | Data Type | Description |
---|---|---|---|
SYSTEM_TABLE_SCHEMA | LIB_NAME | VARCHAR(10) | The library containing the file. |
SYSTEM_TABLE_NAME | FILE_NAME | VARCHAR(10) | The name of the file. |
TABLE_SCHEMA | TABSCHEMA | VARCHAR(128) | The SQL schema name of the library. |
TABLE_NAME | TABNAME | VARCHAR(128) | The SQL name of the file. |
IASP_NUMBER | IASPNUMBER | INTEGER | The independent auxiliary storage pool (IASP) number. |
TEXT_DESCRIPTION | TEXT | VARGRAPHIC(50) CCSID
1200 Nullable
|
The descriptive text for the file. Contains the null value if there is no descriptive text. |
NATIVE_TYPE | DDS_TYPE | VARCHAR(8) | Type of file.
|
FILE_TYPE | FILETYPE | VARCHAR(6) | Type of file.
|
SQL_OBJECT_TYPE | SQL_TYPE | VARCHAR(5) Nullable
|
SQL object type.
Contains the null value if this is not an SQL object. |
LAST_ALTERED_TIMESTAMP | ALTEREDTS | TIMESTAMP(0) | Timestamp when the file was last altered or created. |
FILE_LEVEL_ID | FILE_LVLID | CHAR(13) | File level identifier. This is the date of the file in CYYMMDDHHMMSS format. |
LEVEL_CHECK | LVLCHK | VARCHAR(3) | Record format level check (LVLCHK).
|
FILE_OWNER | OWNER | VARCHAR(10) Nullable
|
The owner of the object. Contains the null value if the object owner is not available. |
CREATE_PUBLIC_AUTHORITY | PUB_AUTH | VARCHAR(10) | The public authority that the file was created with
(AUT). This is not the current public authority for the file.
|
NUMBER_MEMBERS | MEMBERS | INTEGER | Number of members. |
MAXIMUM_ MEMBERS | MAXMBRS | INTEGER Nullable
|
Maximum members (MAXMBRS).
Contains the null value if no maximum is specified; 32767 is used (*NOMAX). |
MAXIMUM_RECORD_LENGTH | RECLENGTH | INTEGER | Maximum record length. This is the length of the record in the file's record format that contains the largest number of bytes. |
NUMBER_KEY_FIELDS | KEY_FLDS | INTEGER | Number of key fields for the file. |
MAXIMUM_KEY_LENGTH | KEY_LEN | INTEGER | Maximum key length for the file. |
TRIGGER_COUNT | TRIG_CNT | INTEGER | Number of triggers. |
CONSTRAINT_COUNT | CONSTR_CNT | INTEGER Nullable
|
Number of constraints for the file. Contains the null value if this is not a physical file. |
NUMBER_BASED_ON_FILES | BASE_FILES | INTEGER Nullable
|
Number of files directly referenced by a logical
file, view, or index. Contains the null value for physical files. |
BASED_ON_FILES | BASED_ON | CLOB(35K) CCSID 1208 Nullable
|
A list of files directly referenced by this logical
file, view, or index. This list is returned as an array within a JSON object. The array is
identified by BASED_ON_FILES. Each entry in the JSON array identifies one file. Each array entry can
contain information with the following keys:
Contains the null value for physical files. |
ALLOW_READ | ALWREAD | VARCHAR(3) | Allow read operation.
|
ALLOW_WRITE | ALWWRITE | VARCHAR(3) | Allow write operation.
|
ALLOW_UPDATE | ALWUPD | VARCHAR(3) | Allow update operation (ALWUPD).
|
ALLOW_DELETE | ALWDLT | VARCHAR(3) | Allow delete operation (ALWDLT).
|
MAXIMUM_FILE_WAIT_TIME | WAITFILE | INTEGER | Maximum file wait time (WAITFILE).
|
MAXIMUM_RECORD_WAIT_TIME | WAITRCD | INTEGER | Maximum record wait time (WAITRCD).
|
FORCE_WRITE_RATIO | FRCRATIO | INTEGER Nullable
|
Records to force a write (FRCRATIO).
Contains the null value is there is no force write ratio. |
SELECT_OMIT | SELECTOMIT | VARCHAR(3) | Select/omit setting.
|
PROGRAM_DESCRIBED | PGM_DESC | VARCHAR(3) | Program described file setting.
|
DISTRIBUTED | DIST_FILE | VARCHAR(3) | Distributed file setting.
|
FILE_VRM | FILE_VRM | CHAR(6) | File version, release, and modification level. VxRyMz, where x is the version, y the release, and z the modification level. This is the release where the file was created. |
EARLIEST_POSSIBLE_RELEASE | MINRLS | CHAR(6) Nullable
|
Earliest supported version, release, and
modification level. New database support used in the file will prevent the file from being saved to
a prior version, release, and modification level. The value is formatted as VxRyMz, where x is the
version, y the release, and z the modification level. Contains the null value if the value is prior to V2. |
ALLOW_NULL_KEYS | ALWNULLK | VARCHAR(3) Nullable
|
Allow null value key setting (ALWNULL).
Contains the null value if ACCESS_PATH_KEYED is NO. |
ALLOW_NULL_DATA | ALWNULLD | VARCHAR(3) | Allow null value data (ALWNULL).
|
PRIMARY_KEY | PRIKEY | VARCHAR(3) | Primary key (*PRIKEY).
|
UNIQUE_CONSTRAINT | UNQCST | VARCHAR(3) | Unique constraint.
|
VOLATILE | VOLATILE | VARCHAR(3) | SQL volatile table setting.
|
KEEP_IN_MEMORY | KEEPINMEM | VARCHAR(3) | The memory preference of the file.
|
MEDIA_PREFERENCE | UNIT | VARCHAR(4) | Preferred storage unit for the file (UNIT).
|
SOURCE_FILE_LIBRARY | SRCLIB | VARCHAR(10) Nullable
|
Library containing SOURCE_FILE. Contains the null value if a source file was not used. |
SOURCE_FILE | SRCFILE | VARCHAR(10) Nullable
|
Name of the source file containing the DDS used to
create the file. Contains the null value if a source file was not used. |
SOURCE_FILE_MEMBER | SRCMBR | VARCHAR(10) Nullable
|
Source file member name within SOURCE_FILE used to
create the file. Contains the null value if a source file was not used. |
ACCESS_PATH_KEYED | ACCESSPATH | VARCHAR(3) | Whether the file has a keyed sequence access path.
|
ACCESS_PATH_TYPE | AP_TYPE | VARCHAR(14) Nullable
|
Access path type.
Contains the null value if ACCESS_PATH_KEYED is NO. |
ACCESS_PATH_MAINTENANCE | MAINT | VARCHAR(6) Nullable
|
Access path maintenance (MAINT).
Contains the null value if ACCESS_PATH_KEYED is NO. |
ACCESS_PATH_SIZE | ACCPTHSIZ | VARCHAR(7) Nullable
|
Access path size (ACCPTHSIZ).
Contains the null value if ACCESS_PATH_KEYED is NO. |
LOGICAL_PAGE_SIZE | PAGESIZE | INTEGER Nullable
|
Access path page size.
Contains the null value if ACCESS_PATH_KEYED is NO. |
FORCE_KEYED_ACCESS_PATH | FRCACCPTH | VARCHAR(3) Nullable
|
Force keyed access path (FRCACCPTH).
Contains the null value if ACCESS_PATH_KEYED is NO. |
ACCESS_PATH_JOURNALED | JOURNALED | VARCHAR(3) Nullable
|
Access path journaled.
Contains the null value if ACCESS_PATH_KEYED is NO. |
ACCESS_PATH_RECOVERY | RECOVER | VARCHAR(7) Nullable
|
Access path recovery (RECOVER).
Contains the null value if ACCESS_PATH_KEYED is NO. |
SRTSEQ_IND | SRTSEQ_IND | INTEGER | Sort sequence table (SRTSEQ) indicators.
|
SORT_SEQUENCE_LIBRARY | SRTSEQ_LIB | VARCHAR(10) Nullable
|
The library containing the sort sequence table or
alternate collating sequence table. Can contain the special value *LIBL. Contains the null value if SRTSEQ_IND is 1 or if there is no library. |
SORT_SEQUENCE | SRTSEQ | VARCHAR(10) Nullable
|
The sort sequence table or alternate collating
sequence table associated with the file. Contains the null value if SRTSEQ_IND is 1. |
LANGUAGE_IDENTIFIER | LANGID | CHAR(3) Nullable
|
Language identifier (LANGID). Contains the null value if there is no language identifier. |
ROUNDING_MODE | DECFLTRND | VARCHAR(8) Nullable
|
Rounding mode to be used for decimal floating point
(DECFLOAT) calculations.
Contains the null value if the file has no decimal floating point fields or this is not an SQL view, SQL index with a derived key expression, SQL materialized query table, or logical file. |
DECFLOAT_WARNINGS | DECFLTWRN | VARCHAR(3) Nullable
|
Indicates whether warnings should be returned from
decimal floating point calculations.
Contains the null value if the file has no decimal floating point fields or this is not an SQL view, SQL index with a derived key expression, SQL materialized query table, or logical file. |
NUMBER_RECORD_FORMATS | FORMATS | INTEGER | Total number of record formats. |
FORMAT_LEVEL_ID | FMTLVLID | CHAR(13) Nullable
|
The record format level ID. Contains the null value if file has more than one format or if no value is available. |
FORMAT_NAME | FMT_NAME | VARCHAR(10) Nullable
|
The name of the record format. Contains the null value if file has more than one format. |
RECORD_LENGTH | RCD_LEN | INTEGER Nullable
|
The length of the record format. Contains the null value if file has more than one format. |
NUMBER_FIELDS | FIELDS | INTEGER Nullable
|
The number of fields in the record
format. Contains the null value if file has more than one format. |
COMMON_CCSID | CCSID | INTEGER Nullable
|
The CCSID used when all fields with a character,
open, and graphic data type use the same CCSID. Contains the null value if file has more than one format or if all character, open, and graphic fields do not use the same CCSID. |
CONTAINS_EXPLICIT_CCSID | EXPLICIT | VARCHAR(3) Nullable
|
Explicit CCSID setting.
Contains the null value if file has more than one format. |
CONTAINS_MULTIPLE_CCSIDS | MULTIPLE | VARCHAR(3) | Multiple CCSID setting.
|
CONTAINS_UNICODE | UNICODE | VARCHAR(3) Nullable
|
Format contains UTF-8, UTF-16, or UCS-2 fields.
Contains the null value if file has more than one format. |
CONTAINS_VARYING_LENGTH | VARLEN | VARCHAR(3) | File contains variable length fields (VARLEN).
|
CONTAINS_DATETIME | DATETIME | VARCHAR(3) | File contains date/time/timestamp fields.
|
CONTAINS_GRAPHIC | GRAPHIC | VARCHAR(3) | File contains graphic fields.
|
CONTAINS_LOB | LOB | VARCHAR(3) | File contains large object fields. These are the SQL
data types character large object (CLOB), double-byte character large object (DBCLOB), and binary
large object (BLOB).
|
CONTAINS_ROWID | ROWID | VARCHAR(3) | File contains ROWID fields.
|
CONTAINS_UDT | UDT | VARCHAR(3) | File contains user-defined type fields.
|
CONTAINS_DATALINK | DATALINK | VARCHAR(3) | File contains DataLink fields.
|
CONTAINS_DATALINK_
FILE_LINK_CONTROL |
DATALINKFL | VARCHAR(3) | File contains DataLink with FILE LINK CONTROL fields.
|
CONTAINS_NULL | NULLABLE | VARCHAR(3) Nullable
|
File contains null capable fields.
Contains the null value if file has more than one format. |
CONTAINS_DEFAULT | DFT | VARCHAR(3) Nullable
|
The physical file format contains fields with
explicit default values.
Contains the null value if file has more than one format. |
CONTAINS_IDENTITY | IDENTITY | VARCHAR(3) | File contains an identity column.
|
CONTAINS_ROW_CHANGE_
TIMESTAMP |
ROW_CHANGE | VARCHAR(3) | File contains a row change timestamp column.
|
CONTAINS_USER_DEFINED_
FUNCTION |
UDF | VARCHAR(3) | File contains a user-defined function.
|
Values for the following columns are returned when NATIVE_TYPE is PHYSICAL. Otherwise, the columns will contain the null value. | |||
ALLOCATE_STORAGE | ALLOCATE | VARCHAR(3) Nullable
|
The allocate storage setting (ALLOCATE).
|
CONTIGUOUS_STORAGE | CONTIG | VARCHAR(3) Nullable
|
The contiguous storage setting (CONTIG).
Contains the null value if ALLOCATE_STORAGE is NO. |
MAXIMUM_DELETED_PERCENTAGE | DLTPCT | INTEGER Nullable
|
Maximum percentage of deleted records allowed
(DLTPCT).
Contains the null value if the number of deleted records is not checked when the member is closed (*NONE). |
INITIAL_RECORDS | SIZE_INIT | INTEGER Nullable
|
Initial number of records (SIZE). This is the number
of records that can be inserted before an automatic extension occurs. Contains the null value if the number of records that can be inserted into each member is not limited by the user. The system determines the maximum member size (*NOMAX). |
INCREMENT_RECORDS | SIZE_INCR | INTEGER Nullable
|
Increment number of records (SIZE). This is the
maximum number of records that can be inserted into the member after an automatic extension
occurs. Contains the null value when INITIAL_RECORDS is null. |
MAXIMUM_INCREMENTS | SIZE_MAX | INTEGER Nullable
|
Maximum number of increments (SIZE). This is the
maximum number of automatic extensions that can be made to the member. Contains the null value when INITIAL_RECORDS is 0. |
REUSE_DELETED_RECORDS | REUSEDLT | VARCHAR(3) Nullable
|
Reuse deleted records (REUSEDLT).
|
MATERIALIZED_QUERY_TABLE | MQT | VARCHAR(3) Nullable
|
SQL materialized query table setting.
|
PARTITIONED_TABLE | PARTITION | VARCHAR(3) Nullable
|
Partitioned table setting.
|
ROW_AND_COLUMN_ACCESS_
CONTROL |
RCAC | VARCHAR(3) Nullable
|
Row and column access control setting.
|
Values for the following columns are returned when NATIVE_TYPE is LOGICAL. Otherwise, the columns will contain the null value. | |||
TOTAL_SELECT_OMIT | TOTAL_SO | INTEGER Nullable
|
Total number of select/omit statements for all record formats. |
FMTSLR_LIBRARY | FMTSLR_LIB | VARCHAR(10) Nullable
|
Record format selector program library (FMTSLR)
Contains the null value if there is no record format selector program. |
FMTSLR_PROGRAM | FMTSLR_PGM | VARCHAR(10) Nullable
|
Record format selector program (FMTSLR) Contains the null value if there is no record format selector program. |
IS_JOIN_LOGICAL | JFILE | VARCHAR(3) Nullable
|
Join logical file setting (JFILE).
|
DYNAMIC_SELECTION | DYNSLT | VARCHAR(3) Nullable
|
Dynamic selection setting (DYNSLT).
|
WITH_CHECK_OPTION | CHECK | VARCHAR(8) Nullable
|
With check option.
|
PHYSICAL_LOB | PF_LOB | VARCHAR(3) Nullable
|
Whether this logical file has no large object
fields, but the based-on physical file has a large object field.
|
PHYSICAL_DATALINK | PF_DATALNK | VARCHAR(3) Nullable
|
Whether this logical file has no DataLink fields,
but the based-on physical file has a DataLink field.
|
INDEX_COLUMN_IS_EXPRESSION | IXEXP | VARCHAR(3) Nullable
|
If the SQL index key column is an expression.
|
INDEX_EXPRESSION_HAS_UDF | IXEXPUDF | VARCHAR(3) Nullable
|
If the SQL index key column is an expression and the
expression contains a user-defined function (UDF).
|
INDEX_HAS_SEARCH_CONDITION | SPARSE | VARCHAR(3) Nullable
|
If the index has a search condition.
|
INDEX_SEARCH_CONDITION_
HAS_UDF |
SPARSE_UDF | VARCHAR(3) Nullable
|
If the index search condition contains a
user-defined function.
|
Examples
- Examine all the files in
APPLIB1.
SELECT * FROM QSYS2.SYSFILES WHERE LIB_NAME = 'APPLIB1';
- List the files that native logical files in APPLIB1 are based
on.
WITH BASED_ONS (LF_NAME, BASED_ON_NAMES) AS ( SELECT TABLE_NAME, BASED_ON_FILES FROM QSYS2.SYSFILES WHERE SYSTEM_TABLE_SCHEMA = 'APPLIB1' AND NATIVE_TYPE = 'LOGICAL' AND SQL_OBJECT_TYPE IS NULL ) SELECT DISTINCT LF_NAME, LIBRARY AS BASED_ON_LIBRARY, FILE AS BASED_ON_FILE FROM BASED_ONS, JSON_TABLE( BASED_ON_NAMES, 'lax $.BASED_ON_FILES' COLUMNS( LIBRARY CHAR(10), FILE CHAR(10) )) order by 1, 2, 3;