August 09, 2012

These Examples assume your using Linux (2.6.32-220.el6.x86_64 GNU/Linux) and Sybase IQ 15.4.

Sybase IQ can create CIS connections to ASE, for example to access ASE data through an Sybase IQ-to-ASE proxy table.
This can be done in two ways: through JDBC and ODBC. JDBC is easier to set up, but ODBC tends to be significantly faster. In addition, the JDBC connectivity class has been deprecated (If you try to use CLASS ‘asejdbc’, You will receive a message box error via ISQL “asejdbc has been deprecated”)   in Sybase IQ 15.4 on Linux.

With Sybase IQ running on Linux, as is this case, setting up ODBC connections to Sybase ASE can be tricky, to say the least. What it doesn’t help is that the IQ documentation isn’t exactly a concise example of clarity on this point, either. Below is what I found out the hard way about setting up Sybase IQ-to-ASE ODBC connectivity on Linux

IQ versions covered
The information about Sybase IQ-to-ASE ODBC connections below applies to Sybase IQ 15.4. As for OpenClient, I’ve used OCS 15.4, though it should also work for OpenClient 15.0 + (wasn’t tested). If you run into problems, (go to www.sybase.com -> Support -> EBFs/Maintenance -> Software Developer Kit).

IQ-to-ASE ODBC connections – basic syntax
To set up an ODBC connection from Sybase IQ to Sybase ASE, you need to use the following Sybase IQ statement:

create server MY_ASE_ODBC class 'aseodbc' using 'MY_ASE'

Precisely what to specify in the USING ‘…’ clause depends on which of these two variations is chosen:

  1. Specify the ODBC connection attributes directly in the IQ create server statement;
  2. Use an ODBC Data Source Name (DSN), which is defined outside IQ and holds the ODBC connection attributes.

We’ll look at both of these.

In the examples below, I’m assuming we’re on Linux and we’re trying to connect from IQ to an ASE server named ‘MY_ASE’ running on port 5000 on host ‘ASE_15’.
An IQ proxy table is then created to an ASE table named my_tab (owned by dbo) in databases my_db.

The IQ-to-ASE ODBC driver – three vital setup steps
But first, and before going into either of the ODBC variations above, you need to get the following three things right:

  1. You need to have the correct ODBC driver library installed: the filename of the driver lib is libsybdrvodb.so.
    However, this driver may not be bundled with your IQ installation. If it is not, the driver is included with ASE as well as with the OpenClient connectivity libraries (downloadable from www.sybase.com -> Support -> EBFs/Maintenance -> Software Developer Kit).
    You must make sure you have this driver installed. It is OK, and perhaps even best, to install it in its own $SYBASE directory (it does not need to be in the IQ installation directory tree) . I copied the file and pasted it in several directories so I knew the ENV path would recognize it.
  2. The second important thing is that you need the 64-bit version of the ODBC driver if your IQ server is 64-bit, and the 32-bit driver if you’re still running 32-bit IQ.
    It is vital to get this right. On the IQ side, you can find the IQ server’s bitness (if that’s considered a word) with a simpleselect @@version or iqsrv15 -v Sybase
    IQ/15.4.0.6567/111107/P/GA/Enterprise Linux64 - x86_64 - 2.6.9-67.0.4.ELsmp/64bit/2011-11-07 00:40:24
    For the ODBC driver, the pathname tells you whether it’s 32- or 64-bit: the location is usually $SYBASE/DataAccess/ODBC/lib/libsybdrvodb.so or /opt/sybase/ASE_15_5/DataAccess64/ODBC/lib
  3. The third essential requirement is that the pathname of the directory containing the ODBC driver file is in the IQ server’s $LD_LIBRARY_PATH environment variable (or equivalent on other platforms). This means you need to run the following before starting the IQ server:
    export LD_LIBRARY_PATH=
    /opt/sybase/ASE_15_5/DataAccess64/ODBC/lib:$LD_LIBRARY_PATH

With these points covered, you can now start your IQ server…

IQ-to-ASE ODBC connection, without DSN
Now, let’s look at defining an ODBC connection from IQ to ASE with declaring the ODBC attributes directly in the IQ create server statement (i.e. without using an ODBC DSN).

  • — create remote server mapping through ODBC
    create server MY_ASE_ODBC_1 class 'aseodbc'
    using ‘Driver=libsybdrvodb.so;Server=bigbox;Port=5000;Database=my_db;PacketSize=16384;EnableServerPacketSize=0’Important:The Driver= clause must specify the ODBC driver’s filename (libsybdrvodb.so). It may also specify the full pathname to the ODBC driver file, but the driver’s directory pathname must still be included in the IQ server’s $LD_LIBRARY_PATH.Note that this also configures -optionally- a network packet size of 16KB; the ASE server must be able to handle this size, or the connection will fail (don’t ask me why EnableServerPacketSize=0 is needed — the PacketSize keyword was ignored without it).
    Also note that the contents of the using ‘…’ clause are not checked at this stage; any errors will only be raised once IQ tries to make an actual connection.
  • — create external login for the IQ ‘dba’ user
    — let’s assume it maps to the ASE ‘sa’ user; adjust as needed
    CREATE EXTERNLOGIN dba to MY_ASE_ODBC_1
    REMOTE  login sa IDENTIFIED BY sql;    Important: You must create an external login: even though it looks as if you can specify the remote login name+password directly with using ‘…;UID=sa;Password=sql; the specified password is ignored (for some reason, with using ‘…;UserID=sa;…’, the specified username is ignored as well).
  • — test the connection; if there is any error in the setup, error messages will be raised now:
    forward to MY_ASE_ODBC_1 { select @@servername, db_name(), @@version };
  • — if the connection works, create the proxy table:
    create existing table ase_proxy_tab at 'MY_ASE_ODBC_1.my_db.dbo.my_tab';select * from ase_proxy_tab;