Rolling Window clause

Use the Rolling Window clause to define a range-interval distributed storage strategy for a table and for its indexes, and to define a purge policy for detaching excess fragments. Like other tables with range-interval fragmentation, new interval fragments of each rolling window table are created automatically by the database server to store new rows with fragment-key values outside the range of any current fragment.

After the set of rolling fragments exceeds a user-defined "window" that the purge policy defines for the quantity of the fragments, or for the allocated storage size, the database server identifies and detaches the excess fragments from all the rolling window tables in its databases. By default, their purge policies are enforced as a daily task of the Scheduler.

This syntax fragment is part of the Interval fragment clause.

Rolling Window (no size limit)

Rolling Window (with size limit)

Notes:


Element	Description	Restrictions	Syntax
quantity	Maximum number of rolling interval fragments	Must be an integer greater than zero. User-defined range fragments are not included in this limit.	Literal integer
size	Upper limit on total storage size of the table	Must be greater than zero	Literal integer
units	Abbreviated unit of total mass storage for the table	Must be `K`, `KB`, `KiB`, `M`, `MB`, `MiB`, `G`, `GB`, `GiB`, `T`, `TB`, `TiB` (or lowercase forms of these letters). Any trailing characters cause a syntax error.	Unquoted character string

Usage

The ROLLING FRAGMENTS and LIMIT TO keyword options of the INTERVAL fragment clause, which define a Rolling Window distributed storage strategy, can enable the automatic creation of new fragments, based on an interval value expression. This Rolling Window clause of the CREATE TABLE statement resembles in its syntax, but is not identical to, the Rolling Window clause of the ALTER FRAGMENT MODIFY INTERVAL statement.

Unlike ordinary range-interval distributed storage, which creates new fragments, but makes no provision for managing the growth over time in table size, the Rolling Window option defines an upper limit on the current number of interval fragments, or on the total size of storage allocated for the table and its indexes, or both limits. After either limit is exceeded, the database server automatically archives or destroys what the Rolling Window clause identifies as excess fragments, and replaces those with new interval fragments, based on a purge policy that the Rolling Window clause defines.

Tables whose range-interval distributed-storage strategy includes the Rolling Window clause are called rolling window tables. Interval fragments that the database server creates for Rolling Window tables are called rolling fragments.

Unlike the round-robin fragments of tables that enabled AUTOLOCATE configuration or session environment settings create, the dynamic "window" of rolling fragments that this clause defines can support fragment elimination in queries in which fragment-key values in numeric, DATE, and DATETIME expressions are correlated with query predicates. Another efficiency of rolling window tables is the automated archiving or destruction of excess fragments when the Scheduler enforces the purge policy by running the purge_tables task.

The Rolling Window clause defines either or both of the following criteria for automatically removing an existing fragment from the table:

The ROLLING FRAGMENTS keywords specify a limit on how many interval fragments of the table can exist concurrently.
The LIMIT TO keywords specify a limit on the total storage space allocated for the table and for its indexes.

When either of these limits is exceeded, the excess interval fragments are automatically destroyed or detached by the database server, as specified by the DISCARD or DETACH keywords respectively. These specifications define a purging policy for the table. This policy limits how much data the table can store by defining criteria for automatically removing an existing fragment, and for automatically replacing those fragments with new empty fragments in which to insert new data records.

Purge policies for rolling window tables

The Rolling Window clause defines a purge policy for the table. This purge policy limits how much data the table can store by defining criteria for automatically detaching table fragments, after the table reach a user-specified limit on the quantity of rolling fragments, or on the total size of allocated storage. When the purge policy is enforced, the database server automatically replaces detached fragments with new empty fragments in which to insert new data records.

After the purge policy limit is reached, which fragments qualify to be detached is determined by the keywords that define the limit, and by the keywords that define the actions of the purge policy:

For a number-of-fragments limit specified with the ROLLING INTERVALS keywords, only interval fragments are considered. These are detached in order of lowest fragment-key value, as indicated by the sysfragments.evalpos value for the fragment in the system catalog.
This behavior for selecting the interval fragments to detach is equivalent to what the INTERVAL FIRST keywords specify with the LIMIT TO option. The ROLLING INTERVALS option, however, does not support the explicit INTERVAL FIRST keywords, nor any subsequent LIMIT TO option keywords after the DETACH or DISCARD purging specification.
The ROLLING INTERVALS option also makes no provision for detaching range fragments, because range fragments are preserved empty after DETACH or DISCARD purging. For this reason, including range fragments as ROLLING INTERVALS options, which the Rolling Window clause syntax does not support, would achieve no reduction in the number of fragments remaining in a table.
For an allocated storage-size limit specified with the LIMIT TO keywords, three keyword options can indicate which fragment to detach:
- If the ANY keyword immediately follows the DETACH or the DISCARD keyword, a range or interval fragment will be detached, starting with the fragment having the lowest sysfragments.evalpos value. Purge policies specifying ANY for what fragments can be detached can reduce the current size of allocated storage, but as indicated above, detaching range fragments cannot reduce the total number of fragments.
- If the INTERVAL ONLY keywords are specified, only interval fragments are detached, again starting with the fragment having the lowest sysfragments.evalpos value, a value correlated with the age of the rows in the fragment.
  If no interval fragment exists, then the database server will not be able to satisfy the LIMIT clause restriction. If this occurs for an existing rolling window table, you might consider using the ALTER FRAGMENT MODIFY INTERVAL statement to change the purge policy, so that range fragments can be detached. This can be done by replacing the INTERVAL ONLY keywords with the ANY or INTERVAL FIRST keywords. Alternatively, you might use ALTER FRAGMENT to increase the LIMIT TOsize value, if your storage resources can support a larger size limit.
- If the INTERVAL FIRST keywords immediately follow the DETACH or DISCARD keyword, the database server detaches interval fragments first, starting with the lowest sysfragments.evalpos value, until the allocated storage size requirement has been met.
If he Rolling Window clause includes the LIMIT TO keywords, but none of the above options for which fragment to detach, then by default the INTERVAL FIRST policy determines which fragments to detach.
If after having detached all interval fragments, the storage size limit has not been met, the database server, as a safety measure, detaches range fragments, starting with the lowest. In any case, when range fragments are being detached or discarded, they are replaced with new empty fragments for storing the same ranges of values, so that the schema of the table is preserved.

Disposition of the data in purged fragments

The Rolling Window clause provides two keyword options, DETACH and DISCARD, for automated processing of the detached fragments of rolling window tables. There is no default value for this choice of keywords. The database server returns an error if the Rolling Window clause includes neither the DETACH nor the DISCARD keyword.

Use DETACH to attach the fragments into independent tables that the database server automatically creates, and whose table identifiers are of this form:
```
< original_table_name >_< lower value >_< higher value >
```
Here lower_value and higher_value are the minimum and maximum values of the interval range for that fragment, before it was detached.
If a table of that name already exists, a numeric counter is appended after the higher value, beginning with _1 for the first additional table:
```
< original_table_name >_< lower value >_< higher value >_1 
```
and so forth, with _2 appended to the next table name (or a larger integer is appended, if appending _2 does not produce a unique table name).
Use DISCARD to destroy the detached fragments.
The DISCARD keyword specifies that successfully detached fragments be dropped, so that when the purge policy is enforced, unneeded data records are removed in a timely manner. In this way, the number of rolling fragments or the total amount of storage space for the rolling window table is constrained to the stipulated value.

These disposition options are designed to automate space management for tables fragmented by range interval, so that unneeded data is removed in a timely manner, and storage space is contained to the stipulated amount. The alternative to discarding data is to detach fragments. This provides an opportunity to recover from incorrectly specified purge policies, and allows purged fragments to be attached (or their data otherwise moved) to archival tables.

Enforcing a purge policy

In a database with finite storage, DML or load operations that insert new rows, including rows outside the range of existing fragments, can result in an allocated storage size or a quantity of interval fragments that exceeds a limit (or both limits) that the Rolling Window clause specified for a rolling window table, or for multiple rolling window tables.

A rolling window table's purge policy not immediately enforced, however, at the moment when its limit is exceeded

Purge policies are designed to be enforced daily as a Scheduler task at a time when the required ALTER FRAGMENT DETACH and ALTER FRAGMENT ATTACH operations that remove and process fragments of the rolling window table are unlikely to conflict with access attempts by concurrent users. By default, purge policies are enforced daily, at 00:45 local time. For more information, see the description of the built-in purge_tables task of the Scheduler in your HCL OneDB™ Administrator's Guide.

Purge policies also can be manually enforced by running the syspurge( ) system function. After the DBA invokes this function, the database server inspects the system catalog, and identifies any rolling window tables whose purging policy has been exceeded. The database server then either discards or detaches, as specified by the purge policy, qualifying rolling fragments until the purging policy is satisfied, or until no more rolling fragments can be removed. The syspurge( ) function requires no arguments, but accepts an optional argument that enables online log diagnostics.

Modifying, dropping, or adding a purge policy

You can use the ALTER FRAGMENT statement to change or drop the purge policy of a rolling window table, or to change a table that was created with some other storage option into a rolling window table. Simply by adding a purge policy, for example, the Rolling Window clause of the ALTER FRAGMENT statement can change a table that uses simple range-interval fragmentation into a rolling window table.

The Rolling Window clause of the CREATE TABLE statement supports a subset of the syntax of the Rolling Window clause in ALTER FRAGMENT ON TABLE . . . MODIFY INTERVAL statements.

If you are dissatisfied with the purge policy of an existing rolling window table, the ALTER FRAGMENT statement can modify it in several ways, including these:

Change the ROLLING FRAGMENTS or LIMIT TO specifications,
Replace the DETACH or the DISCARD keyword of a purge policy
Suspend a purge policy with the DISABLE keyword option
Resume a suspended purging policy while the ENABLE keyword
Remove the purge policy and the rolling fragments of a rolling window table

To change a rolling window distributed storage strategy into a simple range-interval fragmentation strategy, you can run the ALTER FRAGMENT MODIFY INTERVAL DROP ALL ROLLING statement for the table. You should first archive the rows in any non-empty rolling interval fragments of the table before you do this, if you need to preserve the data.

Restrictions on rolling window tables

Tables that use the

ROLLING
INTERVALS

or LIMIT TO keywords to define a rolling window fragmentation strategy and its purge policy have the following restrictions:

The purging strategy that the Rolling Window clause defines for rolling fragments requires the database server to perform ALTER FRAGMENT DETACH operations on fragments that satisfy the DETACH or DISCARD criteria. The ALTER FRAGMENT DETACH statement is disallowed on tables with columns are primary keys that are referenced by an enabled foreign-key constraint, or on tables created with ROWIDs. For this reason, the CREATE TABLE and ALTER FRAGMENT MODIFY INTERVAL statements cannot define a fragment purging policy on tables that have primary-key constraints or ROWID shadow columns.
Any index that is defined on a rolling window table must have the same storage distribution as the rolling window table.
Only users with DBA access privileges can call routines that implement the DETACH or DISCARD options for detached rolling fragments. Users with RESOURCE access privileges can execute the syspurge( ) function, but this can enforce purging policies only on tables that they own.
The database server silently ignores any invocation of the syspurge( ) function on secondary servers in High Availability Data Replication (HDR) cluster environments. This is because cluster environments do not replicate ALTER FRAGMENT changes that the DETACH and DISCARD options trigger, which are at the core of rolling window purge policies.
Similarly, in a grid environment, purge policies on replicated tables are not enforced.

A rolling window with no storage size limit

The following example of a CREATE TABLE statement defines a range-interval distributed storage strategy, including p4 as a NULL fragment to store rows with no value in the order_date fragment-key column. Because the interval within the range of this fragment key is defined as one month, and the interval transition value is the first day of 2014, the first interval fragment will be generated when a record is inserted with an order_date value in a year later than 2013. Successive interval fragments will be stored in the dbspaces dbs1, dbs2, and dbs3 in round-robin fashion:

CREATE TABLE orders 
       (order_id INT, cust_id INT, 
        order_date DATE, order_desc CHAR (1024))
   FRAGMENT BY RANGE (order_date)
   INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
      ROLLING (3 FRAGMENTS) DETACH 
      STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES < DATE ('01/01/2014') IN dbs1,
      PARTITION p4 VALUES IS NULL in dbs3;

In the example above, the Rolling Window clause sets at 3 the maximum number of rolling interval fragments. If rows will be added in each of the first three months of 2014, three rolling fragments will be generated by March of that year, because each new interval fragment stores data from only a single month. If a 4th interval fragment is created in April, this will exceed the purge policy limit on rolling fragments. Because no limit on storage size is specified, the default INTERVAL FIRST criterion will detach the interval fragment whose evalpos value is smallest among the four rolling fragments. That fragment will be attached to another table, rather than destroyed, because the purge policy specifies DETACH, rather than DISCARD.

A rolling window with a storage size limit

The following range-interval distributed storage strategy for the employee table uses the value in INTEGER column emp_id as the fragment-key column, and 1000 is the interval within the range of this fragment key for rolling interval fragments. Among the three range fragments, the last has an interval transition value of 20000, implying that the first rolling nterval fragment will be generated when a record is inserted with an emp_id value of 20002 or larger. Rolling interval fragments will again be stored in the dbspaces dbs1, dbs2, and dbs3 in round-robin fashion:

    CREATE TABLE employee 
                 (emp_id INTEGER, emp_name CHAR(64), 
                  ssn CHAR(12), basepay FLOAT, varpay FLOAT, 
                  dept_id SMALLINT, hire_date DATE)
        FRAGMENT BY RANGE(emp_id)
        INTERVAL(1000) 
            ROLLING ( 10 FRAGMENTS ) 
            LIMIT TO 100000MiB DETACH ANY
            STORE IN (dbs1, dbs2, dbs3)
            PARTITION p1 VALUES < 5000 IN dbs0,
            PARTITION p2 VALUES < 10000 IN dbs0,
            PARTITION p3 VALUES < 20000 IN dbs4;

Here the Rolling Window clause sets at 10 the maximum number of rolling interval fragments. The purge policy will not be enforced until either of the following events occur:

The database server creates an eleventh rolling fragment, after a record is inserted outside the ranges of any of the three 3 fragments or, by the time an 11th interval fragment is needed, the 10 rolling interval fragments.
The total storage space that the database server has allocated for the employee table and its indexes exceeds 100000 megabytes.

If the limit of the 10 rolling intervals is exceeded before the storage size limit, the database server will detach the interval fragment with the smallest evalpos value among the 11 rolling fragments.

If the limit of 100000 megabytes is exceeded before the limit on the number if interval fragments, the DETACH ANY option allows the database server to select any range fragment or interval fragment to detach.

In either case, that fragment will be attached to another table, rather than destroyed, because the purge policy specifies DETACH, rather than DISCARD.