How to enable odbc trace in Windows

Document created by Karmeng Employee on Nov 7, 2014Last modified by kristen.palazzolo on Dec 17, 2016
Version 2Show Document
  • View in full screen mode

Hi,

ODBC trace log is important when there is connection issue between policy server and database.

I find this KB is very useful

 

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec491497.aspx

 

Here are the contents. ivtrac.dll attached in this discussion:

 

Is it possible to get timestamps in the ODBC trace when the operating system is windows?

Document ID:  TEC491497
Last Modified Date:  11/29/2012
ShowHide Technical Document Details

Description

I can get timestamps in the ODBC trace when I set the parameter TraceOptions in the system_odbc.ini file on UNIX systems, but can get the same functionality on windows. Is it possible to set the parameter TraceOptions with the Oracle / SQL Server Wire Protocol on windows?

Solution

IMPORTANT: This article contains information about modifying the registry.
Before you modify the registry, make sure to create back up of the registry and ensure that you understand how to restore the registry if a problem may occur.
For more information about how to back up, restore, and edit the registry, please review the relevant Microsoft Knowledge Base articles on support.microsoft.com.

The TraceOptions settings will not apply to windows since the trace library on windows is from Microsoft.

Data Direct has an extended trace library for windows which you can be used to get the timestamp value in the ODBC trace.

This library can be implemented as follows:

1.    Copy the trace library, ivtrac.dll, to C:\Windows\System32. The Windows driver manager requires that the trace library be in this location.

2.    Open the Windows Registry with regedit and navigate to HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC. Add a new string value called TraceOpts and set it to the appropriate value to enable the desired features. A list of valid values for TraceOpts and their corresponding features is listed below.

3.    Open the ODBC Administrator and select the Tracing tab. Chose C:\ Windows\System32\ivtrac.dll in the Custom Trace DLL field.

4.    As with the standard trace library, specify a trace file name and start ODBC tracing by clicking the Start Tracing Now button.

Valid TraceOpts values and their associated features:

To enable the new ODBC tracing features, set TraceOpts=n where n is a valid integer value. Multiple features can be used simultaneously by setting TraceOpts to their cumulative total. For example, to use both option 1 and option 4 set TraceOpts=5.

TraceOpts=1 : Displays a timestamp before every ENTER and EXIT that is logged in the trace file.

TraceOpts=2 : Displays values bound to parameter markers with SQLBindParameter assuming the app assigned a value to the buffer before calling SQLBindParameter.

TraceOpts=4 : The trace library will call SQLGetInfo to return the database and driver names and versions after every successful connection.

TraceOpts=8 : Displays the thread id and process id before every ENTER and EXIT that is logged in the trace file.

TraceOpts=16 : Displays the hexadecimal values for character data as it is sent to and received from the drivers

TraceOpts=32 : Limits the size of trace logs. When this feature is enabled, a new trace log will be created when the threshold has been reached and names of the logs will be numbered sequentially. The new trace logs will always begin at the entrance to an ODBC function call. The optional setting in the ODBC section, LogFileSize, specifies the maximum size, in megabytes, for an ODBC trace log. If LogFileSize = 2, then the logs will be limited to a maximum size of 2MB. If this feature is enabled and LogFileSize is not specified, the trace library assumes a size limit of 1MB. Note the log file size is approximate and may vary slightly from the specified size.

Example (TraceOpts=9):

<- Begin

  2009-06-16 15:07:58.000 pid=5760  tid=6052 

  ENTER SQLConnectW

    HDBC                0x03f71df0

    WCHAR *             0x03f717e8 [      -3] "smdb"

    SWORD                       -3

    WCHAR *             0x48897cf4 [      -3] "******"

    SWORD                       -3

    WCHAR *             0x48897cf4 [      -3] "******"

    SWORD                       -3

  2009-06-16 15:07:58.078 pid=5760  tid=6052 

  EXIT  SQLConnectW with return code 1 (SQL_SUCCESS_WITH_INFO)

    HDBC                0x03f71df0

    WCHAR *             0x03f717e8 [      -3] "smdb"

    SWORD                       -3

    WCHAR *             0x48897cf4 [      -3] "******"

    SWORD                       -3

    WCHAR *             0x48897cf4 [      -3] "******"

    SWORD                       -3

  2009-06-16 15:07:58.078 pid=5760  tid=6052 

  ENTER SQLGetInfoW

    HDBC                0x03f71df0

    UWORD                       17 <SQL_DBMS_NAME>

    PTR                 0x03f726d8

    SWORD                     2048

    SWORD *             0x05affefc (1024)

  2009-06-16 15:07:58.078 pid=5760  tid=6052 

  EXIT  SQLGetInfoW with return code 0 (SQL_SUCCESS)

    HDBC                0x03f71df0

    UWORD                       17 <SQL_DBMS_NAME>

    PTR                 0x03f726d8 [      -3] "Oracle"

    SWORD                     2048

    SWORD *             0x05affefc (12)

    2009-06-16 15:07:58.078 pid=5760  tid=6052 

    ENTER SQLGetInfoW

    HDBC                0x03f71df0

    UWORD                       17 <SQL_DBMS_NAME>

    PTR                 0x03f726d8

    SWORD                     2048

    SWORD *             0x05affefc (6)

  2009-06-16 15:07:58.078 pid=5760  tid=6052 

  EXIT  SQLGetInfoW with return code 0 (SQL_SUCCESS)

    HDBC                0x03f71df0

    UWORD                       17 <SQL_DBMS_NAME>

    PTR                 0x03f726d8 [      -3] "Oracle"

    SWORD                     2048

    SWORD *             0x05affefc (12)

  2009-06-16 15:07:58.078 pid=5760  tid=6052 

  ENTER SQLGetInfoW

    HDBC                0x03f71df0

    UWORD                       18 <SQL_DBMS_VER>

    PTR                 0x03f726d8

    SWORD                     2048

    SWORD *             0x05affefc (6)

  2009-06-16 15:07:58.078 pid=5760  tid=6052 

  EXIT  SQLGetInfoW with return code 0 (SQL_SUCCESS)

    HDBC                0x03f71df0

    UWORD                       18 <SQL_DBMS_VER>

    PTR                 0x03f726d8 [      -3] "10.02.0000 Oracle 10.2.0.2.0"

    SWORD                     2048

    SWORD *             0x05affefc (56)

-> End

 

This document was generated from the following discussion: The specified item was not found.

1 person found this helpful

Attachments

Outcomes