The db2ts CREATE INDEX command creates a text search index for a text column. You can then search the column data by using text search functions.
The text search index does not contain any data until you run the text search UPDATE INDEX command or the DB2® Administrative Task Scheduler runs the UPDATE INDEX command according to the defined update frequency for the index.
To issue the CREATE INDEX command, you must prefix the command name with db2ts.
To schedule automatic index updates, the instance owner must have DBADM authority or CONTROL privileges on the administrative task scheduler tables.
Database
>>-CREATE INDEX--index_name--FOR TEXT---------------------------> >--ON--schema_name--table_name----------------------------------> >--+-(--text_column_name--)----------------------+--------------> '-(--function_name--(--text_column_name--)--)-' >--| text default information |--| update characteristics |-----> >--| storage options |--| index configuration options |---------> >--| connection options |-------------------------------------->< text default information |--+---------------------+--+------------------+----------------> '-CODEPAGE--code_page-' '-LANGUAGE--locale-' >--+----------------+-------------------------------------------| '-FORMAT--format-' update characteristics |--+--------------------------------------------+---------------> '-UPDATE FREQUENCY--+-NONE-----------------+-' '-| update frequency |-' >--| incremental update characteristics |-----------------------| update frequency |--D-(-+-*------------+-)--H-(-+-*------------+-)---------------> | .-,--------. | | .-,--------. | | V | | | V | | '---integer1-+-' '---integer2-+-' .-,--------. V | >--M-(---integer3-+-)-------------------------------------------| incremental update characteristics |--+----------------------------+-------------------------------| '-UPDATE MINIMUM--minchanges-' storage options |--+---------------------------------+--------------------------> '-COLLECTION DIRECTORY--directory-' >--+-------------------------------------------+----------------| '-ADMINISTRATION TABLES IN--tablespace_name-' index configuration options |--+-----------------------------------------------+------------| | .-,----------------. | | V | | '-INDEX CONFIGURATION--(---| option value |-+-)-' option value |--+----------------------------------------------+-------------| +-COMMENT--text -------------------------------+ +-UPDATEAUTOCOMMIT--+-+--------------------+-+-+ | | '-commitcount_number-' | | | '-+------------+---------' | | '-commitsize-' | +-COMMITTYPE--committype-----------------------+ +-COMMITCYCLES--commitcycles-------------------+ +-INITIALMODE--initialmode---------------------+ +-LOGTYPE--ltype-------------------------------+ +-AUXLOG--auxlog_value-------------------------+ +-CJKSEGMENTATION -cjksegmentation_method------+ '-UPDATEDELAY--updateDelay value---------------' server configuration options |--+--------------------+---------------------------------------| '-SERVERID--serverId-' connection options |--+----------------------------------------------------------------+--| '-CONNECT TO--database_name--+---------------------------------+-' '-USER--username--USING--password-'
You can create only a single text search index for a column.
The default frequency value is taken from the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME is set to UPDATEFREQUENCY.
The UPDATE INDEX command ignores the value of the UPDATE MINIMUM parameter unless you specify the USING UPDATE MINIMUM option for that command.
A small value for the UPDATE MINIMUM parameterincreases consistency between the table column and the text search index. However, it also increases the load on the system.
The COLLECTION DIRECTORY parameter is supported only for an integrated text search server setup. For additional information about collection locations, review the usage notes.
For a nonpartitioned database, if you do not specify a table space, the table space of the base table for which you are creating the index is used.
For a partitioned database, you must use the ADMINISTRATION TABLES IN parameter. To ensure that the staging tables for the text search index are distributed in the same manner as the corresponding base table, the table space must be in the same partition group as the table space of the base table.
Option | Value | Data type | Description |
---|---|---|---|
COMMENT | text | String value of fewer than 512 bytes | Adds a string comment value to the REMARKS column in the DB2 Text Search catalog view TSINDEXES. It also appends the string comment value as the description of the collection to the table. |
UPDATEAUTOCOMMIT | commitsize | String | Specifies the number of rows or number of hours after which a commit is run to preserve the previous work for either initial or incremental updates. If you specify the number of rows, after the number of updated documents reaches the COMMITCOUNT number, the server applies a commit. COMMITCOUNT counts the number of documents that are updated by using the primary key, not the number of staging table entries. If you specify the number of hours, the data in text index is committed after the specified number of hours is reached. The maximum number of hours is 24. For an initial update, the index update processes batches of documents from the base table. After the commitsize value is reached, update processing completes a COMMIT operation, and the last processed key is saved in the staging table with the operational identifier '4.' This key is used to restart update processing after a failure or after the completion of the specified number of commitcycles . If you specify a commitcycles value, the update mode is changed to incremental to initiate capturing changes by using the LOGTYPEBASIC option to create triggers on the text table. However, , until the initial update is complete, log entries that were generated by documents that were not processed in a previous cycle are removed from the staging table. Using the UPDATEAUTOCOMMIT option for an initial text index update significantly increases execution time. For incremental updates, log entries that are processed are removed from the staging table with each interim commit. |
COMMITTYPE | committype | String | Specifies rows or hours for the UPDATEAUTOCOMMIT index configuration option. The default is rows. |
COMMITCYCLES | commitcycles | Integer | Specifies the number of commit cycles. The default is 0, meaning unlimited cycles. If you do not specify the number of cycles, the update operation uses as many cycles as required to finish the update processing, based on the batch size that you specify for the UPDATEAUTOCOMMIT option. You can use the COMMITCYCLES option with the UPDATEAUTOCOMMIT option with a committype option . |
INITIALMODE | initialmode | String | Specifies how the updates are processed. The possible values of the INITIALMODE option are as follows:
|
LOGTYPE | ltype | String | Specifies whether triggers are added to populate
the primary log table. The values are as follows:
Note: The default value of
the LOGTYPE option is CUSTOM for
text search indexes on nicknames.
|
AUXLOG | auxlog_value | String | Controls the creation of the additional log
infrastructure to capture changes that are not recognized by a trigger.
The default setting for range-partitioned tables is ON.
You can change the default value in the default table by setting AuxLogNorm for
non-range-partitioned tables and AuxLogPart for range-partitioned
tables. For text search indexes on nicknames, only the OFF option is supported for theAUXLOG option. |
CJKSEGMENTATION | cjksegmentation_method | String value of fewer than 512 bytes | Specifies the segmentation method that applies
to documents that use the Chinese, Japanese, or Korean language (zh_CN,
zh_TW, ja_JP, or ko_KR locale set), including such documents when
automatic language detection is enabled (when you specify the LANGUAGE parameter
with the AUTO option). Supported values are:
The specified segmentation method is added to the SYSIBMTS. TSCONFIGURATION administrative view. You cannot change the method after creating the text index. |
INDEX CONFIGURATION (COMMENT 'Index on User''s Guide column')
[A-Za-z][A-Za-z0-9@#$_]*
or"[A-Za-z ][A-Za-z0-9@#$_ ]*"
tigertail_MYTSDB_TS250517_0000
You
can retrieve the collection name from the COLLECTIONNAME column in
the SYSIBMTS.TSCOLLECTIONNAMES view. You cannot issue multiple commands concurrently on a text search index if they might conflict. If you issue this command while a conflicting command is running, an error occurs, and the command fails, after which you can try to run the command again. A conflicting command is DISABLE DATABASE FOR TEXT.
You cannot change the auxiliary log property for a text index after creating the index.
The AUXLOG option is not supported for nicknames for data columns that support an MQT with deferred refresh. It is also not supported for views.
To create a text search index on a nickname, the nickname must be a non-relational flat file nickname. Non-relational XML nicknames are not supported
For compatibility with an earlier version, you can specify the UPDATEAUTOCOMMIT index configuration option without type and cycles. This option is associated by default with the COMMITTYPE rows option and unrestricted cycles.
To override the configured values, you can specify the UPDATEAUTOCOMMIT, COMMITTYPE, and COMMITSIZE index configuration options for an UPDATE INDEX operation. Values that you submit for a specific update are applied only once and not persisted.
If you specify theINITIALMODE SKIP option, the text search index manager populates the index. Use this option to control the sequence in which data from the text table is initially processed.
For a partitioned database environment, administration tables that are specific to text search indexes, such as staging tables, and text search indexes are distributed in a manner like that used for the corresponding base table. When creating a text search index, use the ADMINISTRATION TABLES IN parameter so that the specified table space is in the same partition group as the table space of the base table.
The CJKSEGMENTATION option applies to zh_CN, zh_TW, ja_JP and ko_KR locale sets for Chinese, Japanese, and Korean languages. The MORPHOLOGICAL or NGRAM option that you specify for the segmentation method is added to the SYSIBMTS.TSCONFIGURATION administration view.
If you create an index with the LANGUAGE parameter set to the AUTO option, you can specify the CJKSEGMENTATION option. The specified segmentation method applies to Chinese, Japanese, and Korean language documents. You cannot change the value that you set for the cjksegmentation_method option after index creation is complete.
select bookname from ngrambooks where contains (story, '军书','QUERYLANGUAGE=zh_CN') = 1
If you use the INITIALMODE SKIP option, combined with the LOGTYPE ON and AUXLOG ON options, you must manually insert the log entries into the staging table, but only for the initial update. All subsequent updates are handled automatically.