ST_create_index

Use this stored procedure a create spatial grid index on a spatial column to help optimize spatial queries.

The column that you want to index must be a spatial data type that adheres to the following guidelines:
  • The column name cannot be qualified
  • If the column is not the ST_Point data type, then the LOB table space that stored the corresponding BLOB column data must exist. Also, if the table space that contains the base table is LOGGED, then the LOB table space must be created with LOGGED, too.
  • The column cannot have any field procedure or security label defined.
  • Only one spatial index is allowed on a column with a spatial data type.

Determining the correct grid size for a spatial grid index takes experience. Set the grid size in relation to the approximate size of the object that you are indexing. A grid size that is too small or too large can decrease performance. For example, a grid size that is set too small can affect the key to object ratio during an index search. If a grid size is set too large, the initial index search returns a small number of candidates and can decrease the performance during the final table scan.

Important: Because a spatial index cannot be rebuilt, create the spatial index with the COPY YES option specified. When you specify this option, Db2 takes an image copy of the index along with an image copy of the table. Also, you cannot alter the spatial index to change any of the options that you specified when you invoked the ST_create_index stored procedure.

Authorization

The user ID under which the stored procedure is invoked must have one of the following authorities or privileges:
  • SYSADM or DBADM authority on the database that contains the table where the spatial grid index will be used
  • Ownership or INDEX privilege on the table

Syntax

Read syntax diagramSkip visual syntax diagramsysproc.ST_create_index(table_schemanull,table_name,column_name,index_schemanull,index_name,other_index_optionsnull,grid_size1,grid_size2,grid_size3, msg_code,msg_text)

Parameter descriptions

table_schema
Identifies the schema to which the table that is specified in the table_name parameter belongs. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the value in the CURRENT SCHEMA special register is used as the schema name for the table or view.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_name
Identifies the unqualified name of the table on which the index is to be defined. You must specify a non-null value for this parameter.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

column_name
Identifies the column that contains the spatial data type for the index. You must specify a non-null value for this parameter.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

index_schema
Identifies the schema to which the index that is specified in the index_name parameter belongs. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the value in the CURRENT SCHEMA special register is used as the schema name for the table or view.

The index_schema value is converted to uppercase unless you enclose it in double quotation marks.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

index_name
Identifies the name of the index that is to be created. You must specify a non-null value for this parameter.

The index_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

other_index_options
Identifies one or more valid options from the CREATE INDEX statement. For example, you can specify FREEPAGE, PCTFREE, and so on. This parameter is nullable. The following options are not valid for a spatial index:
  • CLUSTER
  • PARTITIONED
  • PARTITIONED BY
  • DEFER YES

The data type of this parameter is VARCHAR(1024).

grid_size1
A number that indicates the granularity of the smallest index grid. You must specify a non-null value for this parameter.

The data type of this parameter is DOUBLE.

grid_size2
A number that indicates either that there is not a second grid for this index, or the granularity of the second index grid. You must specify a non-null value for this parameter. Specify 0, if there is not a second grid. If you want a second grid for the index, then you must specify a grid size that is larger than the value in grid_size1. This value is commonly two to five times larger than the prior grid size.

The data type of this parameter is DOUBLE.

grid_size3
A number that indicates either that there is not a third grid for this index, or the granularity of the third index grid. You must specify a non-null value for this parameter. Specify 0, if there is not a third grid. If you want a third grid for the index, then you must specify a grid size that is larger than the value in grid_size2. This value is commonly two to five times larger than the prior grid size.

The data type of this parameter is DOUBLE.

Output parameters

msg_code
Specifies the message code that is returned from the stored procedure. The value of this output parameter identifies the error, success, or warning condition that was encountered during the processing of the procedure. If this parameter value is for a success or warning condition, the procedure finished its task. If the parameter value is for an error condition, no changes to the database were performed.

The data type of this output parameter is INTEGER.

msg_text
Specifies the actual message text, associated with the message code, that is returned from the stored procedure. The message text can include additional information about the success, warning, or error condition, such as where an error was encountered.

The data type of this output parameter is VARCHAR(4096).

When the message code that is returned is 0 (zero), the message text parameter is set to null.

Example

This example shows how to use a Db2 CALL statement to invoke the ST_create_index stored procedure. This example uses a Db2 CALL statement to create a spatial index named INDEXDEMO on column LOCATION in table OFFICE with the following grid sizes values:
  • gridSize1: 10.0
  • gridSize2: 20.0
  • gridSize3: 35.0
call sysproc.ST_create_index(NULL,'OFFICE', 'LOCATION',NULL,'INDEXDEMO',
NULL,10.0,20.0,35.0,?,?)

The two question marks at the end of this CALL statement represent the output parameters, msg_code and msg_text. The values for these output parameters are returned after the stored procedure is called.