Stopping schema-level subscriptions

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

About this task

About this task

Stopping a schema-level subscription entails inserting a STOP SCHEMASUB signal into the IBMQREP_SIGNAL table at one or more Q Capture servers. When you use the ASNCLP program to stop schema-level subscriptions, it performs these inserts.

When Q Capture processes the signal, the state of the corresponding schema-level subscription changes to I (inactive) in the IBMQREP_SCHEMASUBS table. The state of table-level Q subscriptions remains unchanged unless you explicitly stop these subscriptions, which can be done in a single command by the ASNCLP program.

Procedure

Procedure

To stop a schema-level subscription, use one of the following methods:
Method Description
ASNCLP command-line program Use the STOP SCHEMASUB command. You have two options:
Stop only the schema-level subscription
This option instructs the Q Capture program to stop 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;

STOP SCHEMASUB schemasub1 NEW ONLY;
Stop the schema-level subscription and all of the table-level Q subscriptions that belong to it
This option instructs the Q Capture program to stop replicating supported DDL operations for all tables that are part of the schema-level subscription, and to stop 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:

STOP SCHEMASUB schemasub1 ALL;
SQL Insert a STOP 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',
    'STOP_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 stop.

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