Migrating to replication and publishing Version 9.7 Fix Pack 2 (Linux, UNIX, Windows)

Migrating to replication and publishing Version 9.7 Fix Pack 2 involves running SQL scripts that add a new control table, add new columns to existing control tables, and change nickname column options for some federated targets.

This migration is for Q Capture and Q Apply servers on Linux, UNIX, and Windows operating systems only. The following sections describe the migration process in more detail:

Control table changes

One new control table is added in V9.7 Fix Pack 2 to assist in replication from range-partitioned tables. New columns are added to two control tables to assist the replication administration tools, and new options are added to some nickname columns if you are replicating to federated targets. Table 1 describes the changes.

Table 1. Control table changes for Version 9.7 Fix Pack 2
Change Description
New control table, IBMQREP_PART_HIST Maintains a history of changes to range-partitioned source tables on Linux, UNIX, or Windows systems. This table is used by Q replication and SQL replication.
New column for IBMQREP_CAPPARMS table CAPTURE_ALIAS column added to store the alias name for the database or subsystem that is used as the Q Capture server.
New column for IBMQREP_APPLYPARMS table APPLY_ALIAS column added to store the alias name for the database or subsystem that is used as the Q Apply server.
Options added to nickname columns to improve Q Apply pruning performance VARCHAR_NO_TRAILING_BLANKS 'Y' option added to IBMQREP_DONEMSG.RECVQ, IBMQREP_SPILLEDROW.SPILLQ, and IBMQREP_SPILLQS.SPILLQ for Oracle targets; BINARY_REP 'Y' added to IBMQREP_DONEMSG.MQMSGID for Microsoft SQL Server targets.

Migrating the Q Apply server first

You can migrate either the Q Capture or Q Apply server first because Version 9.7 Fix Pack 2 does not change the ARCH_LEVEL or COMPATIBILITY value in the control tables.

Follow these steps to migrate the Q Apply server first. You can use this procedure with a single Q Capture program that replicates to a single Q Apply program or to multiple Q Apply programs.

  1. Optional: Stop the Replication Alert Monitor to prevent the monitor from issuing alerts while the replication programs are stopped for migration.
  2. Stop the Version 9.7 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.
  3. Upgrade your DB2® instance by installing Fix Pack 2 on top of DB2 Version 9.7.

    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.

  4. Migrate the control tables:
    1. Find the sample migration script for your DB2 or federated target in the sqllib/samples/repl/mig97/q/ directory:
      Platform Migration script file
      DB2 asnqappluwv97fp.sql
      Oracle asnqapporav97fp.sql
      Microsoft SQL Server asnqappmssqlv97fp.sql
    2. Delete or comment out statements that apply to fix packs other than Version 9.7 Fix Pack 2.
    3. Edit the script to replace the schema name as required and run the script.
  5. You can now start the Version 9.7 Fix Pack 2 Q Apply program to work with your Version 9.7 Q Capture program.

If you are ready to migrate the Q Capture server next, follow these steps:

  1. Stop the Version 9.7 Q Capture program.
  2. Upgrade your DB2 instance by installing Fix Pack 2 on top of DB2 Version 9.7.

    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.

  3. Migrate the control tables:
    1. Find the sample migration script, sqllib/samples/repl/mig97/q/asnqcapluwv97fp.sql.
    2. Edit the script to replace the schema name as required and run the script.
  4. Start the Version 9.7 Fix Pack 2 Q Capture program with the parameters migrate=y and startmode=warmns.
    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.
  5. Start the Replication Alert Monitor if you stopped it.

Migrating the Q Capture server before migrating multiple Q Apply servers

This procedure is a slight modification of the above procedures for use with multiple Q Capture or Q Apply servers.

  1. Follow the steps above for migrating a Q Capture server.
  2. Start the Version 9.7 Fix Pack 2 Q Capture program with the migrate=y and startmode=WARMNS parameters.
  3. If you have more than one Q Capture program, migrate each one. Q Capture programs do not have to be migrated all at once.
  4. Follow the steps above to migrate a Q Apply program.
  5. You can now start the Version 9.7 Fix Pack 2 Q Apply program to work with your Version 9.7 Fix Pack 2 Q Capture program.
  6. Repeat the migration steps for each Q Apply program that you want to migrate.