db2ts CLEAR EVENTS FOR TEXT command

This command deletes indexing events from an index's event table used for administration. The name of this 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, this table has to be regularly inspected. Document specific errors have to 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).

For execution, the command needs to be prefixed with db2ts at the command line.

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

Required connection

Database

Command syntax

Read syntax diagramSkip visual syntax diagramCLEAR EVENTS FOR INDEXindex-name FOR TEXTconnection options
connection options
Read syntax diagramSkip visual syntax diagramCONNECT TOdatabase-nameUSERusernameUSINGpassword

Command parameters

index-name
The name of the index as specified in the CREATE INDEX command. The index name must adhere to the naming restrictions for Db2® indexes.
CONNECT TO database-name
This clause specifies the database to which a connection will be established. The database must be on the local system. If specified, this clause takes precedence over the environment variable DB2DBDFT. This clause can be omitted if the following are all true:
  • The DB2DBDFT environment variable is set to a valid database name.
  • The user running the command has the required authorization to connect to the database server.
USER username USING password
This clause specifies the authorization name and password that will be used to establish the connection.

Usage notes

All limits and naming conventions, that apply to Db2 database objects and queries, also apply to Db2 Text Search features and queries. Db2 Text Search related identifiers must conform to the Db2 naming conventions. In addition, there are some additional restrictions. For example, these identifiers can only be of the form:
[A-Za-z][A-Za-z0-9@#$_]*
or
"[A-Za-z ][A-Za-z0-9@#$_ ]*"

When regular updates are scheduled (see UPDATE FREQUENCY options in CREATE INDEX or ALTER INDEX commands), the event table should be regularly checked. To cleanup the Db2 Text Search event table for a text search index, use the CLEAR EVENTS FOR INDEX command after you have checked the reason for the event and removed the source of the error.

Be sure to make changes to all rows referenced in the event table. By changing the rows in the user table, you ensure that the next UPDATE INDEX attempt can be made to successfully re-index the once erroneous documents.

Note that multiple commands cannot be executed concurrently on a text search index if they may conflict. If this command is issued while a conflicting command is running, an error will occur and the command will fail, after which you can try to run the command again. Some of the conflicting commands are:
  • CLEAR EVENTS FOR INDEX
  • UPDATE INDEX
  • ALTER INDEX
  • DROP INDEX
  • DISABLE DATABASE FOR TEXT

Changes to the database: The event table is cleared.