ODBC Trace Diagnostics¶
If you have built your ODBC directly from code such as ADO or C, you can debug your application directly using the appropriate debugger.
ODBC trace logs are a common method for debugging ODBC applications and can be used with any application that uses the Ingres ODBC. ODBC trace logs are the most meaningful if you understand ODBC programming or Ingres internals.
Standard ODBC Tracing¶
Standard ODBC tracing is written in a format that summarizes the ODBC functions called and provides information about the arguments passed to the ODBC functions. Here is an excerpt of a standard ODBC trace:
Ingres Support can often generate test cases that reproduce the problem entirely from the information in the ODBC trace log.
Windows Environments¶
ODBC trace logs can be created for any Ingres application that uses ODBC. In addition to straight ODBC, applications include, but are not limited to:
- ADO
-
OLE DB Provider for ODBC
-
.NET Data Provider for ODBC
-
Ingres Python DBI Driver
-
Ingres PHP ODBC Driver
-
Windows Access
-
Windows Excel
-
OpenOffice Base
-
Microsoft ASP pages
Note
ASP pages, or any ODBC-based application executed from IIS (Internet Information Service) do not support ODBC tracing. See the Microsoft Knowledge Base document 836072 for more information.
The Windows ODBC Administrator is used to enable tracing. The ODBC Administrator may be visible from the Control Panel, or can be executed from the command prompt as \WINDOWS\SYSTEM32\odbcad32.exe (32-bit environments) or \WINDOWS\SysWOW64\odbcad32.exe (64-bit environments).
To enable tracing, click the Tracing tab in the ODBC Administrator’s startup page, and then click the Start tracing now button. The Tracing page includes an entry box that specifies the path and file name of the ODBC trace log file. You can edit this box to redirect ODBC tracing output to another file.
Linux Environments¶
This section discusses several methods for supporting ODBC tracing in Linux environments.
Ingres ODBC CLI¶
For Ingres ODBC CLI applications, a standard ODBC trace is possible. Ingres does not currently release the code for ODBC trace libraries. However, you can obtain a binary of the ODBC trace library from Ingres Support.
To enable tracing for ODBC CLI applications:
- Install the ODBC trace library
-
Select Drivers ⇒ Tracing from the Ingres ODBC Administrator, iiodbcadmin.
-
To enable tracing for Linux, select the Set Tracing menu option to turn tracing on, and then select Save.
UnixODBC Driver Managers¶
UnixODBC environments support tracing. The Ingres ODBC Administrator can be used to trace in unixODBC environments, as previously documented for ODBC CLI applications. In fact, ODBC DSN definitions can be created in the Ingres ODBC Administrator and can be used in unixODBC applications.
To enable unixODBC tracing by manually editing the odbcinst.ini file, add this code section:
ODBC Tracing on All Platforms--Internal ODBC Tracing¶
Internal ODBC tracing is available on all platforms, regardless of the ODBC application used. Internal ODBC tracing follows the conventions of GCA and API tracing and relies on specification of Ingres environment variables to generate the trace.
The environment variable II_ODBC_LOG specifies the file name of the ODBC trace. We recommend specifying the full path of the trace log file; otherwise it may be difficult to find the log after the application is executed.
To prevent the server from writing to the wrong log, the file name can contain one or both of the following parameter values:
%p – Replaced at startup with the Process Identifier (PID) of the server process.
%d – Replaced at startup with the current date in YYYYMMDD format.
For example:
The environment variable II_ODBC_TRACE specifies the tracing level, which ranges from 0 to 5. (The higher the value, the more detailed the output.)
The ingsetenv utility can be used to specify ODBC tracing. In IIS environments, this utility is the only option.
Note
Use ingsetenv carefully, since all ODBC applications will “see” these variables whenever they are executed.
Example: ODBC Tracing Using ingsetenv
If possible, it is preferable to use platform-specific commands to set the trace variables, especially if your environment executes many ODBC applications simultaneously.
On Windows, ODBC tracing is specified as follows:
On Linux, ODBC tracing is specified as follows for Bourne, Korn and Bash shells:
You may wish to add GCA or API tracing. The environment variables II_GCA_TRACE and II_API_TRACE can be used in addition to II_ODBC_LOG.
Here is a sample excerpt from an ODBC trace log that specifies both ODBC and GCA tracing:
Note
Do not define II_API_LOG or II_GCA_LOG if you want to include ODBC trace information. II_ODBC_LOG must be defined.
Internal versus Standard ODBC Trace Logs¶
Internal ODBC trace logs are most useful for ODBC problems that are not related to the ODBC, such as:
- Problems connecting to the database
-
Errors reported from the warehouse, such as SQL syntax errors, table permission errors, or failure to return data from a query
-
Errors related to commit or rollback
On Windows, internal ODBC trace logs is the only option for ODBC applications executed from IIS.
Standard ODBC trace logs are best for general use. Sometimes the best results are obtained by generating both types of trace logs.
Disable Tracing¶
Do not forget to disable ODBC tracing after you have generated your test case! ODBC tracing, like all tracing, can have a significant impact on performance. It is even possible that the disk may become full if tracing is enabled for a significant period of time.
How You Disable Standard Tracing¶
To disable standard tracing on Windows
- Click the Tracing tab in the ODBC Administrator.
- Click the Stop tracing now button.
To disable standard tracing for ODBC CLI applications
- Execute the iiodbcadmin utility and select the Drivers ⇒ Tracing menu options.
- Select the Set Tracing menu option to turn tracing off, and then select Save.
To disable standard tracing in unixODBC environments by editing the odbcinst.ini file
- Open the odbcinst.ini file.
- Unset tracing with the following edits:
How You Disable Internal Tracing¶
The ingunset utility turns off variables set via ingsetenv.
In Linux environments, internal tracing is disabled using either unset or unsetenv, depending on the execution shell.
On Windows, ODBC trace variables are unset as follows: