SYSPROC.SYSTS_UPDATE

You can call the SYSPROC.SYSTS_UPDATE stored procedure to update the text search index to reflect the current contents of the text column.

Because updating a text search index is an extensive operation, the text search index is not updated synchronously when the DB2® table is updated. Instead, changes to the DB2 table column are captured by a trigger and written to a staging table. The text search index is updated the next time the SYSPROC.SYSTS_UPDATE stored procedure is invoked or when the UPDATE FREQUENCY option indicates it is updated. Therefore, some search requests might not reflect recent updates to the table.

This stored procedure returns only after all the update processing for the text search index on the OmniFind Text Search Server for DB2 for i is completed. The duration depends on the number of entries in the staging table at the time the SYSTS_UPDATE was called. During the update process, the text search index remains searchable.

If an issue occurred while indexing a document from the base table, the staging table column with the TOBEDELETED has a value set to either E (error) or W (warning). ERRORMSG column has value to record error messages. You can retrieve these records with a stored procedure that includes the following query:

SELECT s.TOBEDELETED, s.ERRORMSG, based_on_columns 
FROM based_on_table t INNER JOIN QSYS2.stagingtables 
ON (QQQ_TEXTSEARCH_KEY(t.k1, t.k2, t.k3, ...) = s.KEYID)
WHERE s.TOBEDELETED IN('E','W')

In this case, based_on_columns is the column list that you need to see from the based_on_table. based_on_table is the table being indexed. staging table is the staging table listed in the catalogs for the text search index. k1, k2, k3, ... is the list of key columns in the primary key, row ID, or unique key that is used to build the text search index. ERRORMSG is a column records the error messages while indexing this document. Once you correct the errors for those documents, run the update again.

If an index update is requested at the same time an update is already running for that index, an error is returned. Only one update is allowed to run at a time for a given index.

If an index is created as SECURED, the unmasked data is retrieved from the base table and be indexed, like there is no mask or permission created on this table.

If there is field procedure created on the base table, the original unmasked data is indexed.

Prerequisites

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

The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).
The following stored procedures are not running for the text search index that you want to update: SYSPROC.SYSTS_CREATE, SYSPROC.SYSTS_UPDATE, and SYSPROC.SYSTS_DROP.
The text search server that the index resides on must have been started using the SYSTS_START stored procedure. The SERVERSTATUS column in QSYS2.SYSTEXTSERVERS must have a value of '0' (started).

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 privileges:
- The 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

If the index was created by SYSTS_CREATE as SECURED index, the authorization ID must have DB_SECADMIN 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 full-text index in the DB2 subsystem. You must specify a non-null value for this parameter.
The data type for this parameter is VARCHAR(128).
options: A character string that specifies the option that is available for this stored procedure.
The available option is USING UPDATE MINIMUM. This option uses the USING UPDATE MINIMUM settings that you specified for the SYSPROC.SYSTS_CREATE stored procedure. It starts an incremental update only if the specified number of changes was reached. The default is to unconditionally start the update process.

USING UPDATE MINIMUM