DB2 Version 9.7 for Linux, UNIX, and Windows

CLI traces

The CLI trace contains a record of all the CLI function calls that the CLI driver made.

The CLI trace is an essential tool for diagnosing problems with applications that access the CLI driver. The CLI trace provides diagnostic information when a problem is encountered in any of the following places:

A CLI application
An ODBC application, because ODBC applications use the CLI interface to access IBM® database servers
A CLI stored procedure

By default, the trace utility is disabled. When enabled, the trace utility generates one or more trace files whenever an application accesses the CLI driver. These trace files provide the following information:

The order in which the application called the CLI functions
The contents of input and output parameters that were passed to and received from the CLI functions
The return codes and any error or warning messages that the CLI functions generated

CLI trace file analysis provides a number of benefits. First, subtle program logic and parameter initialization errors are often evident in the traces. Second, CLI traces might suggest ways of better tuning an application or the databases that it accesses. For example, if a CLI trace shows that a particular set of columns is queried many times, you might want to create an index corresponding to one of the columns to improve application performance. Finally, analysis of CLI trace files can help you understand how a third-party application or interface is behaving.

Different methods for tracing the CLI driver

You can trace the CLI driver by using one of the following methods:

By issuing the db2trc on -cli command. The db2trc on -cli command provides a dynamic way to obtain a CLI trace. You can enable and disable the trace without restarting the application. The command creates a binary trace file that you must process into user-readable output by issuing the db2trc fmt -cli trace.dmp clitrace.txt command, where trace.dmp is the name of the binary trace file and clitrace.txt is the name of the output file.
By modifying the db2cli.ini configuration file to include the CLI trace configuration keywords that controls the trace. If you use the db2cli.ini file to obtain a trace, you must restart the application for the CLI trace configuration keywords in the db2cli.ini configuration file to take effect. The restart is required because the values of the CLI trace configuration keywords are read from the db2cli.ini file only when an application is initialized. You can use a special CLI trace keyword, TraceRefreshInterval, to indicate an interval at which the values of specific CLI trace keywords are reread from the db2cli.ini file.
You can override the TRACE keyword setting in the db2cli.ini file by setting the SQL_ATTR_TRACE environment attribute with the SQLSetEnvAttr() API in the application.

CLI driver traces versus ODBC driver manager traces

When diagnosing ODBC applications, it is often easiest to determine the problem by using an ODBC trace or a CLI trace. An ODBC driver manager provides the capability to enable an ODBC trace. An ODBC driver manager is not part of a DB2® product; you must acquire an ODBC driver manager from an independent software vendor. To determine how to enable ODBC tracing, consult your ODBC driver manager documentation.

It is important to understand the difference between an ODBC driver manager trace and a CLI driver trace. An ODBC driver manager trace shows the ODBC function calls that an ODBC application makes to the ODBC driver manager. By contrast, a CLI driver trace shows the function calls that the ODBC driver manager makes to the CLI driver on behalf of the application.

The CLI traces are specific to the DB2 software and typically contain more information than a generic ODBC trace. Both types of traces list entry and exit points for all ODBC function calls from an application and include any parameters that are passed to the ODBC functions and return codes from the ODBC functions.

An ODBC driver manager might forward some function calls directly from the application to the CLI driver. However, the ODBC driver manager might also delay or avoid forwarding some function calls to the driver. The ODBC driver manager might also modify application function arguments or map application functions to other functions before forwarding the call to the CLI driver.

The ODBC driver manager intervenes to perform listed tasks:

ODBC 2.0 functions that are deprecated in ODBC 3.0 are mapped to new functions.
ODBC 2.0 function arguments that are deprecated in ODBC 3.0 are mapped to equivalent ODBC 3.0 arguments.
The Microsoft cursor library maps calls to functions such as the SQLExtendedFetch() function to multiple calls to theSQLFetch() function and other supporting functions to achieve the same result.
ODBC driver manager connection pooling usually defers SQLDisconnect() function call requests or avoids them altogether if the connection is reused.

For you to determine what ODBC function calls are made to the CLI driver by the ODBC driver manager, you might find an ODBC driver manager trace to be a useful complement to the CLI driver trace.

For more information about the ODBC driver manager trace, see the ODBC driver manager documentation that is provided by the independent software vendor. For more information about the Microsoft ODBC 3.0 Software Development Kit and Programmer's Reference, see http://www.msdn.microsoft.com/.

CLI driver and DB2 traces

The CLI driver uses many internal DB2 functions to do its work. These internal DB2 function calls are logged in the DB2 traces. The DB2 traces are meant to assist IBM Service in problem determination and resolution.

CLI traces and CLI stored procedures

On all workstation platforms, you can use the CLI trace utility to trace the execution of CLI stored procedures.

The CLI trace information and instructions are generic and apply to the stored procedures. However, unlike applications that are typically executed on a remote computer that is separate from the database server, stored procedures execute on the database server. Therefore, you must take following steps when tracing the CLI stored procedures:

Specify the trace keyword options in the db2cli.ini file on the DB2 server.
You must set all keywords correctly before you run the db2start command to start the database manager.

Note: The TRACEREFRESHINTERVAL and QUERYTIMEINTERVAL CLI keywords are ignored if you use them inside a stored procedure that uses CLI API calls.