This procedure updates the text search index
to reflect the current contents of the text column with which the
index is associated.
While the update is being performed, a search is possible.
Until completion of the update, the search operates on a partially
updated index.
The procedure issues an UPDATE INDEX text search administration command on the database server.
Syntax
>>-SYSTS_UPDATE--(--index_schema--,--index_name--,-------------->
>--update_options--,--message_locale--,--message--)------------><
The schema is SYSPROC.
Procedure parameters
- index_schema
- An input argument of type VARCHAR(128) that specifies the schema
of the text search index. The index_schema must
follow the naming restriction for DB2® schema names. If the argument is null or an empty string,
the value of CURRENT SCHEMA is used. The index_schema is case-sensitive.
- index_name
- An input argument of type VARCHAR(128) that specifies the name
of the index. Together with index_schema, it uniquely
identifies a text search index in a database. The index_name is case-sensitive.
- update_options
- An input argument of type VARCHAR(32K) that specifies update options.
The possible values are:
- USING UPDATE MINIMUM: this setting respects the UPDATE MINIMUM
settings from the CREATE INDEX text search administration
command and the SYSTS_CREATE procedure.
- UPDATEAUTOCOMMIT: this setting overrides
the commitcount defined for the text index for the duration of this
update execution.
- NULL or an empty string ("): the update is unconditionally started
when the procedure is called.
- message_locale
- An input argument of type VARCHAR(33) that specifies the locale
to be used for any error message returned. If the argument is null
or an empty string, or the message files for the specified locale
are not available on the server, 'en_US' is used.
- message
- An output argument of type VARCHAR(32K) that specifies a warning
or informational message for a successfully completed operation.
Authorization
The privileges
held by the authorization ID of the statement must include at least
one of the following authorities:
- CONTROL privilege on the table on which the text index is defined
- DATAACCESS authority
Examples
Example 1: In the following
example, the text search index that was created with
index_schema 'db2ts' and
index_name 'myTextIndex' is being updated. A NULL value in the place
of the
update_options means that an update is unconditionally
started when the stored procedure is called. Any error messages are
requested to be returned in English. When the procedure succeeds,
the output parameter message indicative of the successful operation
is returned to the caller.
CALL SYSPROC.SYSTS_UPDATE('db2ts', 'myTextIndex', '', 'en_US', ?)
The following example is a sample output from this query.
Value of output parameters
--------------------------
Parameter Name : MESSAGE
Parameter Value : Operation completed successfully.
Return Status = 0
Example 2: In the following example, SYSTS_UPDATE
is called to update a text search index with index_schema 'db2ts' and index_name 'myTextIndex'. This index does not exist and results in
an error.
CALL SYSPROC.SYSTS_UPDATE('db2ts', 'myTextIndex', 'USING UPDATE MINIMUM',
'en_US', ?)
The following example is a sample output
from this query.
SQL20427N An error occurred during a text search administration
procedure or command. The error message is "CIE00316 Text search
index "db2ts"."myTextIndex" does not exist. ". SQLSTATE 38H14
Usage notes
- Text search administration procedures use an existing
connection to the database. The current transaction might be committed
or rolled back depending on the completion of the procedures. As such,
you might want to commit all transaction changes to avoid any unexpected
impact from such a commit or rollback. One way to achieve this is
to turn on AUTOCOMMIT.
- Multiple procedures or commands cannot be run concurrently on
a text search index if they might conflict. Some of the conflicting
procedures and commands are:
- SYSTS_ALTER procedure or ALTER INDEX db2ts command
- SYSTS_CLEAR_EVENTS procedure or CLEAR EVENTS FOR INDEX db2ts command
- SYSTS_DISABLE procedure or DISABLE DATABASE FOR TEXT db2ts command
- SYSTS_DROP procedure or DROP INDEX db2ts command
- STOP FOR TEXT db2ts command
- SYSTS_UPDATE procedure or UPDATE INDEX db2ts command
If there is a conflict, the procedure returns an SQLCODE -20426
and SQLSTATE 38H13.
- This procedure does not return until all index update processing
is completed. The duration depends on the number of documents to be
indexed and the number of documents already indexed. The collection
name for the index can be retrieved from the SYSIBMTS.TSCOLLECTIONNAMES
view (column COLLECTIONNAME).
- When there are individual document errors, the documents must
be corrected. The primary keys of the erroneous documents can be looked
up in the event table for the index. By changing the corresponding
rows in the user table, the next call to SYSTS_UPDATE will reprocess
these documents.
- When this procedure is run,
- rows are inserted into the event table (including parser error
information). Information is deleted from the index staging table
in case of incremental updates. Before the first update, it creates
triggers on the user table.
- the collection is updated: new or changed documents
are parsed and indexed and deleted documents are discarded from the
index.