Starting schema-level subscriptions

You start a schema-level subscription to instruct the Q Capture program to begin capturing SQL operations such as CREATE TABLE and DROP TABLE for tables that are part of the subscription. You can also optionally start all of the included table-level Q subscriptions.

Before you begin

About this task

About this task

Newly created schema-level subscriptions are in N (new) state and are automatically started when the Q Capture program is started or reinitialized. You might need to use this procedure if you stopped the subscription or it was stopped because of an error.

Starting a schema-level subscription entails inserting a START SCHEMASUB signal into the IBMQREP_SIGNAL table at one or more Q Capture servers. When Q Capture processes the signal, the state of the corresponding schema-level subscription changes to A (active) in the IBMQREP_SCHEMASUBS table.

When you use the ASNCLP program to start schema-level subscriptions, it performs the signal inserts. If you use SQL to perform the inserts yourself, the START SCHEMASUB signal must be inserted into the IBMQREP_SIGNAL table at all Q Capture servers in bidirectional or peer-to-peer configurations.

Procedure

Procedure

To start a schema-level subscription, use one of the following methods:
Method Description
ASNCLP command-line program Use the START SCHEMASUB command. You have two options:
Start only the schema-level subscription
This option instructs the Q Capture program to begin replicating CREATE TABLE and DROP TABLE operations for all tables that are part of the schema-level subscription. The following example accomplishes this task for schemasub1 between the bidirectional replication servers SAMPLE1 and SAMPLE2:
ASNCLP SESSION SET TO Q REPLICATION;

SET BIDI NODE 1 SERVER DBALIAS SAMPLE1;
SET BIDI NODE 2 SERVER DBALIAS SAMPLE2;

SET RUN SCRIPT NOW STOP ON SQL ERROR ON;

START SCHEMASUB schemasub1 NEW ONLY;
Start the schema-level subscription and all of the table-level Q subscriptions that belong to it
This option instructs the Q Capture program to begin replicating supported DDL operations for all tables that are part of the schema-level subscription, and to start replicating row changes for all of the table-level Q subscriptions that are part of the schema-level subscription.

In the following example, the other commands that are needed in the script are the same as the previous example and are not shown:

START SCHEMASUB schemasub1 ALL;
SQL Insert a START SCHEMASUB signal into the IBMQREP_SIGNAL table at one or more Q Capture servers:
insert into schema.IBMQREP_SIGNAL(
    SIGNAL_TIME,
    SIGNAL_TYPE,
    SIGNAL_SUBTYPE,
    SIGNAL_INPUT_IN,
    SIGNAL_STATE
) values (
     CURRENT TIMESTAMP,
    'CMD',
    'START SCHEMASUB',
    'schema_subname',
    'P' );
Where schema identifies a Q Capture program, and schema_subname is the name of the schema-level subscription that you want to start.

For bidirectional or peer-to-peer replication, you must insert the signal into all Q Capture servers in the configuration.