DB2 10.5 for Linux, UNIX, and Windows

SYSTS_ALTER procedure - Change the update characteristics of an index

This procedure is used to change the update characteristics of an index.

The procedure issues an ALTER INDEX text search administration command on the database server.

Authorization

The privileges that are held by the authorization ID of the statement must include the SYSTS_MGR role and at least one of the following authorities:

DBADM authority
ALTERIN privilege on base schema
CONTROL or ALTER privilege on the base table on which the text search index is defined

To change an existing schedule, the authorization ID must be the same as the index creator, or must have DBADM authority.

Default PUBLIC privilege

None

Syntax


>>-SYSTS_ALTER--(--index_schema--,--index_name--,--------------->

>--| update characteristics |----------------------------------->

>--| options  |--,--message_locale--,--message--)--------------><

update characteristics

|--+--------------------------------------------+--------------->
   '-UPDATE FREQUENCY--+-NONE-----------------+-'   
                       '-| update frequency |-'     

>--+----------------------------+-------------------------------|
   '-UPDATE MINIMUM--minchanges-'   

update frequency

|--D-(-+-*------------+-)--H-(-+-*------------+-)--------------->
       | .-,--------. |        | .-,--------. |     
       | V          | |        | V          | |     
       '---integer1-+-'        '---integer2-+-'     

       .-,--------.     
       V          |     
>--M-(---integer3-+-)-------------------------------------------|

options

|--+----------------------------------+-------------------------|
   +-| index configuration options  |-+   
   '-| activation options |-----------'   

index configuration options

|--+-------------------------------------------+----------------|
   '-INDEX CONFIGURATION--(-| option-value |-)-'   

option-value

|--+----------------------------------------------+-------------|
   +-UPDATEAUTOCOMMIT--+-+--------------------+-+-+   
   |                   | '-commitcount_number-' | |   
   |                   '-+------------+---------' |   
   |                     '-commitsize-'           |   
   +-COMMITTYPE--committype-----------------------+   
   '-COMMITCYCLES--commitcycles-------------------'   

activation options

|--+-----------------------------------+------------------------|
   SET--+-ACTIVE---+--+------------+---'   
        '-INACTIVE-'  '-UNILATERAL-'

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

update characteristics

An input argument of type VARCHAR(32K) that specifies the alter options. The following alter options are allowed:

UPDATE FREQUENCY

Specifies the frequency with which index updates are made. The indexes are updated if the number of changes is at least the value that is set for UPDATE MINIMUM. The update frequency NONE indicates that no further index updates are made. This can be useful for a text column in a table with data that does not change. It is also useful if you intend to manually update the index by using the UPDATE INDEX command. You can do automatic updates if the DB2_ATS_ENABLE registry variable is set and you issue the START FOR TEXT command.

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

NONE

No automatic updates are applied to the text index. You must start any further index updates manually.

D

The days of the week when the index is updated.

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

H

The hours of the specified days when the index is updated.

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

M

The minutes of the specified hours when the index is updated.

integer3

If you do not specify the UPDATE FREQUENCY option, the frequency settings are unchanged.

UPDATE MINIMUM minchanges

Specifies the minimum number of changes to text documents that must occur before the index is incrementally updated. Multiple changes to the same text document are treated as separate changes. If you do not specify the UPDATE MINIMUM option, the setting is unchanged.

INDEX CONFIGURATION (option-value)

This is an optional input argument of type VARCHAR(32K) that allows altering text index configuration settings. The following options are supported:

Table 1. Specifications for option-value
Option	Value	Data type	Description
SERIALUPDATE	`updatemode`	Integer	Specifies whether the update processing for a partitioned text search index must be run in parallel or in serial mode. In parallel mode the execution is distributed to the database partitions and issues independently on each node. In serial mode the execution is run without distribution and stops when a failure is encountered. Serial mode execution usually takes longer but requires less resources. 0 = parallel mode 1 = serial mode
UPDATEAUTOCOMMIT	`commitsize`	String	Specifies the number of rows or number of hours after which a commit is run to automatically preserve the previous work for either initial or incremental updates. If you specify the number of rows: After the number of documents that are updated 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 text index is committed after the specified number of hours is reached. The maximum number of hours is 24. For initial updates, 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 operational identifier '4'. This key is used to restart update processing either after a failure or after the number of specified `commitcycles` are completed. If a `commitcycles` is specified, the update mode is modified to incremental to initiate capturing changes by using the LOGTYPE BASIC option to create triggers on the text table. However, until the initial update is complete, log entries that are generated by documents that have not been processed in a previous cycle are removed from the staging table. Using the UPDATEAUTOCOMMIT option for an initial text index update leads to a significant increase of execution time. For incremental updates, log entries that are processed are removed correspondingly 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 for unlimited cycles. If you do not explicitly specify cycles, the update operation uses as many cycles as required based on the batch size that you specified with the UPDATEAUTOCOMMIT option to finish the update processing. You can use this option with the UPDATEAUTOCOMMIT setting with a `committype`.

activation options

Specifies an input argument of type integer that sets the status of a text index.

ACTIVE: Sets the text index status to active.

INACTIVE: Sets the text index status to inactive.

UNILATERAL: Specifies a unilateral change that affects the status of DB2 Text Search indexes. If you specify this argument, only the status of a DB2 Text Search index is changed to active or inactive. Without the UNILATERAL argument, the activation status of the DB2 Text Search and DB2 Net Search Extender indexes is jointly switched so that only one of the text indexes is active.

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.

Examples

Example 1: In the following example, the update characteristics of a text search index are being altered. This index was originally created with index_schema 'db2ts' and index_name 'myTextIndex'. By using 'UPDATE FREQUENCY NONE', the intention is to make no further updates to the text search index as possibly no changes are expected for the associated table column. Any error messages are requested to be returned in English. When the procedure is complete, a success message is returned to the caller.

  CALL SYSPROC.SYSTS_ALTER('db2ts', 'myTextIndex', 
   'UPDATE FREQUENCY NONE', 'en_US', ?)

The following example is of 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, the SYSTS_ALTER stored procedure is called to alter the update-characteristics for a text search index with index_schema 'db2ts' and index_name 'myTextIndex2'. The intention is to ensure that updates to the index occur every hour on the hour. However, in this example the index does not exist and results in an error.

  CALL SYSPROC.SYSTS_ALTER('db2ts', 'myTextIndex2', 
   'update frequency D(*) H(*) M(0)', 'en_US', ?)

The following example is of output from this query.

  SQL20427N An error occurred during a text search administration 
  procedure or command. The error message is "CIE00316 Text search 
  index "db2ts"."myTextIndex2" does not exist. ". SQLSTATE 38H14

Usage notes

Text search administration procedures use an existing connection to the database. You should commit all transaction changes before issuing a text search administration procedure to avoid any unexpected impact from a commit or rollback. One way to achieve this is to turn on AUTOCOMMIT.
You cannot run multiple procedures or commands 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
- SYSTS_DROP procedure or DROP INDEX db2ts command
- SYSTS_UPDATE procedure or UPDATE INDEX db2ts command
- SYSTS_CONFIGURE procedure
If there is a conflict, the procedure returns an SQLCODE -20426 and SQLSTATE 38H13.
When you run this procedure to change the frequency, a schedule task is created, updated or dropped for the text index.

The result of activating indexes depends on the original index status. The following table describes the results.

Table 2. Status changes without invalid index:
Initial DB2 Text Search or Net Search Extender Status	Request Active	Request Active Unilateral	Request Inactive	Request Inactive Unilateral
Active / Inactive	No change	No change	Inactive / Active	Inactive / Inactive
Inactive / Active	Active / Inactive	Error	No change	No change
Inactive / Inactive	Active / Inactive	Active / Inactive	Inactive / Active	No change

SQL20427N and CIE0379E error messages are returned for active index conflicts.

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.

The UPDATEAUTOCOMMIT, COMMITTYPE, and COMMITSIZE index configuration options are also specified for an UPDATE INDEX operation to override the configured values. Values that you submit for a specific update operation are applied only once and not persisted.