From Ingres Community Wiki

Contents 1 Introduction 2 "Standard" ODBC Tracing 2.1 Window Environments 2.2 ODBC Tracing on Unix, Linux and VMS 2.3 Ingres ODBC CLI 2.4 UnixODBC Driver Managers 3 ODBC tracing on all platforms - "Internal" ODBC tracing 4 Internal versus Standard ODBC Trace Logs 5 Turning Off Tracing 5.1 Standard Tracing 5.2 Internal Tracing

Introduction

If you contact Ingres Support or post an ODBC problem in the Ingres Community Forum, you may be asked to generate an ODBC trace log. This article explains how to generate ODBC trace logs and lists the tracing options available.

"Standard" ODBC Tracing

"Standard" ODBC traces are ODBC logs written when ODBC tracing is enabled in the ODBC Administrator. These types of logs are written in a format that describes each function entry and function exit point of an ODBC application. Here is an excerpt from a "standard" ODBC trace:

python iter     ae4-21c ENTER SQLAllocHandle
                SQLSMALLINT                  1 <SQL_HANDLE_ENV>
                SQLHANDLE           00000000
                SQLHANDLE *         0021FCA4

python iter     ae4-21c EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
                SQLSMALLINT                  1 <SQL_HANDLE_ENV>
                SQLHANDLE           00000000
                SQLHANDLE *         0x0021FCA4 ( 0x00961788)

Quite often, Ingres Support can generate test cases that reproduce the problem entirely from the ODBC trace log.

Window Environments

ODBC trace logs may be created for any Ingres application that uses ODBC. In addition to "straight" ODBC, applications include, but are not limited to:

• ADO
• ADO .Net Service Provider
• ODBC .Net Service Provider
• Ingres Python DBI Driver
• Ingres PHP ODBC Driver
• Windows Access
• Windows Excel
• OpenOffice Base
• Open Office Calc
• ASP pages

NOTE: ASP pages, or any ODBC-based application executed from IIS (Internet Information Service) do not support ODBC tracing. See the Microsoft KB document 836072 at [1].

The Windows ODBC Administrator is used to enable tracing. The ODBC Administrator may be visible from the Control Panel, or may be executed from the command prompt as \WINDOWS\SYSTEM32\odbcad32.exe.

To enable tracing, click on the "Tracing" tab in the ODBC Administrator's startup page, and click on 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 may edit this box if you wish to redirect ODBC tracing to another file.

ODBC Tracing on Unix, Linux and VMS

Ingres ODBC CLI

For Ingres ODBC CLI applications, a standard ODBC trace is possible, but more difficult to obtain. 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. Once you have installed the ODBC trace library, traces may be enabled for ODBC CLI applications by selecting "Drivers->Tracing" from the Ingres ODBC Administrator, iiodbcadmin. For Linux, Unix and VMS, execute the iiodbcadmin utility; select the "Set Tracing" menu option to turn tracing on, then select "Save".

UnixODBC Driver Managers

UnixODBC environments support tracing. The Ingres ODBC Administrator may be used to trace in unixODBC environments, as documented above for ODBC CLI applications. In fact, unixODBC DSN's (Data Source Names) may be administered from the Ingres ODBC Administrator.

If you wish to edit the odbcinst.ini file manually, add this section to odbcinst.ini to enable unixODBC tracing:

[ODBC]
Trace = yes
TraceFile = /tmp/odbctrace.log

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 (logical definitions on VMS) to generate the trace.

The environment variable II_ODBC_LOG specifies the file name of the ODBC trace. It is recommended to specify the full path of the trace log file; otherwise it may be difficult to find the log after the application is executed. II_ODBC_TRACE specifies the tracing level and ranges from 0 to 5--the higher the value, the more detailed the output.

The "ingsetenv" utility may be used to specify ODBC tracing. In IIS environments, this is the only option.

Use ingsetenv carefully, since all ODBC applications will "see" these variables whenever they are executed.

Here is an example of ODBC tracing using ingsetenv:

ingsetenv II_ODBC_LOG C:\temp\odbc.log
ingsetenv II_ODBC_TRACE 5

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:

> set II_ODBC_TRACE=5
> set II_ODBC_LOG="C:\My Path\odbctrace.log"

On Unix and Linux, ODBC tracing is specified as follows for Bourne, Korn and Bash shells:

# II_ODBC_TRACE=5
# export II_ODBC_TRACE
# II_ODBC_LOG=/tmp/odbctrace.log
# export II_ODBC_LOG

On Unix and Linux, ODBC tracing is specified as follows for the C shell:

$ setenv II_ODBC_TRACE 5
$ setenv II_ODBC_LOG /tmp/odbctrace.log

On VMS, ODBC tracing is specified as follows:

$ DEF/JOB II_ODBC_LOG SYS$LOGIN:ODBC.LOG
$ DEF/JOB II_ODBC_TRACE 5

You may wish to add GCA or API tracing. The environment variables II_GCA_TRACE and II_API_TRACE may be used to in addition to II_ODBC_LOG. See GCA_Tracing for more information about GCA tracing.

Here is a sample excerpt from an ODBC trace log that specifies both ODBC and GCA tracing:

!ODBC Trace: SQLGetInfo (ca7cd0, 77, 21f0a4, 100, 21f144)
!ODBC Trace: ResetDbc
!ODBC Trace: SQLDriverConnect ()
!ODBC Trace: ResetDbc
!ODBC Trace: ConDriverName
!ODBC Trace: ConDriverInfo
!ODBC Trace: SQLConnect (ca7cd0, 0, 0, 0, 0, 0, 0)
!   0 GCA_REQUEST timeout=120000 async
!   0     GCA SA_INIT status 00000000 (0)
!   0     GCA SA_JUMP status 00000000 (1)
!   0     GCA RQ_INIT status 00000000 (222)
!   0   GCA_REQUEST target='var/INGRES' prot=66
!   0     GCA RQ_ALLOC status 00000000 (223)
!   0     GCA SA_BUFFERS status 00000000 (224)
!   0     GCA RQ_IS_NOXLATE status 00000000 (225)
!   0     GCA RQ_IS_RSLVD status 00000000 (226)
!   0     GCA RQ_ADDR_NSID status 00000000 (227)
!   0     GCA RQ_IS_NMSVR status 00000000 (228)
!   0     GCA RQ_USE_NMSVR status 00000000 (229)

NOTE: Do not define II_API_LOG or II_GCA_LOG if you wish to include ODBC trace information. II_ODBC_LOG must be defined.

Internal versus Standard ODBC Trace Logs

Internal ODBC trace logs are the most useful for ODBC problems that are not related to the ODBC. For example:

• Problems connecting to the database.
• Errors reported from the DBMS server, such as SQL syntax errors, table permission errors, or failure to return data from a query.
• Errors related to commit or rollback.
• On Windows, this 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.

Turning Off Tracing

Don't 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.

Standard Tracing

On Windows, click on the "Tracing" tab in the ODBC Administrator, and click on the "Stop tracing now" button.

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, then select "Save".

In unixODBC environments, if you prefer to edit the odbcinst.ini file manually, unset tracing as follows:

[ODBC]
Trace = no
TraceFile = 

Internal Tracing

The "ingunset" utililty turns off variables set via "ingsetenv". In Unix and Linux environments, "unset" or "unsetenv" is used, depending on the execution shell. On VMS, "DEASSIGN/JOB" unsets the Ingres logical names.

On Windows, unset ODBC trace variables as follows:

> SET II_ODBC_LOG=
> SET II_ODBC_TRACE=