Rolling Window clause

Usage

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

Modifying rolling window tables

The Rolling Window clause of the ALTER FRAGMENT MODIFY INTERVAL statement resembles in its syntax, but is not identical to, the Rolling Window clause of the CREATE TABLE statement. The Rolling Window clause of ALTER FRAGMENT supports the following functionality:

• You can define a purge policy for a table that uses range-interval fragmentation.
• You can modify an existing purge policy by any of the following changes:
  • Changing the ROLLING FRAGMENTS value for quantity
  • Changing the LIMIT TO value for size
  • Replacing the DETACH or DISCARD keyword with the DETACH or DISCARD keyword.
  • Replacing the ANY or INTERVAL FIRST or INTERVAL ONLY keyword option.
  If you delete the ANY or INTERVAL FIRST or INTERVAL ONLY keyword specification without replacement, the default purge policy action is INTERVAL FIRST. (For more information about the effects of these keywords on which qualifying fragments will be purged, see the topic Interval fragment clause.)
• You can specify the INTERVAL DISABLED keywords to disable interval fragmentation for a rolling window table, thereby suspending its purge policy.
• You can specify the INTERVAL ENABLED keywords to restore interval fragmentation (and re-enable the purge policy) for a table for which interval fragmentation and the creation and archiving or destruction of rolling fragments had been disabled.
• You can specify the DROP ALL ROLLING keywords to remove an existing purge policy. The effect is to change the rolling window table to a table fragmented by interval.

If you intend to suspend the current purge policy temporarily, and subsequently to restore the same purge policy, you should use the INTERVAL DISABLED keywords, rather than the DROP ALL ROLLING keywords.

Enforcing a purge policy

A rolling window table's purge policy is not immediately enforced when the total allocated storage size or the total number of interval fragments exceeds the limit that the Rolling Window clause specifies.

Purge policies are designed to be enforced daily as a Scheduler task at a time when the required DETACH and ATTACH operations on 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 the syspurge() 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.

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 only enforce purging policies 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. Similarly, in grid environments, purge policies on replicated tables are not enforced. This is because grid environments and 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.

Examples of modifying an existing rolling window table

The examples of ALTER FRAGMENT ON TABLE . . . MODIFY INTERVAL statements that follow all change the current rolling-window options of a table called window_orders that the following CREATE TABLE statement defined:

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

In the CREATE TABLE statement example above, the Rolling Window clause sets at 3 the maximum number of rolling interval fragments. If rows are be added in each of the first three months of 2015, 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 5th interval fragment is created in May, 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.

ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL statements that follow illustrate various changes to the original Rolling Window distribution scheme, as defined above for the window_orders table.

• Set a maximum size limit on the data stored in the table:

  ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL 
     LIMIT TO 30 MiB DETACH INTERVAL ONLY; 

  This sets a 30 megabyte limit on the total storage size of the window_orders table, and stipulates that range fragments like p0 cannot be detached to reduce the current size below that limit. The maximum number of interval fragments (4) and the disposal mode of the purge policy (DETACH) are unchanged. Note that the actual storage size could exceed the new limit until the Scheduler enforces the purge policy. At that point, however, at least one interval fragment would be detached, in an effort to comply with the LIMIT TO setting.

  The ANY, or INTERVAL FIRST, or INTERVAL ONLY option for the fragments to purge cannot be reset unless you also include a LIMIT TO specification in the Rolling Window clause of the ALTER FRAGMENT MODIFY statement.

• Changing the disposal option:

  ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL 
     ROLLING (4 FRAGMENTS) DISCARD;

  This maintains the current number of interval fragments, but changes the disposal option to DISCARD, so that the detached will be dropped from the database, in accordance with this change to the original purge policy for the window_orders table.

• Changing the number of interval fragments:

  ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL 
     ROLLING (6 FRAGMENTS);

  This increases the number of interval fragments to six, so that no new fragment is added (and no existing fragment is detached) until a new row is inserted whose fragment-key value is a DATE whose month is seven months later than the month values in he oldest fragment.

• Rolling back both previous changes to the quantity and disposal of interval fragments:

  ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL 
     ROLLING (4 FRAGMENTS) DETACH;

  This restores the original window_orders storage options, and illustrates that a single ALTER FRAGMENT ON TABLE . . . MODIFY INTERVAL statement can change more than one option of a Rolling Window strategy.
• Drop the rolling-window distributed-storage behavior of the table:

  ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL 
     DROP ALL ROLLING; 

  This drops the automatic purge policy, changing window_orders from a rolling-window table to an ordinary range-interval table. If a LIMIT TO maximum size, or if ANY or other keyword option priorities for dropping fragments had been part of the purge policy, those would have also been dropped. No existing data is destroyed, but the future distributed-storage behavior of the window_orders table will match what the following CREATE TABLE statement (with no Rolling Window clause) implies:

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

Restrictions on rolling window tables