alter database replication definition

Syntax

alter database replication definition db_repdef
         with primary at data_server.database         [alter owner from current_table_owner to new _table_owner [for table_name]] |
         [not] replicate DDL | [not] replicate setname setcont |
         [not] replicate [{SQLDML | DML_options} [in table_list]}
         [with dsi_suspended]

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

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

Parameters

• db_repdef – Name of the database replication definition.
• with primary at data_server.database – specifies the data server and database containing the primary data
• alter owner from current_table_owner to new _table_owner [for table_name] – specifies the current and new table owners when you want to change the owner of a table

  Include the for tablename option to specify for which you want to transfer ownership. Omit for tablename if you want to change the owners for all tables in the setname category in the database replication definition.

• [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.

  The filter category specified by setname replaces the current filter category or adds the filter category to the database replication filters, if it is a new category.

• [not] replicate {SQLDML | DML_options} [in 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

• Example 3 – After changing the table owner from mario to angela with the Adaptive Server alter... modify owner command, immediately execute the rs_send_repserver_cmd stored procedure at the primary database to change the owner of the author_name table in the authors_dbrepdef databe replication definition:

  exec rs_send_repserver_cmd 'alter database replication definition authors_dbrepdef
  with primary at NY_DS.pdb1 
  alter  owner from mario to angela for author_name'

Usage