Maintaining Data Records with ILM

As a system administrator, you can use ILM to regularly reorganize and archive the data in your database. ILM is the right option if you have a large, complex database and you want to maintain it with the lowest possible cost in terms of Automation Engine load and maintenance efforts. It makes use of partitioning, whereby data is stored in different areas within the database depending on certain criteria. This simplifies maintenance because data records of a particular period are stored in the same logical area and can be archived and reorganized together.

You can also use Utilities to reorganize and archive data. For more information on which method to use, see Maintaining Data Records.

Overview

During the daily operation of an Automation Engine system, large amounts of reporting, statistical and historical data accumulate. Partitioning the database with ILM is an efficient, fast and easy method to deal with this data flow and maintain the object version as well as the deleted object data. ILM partitions allow you to store data in different logical areas in the database according to the start time of the object, to define the intervals in which the partitions should change, and so on.

Partitioning the Automation Engine database using ILM affects only the following type of data:

Note: ILM does not handle object versions or deleted objects. These must be reorganized with Utilities, even if you use partitioning with ILM.

Key Concepts Related to ILM

Prerequisites

To use the ILM (Information Lifecycle Management) functionality you must use a database system with a partitioning feature (usually enterprise editions).

MS SQL and Oracle require the following additional permissions to work with ILM (Information Lifecycle Management):

Database Rights

You need a database user with schema rights to use partitioning. The database user also requires the following rights for the ILM actions:

You can also use a separate database user for ILM, for security reasons. If you decide to have a separate database user for ILM, you have to create a Login Object (LOGIN) object in Client 0 and enter the login data of the database user. Select the agent type DB and enter the name of the Login object in the key LOGIN of the variable UC_ILM_CONTAINER_* - Automation Engine Database Partitions.

Also, the database user for the ILM actions and the database user specified in the ODBC section of the INI file (ucsrv.ini) of the Automation Engine must have permission to read the following system tables:

Activating ILM

The utility AE DB Load allows you to activate the ILM functionality, either during a new installation, and upgrade or at any given point in time. You can do it when while Loading the AE Database or using the Start Parameters - Utilities with a command line interface (CLI).

As an Automation Engine system administrator, you specify the number of partition sets and define where they are stored. To configure ILM after activation, the Automation Engine needs to be active and you need to be able to log on via the Automic Web Interface.

Notes:

To Activate and Configure ILM

Important! The variable UC_ILM_CONTAINER_* - Automation Engine Database Partitions must always match the number of online partitions defined in the variable UC_ILM_SETTINGS - Settings for Partitioning with ILM.

  1. Define the number of partitions and their physical storage area.

  2. Create the files and file groups (MS SQL Server) or tablespaces (Oracle).

    They should only contain data of your AE system in order to facilitate the archiving and storage of partitions. The Automation Engine does not check whether file groups or tablespaces are used for other purposes as well.

  3. Start the utility AE DB Load and load uc_upd.txt. A form is displayed in which you can specify the main settings for ILM.

  4. Activate the checkbox Install ILM.

    During ILM installation you can choose if you wish to keep historical data or not.

  5. Enter the number of online partitions.

  6. Enter the file groups (MS SQL Server) or tablespaces (Oracle).

    For more information, see Mapping Partitions to Tablespaces / Filegroups

  7. In the Automic Web Interface, log on to system Client 0.

  8. Adjust the variable UC_ILM_SETTINGS - Settings for Partitioning with ILM, if needed. Do not forget to adjust the variable UC_ILM_CONTAINER_* - Automation Engine Database Partitions accordingly.

  9. Adjust the setting CHANGE_LOGGING_DAYS in the UC_SYSTEM_SETTINGS - System-Wide Settings to fit to the partition change interval.

  10. To define how often the Automation Engine should change to a new partition, create a Calendar Object (CALE) with a Calendar Events that you assign to the UC_ILM_SETTINGS - Settings for Partitioning with ILM variable using the key CALENDAR.

  11. Activate ILM in the Administration Perspective. See Managing ILM Partitions.

  12. Automate partition handling such as archiving, storage, deletion and so on.

Mapping Partitions to Tablespaces / Filegroups

Mapping defines the physical storage areas in the database (tablespaces / filegroups) in which the partition sets are stored. To map partitions to tablespaces / filegroups, ILM uses containers. It is possible to store entire partition sets in one container, and groups of partitions in separate containers. In both cases the mapping is cyclic. That means that partition set 1 goes to tablespace 1, partition set 2 goes to tablespace 2, partition set 3 goes to tablespace 3, partition set 4 goes again to tablespace 1, and so on.

Note: If the database is in a single logical drive, all your partitions should be in one physical storage area. Different physical storage areas are only necessary if your system has more than one logical drive.

Storing an Entire Partition Set in a Container

The mapping of the partition sets and tablespaces / filegroups is as follows:

Example

All data of partition sets is stored in one tablespace: enter TAll in all variables for each container

Container Resulting partition sets UC_ILM_CONTAINER_STATISTICS UC_ILM_CONTAINER_REPORT UC_ILM_CONTAINER_MISC
1 1, 4 , 7, ... TAll TAll TAll
2 2, 5, 8, ... TAll TAll TAll
3 3, 6, 9, ... TAll TAll TAll

Storing Groups of Partitions in Separate Containers

You can also store groups of partitions in separate tablespaces / filegroups, instead of assigning a whole partition set. These groups are divided by the type of data:

The data records are mapped to the corresponding category in the variable UC_ILM_CONTAINER_* - Automation Engine Database Partitions .

Note: All three variables must have the same number of partitions. It is not possible to use five partitions for statistical records but only three for reports.

Example

The variables determine where data is stored. Statistical data is stored in the variable UC_ILM_CONTAINER_STATISTICS, report data in the variable UC_ILM_CONTAINER_REPORT, and messages and the rest of the data in the variable UC_ILM_CONTAINER_MISC.

Container Resulting partition sets UC_ILM_CONTAINER_STATISTICS UC_ILM_CONTAINER_REPORT UC_ILM_CONTAINER_MISC
1 1, 4 , 7, ...

S1

 R1 M1
2 2, 5, 8, ... S2 R2 M2
3 3, 6, 9, ... S3 R3 M3

Changing Partitions

To define the date on which the partition change takes place you need a Calendar Object (CALE) object assigned to the variable UC_ILM_SETTINGS - Settings for Partitioning with ILM using the key CALENDAR. The partition changes at 00:00 on the dates specified in that Calendar using the time zone defined in Client 0 as basis. If the Automation Engine is not active at this time, the partition changes as soon as it starts.

After a partition change, whether successful of failed, you can specify how the Automation Engine should react in either case. To do so, you define which objects should be executed in the EXECUTE_ON_SUCCESS and EXECUTE_ON_FAILURE keys in the variable UC_ILM_SETTINGS - Settings for Partitioning with ILM.

Long Running Tasks

You can not archive and delete a partition if it contains active tasks. Make sure to specify an interval for the partition change that is long enough to avoid such a situation.

Nevertheless, there are tasks that run for a very long time or never end, such as Events, recurring tasks or the Remote Task Manager. You can compare the log-change interval with the time interval of the partition change.

Example

If you have five partitions and you change partitions every day, the log change interval must be below five days.

The log of objects that are executed once are changed automatically before a partition change. For more information, see Executing Objects Once. The following tasks require manual interaction because no log change is made:

See also: