The create database replication definition syntax has
been modified and several new parameters added.
create database replication definition <db_repdef>
with primary at <server_name>.<db>
[not replicate DDL] |
[replicate DDL [{{with | without} auto_update_table_list} |
{{with | without} auto_extend_table_list}]]
[[not] replicate functions setcont]
[[not] replicate transactions setcont]
[[not] replicate system procedures setcont]
[[not] replicate tables [[setcont [except setcont]] | in files (‘<file_path>’)]
[[not] replicate {SQLDML | DML_options} [in <table_list>]]
[user <username> password <pass>]]
setcont ::= [[in] ([<owner1>.]<name1>[, [<owner2>.]<name2> [, ... ]])]
Replication
Server 15.7.1 SP200 adds these parameters:
- [replicate DDL [{{with | without} auto_update_table_list} | {{with |
without} 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. For a table, if
pdb_automark_tables is true, 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.
- [[not] replicate tables [[<setcont>
[except <setcont>]] –
Specifies whether to send tables to the replicate database. When replicating
tables you can have a maximum of one clause for tables. If you omit the clause,
Replication Sever replicates all tables.
An exception list can only be used
with table list. The exception list adds additional adjustment for the table
list defined in the database replication definition and takes precedence
over the table list.
- [[not] replicate tables in files
(‘<file_path>’)] – Specifies
whether to send tables specified in the <file_path> to the
replicate database.
where,
<file_path> 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.
An exception list can only be used with table list. The
exception list adds additional adjustment for the table list defined in the
database replication definition and takes precedence over the table list.
- [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
the <username> and <password>.
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.
Example
Example 1
Creates a database replication definition
<dbrepdef>. DDL is
replicated and if a table is marked for replication at the primary, the table is
automatically added to
<table_list> when create table DDL commands
are encountered. The command also replicates data for the table
<USER1.TABLE1> and all data for
<TABLE2>,
except the
<TABLE2> owned by
<USER2>.
create database replication definition <dbrepdef>
with primary at <ds1>.<pdb1>
replicate DDL
with auto_extend_table_list
replicate tables in (<USER1>.<TABLE1>, *.<TABLE2>) except in (<USER2>.<TABLE2>)
Example
Example 2
Creates a database replication definition <db_repdef>. DDL is
replicated, but does update the <table_list> when new DDL commands
are encountered. The command also replicates data for the table specified in the
file /sap/user/tablelist.txt, which include tables
<USER1>.<TABLE1>, and all data for
<TABLE2>.
For example,
tablelist.txt
contains:
================
#user tables:
USER1.TABLE1
*.TABLE2
================
create database replication definition <db_repdef>
with primary at <ds1>.<pdb1>
replicate DDL
replicate tables in files (‘/sap/user/table_list.txt’)
See create database replication definition in the Reference
Manual.