Fragmenting by EXPRESSION

In an expression-based distribution scheme, each fragment expression in a rule specifies a storage space. Each fragment expression in the rule isolates data and aids the database server in searching for rows.

To fragment a table by expression, specify one of the following rules:

• Range rule

  A range rule specifies fragment expressions that use a range to specify which rows are placed in a fragment, as the next example shows:

  FRAGMENT BY EXPRESSION c1 < 100 IN dbsp1,
  c1 >= 100 AND c1 < 200 IN dbsp2, c1 >= 200 IN dbsp3

• Arbitrary rule

  An arbitrary rule specifies fragment expressions based on a predefined SQL expression that typically uses OR clauses to group data, as the following example shows:

  FRAGMENT BY EXPRESSION
  zip_num = 95228 OR zip_num = 95443 IN dbsp2,
  zip_num = 91120 OR zip_num = 92310 IN dbsp4,
  REMAINDER IN dbsp5

The USING Opclass Option (IDS)

With the USING operator class option, you can specify a nondefault operator class for the fragmentation strategy. The secondary-access method of the chosen operator class must have a B-tree index structure.

In the following example, the abs_btree_ops operator class specifies several user-defined strategy functions that order integers based on their absolute values:

CREATE OPCLASS abs_btree_ops FOR btree
   STRATEGIES (abs_lt, abs_lte, abs_eq, abs_gte, abs_gt)
   SUPPORT (abs_cmp)

For the fragmentation strategy, you can specify the abs_btree_ops operator class in the USING clause and use its strategy functions to fragment the table, as follows:

FRAGMENT BY EXPRESSION USING abs_btree_ops
   (abs_lt(x,345)) IN dbsp1,
   (abs_gte(x,345) AND abs_lte(x,500)) IN dbsp2,
   (abs_gt(x,500)) IN dbsp3

For information on how to create and extend an operator class, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

User-Defined Functions in Fragment Expressions (IDS)

For rows that include user-defined data types, you can use comparison conditions or user-defined functions to define the range rules. In the following example, comparison conditions define the range rules for the long1 column, which contains an opaque data type:

FRAGMENT BY EXPRESSION
long1 < '3001' IN dbsp1,
long1 BETWEEN '3001' AND '6000' IN dbsp2,
long1 > '6000' IN dbsp3

An implicit, user-defined cast converts 3001 and 6000 to the opaque type.

Alternatively, you can use user-defined functions to define the range rules for the opaque data type of the long1 column:

FRAGMENT BY EXPRESSION
(lessthan(long1,'3001')) IN dbsp1,
(greaterthanorequal(long1,'3001') AND 
lessthanorequal(long,'6000')) IN dbsp2,
(greaterthan(long1,'6000')) IN dbsp3

Explicit user-defined functions require parentheses around the entire fragment expression before the IN clause, as the previous example shows.

User-defined functions in a fragment expression can be written in SPL or in the C or Java language. These functions must satisfy four requirements:

• They must evaluate to a Boolean value.
• They must be nonvariant.
• They must reside within the same database as the table.
• They must not generate OUT nor INOUT parameters.

For information on how to create UDRs for fragment expressions, refer to IBM Informix User-Defined Routines and Data Types Developer's Guide.

Using the REMAINDER Keyword

Use the REMAINDER keyword to specify the storage space in which to store valid values that fall outside the specified expression or expressions. If you do not specify a remainder, and a row is inserted or updated with values that do not correspond to any fragment definition, the database server returns an error.

The following example uses an arbitrary rule to define five fragments for specific values of the c1 column, and a sixth fragment for all other values:

3CREATE TABLE T1 (c1 INT) FRAGMENT BY EXPRESSION 
3PARTITION PART_1 (c1 = 10) IN dbs1,
3PARTITION PART_2 (c1 = 20) IN dbs1,
3PARTITION PART_3 (c1 = 30) IN dbs1,
3PARTITION PART_4 (c1 = 40) IN dbs2,  
3PARTITION PART_5 (c1 = 50) IN dbs2,
3PARTITION PART_6 REMAINDER IN dbs2; 

Here the first three fragments are stored in partitions of the dbs1 dbspace, and the other fragments, including the remainder, are stored in partitions of the 3dbs2 dbspace. Explicit fragment names are required in this example, because each dbspace has multiple partitions.