Dynamic Aging

Currently you have to page out your NSE partition from time to time manually. This is an important task to save frequently memory and not wasting your costly resources. But sometimes it was forgotten or there is a project freeze phase and you cannot touch your systems. Today, such tasks can be carried out completely automatically and autonomously using the feature Dynamic Aging.

Dynamic Aging makes it possible to automatically manage at which point in time older partitions can be moved to the 'warm' data store => NSE. As an extension of the Dynamic Range Partitioning feature Dynamic Aging makes it possible to automatically manage when older partitions can be moved to the 'warm' data store (Native Storage Extension) with the load-unit attribute for the partition set to PAGE LOADABLE. Warm data is then stored on disk and only loaded to memory when required.

Dynamic Aging can be used with both THRESHOLD mode (defining a maximum row count number in partition OTHERS) and INTERVAL mode (defining a maximum time or other numeric interval between each new partition). For example, for a partitioned table which is managed by dynamic partitioning and containing date/time information, you can specify an age limit (for example six months) so that when data in an old partition reaches this age it is moved to NSE storage. The interval is referred to as a 'distance threshold' value.

Prerequisites

Dynamic Aging supports INTEGER columns and DATE columns using the following data types (the NVARCHAR data type is not supported):

• INTEGER data types: INT / SMALLINT / TINYINT / BIGINT
• DATE data types: DATE / LONGDATE / SECONDDATE / TIMESTAMP

Syntax

... PARTITION OTHERS DYNAMIC [THRESHOLD <int> [DISTANCE <int> PAGE LOADABLE]]

... PARTITION OTHERS DYNAMIC INTERVAL <int + type> [DISTANCE <int + type> PAGE LOADABLE];

Interval option

CREATE COLUMN TABLE EXAMPLE (A SECONDDATE NOT NULL) PARTITION BY RANGE(YEAR(A)) (

PARTITION 0000 <= VALUES < 2021,

PARTITION 2021 <= VALUES < 2022,

PARTITION 2022 <= VALUES < 2023,

PARTITION 2023 <= VALUES < 2024,

PARTITION 2024 <= VALUES < 2025,

PARTITION 2025 <= VALUES < 2026,

PARTITION OTHERS DYNAMIC INTERVAL 1 YEAR DISTANCE 2 YEAR PAGE LOADABLE);

=> In this example, table EXAMPLE has a column for time values; it has an interval value of 1 year and a distance value of two years. This means new partitions generated by Dynamic Range will have range interval set as 1 year, and the partitions containing data older than 2 years (comparing with the latest data) will be moved to warm storage.

ALTER TABLE EXAMPLE ALTER PARTITION OTHERS DYNAMIC DISTANCE 3 YEAR PAGE LOADABLE;

Threshold option

[...] PARTITION OTHERS DYNAMIC THRESHOLD 3000000 DISTANCE 50000 PAGE LOADABLE

Deactivation

ALTER TABLE EXAMPLE ALTER PARTITION OTHERS NO DYNAMIC DISTANCE;

Manual Refresh

ALTER TABLE T1 REFRESH DYNAMIC DISTANCE

=> The background job to apply Dynamic Aging is timed to run automatically every 20 minutes but an update can be triggered manually for a single table with a REFRESH instruction

Pitfalls interval option

If a partition has been explicitly set to COLUMN LOADABLE, or if the column loadable attribute is inherited from its parent, then the partition's load unit will not  be changed by Dynamic Aging. By default, the load unit setting of a partition is DEFAULT LOADABLE, which means there is no explicit preference. In practice, this means that you must not explicitly apply the COLUMN LOADABLE load unit property to an OTHERS partition where dynamic aging is active as this would prevent any automatic conversion of data.

How you can check if interval option is active?

SELECT TABLE_NAME, DYNAMIC_DISTANCE_INTERVAL, DYNAMIC_DISTANCE_LOAD_UNIT from TABLE_PARTITIONS

Summary

The Dynamic Aging feature is genius if you are using NSE, but cannot be used for SAP applications like S/4 or BW/4, because there are no table with datetime data type. NVARCHAR is not supported by now. You can only use it in HANA cloud or in HANA 2.0 native scenarios. Sadly but it is a first step.