This procedure deletes indexing events from an index's
event table used for administration.
The name of the event table can be found in the view
SYSIBMTS.TSINDEXES in column EVENTVIEWNAME. Every index update operation
that processes at least one document produces informational and, in
some cases, error entries in the event table. For automatic updates,
the event table has to be regularly inspected. Document specific errors
must be corrected by changing the document content. After correcting
the errors, the events can be cleared (and should be, in order not
to consume too much space).
The procedure issues a CLEAR EVENTS FOR INDEX text search administration command
on the database server.
Authorization
The privileges held by the authorization ID of the statement must
include both of the following authorities:
- SYSTS_MGR role
- DBADM with DATAACCESS authority or CONTROL privilege on the table
on which the index is defined
Default PUBLIC privilege
None
Syntax
>>-SYSTS_CLEAR_EVENTS--(--index_schema--,--index_name--,-------->
>--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.
- 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.
Examples
Example 1: In the following
example, SYSTS_CLEAR_EVENTS is being called for a text search index
that was created with index_schema 'db2ts' and index_name 'myTextIndex'. 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_CLEAR_EVENTS('db2ts', 'myTextIndex', 'en_US', ?)
The following is an example of 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_CLEAR_EVENTS
is called to clear the event table entries for a text search index
with index_schema 'db2ts' and index_name 'myTextIndex2'. This index
does not exist and results in an error.
CALL SYSPROC.SYSTS_CLEAR_EVENTS('db2ts', 'myTextIndex2', 'en_US', ?)
The following is an example of 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"."myTextIndex2" 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_DISABLE procedure or DISABLE DATABASE FOR TEXT db2ts command
- SYSTS_DROP procedure or DROP INDEX db2ts command
- SYSTS_CONFIGURE procedure
- SYSTS_UPDATE procedure or UPDATE INDEX db2ts command
If there is a conflict, the procedure returns an SQLCODE -20426
and SQLSTATE 38H13.
- When regular updates are scheduled (see UPDATE FREQUENCY options
in SYSTS_CREATE or SYSTS_ALTER procedures), the event table should
be checked regularly.
- To clean up the DB2 Text
Search event table for a text search index, use the SYSTS_CLEAR_EVENTS
procedure or CLEAR EVENTS FOR INDEX db2ts command
after you have checked the reason for the event and removed the source
of the error.
- Ensure that changes have been made to all rows referenced in the
event table. By changing the rows in the user table, you ensure that
when you run the SYSTS_UPDATE procedure or UPDATE INDEX db2ts command again, an attempt is made to index the erroneous documents
again.
- When this command is issued, the event table is cleared.