Expression Fragment Clause

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.

This syntax fragment is part of the FRAGMENT BY clause.

Expression Fragment Clause
Expression Fragment Clause 

   .-,------------------------------------------------.   
   V                                                  |   
|----+-----------------+--+-expr-------+--IN--dbspace-+----------+->
     '-PARTITION--part-'  '-(--expr--)-'              '-INDEX OFF'    

>--+------------------------------------------------+-----------|
   '-,--+-----------------+-REMAINDER--IN--dbspace-'+----------+   
        '-PARTITION--part-'                        '-INDEX OFF-'
Element Description Restrictions Syntax
part Name of a fragment Required if part is stored in the same dbspace as another fragment of this table. Must be unique among names of fragments of the same table. Identifier
dbspace dbspace to store the table fragment You can specify no more than 2,048 dbspaces. All dbspaces that store the fragments must have the same page size. Identifier
dbspace
expr An expression, based on column values, defining a fragment Must return a Boolean value (true or false). Data values must be from a single row of the table. Expression
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;
Warning: See the note about the DBCENTURY environment variable and date values in fragment expressions in the section Logging Options.

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: 
CREATE TABLE T1 (c1 INT) FRAGMENT BY EXPRESSION 
   PARTITION PART_1 (c1 = 10) IN dbs1,
   PARTITION PART_2 (c1 = 20) IN dbs1,
   PARTITION PART_3 (c1 = 30) IN dbs1,
   PARTITION PART_4 (c1 = 40) IN dbs2,  
   PARTITION PART_5 (c1 = 50) IN dbs2,
   PARTITION 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 named fragments of the dbs2 dbspace. Explicit fragment names are required in this example, because each dbspace has multiple partitions.

Fragmentation in NLCASE INSENSITIVE databases

In databases with the NLSCASE INSENSITIVE property, operations on NCHAR and NVARCHAR data ignore lettercase, so that the database server treats case variants among strings composed of same sequence letters as duplicates. If the fragment keys for a table that is fragmented by expression are NCHAR or NVARCHAR columns, then each fragment defined by a character expression stores all lettercase variants that match the expression that defines the fragment. For example, for the expression lname = 'Garcia' where lname is a column of type NCHAR or NVARCHAR, rows with the following values in that column would all be stored in the same fragment, because the case-insensitive expression evaluates to TRUE for these (and similar) string values:
'Garcia' 'garcia' 'GARCIA' 'GarCia' 'gARCia'

For more information about NLSCASE INSENSITIVE databases, see CREATE DATABASE statement, Duplicate rows in NLSCASE INSENSITIVE databases, and NCHAR and NVARCHAR expressions in case-insensitive databases.