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.

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.
3. Install and license the SQL Server ODBC driver on the Windows machine where SAP IQ is installed.

   For installation instructions, refer to 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 (in this case, SQL Server) and the ODBC driver that is required to connect to it (in this case, the SQL Server ODBC driver).

   You configure ODBC data sources in ODBC Data Source Administrator, which is included with Windows. There are two versions of ODBC Data Source 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 Data Source Administrator. To start the 64-bit ODBC Data Source Administrator, in the Windows Run dialog box, enter:

   odbcad32.exe

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

5. Set up your SAP IQ environment by setting the environment variables in SAPIQDIR\SYBASE.bat and SAPIQDIR\SYBASE.ENV.
6. 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%

7. Tell SAP IQ where to write log files for the sample database by specifying a log file directory location. For example:

   set IQ16LOGDIR = C:\Temp

8. 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

9. Start the SAP IQ server:

   start_iq @iqdemo.cfg iqdemo.db

10. 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"

11. 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'

12. 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:

    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'

13. Check that you can retrieve your remote SQL Server data.

    select * from Persons;