ST_RUN_GEOCODING procedure

Use this stored procedure to run a geocoder in batch mode on a geocoded column.

Authorization

The user ID under which this stored procedure is invoked must hold one of the following authorities or privileges:

DATAACCESS authority on the database that contains the table on which the specified geocoder is to operate
CONTROL privilege on this table
UPDATE privilege on this table

Syntax

Parameter descriptions

table_schema

Names the schema to which the table or view 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 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 or view that contains the column into which the geocoded data is to be inserted or updated. If a view name is specified, the view must be an updatable view. 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

Names the column into which the geocoded data is to be inserted or updated. 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).

geocoder_name

Names the geocoder that is to perform the geocoding. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the geocoding is performed by the geocoder that was specified when geocoding was set up.

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

parameter_values

Specifies the list of geocoding parameter values for the geocoder function. Although you must specify a value for this parameter, the value can be null. If the entire parameter_values parameter is null, the values that are used are either the parameter values that were specified when the geocoder was set up or the default parameter values for the geocoder if the geocoder was not set up.

If you specify any parameter values, specify them in the order that the function defined them, and separate them with a comma. For example:


parameter1-value,parameter2-value,...

Each parameter value can be a column name, a string, a numeric value, or null.

Each parameter value is an SQL expression. Follow these guidelines:

If a parameter value is a geocoding column name, ensure that the column is in the same table or view where the geocoded column resides.
If a parameter value is a string, enclose it in single quotation marks.
If a parameter value is a number, do not enclose it in single quotation marks.
If the parameter is null, cast it to the correct type. For example, instead of specifying just NULL, specify:
```
CAST(NULL AS INTEGER)
```

If any parameter value is not specified (that is, if you specify two consecutive commas (...,,...)), this parameter must be specified either when geocoding is set up or when geocoding is run in batch mode with the parameter_values parameter of the respective stored procedures.

The data type of this parameter is VARCHAR(32K).

where_clause

Specifies the body of the WHERE clause, which defines a restriction on the set of records that are to be geocoded. Although you must specify a value for this parameter, the value can be null.

If the where_clause parameter is null, the resulting behavior depends on whether geocoding was set up for the column (specified in the column_name parameter) before the stored procedure runs. If the where_clause parameter is null, and:

A value was specified when geocoding was set up, that value is used for the where_clause parameter.
Either geocoding was not set up or no value was specified when geocoding was set up, no where clause is used.

You can specify a clause that references any column in the table or view that the geocoder is to operate on. Do not specify the keyword WHERE.

The data type of this parameter is VARCHAR(32K).

commit_scope

Specifies that a COMMIT is to be performed after every n records that are geocoded. Although you must specify a value for this parameter, the value can be null.

If the commit_scope parameter is null, the resulting behavior depends on whether geocoding was set up for the column (specified in the column_name parameter) before the stored procedure runs. If the commit_scope parameter is null and:

A value was specified when geocoding was set up for the column, that value is used for the commit_scope parameter.
Either geocoding was not set up or it was set up but no value was specified, the default value of 0 (zero) is used, and a COMMIT is performed at the end of the operation.

The data type of this parameter is INTEGER.

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_RUN_GEOCODING stored procedure. This example uses a Db2 CALL command to geocode the LOCATION column in the table named CUSTOMER. This CALL command specifies the geocoder_name parameter value as SAMPLEGC and the commit_scope parameter value as 10. A COMMIT is to be performed after every 10 records are geocoded:


call DB2GSE.ST_RUN_GEOCODING(NULL, 'CUSTOMERS', 'LOCATION',
     'SAMPLEGC',NULL,NULL,10,?,?)

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.