Enterworks - ETT 154 - How to Find and Populate Missing Snapshot Table Records for a Repository

EnterWorks maintains a snapshot table for each repository to store a subset of the repository attribute data in relational form for improved performance with searches, sorting and displaying the data, facilitate workflow access and View Scheduled Exports.  When the list view is open for a repository, the snapshot table determines what records are going to be visible.  If all attributes in the User Preference are in the snapshot table, then the data displayed in the repository list view comes solely from the snapshot table.

There are occasions when one or more records in the repository do not have a snapshot table counterpart.  Some possible causes include:

• Snapshot rebuild job being aborted or services being restarted while the job was still processing.
• A data truncation error occurred when the record was being inserted into the snapshot table.
• Import or Promotion operations that insert new records in repositories are disrupted before the snapshot table updates are performed.
• Possibly other, yet unknown cause

When this happens, the record cannot be viewed in the UI and the system may permit a duplicate record to be created since it failed to find the existing record in the snapshot table.  Having the ability to quickly identify and populate missing snapshot table records eliminates the consequences of the records being missing and can assist with identifying the root cause for the snapshot records not being created by reporting the missing snapshot table records as soon as possible.

A SQL Stored procedure has been created that will find and report the details on any records in the designated repository that are not in the corresponding snapshot table.  This stored procedure can also optionally launch a Populate Missing Records snapshot table job on the repository if there are any missing records.  This session describes the stored procedure in detail and how to use it to generate reports and/or launch Populate Missing Records snapshot jobs if there are any records missing. 

This session provides background information on Snapshot tables and the consequences of them not being fully populated and describes the stored procedure in detail and how to use it within Scheduled Jobs.

Updated 4/21/2024

It was discovered that the Populate Missing Records option for a snapshot build job may result in some snapshot columns being cleared of their values, necessitating a full rebuild of the snapshot table.  Until this has been resolved, the EW_ReportAndPopulateMissingSnapshotData.sql script has been modified so the POPULATE action creates the missing records without attempting to populate the snapshot columns as this will make the records visible in the UI, even though the repository list view and queries against the snapshot table will show no values for the attribute columns.  The report generated with the POPULATE action should be used to revisit those records to manually edit and save them through the UI (a change has to be made for the snapshot table to be updated).

 Prerequisites - ETT 024

4