SYSPROC.SYSTS_ALTER

You can call the SYSPROC.SYSTS_ALTER stored procedure to modify attributes of an index that was created by SYSPROC.SYSTS_CREATE. Only attributes explicitly specified on this procedure are changed. All other attributes of the index remain unchanged.

This is useful if you need to change the attributes of the index, such as the update frequency, after the index has already been created.

Prerequisites

Before you call the SYSPROC.SYSTS_ALTER stored procedure, verify the following prerequisite:

The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).

Authorization

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

The privilege to create in the schema. For more information, see Authorization, privileges and object ownership.
Administrative authority

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

The following system authorities:
- *USE to the Create Logical File (CRTLF) command
- *CHANGE to the data dictionary if the library into which the text index is created is an SQL schema with a data dictionary
Administrative authority

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

For the referenced table:
- The INDEX privilege on the table
- The system authority *EXECUTE on the library containing the table
Administrative authority
If SQL names are specified, and a user profile exists that has the same name as the library into which the text index is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following authorities:
- System authority *ADD to the user profile with that name
- Administrative authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following privileges:

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
Administrative authority

For information about the system authorities corresponding to SQL privileges, see GRANT (Table or View Privileges).

Syntax

The schema qualifier is SYSPROC.

Parameters

indexSchema

Identifies the schema of the text search index. If this parameter is null, the value of the CURRENT SCHEMA special register for the invoker is used.

The data type of this parameter is VARCHAR(128).

indexName

Identifies the name of the text search index. The name of the text search index together with the index schema uniquely identifies the text search index in the DB2® subsystem. You must specify a non-null value for this parameter.

The data type of this parameter is VARCHAR(128).

options

A character string that specifies the various options that are available for this stored procedure.

The data type of this parameter is VARCHAR(32000).

The parameter cannot be NULL.

options

rename-function-information

Specifies the user-defined function to be renamed.

function-schema.function-name: Specifies the schema and name of a user-defined function.

This option is used to change a function that was specified while creating an index. If the function is changed, SYSTS_UPDATE uses the new function to index the text column.

If the function was changed, SYSTS_UPDATE does not change the existing data of the index. Only the new changed data after the last update is processed.

update-characteristics

update-characteristics

Specifies the frequency of automatic updates to the text search index. Also specifies the minimum number of changes to text documents before the text search index is updated incrementally at the specified time.

UPDATE FREQUENCY update-frequency

Specifies when to make automatic updates to the text search index. The default value is NONE. This option might be useful for a text column in which there are no further changes. The format of the update-frequency option supports two different formats.

update-frequency (Format 1)

NONE: If NONE is specified, then no further index updates are made. The update must be started manually. This option might be useful for a text column in which no further changes are planned.
D: Specifies the day or days of the week when the index is updated. An asterisk (*) specifies all days. 0 specifies Sunday.
H: Specifies the hour or hours when the index is updated. An asterisk (*) specifies all hours.
M: Specifies the minute or minutes when the index is updated. An asterisk (*) cannot be specified. The minimum update frequency is 5 minutes.

Example: This example specifies that the index update is to run every 30 minutes.

UPDATE FREQUENCY D(*) H(*) M(0,30)

update-frequency (Format 2, chronological)

The format of the update-frequency (chronological) option is a list of the five values separated by a blank space. The five values represent the minutes, hours, days of the month, months of the year, and days of the week beginning with Sunday.

If you specify an interval of values or an asterisk (*), you can specify a step value by using a forward slash (/) at the end of the defined interval.

Example: This example specifies that the index update is to run every quarter hour (0,15,30,45) on the even hours between 8 a.m. and 6:45 p.m. (8-18/2 is equivalent to 8,10,12,14,16,18), from Monday to Friday every month of the year (* * 1-5).

0,15,30,45 8-18/2 * * 1-5

minute: Specifies the minutes of the hour when the text search index is to be updated. You can specify an asterisk (*) for an interval of every 5 minutes, or you can specify an integer 0 - 59. You cannot repeat values. The minimum update frequency is 5 minutes. A value of 1,4, or 8 is not allowed.
update-frequency (minute)
hour: Specifies the hours of the day when the text search index is to be updated. You can specify an asterisk (*) for every hour, or you can specify an integer 0 - 23. You cannot repeat values.
update-frequency (hour)
dayOfMonth: Specifies the days of the month when the text search index is to be updated. You can specify an asterisk (*) for every day, or you can specify an integer 1 - 31. You cannot repeat values.
update-frequency (dayOfMonth)
monthOfYear: Specifies the months of the year when the text search index is to be updated. You can specify an asterisk (*) for every month, or you can specify an integer 1 - 12. You cannot repeat values.
update-frequency (monthOfYear)
dayOfWeek: Specifies the days of the week when the text search index is to be updated. You can specify an asterisk (*) for every day, or you can specify an integer 0 - 7. Both 0 and 7 are valid values for Sunday. You cannot repeat values.
update-frequency (dayOfWeek)

UPDATE MINIMUM minchanges

Specifies the minimum number of record changes made to the underlying table before the text search index is updated incrementally at the time specified in the update-frequency option. The value must be an integer 1 - 2147483647.

index-configuration-options

ROW_COLUMN_ACCESS

COMMENT

Specifies a comment that is stored in the REMARKS column of the QSYS2.SYSTEXTINDEXES administration table and as the description of the OmniFind Text Search Server for DB2 for i collection.

The value for this option is a string value that is less than or equal to 512 bytes.

UPDATEAUTOCOMMIT

Specifies how often a commit operation is performed when fetching documents during an index update. A value of 0 (zero) means that a commit operation occurs only at the end of processing.

The value must be an integer between 0 (zero) and 2147483647.

Performance tip: The value of UPDATEAUTOCOMMIT can have a substantial impact on the performance of index updates. The commit operation that takes place at the specified interval ensures a consistent checkpoint from which to restart the index update, if it is interrupted. However, the commit also temporarily suspends the update process. Increasing the UPDATEAUTOCOMMIT value (or setting it to 0) can substantially improve the update performance, especially the initial update. The value you specify must balance the need for performance with the need for recoverability, based on the frequency of the index updates.

ROW_COLUMN_ACCESS

Specifies whether the text search index is considered secure for row access control and column access control.

NOT_SECURED

Specifies that the text search index is considered not secure for row access control and column access control. This is the default.

The based on table for the index must not have an active permission or Mask.

SECURED

Specifies that the index is considered secure for row access control and column access control.

A text search index must be defined as secured to be built over a table with an active permission or column Mask. If a function is referenced to access or modify data in a masked column, the function must be defined as secured. The authorization ID must have DB_SECADMIN authority to create a text search index with the SECURED attribute.

When a text search index is built over sensitive information there are additional considerations:

The database administrator needs to be aware that the data specified as key column(s) for the text search index will be stored in a staging table in QSYS2 and sent to the text search server using network protocols.

The database administrator needs to be aware that data indexed by a text search index is sent to the text search server using network protocols and stored outside of DB2 on the text search server.

Tips: If users alter an index from SECURED to NOT SECURED, users have to make sure the based table must not have an active permission mask. Or an error will be thrown.