Administration Guide > Database > Database Maintenance > Method B - Partitioning with ILM

Partitioning with ILM

A remarkable amount of data accumulates during the daily operation process of an AE system.

This includes:

One method to maintain the AE databaseA database is an organized collection of data including relevant data structures. is to utilize partitioning with ILMStands for Information Lifecycle Management, which refers to a wide-ranging set of strategies for administering storage systems on computing devices. (Information Lifecycle Management).

General

Partitioning means that the data referred to above is stored in specific areas. Partitioning simplifies maintenance because data records of a particular period are stored at the same location and can be archived and reorganized together.

The starting point is the activation of an object, server process, agent etc. The statistical record which is created during such a process is stored in the current partition. All corresponding data (e.g. reports) is stored in the partition in which this statistical record has been stored.

You can specify the number of partitions and where they should be stored. Even the interval for a partition change can be configured.

Partitioning with ILM is supported for the MS SQL Server (only Enterprise or Developers Edition) and for Oracle.

The System Overview includes the area ILM in which the partitions are displayed.

Access to this area is protected with a privilege.

Note that object versions and deleted objects must still be reorganized with utilities even if you use partitioning with ILM.

Functioning

Let us look at the principle of partitioning using an example:

An AE database contains three partitions. A partition change is performed at the beginning of every month. Thus, partition "P1" contains the data records of activations which took place in September and partition "P2" those of October. The current month is November and the corresponding partition is "P3". The data of an object that is activated during this month is stored in P3. A task that has already started in October but ends now is stored in partition "P2".

The three partitions "P1", "P2" and "P3" are online which means that the Automation Engine can access data records in them.

Several weeks pass, it is the beginning of December and time has come to change the partition. The newly created partition "P4" is the most current one. As the number of online partitions can be specified, the system first checks whether this value has been exceeded. In this example, the specified value is "3"; this means that the same container (tablespace/filegroup) is used as for partition "P1". The data within partition “P1” will be available until you decide to drop the partition.

Partitions

In the example above, statistical records, reports, messages etc. of a particular period of time are stored in one container. It is also possible to store reports and messages each in a separate container (tablespace/filegroup). Three variables are available in which you can determine the storage locations for partitions:

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

Partition Change

Use a Calendar object to specify the days on which a partition change should take place. Enter the name of the Calendar object and the keyword in the variable UC_ILM_SETTINGS, key CALENDAR. The partition changes at 00:00 on the specified days. The TimeZone of system client 0000 serves as the time basis. Should the Automation Engine not be active at this point in time, the partition will change at its next start time.

When the time for a partition change has come, processing in the AE system comes to a halt and waits until all work processes have ended their current database transactions. The key TIMEOUT in the variable UC_ILM_SETTINGS is decisive for the maximum waiting time. A new partition can only be created if there are no longer any active database transactions.

Now the system checks whether there are more online partitions than specified in the variable UC_ILM_SETTINGS, key ONLINE_PARTITIONS. If this is the case (MS SQL Server), the system checks whether partitions that exceed the specified value contain data records of active tasks.

The Automation Engine's log file includes detailed information about the partition change. This includes a list of all active tasks of the partition which should no longer be online.

You can check a partition for active tasks at any time via the System Overview, area "ILM". The result is also supplied in list form.

MS SQL Server: A switch-out is performed if a partition no longer contains data records of active tasks. During this process, a staging table is created which can be stored, unloaded etc.
Oracle databases: The partition remains the same and you can use Oracle means on partition level to create backups, exports etc. and then delete them. For Oracle the AE only checks for active tasks when an attempt is made to delete the partition.

A partition remains as it is if it still contains data records of active tasks. This partition is checked again when the next partition change is performed. You can check the list of active tasks, adjust it and finally perform a switch-out for the relevant partition or delete it in the System Overview.

Note that you can archive, delete etc. a partition even if it contains active tasks. Automic strongly recommends contacting Automic Support in this case in order to avoid problems. For example, if a task ends and its statistical record belongs to a deleted partition, its report cannot be stored and is lost.

You can automatically react to successful or failed partition changes via the variable UC_ILM_SETTINGS in which you can define the objects to be activated in such a case. The keys EXECUTE_ON_SUCCESS and EXECUTE_ON_FAILURE serve especially this purpose.

Long-Running Tasks

As described above, active tasks prevent the offline switching of partitions that contain the statistical records of these active tasks. Automic recommends specifying an interval for the partition change which is high enough to avoid such a situation. Despite this, there are tasks which run for a very long time or never end by definition.

A regular log change is made and a new statistical record is created for the following tasks:

Compare the log-change interval with the time interval of the partition change.

In the following tasks, the log changes automatically directly before a partition change takes place:

The following tasks require manual interference because no log change is made:

Database Rights for ILM

A database user with schema rights is required for the database actions that are necessary in order to use partitioning with ILM. For security reasons, Automic recommends using a separate database user for ILM actions. Create a Login object in system client 0000 and enter the database user's login data. Select the host type "DB" and enter the Login object's name in the variable UC_ILM_SETTINGS, key LOGIN.

The database user for the ILM actions and the database user specified in the Automation Engine's INI-file section [ODBC] need to have permission to read the following system tables:

The following rights are also required for the database user for the ILM actions:

Installation

ILM installation is performed using the utility AE.DB Load when loading initial data. Install ILM:

The AE database must be maintained using the utilities if you decide not to use partitioning with ILM.

Note that you cannot de-install partitioning with ILM but you can deactivate the ILM functionality. If ILM is not active, no partition change or switch-out (only MS SQL Server) is performed.

When you use an Oracle database, make sure that there is only one schema of Automation Engine version 8.00A within an Oracle instance that is used for ILM.

Procedure

  1. Think about the number of partitions that your AE system should have and where they should be stored.
  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 also used for other purposes.
  3. Start the utility AE DB Load and load the initial data. A form is displayed in which you can specify the main settings for ILM.
  4. Activate the checkbox "Install ILM".
  5. When ILM is installed, a "switch in" of records in the EH database table takes place. Do not uncheck the "do switch in after installation" option if there are records in the EH table.

  6. Enter the number of online partitions.
  7. When updating to or within Automation Engine version 8.00A or later, you can activate the checkbox "Do Switch-In after installation". Doing so has the effect that existing data is stored in the partitions.
  8. Enter the file groups (MS SQL Server) or tablespaces (Oracle).
  9. All other settings can be specified as soon as the Automation Engine is active and you have logged on to the AE system via the UserInterface.
  10. Log on to system client 0000.
  11. Adjust the variables UC_ILM_SETTINGS and UC_CONTAINER_* (if required).
  12. Adjust the setting CHANGE_LOGGING_DAYS within UC_SYSTEM_SETTINGS to fit to the partition change interval.
  13. Automate partition handling (e.g. archiving, storage, deletion etc.).

Important Note for MS SQL Server

A partition change and the corresponding switch-outs require that all indexes have been defined locally i.e. with exactly the same rules as those used to partition the tables. Globally-defined indexes prevent switch-outs and switch-ins.

Script

The script function ILM can be used to control particular functions.