Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Technote > Recommended ODBC Drivers for 7.0.4 MobiLink  
RSS Feed
 
 
 

Recommended ODBC Drivers for 7.0.4 MobiLink

The MobiLink server uses ODBC 3.0 to connect to the consolidated database. The information presented here is intended to help you choose which ODBC driver to use with your consolidated database.

In theory MobiLink should be able to use as a consolidated database any ODBC 3.0 data source that supports the optional ODBC functionality required by MobiLink, but in practice ODBC implementations vary widely. Different ODBC drivers have different capabilities, and the MobiLink server attempts to adapt to these by querying the driver. Typically the driver also has behavior (or bugs) that can't be detected by querying the driver. For these we test the driver in an attempt to find viable workarounds. Some workarounds are used for different consolidated database vendors, while others are specific to a vendor and even a specific version.

Once we have identified a set of workarounds that are required for a driver, we update the MobiLink server so that it recognizes the version of the driver, and automatically associates the set of workarounds with that version.

For some ODBC drivers, we are not able to develop viable workarounds for all issues. In these cases, you still may be able to use the driver if your synchronizations do not require the missing driver functionality.

For ease of testing, and to allow MobiLink to apply workarounds to drivers that it does not recognize, the MobiLink workarounds can be set by the undocumented -hw command-line switch. This switch and its associated workarounds are not documented because indiscriminate use of workarounds could cause data corruption or software errors.

Note that after workaround sets are identified, we can make future versions of MobiLink apply the associated hidden workaround switches automatically. Thus if you chose to use hidden workarounds directly, you may not need them with newer versions of the MobiLink server. Continued use of hidden switches with a newer MobiLink may even cause problems or reduced performance.

Driver testing

We use two types of testing of ODBC drivers with MobiLink.

The first, referred to as MLR, is a comprehensive suite of regression tests that exercise synchronization functionality. If some specific MLR tests fail, the driver may still be suitable as long as your synchronizations do not rely on the functionality that failed. For example, if the only MLR tests that fail are for synchronization of LONG BINARY data, then the driver may still be suitable for you if you are not using MobiLink to synchronize LONG BINARY data.

The second type of testing, referred to as MLB, is high-load testing involving many clients synchronizing simultaneously with a MobiLink server running on a multi-processor computer. We have found that some drivers will fail with multiple simultaneous connections or under high load while running on multiple processors. In these cases, you may need to restrict MobiLink to run on a single processor (via the -zt 1 switch) or limit it to a single worker thread (via the -w 1 switch).

Recommended Drivers

As a result of our testing, we recommend the following ODBC drivers for use with version 7.0.4 of the MobiLink server. Unless specified otherwise, the recommendations refer to the Windows version of MobiLink.

The following sections list the recommended ODBC driver for different types of consolidated database. In some cases, alternative drivers are also listed. Click on one of the consolidated DBMS types below to see recommended driver information for using MobiLink with that type of DBMS:

Sybase Adaptive Server Anywhere (ASA)

Sybase Adaptive Server Enterprise (ASE)

Oracle 9i

Oracle 8 and 8i

Microsoft SQL Server 7 and 2000

IBM DB2 UDB 5.2, 7.1

Sybase Adaptive Server Anywhere (ASA)

Driver

Adaptive Server Anywhere 7.0

Version

7.0.4

Status

Recommended for use with MobiLink.

Client s/w

None.

OS

Win32, Solaris, Linux

Workarounds applied by MobiLink

-hwc+o+

Other required workarounds

None

Pros

All MLR and MLB tests pass.

Cons

None.

Notes

The MLR tests all pass with the ODBC driver included with ASA, and the MLB testing shows no problems with multiple clients simultaneously synchronizing through a MobiLink server running on a multi-processor computer.

ASA 6.0.4, 7.0.0, 7.0.1, 7.0.2 and 7.0.3 drivers also work with MobiLink without additional workarounds.

Sybase Adaptive Server Enterprise (ASE)

Driver

Sybase ASE ODBC Driver

Version

3.50.00.10

Status

Recommended for use with MobiLink with caveats.

Client s/w

Open Client 12.0

OS

Win32, Solaris

Workarounds applied by MobiLink

-hwf+i+j+t+v+w+A+

Other required workarounds

Win32: -hwC+

Solaris: -hwd+

Pros

Win32:All MLR tests pass. MLB tests pass with -hwC+.

Solaris: Most MLR tests pass. MLB tests pass.

Cons

Win32:
Under certain conditions Mobilink can experience an application exception on shutdown. This can be avoided by setting the additional workaround -hwC+, but this option will affect the performance of Mobilink. The problem is caused by reusing the prepared statement cache. It's not clear this is a driver problem or Mobilink bug, and the problem is being investigated.

This driver's upload performance can degrade significantly when there are many download_cursor and download_delete_cursor scripts that contain many columns. The -hwC+ workaround prevents this problem.

MLB tests have only been run using upload_cursor. MLB tests pass when using the option -hwC+

Solaris:
Prepared download script bug failure test failed.

Notes

Win32:
For ASE version 11.9.2 or 12, we recommend using the 12.0 Open Client software and the Sybase ASE ODBC driver version 3.50.00.10.

Solaris:
Some tests related to BIGINT needs the option -hwd+.

When setting up the DSN, set the "OptimizePrepare=2" and "SelectMethod=1".

 

Driver

iAnywhere Solutions - Sybase ASE ODBC Driver
DataDirect Connect ODBC Driver for Sybase ASE

Version

3.70.00.35

Status

Recommended for use with MobiLink with caveats -- This driver must be purchased from DataDirect

Client s/w

None

OS

Win32, Solaris, Linux

Workarounds applied by MobiLink

-hwf+i+j+t+v+w+A+

Other required workarounds

None

Pros

Most MLR tests and all MLB tests pass.

Cons

Only one blob test failed showing "Unclosed quote before the character string '(' in the MobiLink log. Cause of this failure is under investigation.

Notes

For Solaris and Linux platforms, this driver is the best choice, for Win32, the best choice is Sybase ASE ODBC Driver.
When setting up the DSN, for Win32 set the "Enable Describe Parameter" option and select "2- Full" option for Prepare Method on Performance tag; For Solaris and Linux, set the "OptimizePrePare=2" and "SelectMethod=1".

Explicit casts to NUMERIC and DECIMAL in statements is necessary (using CONVERT).

Oracle 9i

We have found the greatest variability between driver versions with Oracle's ODBC drivers. In many cases, new bugs have appeared in subsequent versions of the ODBC driver so if you have an Oracle ODBC driver that works for you, then we recommend that you continue to use it. Most Oracle ODBC drivers seem to have multi-threading issues.

Driver

iAnywhere Solutions - Oracle ODBC Driver
DataDirect Connect ODBC Driver for Oracle

Version

3.70.00.34

Status

Recommended for use with MobiLink with caveats -- This driver must be purchased from DataDirect

Client s/w

For Win32 and Solaris, Net8 8.1.7.0.0 or Net 9.0.1.1.1;
for Linux, Net8 8.1.7.0.0

OS

Win32, Solaris, Linux

Workarounds applied by MobiLink

-hwf+i+k+n+w+A+

Other required workarounds

None

Pros

Passed all MLR tests.
Passes MLB tests.

Cons

Driver has problems with double-hyphen comments and unnecessary semicolons. This driver problem has been reported to Merant.

Notes

For some of the MLR tests, the "SQLDescribeParam" option needs to be enabled for the driver, but it is not needed for the MLB tests.

In the MLB tests, the Merant driver is significantly faster than the Oracle 8.0.5.8 driver for synchronizing downloads but somewhat slower for uploads. The driver works in the MLB tests under high load on multiple processors, but the performance benefit of using multiple processors is small: we found that throughput decreased somewhat when using more than two processors.

Oracle 8 and 8i

Driver

iAnywhere Solutions - Oracle ODBC Driver
DataDirect Connect ODBC Driver for Oracle

Version

3.70.00.34

Status

Recommended for use with MobiLink with caveats -- This driver must be purchased from DataDirect

Client s/w

Net8 8.1.6.0.0 or Net8 8.1.7.0.0

OS

Win32, Solaris, Linux

Workarounds applied by MobiLink

-hwf+i+k+n+w+A+

Other required workarounds

None

Pros

Passes all MLR tests.
Passes MLB tests.

Cons

Driver has problems with double-hyphen comments and unnecessary semicolons. This driver problem has been reported to Merant.

Notes

For some of the MLR tests, the "SQLDescribeParam" option needs to be enabled for the driver, but it is not needed for the MLB tests.

In the MLB tests, the Merant driver is significantly faster than the Oracle 8.0.5.8 driver for synchronizing downloads but somewhat slower for uploads. The driver works in the MLB tests under high load on multiple processors, but the performance benefit of using multiple processors is small: we found that throughput decreased somewhat when using more than two processors.

 

Driver

Oracle ODBC Driver

Version

8.0.5.8.0

Status

Recommended for use with MobiLink with caveats.

Client s/w

Net8 8.0.5.0.0 or 8.0.5.0.3

OS

Win32

Workarounds applied by MobiLink

-hwd+h+k+r+s+

Other required workarounds

-zt 1 (to restrict execution to a single processor)

Pros

Passes most MLR tests.
Passes MLB tests when run on a single processor.

Cons

Fails MLB tests when run on multiple processors.

Notes

The most functional ODBC driver from Oracle that we have tested is version 8.0.5.8.0, when used with Net8 version 8.0.5.0.0. This driver passes all MLR tests, and as long as it is not run on multiple processors it also works for the MLB tests. When run under high load from multiple connections on multiple processors, an application exception will eventually occur in an Oracle ODBC driver DLL. Typically this occurs as an unknown exception (0xebad0000) in ORA805.DLL (which is called from OCI.DLL). On a multiple-processor computer, you can restrict MobiLink (and hence the ODBC driver) to execute on a single processor by using the -zt 1 MobiLink command-line option.

The problem with multiple processors also happens when the Net8 client is updated to version 8.0.5.0.3.

 

Driver

Oracle ODBC Driver

Version

8.0.5.10.0

Status

Not recommended for use with MobiLink.

Client s/w

Net8 8.0.5.0.0

OS

Win32

Workarounds applied by MobiLink

-hwd+f+h+k+p+r+s+

Other required workarounds

-hwp-q+

Pros

Passes most MLR tests.

Cons

Fails a few MLR tests with "ORA-933: SQL command not properly ended" errors.
Fails MLR tests for LONG VARCHAR and LONG BINARY with "ORA-24801: illegal parameter value in OCI lob function" errors.
Fails MLB tests with "option value changed" and "Invalid attribute/option identifier <0>" errors.

Notes

We were unable to determine the cause of the ORA-933 errors in the MLR tests.

With version 8.0.5.10.0 of the Oracle ODBC driver, the MLB tests could not be run at all. The tests failed from driver errors about invalid attribute/option identifier and changed option values. The same problem occurs with version 8.0.5.9.0 of the Oracle ODBC driver.

We have not been able to determine a viable workaround for these issues, but we are aware that some customers are able to use this driver successfully.

 

Driver

Oracle ODBC Driver

Version

8.1.6.2.0

Status

Not recommended for use with MobiLink.

Client s/w

Net8 8.1.6.0.0

OS

Win32

Workarounds applied by MobiLink

None

Other required workarounds

-hwd+r+ -s 1

Pros

Passes most MLR tests.

Cons

Fails MLR tests for LONG VARCHAR and LONG BINARY columns.
MLR test with REAL columns failed because values uploaded were slightly different when downloaded.
Need to disable bulk fetching (via –s 1). Since rows must be fetched one at a time download performance is adversely affected.

Notes

MLB tests have not been performed with this driver. It may have multi-threaded problems as do all the Oracle ODBC drivers for which we have been able to do MLB testing.

 

Driver

Oracle ODBC Driver

Version

8.1.7.0.0

Status

Not recommended for use with MobiLink.

Client s/w

Net8 8.1.7.0.0

OS

Win32

Workarounds applied by MobiLink

None

Other required workarounds

-hwd+h+k+r+

Pros

Passes most MLR tests.

Cons

Fails MLR tests for LONG VARCHAR and LONG BINARY with "ORA-24801: illegal parameter value in OCI lob function" errors.
Fails MLB tests with "option value changed" and "Invalid attribute/option identifier <0>" errors.

Notes

With version 8.1.7.0.0 of the Oracle ODBC driver (and Net8 8.1.7.0.0), the MLB tests could not be run at all. The tests failed from driver errors about invalid attribute/option identifier and changed option values.

We have not been able to determine a viable workaround for this issue, but we are aware that some customers are able to use this driver successfully.

 

Driver

Oracle ODBC Driver

Version

8.1.7.2.0

Status

Not recommended for use with MobiLink.

Client s/w

Net8 8.1.7.0.0

OS

Win32

Workarounds applied by MobiLink

None

Other required workarounds

-hwd+h+k+n+r+

Pros

Passes most MLR tests. Passes MLB tests when run with a single MobiLink worker thread.

Cons

Fails many MLR tests when executing a stored procedure that is part of the setup for several tests.
Fails MLB tests with conversion errors when run with multiple MobiLink worker threads.

Notes

Version 8.1.7.2.0 of the Oracle ODBC driver (with Net8 8.1.7.0.0) tends to fail with multiple simultaneous synchronizations even when running only on a single processor. Therefore we do not recommend it. The failure typically occurs as a conversion error ("ORA-01461 can bind a LONG value only for insert into a LONG column" or "ORA-01460 unimplemented or unreasonable conversion requested").

You can restrict it to work on only one synchronization at a time by limiting the number of MobiLink worker threads to one (via –w 1), but this will seriously limit throughput performance.

 

Driver

Oracle ODBC Driver

Version

8.1.7.4.0

Status

Not recommended for use with MobiLink.

Client s/w

Net8 8.1.7.0.0

OS

Win32

Workarounds applied by MobiLink

-hwf+i+k+n+w+A+

Other required workarounds

None

Pros

Passes most MLR tests.

Cons

Real data type fails. Fails a few MLR tests with "ORA-933: SQL command not properly ended" errors.
Fails MLR tests for LONG VARCHAR and LONG BINARY with "ORA-24801: illegal parameter value in OCI lob function" errors.
Fails MLB tests with "option value changed" and "Invalid attribute/option identifier <0>" errors.

Notes

We were unable to determine the cause of the ORA-933 errors in the MLR tests.

With version 8.0.5.10.0 of the Oracle ODBC driver, the MLB tests could not be run at all. The tests failed from driver errors about invalid attribute/option identifier and changed option values. The same problem occurs with version 8.0.5.9.0 of the Oracle ODBC driver.

We have not been able to determine a viable workaround for these issues, but we are aware that some customers are able to use this driver successfully.

 

Microsoft SQL Server 7 and 2000

Important: With any version of SQL Server, you should specify "SET NOCOUNT ON" as the first statement in all stored procedures or SQL batches executed via ODBC. Without this option, a "number of rows affected" message is sent to MobiLink for each statement executed. If you are doing a relatively large number of inserts, updates, and select into statements, these messages can fill network buffers resulting in data being lost without any warning.

Driver

Microsoft SQL Server ODBC Driver

Version

3.60.03.19 (for SQL Server 7)
2000.80.194.00 (for SQL Server 2000)

Status

Recommended for use with MobiLink.

Client s/w

None

OS

Win32

Workarounds applied by MobiLink

-hwq+ (for MSS 7)
-hwq+C+ (for MSS 2000)

Other required workarounds

None

Pros

Passes MLR and MLB tests.

Cons

None

Notes

The MLR tests all pass with the ODBC drivers included with Microsoft SQL Server 7 and 2000. MLB tests run with these drivers when the NOCOUNT option was set for all stored procedures.

 

Driver

iAnywhere Solutions - SQL Server
DataDirect Connect ODBC Driver for SQL Server

Version

3.70.00.34

Status

Recommended for use with MobiLink -- This driver must be purchased from DataDirect

Client s/w

None

OS

Win32

Workarounds applied by MobiLink

-hwq+ (for MSS 7)
-hwq+C+ (for MSS 2000)

Other required workarounds

None

Pros

Passes MLR and MLB tests.

Cons

None

Notes

This driver doesn't need client software. If you don't have a SQL Server client, this driver is the best choice.

IBM DB2 UDB 7.1 , 5.2

Driver

IBM DB2 UDB 7.1ODBC driver

Version

Driver is included with IBM DB2 UDB 7.1.

Status

Recommended for use with MobiLink with caveats.

Client s/w

7.01.0000

OS

Win32

Workarounds applied by MobiLink

-hwf+h+n+u+A+

Other required workarounds

None

Pros

Passes most MLR tests.

Cons

Long varchar, long binary, clob, blob, and binary tests fail.  SQLBulkOperatons function fails when dealing with clob and blob.

Notes

MLB tests have not been run with this driver.

 

Driver

IBM DB2 UDB 5.2 ODBC driver

Version

Driver is included with IBM DB2 UDB 5.2.

Status

Recommended for use with MobiLink with caveats.

Client s/w

5.02.0000

OS

Win32

Workarounds applied by MobiLink

-hwf+h+n+u+

Other required workarounds

None

Pros

Passes MLR tests.

Cons

Long varchar, long binary, clob, blob, and binary tests fail.  SQLBulkOperatons function fails when dealing with clob and blob.

Notes

MLB tests have not been run with this driver.

 

Driver

Merant DataDirect Connect ODBC Driver for IBM DB2 UDB

Version

3.7 and 3.7 sp1

Status

Not Recommended for use with MobiLink.

Client s/w

None

OS

Win32

Workarounds applied by MobiLink

-hwf+h+n+u+

Other required workarounds

None

Pros

The MLR will not run due to single connection limitation.

Cons

None

Notes

Merant DataDirect Connect ODBC Driver for DB2 both version 3.7 and 3.7sp1 are not recommended for use with MobiLink. Version 3.7 only supports one connection and short schema name ( Less than 18 characters ). Merant says Version 3.7 sp1 supports multiple connections and long schema name. However there is an application exceptions when using MobiLink when using Merant ODBC Driver for DB2.
MLB tests have not been run with this driver.


 

Related Links

DOCUMENT ATTRIBUTES
Last Revised: Nov 11, 2009
Product: SQL Anywhere
Business or Technical: Technical
  
Content Id: 1024014
Infotype: Technote
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics