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 9.0.0 MobiLink  
RSS Feed
 
 
 

Recommended ODBC Drivers for 9.0.0 MobiLink

The MobiLink server connects to several types of consolidated database, using the ODBC 3.5 standard. For most supported consolidated database types, you should use the iAnywhere Solutions ODBC driver for that type of database. The iAnywhere Solutions ODBC drivers and the MobiLink server are included in the SQL Anywhere Studio product. When iAnywhere Solutions does not provide an ODBC driver, then you must use one provided by another vendor. ODBC drivers from other vendors may not support some optional ODBC functionality that the MobiLink server requires.

Some ODBC drivers have different behaviors that cannot be detected by querying the driver. For these we test the driver to determine viable workarounds. 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. It then will automatically apply the required set of workarounds for that driver.

For some ODBC drivers, we are unable 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 from other vendors will fail with multiple simultaneous connections or under high load while running on multiple processors.

Recommended Drivers

As a result of our testing, we recommend the following ODBC drivers for use with version 9.0.0 of the MobiLink server.

If you are having problems with an older version of MobiLink, you should consider upgrading to a newer version. Older versions may no longer be actively supported. For more information, see SQL Anywhere Supported Platforms and Engineering Support Status.

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 8, 8i & 9i

Microsoft SQL Server 2000

IBM DB2 UDB 7.2, 8.1

Warning for Unix

On Unix, the ODBC system information file (.odbc.ini by default) may be deleted if file descriptor handles are exhausted. Be sure to keep a backup copy of the file, or change it to have read-only access, to avoid losing your ODBC driver information.

Sybase Adaptive Server Anywhere (ASA)

Driver

Adaptive Server Anywhere 9.0

Version

9.0.0

Status

Recommended for use with MobiLink.

Client s/w

None.

OS

Win32, Win64, Solaris, Linux, AIX, Mac OSX

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.

On Unix and Linux, make sure the directory containing the symbolic link to your ASA ODBC driver is first in your LD_LIBRARY_PATH. This prevents the loading of the driver manager used by the non-ASA ODBC drivers. The ASA ODBC driver must not be used with the non-ASA driver manager.

Sybase Adaptive Server Enterprise (ASE)

Driver

iAnywhere Solutions 9 - Sybase ASE ODBC driver
DataDirect Sybase Wire Protocol ODBC Driver

Version

4.10.00.48 (Win32), 4.20.00.12 (Solaris, Linux, AIX)

Status

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

Client s/w

None

OS

Win32, Solaris, Linux, AIX

Workarounds applied by MobiLink

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

Other required workarounds

None

Pros

All MLR and MLB tests pass.

Cons

The MLB test for 1000 clients and 1000 rows with deprecated cursor-based uploads may fail. Always use statement-based uploads instead.

Notes

The file name of the driver is wqase18.dll on Win32, wqase19.so on Solaris, Linux and AIX.

When setting up the DSN, on Win32, check the "Enable Describe Parameter" option and select the "2-Full" option for Prepare Method on the Performance tab. On Solaris, Linux and AIX, set the "OptimizePrepare=2" option and leave the other options with default values. Setting "OptimizePrepare=3" and "SelectMethod=1" can cause blocking in the ASE server.

If you are using deprecated cursor-based uploads, you may need to use -hwC+

 

Oracle 8, 8i & 9i

We do not recommend any ODBC drivers from Oracle. All of the ones from Oracle that we have tested have had multi-threading issues. Use one of the following drivers instead.

Driver

iAnywhere Solutions 9 - Oracle Wire Protocol ODBC Driver
DataDirect Oracle Wire Protocol ODBC Driver

Version

4.10.00.48 (Win32), 4.20.00.12 (Solaris, Linux, AIX)

Status

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

Client s/w

None

OS

Win32, Solaris, Linux, AIX

Workarounds applied by MobiLink

-hwf+i+k+w+A+F+

Other required workarounds

None

Pros

All MLR tests pass, except a test of bulk inserts using stored procedures.
All MLB tests pass.

Cons

If the database uses the Unicode character set, then the Win32 driver will not support the CLOB data type.

The driver doesn't support bulk insert using stored procedures.

Notes

The file name of the driver is wqora18.dll on Win32, wqora19.so on Solaris, Linux and AIX.

This driver supports Oracle 8i (8.1.6, 8.1.7) and Oracle 9i.

This driver doesn't need Oracle client software, is faster than the Oracle native ODBC driver and is easier to set up.

On Win32, the driver has problems establishing a savepoint in some situations. This bug does not cause synchronizations to fail, and it will be fixed in a subsequent EBF or maintenance release.

On Unix, EnableScrollableCursor=1 should be set, otherwise, it will fail to unlock the driver.

 

Driver

DataDirect Oracle ODBC Driver

Version

4.10.00.48

Status

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

Client s/w

Net8 8.1.7.0.0, Net8 8.1.7.0.1 (Linux), Net 9.0.1.1.1

OS

Win32, Solaris, Linux

Workarounds applied by MobiLink

-hwf+i+k+w+A+F+

Other required workarounds

-hwC+ required for deprecated cursor-based upload scripts,

Pros

All MLR and MLB tests pass.

Cons

This driver has problems with double-hyphen comments and unnecessary semicolons.

Notes

The file name of the driver is ivor818.dll on Win32, ivor818.so on Solaris and Linux.

This driver supports Oracle 7.3.4, Oracle 8.0.5 and above, including Oracle 8i and 9i.

When setting up the DSN, on Win32, don't check "Enable Static Cursors for Long Data"; on Unix, don't set EnableStaticCursorsForLongData=1, it will adversely affect MobiLink performance. "Application Using Threads" should always be checked on Win32 or "ApplicationUsingThreads=1" should be set on Unix. If you use stored procedures to return result sets, you need to check "Procedure Returns Results" on Win32 or set "ProcedureRetResults=1" on Unix.

On Linux, Net8 version 8.1.7.0.1 is required, plus a patch that addresses Oracle bugs 1489496 and 1467074.

On Unix, unset the LC_MESSAGES enviroment variable or set the LC_MESSAGES=en_US. Improper setting of LC_MESSAGES will make the driver unusable.

It's best if the character set of the Oracle client is the same as the database server character set. If they are different, there are many ways mentioned in Oracle documents to avoid the problem. The simplest way is setting the NLS_LANG enviroment variable on the client side. For example, on Unix, if the charset of consolidated database is WE8ISO8859P1, export NLS_LANG on the command line as
    export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

 

Microsoft SQL Server 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 indication that it is being lost!

Driver

Microsoft SQL Server ODBC Driver

Version

2000.81.9030.04 (for SQL Server 2000, Win32)
2000.85.1022 (for SQL Server 2000, Win64)

Status

Recommended for use with MobiLink.

Client s/w

None

OS

Win32, Win64

Workarounds applied by MobiLink

-hwq+e+C+

Other required workarounds

None

Pros

Passes all MLR and MLB tests.

Cons

None

Notes

Be sure to put "SET NOCOUNT ON" at the start of all stored procedures.

For SQL Server 2000, the driver has problems with the ODBC convention for calling stored procedures:
    { call MyMSSProc(?, ?, ? ) }
You should instead use the native calling convention:
    exec MyMSSProc ?, ?, ?

 

IBM DB2 UDB 8.1, 7.2

Important:

Driver

iAnywhere Solutions 9 - DB2 Wire Protocol ODBC Driver (Only available on Solaris, Linux, AIX)
DataDirect DB2 Wire Protocol ODBC Driver

Version

4.20.00.12 (Solaris, Linux, AIX)

Status

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

Client s/w

None

OS

Solaris, Linux, AIX

Workarounds applied by MobiLink

-hwf+n+u+A+D+

Other required workarounds

None

Pros

Passes all MLR tests.
Passes all MLB tests.

Cons

The BLOB data type is only supported when running against DB2 8.1 or above.

This driver supports passing literal values to stored procedures.

This driver only supports up to 32K-length values of the CLOB data type when running against DB2 7.2 or previous server.

Notes

This driver supports DB2 UDB 7.2 and 8.1.

The performance of this driver is better than IBM provided native DB2 ODBC driver.

If your consolidated database is DB2 7.2, and you want to support BLOB, or CLOB bigger than 32k, this driver is not recommended.

If your consolidated database is DB2 7.2 you will find the following problems if you are using deprecated cursor-based uploads instead of statement-based uploads:

  1. If set WithHold=1 in your odbc.ini file,the maximum row number that Mobilink can upload is rowset_size x 5446, where the rowset_size is set by -s, default is 10, otherwise Mobilink will crash inside the driver. This is a driver bug and has been reported to DataDirect. Setting WithHold=0 doesn't have this restriction.
  2. During upload, If the cursor rowset_size is bigger than 1, and there is an unique violation error, the driver won't rollback all inserted rows, using the workaround -s1 to solve the problem.

Before you use the DB2 data source for the first time, you must successfully bind a package for the data source. Consult the MobiLink documentation for details.

This driver has difficulties caching prepared SQL statements in some situations when using deprecated cursor-based uploads. The -hwC+ workaround fixes this problem.

For MLB testing, the database configuration parameter LOCKLIST needs to be changed to 500.

The CHR() function of DB2 UDB 8.1 changes 0x00 to 0x20.

The file name of the driver is wqdb219.so on Solaris, Linux and AIX.

 

Driver

IBM DB2 UDB 8.1 ODBC Driver

Version

8.01.02.169 (Win64)

Status

Recommended for use with MobiLink with caveats.

Client s/w

DB2 UDB Client 8.01

OS

Win64

Workarounds applied by MobiLink

-hwf+h+n+u+A+

Other required workarounds

None

Pros

Passes all MLR tests.
Passes all MLB tests.

Cons

With "Long object binary treatment" set to "AS LONGVAR data", all BLOB, CLOB, LONG VARCHAR, LONG VARCHAR FOR BIT DATA tests will fail.

Notes

This driver is available for Win32 as well as Win64, but for Win32 we recommend using the iAnywhere driver instead.

This driver doesn't support cursor update. Cursor-based uploads are deprecated; always use statement-based uploads.

For MLB testing, two database configuration parameters LOCKLIST and APPLHEAPSZ need to be changed. Set LOCKLIST to 500 and APPLHEAPSZ to 1024.

The CHR() function of DB2 UDB 8.1 changes 0x00 to 0x20.

The above results come from testing DB2 UDB 8.1 server. There are a lot of restrictions for using this driver with DB2 UDB 7.2 or earlier servers. Please refer to IBM documentation. We suggest you don't use this driver with DB2 UDB 7.2 or earlier servers.

 

Driver

IBM DB2 UDB ODBC driver

Version

7.01.00.65

Status

Recommended for use with MobiLink with caveats.

Client s/w

DB2 UDB Client 7.01.0000 with FixPak 3 and FixPak 7

OS

Win32

Workarounds applied by MobiLink

-hwf+h+n+u+A+

Other required workarounds

None

Pros

Passes all MLR tests, except two BLOB columns tests, using statement-based upload.
Passes fewer MLR tests using cursor-based upload.
Passes all MLB tests.

Cons

Cursor-based uploads of BLOB, CLOB, LONG VARCHAR and LONG VARCHAR FOR BIT DATA fail.

The driver has a bug with two or more BLOB columns, SQLParamData shows "Memory allocation failed" when doing second BLOB column.

Notes

Use statement-based uploads instead of deprecated cursor-based uploads.

To install this driver, if your DB2 UDB client is 7.01.0000, you need to first install FixPak3 and then install FixPak 7; if your DB2 UDB client is 7.02.0000, just install FixPak 7 directly.

For MLB testing, two database configuration parameters LOCKLIST and APPLHEAPSZ need to be changed. Set LOCKLIST to 500 and APPLHEAPSZ to 1024.

This driver supports DB2 UDB 8.1 database.

This driver is better than 7.01.00.00.


 

Related Links

DOCUMENT ATTRIBUTES
Last Revised: Nov 03, 2009
Product: SQL Anywhere
Technical Topics: MobiLink
  
Business or Technical: Technical
Content Id: 1025763
Infotype: Technote
 
 
 

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