ODBC Tracing

Today morning, we got a call from one of our clients about a performance issue with one of their vendor applications.  After talking to the vendor, we started running the ODBC trace since this application was using an ODBC connection and we suspected that a recent upgrade of MDAC (Microsoft Data Access Components) on this system might have led to these issues.  The ODBC driver manager has tracing capabilities that help us record the function calls made by the vendor application and logs those into a log file.  The tracing is performed by a trace DLL that captures all the calls that get made between the vendor application and the driver manager as well as the driver manager and the driver.

So, how can we go about enabling the ODBC trace?  You can do it through the GUI or you can also do it via the registry.  Let’s take a look at both the approaches:

GUI:  On the ODBC Data Source Administrator, you will see a tab called Tracing…

On the tracing tab, if you want to run a machine wide trace, then check the check box for “Machine-Wide tracing for all user identities” to help enable machine wide tracing.  The default is per user tracing.

The log file path name can be specified.  The tracing starts when you click on the button in this GUI to start the trace and it can be stopped from there itself.  The trace file contains a log of each ODBC function call that gets made along with the data types and the values for all of the arguments.  All input functions as well as returned functions along with the return codes and error states are logged.

Registry/ini file:

The other way to enable the ODBC tracing is by using the registry.  The registry entry for machine wide ini file setting is at:

HKLM\SOFTWARE\ODBC\Odbc.ini

and the one that is user specific is at this location:

HKCU\SOFTWARE\ODBC\Odbc.ini

Where HKLM: HKEY_LOCAL_MACHINE and HKCU: HKEY_CURRENT_USER

So what decides which key is being used?  In the ODBC GUI before you have seen that one can create a system data source or a user data source – system data source is available to all the users where as the user data source is available only to the current user.  The system data sources are under HKLM and the current user ones are under HKCU.  So, under Odbc.ini, you can set the Trace and TraceFile keywords and setting these enables/disables the tracing.

Through the application code:  If you are using ODBC in your application, through the API also you can have this functionality of enabling/disabling the tracing which can be made configurable as well as pretty granular.  This, in our opinion, provides much better instrumentation of the code.  One can do this by calling SQLSetConnectAttr and set the SQL_ATTR_TRACE attribute in the connection to SQL_OPT_TRACE_ON.  So, by doing this you would be enabling/disabling it for the connection duration.  When you want to turn it off, set it to SQL_OPT_TRACE_OFF.  SQL_ATTR_TRACEFILE is where you specify the name of the trace file.