This procedure changes the update characteristics of an
index.
The procedure issues an ALTER INDEX text
search administration command on the database server.
Authorization
The
privileges 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 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 alter options allowed are as follows:
- 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
DB2_ATS_ENABLE registry variable is set and the START FOR
TEXT command is issued.
The default frequency value is
taken from the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME='UPDATEFREQUENCY'.
- NONE
- No automatic updates will be applied to the text index. Any further
index update will have 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
If you do not specify the UPDATE FREQUENCY option,
the frequency settings are left 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 left unchanged.
- INDEX CONFIGURATION (option-value)
- This is an optional input argument of type VARCHAR(32K) that allows
altering text index configuration settings. The following option is
supported:
Table 1. Specifications for option-valueOption |
Value |
Data type |
Description |
SERIALUPDATE |
updatemode |
Integer |
Specifies whether the update processing for
a partitioned text search index should be run in parallel or in serial
mode. In parallel mode the execution is distributed to the database
partitions and executes 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
significantly less resources. - 0 = parallel mode
- 1 = serial mode
|
UPDATEAUTOCOMMIT |
commitcount_number |
|
- This integer option 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 staging table with each interim
commit. COMMITCOUNT counts the number of documents
updated, not the number of staging table entries.
Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs,
this 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 the operational
identifier '4'. Use this key to restart update processing either
after a failure or after the number of specified commitcycles are
completed. If you specify a commitcycles , 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 |
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.
|
- activation options
- Starting with Version 9.7 Fix Pack 3 and later fix packs, this
input argument of type integer 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 which only affects the status of DB2 Text Search indexes. If this
argument is specified, 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 succeeds, the output
parameter message indicative of the successful operation is returned
to the caller.
CALL SYSPROC.SYSTS_ALTER('db2ts', 'myTextIndex',
'UPDATE FREQUENCY NONE', 'en_US', ?)
The following
is an example 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, this 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 is an example 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. It is recommended to commit all
transaction changes before executing a text search administration
procedure to avoid any unexpected impact from a commit or rollback
in the procedure. One way to achieve this is to turn on AUTOCOMMIT.
- Multiple procedures or commands cannot be run 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 this procedure is run 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. It is associated by default with the COMMITTYPE rows option
and unrestricted cycles.
- You can specify the UPDATEAUTOCOMMIT, COMMITTYPE and COMMITSIZE
index configuration options 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.