DB2 Version 9.7 for Linux, UNIX, and Windows

SYSTS_CREATE procedure - Create a text search index on a column

This procedure creates a text search index for a text column which allows the column data to be searched using text search functions.

Once the text search index is created, the column can be searched using text search functions in queries. The index will not contain any data until the text search UPDATE INDEX command or SYSTS_UPDATE procedure is explicitly executed by the user, or implicitly executed by the text search instance level services, according to the defined update frequency for the index.

The procedure issues a CREATE INDEX text search administration command on the database server.

Syntax


>>-SYSTS_CREATE--(--index_schema--,--index_name--,-------------->

>--| text source |--,--| options |------------------------------>

>--,--message_locale--,--message--)----------------------------><

text source

|--table-name--(--| text column name |--)--,--------------------|

text column name

|--+-column-name-----------------+------------------------------|
   '-function-name (column-name)-'   

options

|--+-+------------------------------+----+----------------------|
   | '-| text default information |-'    |   
   +-+----------------------------+------+   
   | '-| update characteristics |-'      |   
   +-+---------------------+-------------+   
   | '-| storage options |-'             |   
   '-+---------------------------------+-'   
     '-| index configuration 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 '

The schema is SYSPROC.

Procedure parameters

index_schema

An input argument of type VARCHAR(128) that specifies the schema of the text search index. The index_schema must follow the naming restriction for DB2® schema names. If the argument is null or an empty string, the value of CURRENT SCHEMA is used. The index_schema is case-sensitive.

index_name

An input argument of type VARCHAR(128) that specifies the name of the index. Together with index_schema, it uniquely identifies a text search index in a database. The index_name is case-sensitive.

text source

An input argument of type VARCHAR(1024) that specifies the name of the column to be indexed. The options are:

table-name

The table name containing the text column. Text search indexes cannot be created on the following tables:

range-partitioned tables
federated tables
materialized query tables
views

The table-name is case-sensitive.

text column name

The column name of the column to be indexed.

column-name: The column must be of one of the following data types: CHAR, VARCHAR, LONG VARCHAR, CLOB, DBCLOB, BLOB, GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, or XML. If the data type of the column is not one of these, use a transformation function specified with function-schema.function-name to convert the column type to one of the valid types. Refer to the function-name (column-name) for syntax and details. Alternatively, you can specify a user-defined external function that accesses the text documents to be indexed. Only a single text search index can be created for a column. The column-name is case-sensitive.
function-name (column-name): Specifies the schema qualified name, that conforms to DB2 naming conventions, of an external scalar function that accesses text documents in a column that is not of a supported type for text searching. Performs a data type conversion of that value, and returns the value as one of the supported data types for text searching. Its task is to perform a column type conversion. This function must take only one parameter and return only one value. The function-name (column-name) is case-sensitive.

options

An input argument of type VARCHAR(32K) that specifies the options to be used. If no options are needed, the argument can be null or an empty string. The available options are:

CODEPAGE code-page

Specifies the DB2 code page (CODEPAGE) to be used when indexing text documents. The default value is specified by the value in the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME='CODEPAGE' (which happens to be the database code page). This argument only applies to binary data types, that is, the column type or return type from a transformation function must be BLOB or character-type FOR BIT DATA.

LANGUAGE language

Specifies the language to be used by DB2 Text Search for language specific processing of a document during indexing. If you do not specify a locale, the database territory will be used to determine the default setting for LANGUAGE. If you would like to have your documents automatically scanned to determine the locale, specify locale as AUTO.

FORMAT format

Specifies the format of text documents in the column. The supported formats include: TEXT, XML, HTML and INSO. DB2 Text Search needs this information when indexing documents. If the format is not specified, the default value is used. The default value is in the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME='FORMAT'. For columns of data type XML, the default format 'XML' is used, regardless of the value of DEFAULTNAME. Rich text support must be installed to use the INSO format.

UPDATE FREQUENCY

Specifies the frequency with which index updates are made. The index will be updated, if the number of changes is at least the value set for UPDATE MINIMUM. The update frequency NONE indicates that no further index updates will be made. This can be useful for a text column in a table with data that will not change. It is also useful when the user intends to manually update the index (using the UPDATE INDEX command). Automatic updates can only be done if the START FOR TEXT command has been run and the DB2 Text Search instance services are running.

The default frequency value is taken from the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME='UPDATEFREQUENCY'.

NONE

No further index updates are made. The update has to be started manually.

D

The day(s) of the week when the index is updated.

*: Every day of the week.
integer1: Specific days of the week, from Sunday to Saturday: 0 to 6

H

The hour(s) of the specified day(s) when the index is updated.

*: Every hour of the day.
integer2: Specific hours of the day, from midnight to 11 pm: 0 to 23

M

The minute(s) of the specified hour(s) when the index is updated.

integer3: Specified as top of the hour (0), or in multiples of 5 minute increments after the hour: 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50 or 55

UPDATE MINIMUM minchanges

Specifies the minimum number of changes to text documents before the index is updated incrementally at the time specified in UPDATE FREQUENCY. Positive integer values only are allowed. The default value is taken from the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME='UPDATEMINIMUM'.

Note: This value is ignored during an UPDATE INDEX command (unless the USING UPDATE MINIMUM option is used there). A small value increases consistency between the table column and the text search index. However, it also causes higher performance usage.

COLLECTION DIRECTORY directory

The directory in which the text search index is stored. By default the collection data will be located in DBPATH/db2collections where the value for DBPATH is the file path used for creating the database. You must specify the absolute path. The maximum length of the absolute path name is 215 characters. Different indexes are organized in subdirectories named index identifier under COLLECTION DIRECTORY where index identifier is a system generated identifier.

ADMINISTRATION TABLES IN tablespace-name

Specifies the name of an existing regular table space for the administration tables created for the index. If not specified, the table space of the base table for which the index is being created is used.

INDEX CONFIGURATION (option-value)

Specifies additional index related values as option value string pairs. The following values are supported:

Table 1. Specifications for option-value
Option	Value	Data type	Description
COMMENT	`text`	String value less than 512 bytes	Adds a string comment value to the `REMARKS` column in the DB2 Text Search catalog view `TSINDEXES`. It also adds the string comment value as the description of the collection.
UPDATEAUTOCOMMIT	`commitcount_number`	Integer	Starting with DB2 Version 9.7 Fix Pack 3, it specifies the number of index updates after which a commit is executed to preserve the previous work automatically for either initial or incremental updates. For initial updates, the index update will process batches of documents from a base table after the trigger to capture data updates is activated. After the amount of documents updated reaches the `COMMITCOUNT` number, the server will do an interim commit. Log entries generated by unprocessed documents will be removed from staging table. Using the `UPDATEAUTOCOMMIT` option for an initial text index update will lead to a significant increase of execution time. For incremental updates, log entries which have been processed will be removed correspondingly from the staging table with each interim commit. `COMMITCOUNT` counts the number of documents updated, not the number of staging table entries.

Remember: Non-numeric values must be enclosed in single quotation marks. A single quote character within a string value must be represented by two consecutive single quotation marks.

Example:: INDEX CONFIGURATION (COMMENT 'Index on User''s Guide column')

message_locale

An input argument of type VARCHAR(33) that specifies the locale to be used for any error message returned. If the argument is null or an empty string, or the message files for the specified locale are not available on the server, 'en_US' is used.

message

An output argument of type VARCHAR(32K) that specifies a warning or informational message for a successfully completed operation.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the table on which the index is defined
INDEX privilege on the table on which the index is defined
and one of the following authorities:
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the index does not exist
- CREATEIN privilege on the schema, if the schema name of the index refers to an existing schema

DBADM authority

Examples

Example 1: In the following example, a text search index with index_schema 'db2ts' and index_name 'myTextIndex' is created using the SYSTS_CREATE procedure. The option 'UPDATE MINIMUM 10' specifies that at least 10 changes should be made to the text documents associated with the index before an incremental update of the index should be performed. Any error messages are requested to be returned in English. When the underlying text search command runs successfully, the output parameter message is set to indicate the status of the command execution.

  CALL SYSPROC.SYSTS_CREATE('db2ts', 'myTextIndex', 
   'myUserSchema.myBaseTable (myTextColumn)', 'UPDATE MINIMUM 10', 
   'en_US', ?)

The following example is a sample output from this query.

  Value of output parameters   
  --------------------------   
  Parameter Name  : MESSAGE   
  Parameter Value : Operation completed successfully.    
  Return Status = 0

Example 2: In the following example, SYSTS_CREATE is called to create a text search index with index_schema 'db2ts' and index_name 'myTextIndex'. No options are specified. In this example, the index already exists which results in an error message being returned to the caller.

  CALL SYSPROC.SYSTS_CREATE('db2ts', 'myTextIndex', 
   'myUserSchema.myBaseTable (myTextColumn)', '', 'en_US', ?)

The following example is a sample output from this query.

  SQL20427N An error occurred during a text search administration 
  procedure or command. The error message is "CIE00201 Text search 
  index "db2ts"."myTextIndex" already exists. ".

Usage notes

Text search administration procedures use an existing connection to the database. The current transaction might be committed or rolled back depending on the completion of the procedures. As such, you might want to commit all transaction changes to avoid any unexpected impact from such a commit or rollback. One way to achieve this is to turn on AUTOCOMMIT for the connection.
Note: You may run into a deadlock if a transaction is not committed before executing a text search administration operation, as the same database objects may be affected and the operation waits for the previous step to commit. This may occur, for example, if AUTOCOMMIT is turned off and a table is created followed by creating a text index without an explicit commit between the two transactions.
Multiple procedures or commands cannot be executed concurrently on a text search index if they might conflict. Some of the conflicting procedures and commands are:
- SYSTS_ALTER procedure or ALTER INDEX db2ts command
- SYSTS_CLEAR_EVENTS procedure or CLEAR EVENTS FOR INDEX db2ts command
- SYSTS_DISABLE procedure or DISABLE DATABASE FOR TEXT db2ts command
- STOP FOR TEXT db2ts command
- SYSTS_UPDATE procedure or UPDATE INDEX db2ts command
If there is a conflict, the procedure returns an SQLCODE -20426 and SQLSTATE 38H13.
With the successful execution of the CREATE INDEX command:
- DB2 Text Search server data is updated. A collection of name instance_database-name_index-identifier_number is created, as in the following example:
```
tigertail_MYTSDB_TS250517_0000
```
  The collection name can be retrieved from the SYSIBMTS.TSCOLLECTIONNAMES view (column COLLECTIONNAME).
- DB2 Text Search catalog information is updated. An index staging table is created in the specified table space with appropriate DB2 indexes. In addition, an index event table is created in the specified table space.
- When DB2 Text Search coexists with DB2 Net Search Extender and an active Net Search Extender index already exists for the table column, the new text index is set to inactive.
- The newly created text search index is not automatically populated. The SYSTS_UPDATE procedure or UPDATE INDEX command must be executed either manually or automatically (as a result of an update schedule having been defined for the index through the specification of the UPDATE FREQUENCY option) for the text search index to be populated.
- The Text Search index data file on the DB2 database server is updated. Scheduled update information is recorded for each index in the instance.

Usage restrictions:

A primary key must be defined for the table. In DB2 Text Search, a multicolumn DB2 primary key can be used without type limitations. The number of primary key columns is limited to 2 columns less than the number of primary key columns allowed by DB2.
The total length of all primary key columns for a table with DB2 Text Search indexes is limited to 15 bytes less than the maximum total primary key length allowed by DB2. Refer to the DB2 restrictions of DB2 CREATE INDEX statement.