SYSTS_UPDATE procedure - Update the text search index

While the update is being performed, a search is possible. Until completion of the update, the search operates on a partially updated index.

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

Authorization

Default PUBLIC privilege

None

Syntax

>>-SYSTS_UPDATE--(--index_schema--,--index_name--,-------------->

>--update_options--,--message_locale--,--message--)------------><

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_options

An input argument of type VARCHAR(32K) that specifies update options. If no options are needed the argument can be null or an empty string. The possible values are:

UPDATE OPTIONS value	Description
USING UPDATE MINIMUM	This option enforces the use of the UPDATE MINIMUM value that is defined for the text search index and processes updates if the specified minimum number of changes have occurred.
FOR DATA REDISTRIBUTION	This option specifies that a text search index in a partitioned database needs to be refreshed after data partitions were added or removed and a subsequent data redistribution operation completed. Search results may be inconsistent until the text search index was updated with the FOR DATA REDISTRIBUTION option.
ALLROWS	This option specifies that an initial update should be attempted unconditionally.
UPDATEAUTOCOMMIT	This 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 number of documents updated reaches the COMMITCOUNT number, the server does an interim commit. Log entries generated by unprocessed documents 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 which have been processed are 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, 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.
COMMITTYPEcommittype	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.
COMMITCYCLEScommitcycles	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.

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

  CALL SYSPROC.SYSTS_UPDATE
  ('db2ts', 'myTextIndex', '', 'en_US', ?)

  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : Operation completed successfully.

  CALL SYSPROC.SYSTS_UPDATE
  ('db2ts', 'myTextIndex2', 'FOR DATA REDISTRIBUTION', 'en_US', ?) 

  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : Operation completed successfully.

  Return Status = 0

Example 3: In the following example, SYSTS_UPDATE is called to update a text search index with index_schema 'db2ts' and index_name 'myTextIndex3'. This index does not exist and results in an error.

  CALL SYSPROC.SYSTS_UPDATE('db2ts', 'myTextIndex3', 'USING UPDATE MINIMUM', 
		'en_US', ?)

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

Usage notes

• Text search administration procedures use an existing connection to the database. The current transaction might be committed or rolled back depending on the completion of the procedures. To avoid any unexpected impact from such a commit or rollback, you might want to commit all transaction changes. Turning on AUTOCOMMIT is one way to commit all transaction changes.
• Certain procedures or commands cannot be executed concurrently on a text search index because the timing of the conflicting operation might cause an error. Some of the conflicting procedures and commands are:
  • SYSTS_ALTER procedure or db2ts ALTER INDEX command
  • SYSTS_CLEAR_EVENTS procedure or db2ts CLEAR EVENTS FOR INDEX command
  • SYSTS_DISABLE procedure or db2ts DISABLE DATABASE FOR TEXT command
  • SYSTS_UPDATE procedure or db2ts UPDATE INDEX command
  If there is a conflict, the procedure returns an SQLCODE -20426 and SQLSTATE 38H13.
• This procedure does not return until all index update processing is completed. The duration depends on the number of documents to be indexed and the number of documents already indexed. The collection name for the index can be retrieved from the SYSIBMTS.TSCOLLECTIONNAMES view COLLECTIONNAME column.
• When there are individual document errors, the documents must be corrected. The primary keys of the erroneous documents can be looked up in the event table for the index. By changing the corresponding rows in the user table, the next call to SYSTS_UPDATE reprocesses these documents.
• When the SYSTS_UPDATE procedure is run, the following events occur:
  • Rows are inserted into the event table, including parser error information. Information is deleted from the index staging table in case of incremental updates. Before the first update, the SYSTS_UPDATE procedure creates triggers on the user table.
  • The collection is updated:
    • New or changed documents are parsed and indexed.
    • Deleted documents are discarded from the index.
• If a synonym dictionary has been associated with a text index, executing the update with the ALLROWS or FOR DATA REDISTRIBUTION options removes the association by dropping and recreating the collections for the text index. The synonym dictionary must be associated with the new text index collections.
• 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.
• When you specify UPDATEAUTOCOMMIT, COMMITTYPE or COMMITSIZE values for the update operation, they override existing configured values only for the specific update and are not persisted.