db2ext.textindexes view

Each database enabled for DB2 Net Search Extender contains a db2ext.textindexes view. This contains information on settings, statistics, and defaults for the created text indexes in this database.

When you create a text index, new entries are created in db2ext.textindexes. When you drop the text indexes, these entries are deleted.

You can query the view to obtain information about the indexes. This is an example using the index schema:

db2 "select COLNAME from DB2EXT.TEXTINDEXES where INDSCHEMA='myschema' 
     and INDNAME='myindex'"

Note, however, that you cannot modify the view using normal SQL data manipulation commands, or explicitly create or drop the catalog view. Additional contents of the view are found in the following table.

Also note that the replication parameters are not included in this view.

Table 11. db2ext.textindexes view
Attribute Type Notes
INDSCHEMA VARCHAR(128) Schema name of the text index.
INDNAME VARCHAR(128) Name of the text index.
TABSCHEMA VARCHAR(128) The table name of the schema for base tables, nicknames, and views.
TABNAME VARCHAR(128) Alias name the index was created on.
COLNAME VARCHAR(128) Column the index was created on.
CCSID INTEGER Document CCSID for this index.
LANGUAGE VARCHAR(5) Document language for this index.
FUNCTIONSCHEMA VARCHAR(128) Schema of the column mapping function.
FUNCTIONNAME VARCHAR(18) Name of the column mapping function.
INDEXDIRECTORY VARCHAR(256) Directory for full-text index files.
WORKDIRECTORY VARCHAR(256) Directory for index temporary files.
CACHEDIRECTORY VARCHAR(256) Directory for persistent cache (if persistentcache=1).
UPDATEFREQUENCY VARCHAR(300) Trigger criterion for applying automatic updates to this index.
UPDATEMINIMUM INTEGER Minimum number of documents that must be changed before an update is performed.
EVENTVIEWSCHEMA VARCHAR(128) Schema of the event view created for this index.
EVENTVIEWNAME VARCHAR(128) Name of the event view created for this index.
LOGVIEWSCHEMA VARCHAR(128) Schema of the log view created for an index.
LOGVIEWNAME VARCHAR(128) Name of the log view created for an index (important for incremental update on views).
COMMITCOUNT INTEGER Default for commitcount update.
NUMBER_DOCS INTEGER Total number of documents currently in the index. Note that during an index update, this value is only updated if the commitcount is set.
REORG_SUGGESTED INTEGER Indicates if performance can be improved by running UPDATE INDEX REORGANIZE. This parameter is only true (1) if at least one of the nodes has an index reorganization suggested.
REORGAUTOMATIC INTEGER 1, if the index gets automatically reorganized during the update operation.
RECREATEONUPDATE INTEGER 1, if the index gets automatically reorganized during the update operation.
CREATIONTIME TIMESTAMP Time of index creation.
UPDATETIME TIMESTAMP Time of last update. If UPDATE TIME is equal to CREATION TIME, then no update has been processed.
PERSISTENTCACHE INTEGER 1, if persistent cache is used.
MAXIMUMCACHESIZE INTEGER Maximum size of cache.
PCTFREE INTEGER Percentage of cache left free for future inserts.
CACHETABLE VARCHAR(32000) Column expression list for the CACHE TABLE.
RESULTORDER VARCHAR(32000) SQL-order-by for INITIAL RESULT ORDER.
ATTRIBUTES VARCHAR(32000) Column expression list for ATTRIBUTES.
VIEWKEYCOLUMNS VARCHAR(32000) Key columns for index on view.