SYSIBMTS.TSSTAGING view

The staging table stores the change operations on the user table that requires synchronization with the text search index.

Triggers are created on the user table when the default LOGTYPE BASIC option is enabled to insert change information into the staging table. Alternatively, if the LOGTYPE CUSTOM option is enabled, you must populate the staging table manually. In addition, with the auxiliary log option, integrity processing detects changes to the user table. The UPDATE INDEX FOR TEXT command reads the entries and deletes them after successful synchronization.

The database might have multiple views with the prefix SYSIBMTS.TSSTAGING_. Each view is differentiated by the nnnnnn value, an internal identifier that points to the corresponding text index that the view is associated with. To determine the text search index that is associated with a particular view, query the view SYSIBMTS.TSINDEXES, searching for the schema name and view name in the columns STAGINGVIEWSCHEMA and STAGINGVIEWNAME. The query returns a single row that describes the text search index and user table in question.

The number of columns in this view depends on the number of primary key columns in the user table. The columns PK1..PKnn match the primary key columns of the user table and have corresponding data type and lengths definitions. The data type of each of the columns in the view exactly corresponds to the data type of the corresponding primary key column.

Each row in this view represents an insert, a delete, or an update operation on a user table row or text document. You can identify the text document by retrieving the primary key column values from the row in this view and looking them up in the user table.

You can use the following query to obtain information about the view:
	db2 "SELECT STAGINGVIEWSCHEMA, STAGINGVIEWNAME from SYSIBMTS.TSINDEXES
	where INDSCHEMA=schema-name and INDNAME=index-name"
Table 1. SYSIBMTS.TSSTAGING view
Column Name Data type Nullable? Description
OPERATION INTEGER NO The operation on the base table to be reflected on the text search index.

This column has the following four values:

  • 0 = insert
  • 1 = update
  • 2= delete
  • 4 = restart. You must not set or use this value for a manual insert as it leads to a wrong operation message for incremental index updates.
TIME TIMESTAMP NO Sequence ID of a row (when an insert, an update, or a delete trigger is fired). This is a timestamp but might not exactly represent the time of the operation.
BKUPSTATUS INTEGER NO Processing status of the row:
-1 unprocessed

0 means processed

>0 Backup count ( If the backup is enabled for index.)
PK01 Data type of the key columns in the indexed table YES First primary key column of the base table.
... ... ... ...
PKnn Data type of the key columns in the indexed table YES Last primary key column of the base table.