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.
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. |