CREATE INDEX EXTENSION statement

The CREATE INDEX EXTENSION statement defines an extension object for use with indexes on tables that have structured type or distinct type columns.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the schema name of the index extension does not refer to an existing schema
  • CREATEIN privilege on the schema, if the schema name of the index extension refers to an existing schema
  • SCHEMAADM authority on the schema, if the schema name of the index extension refers to an existing schema
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagramCREATE INDEX EXTENSIONindex-extension-name (,parameter-name1data-type1)index-maintenanceindex-search
index-maintenance
Read syntax diagramSkip visual syntax diagramFROM SOURCE KEY(parameter-name2 data-type2)GENERATE KEY USINGtable-function-invocation
search-method-definition
Read syntax diagramSkip visual syntax diagramWHENmethod-name( ,parameter-name4data-type4 )RANGE THROUGHrange-producing-function-invocation FILTER USINGindex-filtering-function-invocationcase-expression

Description

index-extension-name
Names the index extension. The name, including the implicit or explicit qualifier, must not identify an index extension described in the catalog. If a two-part index-extension-name is specified, the schema name cannot begin with 'SYS'; otherwise, an error is returned (SQLSTATE 42939).
parameter-name1
Identifies a parameter that is passed to the index extension at CREATE INDEX time to define the actual behavior of this index extension. The parameter that is passed to the index extension is called an instance parameter, because that value defines a new instance of an index extension.

parameter-name1 must be unique within the definition of the index extension. No more than 90 parameters are allowed. If this limit is exceeded, an error (SQLSTATE 54023) is returned.

data-type1
Specifies the data type of each parameter. One entry in the list must be specified for each parameter that the index extension will expect to receive. The only SQL data types that can be specified are those that can be used as constants, such as VARCHAR, INTEGER, DECIMAL, DOUBLE, or VARGRAPHIC (SQLSTATE 429B5). The decimal floating-point data type cannot be specified (SQLSTATE 429B5). The parameter value that is received by the index extension at CREATE INDEX must match data-type1 exactly, including length, precision, and scale (SQLSTATE 428E0).

Character and graphic string data types cannot specify string units of CODEUNITS32.

index-maintenance
Specifies how the index keys of a structured or distinct type column are maintained. Index maintenance is the process of transforming the source column to a target key. The transformation process is defined using a table function that has previously been defined in the database.
FROM SOURCE KEY (parameter-name2 data-type2)
Specifies a structured data type or distinct type for the source key column that is supported by this index extension.
parameter-name2
Identifies the parameter that is associated with the source key column. A source key column is the index key column (defined in the CREATE INDEX statement) with the same data type as data-type2.
data-type2
Specifies the data type for parameter-name2; data-type2 must be a user-defined structured type or a distinct type that is not sourced on LOB, XML, or DECFLOAT (SQLSTATE 42997). When the index extension is associated with the index at CREATE INDEX time, the data type of the index key column must:
  • Exactly match data-type2 if it is a distinct type; or
  • Be the same type or a subtype of data-type2 if it is a structured type
Otherwise, an error is returned (SQLSTATE 428E0).
GENERATE KEY USING table-function-invocation
Specifies how the index key is generated using a user-defined table function. Multiple index entries may be generated for a single source key data value. An index entry cannot be duplicated from a single source key data value (SQLSTATE 22526). The function can use parameter-name1, parameter-name2, or a constant as arguments. If the data type of parameter-name2 is a structured data type, only the observer methods of that structured type can be used in its arguments (SQLSTATE 428E3). The output of the GENERATE KEY function must be specified in the TARGET KEY specification. The output of the function can also be used as input for the index filtering function specified on the FILTER USING clause.
The function used in table-function-invocation must:
  • Resolve to a table function (SQLSTATE 428E4)
  • Not be defined with PARAMETER CCSID UNICODE if this database is not a Unicode database (SQLSTATE 428E4)
  • Not be defined with LANGUAGE SQL (SQLSTATE 428E4)
  • Not be defined with NOT DETERMINISTIC (SQLSTATE 428E4) or EXTERNAL ACTION (SQLSTATE 428E4)
  • Be defined with NO SQL (SQLSTATE 428E4)
  • Not have a structured data type, LOB or XML (SQLSTATE 428E3) in the data type of the parameters, with the exception of system-generated observer methods
  • Not include a subquery (SQLSTATE 428E3)
  • Not include an XMLQUERY or XMLEXISTS expression (SQLSTATE 428E3)
  • Return columns with data types that follow the restrictions for data types of columns of an index defined without the EXTEND USING clause
If an argument invokes another operation or routine, it must be an observer method (SQLSTATE 428E3).

The definer of the index extension must have EXECUTE privilege on the function, EXECUTEIN privilege on the schema containing this function, or DATAACCESS authority on the schema containing this function.

index-search
Specifies how searching is performed by providing a mapping of the search arguments to search ranges.
WITH TARGET KEY
Specifies the target key parameters that are the output of the key generation function specified on the GENERATE KEY USING clause.
parameter-name3
Identifies the parameter associated with a given target key. parameter-name3 corresponds to the columns of the RETURNS table as specified in the table function of the GENERATE KEY USING clause. The number of parameters specified must match the number of columns returned by that table function (SQLSTATE 428E2).
data-type3
Specifies the data type for each corresponding parameter-name3. data-type3 must exactly match the data type of each corresponding output column of the RETURNS table, as specified in the table function of the GENERATE KEY USING clause (SQLSTATE 428E2), including the length, precision, and type.
SEARCH METHODS
Introduces the search methods that are defined for the index.
search-method-definition
Specifies the method details of the index search. It consists of a method name, the search arguments, a range producing function, and an optional index filter function.
WHEN method-name
The name of a search method. This is an SQL identifier that relates to the method name specified in the index exploitation rule (found in the PREDICATES clause of a user-defined function). A search-method-name can be referenced by only one WHEN clause in the search method definition (SQLSTATE 42713).
parameter-name4
Identifies the parameter of a search argument. These names are for use in the RANGE THROUGH and FILTER USING clauses.
data-type4
The data type associated with a search parameter.
RANGE THROUGH range-producing-function-invocation
Specifies an external table function that produces search ranges. This function uses parameter-name1, parameter-name4, or a constant as arguments and returns a set of search ranges.
The table function used in range-producing-function-invocation must:
  • Resolve to a table function (SQLSTATE 428E4)
  • Not include a subquery (SQLSTATE 428E3) or SQL function (SQLSTATE 428E4) in its arguments
  • Not include an XMLQUERY or XMLEXISTS expression in its arguments (SQLSTATE 428E3)
  • Not be defined with PARAMETER CCSID UNICODE if this database is not a Unicode database (SQLSTATE 428E4)
  • Not be defined with LANGUAGE SQL (SQLSTATE 428E4)
  • Not be defined with NOT DETERMINISTIC or EXTERNAL ACTION (SQLSTATE 428E4)
  • Be defined with NO SQL (SQLSTATE 428E4)
The number and types of this function's results must relate to the results of the table function specified in the GENERATE KEY USING clause (SQLSTATE 428E1) by:
  • Returning up to twice as many columns as returned by the key transformation function
  • Having an even number of columns, in which the first half of the return columns defines the start of the range (start key values), and the second half of the return columns defines the end of the range (stop key values)
  • Having each start key column with the same type as the corresponding stop key column
  • Having the type of each start key column be the same as the corresponding key transformation function column

More precisely, let a1:t1, ..., an:tn be the function result columns and data types of the key transformation function. The function result columns of the range-producing-function-invocation must be b1:t1, ..., bm:tm, c1:t1, ..., cm:tm, where m <= n and the "b" columns are the start key columns and the "c" columns are the stop key columns.

When the range-producing-function-invocation returns a null value as the start or stop key value, the semantics are undefined.

The definer of the index extension must have EXECUTE privilege on the function, EXECUTEIN privilege on the schema containing this function, or DATAACCESS authority on the schema containing this function.

FILTER USING
Allows specification of an external function or a case expression to be used for filtering index entries that were returned after applying the range-producing function.
index-filtering-function-invocation
Specifies an external function to be used for filtering index entries. This function uses the parameter-name1, parameter-name3, parameter-name4, or a constant as arguments (SQLSTATE 42703) and returns an integer (SQLSTATE 428E4). If the value returned is 1, the row corresponding to the index entry is retrieved from the table. Otherwise, the index entry is not considered for further processing.

If not specified, index filtering is not performed.

The function used in the index-filtering-function-invocation must:
  • Not be defined with PARAMETER CCSID UNICODE if this database is not a Unicode database (SQLSTATE 428E4)
  • Not be defined with LANGUAGE SQL (SQLSTATE 429B4)
  • Not be defined with NOT DETERMINISTIC or EXTERNAL ACTION (SQLSTATE 42845)
  • Be defined with NO SQL (SQLSTATE 428E4)
  • Not have a structured data type in the data type of any of the parameters (SQLSTATE 428E3)
  • Not include a subquery (SQLSTATE 428E3)
  • Not include an XMLQUERY or XMLEXISTS expression (SQLSTATE 428E3)
If an argument invokes another function or method, these rules are also enforced for this nested function or method. However, system-generated observer methods are allowed as arguments to the filter function (or any function or method used as an argument), as long as the argument results in a built-in data type.

The definer of the index extension must have EXECUTE privilege on the function, EXECUTEIN privilege on the schema containing this function, or DATAACCESS authority on the schema containing this function..

case-expression
Specifies a case expression for filtering index entries. Either parameter-name1, parameter-name3, parameter-name4, or a constant (SQLSTATE 42703) can be used in the searched-when-clause and simple-when-clause. An external function with the rules specified in FILTER USING index-filtering-function-invocation may be used in result-expression. Any function referenced in the case-expression must also conform to the rules listed under index-filtering-function-invocation. In addition, subqueries and XMLQUERY or XMLEXISTS expressions cannot be used anywhere else in the case-expression (SQLSTATE 428E4). The case expression must return an integer (SQLSTATE 428E4). A return value of 1 in the result-expression means that the index entry is kept; otherwise, the index entry is discarded.

Notes

  • Creating an index extension with a schema name that does not already exist will result in the implicit creation of that schema, provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.

Example

The following example creates an index extension called grid_extension that uses a structured type SHAPE column in a table function called gridEntry to generate seven index target keys. This index extension also provides two index search methods to produce search ranges when given a search argument. 
   CREATE INDEX EXTENSION GRID_EXTENSION (LEVELS VARCHAR(20) FOR BIT DATA)
     FROM SOURCE KEY (SHAPECOL SHAPE)
     GENERATE KEY USING GRIDENTRY(SHAPECOL..MBR..XMIN,
                                   SHAPECOL..MBR..YMIN,
                                   SHAPECOL..MBR..XMAX,
                                   SHAPECOL..MBR..YMAX,
                                   LEVELS)
     WITH TARGET KEY (LEVEL INT, GX INT, GY INT,
                      XMIN INT, YMIN INT, XMAX INT, YMAX INT)
     SEARCH METHODS
     WHEN SEARCHFIRSTBYSECOND (SEARCHARG SHAPE)
     RANGE THROUGH GRIDRANGE(SEARCHARG..MBR..XMIN,
                                   SEARCHARG..MBR..YMIN,
                                   SEARCHARG..MBR..XMAX,
                                   SEARCHARG..MBR..YMAX,
                                   LEVELS)
     FILTER USING
       CASE WHEN (SEARCHARG..MBR..YMIN > YMAX) OR
          SEARCHARG..MBR..YMAX < YMIN) THEN 0
       ELSE CHECKDUPLICATE(LEVEL, GX, GY,
                                  XMIN, YMIN, XMAX, YMAX,
                                  SEARCHARG..MBR..XMIN,
                                  SEARCHARG..MBR..YMIN,
                                  SEARCHARG..MBR..XMAX,
                                  SEARCHARG..MBR..YMAX,
                                  LEVELS)
       END
     WHEN SEARCHSECONDBYFIRST (SEARCHARG SHAPE)
     RANGE THROUGH GRIDRANGE(SEARCHARG..MBR..XMIN,
                                   SEARCHARG..MBR..YMIN,
                                   SEARCHARG..MBR..XMAX,
                                   SEARCHARG..MBR..YMAX,
                                   LEVELS)
     FILTER USING
       CASE WHEN (SEARCHARG..MBR..YMIN > YMAX) OR
          SEARCHARG..MBR..YMAX < YMIN) THEN 0
       ELSE MBROVERLAP(XMIN, YMIN, XMAX, YMAX,
                                   SEARCHARG..MBR..XMIN,
                                   SEARCHARG..MBR..YMIN,
                                   SEARCHARG..MBR..XMAX,
                                   SEARCHARG..MBR..YMAX)
       END