Create Column-Level Translations

Column-level translations affect each replicated instance of a particular column (datatype) and table. They are defined using the create replication definition or alter replication definition command.

To set up column-level translations, you simply create or alter the replication definition, identifying the column to be translated and its initial and final datatypes using the map to option.

If you are creating a new replication definition, use create replication definition.

For lists of supported datatype translations, see the Heterogeneous Replication Guide.
If you are adding or altering a column in an existing table, use alter replication definition.

Sybase provides a set of datatype definitions and datatype classes that you can use to modify the datatype of the replicated columns. Each datatype class contains datatype definitions for a particular data server:

Adaptive Server – rs_sqlserver_dt_class
DB2 – rs_db2_dt_class
Microsoft SQL Server – rs_msss_dt_class
Oracle – rs_oracle_dt_class
Sybase IQ – rs_iq_dt_class
UDB – rs_udb_dt_class

Datatype classes are not replicated and cannot be modified. Column-level translations are implemented after subscription resolution and before class-level translations.

You can activate a column-level translation for a particular column when you create or alter a table replication definition. The syntax for create replication definition with column and datatype variables specified for HDS is:

create replication definition replication_definition
with primary at data_server.database
...
(column_name [as replicate_column_name] declared_datatype [null | not null]
[map to published_datatype])
...

where:

The declared datatype depends on the datatype of the value delivered to the Replication Server from the Replication Agent:
- If the Replication Agent delivers a native Replication Server datatype, such as datetime, to the Replication Server, the declared datatype is the native datatype.
- Otherwise, the declared datatype must be the datatype definition for the original datatype at the primary database.
  
  For example, the Replication Agent delivers a value in the DB2 TIMESTAMP datatype, as a character string with delimiters, to Replication Server. In this case, the declared datatype is the datatype definition rs_db2_timestamp.
The published datatype is the datatype of the column after the column-level translation (and before a class-level translation, if any). The published datatype is normally either a Replication Server native datatype or a datatype definition for the datatype in the replicate database. If the published datatype is omitted from the replication definition, it defaults to the declared datatype.

Both declared and published datatypes have a base datatype. For example, the datatype rs_db2_timestamp has a base datatype of char(26); the native datatype char(26) also has a base datatype of char(26). A datatype definition describes a non-Sybase datatype in terms of a Replication Server native datatype. The base datatype fixes the maximum and minimum length to be associated with the datatype definition and provides defaults for other datatype attributes. The base datatype defines the delimitation of values for the datatype definition when a value of that type is delivered to Replication Server either in Log Transfer Language (LTL) or in a command executed by a Replication Server administrator such as create subscription.

Note: Native datatypes include all datatypes supported by Replication Server. However, you cannot use text, unitext, image, rawobject, and rawobject in row datatypes for defining a datatype definition; neither can you use these datatypes as the source or target of a translation.

For example, to create a table replication definition ase_employee_repdef_for_db2 that translates values in the birthdate column from datetime (birthdate primary table datatype) to DB2 DATE datatype for the replicate database, log in to the primary Replication Server and enter:

create replication definition
  ase_employee_repdef_for_db2
    with primary at ase_server.ase_database
    with all tables named ‘employee’
      (empid int,
      first_name char(20),
      last_name char(20),
      ...
      birthdate datetime map to rs_db2_date,
      salary money, 
      ...

In this example, birthdate is the column name, datetime is the declared datatype, and rs_db2_date is the published datatype. Because the declared datatype is a native datatype, the native and base datatype are the same. That is, the base datatype of datetime is datetime. The published datatype rs_db2_date is a datatype definition for DB2, and its base datatype is char(10).