DB2 10.5 for Linux, UNIX, and Windows

Obtaining a DB2 trace using db2trc

The db2trc command controls the trace facility provided with DB2®. The trace facility records information about operations and formats this information into a readable form.

Keep in mind that there is additional processor usage when a trace is running so enabling the trace facility might impact your system's performance.

Note: On Windows operating systems, the db2trc command will trace all DB2 instances that belong to the same installed copy. On Linux and UNIX operating systems, DB2 instances can be traced individually.

In general, IBM Software Support and development teams use DB2 traces for troubleshooting. You might run a trace to gain information about a problem that you are investigating, but its use is rather limited without knowledge of the DB2 source code.

Nonetheless, it is important to know how to correctly turn on tracing and how to dump trace files, just in case you are asked to obtain them.

Note: You will need one of SYSADM, SYSCTRL or SYSMAINT authority to use db2trc

To get a general idea of the options available, execute the db2trc command without any parameters:

C:\>db2trc 
Usage: db2trc (chg|clr|dmp|flw|fmt|inf|off|on) options

For more information about a specific db2trc command parameter, use the -u option. For example, to see more information about turning the trace on, execute the following command:

db2trc on -u

This will provide information about all of the additional options (labeled as "facilities") that can be specified when turning on a DB2 trace.

When turning trace on, the most important option is -L. This specifies the size of the memory buffer that will be used to store the information being traced. The buffer size can be specified in either bytes or megabytes. (To specify megabytes append either "M" or "m" after the value). The trace buffer size must be a power of two megabytes. If you specify a size that does not meet this requirement, the buffer size will automatically be rounded down to the nearest power of two.

If the buffer is too small, information might be lost. By default only the most recent trace information is kept if the buffer becomes full. If the buffer is too large, it might be difficult to send the file to the IBM Software Support team.

If tracing an operation that is relatively short (such as a database connection), a size of approximately 8 MB is usually sufficient:

C:\> db2trc on -l 8M 
Trace is turned on

However, if you are tracing a larger operation or if a lot of work is going on at the same time, a larger trace buffer might be required.

On most platforms, tracing can be turned on at any time and works as described previously. However, there are certain situations to be aware of:

On multiple database partition systems, you must run a trace for each physical (as opposed to logical) database partition.
On HP-UX, Linux and Solaris platforms, if the trace is turned off after the instance has been started, a very small buffer will be used the next time the trace is started regardless of the size specified. For example, yesterday you turned trace on by using db2trc on -l 8m, then collected a trace, and then turned the trace off (db2trc off). Today you want to run a trace with the memory buffer set for 32 megabytes (db2trc on -l 32m) without bringing the instance down and restarting. You will find that in this case trace will only get a small buffer. To effectively run a trace on these platforms, turn the trace on before starting the instance with the size buffer you need and "clear" the buffer as necessary afterwards.

To reduce the amount of data collected or formatted, the db2trc command supports several mask options. Reducing the amount of data collected is useful, because it can reduce the additional processor usage incurred due to an ongoing trace collection, and because you can collect data more selectively. Collecting data more selectively can also help speed up problem diagnosis.

You typically use the -m mask option under the guidance of IBM support. However, you can use the -p mask option to collect a trace only for specific process IDs (and optionally thread IDs). For example, to enable tracing for process 77 with threads 1, 2, 3, and 4, and for process 88 with threads 5, 6, 7, and 8 the syntax is:

db2trc on -p 77.1.2.3.4,88.5.6.7.8

Using the trcon and troff scripts to control trace collection

Two scripts are available to make trace collection simpler by replacing several manually issued commands with a single script invocation.

The db2trcon script turns on tracing and supports several options. You can use this script to turn on db2trc for a specified amount of time, specify to collect a trace for only the top processor time consuming engine dispatchable units (EDUs), and generate dump, flow and format files automatically. For example, to turn tracing on for a duration of 45 seconds for the top 5 processor time consuming EDUs, sampled in 15 second intervals from the time the script is run, issue the following command:

db2trcon
-duration 45 -top 5 -interval 15 -flw -fmt

When db2trc is turned off after the specified duration, db2trcon automatically generates the dump, flow and format files for you.

The db2trcoff turns off tracing and can generate dump, flow and format files automatically with a single command. For example, to turn db2trc off with -force and generate flow format and dump files, issue the following command:

db2trcoff -flw
-fmt -force

Note that if you turned tracing on with the db2trcon script and specified a duration, you do not need to issue the db2troff command separately.