Creating a text index on a nickname with incremental index update using DB2 Replication

Before creating a text index on a nickname using a replication capture table, you must perform the following steps:

1. Set up the DB2® federated database with all server definitions and wrapper definitions.
2. Set up the replication control tables and the capture programs at the remote server. This is where the source table for the nickname resides. If the DB2 database system does not automatically create nicknames, you must create nicknames in the federated DB2 database using one schema name for the following tables:
   • IBMSNAP_SIGNAL
   • IBMSNAP_PRUNE_SET
   • IBMSNAP_PRUNCNTL
   • IBMSNAP_REGISTER
   • IBMSNAP_REG_SYNC (Non-DB2 remote sources only)

   After this step, nicknames for the replication control tables are available as nicknames under one "capture control schema" on the federated DB2 database. This schema name is important for the DB2TEXT CREATE INDEX command.

3. Register the table as a replication source.
4. If the DB2 database system does not automatically create a nickname in the registration step, create a nickname for the replication capture table in the federated database. The replication capture table can either be a Change Data (CD) table or a Consistent Change Data (CCD) table. This nickname is a parameter for the DB2TEXT CREATE INDEX command.

   Note that the column names IBMSNAP_OPERATION, IBMSNAP_COMMITSEQ, IBMSNAP_INTENTSEQ, and the names of the primary key columns must not be changed.

5. If you are using DB2 replication source, ensure that your capture program is running. Do not use a cold start for the capture program: if you do, all rows in the IBMSNAP_SIGNAL table for APPLY_QUAL LIKE 'NSE%' have to be reinserted. In the following SQL statement you can see how this is done:

   INSERT INTO capture_control_schema.IBMSNAP_SIGNAL
   SELECT CURRENT TIMESTAMP, 'CMD', 'CAPSTART', MAP_ID, 'P' 
   FROM capture_control_schema.IBMSNAP_PRUNCNTL
   WHERE APPLY_QUAL LIKE 'NSE

6. You can use the following example to create a text index on a nickname using replication:

   DB2TEXT
   CREATE INDEX indexname FOR TEXT ON nickname (text_column)
   REPLICATION CAPTURE TABLE capture_nickname
   CONTROL TABLE SCHEMA capture_control_schema