Wednesday, November 24, 2010

Database Alias and DB2 ODBC Drivers

One of my recent project required to use ODBC to access DB2 databases located on remote VMWare LabManager images. I am using a Windows PC (Vista) laptop to develop and test my project (LotusScript Data Object code) - a Lotus Notes/Domino DB2 integration using ODBC. The first step for me was to install the ODBC DB2 drivers since I did not have DB2 installed on my laptop.

Several installations options were offered to me for DB2 9.7:
The installation of  the IBM Data Server Runtime Client is very fast and straightforward. It installs the ODBC/CLI drivers and a small set of useful command line setup tools:

After this, we can create the Database Aliases using the Windows ODBC Data Source Administrator.
When you look at the Drivers tab, you should now see your DB2 ODBC drivers

To add a DB2 Data Source Name (DSN):
  • select User or System and click on the Add... button.
  • select the DB2 ODBC/CLI driver
  • enter a Data source name and add an Alias if needed (click on the Add button next to your existing aliases if needed)
  • enter Data Source parameters (Description, user ID, password) - click "Save password" checkbox  to save your login and password locally in your db2cli.ini file.
  • enter your TCP/IP connection (port number is 50000 by default for me for DB2), the host name is the IP address of my DB2 server VMWare image.
  • I did not have to change anything in the defaults of Security options and Advanced Settings.

    From there you are ready to use your ODBC DSN ready to connect to your DB2 Database.

    One issue you will encounter though will be how to delete an existing Database Alias from the DB2 ODBC tab either to modify an existing one or to remove an old one.
    These appear in the drop down of the ODBC IBM DB2 Driver - Add popup window.

    The truth is that even though you are accessing a remote DB2 server machine, these aliases are stored locally on your DB2 installation.
    To remove the DB2 Database Aliases ODBC drivers, just start the IBM DB2 Command Line Processor and use the following command:

    UNCATALOG DATABASE <database_alias>

    In certain cases, you also need to refresh the directory cache. For this, just stop and restart the DB2 Management Service on your local Windows machine.


    Pro Blogger said...

    Ho can this be done on 6b Bit Windows 2008 server ?

    Anonymous said...

    UNCATALOG DATABASE command solves my problems.
    Thanks for sharing.

    Anonymous said...

    Been looking for this for hours, Thanks for your help!


    Anonymous said...

    UNCATALOG DATABASE solved my problems too. I did have to run the DB2 CLP as Admin and had to type db2 (enter) for it to get in db2 mode first.