SQLines Data - Configure and Troubleshoot Connection to IBM DB2

If you are going to connect to a localhost IBM DB2 database you do not need to setup any additional libraries to use SQLines Data tool as they are already supplied with IBM DB2 server.

If you need to connect to a remote IBM DB2 database, you need to have DB2 Runtime Client or IBM Data Server Driver Package installed in your system.

First check if you already have the IBM DB2 library:

• Linux:
  • libdb2.so
  • For example, at:
  • /opt/ibm/db2/V11.1/lib64
  • /opt/ibm/db2/V11.1/lib32
  • /opt/ibm/db2/V10.5/lib
• Windows:
  • db2cli64.dll (Windows 64-bit)
  • db2cli.dll
  • For example, at (Drive letter can be different):
  • C:\Program Files\IBM\SQLLIB\BIN

Download the driver package from IBM web site, for example ibm_data_server_driver_package_linuxx64_v11.1.tar.gz

Unzip the file:

  tar -zxvf ibm_data_server_driver_package_linuxx64_v11.1.tar.gz

Run the installation script:

  cd dsdriver
 
  # it uses #! /bin/ksh -p, so specify bash explicitly if ksh is not available
  bash ./installDSDriver

Before running sqldata command import definitions from the db2profile script if you use DB2 Data Server Driver Package:

  . db2profile

Or add the DB2 library location to LD_LIBRARY_PATH:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<db2_client_lib>

For example:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/ibm/db2/V11.1/lib64

If using DB2 Data Server Driver Package, you can add a new data source as in the following example:

  db2cli writecfg add -dsn sample -database SAMPLE -host 10.0.2.2 -port 50000

Or create <db2_driver_dir>/dsdriver/cfg/db2dsdriver.cfg configuration file manually, for example:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<configuration>
  <dsncollection>
    <dsn alias="sample" host="10.0.2.2" name="SAMPLE" port="50000"/>
  </dsncollection>
 
  <databases>
    <database host="10.0.2.2" name="SAMPLE" port="50000"/>
  </databases>
 
</configuration>

Then you are ready to use the DB2 connection in ./sqldata command, for example:

  ./sqldata -sd=db2,user/pwd@sample -t=sales ...

If you get “Error allocating environment handle” error during connection to IBM DB on Linux host due to lack of permissions. Try to run ./sqldata tool under sudo su - db2inst or other appropriate user from DB2ADMIN group.

• If you get the error “Loading IBM DB2 CLI db2cli64.dll: The specified module cannot be loaded”:

Go to My Computer → Properties → Advanced System Settings → Environment Variables and add IBM DB2 libraries location to the system PATH variable, for example: C:\Program Files\IBM\SQLLIB\BIN

• If you get the error “Allocating environment handle”:

Run SQLines Data tool as Administrator or db2admin user.

• If you get the error “Environment variable DB2INSTANCE is not defined or is invalid”:

Go to My Computer → Properties → Advanced System Settings → Environment Variables and add DB2INSTANCE variable.

To define the instance name you can run db2ilist command as follows:

  C:\Program Files\IBM\SQLLIB\BIN>db2ilist
  DB2