Start of change

VALIDATE_DATA, VALIDATE_DATA_FILE, and VALIDATE_DATA_LIBRARY table functions

The VALIDATE_DATA, VALIDATE_DATA_FILE, and VALIDATE_DATA_LIBRARY table functions examine the data in database physical file members to verify it is properly formed. This can be used to identify invalid data, such as invalid decimal values, that were inserted into a DDS-created physical file.

Authorization: The user must have *EXECUTE authority to the library and *OBJOPR and *READ authority to the file.

Read syntax diagramSkip visual syntax diagram VALIDATE_DATA ( LIBRARY_NAME => library-name ,FILE_NAME =>  file-name,MEMBER_NAME => member-name )
library-name
An character string expression that specifies the name of the library containing file-name.
file-name
A character string expression that specifies the name of a database physical file.
member-name
A character string expression that specifies the name of a member in file-name. The special values *FIRST and *LAST are supported. If this parameter is omitted, *FIRST is used.

The VALIDATE_DATA_FILE table function validates all members in a physical file. It is identical to VALIDATE_DATA except it only has library-name and file-name parameters.

The VALIDATE_DATA_LIBRARY table function validates all members in all physical files in a library. It is identical to VALIDATE_DATA except it only has a library-name parameter.

The result of the function is a table containing a row for each row in a member that has invalid data in at least one column. The columns of the result table are described in the following table. The result columns are nullable.
Table 1. VALIDATE_DATA, VALIDATE_DATA_FILE, and VALIDATE_DATA_LIBRARY table function
Column Name Data Type Description
VALIDATE_TIME TIMESTAMP The timestamp when this row was generated.
LIBRARY_NAME VARCHAR(10) The library containing the file.
FILE_NAME VARCHAR(10) The physical file containing the member.
MEMBER_NAME VARCHAR(10) The member with the invalid data.
RELATIVE_RECORD_NUMBER BIGINT The relative record number (RRN) of the row in MEMBER_NAME with the invalid data.
SQL_WARNING INTEGER The SQLCODE of the warning that indicated this error.
REASON_CODE INTEGER The reason code from the warning that indicated this error.
COLUMN_NAME VARCHAR(128) The name of the column with the invalid data.
WARNING_TEXT VARCHAR(1000) The text from the warning that indicated this error.

Note

This function is provided in the SYSTOOLS schema as an example of how to examine all the rows in a table by using an SQL table function. Creating customized versions of this table function to better suit a specific need is encouraged. Use the Insert Generated SQL feature in IBM i Access Client Solutions (ACS) to extract the source for this function. Then modify it and create a new procedure in a user-specified schema.

Example

  • Validate the content of all members in FILE1. If no rows are returned, all the data appears to be good.
    SELECT * FROM TABLE(SYSTOOLS.VALIDATE_DATA_FILE('APPLIB', 'FILE1'));
End of change