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

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

book

Article ID: 53498

calendar_today

Updated On:

Products

CA Single Sign On Secure Proxy Server (SiteMinder) CA Single Sign On SOA Security Manager (SiteMinder) CA Single Sign-On SITEMINDER

Issue/Introduction

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?

 

Environment

Release:
Component: SMPLC

Resolution

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

Attachments

1558535530882TEC491497.zip get_app
Powered by Wolken Software