The SYSTS_CREATE procedure creates a text search index for a text column by issuing the DB2® Text Search CREATE INDEX command on the database server. After you create and update a text search index, you can search the column data by using text search functions.
The index does not contain any data until an index update operation is processed. You can start the update operation by using the stored procedure interface or the command-line interface. For the latter, you can explicitly issue the DB2 Text Search UPDATE INDEX command, or the command is implicitly issued by the DB2 Administrative Task Scheduler, according to the update frequency defined for the index
None
>>-SYSTS_CREATE--(--index_schema--,--index_name--,--------------> >--| text source |--,--| options |------------------------------> >--,--message_locale--,--message--)---------------------------->< text source |--+--------------+--table-name--(--| text column name |--)--,--| '-table-schema-' text column name |--+-column-name-----------------+------------------------------| '-function-name (column-name)-' options |--+-+------------------------------+----+----------------------| | '-| text default information |-' | +-+----------------------------+------+ | '-| update characteristics |-' | +-+---------------------+-------------+ | '-| storage options |-' | +-+---------------------------------+-+ | '-| index configuration options |-' | '-+-----------------------+-----------' '-| partition options |-' text default information |--+---------------------+--+--------------------+--------------> '-CODEPAGE--code-page-' '-LANGUAGE--language-' >--+----------------+-------------------------------------------| '-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------'
The schema is SYSPROC.
The default format is taken from the SYSIBMTS.TSDEFAULTS view, from the row with the DEFAULTNAME column value of UPDATEFREQUENCY.
Specifies the directory in which the text search index collection is stored. You must specify the absolute path, where the maximum length of the absolute path name is 215 characters. The process owner of the Text Search server instance service must have read and write access on this directory.
The COLLECTION DIRECTORY parameter is only supported for an integrated text search server setup. Review the usage notes for additional information about collection locations.
Specifies the name of an existing non-temporary table space for the administration tables that are created for the index. By default the table space of the base table for which you are creating the index is used.
This argument is required when a text index is created for a partitioned table or for tables in partitioned databases. For partitioned databases, the table space must be in the same partition group as the table space of the base table. The text index administration tables are distributed in the same manner as the corresponding base table.
Option | Value | Data type | Description |
---|---|---|---|
COMMENT | text | String value of fewer than 512 bytes | Adds a string comment to the REMARKS column in the DB2 Text Search SYSIBMTS.TSINDEXES catalog view. The comment is also used as the description of the collection. |
UPDATEAUTOCOMMIT | commitsize | - |
|
COMMITTYPE | committype | String | Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, specifies rows or hours for the UPDATEAUTOCOMMIT index configuration option. The default is rows. |
COMMITCYCLES | commitcycles | Integer | Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, specifies the number of commit cycles. The default is 0 for unlimited cycles. If cycles are not explicitly specified, the update operation uses as many cycles as required based on the batch size that is specified with the UPDATEAUTOCOMMIT option to finish the update processing. You can use this option with the UPDATEAUTOCOMMIT setting with a committype. |
INITIALMODE | initialmode | String | Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, specifies how the updates are processed. The INITIALMODE index configuration option has three possible values:
|
LOGTYPE | ltype | String | Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, specifies whether triggers are added to populate the primary log table. It has two values:
|
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. You cannot change the auxiliary log infrastructure property for a text index after it is created. |
CJKSEGMENTATION | CJKSEGMENTATION_method | String | The segmentation method is applicable for documents in Chinese, Japanese, and Korean languages (zh_CN, zh_TW, ja_JP, ko_KR locale sets), including such documents when automatic language detection is enabled (LANGUAGE AUTO). If no option is specified, the value for CJKSEGMENTATION in the defaults table is applied. Supported values are:
The specified segmentation method is added to the SYSIBMTS. TSCONFIGURATION administrative view and cannot be changed after the text index is created. |
CALL SYSPROC.SYSTS_CREATE('db2ts', 'myTextIndex',
'myUserSchema.myBaseTable (myTextColumn)', 'UPDATE MINIMUM 10',
'en_US', ?)
Value of output parameters
--------------------------
Parameter Name : MESSAGE
Parameter Value : Operation completed successfully.
Return Status = 0
CALL SYSPROC.SYSTS_CREATE('db2ts', 'myTextIndex2',
'myUserSchema.myBaseTable (myTextColumn)', '', 'en_US', ?)
SQL20427N An error occurred during a text search administration
procedure or command. The error message is "CIE00201 Text search
index "db2ts"."myTextIndex2" already exists. ".
Creating an index with the LANGUAGE parameter set to the AUTO option allows CJKSEGMENTATION specification as an option. The specified segmentation method applies to Chinese, Japanese, and Korean language documents. You cannot change the value that is set for the CJKSEGMENTATION_method value after index creation is complete.
tigertail_MYTSDB_TS250517_0000
For
partitioned databases, a collection is created for each partition.
You can retrieve the collection name(s) from the SYSIBMTS.TSCOLLECTIONNAMES
view COLLECTIONNAME column. select bookname from morphobooks
where contains (story, '军书','QUERYLANGUAGE=zh_CN') = 1
The AUXLOG option is supported for nicknames for data columns that support a multiple query table (MQT) with deferred refresh. It is not supported for views.
You can specify the UPDATEAUTOCOMMIT index configuration option without type and cycles for compatibility with an earlier version. This index configuration option is associated by default with the COMMITTYPE rows option and unrestricted cycles.
You can also specify the UPDATEAUTOCOMMIT, COMMITTYPE, and COMMITSIZE index configuration options for an UPDATE INDEX operation to override the configured values. Values that are submitted for a specific update operation are only applied once and not persisted.
With theINITIALMODE SKIP option, the text search index manager is responsible to populate the index appropriately. Use this option to control the sequence in which data from the text table is initially processed.