Data Replication with SQL Remote
PART 4. Reference
CHAPTER 14. Utilities and Options Reference
To send and apply SQL Remote messages, and to maintain the message tracking system to ensure message delivery.
{ dbremote | ssremote } [ switches ] [ directory ]
Switch |
Description |
|---|---|
@filename |
Read in switches from configuration file |
@envvar |
Read in switches from environment variable |
-a |
Do not apply received transactions |
-b |
Run in batch mode |
-c "keyword=value; ..." |
Supply database connection parameters |
-cq "keyword=value; ..." |
Supply database connection parameters for the stable queue (Adaptive Server Enterprise only) |
-dl |
Display log messages on screen |
-e locale-string |
Locale setting (Adaptive Server Enterprise only) |
-fq |
Full scan of the stable queue when sending messages (Adaptive Server Enterprise only) |
-g n |
Group transactions consisting of less than n operations. |
-i |
Scan transactions from the transaction log into the stable queue (Adaptive Server Enterprise only). |
-k |
Close window on completion |
-l length |
Maximum message length |
-m size |
Maximum amount of memory used for building messages. |
-o file |
Output messages to file |
-ot file |
Truncate file and log output messages |
-p |
Do not purge messages |
-q |
Run with minimized window |
-r |
Receive messages |
-rd minutes |
Polling frequency for incoming messages |
-rp number |
Number of receive polls before message is assumed lost |
-ru time |
Waiting period to re-scan log on receipt of a resend. |
-s |
Send messages |
-sd time |
Send polling period |
-t |
Replicate all triggers (Adaptive Server Anywhere only) |
-u |
Process only backed up transactions |
-v |
Verbose operation |
-w n |
Number of worker threads to apply incoming messages (Windows NT and Solaris only) |
-x |
Rename and restart the transaction log (Adaptive Server Anywhere only). |
directory |
The directory in which old transaction logs are held (Adaptive Server Anywhere only) |
The Message Agent sends and applies messages for SQL Remote replication, and maintains the message tracking system to ensure message delivery.
The name of the Message Agent executable is as follows:
dbremote The Message Agent for Adaptive Server Anywhere.
ssremote The Message Agent for Adaptive Server Enterprise.
For Adaptive Server Anywhere, the user ID in the Message Agent command line must have either REMOTE DBA or DBA authority. For Adaptive Server Enterprise, the user ID must have replication role.
The optional directory parameter specifies a directory in which old transaction logs are held, so that the Message Agent has access to events from before the current log was started.
The Message Agent uses a number of connections to the database. For a listing, see Connections used by the Message Agent.
For information on REMOTE DBA authority, see The Message Agent and replication security.
@filename Read in command-line switches from the supplied file.
The file may contain line breaks, and may contain any set of command line switches. For example, the following command file holds a set of command line switches for a Message Agent that starts with a cache size of 4 Mb, sends messages only, and connects to a database named field on a server named myserver:
-m 4096 -s -c "eng=myserver;dbn=field;uid=sa;pwd=sysadmin"
If this configuration file is saved as c:\config.txt, it can be used in an command line as follows:
ssremote @c:\config.txt
or
dbremote @c:\config.txt
@environment-variable Read in command-line switches from the supplied environment variable.
The environment variable may contain any set of command line switches. For example, the first of the following pair of statements sets an environment variable holding a set of command line switches for a database server that starts with a cache size of 4 Mb, receives messages only, and connects to a database named field on a server named myserver. The set statement should be entered all on one line:
set envvar=-m 4096 -s -c "eng=myserver;dbn=field;uid=sa;pwd=sysadmin" ssremote @envvar
-a Process the received messages (those in the inbox) without applying them to the database. Used together with -v (for verbose output) and -p (so the messages are not purged), this flag can help detect problems with incoming messages. Used without -p, this flag purges the inbox without applying the messages, which may be useful if a subscription is being restarted.
-b Run in batch mode. In this mode, the Message Agent processes incoming messages, scans the transaction log once and processes outgoing messages, and then stops.
-c "parameter=value; ..." Specify connection parameters. For Adaptive Server Anywhere, if this option is not specified, the environment variable SQLCONNECT is used.
For example, the following statement runs dbremote on a database file named c:\asa6\asademo.db, connecting with user ID DBA and password SQL:
dbremote -c "uid=dba;pwd=sql;dbf=c:\asa6\asademo.db"
The Message Agent must be run by a user with REMOTE DBA authority or DBA authority.
For information on REMOTE DBA authority, see The Message Agent and replication security.
The Message Agent for Adaptive Server Anywhere supports the full range of Adaptive Server Anywhere connection parameters. The Message Agent for Adaptive Server Enterprise supports the following connection parameters:
Parameter |
Description |
|---|---|
UID |
Login ID |
PWD |
Password |
DBN |
(optional) Database name. If this parameter is not supplied, the connection defaults to the default database for the login ID. |
ENG |
Adaptive Server Enterprise name. |
-cq "parameter=value; ..." Specify connection parameters for the stable queue. This option applies to Adaptive Server Enterprise only. If not supplied, the values default to the -c values.
-dl Display messages in the Message Agent window or on the command line and also in the log file.
-e locale-string This option applies to Adaptive Server Enterprise only. Specify Adaptive Server Enterprise locale information. The locale string has the following format:
"language_name,charset_name[,sort_order]"
By default, the Message Agent uses the default locale, which is defined in the file sybase\locales\locales.dat.
If language_name and charset_name are not supplied, the Message Agent obtains them from Adaptive Server Enterprise. If sort_order is not supplied, the Message Agent uses a binary sort order (sort by byte value).
-fq This option is for use only with Adaptive Server Enterprise. It permits a a full scan of the stable queue when sending messages, starting from the oldest confirm_sent value.
This feature is intended for occasional use to clean out a large stable queue. If, for example, a single user has not confirmed receipt of a message from a long time ago, the stable queue may be very large. However, by running -fq you can delete entries from more up-to-date users that have been confirmed, even though they are more recent than the cutoff value at which entries are deleted by default.
-g n Instructs the Message Agent to group transactions containing less than n operations together with transactions that follow. The default is twenty operations. Increasing the value of n can speed up processing of incoming messages, by doing less commits. However, it can also cause deadlock and blocking by increasing the size of transactions.
-i Scan transactions from the transaction log into the stable queue. This option is available for Adaptive Server Enterprise only. It is used when you wish to run a separate copy of the Message Agent for scanning the transaction log and for sending and receiving messages.
If none of -r, -i, or -s is specified, the Message Agent executes all three phases. Otherwise, only the indicated phases are executed.
For more information, see Running multiple Message Agents.
-k Close window on completion.
-l length Specifies the maximum length of message to be sent, in bytes. Longer transactions are split into more than one message. The default is 51200 (50K).
Caution
The maximum message length must be the same at all sites in an installation.
For platforms with restricted memory allocation, the value must be less than the maximum memory allocation of the operating system. For example, Windows 3.x allows a maximum memory allocation of 64K, and the maximum message length must be less than this..
-m size Specifies a maximum amount of memory to be used by the Message Agent for building messages and caching incoming messages. The allowed size can be specified as n (in bytes), nK, or nM. The default is 2048K (2M).
When all remote databases are receiving unique subsets of the operations being replicated, a separate message for each remote database is built up concurrently. Only one message is built for a group of remote users that are receiving the same operations. When the memory being used exceeds the -m value, messages are sent before reaching their maximum size (as specified by the -l switch).
When messages arrive, they are stored in memory by the Message Agent until they are applied. This caching of messages prevents rereading of that are out of order messages from the message system , which may lower performance on large installations. When the memory usage specified using the -m switch is exceeded, messages are flushed in a least-recently used fashion.
-o Append output to a log file. Default is to send output to the screen.
-ot Truncate the log file and then append output messages to it. Default is to send output to the screen.
-p Process the messages without purging them.
-q For Windowing operating systems only, starts the Message Agent with a minimized window.
-r Receive messages. If none of -r, -i, or -s is specified, the Message Agent executes all three phases. Otherwise, only the indicated phases are executed.
The Message Agent runs in continuous mode if called with -r. To have the Message Agent shut down after receiving messages, use the -bswitch in addition to -r.
-rd time By default, the Message Agent polls for incoming messages every minute. This option (rd stands for receive delay) allows the polling frequency to be configured, which is useful when polling is expensive. The Message Agent checks for incoming messages once after each send cycle, regardless of the polling frequency, so if a value greater then the SEND EVERY frequency is supplied, the Message Agent checks for incoming messages once after each send cycle.
You can use a suffix of s after the number to indicate seconds, which may be useful if you want frequent polling. For example:
dbremote -f 30s
polls every thirty seconds.
For more information on polling, see Polling for messages and requesting resends.
-rp When running in continuous mode, the Message Agent polls at certain intervals for messages. After polling a set number of times (by default, one), if a message is missing, the Message Agent assumes it has got lost and requests that it be resent. On slow message systems, this can result in many unnecessary resend requests. You can set the number of polls before a resend request is issued using this option, to cut down on the number of resend requests.
For more information on configuring this option, see Polling for messages and requesting resends.
-ru Control the resend urgency. This is the time between detection of a resend request and when the Message Agent starts fulfilling the request. Use this switch to help the Message Agent collect resend requests from multiple users before rescanning the log. The time unit can be any of {s = seconds; m = minutes; h = hours; d = days}
-s Send messages. If none of -r, -i, or -s is specified, the Message Agent executes all three phases. Otherwise, only the indicated phases are executed.
-sd time Control the send delay, which is the time to wait between polls for more transaction log data to send.
-t All trigger actions are replicated. If you do use this switch, you must ensure that the trigger actions are not carried out twice at remote databases, once by the trigger being fired at the remote site, and once by the explicit application of the replicated actions from the consolidated database.
To ensure that trigger actions are not carried out twice, you can wrap an IF CURRENT REMOTE USER IS NULL ... END IF statement around the body of the triggers.
-u Process only transactions that have been backed up. This switch prevents the Message Agent from processing transactions since the latest backup. Using this switch, outgoing transactions and confirmation of incoming transactions are not sent until they have been backed up.
In Adaptive Server Anywhere, this means only transactions from renamed logs are processed. In Adaptive Server Enterprise, this means that only transactions committed before the latest dump database or dump transaction statement are processed.
-v Verbose output. This switch displays the SQL statements contained in the messages to the screen and, if the -o switch is used, to a log file.
-w n Windows NT and Solaris only. The number of worker threads used to apply incoming messages. The default is zero, which means all messages are applied by the main (and only) thread. A value of 1 (one) would have one thread receiving messages from the message system and one thread applying messages to the database.
The -w switch makes it possible to increase the throughput of incoming messages with hardware upgrades. Putting the consolidated database on a device that can perform many concurrent operations (a RAID array with a striped logical drive) will improve throughput of incoming messages. Multiple processors in the computer running the Message Agent could also improve throughput of incoming messages.
The -w switch will not improve performance significantly on hardware that cannot perform many concurrent operations.
Incoming messages from a single remote database will never be applied on multiple threads. Messages from a single remote database are always applied serially in the correct order.
-x Rename and restart the transaction log after it has been scanned for outgoing messages. In some circumstances, replicating data to a consolidated database can take the place of backing up remote databases, or renaming the transaction log when the engine is shut down.
SQL Remote uses several registry settings (in Windows 95 and NT) or initialization file settings (Windows 3.x and OS/2) to control aspects of message link behavior.
The message link control parameters are stored in the following places:
Windows 95 and Windows NT In the registry, at the following location:
\\HKEY_CURRENT_USER
\Software
\Sybase
\SQL Remote
Windows 3.x and OS/2 In the file SQLANY.INI, in your SQL Remote installation directory.
NetWare You should create a file named dbremote.ini in the sys:\system directory to hold the FILE system directory setting.
For a listing of registry settings, see the section for each message system under Using message types.