Creating 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.

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:

Datatype classes are not replicated and cannot be modified. Column-level translations are implemented after subscription resolution and before class-level translations. See “Using class-level and column-level translations together” for more information.

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:

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.

NoteNative 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).