Optional: Migrating to replication and publishing Version 9.7 Fix Pack 5 (Linux, UNIX, Windows)

The optional migration to Version 9.7 Fix Pack 5 on Linux, UNIX, and Windows adds new indexes on two control tables to help improve performance.

The migration scripts add the following indexes:

IBMQREP_SUBS table: On the SUB_ID column to avoid a table scan while generating a unique value for the SUB_ID column.
IBMQREP_TARGETS table: On the SPILLQ and STATE columns to improve the performance of Q Apply while processing a Q subscription from a spill queue.
IBMQREP_EXCEPTIONS table: On the EXCEPTION_TIME column to improve the processing time of queries that are performed by the Q Replication Dashboard.

Migrating the Q Apply control tables

Follow these steps to migrate the Q Apply control tables.

Stop the Q Apply program.
Note: If your Q Capture program is processing a high volume of data, the receive queue could fill while Q Apply is stopped, prompting the source transmission queue to fill and Q Capture to stop. If this happens, you can start Q Capture after Q Apply migration is complete and Q Apply then starts processing messages on the receive queue.
Migrate the DB2® instance to Version 9.7 Fix Pack 5 by running the fix pack installer, which also upgrades the Q Apply server to Version 9.7 Fix Pack 5.
From this point, do not use the replication administration tools to modify your configuration (for example, add a Q subscription) until the Q Apply control tables are migrated.

Migrate the control tables:

Find the Q Apply migration script for your DB2 or federated target in the sqllib/samples/repl/mig97/q/ directory:

Platform	Migration script file
DB2	asnqappluwv97fp.sql
Informix	asnqappinfxv97fp.sql
Oracle	asnqapporav97fp.sql
Microsoft SQL Server	asnqappmssqlv97fp.sql
Sybase	asnqappsybv97fp.sql
Teradata	asnqappteradatav97fp.sql

Delete or comment out statements that apply to fix packs other than Version 9.7 Fix Pack 5.
In the remainder of the script, replace the schema and table space names as required and run the script.

You can now start the Version 9.7 Fix Pack 5 Q Apply program to work with your Q Capture program.

Migrating the Q Capture control tables

Follow these steps to migrate the Q Capture control tables.

If you are migrating from Version 9.5 or earlier, quiesce all applications on the Q Capture server and let Q Capture read to the end of the DB2 log. This action ensures that no pre-Version 9.7 log records are generated that cannot be read by the V9.7 DB2 or Q Capture.
Stop the Q Capture program.
Migrate the DB2 instance to Version 9.7 Fix Pack 5 by running the fix pack installer, which also upgrades the Q Capture server to Version 9.7 Fix Pack 5.
From this point, do not use replication administration to modify your replication configuration (for example to add a Q subscription) until your Q Capture control tables are migrated.
Migrate the control tables:
1. Find the migration script for your DB2 or Oracle source database in the sqllib/samples/repl/mig97/q/ directory.
  Platform Migration script file
  
  DB2 asnqcapluwv97fp.sql
  
  Oracle asnoqcapluwv97fp.sql
2. Delete or comment out statements that apply to fix packs other than Version 9.7 Fix Pack 5.
3. Change the variable for the Q Capture schema to your schema.
4. Run the script.
If all of the Q Apply programs that this Q Capture program replicates to are migrated to Version 9.7 Fix Pack 5, ensure that the value of the COMPATIBILITY column in the IBMQREP_CAPPARMS table is 0907. You can use the following SQL statement to update the value:
```
UPDATE schema.IBMQREP_CAPPARMS
SET COMPATIBILITY = '0907';
```
Start the Version 9.7 Fix Pack 5 Q Capture program. If you are migrating from Version 9.1 or 9.5 to Version 9.7 Fix Pack 5, use the initialization parameters migrate=y and startmode=warmns. If you are migrating from a previous Version 9.7 release or fix pack, these parameters are not required.
Recommendation: Start the Q Capture program as soon as possible after the migration, before you start other applications if possible. DB2 provides replication with the first valid log sequence number (LSN) within the current set of active log files as determined by the LOGPRIMARY database parameter. You should start Q Capture soon enough to ensure that all of the transactions that need to be captured can be found in the active log files.