Precision and Scale of Fixed-Point Number

Different databases support different maximum precision or scale for the fixed-point number types.

If the source fixed-point number type supports larger precision or scale values than the target and a possible data truncation is foreseen for a non-KEY column, then a warning message is displayed and a suitable string data type is used on the target to avoid data truncation. However, if the data truncation is foreseen for a KEY column of fixed-point number type, then Replicat abends with the corresponding error message. You will have to either choose AUTOSCHEMAOPTIONS TYPEMAP or use AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION to proceed.

Following scenarios describe the usage of these options:

• If the data truncation is foreseen with a user specified option (either AUTOSCHEMAOPTIONS TYPEMAP or AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION) then, the data gets truncated with a corresponding warning message.

  For example:

  MySQL has maximum decimal precision of 65, but Oracle is 38. Therefore DECIMAL(65,10) will be mapped to VARCHAR2 in Oracle.

  The target scale might be automatically adjusted maintaining the precision/scale ratio if the source scale is longer than the target precision.

  For example:

  With AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION, MySQL’s DECIMAL(65,40) will be mapped to NUMBER(38,23) on Oracle and MySQL’s DECIMAL(65,64) will be mapped to NUMBER(38,37) on Oracle.

• The NUMBER type without any explicit precision and scale is handled in the following manner:

  The NUMBER type is supported only in Oracle and TimesTen. In the preview release, TimesTen is not supported. If the source data type is NUMBER (without any explicit precision or scale) and if the target database also supports NUMBER datatype, then the target NUMBER type is used on the target.

  If the target database does not support NUMBER datatype, then the suitable string type such as VARCHAR or VARCHAR2 is used on the target.

  Example:

  NUMBER(5,-2) is mapped to DECIMAL(7,0)

  NUMBER(1,2) is mapped to DECIMAL(2,2)

• If the precision or scale used on the source column is more than what can be supported on the target, and if the corresponding column is part of the key, then a string type can be used on the target to avoid data truncation on the target, which can defeat the uniqueness property on the target table. This can be controlled by an AUTOSCHEMAOPTIONS parameter.