Using the LTM

User's Guide
PART 5. The Adaptive Server Family
CHAPTER 32. Replicating Data with Replication Server

Using the LTM

As the Adaptive Server Anywhere LTM relies on information in the Adaptive Server Anywhere transaction log, you must take care not to delete or damage the log without storing backups. A transaction log mirror is recommended.

For Info For more information about transaction log management, see the section Transaction log and backup management.

You cannot substitute a Adaptive Server Anywhere LTM for a Adaptive Server Enterprise LTM since the transaction logs are of a different format.

The Adaptive Server Anywhere LTM supports replication of inserts, updates, and deletes, as well as replication of Transact-SQL-dialect stored procedures.

The Adaptive Server Enterprise LTM sends data changes to the Replication Server before they are committed. The Replication Server holds the changes until a COMMIT statement arrives. The Adaptive Server Anywhere LTM sends only committed changes to Replication Server. For long transactions this may lead to some added delay in replication, as all changes have to be sent to Replication Server before being distributed.

Configuring tables for replication

Adaptive Server Anywhere does not support the sp_setreplicate system procedure. Instead, a table is identified as a primary data source using the ALTER TABLE statement with a single clause:

ALTER TABLE table-name
SET REPLICATE ON

The effects of setting REPLICATE ON for a table

Setting REPLICATE ON causes extra information to be placed into the transaction log. whenever an UPDATE, INSERT, or DELETE action is carried out on the table. This extra information is used by the Adaptive Server Anywhere Replication Agent to submit the full pre-image of the row, where required, to Replication Server for replication.

Even if only some of the data in the table is to be replicated, all changes to the table are submitted to Replication Server. It is Replication Server's responsibility to distinguish the data that is to be replicated from that which is not.

When a row is updated, inserted, or deleted, the pre-image of the row is the contents of the row before the action, and the post-image is the contents of the row after the action. For INSERTS, only the post-image is submitted (the pre-image is empty). For DELETES, the post-image is empty and the pre-image only is submitted. For UPDATES, both the pre-image and the updated values are submitted.

The same data types are supported for replication as in Replication Server 10.0.

Data type

Description ( Open Client/Open Server type )

Exact integer data types

int, smallint, tinyint

Exact decimal data types

Decimal, numeric

Approximate numeric data types

float (8-byte), real

Money data types

money, smallmoney

Character data types

char( n ), varchar( n ), text

Date and time data types

datetime, smalldatetime

Binary data types

binary( n ), varbinary( n ), image

Bit data types

bit

Data type	Description ( Open Client/Open Server type )
Exact integer data types	int, smallint, tinyint
Exact decimal data types	Decimal, numeric
Approximate numeric data types	float (8-byte), real
Money data types	money, smallmoney
Character data types	char( n ), varchar( n ), text
Date and time data types	datetime, smalldatetime
Binary data types	binary( n ), varbinary( n ), image
Bit data types	bit

Notes

Adaptive Server Anywhere supports data of zero length that is not NULL: however, non-null long varchar and long binary data of zero length is replicated to a replicate site as NULL.

If a primary table has columns with unsupported data types, you can replicate the data if you create a replication definition using a compatible supported data type. For example, to replicate a DOUBLE column, you could define the column as FLOAT in the replication definition.

Side effects of setting REPLICATE ON for a table

There can be a replication performance hit for heavily updated tables. You could consider using replicated procedures if you experience performance problems that may be related to replication traffic, as replicated procedures send only the call to the procedure instead of each individual action.

As setting REPLICATE ON causes extra information to be sent to the transaction log, this log grows faster than for a non-replicating database.

Minimal column replication definitions

The Adaptive Server Anywhere LTM supports the Replication Server replicate minimal columns feature. This feature is enabled at Replication Server.

For more information on replicate minimal columns, see your Replication Server documentation.

Preparing procedures and functions for replication

Stored procedures can be used to modify the data in tables; updates, inserts, and deletes are executed from within the procedure.

Replication Server can replicate procedures as long as they satisfy certain conditions. The first statement in a procedure must carry out an update in order for the procedure to be replicated. See your Replication Server documentation for a full description of how Replication Server replicates procedures.

Adaptive Server Anywhere supports two dialects for stored procedures: the Watcom-SQL dialect, based on the draft ISO/ANSI standard, and the Transact-SQL dialect. You can use either dialect in writing stored procedures for replication.

Function APC format

The Adaptive Server Anywhere LTM supports the Replication Server function APC format. To make use of these functions, set the configuration parameter rep_func to on (the default is off).

The LTM interprets all replicated APC's as either table APC's or function APC's. A single Adaptive Server Anywhere database cannot have some function APC's combined with other table APC's.

For Info For more information about replicate functions, see your Replication Server documentation.

SQL Statements for controlling procedure replication

A procedure can be configured to act as a replication source using the ALTER PROCEDURE statement.

The following statement makes the procedure MyProc act as a replication source.

ALTER PROCEDURE MyProc
REPLICATE ON

The following statement prevents the procedure MyProc from acting as a replication source.

ALTER PROCEDURE MyProc
REPLICATE OFF

These statements have the same effect as running sp_setreplicate or sp_setrepproc 'table' on the procedure in Adaptive Server Enterprise. The sp_setrepproc 'function' syntax is not supported.

The effects of setting REPLICATE ON for a procedure

When a procedure is used as a replication data source, extra information is sent to the transaction log any time the procedure is called.

Asynchronous procedures

Asynchronous procedures are procedures that are called at a replicate site database in order to update data at a primary site database. The procedure carries out no action at the replicate site, but the call to the procedure is replicated to the primary site, where a procedure of the same name is executed: this is called an asynchronous procedure call (APC). The changes made by the APC are then replicated from the primary to the replicate database in the usual manner.

For information about APCs, see your Replication Server documentation.

The APC_user and APC support

Support for APC's in Adaptive Server Anywhere is different from that in Adaptive Server Enterprise. In Adaptive Server Enterprise, each APC is executed using the user ID and password of the user who called the procedure at the replicate site. In Adaptive Server Anywhere, the password is not stored in the transaction log, and so is not available at the primary site. To work around this difference, a single user ID with associated password is entered into the LTM configuration file, and this user ID (the APC_user) is used to execute the procedure at the primary site. The APC_user must, therefore, have appropriate permissions at the primary site for each APC that may be called.

Configuring the LTM

LTM behavior is controlled by modifying the LTM configuration file, which is a plain text file that can be created and edited using a text editor.

The LTM configuration file contains information needed by the LTM, such as the Adaptive Server Anywhere server it is transferring a log from, the Replication Server it is transferring the log to. A valid configuration file is required in order to run the LTM.

Creating a configuration file

You must create a configuration file, using a text editor, before you can run the LTM. The -C LTM command-line specifies the name of the configuration file to use, and has a default of dbltm.cfg.

Configuration file format

The format of the LTM configuration file is the same as the Replication Server configuration file format, which is described in your Replication Server Administration Guide. In summary:

The configuration file contains one entry per line.
An entry consists of a configuration parameter, followed by the = character, followed by the value:
```
Entry=value
```
Lines beginning with a # character are comments, and are ignored by the LTM.
The configuration file cannot contain leading blanks.
Entries are case sensitive.

For Info For the full list of available configuration file parameters, see The LTM configuration file.

Example configuration file

The following is a sample Adaptive Server Anywhere LTM configuration file.

# This is a comment line
# Names are case sensitive.
SQL_user=sa
SQL_pw=sysadmin
SQL_server=PRIMESV
SQL_database=primedb
RS_source_ds=PRIMESV
RS_source_db=primedb
RS=MY_REPSERVER
RS_user=sa
RS_pw=sysadmin
LTM_admin_user=DBA
LTM_admin_pw=SQL
LTM_charset=cp850
scan_retry=2
SQL_log_files=e:\logs\backup
APC_user=sa
APC_pw=sysadmin

Replicating transactions in batches

Effects of buffering transactions

The LTM allows buffering of replication commands to Replication Server. Buffering the replication commands leads in general to two main effects:

Longer lag before replication As transactions are not sent to Replication Server by the LTM until the buffer is full, there may be a longer delay between a transaction being committed in Adaptive Server Anywhere and being replicated to Replication Server. This delay will be more noticeable on low volume installations, where transactions may wait in the buffer for some time until the next transaction is committed.
Higher throughput Buffering the transactions and sending them in batches results in fewer messages being sent, and can significantly increase overall throughput, especially on high volume installations.

How batch mode works

By default, the LTM buffers transactions. The buffer is flushed (the transactions sent to Replication Server) when any of the following conditions is met:

Maximum number of commands The batch_ltl_siz parameter sets the maximum number of LTL (log transfer language) commands that are stored in the buffer before it is flushed. The default setting is 200.
Maximum memory used The batch_ltl_mem parameter sets the maximum memory that the buffer can occupy before it is flushed. The default setting is 256 K.
Transaction log processing completed If there are no more entries in the transaction log to be processed (that is, the LTM is up to date with all committed transactions), then the buffer is flushed.

Turning off buffering

You can turn off buffering of transactions by setting the batch_ltl_cmds configuration parameter to off:

batch_ltl_cmds=off

Language and character set issues

Language and character set issues are an important consideration in many replication sites. Each database and server in the system uses a specific collation (character set and sorting order) for storing and ordering strings. Adaptive Server Anywhere character set support is carried out in a different manner to character set support in Adaptive Server Enterprise and other Open Client/Open Server based applications.

This section describes how to configure the Adaptive Server Anywhere LTM such that data in a Adaptive Server Anywhere database can be shared with Replication Server and hence with other databases.

The LTM automatically uses the default Open Client/Open Server language, sort order, and character set. You can override these defaults by adding entries to the LTM configuration file.

Adaptive Server Anywhere collations

Each Adaptive Server Anywhere database is assigned a specific collation when it is created.

To find the collation sequence used by a database:

Connect to the database from Sybase Central, right click on the database icon in the left pane, and select Properties from the pop-up menu. The collation is listed on the Extended Information tab. Or:
Run the dbinfo command-line utility. For example, the following command provides information about the Adaptive Server Anywhere sample database:
```
dbinfo c:\asa60\sademo.db
```

If no collation sequence is specified when the database is created, then the 850LATIN1 collation sequence is used.

For Info For a full description of Adaptive Server Anywhere collations, see Database Collations and International Languages.

Open Client/Open Server collations

Adaptive Server Enterprise, Replication Server, and other Open Client/Open Server applications share a common means of managing character sets.

For information on Open Client/Open Server character set support, see the chapter "Configuring Character Sets, Sort Orders, and Message Language" in the Adaptive Server Enterprise Administration Guide. For more information about character set issues in Replication Server, see the chapter "International Replication Design Considerations" in the Replication Server Design Guide.

This section provides a brief overview of Open Client/Open Server character set support.

Internationalization files

Files that support data processing in a particular language are called internationalization files. Several types of internationalization files are supplied with Adaptive Server Enterprise and other Open Client/Open Server applications.

There is a directory named charsets under your Sybase directory. Charsets has a set of subdirectories: one for each character set available to you. Each character set contains a set of files, as described in the followning table

File

Description

charset.loc

Character set definition files that define the lexical properties of each character such as alphanumeric, punctuation, operand, upper or lower case.

*.srt

Defines the sort order for alpha-numeric and special characters.

*.xlt

Terminal-specific character translation files for use with utilities.

File	Description
charset.loc	Character set definition files that define the lexical properties of each character such as alphanumeric, punctuation, operand, upper or lower case.
*.srt	Defines the sort order for alpha-numeric and special characters.
*.xlt	Terminal-specific character translation files for use with utilities.

Character set settings in the LTM configuration file

There are three settings in the LTM configuration file that refer to character set issues:

LTM_charset The character set for the LTM to use. You can specify any Sybase-supported character set.
LTM_language The language used by the LTM to print its messages to the error log and to its clients. You can specify any language to which the LTM has been localized that is compatible with the LTM character set.

The Adaptive Server Anywhere LTM has been localized to ( US ) English and Japanese.
LTM_sortorder The sort order that the LTM uses to compare user names. You can specify any Open Client/Open Server sort order that is compatible with the LTM's character set.

Notes

Character set In an Open Client/Open Server environment, an LTM should use the same character set as the data server and Replication Server attached to it.

Adaptive Server Anywhere character sets are differently specified than Open Client/Open Server character sets. Consequently, the requirement is that the Adaptive Server Anywhere character set be compatible with the LTM character set.

Language Valid settings are those mapped in the locales.dat file in the locales subdirectory of the Sybase release directory. However, the LTM output messages in the user interface are currently available in English and Japanese.

Sort order All sort orders in your replication system should be the same. The default is the default entry for your platform in the locales.dat file in the locales subdirectory of the Sybase release directory.

Example

The following settings are valid for a Japanese installation:

LTM_charset=SJIS
LTM_sortorder=bin_sjis
LTM_language=Japanese

Transaction log and backup management

One of the differences between the Adaptive Server Enterprise LTM and the Adaptive Server Anywhere LTM is that while the Adaptive Server Enterprise LTM depends on a temporary recovery database for access to old transactions, the Adaptive Server Anywhere LTM depends on access to old transaction logs. There is no temporary recovery database for the Adaptive Server Anywhere LTM.

Replication depends on access to operations in the transaction log, and for Adaptive Server Anywhere primary site databases access to old transaction logs is sometimes required. This section describes how to set up backup procedures at a Adaptive Server Anywhere primary site to ensure proper access to old transaction logs.

Consequences of lost transaction logs

It is crucial to have good backup practices at Adaptive Server Anywhere primary database sites. A lost transaction log could mean having to resynchronize replicate site databases. At primary database sites, a transaction log mirror is recommended. For information on transaction log mirrors and other backup procedure information, see the Adaptive Server Anywhere User's Guide.

The LTM configuration file contains a directory entry, which points to the directory where backed up transaction logs are kept. This section describes how you can set up a backup procedure to ensure that such a directory is kept in proper shape.

Backup utility options

One of the options to the Backup utility is to rename the transaction log on backup and restart. For the DBBACKUP command-line utility, this is the -r command-line switch. It is recommended that you use this option when backing up the consolidated database and remote database transaction logs.

To see how this option works, consider a database named primedb.db, in directory c:\prime, with a transaction log in directory d:\primelog\primedb.log. Backing up this transaction log to a directory e:\primebak using the rename and restart option carries out the following tasks:

Backs up the transaction log, creating a backup file e:\primebak\primedb.log.
Renames the existing transaction log to d:\primelog\primedb.lnn, where nn is the lowest available integer, starting at 00.
Starts a new transaction log, as d:\primelog\primedb.log.

After several backups, the directory d:\primelog will contain a set of sequential transaction logs. The log directory should not contain any transaction logs other than the sequence of logs generated by this backup procedure.

You can also run the LTM pointing to the directory where backup copies are held. However, the backup utility makes backups to the same file name each time by default, so you will need to ensure that old logs are renamed before subsequent backups.

Using the DELETE_OLD_LOGS option

The DELETE_OLD_LOGS Adaptive Server Anywhere database option is set by default to OFF. If it is set to ON, then the old transaction logs will be deleted automatically by the LTM when Replication Server no longer needs access to the transactions. This option can help to manage disk space in replication setups.

You can set the DELETE_OLD_LOGS option either for the PUBLIC group or just for the maintenance user. The following statement sets the option for the PUBLIC group:

SET OPTION PUBLIC.DELETE_OLD_LOGS = 'ON'

For Info For more information, see DELETE_OLD_LOGS option.

The Unload utility and replication

If a database is participating in replication, it cannot be unloaded and reloaded without re-synchronizing the database. Replication is based on the transaction log, and when a database is unloaded and reloaded, the old transaction log is no longer available. For this reason, good backup practices are especially important when participating in replication.

Replicating an entire database

Adaptive Server Anywhere provides a short cut for replicating an entire database, instead of having to set each table in the database as a replicated table.

A PUBLIC database option called REPLICATE_ALL can be set using the SET OPTION statement. You can set a whole database to be replicated using the command:

SET OPTION PUBLIC.Replicate_all='ON'

You require DBA authority to change this and other PUBLIC option settings. You must restart the database for the new setting to take effect. The REPLICATE_ALL option has no effect on procedures.

For Info For more information, see REPLICATE_ALL option.

Stopping the LTM

The LTM can be shut down from the user interface in Windows NT, and in other circumstances must be shut down by issuing a command.

To stop the LTM in Windows NT, when the LTM is not running as a service:

`Click Shutdown on the user interface.

To stop the LTM by issuing a command:

Connect to the LTM from isql using the LTM_admin_user login name and password in the LTM configuration file. The user ID and password are case sensitive.
Stop the LTM using the SHUTDOWN statement.

Example

The following statements connect isql to the LTM PRIMELTM, and shut it down:
```
isql -SPRIMELTM -UDBA -PSQL
1> shutdown
2> go
```