Tutorial: ODBC Driver Diagnosis and Troubleshooting - Diagnostic Tools
This section discusses the diagnostic tools that are available to you when you are configuring and troubleshooting your ODBC environment for DataDirect Connect for ODBC and DataDirect Connect64 for ODBC.
ODBC tracing allows you to record the ODBC function calls executed to an ODBC driver into a text file. Creating a trace log is particularly useful when you are troubleshooting an issue.
To create a trace log, turn on tracing, start the ODBC application, reproduce the issue, stop the application, and turn off tracing. Then, open the log file in a text editor and review the output to help you debug the problem. Be sure to turn off tracing when you are finished reproducing the issue because tracing decreases the performance of your ODBC application.
For a more thorough explanation of tracing, see How To Read an ODBC Trace File.
On Windows, you enable tracing by clicking the Start Tracing button on the Tracing tab within the ODBC Administrator. You can also specify the name and location of the trace log on this tab.
On UNIX and Linux, the [ODBC] section in the system information file must include the keywords Trace and TraceFile. For example:
In this example, tracing is enabled and trace information is logged in a file named odbctrace.out. Although a path to the trace log is shown in the example, it is not required. To disable tracing, set the Trace value to 0.
The ivtestlib tool (ddtestlib on DataDirect Connect64 and Connect64 XE for ODBC) is provided to help diagnose configuration problems in the UNIX and Linux environments (such as environment variables not correctly set or missing database client components). This tool is installed in the bin subdirectory in the DataDirect product installation directory, for example, /opt/odbc/bin. The tool attempts to load a specified ODBC driver and prints out all available error information if the load fails.
On Solaris, for example, if drivers are installed in /opt/odbc/lib, the command:
attempts to load the Oracle Wire Protocol driver, where xx is the driver level number. If the driver cannot be loaded, ivtestlib returns an error message explaining why.
NOTE: On Solaris, AIX, and Linux, the full path to the driver does not have to be specified for ivtestlib. The HP-UX version of ivtestlib, however, requires the full path.
You can use ivtestlib to check the version strings of the ODBC drivers and Driver Manager on UNIX and Linux. Use the following command line:
Loading the 5.1 version of the Oracle Wire Protocol driver:
might, for example, return
5.1.0001 (B0001, U0002)
Loading the 5.1 version of the Driver Manager on Solaris:
might, for example, return
The Snoop tool is a Java-based application that logs TCP/IP packets sent over the network between the ODBC driver and the database. You must have a Java Virtual Machine (JVM) installed on the machine where the tool is installed. When figuring out how to configure the tool in your environment, think of it as a proxy. When the tool is running, it is listening for incoming TCP requests on a specific port number. The Snoop tool redirects those incoming TCP requests to another port number. At the same time, the tool logs the TCP packets to a text file.
You can configure the Snoop tool for one, two, or three-tiered configurations. When starting the tool, you specify the host name and port number for your database, as well as the port number the tool will listen on for incoming TCP/IP requests. You must reconfigure your ODBC data source to connect to the Snoop tool, using the tool's IP address and port number. When you establish a connection, the tool will generate a file for each connection made. The file names of the text files created by the Snoop tool will be 0.out, 1.out, and so on.
You can start the Snoop tool using the following command:
java Snoop dbhostname dbportnumber -snoopPort portnumber
where dbhostname is the database server name, dbportnumber is the database port number, and portnumber is the port number the tool will listen on for incoming TCP/IP requests.
java Snoop 127.0.0.1 50000 –snoopPort 5555
For a list of configuration options for the Snoop tool, refer to the readme file found in the snoop.jar file.
On Windows, Microsoft ships an ODBC-enabled application named ODBC Test along with their ODBC SDK. ODBC test can be used to test ODBC drivers and the ODBC Driver Manager. ODBC 3.51 includes both ANSI- and Unicode-enabled versions of ODBC Test.
To use ODBC Test, you must understand the ODBC API, the C language, and SQL. For more information about ODBC Test, see the Microsoft ODBC SDK Guide.
The product is shipped with a C application, demoodbc, on UNIX and Linux that is useful for:
- Executing SELECT * FROM emp, where [EMP] is a database table (one for each supported database) that is provided with the product. The scripts for building the EMP database tables are in the demo subdirectory in the product installation directory.
- Testing database connections.
- Creating reproducibles.
- Persisting data to an XML data file.
The demoodbc application is installed in the demo subdirectory in the product installation directory. See the readme in the demo directory for an explanation of how to compile and use this application.
The product is shipped with a C application, named example, on Windows and UNIX/Linux that is useful for:
- Executing any type of SQL statement
- Testing database connections
- Testing SQL statements
- Verifying your database environment
The example application is installed in the example subdirectory in the product installation directory. See the readme in the example directory for an explanation of how to build and use this application.
The DataDirect Technologies Support Web site provides other diagnostic tools that you can download to assist you with troubleshooting. These tools are not shipped with the product. See the DataDirect Web page:
DataDirect Technologies also provides a knowledgebase that is useful in troubleshooting problems. See the DataDirect Knowledgebase page: