Partitioning the AE database by using ILM results in a very specific situation when the maximum RunID (tasks, statistical records) is reached. How quickly this limit is reached depends on the number of tasks that are started on a regular basis. To solve this problem, the system executes a special procedure in such a situation which is referred to as Partition Key Turnaround.
General Information
Statistical entries (for executed tasks) are stored in the AH table which also includes the corresponding RunIDs in the AH_Idnr column. From a technical point of view, this column's values are 32-bit numbers and the maximum value that can be reached is 2,147,483,647. All numbers below 1,000,000 are reserved for specific processes.
Therefore, the allowed values for the RunIDs of statistical records range from: 1,000,000 to 2,147,483,647.
You will rarely ever reach this limit in AE systems that include a low number of regularly executed tasks. By using a huge number of daily activities (~1,000,000), you can already reach this limit after a few years.
Partition Change
For general information about ILM and partition changes click here.
The database always keeps a certain number of partitions online. You can determine this number by specifying the setting ONLINE_PARTITIONS in the variable UC_ILM_SETTINGS.
Using AE with ILM means that each partition is limited by a maximum key value. Only the current partition is not limited. This partition uses a specific virtual value: NULL for MS SQL Server and MAXVALUE for Oracle databases.
A partition change means that a new partition is inserted in the relevant partition's (virtual) upper limit and the oldest partition is deleted. In Oracle, you can directly delete the partition; the MS SQL Server needs a staging table for this purpose.
Partition Key Turnaround Mode
The system automatically and regularly checks the maximum ID usages of all the existing partitions and stores the results in the keys MAX_ENTRIES_AH, MAX_ENTRIES_MELD and MAX_ENTRIES_XAO of the variable UC_ILM_SETTINGS. For a partition change, the system checks the currently highest IDs and calculates whether less than three times the specified value of MAX_ENTRIES_AH, MAX_ENTRIES_MELD and MAX_ENTRIES_XAO can be used until the limit is reached. If so, the system automatically activates Partition Key Turnaround mode.
With this special mode being active, the new partitions are now inserted in the lower number range (starting with 1,000,000) and numbering starts from the beginning. This specific mode remains active until all partitions that include the upper number ranges have been removed and a constant value range up to 2,147,483,647 has become available again.
Partitioning in Oracle differs from partitioning in MS SQL Server databases, and therefore their Partition Key Turnaround patterns are also different. Thus the processes are written per database type.
Oracle
Base scenario:
Maximum usage (MAX_ENTRIES_*): 1,000,000
Currently highest RunID: 2,145,000.000
Online partitions: 4
| Partition | Upper limit |
|---|---|
| P11 | 2,142,483,647 |
| P12 | 2,143,483,647 |
| P13 | 2,144,483,647 |
| P14 | MAXVALUE |
Technically, P11 ranges from 1,000,000 to 2,142,483,647, but logically it ranges from 2,141,483 647 to 2,142,483,647 because the AE only uses this section for its data records.
The partition should now be changed. The difference between the currently highest RunID (2,145,000,000) and the maximum value (2,147,483,647) is less than three times the value of MAX_ENTRIES_* (1,000,000); therefore, the system switches to Partition Key Turnaround Mode:
The oldest partition (P11) is split and a new partition (P15) is inserted in the lower number range (starting with 1,000,000). The upper limit of the new partition is three times the maximum value. In our case, the new partition's (P15) upper limit is 4,000,000.
When P15 has been inserted, the system removes the oldest partition P11 and four partitions are then online again.
This procedure is repeated when the next partition change takes place:
The new partition's upper limit is again three times the maximum value with the upper limit of P15 being the starting point for the calculation. The currently oldest partition P12 is split at the value 7,000,000. In doing so, the new partition P16 is created and P12 is removed.
This procedure is repeated until no more partitions are available in the upper number range. In our case, this will happen after one further partition change (P17).
Deleting the open-ended partition P14 now has the effect that the system does not split the partition but it inserts a new open-ended partition (P18) instead.
This ends Partition Key Turnaround Mode and the normal mode is activated again. The RunIDs are now allocated starting with the lower number range.
The following applies:
- A further partition change is required if the Partition Key Turnaround Mode is active.
- The old partitions must still be deleted in order to ensure that the ones that include the upper number ranges are removed and the system can switch back to normal mode.
- Automic recommends monitoring the RunID usage in order to ensure that the limit of three times the maximum value is not exceeded. The partition must be changed ahead of schedule if this limit cannot be kept.
SQL Server
Base scenario:
Maximum usage (MAX_ENTRIES_*): 1,000,000
Currently highest RunID: 2,145,000,000
Online partitions: 4
| Partition | Upper limit |
|---|---|
| P1 | 2,142,483,647 |
| P12 | 2,143,483,647 |
| P13 | 2,144,483,647 |
| P14 | NULL |
Because a lower limit is defined in MS SQL Server, the lowest partition P1 is empty. Technically, you can use it, but logically it is empty because AE does not use it. It is not shown in the Administration perspective.
Therefore, the lowest logical partition is P12.
The last partition P14 is open ended.
The partition should now be changed. Partition Key Turnaround Mode is activated because the difference between the highest RunID and the maximum limit lies below three times the value of MAX_ENTRIES_*.
MS SQL Server defines a lower limit. Therefore, the lowest partition P1 is split at 1,000,000 so that the lower area also includes a partition. This lower area remains partition P1 and is reserved for the values below 1,000,000. It is still not used. The split creates the new partition P15 which inherits the upper limit of the previously smallest partition.
Technically, P15 now ranges from 1,000,000 to 2,142,483,647. However, this limit will continuously change because of the partition changes that will take place.
The oldest partition P12 is then deleted, whereby P15 automatically inherits this partition's upper limit. Technically, P15 now ranges from 1,000,000 to 2,143,483,647.
The next partition change splits the partition 15: P15 obtains the actual maximum usage value of 1,000,000 as its upper limit with the calculation starting at P1's upper limit. As a result, P15's upper limit is 2,000,000. The oldest partition P13 is now deleted.
Technically, the new partition P16 ranges from 2,000,000 to 2,144,483,647 because it inherits the upper limit of P13.
The current situation is:
With the last partition change, P16 obtains the actual maximum usage value and is split at 3,000,000. Partition P14 is deleted and the new partition P17 is open ended. Partition Key Turnaround Mode ends and the system runs in normal mode again.
The following applies:
- Make sure that the partitions are changed on a regular basis if the maximum usage value of 2,147,483,647 is almost reached.