Incremental updates for Db2 Text Search indexes

Data synchronization in Db2 Text Search is based on processing the content of a staging table that contains information about new, changed, or deleted documents. By default, triggers are created to capture changes in the text table and update the staging table. There is one staging table for each text index. Applying the information in the staging table to the corresponding text index is referred to as performing an incremental update.

You can perform incremental updates by using the following options:
LOGTYPE CUSTOM or BASIC

LOGTYPE BASIC is the default and creates a primary staging table with triggers on the text table to recognize changes.

LOGTYPE CUSTOM creates a primary staging table but does not automatically add any mechanism to recognize changes. Populate the staging table with a replication setup, or by comparing timestamps in the text table, or any other applicable method to identify changed records.

Depending on the data source, the log type might be set automatically and is not customizable. Use the LOGTYPE index configuration option of the CREATE INDEX operation for text search indexes to specify the log type.

AUXLOG ON or OFF

The AUXLOG index configuration option of the Db2 Text Search CREATE INDEX operation controls whether a text-maintained staging table is used for a text search index. This option can be combined with either LOGTYPE basic or BASIC options. If the AUXLOG option is set to ON, along with Logtype BASIC, information about new and deleted documents is captured through integrity processing in an auxiliary staging table that is maintained by Db2 Text Search, and information about changed documents is captured through triggers and stored in the staging table. With LOGTYPE CUSTOM, if the AUXLOG option is set to ON, then information about new, changed, and deleted documents is captured in the auxiliary staging table. By default, this configuration option is set to ON for range-partitioned tables and OFF for nonpartitioned tables.

Capturing changes for an incremental update of the text index through integrity processing might require you to perform more administrative tasks. For example, you might need to issue a RESET PENDING command before text search index updates can be processed. The effect of the text-maintained staging infrastructure is similar to the effect of a materialized query table (MQT) with deferred refresh, and similar limitations and restrictions apply for the creation of an auxiliary staging table as for the creation of an MQT. If you update tables by using only commands that affect all rows in the tables, for example, by using the LOAD REPLACE command, adding the extended staging infrastructure does not provide a benefit. Instead, it is suggested you re-create the text search index after a table is updated.

To create a text index with a LOGTYPE BASIC and AUXLOG ON, see the following example for an initial and incremental update.

  1. Create a table and add data to it.
    db2 "create table test.simple (pk integer not null primary key, 
comment varchar(48))"
db2 "insert into test.simple values (1, 'blue and red')"
  2. Create a text search index. 
    db2ts "create index test.simpleix for text on test.simple(comment) 
index configuration(auxlog on) connect to mydb"
  3. Update the index and load data. 
    db2ts "update index test.simpleix for text connect to mydb"
db2 "load from loaddata4.sql of del insert into test.simple"
  4. After the load operation, the base table is locked. For example, a select operation results in SQL0668N Operation not allowed for reason code "1" on table "TEST.SIMPLE". SQLSTATE=57007. The staging table is accessible, but it does not yet contain the information about the new data.
  5. Enable integrity processing.
    db2 "set integrity for test.simple immediate checked"
    The following message is returned: 
    SQL3601W The statement caused one or more tables to automatically 
be placed in the Set Integrity Pending state.SQLSTATE=01586
  6. At this point, the staging table is locked, and modifying operations for the base table are rejected. For example, the following statement fails:
    "insert into test.simple values(15, 'green')"
    The following message is returned: 
    DB21034E  The command was processed as an SQL statement because 
it was not a valid command line processor command. During SQL processing 
it returned: 
SQL0668N Operation not allowed for reason code "1" on 
table   "SYSIBMTS" ."SYSTSAUXLOG_IX114555".  SQLSTATE=57007
  7. Reset the tables. 
    db2ts "reset pending for table test.simple for text connect to mydb"
    After successfully issuing the RESET PENDING command, the staging table is unlocked and modifications on the base table are again possible. Unlock the staging table either by issuing RESET PENDING command on the base table to unlock all dependent text-maintained staging tables, or with a SET INTEGRITY command on the specific staging table.
  8. The text-maintained staging table now contains the changes that must be applied to the text search index. Issue an update command for the index.
    db2ts "update index test.simpleix for text connect to mydb"