Datatype compatibility

byte

2³¹ bytes, binary data

image

2GB

blob

4 terabytes (4*2⁴⁰), binary data

image

2GB

Values over 2GB are truncated.

boolean

1 byte

char(1)

1 byte

Values are replicated as literal characters t or f.

char(n)

32,767 bytes

char, varchar

32K

clob

4 terabytes (4*2⁴⁰), text data

text

2GB

Values over 2GB are truncated.

date

number of days since 12/31/1899

datetime

01/01/1753 to 12/31/9999

No special effort is required to format the value for either Replication Server or Adaptive Server.

If the Informix value falls outside the Replication Server supported range, Replication Server will not accept it.

Options for handling this situation include:

• Using char(10) as the datatype in the replication definition.

• Modifying the triggers and stored procedures that support the shadow table to normalize values outside Replication Server limits.

datetime largest_qualifier to smallest_qualifier

YEAR: 1 - 9999

MONTH: 1 - 12

DAY: 1 - 31

HOUR: 0 - 23

MINUTE: 0 - 59

SECOND: 0 - 59

FRACTION: 0 - 99999

char(n) or datetime

If datetime is used, 01/01/1753 to 12/31/9999

If char is used, the value of n depends on the date elements defined for the column. For example, datetime year to fraction(5) requires n to be 25, datetime month to day requires n to be 5.

If datetime is used, set the value of the pdb_convert_datetime parameter to true before marking the table, then deal with the range issue as described for the Informix date datatype.

decimal(p) or dec(p)

10^-130 to 10¹²⁴, up to 32 significant digits

decimal or float

When decimal is used, 10^-38 to 10³⁸, 38 significant digits.

float precision and range corresponds to a C double datatype, approximately 16 significant digits.

Using the decimal datatype allows precision at the expense of range.

Using the float datatype allows range (to the extent that the Replication Server platform supports range) at the expense of precision.

However, extremely small values get truncated at log time to 16 digits to the right of the decimal. For example, with the primary column datatype of decimal(32), the number 1.2345678901234567890123456789012e-10 is logged as 0.0000000001234567.

Extremely large values retain precision.

decimal(p,s) or dec(p,s) or numeric(p,s)

Range without error is 0 to 10^p-s - 10^-s, up to 32 significant digits

decimal

10^-38 to 10³⁸, 38 significant digits.

float(n) or double precision

Corresponds to a C double datatype on the given system, approximately 16 significant digits.

float

Corresponds to a C double datatype on the given system, approximately 16 significant digits.

Errors can be introduced if the Informix platform and Replication Server platform handle the C double datatype differently.

Extremely large values may lose significant digits at log time. For example, with primary column datatype of float, the number 1.234567890123456e125 is logged as 1.234568e+125.

However, extremely small values retain all digits.)

integer or int

-2,147,483,647 to 2,147,483,647.

int

-2,147,483,648 to 2,147,483,647.

int8

-9,223,372,036, 854,775,807 to 9,223,372,036, 854,775,807

decimal

10^-38 to 10³⁸, 38 significant digits.

interval largest_qualifier(n) to smallest_qualifier(n)

1 <= n <= 9 for largest_qualifier. If the type of smallest_qualifier is fraction, 1 <= n <= 5. (n on smallest_qualifier only valid if smallest_qualifier is a fraction.)

char(n)

32K

The value of the char n depends on the interval elements defined for the column. For example, interval year(5) to month requires n to be 9, interval day(9) to fraction(5) requires n to be 25.

By modifying the script that builds the shadow table and associated primary table triggers and stored procedures, you can break the interval value into separate integer values. Or you can use a function string to do this breakdown for each replicate.

lvarchar

4 GB

text

2GB

Values over 2GB are truncated.

money(p,s)

Range without error is 0 to 10^p-s - 10^-s, up to 32 significant digits

money or small-money

The money range is -922,337,203, 685,477.5808 to 922,337,203, 685,477.5807.

The smallmoney range is -214,748.3648 to 214,748.3647.

Both money and smallmoney datatypes are accurate to one ten-thousandth of a monetary unit. This results in truncation if the primary column allows accuracy beyond that level.

nchar(n)

up to 32,767 bytes of multi-byte character data

unichar or char

32K

To use the unichar datatype, the use_rssd parameter must be true and the replication definition must specify a Unicode datatype.

nvarchar(m,r)

up to 255 bytes of multi-byte character data

univarchar or varchar

32K

To use the univarchar datatype, the use_rssd parameter must be true and the replication definition must specify a Unicode datatype.

real

See smallfloat.

serial

-2,147,483,647 to 2,147,483,647.

identity or integer

-2,147,483,648 to 2,147,483,647.

If identity is used, the following command is applied to the replicated table before an insert command:

set identity_insert table_name on

The following command is applied to the replicated table after an insert command:

set identity_insert table_name off

Note: Columns with the identity datatype are never updated by the update command.

serial8

-9,223,372,036, 854,775,807 to 9,223,372,036, 854,775,807

identity or numeric

1 to 10³⁸ - 1

If identity is used, the following command is applied to the replicated table before an insert command:

set identity_insert table_name on

The following command is applied to the replicated table after an insert command:

set identity_insert table_name off

Note: Columns with the identity datatype are never updated by the update command.

smallfloat

Corresponds to a C float datatype, approximately 8 significant digits.

real

Corresponds to a C float datatype, approximately 6 significant digits.

Errors may be introduced if the Informix platform and Replication Server platform handle the C float datatype differently.

smallint

-32,767 to 32,767

smallint

-32,768 to 32,767

text

2 GB, text data.

text

2GB

varchar(m [,r])

255 bytes

char or varchar

32K