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 12.0.0 MobiLink

Recommended ODBC Drivers for 12.0.0 MobiLink
 
RSS Feed
 
 
 

The MobiLink server connects to several types of consolidated database using the ODBC 3.5 standard. For some supported consolidated database types you should use the Sybase iAnywhere ODBC driver for that type of database. For others we recommend drivers supplied by the database vendors. The Sybase iAnywhere ODBC drivers and the MobiLink server are included in the SQL Anywhere 12 product. When Sybase iAnywhere 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.

Driver testing

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

The first type of testing is a comprehensive suite of regression tests that exercise synchronization functionality. If some specific regression 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 regression 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 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 12.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:

SQL Anywhere (SA) 11 and 12

Sybase Adaptive Server Enterprise 15.0

Oracle 10g and 11g

Microsoft SQL Server 2005 and 2008

IBM DB2 9.5 and 9.7

MySQL 5.1.3

For detailed supported consolidated databases, please refer to MobiLink Consolidated Database Support.

SQL Anywhere (SA) 12.0.0

Driver

SQL Anywhere 12.0.0

Version

12.0.0

Status

Recommended for use with MobiLink

Client s/w

None

OS

Windows

XP, Vista, 7, Server 2003, Server 2008 and 2008 R2

Linux

Redhat Enterprise Linux 4, 5
SuSE Enterprise Linux 10, 11
Ubuntu 8.0.4, 10.0.4
Supported where the kernel/glibc versions are within the ranges specified on the SQL Anywhere Supported Linux Platforms.

Solaris

9, 10

AIX

5.3, 6.1

MAC OS X

10.5, 10.6 (Intel)

Pros

All tests passed

Cons

None

Notes

None

SQL Anywhere (SA) 11.0

Driver

SQL Anywhere 11.0

Version

11.0.x

Status

Recommended for use with MobiLink

Client s/w

None

OS

Windows

XP, Vista, 7, Server 2003, Server 2008 and 2008 R2

Linux

Redhat Enterprise Linux 4, 5
SuSE Enterprise Linux 10, 11
Ubuntu 8.0.4, 10.0.4
Supported where the kernel/glibc versions are within the ranges specified on the SQL Anywhere Supported Linux Platforms.

Solaris

9, 10

AIX

5.3, 6.1

MAC OS X

10.5, 10.6 (Intel)

Pros

All tests passed

Cons

None

Notes

None

Sybase Adaptive Server Enterprise 15.0

Driver

Sybase Adaptive Server Enterprise ODBC driver

Version

15.05.00.1015 on Windows (x86 and x64) and Linux 15.05.00.1012 on Linux (x86 and x64) - This driver is available in Sybase SDK 15.5 ESD #4.

Status

Recommended for use with MobiLink

Client s/w

None

OS

Windows

XP, Vista, 7, Server 2003, Server 2008 and 2008 R2

Linux

Redhat Enterprise Linux 4, 5
SuSE 10, 11

Pros

All tests passed

Cons

On Linux, if set UseCursor=1, the synchronization tables can't be dropped after synchronization.

Notes

  1. Always set UseCursor to zero on Linux.
  2. Always set ServerInitiatedTransactions to zero.
  3. Set AnsiNull to 1, if the "where clauses" in any of the upload and download scripts may have the clause of "column_name = NULL".
  4. On Linux, you need to set SYBASE environment variable to the directory where you install ASE SDK1500, otherwise, you will get the error, "Could not load code page for requested charset".
  5. On Linux, the 64-bit MobiLink server and the other ODBC drivers define SQLLEN (an ODBC data type) as a 64-bit integer. The Sybase ASE ODBC driver defines SQLLEN as a 32-bit integer. This disagreement is handled by the iAnywhere ODBC driver manager for Linux, if a DSN for ASE on Linux contains the following entry:
      length32=1
    This is required by the 64-bit MobiLink server on Linux for ASE.
  6. By default, the ASE ODBC driver from Sybase SDK 15.5 ESD #4 will trim all blank padding in upload and download strings. If you do not want the driver to trim blank padding, add the following connection parameter to the MobiLink server connection string:
      stripblanks=0
    The connection string of the MobiLink server will look like the following:
      -c "dsn=...;...;stripblanks=0"

Permission requirements

  1. Select permission on MASTER..SYSTRANSACTIONS.
  2. The user ID also needs to have the dtm_tm_role role, if the MobiLink server command line, -cs is used.

 

Driver

Sybase Adaptive Server Enterprise ODBC driver

Version

15.0.0.335 - This driver is available in Sybase 15.0 SDK ESD#16.

Status

Recommended for use with MobiLink with caveats - This driver is only recommended with MobiLink 12.0.0.GA.
The driver in Sybase SDK 15.5 ESD #4 is recommended for MobiLink with EBF #2566 and higher.

Client s/w

None

OS

Windows

XP, Vista, 7, Server 2003, Server 2008 and 2008 R2

Linux

Redhat Enterprise Linux 4, 5
SuSE 10, 11

Pros

All tests passed

Cons

On Linux, if set UseCursor=1, the synchronization tables can't be dropped after synchronization.

Notes

  1. Always set UseCursor to zero on Linux.
  2. Always set ServerInitiatedTransactions to zero.
  3. Set AnsiNull to 1, if the "where clauses" in any of the upload and download scripts may have the clause of "column_name = NULL".
  4. On Linux, you need to set SYBASE environment variable to the directory where you install ASE SDK1500, otherwise, you will get the error, "Could not load code page for requested charset".
  5. On Linux, the 64-bit MobiLink server and the other ODBC drivers define SQLLEN (an ODBC data type) as a 64-bit integer. The Sybase ASE ODBC driver defines SQLLEN as a 32-bit integer. This disagreement is handled by the iAnywhere ODBC driver manager for Linux, if a DSN for ASE on Linux contains the following entry:
      length32=1
    This is required by the 64-bit MobiLink server on Linux for ASE.

Permission requirements

  1. Select permission on MASTER..SYSTRANSACTIONS.
  2. The user ID also needs to have the dtm_tm_role role, if the MobiLink server command line, -cs is used.

 

Oracle 10g and 11g

Oracle 10g

Driver

iAnywhere Solutions 12 - Oracle ODBC Driver

Version

12.00.00

Status

Recommended for use with MobiLink

Client s/w

Oracle 10g client

OS

Windows

XP, Vista, 7, Server 2003, Server 2008

Linux

Redhat Enterprise Linux 4, 5
SuSE Enterprise Linux 10, 11

Solaris

9, 10

AIX

5.3, 6.1

Pros

All tests passed

Cons

None

Notes

  1. Before using this driver, you must install Oracle 10g client. To load this driver, you must set Oracle client libraries in your LD_LIBRARY_PATH (Linux, Solaris) or LIBPATH (AIX).
  2. TNS server name on Windows DSN setting and ServerName in Linux and Unix .odbc.ini are Oracle TNS service name defined in $ORACLE_HOME/network/admin/tnsnames.ora
  3. The default setting of "Procedure return results or uses VARRAY parameters" on Windows or ProcResults on Linux and Unix is 'No'.
  4. If you are using a stored procedure that returns a REF CURSOR, the REF CURSOR parameter (either OUT or IN OUT) must be defined as the last parameter in the parameter list of the stored procedure.

Permission requirements

  1. Permission to EXECUTE on SYS.DBMS_UTILITY - required by MobiLink to determine if the database is running on an Oracle RAC
  2. Permission to SELECT from SYS.GV_$TRANSACTION - required by MobiLink server to do TIMESTAMP based downloads
  3. Permission to SELECT from SYS.V_$SESSION - required by MobiLink server to determine the SID and SERIAL# values of the current session

Oracle 11g

Driver

iAnywhere Solutions 12 - Oracle ODBC Driver

Version

12.00.00

Status

Recommended for use with MobiLink

Client s/w

Oracle 11g client

OS

Windows

XP, Vista, 7, Server 2003, Server 2008

Linux

Redhat Enterprise Linux 4, 5
SuSE Enterprise Linux 10, 11

Solaris

9, 10

AIX

5.3, 6.1

Pros

All tests passed

Cons

None

Notes

  1. Before using this driver, you must install Oracle 11g client. To load this driver, you must set Oracle client libraries in your LD_LIBRARY_PATH (Linux, Solaris) or LIBPATH (AIX).
  2. TNS server name on Windows DSN setting and ServerName in Linux and Unix .odbc.ini are Oracle TNS service name defined in $ORACLE_HOME/network/admin/tnsnames.ora
  3. The default setting of "Procedure return results or uses VARRAY parameters" on Windows or ProcResults on Linux and Unix is 'No'.
  4. If you are using a stored procedure that returns a REF CURSOR, the REF CURSOR parameter (either OUT or IN OUT) must be defined as the last parameter in the parameter list of the stored procedure.

Permission requirements

  1. Permission to EXECUTE on SYS.DBMS_UTILITY - required by MobiLink to determine if the database is running on an Oracle RAC
  2. Permission to SELECT from SYS.GV_$TRANSACTION - required by MobiLink server to do TIMESTAMP based downloads
  3. Permission to SELECT from SYS.V_$SESSION - required by MobiLink server to determine the SID and SERIAL# values of the current session

Microsoft SQL Server 2005 and 2008

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!

Microsoft SQL Server 2005

Driver

Microsoft SQL Native Client ODBC Driver

Version

2005.90.xxxx.00

Status

Recommended for use with MobiLink

Client s/w

None

OS

XP, Vista, 7, Server 2003, Server 2008

Pros

All tests passed

Cons

None

Notes

Be sure to put "SET NOCOUNT ON" at the start of all stored procedures and batches that are executed via ODBC.

Permission requirements

  1. Permission to VIEW SERVER STATE
  2. Permission to SELECT from SYS.DATABASES

Microsoft SQL Server 2008

Driver

Microsoft SQL Native Client ODBC Driver

Version

2007.100.xxxx.xx

Status

Recommended for use with MobiLink

Client s/w

None

OS

XP, Vista, 7, Server 2003, Server 2008 and 2008 R2

Pros

All tests passed

Cons

None

Notes

Be sure to put "SET NOCOUNT ON" at the start of all stored procedures and batches that are executed via ODBC.

Permission requirements

  1. Permission to VIEW SERVER STATE
  2. Permission to SELECT from SYS.DATABASES

IBM DB2 9.5 and 9.7

Important:

  • MobiLink only supports the DB2 on Windows. DB2 AS/400 is not supported.
  • Any DB2 Java stored procedures used in MobiLink synchronization should always immediately set autocommit to OFF, for example by using getConnection.setAutoCommit(false).

IBM DB2 9.5

Driver

IBM DB2 ODBC Driver

Version

9.05.401.90 (Win32 and 64) These drivers can be retrieved from DB2 FixPack 4a. 09.05.0004 (UNIX)

Status

Recommended for use with MobiLink

Client s/w

DB2 UDB Client 9.5

OS

Windows

XP, Vista, 7, Server 2003, Server 2008

Linux

Redhat Enterprise Linux 4, 5
SuSE Enterprise Linux 10, 11
Ubuntu 8.0.4

Pros

All tests passed

Cons

None

Notes

  1. For OS support, please refer to http://www-306.ibm.com/software/data/db2/9/sysreqs.html ( This link may be changed in the future by IBM).
  2. On Solaris, the IBM DB2 ODBC drivers may require certain levels of OS patches, please check the IBM Web site for more details.
  3. On Linux, Solaris and AIX, you need to source db2profile to export DB2 environment variables. db2profile is in your DB2 install home directory.
  4. Two database configuration parameters LOCKLIST and APPLHEAPSZ need to be changed for high load testing. Set LOCKLIST to 500 and APPLHEAPSZ to 1024.
  5. QAnywhere does not support consolidated databases running on a DB2 mainframe.

IBM DB2 9.7

Driver

IBM DB2 ODBC Driver

Version

9.07.00.441 (Win32 and 64) These drivers can be retrieved from DB2 9.7 GA.

Status

Recommended for use with MobiLink

Client s/w

DB2 UDB Client 9.7

OS

Windows

XP, Vista, 7, Server 2003, Server 2008 and 2008 R2

Linux

Redhat Enterprise Linux 5
SuSE Enterprise Linux 10, 11
Ubuntu 8.0.4

Pros

All tests passed

Cons

None

Notes

  1. The ODBC driver version must be 9.0.7.xxxx. Older versions of the IBM DB2 ODBC driver should not be use for the MobiLink server to talk to a DB2 9.7 database, because it can cause data inconsistency.
  2. If you are using timestamp-based download and if any of the download_cursor and/or download_delete_cursor scripts are implemented in stored procedure calls, either the DB2 consolidated database server must run with the configuration parameter, ConcurrentAccessResolution being set to 2 (Wait for outcome), or executing the following SQL command

    CALL SET_ROUTINE_OPTS(GET_ROUTINE_OPTS() || 'CONCURRENTACCESSRESOLUTION WAIT FOR OUTCOME'

    before creating the stored procedures for download_cursor and/or download_delete_cursor scripts is required. Then the DB2 database server will block the download cursors if there are any uncommitted operations that have modified the download cursor results. Without doing so, new data may not be downloaded to the remote databases and data inconsistency may occur.
  3. When Oracle compatibility features are enabled, DB2 supports some Oracle data types, such as, DATE, VARCHAR2, and NUMBER. These data types will behave the same as those in an Oracle database. For detailed data type mapping of these compatible data types between DB2 and SQL Anywhere/UltraLite, please see the MobiLink reference chapter in the MobiLink Server Administration manual.
  4. For OS support, please refer to http://www-306.ibm.com/software/data/db2/9/sysreqs.html ( This link may be changed in the future by IBM).
  5. On Solaris, the IBM DB2 ODBC drivers may require certain levels of OS patches, please check the IBM Web site for more details.
  6. On Linux, Solaris and AIX, you need to source db2profile to export DB2 environment variables. db2profile is in your DB2 install home directory.
  7. Two database configuration parameters LOCKLIST and APPLHEAPSZ need to be changed for high load testing. Set LOCKLIST to 500 and APPLHEAPSZ to 1024.
  8. QAnywhere does not support consolidated databases running on a DB2 mainframe.

MySQL 5.1.3

Driver

MySQL ODBC driver

Version

5.01.06.00 (Win32 and 64) and 05.01.0006 (Linux32 and 64).

Status

Recommended for use with MobiLink

Client s/w

None

OS

Windows

XP, Vista, 7, Server 2003, Server 2008

Linux

Redhat Enterprise Linux 4, 5
SuSE 10, SuSE Enterprise Linux 10, 11

Pros

All tests passed

Cons

None

Notes

  1. Storage Engine:

    MySQL offers a set of storage engines in a MySQL server and some storage engines are ACID compliant and some are not. The MobiLink server requires an ACID compliant storage engine, such as InnoDB, Falcon. Please make sure the default storage engine is ACID compliant. If the default storage engine is not ACID compliant, please make sure all the MobiLink server system tables are created using an ACID compliant storage engine.

  2. Stored Procedures:

    The MySQL ODBC driver does not support inout or out parameters for stored procedure calls. Therefore, all procedures that need inout parameters must be written as functions that can return an out value.

    The MobiLink server events that require inout parameters, such as authenticate_user, handle_error, modify_user must be implemented as functions and run using a select statement, like
      select function_name( ?, ?, ?, ... )
    and not { call function_name( ?, ?, ?, ... ) }. Then the MobiLink server will catch the return value properly.

    If the MobiLink server scripts use named parameters, the parameters will not be changed after execution of the script. User-defined named parameters, e.g. {ml u.my_var} are thus not supported.

  3. Cursor Scripts:

    The upload_fetch, download_cursor, or download_delete_cursor event may be written as a select statement and the MobiLink server uses the read-committed isolation level to execute the select statement. However, with the read-committed isolation, the MySQL ODBC driver may allow the MobiLink server to read uncommitted operations (inserts, updates and deletes). Then with this behavior, synchronization data would not be consistent between the consolidated and remote databases. This problem has been reported to MySQL.

    To work around this problem, all the select statements for the upload_fetch, download_cursor, and download_delete_cursor events must contain a "lock in share mode" clause. For instance, a download_cursor script should be written as
    select col1, col2, ..., coln from a_sync_table where last_modified > ? ... lock in share mode
    if it is a select statement. With the "lock in share mode" clause, the select statement will be blocked by any uncommitted operations made by the other connections on the same table and then upload updates and download data will be consistent in the consolidated and remote databases.

  4. Linux Platforms:

    There are two ways to specify an ODBC configuration file on Linux:

    a) Place the file ODBC.INI (the file name must be in upper case) or symbolic link, ODBC.INI in the home directory of the current user;
    b) Create an environment variable, ODBCINI and setup this environment variable by pointing it to the real ODBC configuration file.

    A sample DSN for MySQL on UNIX can be
      [a_mysql_dsn]
      Driver=full_path/libmyodbc5.so
      server=host_name
      uid=user_name
      pwd=user_password
      database=database_name

  5. QAnywhere Support:

    QAnywhere does not support consolidated databases running on MySQL servers.


 

Related Links

DOCUMENT ATTRIBUTES
Last Revised: Aug 16, 2011
Product: SQL Anywhere
Technical Topics: MobiLink
  
Business or Technical: Technical
Content Id: 1080709
Infotype: Technote
 
 
 

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