create_idx

Use the create_idx command to create a 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 LOG YES, then the LOB table space must be created with LOG YES, 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 create_idx command.

Authorization

The user ID under which the command 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

Command syntax

DSN5SCLP /create_idx DALLAS
	[-tableSchema tab_schema]
	-tableName tab_name
	-columnName col_name
	[-indexSchema idx_schema]
	-indexName idx_name
	[-otherIdxOpts other_idx_opts]
	-gridSize1 gsize1
	-gridSize2 gsize2
	-gridSize3 gsize3

Parameter descriptions

All parameters are required and case-sensitive unless otherwise indicated.

-tableSchema
Identifies the schema to which the table that is specified in the -tableName parameter belongs. This parameter is optional.

If you specify this parameter, then a value must be present. If you do not specify this parameter, the CURRENT SCHEMA special register is used as the schema name for the table.

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

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

-indexSchema
Identifies the schema to which the index that is specified in the index_name parameter belongs. This parameter is optional.

If you specify this parameter, then a value must be present. If you do not specify this parameter, the CURRENT SCHEMA special register is used as the schema name for the index.

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

-otherIdxOpts
Identifies one or more valid options from the CREATE INDEX statement. This parameter is optional.
For example, you can specify FREEPAGE, PCTFREE, and so on. If you specify a value for this parameter, the value must be non-empty. The following options are not valid for a spatial index:
  • CLUSTER
  • PARTITIONED
  • PARTITION BY
  • DEFER YES

The value of this parameter is not case-sensitive.

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

-gridSize2
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-empty 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 -gridSize1. This value is commonly two to five times larger than the prior grid size.

-gridSize3
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-empty 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 -gridSize2. This value is commonly two to five times larger than the prior grid size.

Examples

Example 1

This example shows the create_idx command with only the required parameters specified.
DSN5SCLP /create_idx DALLAS +
-tableName POINTS -columnName P +
-indexName P_IDX +
-gridSize1 10.0 -gridSize2 20.0 -gridSize3 35.0

Example 2

This example shows the create_idx command with all of the parameters specified.
DSN5SCLP /create_idx DALLAS +
-tableSchema DB2GSE -tableName POINTS -columnName P +
-indexSchema DB2GSE -indexName P_IDX2 +
-otherIdxOpts "FREEPAGE 0" +
-gridSize1 10.0 -gridSize2 20.0 -gridSize3 35.0