Sample: Incrementally updating a Db2 Text Search index on range-partitioned tables

Incremental updates of Db2 Text Search indexes on range-partitioned tables require the extended text-maintained staging infrastructure to apply changes from attaching or detaching partitions.

About this task

When the extended staging infrastructure is enabled for the text search indexes, document updates are captured through an update trigger into the primary staging table, and document inserts and deletes are captured in the auxiliary staging table through integrity processing.

When the extended staging infrastructure is not enabled, you cannot use an incremental update to process changes related to attaching or detaching ranges or to process documents that you loaded into an added partition by using the LOAD command with the INSERT parameter. You must re-create the text index to synchronize it with the base table.

By default, the extended text-maintained infrastructure will be added for text search indexes on range-partitioned tables, however, for scenarios where the text search index is not refreshed with incremental updates, you can create the text search index with the AUXLOG option set to OFF as shown in the following example:

db2ts create index sampleix for text on sample(comment) administration tables in 
mytablespace index configuration(auxlog off) connect to mydb

In this case, only a primary staging table is added, and document changes are recognized through triggers, which excludes changes for example, from attach or detach operations. You must specify the ADMINISTRATION TABLES IN parameter when creating indexes on range-partitioned tables; otherwise, an error is generated.

Example

Scenario 1: To attach a partition for a table with the extended text search staging infrastructure

Create a range-partitioned table.

db2 "create table uc_007_customer_archive (pk integer not null 
primary key, customer varchar(128) not null, 
year integer not null, address blob(1M) not null) partition 
by range(year)(starting(2000)ending(2001)every 1)"

Create the text search index.

db2ts "create index uc_007_idx for text on 
uc_007_customer_archive (address)
administration tables in mytablespace"

View the index name and logging information.

db2 "select indexname, stagingviewname, auxstagingname 
from sysibmts.tsindexes"

Update the text search index.

db2ts "update index uc_007_idx for text"

Create another table and import data into the table.

db2 "create table uc_007_customer_2001 (pk integer not null 
primary key,
customer varchar(128) not null, year integer not null, 
address blob(1M) not null)"

db2 "import from uc_007_2001.del of del lobs 
from ./data modified by codepage=1208
insert into uc_007_customer_2001"

Add the data from the new table as a new partition.

db2 "alter table uc_007_customer_archive attach 
partition p2001 starting(2001) ending(2002)
exclusive from uc_007_customer_2001"

View the contents.

db2 "select * from sysibmts.systsauxlog_ix253720"

The output is as follows:

PK       GLOBALTRANSID    GLOBALTRANSTIME      OPERATIONTYPE
----- ---------------    ------------------   ----------------
0 record(s) selected.

The changes are not visible, so integrity processing is required. Integrity processing places dependent tables in pending mode.
```
db2 "set integrity for uc_007_customer_archive immediate checked"
```

View the contents.

db2 "select * from sysibmts.systsauxlog_ix253720"

The following error message is returned:

PK      GLOBALTRANSID    GLOBALTRANSTIME     OPERATIONTYPE
----- -----------------  -----------------   ---------------
SQL0668N Operation not allowed for reason code "1" on table
"SYSIBMTS"."SYSTSAUXLOG_IX253720". SQLSTATE=57007

Perform integrity processing for the text search staging tables. The command processes all text indexes for the table.

db2ts "reset pending for table uc_007_customer_archive for text"
db2 "select * from sysibmts.systsauxlog_ix253720"

The output is as follows:

PK      GLOBALTRANSID       GLOBALTRANSTIME              OPERATIONTYPE
---- --------------------  -----------------------       --------- 
 1   x'000000000002215B'   x'20081020204612500381000000'    1
 2   x'000000000002215B'   x'20081020204612500602000000'    1
 3   x'000000000002215B'   x'20081020204612500734000000'    1
 5   x'000000000002215B'   x'20081020204612500864000000'    1

Use incremental update to process data from the newly attached partition.
```
db2ts "update index uc_007_idx for text"
```

Scenario 2: To detach a partition for a table with extended text search staging infrastructure

Alter the table from the partition.

db2 alter table uc_007_customer_archive detach partition p2005 
into t4p2005

The following message is retuned:

SQL3601W The statement caused one or more tables 
to automatically be placed in the Set Integrity Pending state.  
SQLSTATE=01586

Issue the RESET PENDING command to perform integrity processing for the text search staging tables.
```
db2ts "reset pending for table uc_007_customer_archive for text"
```
Use incremental update to process data from the newly detached partition.
```
db2ts "update index uc_007_idx for text"
```