Interval fragment clause

Use the Interval Fragment clause to store rows in fragments defined by one or more range expressions that evaluate to a numeric or INTERVAL data type. After you specify at least one non-NULL range for a fragment, the database server can create new interval fragments automatically during DML operations that insert new rows whose fragment key values are outside the range of any existing fragment. The interval fragmentation strategy is useful when all possible fragment-key values in a growing table are not known, and you do not want to preallocate fragments for data that is not yet there.

This syntax fragment is part of the FRAGMENT BY clause.

Interval Fragment clause

Notes:

¹ See Rolling Window clause
² Use this path no more than once


Element	Description	Restrictions	Syntax
dbspace	Name of a dbspace to store a fragment	You can specify no more than 2,048 dbspaces. All dbspaces that store the fragments must have the same page size.	Identifier
dbspace _fun	Name of a UDF that returns the name of a dbspace	The user-defined function and the returned dbspace must exist when the database server calls the UDR to allocate storage for a new fragment.	CREATE FUNCTION statement
intvl_ valexpr	Interval value expression that defines an interval size in the fragment key range	Must be a constant literal expression that evaluates to a numeric or INTERVAL value compatible with the data type of the fragment key expression	Identifier
partition	Name that you declare here for a range fragment	Must be unique among fragment names of the same table. If a table and its index use the same range interval fragmentation strategy, each index fragment must have the same name as the corresponding table fragment.	Identifier
range _expr	Constant expression that defines the upper bound for fragment key values stored in the fragment	Must be a constant literal expression that evaluates to a numeric, DATETIME, or DATE data type compatible with the data type of the fragment key expression	Constant Expressions

Usage

The Interval Fragment clause defines one or more non-overlapping intervals in the range of the fragment key expression that you specify (within parentheses) immediately after the FRAGMENT BY RANGE keywords of the FRAGMENT BY clause. The PARTITION BY RANGE keywords are a synonym for the FRAGMENT BY RANGE keywords. When a DML operation updates or inserts into the table a row that matches a range that you defined for a fragment, the database server store the new or updated row in that fragment.

You must define at least one range fragment for storing rows with non-NULL fragment-key values. You are not required to define a fragment for rows whose fragment key value is NULL. If you define no fragment for NULL values, however, DML operations on the table that would result in a row with a NULL fragment-key value will fail.

The Interval Fragment clause does not support a REMAINDER fragment, because the database server automatically creates new interval fragments to store inserted rows that have fragment-key values outside the range of any existing fragment.

For tables with distributed storage defined by an Interval Fragment clause that includes the Rolling Window clause, the database server automatically detaches fragments of the table after enough new interval fragments have been created to exceed a user-defined upper limit. You can use the Rolling Window clause to define either of both of the following limits:

on the current number of system-generated interval fragments,
or on the total size of storage space allocated for the table and its indexes.

For information on the syntax and behavior of Rolling Window tables, see the link in the syntax diagram above.

The fragment key for range interval fragmentation can reference only a single column. For example, the following specification is not valid:

FRAGMENT BY RANGE (basepay + varpay)

The fragment key can be a column expression, as in the following specification:

FRAGMENT BY RANGE ((ROUND(basepay))

The INTERVAL size specification

The intvl_valexpr expression that follows (within parentheses) the INTERVAL keyword defines the size of an interval within the range of fragment key values.

The data type of the intvl_valexpr expression depends on the data type of the fragment key column that followed the RANGE keyword:

If the fragment key is a numeric data type, the intvl_valexpr expression must evaluate to a numeric value. A numeric intvl_valexpr expression must be a constant expression greater than zero, with no fractional part.
If the fragment key is a DATE or DATETIME data type, the intvl_valexpr expression must evaluate to an INTERVAL value. An INTERVAL intvl_valexpr expression must be a constant expression greater than zero.

The minimum value of the intvl_valexpr expression depends on the data type of the fragment key expression.

The minimum is a second if the fragment key is a DATETIME column
The minimum is a day if the fragment key is DATE column
The minimum is 1 if the fragment key is a numeric column.

You can use a literal number or a literal INTERVAL value as the intvl_valexpr expression. You can also use the built-in NUMTODSINTERVAL, NUMTOYMINTERVAL, TO_DSINTERVAL, or TO_YMINTERVAL functions to specify the intvl_valexpr expression. For the syntax of these functions, and examples of their use in the Interval Fragment clause, see TO_YMINTERVAL function and TO_DSINTERVAL function.

If you specify no intvl_valexpr expression, the automatic creation of interval fragments is disabled, but empty parentheses are still required after the INTERVAL keyword to avoid a syntax error.

The STORE IN specification

The dbspace (or the comma-separated list of dbspace names) that follows the STORE IN keywords identifies storage spaces for new interval fragments that the server automatically creates when DML operations store rows whose fragment key values are outside the range of existing fragments. If you specify multiple dbspaces, the database server creates interval fragment in a round-robin fashion in the dbspaces specified in the STORE IN clause, and declares system-generated names for the new fragments.

The dbspaces in the STORE IN clause need not be present when the table or index is created. You can add the dbspaces to the system after creating the table or index. All of the dbspaces referenced in the Interval Fragment clause must have the same page size.

If you omit the STORE IN clause, and the table needs to store rows outside the existing interval and range fragments, the database server automatically creates new interval fragments in a round-robin fashion in the dbspaces that the PARTITION specifications list for the range expression fragments.

Instead of a list of literal dbspace identifiers, the STORE IN clause can optionally specify a user-defined function that returns the name of an existing dbspace. The identifier that you declare for this UDF is arbitrary.

The function accepts four parameters:

a table owner, of a CHAR(32) data type
a table name, of a CHAR(255) data type
a fragmentation value of the same data type as the fragment key, or a compatible type that can be implicitly cast to that data type
and a retry flag, of an INT data type.

Important: When you reference an arbitrary UDR in the STORE IN clause, however, do not specify any parameters to the UDF. All that is required is the UDF name, followed by an empty pair of parentheses, as indicated in the syntax diagram above. The database server automatically supplies a parameter list at invocation time.

User-defined range fragments

You must define at least one range fragment in the Interval Fragment clause. Each fragment declaration requires these elements:

The PARTITION keyword, followed by a name that you declare for the fragment. No other fragment of the table can have the same name.
The VALUES keyword, followed by a Boolean expression with one of the following formats:
- the less than ( < ) relational operator and a range expression defining the upper bound for fragment key values that can be stored in the fragment
- the IS NULL operator. If the fragment key can take a NULL value, you can use this to define the NULL fragment that stores only the rows with NULL as their fragment key value.
No more than one fragment can be defined by the IS NULL operator. The NULL fragment is not required, but if the NULL fragment does not exist, the database server returns an error if a user attempts to insert a row in which the fragment key column is NULL.
The IN keyword, followed by the name of the dbspace that stores the fragment. This can be a dbspace that the STORE IN specification also references, or a dbspace that is not included in the STORE IN list.

If the range fragments are not defined in ascending order, the database server sorts them in ascending order, so that the fragment in the first ordinal position has the smallest upper bound.

Two fragments in the same Interval Fragment clause cannot have the same upper bound. None of the range fragments defined in the PARTITION specifications can overlap. If an (intvl_valexpr) size specification follows the INTERVAL keyword, the database server issues an error if the difference between the range expressions that define consecutive range fragments is not the same value as the INTERVAL size specification.

The NULL fragment is not required, but the database server returns an error if a user attempts to insert a row in which the fragment key value is NULL, but no NULL fragment exists.

Important:

Output from the dbschema -ss command to display the schema of a table fragmented by a range-interval distribution scheme returns only the range fragments that the user defined in the CREATE TABLE or ALTER FRAGMENT statement.

The same is true for output from the dbexport -ss command.

System-generated interval fragments

When you use the Interval Fragment clause to define range interval fragmentation for a table or index, it is not necessary to know what the full range of fragment key values will be. When a row is inserted that does not fit in any range fragment or interval fragment, the database server automatically creates a new interval fragment to store the row, based on the interval intvl_valexp value, without DBA intervention.

The interval fragments are created in round-robin fashion in the dbspaces specified in the STORE IN clause. If this clause had been omitted, interval fragments would be created in the dbspaces that store the range fragments. If a dbspace specified for the interval fragment is full or down, the database server skips that dbspace and selects the next one in the list.

The system-generated name for interval partitions of a table or of an index is sys_evalpos, where evalpos is the sysfragments.evalpos entry for the fragment in the system catalog. If a table and its index use the same range interval fragmentation strategy, each system-generated index fragment will have the same identifier as a system-generated fragment of the table.

These automatically generated fragments correspond to parts of the fragment key range that include the new data values. Gaps can separate automatically generated interval fragments, if between two successive fragments a portion of the range that is larger than intvl_valexp includes no rows. Gaps are not allowed, however, between the fragments that you explicitly define in the Interval Fragment clause.

If you specify no intvl_valexp expression, the range fragments that you explicitly define in the Interval Fragment clause are available to store rows that have corresponding fragment key values within their range intervals, as are any existing interval fragments that were generated before the ALTER FRAGMENT statement disabled the automatic creation of interval fragments. In both cases, however, the automatic creation of new interval fragments is disabled. If a user attempts to insert a row whose fragment key value does not fall in the range of any existing fragment, the database server issues error -772, and the insertion fails.

As noted above, output from the dbschema -ss and dbexport -ss commands to display the schema of a table fragmented by range interval includes only the user-defined range fragments. No system-generated interval fragments are visible in the output display.

When data records are loaded from the dbexport data file, however, the database server can create additional interval fragments automatically,

based on the range fragments and the interval-transition fragment,
and on the fragment-key values in the inserted rows,
and on other storage specifications of the Interval Fragment clause, as registered in the system catalog.

Example: Fragment by integer values

The following is an example of a table fragmented by range interval, using an integer column as the partitioning key:

CREATE TABLE employee (id INTEGER, name CHAR(32), basepay DECIMAL (10,2), 
                       varpay DECIMAL (10,2), dept CHAR(2), hiredate DATE)
       FRAGMENT BY RANGE (id) 
       INTERVAL (100) STORE IN (dbs1, dbs2, dbs3, dbs4)
             PARTITION p0 VALUES IS NULL IN dbs0,
             PARTITION p1 VALUES < 200 IN dbs1,
             PARTITION p2 VALUES < 400 IN dbs2;

In this table

the value of the interval size is 100,
the fragment key is the value of the employee.id column,
and the VALUES IS NULL keywords define p0 as the table fragment to store rows that have no id column value.

When employee ID exceeds 199, fragments are created automatically in intervals of 100, the specified interval size.

If a row is inserted with an employee ID of 405, a new interval fragment is created to accommodate the row. The new fragment holds rows with id column values in the range >= 400 AND < 500.

If a row is updated and the employee ID is modified to 821, the database server creates a new fragment to accommodate the new row. The fragment holds rows with id column values in the range >= 800 AND < 900.

Example: Fragment by integer values

The following example uses the value of the INT column cust_id as the numeric fragment key, and defines four range fragments. Interval fragments whose interval size is 1000000 will be created by the database server for inserted rows with cust_id values that exceed 7999999:

CREATE TABLE customer (cust_id INT, name CHAR (128), street CHAR (1024),
   state CHAR (2), zipcode CHAR (5), phone CHAR (12))
   FRAGMENT BY RANGE (cust_id)
   INTERVAL (1000000) STORE IN (dbs2, dbs1)
      PARTITION p0 VALUES < 2000000 IN dbs1,
      PARTITION p1 VALUES < 4000000 IN dbs1,
      PARTITION p2 VALUES < 6000000 IN dbs2,
      PARTITION p3 VALUES < 8000000 IN dbs3;

Example: Fragment by 25-year intervals

In the following example of a DATETIME fragment key, if values of the dt1 column exceed the limit of the range fragment that the VALUES clause specifies, interval fragments will be created in the dbs1 dbspace for rows with year values after 2005 in 25-year intervals:

CREATE TABLE t1 (c1 int, d1 date, dt1 DATETIME YEAR TO FRACTION)
   FRAGMENT BY RANGE (dt1) INTERVAL (INTERVAL(25) YEAR(2) TO YEAR)
      PARTITION p1 VALUES <
      DATETIME(2006-01-01 00:00:00.00000) YEAR TO FRACTION(5) IN dbs1;

Example: Fragment by year

In this example, the value of the DATE column order_date is the fragment key, and four range fragments are defined, including p4 for rows that have NULL values for order_date. For an inserted row where the year value in order_date is later than 2007, interval fragments will be created automatically in intervals of 1 month after 01/01/2008, with successive fragments created in the dbs1, dbs2, and dbs3 dbspaces:

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')) STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES < DATE ('01/01/2005') IN dbs1,
      PARTITION p1 VALUES < DATE ('01/01/2006') IN dbs1,
      PARTITION p2 VALUES < DATE ('01/01/2007') IN dbs2,
      PARTITION p3 VALUES < DATE ('01/01/2008') IN dbs3,
      PARTITION p4 VALUES IS NULL in dbs3;

Example: Fragment by 1.5 years

In this example, the DATE fragment key is similar to the previous example, but here the interval size is specified as 1.5 years. Interval fragments will be created in intervals of 18 months (1.5 years) for order_date values after 12/31/2009:

CREATE TABLE orders1 (order_id INT, cust_id INT, order_date DATE,
                       order_desc CHAR (1024))
  FRAGMENT BY RANGE (order_date)
  INTERVAL (NUMTOYMINTERVAL (1.5,'YEAR')) STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES < DATE ('01/01/2004') IN dbs1,
      PARTITION p1 VALUES < DATE ('01/01/2006') IN dbs1,
      PARTITION p2 VALUES < DATE ('01/01/2008') IN dbs2,
      PARTITION p3 VALUES < DATE ('01/01/2010') IN dbs3;

You cannot insert rows into the orders1 table if the order_date value is missing, because no NULL fragment is defined. For the syntax to add a NULL fragment to an existing table that uses range interval fragmentation, see the ADD Clause topic of the ALTER FRAGMENT statement.

Example: Create a UDF to assign dbspaces

Here is an example of a UDF that can return a dbspace name, and a CREATE TABLE statement that defines a table fragmented by range interval, and calls that function in the STORE IN clause:

CREATE FUNCTION mydbname
   (
   owner CHAR(255),
   table CHAR(255),
   value DATE,		-- Data type must match or must be compatible
                -- with the data type of the fragment key
   retry INTEGER
   ) 
   RETURNING CHAR(255) 
   IF (retry > 0)
   THEN 
       RETURN NULL;  -- This UDF does not handle retries: if the first call
                     -- fails, an invalid dbspace is returned, and the DML
                     -- statement that requires a new fragment also fails. 
   END IF; 
   IF (MONTH(value) < 7) 
   THEN 
       RETURN "dbs1"; 
   ELSE 
       RETURN "dbs2"; 
   END IF; 
END FUNCTION;

CREATE TABLE orders 
     (
     order_num               SERIAL(1001), 
     order_date              DATE, 
     customer_num            INTEGER NOT NULL, 
     ship_instruct           CHAR(40), 
     backlog                 CHAR(1), 
     po_num                  CHAR(10), 
     ship_date               DATE, 
     ship_weight             DECIMAL(8,2), 
     ship_charge             MONEY(6), 
     paid_date               DATE
     ) 
PARTITION BY RANGE(order_date) INTERVAL(1 UNITS MONTH) 
STORE IN (mydbname()) 
PARTITION prv_partition VALUES < DATE("01/01/2010") IN mydbs;

The database server calls the specified function when a new interval fragment needs to be created for the table. If the attempted fragment creation in the returned dbspace fails, the same function is called a second time with the retry flag set, so that a different existing dbspace name can be returned. Upon a second failure, the DML statement that is being executed return an error. (The UDF in the example above does not handle retries, but returns NULL, an invalid dbspace name, if the first attempt fails.)