Integrity checks

The PolicyCenter application includes a large set of database SQL queries that provide integrity checks on staging table data. These checks find and report problems that would cause the import to operational tables to fail or put PolicyCenter into an inconsistent state. Optionally, the integrity check command can clear error tables and exclusion tables. You can see the integrity check SQL queries at Server Tools > Info Pages > Load Integrity Checks. You can check if any integrity checks failed at Server Tools > Info Pages > Load Errors.

Before importing staging table data into operational database tables, PolicyCenter runs integrity checks. If the checks fail, PolicyCenter rolls back the data import. To avoid rolling back a data import, run the integrity checks and correct any errors before attempting the import. Even if the rows that caused errors in earlier integrity check runs were removed, PolicyCenter must rerun integrity checks before importing your data.

• Identifying problems before a load starts is more efficient than triggering exceptions during the load process. If population of the operational tables encounters errors, the database must roll back the entire set of loaded records. Such rolling back of database changes is typically slow and resource-intensive.
• Some integrity check violations occur even if you remove all rows that contain errors from the staging tables. These violations occur for errors that cannot be tied to a single row.

The following descriptions contrast data integrity checks with other validations.

• The user interface (PCF) code enforces additional requirements.

  For example, a property that is nullable in the database may require that users set a value in the PolicyCenter user interface. Importing a null value in this property passes integrity checks. However, if a user uses the PolicyCenter interface to edit an object containing the property, PolicyCenter requires a non-null value before saving because of data model validation.

• Integrity checks are different from validation rule sets and the validation plugin.

Before you run the checks, you must set the server run level to maintenance. After the checks complete, set the server run level back to multiuser.

Example commands are shown below.

• Web service method example

  var batchProcessID =
    TableImportAPI.integrityCheckStagingTableContentsAsBatchProcess(clearErrorTable : true,
                                                                    populateExclusionTable : false,
                                                                    allowRefsToExistingNonAdminRows:false,
                                                                    numThreadsIntegrityChecking : 8)

• Command prompt tool example

  table_import -password password -integritycheck -clearerror -numthreadsintegritychecking 8 -batch