Manage a dataset schema

This function enables you to check the database and fix any structural issues that may have accumulated.

For efficient maintenance of the Content Manager database, it is essential that:

• the database is backed up completely before initiating any actions within the Schema Manager
• if the database contains critical site-specific customizations, a script needs to be available to reinstate these customizations in case they are unintentionally chosen to be removed by the person running the Schema Manager

To access:

1. Expand Datasets
2. Right-click the dataset you want to work with and on the Schema menu, click Manage.

   The Schema Manager dialog box appears.

   The purpose of the Schema Manager is to allow the user to perform the following tasks:

   • Detect and recreate any missing objects.
   • Detect and repair any objects not matching the default schema.
   • Detect and remove any invalid objects not part of the default schema.
   • Detect and recreate system required Special Logins.
   • Convert ANSI databases to Unicode.
   • Drop and/or Recreate Indexes and Referential Constraints.

• The left hand navigation panel allows you to view the various objects within the database displayed in an expandable tree view. These objects are categorized within Tables, Procedures, Views and Special Logins. Expanding each of these categories will display the names of the relevant objects. Further expansion on individual tables will list their components in the form of columns, indexes and foreign keys.
  
  Each level and object has a right-click menu. The maintenance tasks available on this menu, for each level, are as follows:
  
  • Check – This is the default action. This function runs a scan over every object listed, within the selected branch, to check its current state against the default schema.
  • Repair – Available after a Check is run. Corrects issues found by the Check, within the selected branch.
  • Recreate – Available after a Check is run. Re-creates the object (and its components), within the selected branch, if a Repair doesn’t work

    NOTE: This process does NOT modify data. Data issues have to be manually fixed.

    Additional Tasks available:

    • Delete All Indexes - Available at the Dataset level only – Removes all Indexes on all tables in the selected database.
    • Recreate All Indexes - Available at the Dataset level only – Adds all the missing Indexes on the default Schema tables in the selected database.
    • Delete All Referential Constraints - Available at the Dataset level only - Removes all Foreign Keys on all tables in the selected database.
    • Recreate All Referential Constraints - Available at the Dataset level only – Adds all the missing Foreign Keys on the default Schema tables in the selected database.
    • Cleanup Orphaned Data - Available at the Dataset level only – reviews and deletes data that is no longer relevant. It reviews shared object metadata tables that are not protected with relational integrity constraints, such as access controls, barcodes and notes. The Cleanup Orphaned Data dialog displays a list of all the objects and their associated data types and provides a count for the number of rows that contain orphaned data and are flagged to be cleaned up.
      • Check All - click Check All to run a check on all the data types. This will run a check and list only the rows that contain orphaned data and are flagged to be cleaned up.
      • Check - click Check to run a check on the selected data type. This will update the selected data type and display the number of orphaned data instances have been flagged to be cleaned up.
      • View - click View to display a list of all orphaned items associated the selected data type.
      • Cleanup - click Cleanup to remove all orphaned data rows for the selected data type.
      • Cleanup All - click Cleanup All to remove all orphaned data rows for the selected dataset.
    • Initialize Document Views Counter - Available at the Dataset level only - select this option to initialize this view counter based on the number of Document Viewed events stored in the online audit log. This will only be useful if your database is currently auditing document view activities for all record types.
      • Limit how many rows can be processed at one time - select this option to limit the number of rows that are processed at one time and execute the operation in batches.
        • Maximum rows to process - set the maximum number of rows to update at any one time. If the processes are timing out, reduce this number.
      • Restart from Unique Identifier - if the initialization process fails, the Unique Identifier for the row it failed on will be written to a log file. To restart the process from this record, enter the Unique Identifier in this field.
    • Delete All - Available at the named table’s Foreign Keys and Index levels only - Removes all the selected object types for the selected table in the selected database.
    • Delete - Available at the named table’s individual index and foreign key levels only - Removes the selected object from the selected table in the selected database.
    • Set NULL Column Values - Available at the Dataset, Tables, named table and named table’s Columns levels only - sets an empty string value into columns that currently contain a NULL value. This ensures that when NOT NULL constraints have been applied to all columns by the Schema Manager, no errors will occur. For Oracle the value will be one space.
    • Remove trailing blanks - Available at the Dataset, Tables, named table and named table’s Columns levels only - some fields within older databases may contain unnecessary spaces at the end of their values. These spaces can affect the way Content Manager works. Use this function remove these trailing spaces from those fields.
    • Convert to Unicode - Available at the Dataset level only - convert ANSI databases to support Unicode. Your Oracle or SQL Server database must support Unicode for this option. See CM23.4_Spec.pdf for more information about Unicode support.

    NOTE: Please make sure you backup the database before proceeding with this process. If for any reason you have to cancel this process, you will need to restore that backup to get the database back a recommended state. A partially converted database is NOT recommended.

    • Add GIS Support - This option allows you to add Geographical Information System (GIS) support to the dataset. Once this feature is enabled it cannot be disabled.
    • Fix Document Type Indexes - Available at the Dataset level only - indexes existing documents by file type, file extension.

      Enables you to create or append file type or file extension values to the file type or file extension index for all existing electronic documents in the dataset.

      This function's purpose is to make these electronic documents available for a search based on the electronic document's file extension, for example, to search by all records created yesterday with a file extension of .doc.

      This is useful to index electronic documents that existed before an upgrade of Content Manager that included the Electronic Document Types search method.

      Any documents added to a Content Manager dataset after this upgrade are automatically indexed; however, you need to manually index existing documents in the dataset using this function for Content Manager to be able to find them using this search method.

    • Fix Date Values - Available at the Dataset level only - displays the Date Repair Utility dialog, which enables you to run two date repair utilities:
      • Repair old GMT dates - checks all records with the default midnight time stamp, adds 12 hours to it and adds an identifier in the end.

        It will go through all the columns and tables and upgrade the format of the date and time value to the new format.

        This function corrects the date and time values so that the time values are sorted correctly.

        • Searching for records accommodates this different format:
          • Searching for a particular date will find all the records with that date, including the ones without times.
          • Searching for a range of times on the date will find all the records with that date and time range and also the ones without a time if the lower time range value is 11:59:00am or earlier and the upper time range is 12:00:00pm or later.
          • Searching for a date and specifying exactly midday as the time will not find the records without times.
      • Repair dates without time component - fixes dates that were incorrectly altered by TRIM 7.32 or TRIM 7.33.
        There is no need to run this utility unless you have been running one of these versions of TRIM (previous name of Content Manager) in the past.

        To run the utility, fill in the date fields and your time zone and choose whether to merely detect or to also correct the incorrect dates in your database.
        Click OK to start the process.
        

    • Fix Location Default Relationships - Available at the Dataset level only - checks Location relationships to ensure there is always a default. This is required by the Location statistics program needs when querying the database directly. This will ensure aggregation statistics works correctly.

• The right hand panel provides the following options:

  • Selected Item - the highlighted object from the left hand panel.

  • Test Results - after performing a Check, for each object highlighted on the left hand panel, a brief status and recommended action will appear here.

  • Check All Items – this performs the same actions as the right-click Check option at the Dataset level on the left hand panel.

  • Save Report - after running a Check or Check All Items on the dataset, click this option to save the generated report.
  • View Log - after running a Check, Repair or Recreate on the dataset, this option displays the log file for the SQL statements that were issued to the database.
  • View Report - if a report is saved, it can be opened using this option.