SYSIBMTS.TSINDEXES view

The current text search index properties are shown in the SYSIBMTS.TSINDEXES view.

The following example uses the index schema and name: 
db2 "SELECT COLNAME from SYSIBMTS.TSINDEXES where INDSCHEMA=schema-name
       and INDNAME=index-name"
The SYSIBMTS.TSINDEXES view is described in the following table.
Table 1. SYSIBMTS.TSINDEXES view
Column name Data type Nullable? Description
INDSCHEMA VARCHAR(128) NO Schema name for the text search index.
INDNAME VARCHAR(128) NO Unqualified name of the text search index.
TABSCHEMA VARCHAR(128) NO Schema name of the base table.
TABNAME VARCHAR(128) NO Unqualified name of the base table.
COLNAME VARCHAR(128) NO Column that the text search index was created on.
CODEPAGE INTEGER NO Document code page for the text search index.
LANGUAGE VARCHAR(5) NO Document language for the text search index.
FORMAT VARCHAR(30) YES Document format.
FUNCTIONSCHEMA VARCHAR(128) YES Schema for the column type.
FUNCTIONNAME VARCHAR(18) YES Name of the column-type conversion function.
COLLECTIONDIRECTORY VARCHAR(512) YES Directory for the text search index files.
UPDATEFREQUENCY VARCHAR(300) NO Trigger criterion for applying updates to the index.
UPDATEMINIMUM INTEGER YES Minimum number of entries in the log table before an incremental update is performed. A lower value means better consistency between the table column and the text search index. However, a lower value also increases the resources that are required for text search indexing.
EVENTVIEWSCHEMA VARCHAR(128) NO Schema for the event view that is created for the text search index (always SYSIBMTS).
EVENTVIEWNAME VARCHAR(128) NO Name of the event view that is created for the text search index.
STAGINGVIEWSCHEMA VARCHAR(128) YES Schema for the log view that is created for the text search index (always SYSIBMTS).
STAGINGVIEWNAME VARCHAR(128) YES Name of the log view that is created for the text search index.
REORGAUTOMATIC INTEGER YES Reserved (not supported in this release). The value is always 1.
RECREATEONUPDATE INTEGER NO Reserved (not supported in this release). The value is always 0.
ATTRIBUTES VARCHAR(18) YES Reserved (not supported in this release).
INDEXMODELNAME VARCHAR(128) YES Reserved (not supported in this release).
COLLECTIONNAMEPREFIX VARCHAR(128) NO Prefix of the collection name on the text search server.
COMMENT VARCHAR(512) YES Comment that is specified for a parameter that is related to index properties of the CREATE INDEX command.
AUXSTAGINGSCHEMA VARCHAR(48) YES Schema of the text-maintained staging table.
AUXSTAGINGNAME VARCHAR(48) YES Name of the text-maintained staging table.
INDSTATUS VARCHAR(10) NO Index status:
  • ACTIVE indicates an active index.
  • INACTIVE indicates an inactive index. (This value is not used for DB2® Text Search.)
  • INVALID indicates an invalidated index, usually a side effect of a DB2 operation.
SERIALMODE INTEGER NO For distributed setups:
  • 0=parallel update
  • 1=serial update
INDEXMODELNAME VARCHAR(128) YES Reserved (not supported in this release).