Connect SAP IQ to SQL Server

SAP IQ (formerly known as SAP Sybase IQ or Sybase IQ) is a high-performance relational database designed specifically for data warehousing. This cross-platform product runs on several popular Unix, Linux, and Windows platforms.

SAP IQ can integrate data from diverse sources, not just IQ databases, but other databases in the Adaptive Server family, as well as non-Sybase databases and flat files.

This blog shows how to access SQL Server data from SAP IQ running on the Windows platform.

SQL Server Steps

  1. In SQL Server Management Studio, create a sample table to hold the data that you will retrieve from SAP IQ. For example: 
    CREATE DATABASE mydb
USE mydb
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO Persons VALUES(1,'Name', 'My', 'MyAddress', 'MyCity')
  2. Download the SQL Server ODBC driver for your Windows platform. (Registration required.)
  3. Install and license the SQL Server ODBC driver on the Windows machine where SAP IQ is installed.

    For installation instructions, see the SQL Server ODBC driver documentation.

  4. In ODBC Data Source Administrator on your SAP IQ machine, configure a System ODBC data source that connects to your SQL Server instance. In the data source, specify the database that holds the data you want to work with in SAP IQ.

    An ODBC data source stores the connection details for the target database (e.g. SQL Server) and the ODBC driver that is required to connect to it (e.g. the SQL Server ODBC driver).

    You configure ODBC data sources in ODBC Administrator, which is included with Windows. There are two versions of ODBC Administrator included with Windows, one is 32-bit and one is 64-bit. As SAP IQ is a 64-bit application, you need to use the 64-bit version of ODBC Administrator. To start the 64-bit ODBC Administrator, in the Windows Run dialog box, type:

    odbcad32.exe

    For instructions on configuring data sources, see the SQL Server ODBC driver documentation.

    5. SAP IQ Steps

    1. Set up your SAP IQ environment by setting the environment variables in SAPIQDIR\SYBASE.bat and SAPIQDIR\SYBASE.ENV.
    2. Add the directory where start_iq is located to your PATH, so that the sample database creation script can run this command. For example: 
      set PATH=C:\SAPIQ\IQ-16_1\Bin64;%PATH%
    3. Tell SAO IQ where to write log files for the sample database by specifying a log file directory location. For example: 
      set IQ16LOGDIR = C:\Temp
    4. Create the sample database by running mkiqdemo.bat. You need to set a database user name and password by including the -dba and -pwd arguments. For example: 
      cd C:\ProgramData\SAPIQ\demo
mkiqdemo.bat -dba myuser -pwd mypassword
    5. Start the SAP IQ server: 
      start_iq @iqdemo.cfg iqdemo.db
    6. Use dbisql, the SAP IQ interactive SQL utility, to connect to the sample database. For example: 
      dbisql -nogui -c "UID=myuser;PWD=mypassword;DATABASE=iqdemo"
    7. Create a remote server that uses the SQL Server ODBC driver data source you created earlier to connect to SQL Server.

      You need to specify your Windows or SQL Server login in remote server connection string. Otherwise SAP IQ passes your sample database user name and password to the SQL Server ODBC driver.

      For example:

      CREATE SERVER easysoft
CLASS 'ODBC'
USING 'DSN=MYDSN;UID=mydomain\mywindowsuser;PWD=mywindowspassword'
    8. Create a local SAP IQ table that enables you to work with your remote SQL Server data. Use a four part name to specify the remote table i.e.: 
      remoteserver.database.schema.object

      For the sample SQL Server table we created earlier, the CREATE EXISTING TABLE statement would be: 

      CREATE EXISTING TABLE Persons(
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),City varchar(255)) AT 'easysoft.mydb.dbo.Persons'
    9. Check that you can retrieve your remote SQL Server data. 
      select * from Persons;