IBM Support

Gathering an ODBC Trace

Troubleshooting


Problem

This document describes how to trace the ODBC API calls made by an application. Additionally, this document lists common problems that might be encountered during the gathering of traces and explains how to correct them.

Resolving The Problem

To gather an ODBC trace by using Microsoft ODBC data source administrator, do the following:
  1. Shut down all ODBC applications including the application to be traced.

    The ODBC Administrator sometimes fails to trace the wanted type of application (user or system). To avoid this problem, shut down all ODBC applications or refer to the following section Forcing the Trace On. When you end ODBC applications, also end any services that use ODBC. Services must be shut down using the Services tool located in the Windows control panel. For Internet Information Server, the service is called the World Wide Web publishing service. Microsoft SQL Server also runs as a service.
  2. Start the ODBC data source administrator.

    Click Start on the Microsoft Windows taskbar, select Programs > IBM i Access Client Solutions > ODBC Administration.

    If the shortcut does not exist, click the Start button on the Windows taskbar, and click run. Type ODBCAD32.EXE, and click OK. Also, depending on if it is 64-bit or 32-bit you need to make sure you are launching the correct ODBC Administrator. The executable file is the same name but it resides in different paths.

    Note: The ODBC administrator can also be accessed through the Windows control panel; however, the name of the shortcut and its location varies with different versions of Microsoft Data Access Components (MDAC) and Windows.
  3. Start the Traces.

    In the ODBC data source Administrator, click the Tracing tab. Depending upon the Microsoft Data Access Components (MDAC) version installed on your PC, you see something similar to the following interface.

    Newer versions of MDAC produce more detailed traces. It is believed that MDAC version 2.8 is the last version. If you are running an older version, download and install updates to the MDAC level on your PC by searching the Microsoft website, www.microsoft.com, for MDAC. With the old version, select the option labeled All the time under When to trace. In both versions, change the log file path to something new, for example, C:\IBMSQL.LOG. The reason for doing this is that this trace tool appends data to existing log files, and we do not want to collect more data than is relevant. Click the Apply button, and then the Start Tracing Now button. Notice that the text on the button changes to read Stop Tracing Now when the trace is active. The trace is now active for all ODBC connections.

    We require that a second trace is gathered at the same time.  It contains details that allow IBM Support to more quickly determine the cause of failures. The cwbcotrc is a console application that can be started from the Windows DOS prompt. Run the following command to ensure that it is already off: CWBCOTRC. The result shows that the trace is off for the current user and global trace. If the traces are not off, turn them off using the following commands:

    CWBCOTRC OFF
    CWBCOTRC OFF /ALLUSERS
    CWBCOTRC DEL
    CWBCOTRC DEL /ALLUSERS

    If the application is running as a desktop application, start the trace with the following command at the DOS prompt:
    CWBCOTRC ON

    If the application is running as a Windows service program, then start the trace with the following command:
    CWBCOTRC ON /ALLUSERS
  4. Start your ODBC enabled application, reproduce the failure, and close the application.
  5. Stop the trace, and send the log to IBM Support.

    Return to the ODBC data source administrator, and click the Stop Tracing Now button.

    To stop the CWBCOTRC, use the command CWBCOTRC OFF or use the command CWBCOTRC OFF /ALLUSERS if the trace was started for all users in Step 3. When you stop the trace, the location of the log file is displayed in the command prompt window.
  6. Start a Windows command prompt as Administrator (elevated authority is required for parts of the data collection to work).  Run the command CWBSVGET; it takes a few moments to complete. Eventually it refreshes displaying the path and file that it generated. When complete, cwbsvget prompts you to press the Enter key to open Windows Explorer. Press enter to locate the compressed file that was generated and send that to IBM for analysis. Upload the compressed file by using the standard or secure upload tools at http://www.ecurep.ibm.com
Alternative Trace DLL

The Microsoft version of the trace has a significant impact on performance. It is also not thread safe and can mix or lose trace points tracing multithreaded applications. The Client Access group supplies its own version of the ODBC trace DLL that corrects both of these problems. The DLL is cwbodtrc.dll and is available at the following website:

public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/

NOTE: A 32-bit version of this trace DLL is the only one available so it can be used only with 32-bit applications.

To use the DLL, copy the DLL to the Windows system 32-bit directory. On a 64-bit Windows system that is C:\Windows\SysWOW64. On a 32-bit Windows system, it is C:\Windows\System32.

Next, open the 32-bit ODBC data source administrator utility, click the Tracing tab, and then select the cwbodtrc DLL using the Select DLL... button (refer to the pictures). Finally, click Apply and OK to save the changes.

Forcing the Trace On

IBM i Access Client Solutions has a cwbODBCreg tool that can be used to force the ODBC trace on for both user applications and services. The tool is available at the following FTP site:  ftp.software.ibm.com in the path /as400/products/clientaccess/win32/files/odbc_tool/cwbODBCreg.zip

Log on Windows as an administrator and run cwbODBCreg.exe from a command prompt. It displays the following help:

To enable/disable system-wide ODBC trace
cwbODBCreg -trace ON|OFF
<log file name> = fully qualified log file name in the form <drive letter>:\<path>\<file name>. ex: c:\sql.log

What to Do when the Trace Does Not Work

If the trace does not work, do the following:
  • Use the cwbOdbcReg.exe tool to force the trace on. Refer to the section Forcing the Trace On.
  • If ODBC Connection pooling is enabled, use the ODBC Administrator to disable it. Select the Connection Pooling tab, then double-click the IBM iSeries Access ODBC Driver. Select Don't pool connections to this driver, then click OK. The default for most applications is to use OLE DB level pooling, not ODBC. Also, remember to re-enable pooling later. Disabling pooling results in a significant performance impact.
  • Verify that the file ODBCTRAC.DLL is installed in the Windows system directory. This directory could be Windows\System, Win98\System, or some other name depending on how Windows was installed. In a Microsoft Windows 10 based system, it is typically Windows\System32.) The trace does not run when the DLL is not installed in this directory. Verify that this DLL is not a zero length file by right-clicking the file in Windows Explorer and selecting Properties. The size must not be zero.
  • Use the cwbOdbcReg.exe tool to force the trace on. Refer to the section Forcing the Trace On.
  • If ODBC Connection pooling is enabled, use the ODBC Administrator to disable it. Select the Connection Pooling tab, then double-click the IBM iSeries Access ODBC Driver. Select Don't pool connections to this driver, then click OK. The default for most applications is to use OLE DB level pooling, not ODBC. Also, remember to re-enable pooling later. Disabling pooling results in a significant performance impact.
  • Verify that the file ODBCTRAC.DLL is installed in the Windows system directory. (The directory could be Windows\System, Win98\System, or some other name depending on how Windows was installed. In a Microsoft Windows 10 based system, it is typically Windows\System32.) The trace does not run when the DLL is not installed in this directory. Verify that this DLL is not a zero length file. To confirm the file size, find the file in Windows Explorer, right-click the file, and select Properties. The size must not be zero.
  • In the ODBC Administrator, click the Tracing tab. Verify that the selection for Custom Trace DLL is pointing to the ODBCTRAC.DLL mentioned previously.
  • Specify a drive letter in the trace log path in the ODBC Administrator. If you do not select a drive, the log is placed on the drive where the executable file making the ODBC connection resides. In Windows, verify that you have NTFS authority to the path the trace points to (in the ODBC Administrator).
  • Verify that there is enough disk space available on the drive where the trace is being logged. The logs can become large.
  • Ensure that the user the service runs as is authorized to the log path. I suggest setting it to the path:  C:\Documents and Settings\All Users\Documents\IBM\SQL.LOG
  • Verify that authority to the trace is not revoked. Run REGEDT32.

    For Windows NT services:
    Open HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC

    For user programs:
    Open HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC

    Select the Security menu, and click the Permissions item. Verify that the user profile being used has Full Control permission for the key. For Windows NT services, the profile is the Windows NT profile that the service is run under.
  • In the same registry key, verify that all the values are correctly set, and match the values shown in the ODBC administrator. The value TraceDll must contain the correct path to the ODBCTRAC.DLL. The value Trace must be 1 (after the trace is enabled). The value TraceFile must contain the path to the log file.
  • Verify that the application does not disable tracing. You might need to contact the application vendor to determine this answer.
  • For more information, see Microsoft's documentation on their website. Searching for ODBC security results in several hits describing methods to secure ODBC access and prevent tracing in a Windows environment.
Uploading trace data for IBM analysis

Once the trace data is collected, there is a standard web interface for uploading that data to IBM for analysis.

http://www.ecurep.ibm.com/app/upload

On this page, you are required to provide the case number, select OS/400, and then continue so you can browse to the local file or files to be uploaded.

This interface is limited to 2Gb file size. If the trace file is larger, you need to FTP the file to IBM. Refer to the chart on the following web page for more details and methods of upload:  http://www-05.ibm.com/de/support/ecurep/send.html

Note: There is a Java utility that is multi-threaded that makes transferring large amounts of data much faster.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

19350756

Document Information

Modified date:
24 June 2021

UID

nas8N1017796

Page Feedback