Data Replication with SQL Remote
PART 4. Reference
CHAPTER 14. Utilities and Options Reference
Replication options are database options included to provide control over replication behavior.
SET [ TEMPORARY ] OPTION
... [ userid. | PUBLIC. ]option_name = [ option_value ]
exec sp_remote_option option-name, option-value
Argument |
Description |
|---|---|
option_name |
The name of the option being changed. |
option-value |
A string containing the setting for the option. |
The following options are available.
OPTION |
VALUES |
DEFAULT |
|---|---|---|
Blob_threshold |
Integer, in K |
256 |
Compression |
-1 to 9 |
6 |
Delete_old_logs |
ON, OFF |
OFF |
Qualify_owners |
ON, OFF |
OFF |
Quote_all_identifiers |
ON, OFF |
OFF |
Replication_error |
procedure-name |
NULL |
Subscribe_by_remote |
ON,OFF |
ON |
Verify_threshold |
integer |
256 |
Verify_all_columns |
ON,OFF |
OFF |
These options are used by the Message Agent, and should be set for the user ID specified on the Message Agent command line. They can also be set for general public use.
The options are as follows:
Blob_threshold option Any value longer than the Blob_threshold option is replicated as a blob. That is, it is broken into pieces and replicated in chunks, before being reconstituted by using a SQL variable and concatenating the pieces at the recipient site.
If you are replicating blobs in an installation with Adaptive Server Enterprise, you must ensure that Blob_threshold is set to a value larger the largest blob being replicated.
For information on blob replication and Adaptive Server Enterprise, see Replication of blobs.
Compression option Set the level of compression for messages. Values can be from -1 to 9, and have the following meanings:
-1 Send messages in Version 5 format. Message Agents (both dbremote and ssremote) from previous versions of SQL Remote cannot read messages sent in Version 6 format. You should ensure that COMPRESSION is set to -1 until all Message Agents in your system are upgraded to Version 6.
0 No compression.
1 to 9 Increasing degrees of compression. Creating messages with high compression can take longer than creating messages with low compression.
Delete_old_logs option his option is used by SQL Remote and by the Adaptive Server Anywhere Replication Agent. The default setting is OFF. When set to ON, the Message Agent (DBREMOTE) deletes each old transaction log when all the changes it contains have been sent and confirmed as received.
Qualify_owners option Controls whether SQL statements being replicated by SQL Remote should use qualified object names. The default in Adaptive Server Anywhere is ON and the default in Adaptive Server Enterprise is OFF.
Qualifying owners in Adaptive Server Enterprise setups is rarely needed because it is common for objects to be owned by dbo. When qualification is not needed in Adaptive Server Anywhere setups, messages will be slightly smaller with the option off.
Quote_all_identifiers option Controls whether SQL statements being replicated by SQL Remote should use quoted identifiers. The default is OFF.
When this option is off, the dbremote quotes identifiers that require quotes by Adaptive Server Anywhere (as it has always done) and ssremote does not quote any identifiers. When the option is on, all identifiers are quoted.
Replication_error option Specifies a stored procedure called by the Message Agent when a SQL error occurs. By default no procedure is called.
The replication error procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure is called once with the SQL error message and once with the SQL statement that causes the eror.
While the option allows you to track and monitor SQL errors in replication, you must still design them out of your setup: this option is not intended to resolve such errors.
Subscribe_by_remote option When set to ON, operations from remote databases on rows with a subscribe by value that is NULL or an empty string assume the remote user is subscribed to the row. When set to OFF, the remote user is assumed not to be subscribed to the row.
The only limitation of this option is that it will lead to errors if a remote user really does want to INSERT (or UPDATE) a row with a NULL or empty subscription expression (for information held only at the consolidated database). This is reasonably obscure and can be worked around by assigning a subscription value in your installation that belongs to no remote user.
For more information about this option, see Using the Subscribe_by_remote option with many-to-many relationships, and Using the Subscribe_by_remote option with many-to-many relationships.
Verify_threshold option If the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE is replicated. The default setting is 1000.
This option keeps the size of SQL Remote messages down, but has the disadvantage that conflicting updates of long values are not detected.
Verify_all_columns option The default setting is OFF. When set to ON, messages containing updates published by the local database are sent with all column values included, and a conflict in any column triggers a RESOLVE UPDATE trigger at the subscriber database.
The extraction utility for Adaptive Server Enterprise sets the public option in remote Adaptive Server Anywhere databases to match the setting in the Adaptive Server Enterprise database.
The following statement sets the Verify_all_columns option to OFF in Adaptive Server Anywhere, for all users:
SET OPTION PUBLIC.Verify_all_columns = 'OFF'
The following statements set the Verify_all_columns option to OFF in Adaptive Server Enterprise:
exec sp_remote_option Verify_all_columns, 'OFF' go
In Adaptive Server Enterprise, replication options are used only by SQL Remote.