Skip to end of metadata
Go to start of metadata

Purpose

Steps that provide instructions to move data from SAP HANA to SAP IQ

Overview

This document provides detail steps to export data from SAP HANA into SAP IQ  

Steps:

1. Setting environment variables by editing $SYBASE/IQ-16_0/IQ-16_0.csh file. Add HANA_HOME, ODBCHOME and ODBCINI environment variables to the file.

Example:

                  setenv HANA_HOME /hana/sap/hdbclient/libodbcHDB.so

                  setenv ODBCHOME /hana/rel16_iq_sp03/

                  setenv ODBCINI /hana/rel16_iq_sp03/odbc.ini

2. Add HANA and IQ connection profiles to odbc.ini

Example:

[iq1603]
DRIVER=/hana/rel16_iq_sp03/IQ-16_0/lib64/libdbodbc16.so
EngineName=iq1603
CommLinks=tcpip(host=myhost;port=9286)
Userid=DBA
Password=sql
AutoStop=no
DatabaseName=iqdb

[SB1]
Driver=/hana/sap/hdbclient//libodbcHDB.so
ServerNode=myhost:30315


3. Source $SYBASE/IQ-16_0/IQ-16_0.csh and restart IQ server. HANA_HOME and ODBCINI environment variables need to be set when starting IQ server.

4. Login into IQ server and create HANA remote server.

Example:  

HANA remote server name: SB1

HANA host:  "myhost"  listening on port 30315:

dbisql -c 'dsn=iq1603' -nogui

(DBA)> create server SB1 class 'hanaodbc' using 'dsn=SB1'            
Execution time: 0.067 seconds

5. Login into IQ server to create externlogin that map to the local user.

Example:

DBA)> create externlogin DBA to SB1 remote login SYSTEM identified by 'Saphanasql1'

Execution time: 2.111 seconds

6. Login into IQ server to confirm connection to HANA instance by running sp_remote_tables.

Example:

(DBA)>sp_remote_tables SB1

(NULL)
                                                 _SYS_STATISTICS

                  HOST_ONE_DAY_FILE_COUNT

 

(NULL)
                                                 _SYS_STATISTICS

                  HOST_RECORD_LOCKS

(NULL)
                                                 _SYS_STATISTICS

                  STATISTICS_LAST_CHECKS


(1090 rows)

        

 

7. Create schema for tables in HANA instance.

Note: Step 7 is optional,  when schema already existed in HANA

Example: HANA instance name SB1

(DBA)> forward to SB1
0 row(s) affected
Execution time: 0 seconds

(DBA)> create schema hanaiq
0 row(s) affected
Execution time: 0.23 seconds

(DBA)> create table hanaiq.t1(col1 int)
0 row(s) affected
Execution time: 1.321 seconds

(DBA)> insert into hanaiq.t1 values(1)

0 row(s) inserted

Execution time: 0.07 seconds

(DBA)> commit

Execution time: 0.031 seconds

(DBA)> select * from hanaiq.t1
       COL1
-----------
          1

(1 rows)
Execution time: 0.037 seconds

(DBA)> forward to
Execution time: 0.001 seconds

8. Create a proxy table in IQ server

Example:

The following command create a proxy table named TAB1 for the remote table hanaiq.t1 at the remote HANA instance SB1

(DBA)> create existing table DBA.TAB1 (COL1 int) at 'SB1.HANAIQ..T1'
Execution time: 0.441 seconds

(DBA)> select count(*) from DBA.TAB1
    count()
-----------
          1

(1 rows)

Execution time: 0.063 seconds