Using passthrough mode

The publisher of the consolidated database can directly intervene at remote sites using a passthrough mode, which enables standard SQL statements to be passed through to a remote site. By default, passthrough mode statements are executed at the local (consolidated) database as well, but an optional keyword prevents the statements from being executed locally.

Caution

Always test your passthrough operations on a test database with a remote database subscribed. Never run untested passthrough scripts against a production database.

Starting and stopping passthrough

Passthrough mode is started and stopped using the PASSTHROUGH statement. Any statement entered between the starting PASSTHROUGH statement and the PASSTHROUGH STOP statement which terminates passthrough mode is checked for syntax errors, executed at the current database, and also passed to the identified subscriber and executed at the subscriber database. The statements between a starting and stopping passthrough statement are called the passthrough session.

The following statement starts a passthrough session which passes the statements to a list of two named subscribers, without being executed at the local database:

PASSTHROUGH ONLY
FOR userid_1, userid_2;
Directing passthrough statements

The following statement starts a passthrough session that passes the statements to all subscribers to the specified publication:

PASSTHROUGH ONLY
FOR SUBSCRIPTION TO [owner].pubname [ ( string ) ];

Passthrough mode is additive. In the following example, statement_1 is sent to user_1, and statement_2 is sent to both user_1 and user_2.

PASSTHROUGH ONLY FOR user_1 ;
statement_1;
PASSTHROUGH ONLY FOR user_2 ;
statement_2;

The following statement terminates a passthrough session:

PASSTHROUGH STOP;

PASSTHROUGH STOP terminates passthrough mode for all remote users.

Order of application of passthrough statements

Passthrough statements are replicated in sequence with normal replication messages, in the order in which the statements are recorded in the log.

Passthrough is commonly used to send data definition language statements. In this case, replicated DML statements use the before schema before the passthrough and the after schema following the passthrough.

Notes on using passthrough mode
  • You should always test your passthrough operations on a test database with a remote database subscribed. You should never run untested passthrough scripts against a production database.
  • You should always qualify object names with the owner name. PASSTHROUGH statements are not executed at remote databases from the same user ID. Consequently, object names without the owner name qualifier may not be resolved correctly.

Uses and limitations of passthrough mode
Operations not replicated in passthrough mode