SYSPROC.SYSTS_UPDATE

Invoke the SYSPROC.SYSTS_UPDATE stored procedure to update the text search index to reflect the current contents of the text column that the text search index is associated with.

After the stored procedure completes updating the text search index, the collection data from the text search server is backed up to a Db2 table.

Because updating a text search index is an extensive operation, the text search index that is maintained on the text search server is not updated synchronously when the Db2 table is updated. Instead, changes to the Db2 table column are captured by a trigger to a local log table. The text search index on the text search server is updated the next time that you invoke the SYSPROC.SYSTS_UPDATE stored procedure. Therefore, some search requests might not reflect recent updates to the table.

This stored procedure returns only after all of the update processing for the text search index on the text search server is completed. The duration depends on the number of new documents that are being indexed and the number of documents that are already indexed. During the update process, the text search index remains searchable.

If individual documents contain errors, you must correct the documents, update the table, and update the text search index again. You can look up the ROWIDs of the erroneous documents in the table SYSIBMTS.EVENTS_[n], where [n] is the index ID according to the INDEXID column of the SYSIBMTS.SYSTEXTINDEXES administration table. When you change the corresponding rows in the table, the next SYSPROC.SYSTS_UPDATE stored procedure request can process these documents again.

Tip: Use the Db2 administrative task scheduler to call the SYSPROC.SYSTS_UPDATE stored procedure according to a specific schedule by setting the UPDATE FREQUENCY option in the SYSPROC.SYSTS_CREATE stored procedure. For more information about the Db2 administrative task scheduler, see Scheduling administrative tasks.

Important: The text search server might block the index update process if multiple index updates are being performed at the same time. The text search server might also block the index update process if the configuration value for the number of concurrent text search indexing subsystems is too small.

Note: All stored procedures can perform COMMIT statements on the current connection. This capability makes it possible to keep changes on the text search server consistent with your Db2 subsystem. After you invoke a stored procedure, you cannot roll back the changes that are made on your Db2 subsystem or the text search server. Rollback in a stored procedure is done only to a savepoint that is set when you invoke the stored procedure.

Prerequisites

Before you invoke the SYSPROC.SYSTS_UPDATE stored procedure, verify the following prerequisites:

Db2 text search functionality was started by invocation of the SYSPROC.SYSTS_START stored procedure.
The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).
The DB2ENCRYPTEDPW column in the SYSIBMTS.SYSTEXTSERVERS table has a valid, encrypted password value for the user ID that the text search server uses to connect to Db2 for z/OS®.
The following stored procedures are not running for the text search index that you want to update: SYSPROC.SYSTS_CREATE, SYSPROC.SYSTS_DROP, SYSPROC.SYSTS_RESTORE, and SYSPROC.SYSTS_TAKEOVER.
At least one text search server is running.
The distributed data facility (DDF) must be started, even if you call the stored procedure locally on your z/OS system. The DDF is required to allow the text search server to connect to Db2 to store a text search index.

Authorization

The user ID under which this stored procedure is invoked must have the following privileges:

EXECUTE on the procedure
EXECUTE on the packages SYSIBMTS.*
SELECT and INDEX privileges on the table that the text search index is created on

Also, if the SQL ID that calls this stored procedure is different from the SQL ID that called the SYSPROC.SYSTS_CREATE stored procedure, the following index privileges are required:

SELECT, INSERT, UPDATE (on the column TOBEDELETED), and DELETE privileges on table SYSIBMTS.STAGING_[n]
INSERT privilege on table SYSIBMTS.EVENTS_[n]

where [n] is the index ID according to the INDEXID column of the SYSIBMTS.SYSTEXTINDEXES administration table.

In addition, the user ID that is listed in the SYSIBMTS.SYSTEXTCONNECTINFO table must have SELECT, INSERT, UPDATE, and DELETE privileges for the text search index table. These privileges must be valid for the user ID after the user ID is used to connect to Db2 for z/OS on a T4 Java™ connection and going through DRDA connect processing. The database name is SYSIBMTS. The index table name is SYSIBMTS.INDEX_[n], where [n] is the index ID according to the INDEXID column of the SYSIBMTS.SYSTEXTINDEXES administration table.

Syntax

The schema qualifier is SYSPROC.

options-spec

index-configuration-options

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.

Recommendation: Use a valid SQL name for this parameter.

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 full-text index in the Db2 subsystem. You must specify a non-null value for this parameter.

Recommendation: Use a valid SQL name for this parameter.

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

options-spec

A character string that specifies options that are available for this stored procedure. You can specify only one option.

USING UPDATE MINIMUM: This option uses the USING UPDATE MINIMUM settings that you specified for the SYSPROC.SYSTS_CREATE stored procedure and starts an incremental update only if the specified number of changes was reached. The default is to unconditionally start the update process.
ALLROWS: This option uses ALLROWS to start an initial update. Use this option when a load replace to the base table is done, or when you need to restore data from the base table. If a synonym dictionary has been created for the text search index, using the ALLROWS option will remove the dictionary.

index-configuration-options

Specifies additional index-specific values as option value pairs. You must enclose string values in single quotation marks. A single quotation mark within a string value must be represented by two consecutive single quotation marks.

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 default value is 100 and COMMITTYPE specified as ROWS.

UPDATEAUTOCOMMIT requires a numeric value. This value represents either a number of rows or a number of hours, depending on the specification for COMMITTYPE. If COMMITTYPE is set to HOURS, the value cannot exceed 24. The text index is committed when the value of UPDATEAUTOCOMMIT is reached.

If update processing takes a very long time and Db2 active logs are small, consider using the default value. Otherwise, the active log entries of customer transactions that run in parallel to the text search index update cannot be cleared and might lead to a full the active log.

COMMITTYPE

Specifies either ROWS or HOURS. If you specify ROWS, the text index is committed after the number of rows that is specified by UPDATEAUTOCOMMIT is reached. If you specify HOURS, the text index is committed after the period of time that is specified by UPDATEAUTOCOMMIT is reached. The default value is ROWS.

You must use this option in conjunction with UPDATEAUTOCOMMIT or COMMITCYCLES.

COMMITCYCLES

Specifies a numeric value for the number of commit cycles for the processing of an index update. By default, the number of commit cycles is unrestricted. If COMMITCYCLES is 0 (zero), the update process uses as many cycles as needed to finish processing.

You must use this option in conjunction with UPDATEAUTOCOMMIT and COMMITTYPE.

Examples

Example 1: This example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_UPDATE stored procedure. This example uses the SYSPROC.SYSTS_UPDATE stored procedure to update the text search index with the current text column that the text search index is associated with.



CALL SYSPROC.SYSTS_UPDATE('SCHEMA1','IVC2','')

Here is a list of the parameters used in this example.

'SCHEMA1' = indexSchema
'IVC2' = indexName
' ' = options

The options parameter is blank by default. The default is to unconditionally start the update process.

Example 2: The following examples show how you can use the UPDATEAUTOCOMMIT, COMMITTYPE, and COMMITCYCLES options to specify how often a commit operation is performed when documents are fetched during an index update. In the first example, the commit operation is performed after two hours with two commit cycles.

CALL SYSPROC.SYSTS_UPDATE('USRT002', 'ITEST', 
 'INDEX CONFIGURATION ( UPDATEAUTOCOMMIT 2, COMMITTYPE HOURS ,COMMITCYCLES 2)')

In the next example, the commit operation is performed after two hours with no restriction on the number of commit cycles.

CALL SYSPROC.SYSTS_UPDATE('USRT002', 'ITEST', 
 'INDEX CONFIGURATION ( UPDATEAUTOCOMMIT 2, COMMITTYPE HOURS )')

In this example, the commit operation is performed after 2000 rows with no restriction on the number of commit cycles.

CALL SYSPROC.SYSTS_UPDATE('USRT002', 'ITEST', 
 'INDEX CONFIGURATION ( UPDATEAUTOCOMMIT 2000 )')