Deleting orphaned DB2® Text Search collections

You can delete orphaned collections with the db2ts CLEANUP FOR TEXT command or use the following process to identify and remove orphaned collections by using the administration tool.

About this task

A text search index is associated with a single collection for non-partitioned or single-partition databases, and with n collections for multi-partition databases with n the number of relevant data partitions. Although db2ts commands and procedures operate on text search indexes, the text search tools operate on the text search collections. When a text search index no longer exists but its corresponding text search collection does, it is called an orphaned collection.

A collection will get orphaned in the following scenarios:

dropping a database that contains the text index
using the FORCE option with the DISABLE or DROP index operation

These operations succeed even if the Text Search server is not reachable.

A collection may also get an orphaned or an invalid status in some failure scenarios. For example, a disk crash may cause an inconsistency in the text index metadata.

To determine whether any orphaned collections exist:

Use the administration tool to report all text search collections. Issue the following command:
```
	adminTool status -configPath <absolute-path-to-configuration-folder>
```
Query the SYSIBMTS.TSCOLLECTIONNAMES administrative view to report all text search indexes on the current database:
```
	SELECT collectionname FROM SYSIBMTS.TSCOLLECTIONNAMES
```
Perform this query on all the databases enabled for Db2® Text Search, and combine the results into a list.
The administration tool lists all text search collections, while the query on the SYSIBMTS.TSCOLLECTIONNAMES view lists only text search indexes on the current database.
Compare the lists returned by the administration tool and by the SELECT statement. Any text search collection returned by the administration tool but not by the SELECT statement is an orphaned collection. The only exception to this rule is the default collection that is created when the Db2 Text Search server is started.

Remove the orphaned text search collection with the following command:

	adminTool delete -configPath <absolute-path-to-configuration-folder>
	-collectionName collection-name

Important: The action performed by the adminTool delete command is not recoverable and is equivalent to dropping an index or rendering an index inconsistent.

Example

You currently have Db2 Text Search enabled for a database called DBCP1208, which is running on a UNIX system. To determine whether any orphaned text search collections exist, use the administration tool and a SELECT statement:

	adminTool.sh status -configPath $HOME/sqllib/db2tss/config  

	CollectionName    	IndexSize         NumOfDocuments
	Default         13,159B               0 
	tigertail_DBCP1208_TS542717_0000          13,159B               11 
	tigertail_DBCP1208_TS012817_0000          13,159B               17 
	tigertail_DBCP1208_TS082817_0000          13,159B               16 
	tigertail_DBCP1208_TS152817_0000          13,159B               18 
	tigertail_DBCP1208_TS212817_0000          13,159B               16 
	tigertail_DBCP1208_TS302817_0000          13,159B               17 
	tigertail_DBCP1208_TS392817_0000          13,159B               10 
	tigertail_DBCP1208_TS462817_0000          13,159B               10 
	tigertail_DBCP1208_TS542817_0000          13,159B               12 
	tigertail_DBCP1208_TS022917_0000          13,159B               10 
	tigertail_DBCP1208_TS112917_0000          13,159B               16 
	tigertail_DBCP1208_TS192917_0000          13,159B               11 
	tigertail_DBCP1208_TS262917_0000          13,159B               12 
	tigertail_DBCP1208_TS867530_0000          13,159B               16 


	db2 select collectionname from sysibmts.tscollectionnames 

	COLLECTIONNAME 
	--------------------------------------------------------------------
	tigertail_DBCP1208_TS542717_0000                                   
	tigertail_DBCP1208_TS012817_0000
	tigertail_DBCP1208_TS082817_0000
	tigertail_DBCP1208_TS152817_0000
	tigertail_DBCP1208_TS212817_0000
	tigertail_DBCP1208_TS302817_0000
	tigertail_DBCP1208_TS392817_0000
	tigertail_DBCP1208_TS462817_0000
	tigertail_DBCP1208_TS542817_0000
	tigertail_DBCP1208_TS022917_0000
	tigertail_DBCP1208_TS112917_0000
	tigertail_DBCP1208_TS192917_0000
	tigertail_DBCP1208_TS262917_0000

	13 record(s) selected.

Comparing the two outputs, you see that the text search collection tigertail_DBCP1208_TS867530_0000 does not have a corresponding text search index. Use the administration tool to delete that orphaned collection:

	adminTool.sh delete -configPath $HOME/sqllib/db2tss/config
	-collectionName tigertail_DBCP1208_TS867530_0000