Skip to Content
SAP IQ

SAP IQ: Creating Remote Connection To MySQL

Tags:


On Linux, for creating remote connection to MySQL Server, you will need to install unixodbc driver and MySQL driver

  • Download unixODBC-2.3.2.tar.gz from:

http://www.unixodbc.org/download.html

  • As root gunzip unixODBC-2.3.2.tar.gz
  • Untar unixODBC-2.3.2.tar

        tar -xvf unixODBC-2.3.2.tar

  • It will create unixODBC-2.3.2 directory with all source files inside
  • As root install unixODBC drivers

    sudo ./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable -iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE 

   checking for a BSD-compatible install...
  /usr/bin/install -c

   checking whether build environment is sane...
  yes

   

     checking for a thread-safe mkdir -p...
 
  /bin/mkdir -p

 

  ...

  ...

  ...

  config.status: creating Makefile

    config.status: creating config.h

    config.status: executing depfiles commands

   config.status: executing libtool commands

   [unixODBC-2.3.2]$ sudo make

   make all-recursive

   make[1]: Entering directory
/download/unixODBC-2.3.2'

    Making all in extras

   ...

   ...

   ...

  make[2]: Leaving directory
/download/unixODBC-2.3.2'

  make[1]: Leaving directory
/download/unixODBC-2.3.2'

  [unixODBC-2.3.2]$ sudo make install

   Making install in extras

  make[1]: Entering directory
/download/unixODBC-2.3.2/extras'

   ...

  ...

  ...

   make[2]: Entering directory

/download/unixODBC-2.3.2'

  make[2]: Nothing to be done for
install-exec-am'.

touch /etc/odbcinst.ini

touch /etc/odbc.ini

mkdir -p /etc/ODBCDataSources

/usr/bin/odbc_config --header >
/usr/include/unixodbc_conf.h

   make[2]: Leaving directory
/download/unixODBC-2.3.2'

make[1]: Leaving directory
/download/unixODBC-2.3.2'

  • Download MySQL ODBC Driver and install odbc driver as root:

         MySQL :: Download Connector/ODBC     

  

  • Install MySQL ODBC Driver

         [mysql]# rpm -ivh mysql-connector-odbc-5.3.4-1.el6.x86_64.rpm

      

      Preparing...                ########################################### [100%]

    1:mysql-connector-odbc   ########################################### [100%]

    Success: Usage count is 1

    Success: Usage count is 1

  • Edit /etc/odbc.ini and add MySQL server entry

    

     [mysql]

    Description             = ODBC for MySQL

    Driver          = /usr/lib64/libmyodbc5w.so

    Server  = localhost

    Port    = 3306

    User    = root

    Password        = xxxxxx

    Database        = MySQL

    FileUsage               = 1

    Socket  = /var/lib/mysql/mysql.sock

    Option  = 3

  • Set LD_LIBRARY_PATH environment variable to MySQL libraries in IQ.csh

            setenv LD_LIBRARY_PATH  "/usr/lib64:${LD_LIBRARY_PATH}"

  • set ODBCINI and ODBCHOME environment variables

          setenv ODBCINI /etc/odbc.ini

          setenv ODBCHOME /etc

  • cd $SYBASE/IQ-16_0./lib64 and if libodbc.so link already exists, remove it, to make sure correct driver manager get loaded.

     rm libodbc.so

  • Restart IQ server
  • Test ODBC driver connectivity

   

        [/etc]# /usr/bin/isql -v mysql root sqlpass

       +---------------------------------------+

       | Connected!                            |

       |                                       |

       | sql-statement                         |

       | help [tablename]                      |

       | quit                                  |

       |                                       |

      +---------------------------------------+

     SQL> use MySQL

     SQLRowCount returns 0

     SQL> select * from employee

    +---------------------+---------------------+---------------------+

    | Name                | Dept                | jobTitle            |

    +---------------------+---------------------+---------------------+

   | Fred Flinstone      | Quarry Worker       | Rock Digger         |

   | Wilma Flinstone     | Finance             | Analyst             |

   | Barney Rubble       | Sales               | Neighbor            |

   | Betty Rubble        | IT                  | Neighbor            |

   +---------------------+---------------------+---------------------+

  SQLRowCount returns 4

  4 rows fetched

  SQL> quit

   

  • log in IQ server using dbisql

     

    dbisql -c 'dsn=vicky_iqdemo0830' -nogui

   (DBA)> create server mysql class 'MYSQLODBC' using 'dsn=mysql'

   Execution time: 0.037 seconds


   (DBA)> create externlogin DBA to mysql remote login root identified by sqlpass
   Execution time: 0.031 seconds

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

(DBA)> use MySQL
  0 row(s) affected
  Execution time: 1.422 seconds

(DBA)> select * from employee
  Name                 Dept                 jobTitle
   --------------------------------------------------------------
   Fred Flinstone       Quarry Worker        Rock Digger
   Wilma Flinstone      Finance              Analyst
   Barney Rubble        Sales                Neighbor
   Betty Rubble         IT                   Neighbor

  (4 rows)

  Execution time: 0.044 seconds

  ( DBA)> forward to
   Execution time: 0.004 seconds

  • Create existing table

  

   (DBA)> create existing table DBA.employee at 'mysql.mysql.root.employee'

   Execution time: 0.882 seconds

  (DBA)> select * from DBA.employee
   Name                 Dept                 jobTitle
   --------------------------------------------------------------
  Fred Flinstone       Quarry Worker        Rock Digger
  Wilma Flinstone      Finance              Analyst
  Barney Rubble        Sales                Neighbor
  Betty Rubble         IT                   Neighbor

(4 rows)

Execution time: 0.066 seconds

(DBA)> exit

No comments