Exact numeric datatype issues

There may be problems with exact numeric datatypes when the values replicated are at the boundaries (maximum or minimum values) of what is supported by the datatype definitions.

Microsoft SQL Server supports either 28 or 38 digits of precision, depending on how the server is started. By default, Microsoft SQL Server supports 28 digits of precision.

Sybase does not provide datatype definitions that support the Microsoft default of 28 digits of precision. Datatype definitions are not needed to support 38 digits of precision, because the Replication Server native numeric datatypes support up to 72 digits of precision.

When a number exceeds numeric precision of the Microsoft SQL Server replicate database, Replication Server returns the following error:

E. 2007/12/14 11:14:58. ERROR #1028 DSI EXEC(134(1)
   dcm_gabeat70_devdb.devdb)
   - dsiqmint.c(2888)
   Message from server: Message: 30291, State 0, 
   Severity 19 --
   '[VENDORLIB] Vendor Library Error: [[Message
   Iteration=1|Data Source
   Name=mssql70_devdb|SQLState=22003|Native
   Error=1007|Message=[Microsoft][ODBC SQL Server
   Driver][SQL Server]The number
   '9999999999999999999.9999999999999999999' is out of
   the range for numeric representation (maximum
   precision 28).[Message Iteration=2|SQLState=22003|
   Native Error=|Message=[Microsoft][ODBC SQL Server
   Driver][SQL Server]The number
   '0.99999999999999999999999999999999999999' is out of
   the range for numeric representation (maximum
   precision 28).] <DCA>'

The most difficult numeric datatype issues involve precision and scale. Replication Server does not allow the precision and scale of a decimal datatype to be specified. A datatype definition can specify the maximum precision and maximum scale to be supported. However, if this does not equate to the specified precision and scale of an individual replicate column, then as the data approaches values near or at the boundaries, you may encounter problems that are reported differently, depending on the replicate data server.

For example, suppose you have a primary column declared as decimal (8,5) (8 digits of precision and a scale of 5), and suppose the replicate column is declared as decimal (6,4), even though the replicate data server can support a maximum of 7 digits precision and a scale of 7. In the replication definition, you specify the translation for the primary data server decimal datatype and for which there is a class-level translation to the replicate data server decimal datatype. Both datatype definitions specify the associated data servers maximum precision and scale.

If the value 999.99999 comes from the primary database, and the replicate data server’s datatype definition specifies that rounding should be attempted, Replication Server attempts to apply a value of 1000.000. Even though this value satisfies the replicate database requirements for maximum precision and scale, it fails the precision and scale specified for this particular column. And if you specify for the replicate database’s datatype definition that it should replace the value with the specified maximum value for the datatype definition, Replication Server attempts to apply a value of 9999999, which also fails the specified precision and scale for this particular column.

Error messages you might see from various data servers in this case include:

• The following DB2 error:

  E. 2007/12/14 15:03:11. ERROR #1028 DSI EXEC(129(1)
     dwm5_via_rct.dwmdbas) 
     - dsiqmint.c(2888)
     Message from server: Message: 30291, State 0,
     Severity 19 --
     '[VENDORLIB] Vendor Library Error: [[Message
     Iteration=1|SQLState=22003|Native Error=
     -413|Message=[Sybase][ClearConnect ODBC][DB2]The
     decimal or numeric value had an incorrect wire
     length compared to its specified FDOCA length
     10000000000000000000.00000000000] <DCA>'.
  

• The following Microsoft SQL Server error:

  E. 2007/12/14 12:29:16. ERROR #1028 DSI EXEC(134(1)
     dcm_gabeat70_devdb.devdb)
     - dsiqmint.c(2888)
     Message from server: Message: 30291, State 0,
     Severity 19 --
     '[VENDORLIB] Vendor Library Error: [[Message
     Iteration=1|Data Source Name=mssql70_devdb|SQL
     Function=INSERT|SQLState=22003|Native Error=
     8115|Message=[Microsoft][ODBC SQL Server Driver]
     [SQL Server]Arithmetic overflow error converting
     numeric to data type numeric.[Message Iteration=
     2|SQLState=01000|Native Error=|Message=
     [Microsoft][SQL Server]The statement has been
     terminated.] <DCA>'