db2trc - Trace command

The trace facility records information about operations and formats this information into readable form. Enabling the trace facility (OFF by default) might impact your system's performance. As a result, only use the trace facility when directed by a DB2 technical support representative; otherwise, turn off the trace once enough information has been recorded.

DB2 traces can be especially useful when analyzing recurring and reproducible problems, which greatly facilitates the support representative's job of problem determination.

When using DB2, you might on occasion encounter an error message that directs you to "get a trace and call IBM® Support", "turn on trace and examine the trace record", or to "contact your technical support representative with the following information: problem description, SQLCODE, SQLCA contents (if possible), and trace file (if possible)". Or, when you report a problem to IBM Support, you might be asked to perform a trace to capture detailed information about your environment.

Authorization

Required connection

None

Command syntax

                                    .-db2-.                                        
>>-db2trc--+---------------------+--+-----+--+------------------------+--+-----+-><
           '-| parsing-options |-'  '-das-'  '-| collection-options |-'  '- -u-'   

parsing-options

|--+-ccfmt--destFile--------------------------------------------------------------------+--|
   +-flow--dumpFile--destFile--| mask-options |--+------------------+-------------------+   
   |                                             '-| flow-parsing |-'                   |   
   +-format--dumpFile--destFile--| mask-options |--+--------------------+---------------+   
   |                                               '-| format-parsing |-'               |   
   +-perffmt--dumpFile--destFile--------------------------------------------------------+   
   +-formattedFlow--|--fflw--dumpFile--destFile--| mask-options |--+------------------+-+   
   |                                                               '-| fflw-parsing |-' |   
   +-perfrep--dumpFile--destFile--| perfrep-options |-----------------------------------+   
   +-print--| print-options |-----------------------------------------------------------+   
   '-info-------------------------------------------------------------------------------'   

location-options

|--+--------------------------------------------------+--------->
   |  (1)                                             |   
   +------- -global--+------------------+-------------+   
   |                 '- -host -hostname-'             |   
   |         .-,--------.                             |   
   |         V          |                             |   
   +- -host----hostname-+-----------------------------+   
   |             .-,------------------------------.   |   
   |             V                                |   |   
   '- -member--+---member_number--|--member_range-+-+-'   
               '-all--------------------------------'     

>--+-------------------------------+----------------------------|
   '- -sdir--shared directory path-'   

mask-options

|--+-----------+--+--------------------------------+------------>
   '- -m--mask-'  |      .-,---------------------. |   
                  |      |        .-.--------.   | |   
                  |      V        V          |   | |   
                  '- -p------pid----+------+-+---+-'   
                                    '-.tid-'           

>--+--------------------------------------------------+---------|
   |             .-,------------------------------.   |   
   |             V                                |   |   
   '- -member--+---member_number--|--member_range-+-+-'   
               '-all--------------------------------'     

flow-parsing

   .---------------------------------------.   
   V                                       |   
|----+- -x--firstRecord--+-------------+-+-+--------------------|
     |                   '--lastRecord-' |     
     +- -data----------------------------+     
     +- -t-------------------------------+     
     +- -wc------------------------------+     
     +- -mf------------------------------+     
     '- -rds-----------------------------'     

format-parsing

   .---------------------------------------.   
   V                                       |   
|----+- -x--firstRecord--+-------------+-+-+--------------------|
     |                   '--lastRecord-' |     
     +- -r-------------------------------+     
     +- -xml-----------------------------+     
     +- -c-------------------------------+     
     +- -cli-----------------------------+     
     +- -ncf-----------------------------+     
     '- -errors--------------------------'     

fflw-parsing

   .---------------------------------------.   
   V                                       |   
|----+- -x--firstRecord--+-------------+-+-+--------------------|
     |                   '--lastRecord-' |     
     +- -data----------------------------+     
     +- -t-------------------------------+     
     +- -wc------------------------------+     
     '- -rds-----------------------------'     

perfrep-options

|--+-----+--+-------------------------+------------------------->
   '- -g-'  '- -sort--+-------------+-'   
                      +-count-------+     
                      +-timespent---+     
                      '-timeelapsed-'     

>--+----------------------------------+-------------------------|
   '- -order--+---------------------+-'   
              +-asc--|--ascending---+     
              '-desc--|--descending-'     

collection-options

|--+-on--| on-options |--| location-options |--------------------------+--|
   +-change--+---------------------------------+--| location-options |-+   
   |         '-| trace-options |--+----------+-'                       |   
   |                              '- -resume-'                         |   
   +-dump--dumpFile--+-----+--| location-options |---------------------+   
   |                 '- -q-'                                           |   
   +-info--------------------------------------------------------------+   
   +-clear-------------------------------------------------------------+   
   +-stop--| location-options |----------------------------------------+   
   '-off--| location options |-----------------------------------------'   

on-options

|--+------------------------------+----------------------------->
   '- -l--+---------------------+-'   
          '-bufferSize--+-----+-'     
                        '-M|m-'       

>--+------------------------------+--+---------------+---------->
   '- -i--+---------------------+-'  '- -f--filename-'   
          '-bufferSize--+-----+-'                        
                        '-M|m-'                          

>--+--------------------------------------------+---------------|
   +-| trace-options |--+-------+--+----------+-+   
   |                    '- -cli-'  '- -errors-' |   
   '-| performance-counter |--------------------'   

trace-options

|--+- -m--mask----------------------------------------+---------|
   |      .-,---------------------.                   |   
   |      |        .-.--------.   |                   |   
   |      V        V          |   |                   |   
   +- -p------pid----+------+-+---+-------------------+   
   |                 '-.tid-'                         |   
   |             .-,------------------------------.   |   
   |             V                                |   |   
   +- -member--+---member_number--|--member_range-+-+-+   
   |           '-all--------------------------------' |   
   |          .-,---------.                           |   
   |          V           |                           |   
   +- -appid------appid---+---------------------------+   
   |           .-,----------.                         |   
   |           V            |                         |   
   +- -apphdl------apphdl---+-------------------------+   
   +- -c--cpid----------------------------------------+   
   +- -rc--returnCode---------------------------------+   
   +- -e--maxSysErrors--------------------------------+   
   +- -t----------------------------------------------+   
   '- -debug------------------------------------------'   

performance-counter

|-- -perfcount--+-----+--+-----------+--------------------------|
                '- -t-'  '- -m--mask-'   

print-options

|--+- -stack-+--recordID--flowFile------------------------------|
   '- -s-----'                       

1. The -global parameter has been deprecated. You can use the -member all parameter options to obtain information globally.

Command parameters

-appid

Specifies which application IDs to trace. The -appid option works with the on and change options. -appid will not work with the -perfcount option.

-apphdl

Specifies which application handles to trace. The -apphdl option works with the on and change options. -apphdl will not work with the -perfcount option.

-member member_number | member_range

Specifies which database members (or partitions) to trace. If this option is not specified, the command is executed for all logical members on the host where the db2trc command is issued. Multiple members can be specified as a comma separated list of member_number (member1, member2), or using member_range, where member_range is a range of members (member1-member3), or using any combination of the first two methods.

all

Specifies that the command is issued on all members defined in db2nodes.cfg.

The -member option works with the on, change, format (both flow and format options), stop, and off options. See examples 2 and 3 below for further details. The -member option does not work with the -perfcount option.

db2

Specifies that all trace operations will be performed on the DB2 instance. This is the default.

das

Specifies that all trace operations will be performed on the DB2 Administration Server instance.

on

Use this parameter to start the trace facility. See Shared trace-options section below for a list of parameters.

-l [bufferSize]

This option specifies the size and behavior of the trace buffer. -l specifies that the last trace records are retained (that is, the first records are overwritten when the buffer is full). The buffer size can be specified in either bytes or megabytes. To specify the buffer size in megabytes, add the character M | m to the buffer size. For example, to start db2trc with a 4-megabyte buffer:

db2trc on -l 4m

The default and maximum trace buffer sizes vary by platform. The minimum buffer size is 1 MB. The buffer size must be a power of 2.

-i [bufferSize]

This option specifies the size and behavior of the trace buffer. -i specifies that the initial trace records are retained (that is, no more records are written to the buffer once it is full). The buffer size can be specified in either bytes or megabytes. To specify the buffer size in megabytes, add the character M | m to the buffer size.

-f filename

When tracing to a file, a fully-qualified file name must be specified, and if -l or -i is used with -f option, their buffersize values will limit the size of the file on disk. -l will preserve the last trace records and will be allowed to wrap within the file. -i will preserve the initial trace records and will stop tracing when the file size limit is reached. To specify the file size in megabytes, add the character M | m, and for gigabytes, add the character G | g after the value specified for -i and/or -l buffersize.

-cli

Trace the CLI/ODBC driver and CLI applications. For more information, see "Trace CLI/ODBC configuration keyword".

-errors

Trace only errors and nonzero return codes on function exit. This option cannot be specified at the same time with the -debug or -perfcount options.

change

This collection option lets you change the trace options that are in effect. See Shared trace-options section below for a list of parameters.

-resume

This option lets you resume execution of a suspended process. You cannot resume if -suspend was not enabled.

Shared trace-options

Common trace options shared between on and change.

-m mask

Reduces the amount of data collected or formatted. The trace mask has the following format: types.products.components.functions.categories

Values for the mask would be provided by IBM Support.

The mask consists of five parts (trace record types, products, components, functions, and function categories). Each part can consist of comma separated lists, hyphen separated ranges, or single entries. An asterisk (*) can be used to match anything. Field values may be specified by their names or corresponding numbers. Short form of mask specifying names of either products, components or functions parts of the full format may be used. Setting the mask to "*.*.*.*.*" is equivalent to not specifying a mask.

Example: -m "entry,exit.*.SQLO,SQLE.*.*"

-p pid [.tid]

Enables the trace facility only for the specified process IDs (pid) and thread IDs (tid). The period (.) must be included if a tid is specified. You can specify multiple thread ID's for a process ID. A period separates multiple tid's for a pid. A comma separates each pid tid pair. A maximum number 64 tid's can be paired with a pid.

For example, to enable tracing for processes 10, 20, and 30 the syntax is:

db2trc on -p 10,20,30               

To enable tracing only for thread 33 of process 100 and thread 66 of process 200 the syntax is:

db2trc on -p 100.33,200.66

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

-c cpid

Trace or format only this companion process.

-rc returnCode

Treat returnCode as a system error. returnCode must be specified as a signed integer.

-e maxSysErrors

Stop trace after maxSysErrors system errors occurred.

-t

Include timestamps.

-debug

This is an internal option used for debugging purposes by IBM Support. Usage is not recommended.

info

The following is an example of environment information listed with this parameter:

D:\Program Files\IBM\SQLLIB\BIN>db2trc info
Marker                  :  @TRACE@
Trace version           :      7.0
Platform                :       NT
Build level             :  s060629
maxBufferSize           : 2097152 bytes (2 MB)
auxBufferSize           : 6291456 bytes (6 MB)
allocationCount         : 1
DB2TRCD pid             : 2384
DB2TRCD64 pid           : 0
Trace destination       : <shared memory buffer>
debug                   : disabled
debug runtime passno    : 0
numSuspended            : 0
Trace starting time : 2011-03-25-15.03.58.909713-240

Buffer size             : 2097152 bytes (2 MB)
Allow buffer to wrap    : yes
Mask                    : *.*.*.*.*
Timestamps              : enabled
PID.TID mask            : all
Fixed data mask #1      : all
Fixed data mask #2      : all
Max system errors       : infinite
Treat this rc as sys err: none
Member mask             : none
Application ID mask     : none
Application Handle mask : none

dump dumpFile

Dumps the binary format trace information, stored in the buffer, to a file. The following command will put the information in the current directory in a file called db2trc.dmp:

db2trc dump db2trc.dmp

Specify a dump file name with this parameter. The binary format dump file is saved in the current directory unless the path is explicitly specified.

-q

Quiet mode.

ccfmt destFile

Dump and format a code coverage trace. Specify a destination file name for the dump.

flow dumpFile destFile

Formats the trace dumped to a binary file into a readable text file. Use the flow option to format records sorted by process or thread. Specify the name of the dump file and the name of the destination file that will be generated. For example:

db2trc flow db2trc.dmp db2trc.flw

-x firstRecord [-lastRecord]

Displays record numbers firstRecord to lastRecord.

-data

Includes any trace record data in the flow.

-t

Includes timestamps (in sec:nsec format), if available.

-wc

Includes wall-clock timestamps, if available. To make use of this option, you must turn trace ON by also specifying the -t option which includes the capture of timestamps. For a usage example, see Example 1.

-mf

Generates a separate destination file for each distinct flow.

-rds

Includes RDS operators information, if available.

format dumpFile destFile

Formats the trace dumped to a binary file, into a readable text file. Use the format option to format records chronologically.

-x firstRecord [-lastRecord]

Displays record numbers firstRecord to lastRecord.

-r

Outputs data in reverse order.

-xml

Outputs data in xml parsing format.

-c

Formats communication buffers.

-cli

Formats the CLI/ODBC driver and CLI application buffers. For more information, see "Trace CLI/ODBC configuration keyword".

-ncf

Specifies not to use component custom formatting.

-errors

Trace only errors and nonzero return codes on function exit.

-global

Specifies that db2trc will also be run on remote hosts. This option is deprecated in DB2 Version 9.7 Fix Pack 4 and later fix packs.

-host hostname

Specifies that db2trc will be run only on the specified host or hosts. If this parameter is not specified, the command is issued on the local host. If multiple hosts are specified, all host names must be valid for the command to complete.

-sdir shared directory path

Specifies the shared directory that db2trc will use to save files applicable to the -f and dump parameters.

perffmt dumpFile destFile

Formats a dump file containing performance counter data into readable text.

formattedFlow| fflw dumpFile destFile

Formats the trace that is dumped to a binary file into a readable file. The data in this file is sorted in chronological order instead of being grouped by PID and TID, which occurs if you specify the -flow parameter.

-x firstRecord [-lastRecord]

Displays record numbers firstRecord to lastRecord.

-data

Includes any trace record data in the flow.

-t

Includes timestamps in sec:nsec format, if available.

-wc

Includes wall-clock timestamps, if available. To use this parameter, you must turn on the trace by also specifying the -t option, which captures timestamps.

-rds

Includes Relational Data Services (RDS) operator information, if available.

perfrep dumpFiledestFile

Formats the trace that is dumped to a binary file into a performance report text file.

-g

Groups the output by the combination of the trace's member (node) number, PID, and TID.

-sort count|timespent|timeelapsed

Sorts the output in one of the following ways:

count

The number of invocations of each function.

timespent

The time spent in each function. This is the default value.

timeelapsed

The elapsed time spent in each function.

-order asc|ascending|desc|descending

Specifies the order in which to sort the output data:

asc | ascending

Ascending order.

desc | descending

Descending order. This is the default.

print -stack recordID flowFile

Prints a backtrace of all stack frames for the specified record ID from the specified flow file. See Example 5 for further details.

clear

Clears the contents of the trace buffer, particularly just before connecting to a specific database. This option can be used to reduce the amount of collected information by clearing the buffers of accumulated useless information before a connection to the desired database is established.

stop

This collection option stops tracing on demand; all processes suspend tracing, but the contents of the trace buffer are preserved so that they can be dumped later. This action is in contrast to the off option, which disables the trace facility altogether.

off

Disables the trace facility. After the trace is dumped to a file, disable the trace facility by typing:

db2trc off

-u

Provides additional information about most of the command line options. General form of the command line entry is shown in Usage notes below. Here is an example to obtain more information about the dump command for the DAS instance:

db2trc das dump -u

Examples

Example 1

To capture a trace with wall-clock timestamps included, you must specify the -t option when you turn trace ON and you must specify -t -wc options with the flow option to output the wall-clock timestamps into the readable text file. The following is an example of the steps you can perform:

1. Turn trace ON, capture timestamp information, and specify the dump file name by executing the following command:

   db2trc on -t -f db2trc.dmp

2. After the trace period has ended, turn trace OFF by executing the following command:

   db2trc off

3. To format the binary dump (.dmp) file into a readable text file (for example, db2trc.flw), execute the following command:

   db2trc flow -t -wc db2trc.dmp db2trc.flw

   The following is an example of the output displayed when the formatting has been completed:

   Total number of trace records     : 3349
   Trace truncated                   : NO
   Trace wrapped                     : NO
   Number of trace records formatted : 43 (pid: 5414 tid 182967198368 node: 0)
   Number of trace records formatted : 2690 (pid: 29615 tid 182960067008 node: 0)
   Number of trace records formatted : 118 (pid: 5394 tid 183102335328 node: 0)
   Number of trace records formatted : 498 (pid: 29616 tid 182965078816 node: -1)

4. To view the contents of the readable text file db2trc.flw, execute the following command:

   more db2trc.flw

   The following is an example of the output displayed when reading the contents of the readable text file containing wall-clock timestamp information:

Example 2

The following are examples for the use of the -member trace mask

• db2trc on -member n1[,n2,n3,n64]
  This command allows specification of which database members or partitions to trace.

  Note: Must be an integer number. If multiple members are specified, they must be separated by a comma. You can specify up to 64 members.
• db2trc chg -member n1[,n2,n3,n64]

  This command allows you to change the member mask to the specified database members or partitions.

• db2trc stop -member n1[,n2,n3,n64]
  This command allows you to remove members from the member mask. When the last member is removed from the member mask, trace is fully stopped having the same effect as running db2trc stop

  • The member number issued by this command must exist in the current member mask. Running db2trc info will display the current member mask ).
  • If you run the db2trc on command without the-member option, there will be no members in the member mask. This means that all members are traced
  • When all members (defined by db2nodes.cfg file ) in the current host are specified in this command, it will have the same effect as running db2trc stop , in this case when running db2trc stop without the -member option, trace is fully stopped for all members on that host.
• db2trc off -member n1[,n2,n3,n64]
  This command allows removal of members from the member mask. When the last member is removed from the member mask, trace is turned off, this has the same effect as running db2trc off.

  • The member number issued by this command must exist in the current member mask. Run db2trc info to display the current member mask.
  • If you run the db2trc on command without the -member option, there will be no members in the member mask. This means that all members are traced
  • When all the members (defined by db2nodes.cfg file) in current host are specified in this command, it will have same effect as db2trc off. When running db2trc off without the -member option then trace is turned off on that host

• db2trc flw -member n1[,n2,n3,n64] and db2trc fmt -member n1[,n2,n3,n64]

  Running this command will allow you to specify which members to include in the formatted trace

Example 3

The following are examples for the use of the -member trace mask with the db2nodes.cfg defined as the following:

0 host1 0
1 host1 1
2 host1 2
3 host2 0
4 host3 0

• db2trc on[ chg] -member 1,2,3

  when -member is run the member number will be mapped to the related host name, specified by -member and then run on the related host by means of the rah (or the db2_all) command . For this example db2trc -member 1,2 will run on host1 and db2trc -member 3 will run on host2.

• db2trc on -host host1,host2

  db2trc on will run on both host1 and host2

• db2trc on -member all

  db2trc on will run on host1, host2, and host3

Example 4

The following are examples for the use of the -appid and -apphdl:

• db2trc on -appid appid1,appid2

  This command will turn on trace for specific application IDs. This supports up to 12 application IDs.

• db2trc chg -appid appid1,appid2

  This command will change (or reset) the specific application ID. To reset, run command db2trc chg -appid none. It will remove all application IDs in the mask.

• db2trc on -apphdl apphdl1,apphdl2,apphdl3

  This command will turn on trace for specific application handle. This supports up to 16 application handles.

• db2trc chg -apphdl apphdl1,apphdl2,apphdl3

  This command will change (or reset) the specific application handle. To reset, run command db2trc chg -apphdl none. It will remove all application handles in the mask

Example 5

The following is a sample flow file test.flw:

pid = 1608 tid = 47604608002368 node = 0

1           sqlossig entry [eduid 1 eduname db2sysc]
2           sqlossig exit [rc = 0x840F0001 = -2079391743 = SQLO_ACCD]
3           sqkfFastCommManager::ResourceSelfTuning entry [eduid 1 eduname db2sysc]
4           | sqlogmt entry [eduid 1 eduname db2sysc]
5           | sqlogmt exit
6           sqkfFastCommManager::ResourceSelfTuning exit
7           sqkfFastCommManager::CollectResourceUsageStats entry [eduid 1 eduname db2sysc]
8           | sqlogmt entry [eduid 1 eduname db2sysc]
9           | sqlogmt exit
10          sqkfFastCommMandager::CollectResourceUsageStats exit
11          sqkfFastCommManager::UpdateMemoryConsumptionStats entry [eduid 1 eduname db2sysc]
12          sqkfFastCommManager::UpdateMemoryConsumptionStats exit
13          sqleSyscUpdateDynamicVars entry [eduid 1 eduname db2sysc]
14          | sqloGetSysMonSetting entry [eduid 1 eduname db2sysc]
15          | | sqloGetEnvUnCached entry [eduid 1 eduname db2sysc]
16          | | | EnvPrfOpen entry [eduid 1 eduname db2sysc]
17          | | | | EnvKeyName entry [eduid 1 eduname db2sysc]

19          | | | | | | sqloxltc_app entry [eduid 1 eduname db2sysc]
20          | | | | | | sqloxltc_app exit
21          | | | | | sqloGetUserAttribByName data [probe 770]
22          | | | | | sqloGetUserAttribByName data [probe 820]
23          | | | | | | sqloxult_app entry [eduid 1 eduname db2sysc]
24          | | | | | | sqloxult_app exit
25          | | | | | sqloGetUserAttribByName exit
26          | | | | EnvKeyName exit
27          | | | EnvPrfOpen exit
28          | | sqloGetEnvUnCached exit
29          | sqloGetSysMonSetting exit
30          sqleSyscUpdateDynamicVars exit

To print a backtrace of all stack frames for line 5 from the test.flw file, execute the following command:

db2trc print -stack 5 test.flw

or

db2trc print -s 5 test.flw

The following is the output displayed:

pid = 1608 tid = 47604608002368 node = 0
3           sqkfFastCommManager::ResourceSelfTuning entry [eduid 1 eduname db2sysc]
4           | sqlogmt entry [eduid 1 eduname db2sysc]
5           | sqlogmt exit

Note that line 18 does not exist in the test.flw file. If the record ID specified does not exist, you will get an error message:

$ db2trc print -stack 18 test.flw
ERROR: Unable to find the Record ID 18 . Exiting.

Example 6

The following example shows the usage of the perfrep parameter:

1. Turn on the trace by issuing the following command:

   db2trc on -t

2. Perform DB2 operations, for example, by issuing the following command:

   db2start

3. To dump the trace information to a binary file, issue the following command:

   db2trc dump db2trc.dmp

4. To format the binary file into readable output that is grouped by member number, PID, and TID, issue the following command:

   db2trc perfrep -g db2trc.dmp db2trc.perfrep

   Sample output is as follows:

   Node : 0 , PID : 12648456 , TID : 258
   
     nCalls   TotalElapsed     AvgElapsed     TotalSpent       AvgSpent FunctionName
          2   22.163451643   11.081725821   22.163444161   11.081722081 sqlorqueInternal
          3    0.097682328    0.032560776    0.097682328    0.032560776 OSSHLibrary::load
          1    0.015628456    0.015628456    0.015579146    0.015579146 sqlnlsgetcpcc
          1    0.015929874    0.015929874    0.010516227    0.010516227 sqloRunInstance
          1    0.007650045    0.007650045    0.006686877    0.006686877 sqloexec
          1    0.004468380    0.004468380    0.004468380    0.004468380 sqlosearchpath
          1    0.004469429    0.004469429    0.002867957    0.002867957 sqloWatchDogSetup
          2    0.049929905    0.024964952    0.002643447    0.001321724 pdLogInternal
         10    0.002660025    0.000266002    0.002576667    0.000257667 sqloGetUserAttribByName
          3    0.002527244    0.000842415    0.002504231    0.000834744 sqloopenp
          2    0.001962144    0.000981072    0.001932671    0.000966335 sqloGetUserAttribById
          5    0.001829002    0.000365800    0.001344719    0.000268944 GlobalReg::GlobalReg
         15    0.001569204    0.000104614    0.001109852    0.000073990 sqlogmblkEx
          1    0.044276370    0.044276370    0.000982115    0.000982115 ossGetCPUInfo
          1   22.178500518   22.178500518    0.000686105    0.000686105 sqleWatchDog
          3    0.000667789    0.000222596    0.000667789    0.000222596 sqloAddOneReservedHandle
         40    0.000720231    0.000018006    0.000604822    0.000015121 GlobalReg::UnpackRecord
          5    0.002412400    0.000482480    0.000589444    0.000117889 GenRegBin::GetNext
   [....]
   Node : 0 , PID : 11731144 , TID : 2
   
     nCalls   TotalElapsed     AvgElapsed     TotalSpent       AvgSpent FunctionName
          1   18.300280961   18.300280961   18.300280961   18.300280961 sqloAlarmThreadEntry
          1   18.301955934   18.301955934    0.000651340    0.000651340 sqloEDUEntry
         10    0.000346784    0.000034678    0.000216876    0.000021688 sqlogmblkEx
          1    0.000475853    0.000475853    0.000161166    0.000161166 sqlo_create_init_EDU_data
         10    0.000157996    0.000015800    0.000140807    0.000014081 sqlofmblkEx
          1    0.000178087    0.000178087    0.000124753    0.000124753 sqloGetShrEDUWaitElem
          1    0.000129908    0.000129908    0.000081576    0.000081576 SMemBasePool::getNewChunkSubgroup
          1    0.000200591    0.000200591    0.000047364    0.000047364 sqlo_destroy_EDU_data
          1    0.000045839    0.000045839    0.000045839    0.000045839 sqlo_waitlist::post
          1    0.000031140    0.000031140    0.000024636    0.000024636 SMemSet::getChunksFromTree
          1    0.000033143    0.000033143    0.000018288    0.000018288 sqloFreeShrEDUWaitElem
          2    0.000016570    0.000008285    0.000016570    0.000008285 sqloMemProtEDU_init
          2    0.000013807    0.000006904    0.000013807    0.000006904 SqloMemController::updateCachedMemory
          1    0.000059408    0.000059408    0.000013568    0.000013568 sqloPostEDUWaitPost
          1    0.000017188    0.000017188    0.000011827    0.000011827 SMemSet::returnContiguousChunks
          2    0.000011225    0.000005613    0.000011225    0.000005613 sqlogmt2
   [....]

Example 7

The following example shows the usage of the formattedFlow parameter:

1. Turn on the trace by issuing the following command:

   db2trc on -t

2. Perform DB2 operations, for example, by issuing the following command:

   db2start

3. To dump the trace information to a binary file, issue the following command:

   db2trc dump db2trc.dmp

4. To format the binary file into a readable report, issue the following command:

   db2trc fflw db2trc.dmp db2trc.fflw

   Sample output is as follows:

   PID-TID  EduName  Node RecordNum   Function
   [...]
   12648456-258  db2wdog  [ 0] 19735       |||cryptContextInit entry
   12648456-258  db2wdog  [ 0] 19736       |||cryptContextInit data [probe 10]
   12648456-258  db2wdog  [ 0] 19737       |||cryptContextInit data [probe 100]
   12648456-258  db2wdog  [ 0] 19738       |||cryptContextInit exit
   12648456-258  db2wdog  [ 0] 19739       ||sqloWatchDogSetup data [probe 2]
   12648456-258  db2wdog  [ 0] 19740       ||sqloWatchDogSetup exit
   12648456-258  db2wdog  [ 0] 19741       ||sqlogmblkEx entry
   12648456-258  db2wdog  [ 0] 19742       |||sqloGetPrivatePoolHandle entry
   12648456-258  db2wdog  [ 0] 19743       |||sqloGetPrivatePoolHandle exit
   11731144-258  db2sysc  [ 0] 19744       sqloGetEnvInternal entry
   11731144-258  db2sysc  [ 0] 19745       sqloGetEnvInternal exit [rc = 0x870F0104 = -2029059836 = RC_ENV_NOT_FOUND]
   12648456-258  db2wdog  [ 0] 19746       ||sqlogmblkEx mbt [Marker:PD_OSS_ALLOCATED_MEMORY ]
   12648456-258  db2wdog  [ 0] 19747       ||sqlogmblkEx exit
   11731144-258  db2sysc  [ 0] 19748       sqloSystemControllerMain entry
   11731144-258  db2sysc  [ 0] 19749       |sqloChangeProcessName entry
   11731144-258  db2sysc  [ 0] 19750       |sqloChangeProcessName data [probe 5]
   11731144-258  db2sysc  [ 0] 19751       |sqloChangeProcessName exit
   11731144-258  db2sysc  [ 0] 19752       |sqloGetShrEDUWaitElem entry
   11731144-258  db2sysc  [ 0] 19753       ||sqlo_waitlist::initialize entry
   11731144-258  db2sysc  [ 0] 19754       ||sqlo_waitlist::initialize exit
   11731144-258  db2sysc  [ 0] 19755       ||sqlogmblkEx entry
   11731144-258  db2sysc  [ 0] 19756       ||sqlogmblkEx mbt [Marker:PD_OSS_ALLOCATED_MEMORY ]
   11731144-258  db2sysc  [ 0] 19757       ||sqlogmblkEx exit
   11731144-258  db2sysc  [ 0] 19758       |sqloGetShrEDUWaitElem data [probe 10]
   11731144-258  db2sysc  [ 0] 19759       |sqloGetShrEDUWaitElem data [probe 20]
   11731144-258  db2sysc  [ 0] 19760       |sqloGetShrEDUWaitElem exit
   12648456-258  db2wdog  [ 0] 19761       |sqloRunInstance data [probe 2]
   12648456-258  db2wdog  [ 0] 19762       |sqloRunInstance exit
   11731144-258  db2sysc  [ 0] 19763       |sqloGetKernelThreadIDFromEDUID entry [eduid 258 eduname db2sysc]
   [...]

Usage notes

db2trc on -i 8m -m "*.*.2.*.*" -t
db2start
db2trc clear
db2 connect to sample
db2trc dump dmp
db2trc off

STAGE #1 - COLLECTION
        Usage: db2trc [facility] <command> [-u]

          [facility]
                 db2 - DB2 instance (default)
                 das - DB2 Administration Server instance

          <command>
                 change - Change trace options
                 clear  - Clear the trace buffer
                 dump   - Generate trace dump file
                 info   - Information
                 off    - Disable the trace facility
                 on     - Enable the trace facility
                 stop   - Stop tracing

STAGE #2 - PARSING
        Usage: db2trc <command> [-u]

          <command>
                 ccfmt         - Dump and format a code coverage trace
                 flow          - Generate control flow diagram
                 format        - Format
                 formattedFlow - Generate global flow diagram
                 info          - Information
                 perffmt       - Format a performance trace
                 perfrep       - Format a performance report

For more information add the "-u" option to any of the above commands. 

When tracing the database server, it is recommended that the trace facility be turned on prior to starting the database manager. This is the most reliable method for the database manager, running on any UNIX and Linux platform, to be immediately aware of trace changes.

db2trc on -m "*.*.155.*.*"

When you know the specific database partitions that are involved in a problem, trace only those database partitions. Use the option db2trc on -member NN to specify which database partitions to trace.

When the problem is related to a specific application ID, you are able to limit trace only to that specific application ID by using the db2trc option db2trc on -appid <appID>.

When tracing an application on Windows operating systems, it is important that the db2trc.exe is started using the same path name where application will find the DB2 libraries. For example, if your DB2 libraries are located in d:\sqllib\bin then the db2trc.exe must be started as d:\sqllib\bin\db2trc.exe (Note: If d:\sqllib\bin is in your PATH then you do not need to fully qualify the db2trc.exe). Additionally, if the application is started and resolves the DB2 libraries using a UNC path name(\\<hostname>\<share>\my-dsdriver\bin) instead of a normal path then db2trc.exe must be started using the UNC path name as well (\\<hostname>\<share>\my-dsdriver\bin\db2trc.exe).