IBM Support

How to troubleshoot ODBC connectivity errors in DataStage

Question & Answer


Question

How to troubleshoot ODBC connectivity errors in DataStage

Cause

Connectivity errors with ODBC in DataStage are likely caused by one of the following:

- Configuration issues in dsenv that prevent access to database libraries
- Configuration issues in the .odbc.ini file that prevent DataStage from connecting to a database
- Lack of permissions for the user connecting

Answer

To troubleshoot ODBC connectivity problems please follow these steps in sequential order.


1) Test access to database libraries and odbc drivers outside of DataStage

The first step to troubleshoot an ODBC connectivity error is to validate that DataStage can successfully load the libraries that the ODBC drivers need to connect to a database. The access to these libraries is configured in the file $DSHOME/dsenv which DataStage sources every time the Engine is started.

Note: Please be aware that changes in this file are not reflected in DataStage until the Engine is restarted.

To test access to these libraries

a) Open a telnet session with the machine where the DataStage Engine is running and log as the same user that is trying to connect in DataStage. Then source the dsenv file by going to $DSHOME and executing:

. ./dsenv

this will set the environment variable ODBCINI that points to the .odbc.ini containing the Data Source Names (DSN). This also will set the environment variable PATH and the library path (the name of this variable depends on the platform, LIBPATH for AIX and LD_LIBRARY_PATH for Linux, Solaris, and HP-UX).

b) Then you need to identify the driver you are using in your DSN. This can be obtained by inspecting the .odbc.ini file. Each DSN has a line that starts with "Driver=" and then the full path to the odbc driver used. For example in this DSN:

[iadb]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so
Description=DataDirect DB2 Wire Protocol Driver
Database=iadb
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=localhost
IsolationLevel=CURSOR_STABILITY
Package=DB2 package name
TcpPort=50000
WithHold=1

the driver is

/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so

c) Once you have identified this driver you can use the program ddtestlib to confirm that you have acess to the libraries that the driver needs. This program is located under $DSHOME/../branded_odbc/bin, for example to test the driver of the example, you can run

cd $DSHOME/../branded_odbc/bin
./ddtestlib /opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so

if the libraries are not configured properly you will get one or more lines saying "Cannot load ...". This is just an example:

exec(): 0509-036 Cannot load program /opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so because of the following errors:
0509-022 Cannot load module /opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so.
0509-150 Dependent module libVMicu22.a(VMicu22.so) could not be loaded.
0509-022 Cannot load module libVMicu22.a(VMicu22.so).
0509-026 System error: A file or directory in the path name does not exist.
0509-022 Cannot load module /opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so.
0509-150 Dependent module /opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so could not be loaded.

if you get a result like this, then the odbc driver is not accessing the libraries it needs. To fix this problem you need to edit the file $DSHOME/dsenv and make sure that all the environment variables for this driver are properly set and that this user has proper access to these files. Note: Some databases may require the user to be part of a particular group for the user to access the libraries. Check with your DBA if necessary.

If you change the dsenv you should source it and run this test again until ddtestlib works successfully. A successful execution ddtestlib will show the version of the driver. This is an example:

Load of /opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so successful, qehandle is 0x3
File version: 05.20.0057 (b0034, u0022)

Note: When you source the dsenv file some variables like PATH and LIBPATH append new values to the existing value of the variables as opposed to replacing the entire value. As result of this, if you source dsenv multiple times in the same session you can end up with long values for these variables and this can cause some library conflicts. This won't affect DataStage but can alter the results of your test, so if you modify the settings for these variables in dsenv, it is a good idea to start a brand new telnet session to make sure you are not inheriting unneeded values in these variables.

2) Test connection outside of DataStage

After you have confirmed that ddtestlib is working successfully following the instructions in previous step then you can continue to check if the DSN is properly configured. To do this you can use the program "example". The location of this program varies depending what version of DataStage you are using. It can be found under either $DSHOME/../branded_odbc/example or $DSHOME/../branded_odbc/samples/example.

Note: You might need to change the permissions of this file to make it executable.

Assuming that you have source dsenv already then run:

cd $DSHOME/../branded_odbc/samples/example
./example

This program will prompt you for a DSN, a user and a password. The DSN can be obtained from the file $DSHOME/.odbc.ini. Continuing with the previous example, the DSN to enter would be "iadb". Then it will ask for a database user and password. If example establishes a successful connection it will show:

Enter SQL statements (Press ENTER to QUIT)
SQL>

If any other message is shown here and the prompt SQL> never appears then this indicates that there is a problem in the DSN configuration. Review the documentation about this driver. A guide with all parameters needed for each driver can be found under $DSHOME/../branded_odbc/docs/odbcuser

3) Test connection in DataStage

After you have have successfully tested the DSN using the programs ddtestlib and example then you can continue with DataStage. The only additional file that you DataStage uses besides the .odbc.ini file for ODBC connections is the uvodbc.config file. There is one uvodbc.config file under each project directory and it has a list of all the DSN that can be accessed from this project. Instructions on how to modify this file are located in the same file. Here is an excerpt of this file:

*** To get to any ODBC source other than UniVerse, you need entries
*** that look as follows (the data source must also be configured
*** via the operating system's own mechanisms):
***
*** <data source name>
*** DBMSTYPE = ODBC

A common errors while editing this file are suppressing spaces around the "=". In the previous example, you should add these lines to this file:

<iadb>
DBMSTYPE = ODBC

Note: there is also an uvodbc.config file under $DSHOME. This file has the same structure as that under each project. DSNs added to this file will show up in all projects.

The final test is to try to import a table definition from this database by using DataStage Designer and the ODBC DSN. You should be able to see the DSN listed. If you don't see the DSN, then there is a problem in the uvodbc.config file. If you see the DSN and you can get the list of tables of this database then the connection is working fine and you can use this DSN in a job.

[{"Product":{"code":"SSZJPZ","label":"IBM InfoSphere Information Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"9.1;8.7;8.5;8.1;11.5;11.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21434177