Show TOC

alter database replication definitionLocate this document in the navigation structure

Changes an existing database replication definition. You can also add tables to the replication path and remove tables from the replication path. You can specify tables in line or in a file list.

Syntax
alter database replication definition <db_repdef>
with primary at <data_server>.<database>
{[{not replicate DDL} |
{replicate DDL [{with | without} {auto_update_table_list | auto_extend_table_list}]}] |
[not] replicate <setname> <setcont> |
[not] replicate {{SQLDML | DML_options} [in <table_list>]} |
[alter owner from <current_table_owner> to <new_table_owner> [for <table_name>]] |
[{add | remove} tables {setcont}]}
[with dsi_suspended]
[user <username> password <pass>]
<setcont> ::= [[in] ([<owner1>.]<name1>[, [<owner2>.]<name2> [, ... ]])] | [in files ([<file1> [, <file2> [, ..]]])]
<setname> ::= {tables | functions | transactions | system procedures}
Note

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

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
not replicate DDL

Informs Replication Server not to send DDL to subscribing databases.

replicate DDL [{with | without} {auto_update_table_list | auto_extend_table_list}]

Informs Replication Server to send DDL to subscribing databases and whether to update or extend the table list. If you specify replicate DDL without any option, DDL is sent to the replicate database, however, the table is not added to the replication path.

replicate DDL with auto_update_table_list

Sends DDL commands to the replicate database. If pdb_automark_tables is true for a table, the table is automatically updated in the table list when drop table or rename table DDL commands are encountered.

replicate DDL without auto_update_table_list

Sends DDL commands to the replicate database, but does not update the tables in the table list when drop table or rename table DDL commands are encountered.

replicate DDL with auto_extend_table_list

Sends DDL commands to the replicate database. If pdb_automark_tables is true for a table, the table is automatically added to the table list when create table DDL commands are encountered.

replicate DDL without auto_extend_table_list
Sends DDL commands to the replicate database, but does not add the tables in the table list when create table DDL commands are encountered.
Note If you use the replicate DDL command with the auto_extend_table_list option, make sure that you are not simultaneously replicating system procedures or SQLDML operations.
[not] replicate <setname> <setcont>

Specifies whether 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> and do not 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 to replicate SQL statements to tables defined in <table_list>.

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.

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.

add tables in {(<table_list>) | files '<file_path>'}
Adds tables to the replication path. You can either specify a list of tables or provide the table names in a file. After you add a table to the replication path, the data for that table starts replicating.
Note You can specify only one file at a time and you must specify an absolute path for <file_path>.
remove tables in {(<table_list>) | files '<file_path>'}

Removes tables from the replication path.You can either specify a list of tables or provide the table names in a file.

with dsi_suspended

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

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

[in files ('<file_path>')]
A file that contains an include or exclude table list. You can specify only one file at a time and you must specify an absolute path for <file_path>.
Note The user who starts the primary Replication Server must have read permission to read the file.
The table name format in a file and in a table list is the same. You can specify the table names in a table list in these formats:
  • <ownername>.<tablename>
  • <tablename> (the table name is stored as dbo.tablename)
  • *.<tablename>
  • <ownername>.*
  • '*x*y'.'a*b' (wildcard embedded in the string)
Note When create database replication definition is issued, the partial wildcard is expanded through all lists, including the table list and the exception list. The simple wildcard is expanded in the exception list before they are stored in the system table. You must specify values for user and password if your require any wildcard expansion.
When you use an include or exclude table list, observe these guidelines:
  • The table name delimiter in the file must be a new line character.
  • Lines starting with # are ignored as comments.
  • Blanks before an owner name or table name are truncated.
  • The max length of an owner name is 30 characters.
[user <username> password <pass>]

The user ID and password used to connect to the primary Adaptive Server database or Replication Agent and select from the primary table.

If you use wildcard in a table name, you must specify values for user and password.
Note The user and password values are used once and are not stored in the RSSD.
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, at the primary Replication Server execute:
alter database replication definition authors_dbrepdef
with primary at NY_DS.pdb1
alter owner from mario to angela for author_name
Usage

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

  • When adding tables:

    • If a table is already defined as replicating in the database replication definition, nothing changes. Otherwise, this table is added to the replication path.

    • If a replicate site subscribes to a database replication definition, and the database subscription is created with the with materialization option, the table materializes automatically.

    • If multiple sites subscribe to a database replication definition, the table is not updated in the rs_dbsubsets table until all sites finish materialization.

  • When removing tables, if a table is defined as not replicating in the database replication definition, nothing changes. Otherwise, the table is removed from the replication path.

  • If multiple replicate sites subscribe to a database replication definition, changing the definition affects all of them. To avoid a global change, use a different database replication definition.

Related Information