ANALYZE_CATALOG table function

The ANALYZE_CATALOG table function returns one row for each inconsistency it finds in the database catalog, returns rows that indicate the current database cross reference server status, Start of change or returns one or more rows that indicate whether maintenance for SQL services is in progress End of change .

When this table function is called, it always verifies that the database cross reference server jobs are working properly. If any of the server jobs are busy, the table function will wait until the job is available for work. In some cases, this can delay the return of results for several minutes.

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.
SQL SERVICES: Analysis will be done on the readiness of IBM i (SQL) services. The operating system sometimes needs to perform maintenance for SQL services. This normally occurs shortly after an IPL or shortly after an SQL services PTF is applied or removed. For independent ASPs (IASPs), the maintenance generally occurs shortly after the IASP is varied on. One or more result rows with SQL SERVICES in the CATEGORY column indicate the status of this maintenance.

library-name

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

*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.

For the DBXREF option, if no rows are returned no inconsistencies were identified. Multiple rows can be returned for the same object.

For the DBXREF SERVER option, two rows are always returned for SYSBASE. Two additional rows are returned when connected to an IASP.

Start of change For the SQL SERVICES option, a row is always returned for SYSBASE. A second row is returned when connected to an IASP. End of change

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. When CATEGORY is DBXREF SERVER or SQL SERVICES: a value of QSYS indicates the row is for SYSBASE a value like QSYSnnnnn indicates the row is for an IASP 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 or SQL SERVICES.
OBJECT_TYPE	VARCHAR(30)	Type of database object. Can contain the null value when CATEGORY is DBXREF SERVER or SQL SERVICES.
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. SQL SERVICES This row is for an SQL SERVICES request. The DETAIL column returns the status.
DESCRIPTION	VARCHAR(250)	A text description of the inconsistency or request.
DETAIL	VARCHAR(128)	Additional detail about the inconsistency or request. 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 When CATEGORY is SQL SERVICES and SEVERITY is INFO, contains: COMPLETE Any maintenance for SQL services is complete. It is safe to use SQL services. PENDING Maintenance for SQL services is pending. Using SQL services should be avoided. 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'));
```

Check whether SQL services are undergoing maintenance. If they are, continue checking until the maintenance window has completed.

BEGIN
  DECLARE V_PENDING_COUNT INTEGER;
  REPEAT
    SELECT COUNT(*)
      INTO V_PENDING_COUNT
      FROM TABLE(QSYS2.ANALYZE_CATALOG(OPTION => 'SQL SERVICES'))
      WHERE DETAIL = 'PENDING';
    IF V_PENDING_COUNT > 0 THEN
      CALL QSYS2.QCMDEXC('QSYS/DLYJOB DLY(30)');
    END IF;
  UNTIL V_PENDING_COUNT = 0
  END REPEAT;
END;