AE DB Reorg

Administrators can use the AE DB Reorg utility (ucybdbre) to reorganize data in the database. Reorganizing your database tables regularly is vital because it ensures that you use your database space efficiently. During this process, this utility marks data records with a deletion flag according to the parameters and settings that you have defined. Once the data records are marked, the AE DB Unload utility can permanently remove the flagged data records from the database throughout your AE system, and in all clients.

Important!

  • Check your database, log-file backups, and database consistency regularly. Do so by using the means that are available for the specific database.

  • Reorganize every client regularly, especially client 0. Even in small systems, it is recommended to run the reorganization process every day. You can set up an SQLI variable for this purpose. For details, see SQLI VARA Objects

  • It is recommended to reorganize indexes regularly. Doing so increases your system performance, and ensures that you can use database space more efficiently. You can reorganize indexes by using the means that are available for the specific database, such as the Database Wizard for MS SQL Server.

  • You can archive the relevant data in batch mode. For this purpose that you must start the utility in regular mode at least once to specify the settings you require. For more information, see Start Parameters - Utilities.

Tip! This utility automatically creates and updates the UC_UTILITY_REORG variable. No manual interaction is required. For details, see UC_UTILITY_REORG - Reorganization Specifications

This page includes the following:

Overview

Log on to the utility by using the client that you want to reorganize.

The graphical user interface provides the following status information in the following table columns:

  • Table

    Shows data areas such as messages, statistics, reports, version management and object audits. The related database tables are included in brackets. By double-clicking the relevant table line, you can change the settings.

  • Last reorganization

    Date and time of the last reorganization run that was made with the AE DB Unload utility

  • Current number

    Number of data records that can be reorganized

  • Criteria for reorganization

    Current definitions of the reorganization criteria that have been made. The date as of which the reorganization should take place is shown in brackets.

  • Number after reorganization

    Number of data records that are not reorganized because of the specified criteria

The lower window area provides three sections that you can either double-click directly, or open from the File menu in the upper left window area:

  • Reset deletion flags

    Allows you to reset the deletion flags of the particular.

    To reset the deletion flags of several clients, see AE DB Unload.

  • Forecast

    Creates a forecast of the data that should be reorganized. You may want to create such a forecast before you start the actual reorganization process. In this case, the utility counts the data records to be reorganized according to your settings, and displays the result in the particular tables.

    Note: Forecast calculations ignore the Keep last settings, and the settings specified in no_archive_check=.

  • Reorganization

    Starts the reorganization process. You can view the progress of the reorganization run in the status line of the window. This line shows the name of the current table, the entire number of data records, and the progress in percent.

Defining Reorganization Parameters

You can either use the Settings menu to define your reorganization parameters or double-click the specific table line. The settings you define here only apply to the client that you have used to log on to the utility. You can specify a specific period for all data records that should be archived. The hour 00:00 is used as the start time of a day to ensure that complete days are considered in calculations.

Messages Tab

This table displays all system messages. You can specify which messages to reorganize using the options below:

  • Reorg messages

    Select this checkbox to indicate that the client’s messages should be archived.

  • Unread messages

    All unread messages older than the specified number of days will be reorganized. The system also shows the exact date matching the entered number of days.

  • Read messages

    All read messages older than the specified number of days will be reorganized. The corresponding cutoff date is displayed as well.

Important! If the auto_reorg= parameter is sent in the INI file, its value takes precedence over the graphical interface setting for specifying a retention period (for example, messages older than a certain number of days). This only applies if the value in the INI file is smaller; the system always uses the shorter retention period. For more information, see Utility DB Reorg.

Statistics Tab

This table contains a list of past executions.

  • Reorg statistics

    Select this checkbox to indicate that the client’s statistical data should be reorganized.

  • Reorg statistics older than

    All statistical data older than the specified number of days will be reorganized. The corresponding date for this period is also shown. Statistical data from this date or more recent will be retained.

  • Keep last

    This option allows you to specify that the most recent n statistical records of each object to be reorganized will be kept. This is especially useful for executable objects, as it enables you to preserve statistical data and reports of processes that ran in the past but are executed infrequently. The system guarantees that at least the number of records you specify will be retained for each object to be reorganized. Any records older than this and exceeding the specified number will be marked for deletion.

Important! The Reorg statistics older than and Keep last settings work together as follows: the AE DB Reorg utility keeps all records for each object with a timestamp newer than the Reorg statistics older than threshold. For records older than this threshold, the system retains the newest N entries per object, as set by Keep last. For example, if Reorg statistics older than is set to 30 days and Keep last is set to 5, you will retain all runs from the last 30 days plus 5 older runs per object. Please note that having more than the number set in Keep last is possible for any object with multiple executions in the last X days, as these are excluded from deletion calculations.

Reports Tab

This table contains all reports associated with tasks.

  • Report reorganization

    Select this checkbox to enable reorganization of the client's reports.

  • Reorg reports older than

    All reports older than the specified number of days will be reorganized. The corresponding cutoff date is displayed, and reports newer than this date are retained.

  • Keep last

    This option allows you to specify that the last n statistical records of an object within the reorganization period are preserved. This is especially useful for tasks that run infrequently. Importantly, Keep last applies to the entire reorganization period, meaning the utility evaluates how many reports fall within this period and retains the most recent n reports. It does not imply that all reports except the last n are reorganized.

Important! Statistical records associated with reports (RH/RT tables) are reorganized together with the reports. When report reorganization is activated in this tab, reports are processed before their corresponding statistical records.

Version Management Tab

This table contains the full set of versioned objects created incrementally for each object, see Defining the Version Management Page.

  • Version Management object reorganization

    Select this checkbox to enable reorganization of the client's Version Management objects.

  • Reorg objects older than

    All Version Management objects older than the specified number of days will be reorganized. The corresponding cutoff date is displayed. Objects younger than the specified number of days are retained.

  • Keep last

    This option lets you specify that the most recent n Version Control objects are preserved. This is especially useful for tasks that are modified infrequently. The Keep last setting applies to the entire reorganization period and does not mean that all objects except the last n are reorganized. Instead, the utility counts how many objects fall within the reorganization period and keeps the most recent n.

Object Audit Tab

This table contains the revision reports generated by the AE DB Revision Report utility, see AE DB Revision Report.

  • Reorg Object Audit

    Select this checkbox to enable reorganization of the client's Object Audits.

  • Reorg Object Audits older than

    All Object Audits older than the specified number of days will be reorganized. The corresponding cutoff date is displayed. Object Audits younger than this period are retained.

  • Only reorg revised Object Audits

    This option limits reorganization to only those Object Audits that have previously been included in reports generated by the revision utility.

See also: