ANALYZE_CATALOG table function

The ANALYZE_CATALOG table function returns one row for each inconsistency it finds in the database catalog.

If no rows are returned, no inconsistencies were identified. Multiple rows can be returned for the same object.

Authorization: The caller must have *ALLOBJ special authority.

The schema is QSYS2.

option

A character or graphic string expression that identifies the type of catalog analysis to perform.

DBXREF: Analysis will be done for objects in the database cross reference files. This is the default.; Verification is done for constraints defined for *FILE objects.; The analysis done by this function goes beyond what RCLDBXREF OPTION(*CHECK) provides. It verifies that the constraint definitions in every database file object are correctly recorded in the database catalog.
DBXREF SERVER: Analysis will be done of the database cross reference servers. The database cross reference servers will be verified to ensure they are functioning properly. One or more result rows with DBXREF SERVER in the CATEGORY column indicate the current database cross reference server status.

library-name

A character or graphic string expression that identifies the library to be analyzed. This parameter only applies when option is DBXREF.

*ALL: All *FILE objects within all libraries will be analyzed, including any in independent auxiliary storage pools that are accessible. This is the default.
name: All *FILE objects within this library will be analyzed.

The result of the function is a table containing one row for each reported item with the format shown in the following table. All the columns are nullable.

Table 1. ANALYZE_CATALOG table function
Column Name	Data Type	Description
LIBRARY_NAME	VARCHAR(10)	Name of the library containing the database object. Can contain the null value when CATEGORY is DBXREF SERVER.
OBJECT_NAME	VARCHAR(128)	Name of the database object. Can contain the null value when CATEGORY is DBXREF SERVER.
OBJECT_TYPE	VARCHAR(30)	Type of database object. Can contain the null value when CATEGORY is DBXREF SERVER.
SEVERITY	VARCHAR(7)	The severity of the reported item. ERROR This row indicates an error that should be corrected. INFO This row provides information about processing that was performed. WARNING This row indicates a problem that should be investigated, but it does not indicate a serious problem.
CATEGORY	VARCHAR(20)	The category of this catalog row. CONSTRAINT An inconsistency was found with a FILE object constraint. DBXREF SERVER An inconsistency was found with the database cross reference server jobs. RECLAIM The CATALOG_NAME for this row is known to be inconsistent. Either RCLDBXREF FIX or RCLSTG SELECT(DBXREF) is required. Use RCLDBXREF CHECK to determine the action that needs to be taken.
DESCRIPTION	VARCHAR(250)	A text description of the inconsistency in the database catalog.
DETAIL	VARCHAR(128)	Additional detail about the inconsistency. When CATEGORY is CONSTRAINT, this is the name of the constraint. When CATEGORY is DBXREF SERVER, this can contain the number of entries in the cross reference queue Contains the null value if this row does not have additional detail information.
CATALOG_LIBRARY	VARCHAR(10)	The name of the library containing the catalog. Contains the null value if this row is not related to a specific catalog.
CATALOG_NAME	VARCHAR(10)	The catalog table that contains inconsistent information. Contains the null value if this row is not related to a specific catalog.

Example

Determine if there are any constraint inconsistencies for any database files on the system.
```
SELECT * FROM TABLE(QSYS2.ANALYZE_CATALOG(OPTION => 'DBXREF'));
```

Determine if there are any constraint inconsistencies for database files in library APPLIB.

SELECT * FROM TABLE(QSYS2.ANALYZE_CATALOG(OPTION => 'DBXREF',
                                          LIBRARY_NAME =>'APPLIB'));

Examine how the database cross reference servers are running. If they are behind on processing, the DESCRIPTION and DETAIL columns will indicate the number of entries in the queue that are waiting to be processed.
```
SELECT * FROM TABLE(QSYS2.ANALYZE_CATALOG(OPTION => 'DBXREF SERVER'));
```