Setting up the InfoSphere CDC instance for shadow tables

To set up the InfoSphere® CDC instance for shadow tables, create one InfoSphere CDC instance in the replication engine. Then, configure system parameters to specify the DB2® database that you want to replicate data to the shadow tables and control the behavior in accessing the database.

To create a single InfoSphere CDC instance:

1. Log in to the access-server-host as cdc-user.
2. Start the InfoSphere CDC configuration tool in text mode. The following example shows how to run this tool in the Linux operating system from the console interface:

   unset DISPLAY 
   cd cdc-installation-dir/bin
   ./dmconfigurets

3. If the welcome page is displayed, press Enter to continue. Otherwise, enter 2 and press Enter to add an InfoSphere CDC instance.
4. Enter cdc-instance-name as the name of the instance that you want to add and press Enter. The cdc-instance-name must be unique.
5. Enter the cdc-port port number. InfoSphere CDC displays a default port number of 10901. Press Enter.

   This port number cannot be used by other applications that are installed on the same server. Use this port number when you specify access parameters for your datastore in the InfoSphere CDC Management Console.

6. Press Enter to bypass auto-discovery. This feature is disabled by default.
7. Enter cdc-staging-store-size as the maximum amount of disk space for the InfoSphere CDC staging store on your DB2 server. The default value is 100 GB.
8. Enter the amount of physically available RAM that you want to allocate for this InfoSphere CDC instance and press Enter. By default, the configuration tool allocates 512 MB of RAM for each 32-bit instance and 1024 MB of RAM for each 64-bit instance. A value of 3600 MB works well in most environments.
9. Enter 64 and press Enter.
10. If you want to use TCP/IP as the exclusive method of communication between datastores, press Enter to select the default value n.
11. Enter db2-instance-name as the name of the DB2 instance that contains the database where you want to create the shadow tables.
12. Enter db2-database-name as the name of the DB2 database where you want to create the shadow tables. Press Enter.
13. Enter n to bypass advanced parameters configuration and press Enter.
14. Enter db2-cdc-user as the DB2 user name to access db2-database-name and press Enter.
15. Enter the password for db2-cdc-user and press Enter. The configuration tool searches the database for schemas.
16. Enter the number that corresponds to db2-metadata-schema-name that you chose as the database schema name that InfoSphere CDC uses to store metadata and press Enter.
17. Enter y to specify a refresh loader path and press Enter to provide details.
    1. Enter cdc-refresh-loader-path as the directory to be used for bulk inserts into the database and press Enter. Both cdc-user and db2-instance-owner must have read and write permissions for this directory.
18. The configuration tool creates cdc-instance-name and prompts you to start it. Enter n to decline starting the instance.
19. Enter 6 and press Enter to exit the configuration tool.
20. Configure cdc-instance-name to store the latency information in the SYSTOOLS.REPL_MQT_LATENCY table by setting the maintain_replication_mqt_latency_table system parameter to true.

    dmset -I instance-name
      maintain_replication_mqt_latency_table=true

    The DB2 optimizer uses the latency information in the SYSTOOLS.REPL_MQT_LATENCY table to decide whether to do query routing.
21. Set the mirror_auto_restart_interval_minutes system parameter for InfoSphere CDC instance to indicate the number of minutes between restart attempts for the subscriptions that are marked as persistent. You must set this system parameter to a nonzero value so that persistent subscriptions are restarted. he following example shows how to set this system parameter to 2 minutes, which is a suitable value for most environments:

    dmset -I instance-name mirror_auto_restart_interval_minutes=2

22. Optional: If you want to move the InfoSphere CDC metadata tables to a specific table space of your choice, use the ADMIN_MOVE_TABLE procedure.
    1. Determine which table space holds the metadata tables. The following example shows how to issue a query to display the table space where the metadata tables were created.

       db2 "SELECT TBSPACE
               FROM SYSCAT.TABLESPACES
               WHERE TBSPACEID IN (
                 SELECT TBSPACEID
                   FROM SYSCAT.TABLES
                   WHERE TABNAME LIKE 'TS_%'
                    AND TABSCHEMA = db2-metadata-schema-name)"

    2. Determine the metadata tables to be moved:

       db2 list tables for schema db2-metadata-schema-name | grep TS_

       For a list of all metadata tables, see InfoSphere CDC for DB2 for LUW metadata tables.
    3. Move the metadata tables with the ADMIN_MOVE_TABLE procedure to a specific table space of your choice. The following example shows how to move the TS_AUTH metadata table to the REPL table space:

       CALL ADMIN_MOVE_TABLE('db2-metadata-schema-name', 'TS_AUTH',
          'REPL','REPL','REPL','','','','','','MOVE');

       Repeat the same call statement for all the metadata tables that you found in step 22.b.
23. Start the 64-bit cdc-instance-name.

    nohup ./dmts64 -I cdc-instance-name &

24. Verify that cdc-instance-name has the appropriate settings for the system parameters.

    dmset -I cdc-instance-name