Creating a text search index

After you enable a database for Db2® Text Search, you can create text search indexes on columns that contain the text that you want to search.

Before you begin

Creating a text search index requires one of following authorization levels:

CONTROL privilege on the index table
INDEX privilege on the index table with either the IMPLICIT_SCHEMA authority on the database or the CREATEIN privilege on the index table schema
DBADM with DATAACCESS authority

To schedule automatic index updates, the instance owner must have DBADM authority or CONTROL privileges on the administrative task scheduler tables.

A primary key must exist for this table. If a primary key does not exist, you must create one before creating the index.

About this task

If you do not want to manually apply document changes from the table to the text search index, you can specify the UPDATE FREQUENCY parameter to schedule automated updates. Use the UPDATE MINIMUM parameter to control whether the update only runs when a minimum number of changes is made to the table. For example, to specify that MYSCHEMA.MYTEXTINDEX is to be updated after at least five changes have occurred and that the update services are to check every Monday and Wednesday at 12 midnight and 12 noon, issue the following command:

db2ts "CREATE INDEX MYSCHEMA.MYTEXTINDEX FOR TEXT ON PRODUCT(NAME)
	UPDATE FREQUENCY d(1,3) h(0,12) m(0) UPDATE MINIMUM 5"

	CALL SYSPROC.SYSTS_CREATE('myschema', 'myTextIndex', 'product (name)', 
	'UPDATE FREQUENCY D(1,3) H(0,12) M(0)' 'UPDATE MINIMUM 5', 'en_US', ?)

When you create an index, you can specify its locale (language and territory) by using the LANGUAGE option. To have your documents automatically scanned to determine the locale, set the LANGUAGE to AUTO. If you do not specify LANGUAGE, a default is used. This default is derived using the DEFAULTVALUE from SYSIBMTS.TSDEFAULTS where DEFAULTNAME='LANGUAGE'. (In this case, DEFAULTVALUE is set at the time the database is enabled for text search. This value is derived from the database territory if the database territory can be mapped to one of the document locales supported. If the database territory cannot be used to determine a supported document locale, DEFAULTVALUE is set to AUTO.)

Restrictions

A text column in an index must be one of the following supported types:
- CHAR
- VARCHAR
- LONG VARCHAR
- CLOB
- GRAPHIC
- VARGRAPHIC
- LONG VARGRAPHIC
- DBCLOB
- BLOB
- XML
Text search related objects must follow not only DB2® naming conventions, their identifiers must also contain these characters only:
- [A-Za-z][A-Za-z0-9@#$_]* or
- "[A-Za-z ][A-Za-z0-9@#$_ ]*"
This limitation applies to the following:
- the name of the schema containing the text search index
- the name of the table the text search index is associated with
- the name of the text column
- the name of the text search index

Procedure

Create a text search index using one of the following methods:

Issue the CREATE INDEX command:

	db2ts "CREATE INDEX index-name FOR TEXT ON table-name (column-name)"

Call the SYSPROC.SYSTS_CREATE stored procedure:
```
	CALL SYSPROC.SYSTS_CREATE('index-schema', 'index-name', 'table-name
	(column-name)', 'options', 'locale', ?)
```
Note: Schema name and index name are case-sensitive when the stored procedure is used.

Examples

For example, the PRODUCT table in the SAMPLE database includes columns for the product ID, name, price, description, and so on. To create a text search index called MYSCHEMA.MYTEXTINDEX for the NAME column, issue the command or called the stored procedure, as follows:

db2ts "CREATE INDEX MYSCHEMA.MYTEXTINDEX FOR TEXT ON PRODUCT(NAME)"

CALL SYSPROC.SYSTS_CREATE('MYSCHEMA', 'MYTEXTINDEX', 'PRODUCT(NAME)', '', 'en_US',?)

Similarly, to create a text search index called MYSCHEMA.MYXMLINDEX for the XML column DESCRIPTION, enter the following command:

db2ts "CREATE INDEX MYSCHEMA.MYXMLINDEX FOR TEXT ON PRODUCT(DESCRIPTION)"

CALL SYSPROC.SYSTS_CREATE('MYXMLINDEX', 'MYXMLINDEX', 
  'PRODUCT (DESCRIPTION)', '', 'en_US', ?)