Automatic Table Partition Creation and Purging Based on Date Expressions

I propose the addition of a native feature in Db2 LUW for automatic table partition management based on a date or timestamp column. Specifically:

Automatic Partition Creation:
Allow users to define partitioning expressions (e.g., daily, monthly, yearly) with forward-looking ranges, such as:

• For daily partitions, always maintain partitions at least 1 week in advance.
• For monthly partitions, always maintain at least 3 months in advance.
• For yearly partitions, maintain at least 2 years in advance.

Automatic Partition Purging:
Allow users to define retention criteria (e.g., KEEP 6 MONTHS, KEEP 2 YEARS), so Db2 can automatically drop (purge) the oldest partitions that fall outside the retention policy.

Declarative Syntax:
Users should be able to define these policies declaratively, either in DDL or via table properties.

Example Use Case:
Similar to the StarRocks table partitioning feature, where partitions are created/dropped dynamically based on a date column and retention policy.

Benefits:

• Reduces manual intervention for partition management.
• Prevents data skew and improves query performance by managing partition sizes.
• Simplifies data lifecycle management and automates compliance with retention policies.
• Aligns Db2 with modern data warehouse systems that offer dynamic partition management (e.g., StarRocks, ClickHouse, BigQuery).

Additional details (optional):
Example DDL 

CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE (sale_date) ( AUTOMATIC ( INTERVAL 1 MONTH PRECREATE 3 MONTHS RETAIN 12 MONTHS ) );

Or via ALTER TABLE syntax:

ALTER TABLE sales SET PARTITION POLICY ( INTERVAL 1 MONTH, PRECREATE 3 MONTHS, RETAIN 12 MONTHS );

This would greatly enhance automation and data lifecycle management in Db2, especially for time-series and large fact tables.

Thanks and Best Regards,

Srinivas Suryadevara