Tracing Facilities
DB2 has several tracing facilities that can be used during problem determination. These facilities are discussed in the following sections.
DB2 Trace (db2trc)
A DB2 trace is a detailed trace about most function calls in the DB2 libraries. It is generally only useful to DB2 Service because it references specific trace points within the DB2 source code. Running the trace will generally slow down performance because additional code is executed. To reduce performance loss, the trace is written to shared memory by default, which can then be dumped to a file after the trace is completed. The problem with this is that the size of the shared memory buffer must be specified when enabling the trace, so if the trace contents exceed the size of the memory buffer, the buffer contents wrap and information is lost. There is an option to trace directly to file, but this can be painfully slow and is not generally recommended.
Tracing against DB2 UDB can be started with the db2trc command. The following is the suggested sequence for tracing:
| db2trc on –l 8000000 | This command is run from the command line and starts the tracing facility. The '-l 8388608' option specifies the use of an 8 MB buffer of memory and that the last 8 MB of traces should be kept in the event that tracing wraps. A '-i' can be used instead to specify that the initial 8 MB of tracing be kept. Type 'db2trc ?' for a complete list of options. |
| Reproduce the problem | Because tracing usually occurs within a limited buffer space, try to keep the reproduction of the problem as small as possible so that the buffer does not wrap. Also, be aware that db2trc traces all DB2 activity for the instance that started it. So if |
| you are tracing a server, try to keep unnecessary activity against the database to a minimum. | |
| db2trc dmp <db2trc.dmp> | After the error has been reproduced, the buffer can be dumped to a file. |
| db2trc off | This turns the tracing facility off and frees the memory buffer that was used. |
| db2trc fmt <db2trc.dmp> <db2trc.fmt> | The dump file is a binary file. To read it, you need to format it. This will create a text viewable file that lists function calls made through DB2 in chronological order. It will also show pieces of data that are passed through various functions including the SQLCA. |
| db2trc flw <db2trc.dmp> db2trc.flw | This is another text viewable file that flows the function calls by process and thread. This file will only show return values from each function. |
For more information on tracing in DB2, please refer to the DB2 Troubleshooting Guide.
CLI Trace
To trace a CLI application, you just switch tracing on and specify a file for the trace output. Because ODBC, JAVA, and OLE DB eventually use the CLI code base, these applications can be traced in this manner as well. However, doing so will not show the high-level commands that were used to call the lower-layer CLI. You can use the CCA to turn the necessary switches on to start tracing. Select an ODBC data source in the CCA window and click Properties. This will bring up the Database properties window. Click the Setting button in the CLI/ODBC field and then click the Advanced button in the CLI/ODBC setting window. You will see the CLI/ODBC Advanced settings window.
The interface gives you tips on each parameter. You can switch on tracing, and specify a file name to write to, or a directory to which multiple files will be written (one for each thread, this is usually preferred). Table C.1 lists the various options that are available. You also can specify these options by directly editing the db2cli.ini file found in the sqllib directory in Windows and sqllib/cfg in UNIX. For example, you could add the following lines:
[COMMON] Trace=1 TraceFlush=1 TraceComm=1 TracePidTid=1 TraceTimestamp=1 TraceFileName=C: race race.txt
The files produced during CLI tracing are ASCII text files and are reasonably straightforward to read, especially if you have an understanding of CLI/ODBC calls. The trace will even show error messages that the application has not retrieved. This tracing facility can be very useful for debugging CLI/ODBC applications.
Following is sample output from a CLI trace that shows a failed connection to the database server:
[ Process: 99704, Thread: 1 ]
[ Date & Time: 01-27-2003 18:35:03.815137 ]
[ Product: QDB2/6000 DB2 v8.1.0.8 ]
[ Level Identifier: 01010106 ]
[ CLI Driver Version: 08.01.0000 ]
[ Informational Tokens: "DB2 v8.1.0.8","n030116","U486245","Fixpak 1" ]
SQLAllocEnv( phEnv=&2ff20700 )
---> Time elapsed - +6.241215E+000 seconds
SQLAllocEnv( phEnv=0:1 )
<--- SQL_SUCCESS Time elapsed - +5.415000E-003 seconds
SQLAllocConnect( hEnv=0:1, phDbc=&2ff20704 )
---> Time elapsed - +6.520000E-004 seconds
SQLAllocConnect( phDbc=0:1 )
<--- SQL_SUCCESS Time elapsed - +5.973000E-003 seconds
SQLSetConnectAttr( hDbc=0:1, fOption=SQL_ATTR_AUTOCOMMIT, pvParam=NULL, iStrLen=-6 )
---> Time elapsed - +6.770000E-004 seconds
SQLSetConnectAttr( )
<--- SQL_SUCCESS Time elapsed - +9.100000E-004 seconds
SQLConnect( hDbc=0:1, szDSN="dbmall", cbDSN=-3, szUID="", cbUID=-3, szAuthStr="",
cbAuthStr=-3 )
---> Time elapsed - +1.590000E-004 seconds
SQLConnect( )
<--- SQL_ERROR Time elapsed - +1.502700E-002 seconds
SQLFreeConnect( hDbc=0:1 )
---> Time elapsed - +5.160000E-004 seconds
( Unretrieved error message="[IBM][CLI Driver] SQL1013N The database alias name or
database name "DBMALL" could not be found. SQLSTATE=42705" )
SQLFreeConnect( )
<--- SQL_SUCCESS Time elapsed - +3.085900E-002 seconds
SQLFreeEnv( hEnv=0:1 )
---> Time elapsed - +6.810000E-004 seconds
SQLFreeEnv( )
<--- SQL_SUCCESS Time elapsed - +1.268000E-003 seconds
NOTE
Tracing a CLI/ODBC application will slow down performance considerably. Also, a large amount of data is written to the output trace file and may use up a large amount of disk space. Do not forget to turn it off!
JDBC Trace
Adding keywords to the db2cli.ini file enables a JDBC trace. It can be used for Java programs that use the DB2 app driver. JDBC tracing is generally used concurrently with CLI tracing to see how JDBC calls are translated into corresponding CLI calls. To enable JDBC tracing, add the following lines to the COMMON section of the db2cli.ini file on the client machine where the Java program is running:
[COMMON] JDBCTrace=1 JDBCTraceFlush=1 JDBCTracePathName=<path>
The path specified must be an existing path. It is important to ensure that the user running the Java program has write-access to the path specified. The files created will include the process ID (PID) of the application that was running with a separate file for each Java thread. You must restart your application for tracing to take effect. After you have edited the db2cli.ini file as shown, you can turn off JDBC tracing by changing the JDBCTrace keyword value to 0.