ST_ENABLE_AUTOGEOCODING procedure

Use this stored procedure to specify that Db2® Spatial Extender is to synchronize a geocoded column with its associated geocoding column or columns.

A geocoding column is used as input to the geocoder. Each time that values are inserted into, or updated in, the geocoding column or columns, triggers are activated. These triggers invoke the associated geocoder to geocode the inserted or updated values and to place the resulting data in the geocoded column.

Restriction: You can enable autogeocoding only on tables on which INSERT and UPDATE triggers can be created. Consequently, you cannot enable autogeocoding on views or nicknames.

Prerequisite: Before enabling autogeocoding, you must perform the geocoding setup step by calling the ST_SETUP_GEOCODING procedure. The geocoding setup step specifies the geocoder and the geocoding parameter values. It also identifies the geocoding columns that are to be synchronized with the geocoded columns.

Authorization

The user ID under which this stored procedure is invoked must have one of the following authorities or privileges:
  • DBADM authority on the database that contains the table on which the triggers that are created by this stored procedure are defined
  • CONTROL privilege on the table
  • ALTER privilege on the table
If the authorization ID of the statement does not have DBADM authority, the privileges that the authorization ID of the statement holds (without considering PUBLIC or group privileges) must include all of the following privileges as long as the trigger exists:
  • SELECT privilege on the table on which autogeocoding is enabled or DATAACCESS authority
  • Necessary privileges to evaluate the SQL expressions that are specified for the parameters in the geocoding setup

Syntax

Read syntax diagramSkip visual syntax diagramDB2GSE.ST_ENABLE_AUTOGEOCODING( table_schemanull,table_name,column_name, 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.

The table_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).

table_name
Specifies the unqualified name of the table that contains the column into which the geocoded data is to be inserted or updated. You must specify a non-null value for this parameter.

The table_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).

column_name
Identifies the column into which the geocoded data is to be inserted or updated. This column is referred to as the geocoded column. You must specify a non-null value for this parameter.

The column_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).

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(1024).

Example

This example shows how to use the Db2 command line processor to invoke the ST_ENABLE_AUTOGEOCODING stored procedure. This example uses a Db2 CALL command to enable autogeocoding on the LOCATION column in the table named CUSTOMERS: 

call DB2GSE.ST_ENABLE_AUTOGEOCODING(NULL,'CUSTOMERS','LOCATION',?,?)
The two question marks at the end of this CALL command represent the output parameters, msg_code and msg_text. The values for these output parameters are displayed after the stored procedure runs.