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
- 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
- DBADM authority
Syntax
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
- 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
The definer of the index extension must have EXECUTE privilege on 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.
- 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)
- 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
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