CREATE OPCLASS statement

Use the CREATE OPCLASS statement to create an operator class for a secondary-access method.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

(explicit id cropc002) cropc002 CREATE OPCLASS [IF NOT EXISTS] opclass FORsec_acc_method STRATEGIES( <Strategy Specification>[] ) SUPPORT( support_function )
Element Description Restrictions Syntax
opclass Name that you declare here for a new operator class Must be unique among operator classes within the database Identifier
sec_acc_method Secondary-access method with which the new operator class is associated Must already exist and must be registered in the sysams table Identifier
support_function Support function that the secondary-access method requires Must be listed in the order expected by the access method Identifier

Usage

An operator class is the set of operators that support a secondary-access method for query optimization and building the index. A secondary-access method (sometimes referred to as an index access method) is a set of database server functions that build, access, and manipulate an index structure such as a B-tree, R-tree, or an index structure that a DataBlade® module provides.

The database server provides the B-tree and R-tree secondary-access methods. For more information on the btree secondary-access method, see Default Operator Classes.

Define a new operator class when you want one of the following:
  • An index to use a different order for the data than the sequence that the default operator class provides
  • A set of operators that is different from any existing operator classes that are associated with a particular secondary-access method

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if an operator class of the specified name is already registered in the current database.

You must have the Resource privilege or be the DBA to create an operator class. The actual name of an operator class is an SQL identifier. When you create an operator class, the opclass name must be unique within the database.

When you create an operator class in an ANSI-compliant database, the owner.opclass combination must be unique within the database. The owner name is case sensitive. If you do not put quotation marks around the owner name (or else set the ANSIOWNER environment variable), the name of the operator-class owner is stored in uppercase letters.

The following CREATE OPCLASS statement creates a new operator class called abs_btree_ops for the btree secondary-access method: 
CREATE OPCLASS abs_btree_ops FOR btree
   STRATEGIES (abs_lt, abs_lte, abs_eq, abs_gte, abs_gt)
   SUPPORT (abs_cmp);
An operator class has two kinds of operator-class functions:
  • Strategy functions

    Specify strategy functions of an operator class in the STRATEGY clause of the CREATE OPCLASS statement. In the preceding CREATE OPCLASS code example, the abs_btree_ops operator class has five strategy functions.

  • Support functions

    Specify support functions of an operator class in the SUPPORT clause. In the preceding CREATE OPCLASS code example, the abs_btree_ops operator class has one support function.

1 See CREATE OPCLASS statement