ILM - Information Lifecycle Management

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:

Statistical records
Reports
Messages and data for the AE DB Revision Report and the Open Interface to Output Management Systems, see AE DB Revision Report and Open Interface to Output Management Systems

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

This page includes the following:

Key Concepts Related to ILM

Partition

Division of a logical database entity (database table) into independent logical parts based on certain criteria
Partition key

Key that is used to formulate a partition criterion

The RunID of the object is used as the partition key.
Partition set

Set of partitions which belong together logically (all partitions having the same partition criteria)
Online partition

Partitions accessible by the Automation Engine system
Tablespaces (Oracle) / Filegroups (MS SQL)

Physical storage areas where the content of the database is stored

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.
Container

Mapping of a partition to a tablespace / filegroup

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:

SQL Server requires the VIEW SERVER STATE permission
SQL Database requires the VIEW DATABASE STATE permission in the database
Oracle requires the permission on USER_OBJECTS, V$LOCKED_OBJECT, V$SESSION, and DBA_OBJECTS

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:

ALTER TABLE
DROP PARTITION

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 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 LOGIN key in the UC_ILM_SETTINGS variable. For more information, see Login (LOGIN).

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 (see Automation Engine) must have permission to read the following system tables:

ALL_TAB_PARTITIONS
ALL_CONSTRAINTS
ALL_PART_INDEXES
ALL_INDEXES
ALL_TABLES

Activating ILM

The utility AE DB Load allows you to activate the ILM functionality, either during a new installation, an upgrade, or at any given point in time. You can activate it when you are loading the database, or by using the start parameters for Utilities with a command line interface (CLI).

More information:

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:

Once ILM has been activated, you cannot deactivate it. The ILM functionality can only be stopped. If ILM is stopped, no partition change or switch-out (MS SQL Server) is performed.
When using an Oracle database, make sure that there is only one Automation Engine schema within an Oracle instance used for ILM.

To Activate and Configure ILM

Important! The UC_ILM_CONTAINER_* variable must always match the number of online partitions defined in the UC_ILM_SETTINGS variable. For more information, see UC_ILM_CONTAINER_* - Automation Engine Database Partitions.

Define the number of partitions and their physical storage area.
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.
Start the AE DB Load utility and load uc_upd.txt. A form is displayed in which you can specify the main settings for ILM.
Activate the checkbox Install ILM.

During ILM installation you can choose if you wish to keep historical data or not.
Enter the number of online partitions.
Enter the file groups (MS SQL Server) or tablespaces (Oracle).

For more information, see Mapping Partitions to Tablespaces / Filegroups.

In Oracle, the default tablespace is UC4_DATA, but you can create one for ILM.

In MS SQL the default file group is [PRIMARY], but you can create a file group for ILM. For example, [UC_ILM].
In the Automic Web Interface, log on to system Client 0.
Adjust the UC_ILM_SETTINGS variable, if needed. Do not forget to adjust the UC_ILM_CONTAINER_* variable accordingly. For more information, see UC_ILM_SETTINGS - Settings for Partitioning with ILM and UC_ILM_CONTAINER_* - Automation Engine Database Partitions.
Adjust the CHANGE_LOGGING parameters in the UC_SYSTEM_SETTINGS variable to fit to the partition change interval. For more information, see CHANGE_LOGGING Parameters.
To define how often the Automation Engine should change to a new partition, create a Calendar object with a Calendar event that you assign to the UC_ILM_SETTINGS variable using the key CALENDAR. For more information, see Calendars (CALE) and Calendar Events
Activate ILM in the Administration perspective. For more information, see Managing ILM Partitions.
Automate partition handling such as archiving, storage, deletion and so on.

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 Utilities and Maintaining Data Records.

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:

Container 1 maps partition sets 1, 4, 7, and so on to tablespace 1
Container 2 maps partition sets 2, 5, 8, and so on to tablespace 2
Container 3 maps partition sets 3, 6, 9, and so on to tablespace 3

Example

All data of partition sets is stored in one tablespace:

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

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:

statistical records (tables A*)
reports (tables R*)
miscellanea, such as messages and audit trail (tables Meld, XAO, XRO)

The data records are mapped to the corresponding category in the UC_ILM_CONTAINER_* variable. For more information, see 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 UC_ILM_CONTAINER_STATISTICS variable, report data in the UC_ILM_CONTAINER_REPORT variable, and messages and the rest of the data in the UC_ILM_CONTAINER_MISC variable.

Container	Resulting partition sets	UC_ILM_CONTAINER_STATISTICS	UC_ILM_CONTAINER_REPORT	UC_ILM_CONTAINER_MISC
1	1, 4 , 7, ...	STATISTICS1	REPORT1	MISC1
2	2, 5, 8, ...	STATISTICS2	REPORT2	MISC2
3	3, 6, 9, ...	STATISTICS3	REPORT3	MISC3

Changing Partitions

To define the date on which the partition change takes place you need a Calendar object assigned to the UC_ILM_SETTINGS variable using the CALENDAR key. 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 UC_ILM_SETTINGS variable.

For more information, see Calendars (CALE) and 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. For more information, see CHANGE_LOGGING Parameters.

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:

Active Notification objects
Tasks in a waiting condition (such as Waiting for host)