|
Stored Procedures in MS SQL Server |
Databases |
GCOS 8 |
|
Partitioning the AE databaseA database is an organized collection of data including relevant data structures. by using ILMStands for Information Lifecycle Management, which refers to a wide-ranging set of strategies for administering storage systems on computing devices. 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.
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.
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 open ended and not limited by a maximum value. 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 current 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.
The system automatically and regularly checks the maximum RunID usage of all the existing partitions and stores the result in the MAX_ENTRIES key of the variable UC_ILM_SETTINGS. For a partition change, the system checks the currently highest RunID and calculates whether less than three times the specified value of MAX_ENTRIES can be used until the limit is reached. If so, the system automatically activates Partition Key Turnaround Mode.
With this special mode 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.
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 only data records are written to this AE section.
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:
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 System Overview.
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: