Sybase IQ
12.x 15.0/1 15.2 15.3
Last updated: 09 December 2011
IQ-to-ASE connections with ODBC on Unix/Linux
 
Categories

For a full IQ quickref guide, covering IQ 15.4, go to www.sypron.nl/iqqr.


Introduction - why bother about ODBC?
Sybase IQ can create CIS connections to ASE, for example to access ASE data through an 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 in SQL Anywhere 12, and in IQ 15.4 (which uses SQL Anywhere 12), Sybase has announced that JDBC support will be removed in future IQ releases.

These are all good reasons to use ODBC for IQ-to-ASE connectivity. With IQ running on Windows, using ODBC connectivity is easy (just use the Windows ODBC administration tool).
However, with IQ on Unix/Linux, as is the case for most IQ customers, setting up ODBC connections to ASE can be tricky, to say the least. What doesn't help is that the IQ documentation isn't exactly a shining example of clarity on this point, either.
Below is what I found out the hard way about setting up IQ-to-ASE ODBC connectivity on Unix/Linux; I thought I'd share what I found.

IQ versions covered
The information about IQ-to-ASE ODBC connections below applies to IQ 15.2 and later; I'm getting errors in 15.1 and before (that it works in 15.2+ is probably related to the CIS improvements that were made in IQ 15.2).
As for OpenClient, I've used OCS 15.5, though it should also work for OpenClient 15.0 (wasn't tested). If you run into problems, download and install SDK 15.5 ESD#8 or later; some driver bugs were fixed in that release (go to www.sybase.com -> Support -> EBFs/Maintenance -> Software Developer Kit).

The JDBC connection info below works at least on IQ 12.7 and later.

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

create server MY_ASE_ODBC class 'aseodbc' using '...'

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 atributes.
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 9876 on host 'bigbox'.
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.xxx (the xxx depends on the O/S; on Linux it's .so, this is used for the examples below).
    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) .

  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 a word) with a simple select @@version or iqsrv15 -v (or asiqsrv12 -v if you're still on IQ 12.x).
    For the ODBC driver, the pathname tells you whether it's 32- or 64-bit: the location is $SYBASE/DataAccess/ODBC/lib/libsybdrvodb.so or $SYBASE/DataAccess64/ODBC/lib/libsybdrvodb.so, respectively (no points if you can spot the difference!).

  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, e.g. $LIBPATH on AIX; $SHLIB_PATH on HP-UX). This means you need to run the following before starting the IQ server:
    export LD_LIBRARY_PATH=
    /opt/sdk155/DataAccess64/ODBC/lib:$LD_LIBRARY_PATH

    (here, /opt/sdk155 is used as an example top-level $SYBASE install directory)
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=9876;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 MyS3cret;


    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=MyS3cret;...', 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;


IQ-to-ASE ODBC connection, with DSN
The second option is to define an ODBC connection from IQ to ASE with an ODBC Data Source Name (DSN). This is more in line with the philosphy of ODBC, whereby the DSN is defined in a central place and holds the ODBC connection attributes (though I don't mean to say I prefer it over the other method described above; it's just an alternative).

The crucial issue here is around the file location where the ODBC data source names (DSNs) are stored.
By convention, on Unix/Linux this is in $HOME/.odbc.ini by default (though environment variable settings $ODBCHOME and $ODBCINI can point to an alternative directory or file location, respectively). The iqdsn and dbping tools, as wel as connection classes such as 'asaodbc' do indeed follow this convention.
Unfortunately, the 'aseodbc' class -which is what we're discussing on this page- does not: for some reason it looks for the DSN in the create server statement in $SYBASE/odbc.ini (note the missing leading '.') or odbc.ini in the directory where IQ was started. 'aseodbc' also ignores the $ODBCHOME or $ODBCINI environment variables.
This is a nuisance (and not really documented as I found out the hard way) and the best workaround is to create a symlink in $SYBASE pointing to the $HOME/.odbc.ini file. This way, we can keep all DSNs in the same file.
IQ does not have to be stopped for this.
  • -- verify the location of the .odbc.ini file, at the Unix/Linux command line:
    % iqdsn -f
  • -- create a link named odbc.ini in $SYBASE pointing to the .odbc.ini file found above (assuming $HOME/odbc.ini), at the Unix/Linux command line:
    % ln -s $HOME/.odbc.ini $SYBASE/odbc.ini
  • -- make sure the $SYBASE/interfaces exists in the IQ environment ($SYBASE must point to the top level of the IQ installation directory), or create a link named interfaces in $SYBASE to point to an interfaces file already existing somewhere else (all this at the Unix/Linux command line):
    % ln -s /opt/dba/my_shared_interfaces_file $SYBASE/interfaces
  • -- create the DSN, at the Unix/Linux command line:
    % iqdsn -w MY_ASE_DSN -c "ServerName=MY_ASE;Database=my_db;PacketSize=4096;Driver=libsybdrvodb.so"

    This adds the following lines to the $HOME/odbc.ini file:
    [MY_ASE_DSN]
    ServerName=MY_ASE
    Database=my_db
    PacketSize=4096
    Driver=libsybdrvodb.so

    Here, MY_ASE must exist in the $SYBASE/interfaces file as visible to the IQ server: the ODBC driver will use it to look up the ASE server's host+port.
    We're also configuring -optionally- a network packet size of 4096 bytes; the ASE server must be able to handle this size, or the connection will fail.
  • -- in IQ, create a remote server mapping through ODBC, specifying the DSN just added to the $HOME/odbc.ini file:
    create server MY_ASE_ODBC_2 class 'aseodbc' using 'MY_ASE_DSN';
  • -- in IQ, create an 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_2
    remote login sa identified by MyS3cret;
  • -- in IQ, test the connection; if there is any error in the setup, error messages will be raised now:
    forward to MY_ASE_ODBC_2 { select @@servername, db_name(), @@version };
  • -- if the connection works, create the proxy table:
    create existing table ase_proxy_tab_2 at 'MY_ASE_ODBC_2.my_db.dbo.my_tab';

    select * from ase_proxy_tab_2;

Note that the IQ dbping utility cannot test the connection to a DSN created as above.

Error messages from IQ-to-ASE ODBC connections
These are some error messages you may run into when setting up IQ-to-ASE ODBC connections with the 'aseodbc' connection class. Please note that this list is not complete. Also note that when creating IQ-to-IQ connections, these error messages may also be raised but have a different meaning (which is not covered here).

These errors occur at the moment when IQ tries to establish an actual connection, not when executing create server or create externlogin commands.

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver][SQL Anywhere]Specified database not found
Meaning: the ODBC driver library was not found, either because its name was not specified in the 'using' clause, or because the pathname to the directory with the ODBC driver is not in the $LD_LIBRARY_PATH (or its equivalent on other platforms, see above).
To verify the setting of enviroment variables for the IQ server, start IQ with the -ze startup switch; this will log the settings of all environment variables to the .srvlog file.

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver][SQL Anywhere]Database server not found
Meaning: The pathname to the directory with the ODBC driver is not in the $LD_LIBRARY_PATH (or its equivalent on other platforms, see above).

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver Manager] Not supported until driver loaded
Meaning: the Driver= clause is not specified in the 'using' clause, or in the DSN

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver]Null ini file or ini file not found in $SYBASE or current directory
Meaning: either the $SYBASE/odbc.ini file (or a link with that name) was not found, or the $SYBASE/interfaces file (or a link with that name) was not found (see above on this page on how to create such links).

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver Manager] Data source not found and no default driver specified
Meaning: for an ODBC connection using a DSN, the specified data source name was not found in the $SYBASE/odbc.ini file.

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver]Cannot locate reference to the DB server specified in DSURL.
Meaning: for an ODBC connection using a DSN, the ASE servername specified on the line ServerName= in the DSN entry in the $SYBASE/odbc.ini file could not be found in $SYBASE/interfaces file.

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver]Client unable to establish a connection
Meaning: the remote ASE server is not running.

SQL Anywhere Error -656: Unable to connect to server '...': [Sybase][ODBC Driver][Adaptive Server Enterprise]Login failed.
Meaning: IQ is reaching the remote ASE server, but the connection fails due to an invalid login or password.

For reference: IQ-to-ASE connection with JDBC
For reference, here's how you'd set up a JDBC connection to ASE in Sybase IQ. This is much simpler -as a minimum you only need to specify host+port- and does not have all the setup requirements discussed for ODBC:
  • -- create remote server mapping through JDBC
    create server MY_ASE_JDBC class 'asejdbc' using 'bigbox:9876/my_db?packetsize=4096';
    (NB: the /my_db and ?packetsize=4096 parts are optional)
  • -- 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_JDBC
    remote login sa identified by MyS3cret;
  • -- test the connection
    forward to MY_ASE_JDBC { select @@servername, db_name(), @@version };
  • -- if the connection works, create the proxy table
    create existing table ase_proxy_tab_3
    at 'MY_ASE_JDBC.my_db.dbo.my_tab';

    select * from ase_proxy_tab_3;


An error message from IQ-to-ASE JDBC connections
Here's another thing I found out the hard way, this time for JDBC connections from IQ to ASE.
It could be that the IQ-to-ASE JDBC connection above works fine with forward to ..., but creating the proxy table fails with the following error message:
SQL Anywhere Error -667: Could not access column information for the table 'dbname.owner.table'

In this case, the problem is likely that IQ and ASE are using different versions of jConnect, causing problems when IQ tries to execute the JDBC metadata procedures in ASE to retrieve the table's schema.
This has happened to me between IQ 15.2 and ASE 15.5 where IQ uses jConnect 6 but ASE uses jConnect 7.
You can tell which version of the jConnect procs is installed by ASE by running sp_version in ASE. If this indeed shows jConnect 7.0, then you can fix the issue by running the script $SYBASE/jConnect-6_0/sp/sql_server15.0.sql .
Of course, after running the regular installmaster script (for example, after installing a new ESD) the jConnect 7.0 procedures are re-installed, so you may need to re-run the above script if you need to create more proxy tables.

Instead of installing the jConnect 6 procs, you can also specify the column names and datatypes as part of the create existing table statement, but they must exactly match those of the remote ASE table.


 
 This document is located at www.sypron.nl/iq_ase_odbc.html