ST_GetIndexParms function

The ST_GetIndexParms function takes either the identifier for a spatial index or for a spatial column as an input parameter and returns the parameters used to define the index or the index on the spatial column. If an additional parameter number is specified, only the grid size identified by the number is returned.

Syntax

Parameter

index_schema: A value of type VARCHAR(128) that identifies the schema in which the spatial index with the unqualified name index_name is in. The schema name is case-sensitive and must be listed in the SYSCAT.SCHEMATA catalog view.
If this parameter is null, then the value of the CURRENT SCHEMA special register is used as the schema name for the spatial index.
index_name: A value of type VARCHAR(128) that contains the unqualified name of the spatial index for which the index parameters are returned. The index name is case-sensitive and must be listed in the SYSCAT.INDEXES catalog view for the schema index_schema.
table_schema: A value of type VARCHAR(128) that identifies the schema in which the table with the unqualified name table_name is in. The schema name is case-sensitive and must be listed in the SYSCAT.SCHEMATA catalog view.
If this is parameter null, then the value of the CURRENT SCHEMA special register is used as the schema name for the spatial index.
table_name: A value of type VARCHAR(128) that contains the unqualified name of the table with the spatial column column_name. The table name is case-sensitive and must be listed in the SYSCAT.TABLES catalog view for the schema table_schema.
column_name: A value of type VARCHAR(128) that identifies the column in the table table_schema.table_name for which the index parameters of the spatial index on that column are returned. The column name is case-sensitive and must be listed in the SYSCAT.COLUMNS catalog view for the table table_schema.table_name.
If there is no spatial index defined in the column, then an error is raised (SQLSTATE 38SQ0).
grid_size_number: A DOUBLE value that identifies the parameter whose value or values are to be returned.
If this value is smaller than 1 or larger than 3, then an error is raised (SQLSTATE 38SQ1).

Return type

DOUBLE (if grid_size_number is specified)

If grid_size_number is not specified, then a table with the two columns ORDINAL and VALUE is returned. The column ORDINAL is of type INTEGER, and the column VALUE is of type DOUBLE.

If the parameters are returned for a grid index, the ORDINAL column contains the values 1, 2, and 3 for the first, second, and third grid size, respectively. The column VALUE contains the grid sizes.

The VALUE column contains the respective values for each of the parameters.

Examples

Example 1

This code creates a table with a spatial column and a spatial index.


SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse

CREATE TABLE sch.offices (name VARCHAR(30), location ST_Point )

 CREATE INDEX sch.idx ON sch.offices(location)
      EXTEND USING db2gse.spatial_index(1e0, 10e0, 1000e0)

The ST_GetIndexParms function can be used to retrieve the values for the parameters that were used when the spatial index was created.

Example 2

This example shows how to retrieve the three grid sizes for a spatial grid index separately by explicitly specifying which parameter, identified by its number, is to be returned.


VALUES ST_GetIndexParms('SCH', 'OFFICES', 'LOCATION', 1)

Results:


1
------------------------
  +1.00000000000000E+000



VALUES ST_GetIndexParms('SCH', 'OFFICES', 'LOCATION', 2)

Results:


1
------------------------
  +1.00000000000000E+001


VALUES ST_GetIndexParms('SCH', 'IDX', 3)

Results:


1
------------------------
  +1.00000000000000E+003

Example 3

This example shows how to retrieve all the parameters of a spatial grid index. The ST_GetIndexParms function returns a table that indicates the parameter number and the corresponding grid size.


SELECT * FROM TABLE ( ST_GetIndexParms('SCH', 'OFFICES', 'LOCATION') ) AS t

Results:


ORDINAL     VALUE
----------- ------------------------
          1   +1.00000000000000E+000
          2   +1.00000000000000E+001
          3   +1.00000000000000E+003


SELECT * FROM TABLE ( ST_GetIndexParms('SCH', 'IDX') ) AS t

Results:


ORDINAL     VALUE
----------- ------------------------
          1   +1.00000000000000E+000
          2   +1.00000000000000E+001
          3   +1.00000000000000E+003