Dropping a table without previously calling SYSPROC.SYSTS_DROP

When you drop a table without first calling the SYSPROC.SYSTS_DROP stored procedure, the result is orphaned full-text indexes (metadata in Db2® tables and collections on the text search server).

About this task

Recreation of the identical table without previously calling the SYSPROC.SYSTS_DROP stored procedure for full-text indexes results in a situation where text search functions always return 0 results, and administration stored procedures might succeed but do not have any effect.

Procedure

To detect and delete the orphaned full-text indexes:

  1. Issue the following command:
    select indexschema,indexname from sysibmts.systextindexes I where 
    not exists (select 1 from sysibm.systables t where 
    t.name=i.tablename and t.creator=i.tableschema and 
    t.createdts<i.createtime);
  2. Next, drop the orphans by calling the SYSPROC.SYSTS_DROP stored procedure.
    The SYSPROC.SYSTS_DROP stored procedure drops all of the collections on the text search servers that are installed and populated in the SYSIBMTS.SYSTEXTSERVER administration table. The text search servers must be running so that the SYSPROC.SYSTS_DROP stored procedure can drop the collections that are on them.