alter database replication definition

Description

Changes an existing database replication definition.

Syntax

alter database replication definition db_repdef
         with primary at srv.db
         {[not] replicate DDL | [not] replicate setname setcont |
         [not] replicate [in {SQLDML | DML_options} table_list]}
          [with dsi_suspended]

setname ::= {tables | functions | transactions | system procedures}

setcont ::= [in ([owner1.] name1 [, [owner2.] name2 [, ...]])

The term functions in setname refers to user-defined stored procedures or user-defined functions.

Parameters

db_repdef: Name of the database replication definition.

server_name.db: Name of the primary server/database combination. For example: TOKYO.dbase.

[not] replicate DDL: Tells Replication Server whether or not to send DDL to subscribing databases. If “replicate DDL” is not included, or the clause includes “not,” DDL is not sent to the replicate database.

[not] replicate setname setcont: Specifies whether or not to send objects stated in the setname category to the replicate database. The setname category can have a maximum of one clause each for tables, functions, transactions, and system procedures.

If you omit the system procedures setname or include the not option, Replication Server does not replicate the system procedures.

If you omit tables, functions, or transactions setname or include the not option, Replication Server replicates all objects of the setname category.

[not] replicate [in {SQLDML | DML_options} table_list]}: Informs Replication Server whether or not to replicate SQL statements to tables defined in table_list.

SQLDML: Specifies these Data Manipulation Language (DML) operations:

U – update

D – delete

I – insert select

S – select into

DML_options: Any combination of these DML operations:

U – update

D – delete

I – insert select

S – select into

When the database replication mode is set to any combination of UDIS the RepAgent sends both individual log records and the information needed by Replication Server to build the SQL statement.

owner: An owner of a table or a user who executes a transaction. Replication Server does not process owner information for functions or system procedures.

You can replace owner with a space surrounded by single quotes or with an asterisk.

A space (‘ ‘) – indicates no owner.

An asterisk (*) – indicates all owners. Thus, for example, *.publisher means all tables named publisher, regardless of owner.

name: The name of a table, function, transaction, or system procedure.

You can replace name with a space surrounded by single quotes or with an asterisk.

A space (‘ ‘) – indicates no name. For example, maintuser.’ ‘ means all unnamed maintenance user transactions.

An asterisk (*) – indicates all names. Thus, for example, robert.* means all tables (or transactions) owned by robert.

with dsi_suspended: Tells the replicate Replication Server to suspend the replicate DSI. Can be used to signal need to resynchronize databases.

Examples

Example 1

Changes the database replication definition rep_1C to filter out table2. The replicate DSI will be suspended:

alter database replication definition rep_1C
    with primary at PDS.pdb
    not replicate tables in (table2)
    with dsi_suspended

Example 2

Applies update and delete statements for tables tb1 and tb2:

alter database replication definition dbrepdef
      with primary at ds1.pdb1
      replicate 'UD' in (tb1,tb2)
go

Usage

When alter database replication definition is executed, Replication Server writes an rs_marker to the inbound queue. alter database replication definition does not take affect until the marker reaches the DIST, which gives the DIST time to incorporate the changes in the Database Subscription Resolution Engine (DSRE).
Altering a database replication definition may desynchronize the primary and replication databases. See the Replication Server Administration Guide Volume 1 for instructions for resynchronizing databases.

SQL statement replication

If you do not specify a filter in your replication definition, the default is the not replicate clause. Apply alter database replication definition to change the SQLDML filters. You can either specify one or multiple SQLDML filters in a replicate clause.

For more information about SQL statement replication see create database replication definition.