Checking the event table after calling SYSPROC.SYSTS_CREATE

To get the best performance for the CONTAINS and SCORE functions, the table specified in the SYSTS_CREATE stored procedure must have an index on its ROWID column.

About this task

If there is not a ROWID index, the SYSPROC.SYSTS_CREATE stored procedure adds an entry similar to the following in the event table:

OF00700E DB2 error: A DB2 performance problem occurred. You must create 
an index on the ROWID column. For example, enter the command ''CREATE 
UNIQUE INDEX IROWIDCKHTM ON USRT002.CKHTM(RID). File ./dsxdbcat.cpp:165''.

If you are not sure if an index exists on the ROWID column, you must check the event table each time after running the SYSTS_CREATE stored procedure.

Procedure

To check whether an index exists on the ROWID column:

Issue the following SELECT statement, where EVENTS_index-ID is the name of the event table:

SELECT * FROM SYSIBMTS.EVENTS_index-ID;

Note: If you create the ROWID column with the option NOT NULL GENERATED BY DEFAULT, Db2 automatically creates an implicit index on the ROWID column. If you create the ROWID column in your text table with the option NOT NULL GENERATED ALWAYS, Db2 does not create an implicit index on the ROWID column.