This section describes some known issues and limitations with the HDS feature in Replication Server.
The datatype translations provided by Sybase specify that the thread attempting a translation where the source value exceeds the bounds of the target datatype should be stopped with the following error message:
E. 2001/12/14 11:14:54. ERROR #32055 DSI EXEC(135(1) snickers_dco.ora805) - /nrm/nrm.c(7023) Class Level translation for column/parameter 'datetimecol' failed. Source DTID is 'datetime'. Target DTID is 'rs_oracle_datetime'. Function String Class ID 'rs_oracle_function_class'. Value length is '21'; Maximum target length is '20'; The value is '99991231 23:59:59:010'
Typically, these are the most difficult translation problems to diagnose, because there appears to be no problem with either the pairing of source/target datatypes or the value to be translated.
To diagnose this type of problem, you have to be familiar with the datatype value boundary limits of all the translated target datatypes. For example, to diagnose the error shown, you have to know that the upper boundary of an Oracle DATE value is 12/31/4712.
There are other options:
Use the maximum value of the datatype definition.
Use the minimum value for the datatype definition.
Use the default value for the datatype definition.
The only way you can specify these options is to edit the minimum and maximum boundary error action column values in each hds_xxx_udds.sql script and re-install the datatype definitions by re-executing the hds_xxx_udds.sql scripts in the RSSD.
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 default 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. 2001/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 server's 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. 2001/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 Informix error:
E. 2001/12/14 16:07:31. ERROR #1028 DSI EXEC(136(1) dock723_dev_db.devdb) - dsiqmint.c(2888) Message from server: Message: 30291, State 0, Severity 19 -- '[VENDORLIB] Vendor Library Error: [[Message Iteration=1|SQLState=S1000|Native Error= -1226|Message=[Visigenic][ODBC Informix 7.2 Driver][Informix]-1226: Decimal or money value exceeds maximum precision.] <DCA>'
The following Microsoft SQL Server error:
E. 2001/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>'
Most data servers support integer datatypes with values ranging from -2147483648 to 2147483647, and small integer datatypes with values ranging from -32768 to 32767. Informix supports INT datatypes with values ranging from -2147483647 to 2147483647, and SMALLINT datatypes with values ranging from -32767 to 32767.
Informix supports only up to 32 digits of precision/scale in exact numerics, and Adaptive Server supports up to 38 digits of precision/scale.
Sybase does not provide datatype definitions that:
Limit the lower bound of Informix integer datatypes
Support the restriction of precision/scale in Informix numeric datatypes
You cannot directly insert or update binary datatypes in Informix. If you need to replicate binary data to an Informix database, you must capture the binary literal value as a char or varchar datatype. However, there is no class-level translation from Adaptive Server datatypes to Informix datatypes that handles the bit binary datatype.
The problem is that, without a translation, the bit value goes out as 0x00 or 0x01, which is syntax that Informix does not understand. In this situation, you receive an error similar to this:
E. 2001/12/14 15:11:14. ERROR #1028 DSI EXEC(136(1) dock723_dev_db.devdb) - dsiqmint.c(2888) Message from server: Message: 30291, State 0, Severity 19 -- '[VENDORLIB] Vendor Library Error: [[Message Iteration=1|SQLState=S1000|Native Error= -280|Message=[Visigenic][ODBC Informix 7.2 Driver] [Informix]-280: A quoted string exceeds 256 bytes.] <DCA>'
If you attempt to rely on class-level translations to handle commands with bit data, you receive an error similar to this:
E. 2001/12/14 15:19:57. ERROR #1028 DSI EXEC(136(1) dock723_dev_db.devdb) - dsiqmint.c(2888) Message from server: Message: 30291, State 0, Severity 19 -- '[VENDORLIB] Vendor Library Error: [[Message Iteration=1|SQLState=37000|Native Error= -201|Message=[Visigenic][ODBC Informix 7.2 Driver] [Informix]-201: A syntax error has occurred.] <DCA>'.
Replication Server function strings to set identity insert off and on work in Microsoft SQL Server because it supports identity columns in the same manner as Adaptive Server. However, to support 28-digit precision in a Microsoft SQL Server database, the numeric datatype must be translated to the rs_msss_numeric datatype, and as a result, the identity characteristic is lost. To avoid this problem, the Microsoft SQL Server replicate table must not declare a translated numeric column as an identity.
If you attempt to replicate a translated numeric datatype into an identity column in Microsoft SQL Server, you receive an error similar to this:
E. 2001/12/14 12:05:39. 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=23000|Native Error=544 |Message=[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'ase_alltypes' when IDENTITY_INSERT is set to OFF.] <DCA>'
Datatype definitions for a particular data server datatype are grouped in a datatype class.
For more information about datatype definitions (user-defined datatypes), see the description of the RSSD rs_datatype table in the Replication Server Reference Manual.
The text, image, raw and rs_address datatypes cannot be used as either the source or target of column-level or class-level translations.
Sybase provides function-string classes and associated function strings for all supported non-Sybase replicate data servers. Sybase does not provide unique error classes for non-Sybase data servers.
The following limitations apply to HDS function strings:
The function-string classes are loaded into the RSSD when the RSSD is installed. However, the individual function strings must be loaded separately.
The function strings are designed to work with the replicate setup scripts provided by Sybase. Using any other technique to set up the replicate data server, in particular creating the Replication Server support objects (tables), may result in the function strings not working properly.
After the function strings are installed, the Replication Server must be restarted. This is because, with the exception of “input template” function strings, Replication Server caches all function strings at start-up.
The following limitations apply to object publications and subscriptions in a Sybase replication system:
When declaring columns in a replication definition for a non-Sybase primary database, you must use the Replication Server datatype that matches the datatype of the column in the primary database. If there is no matching native Replication Server datatype, you must find a datatype definition that matches the primary database datatype.
When creating subscriptions with where clauses predicated on a column involved in column-level translation, you must specify the predicate value in “declared” format (that is, before translation).
