db2trc - Trace command

Authorization

Required connection

None

Command syntax

parsing-options

location-options

mask-options

flow-parsing

format-parsing

fflw-parsing

perfrep-options

collection-options

on-options

trace-options

performance-counter

print-options

Notes:

• ¹ 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 does not work with the -perfcount option.

-apphdl

Specifies which application handles to trace. The -apphdl option works with the on and change options. -apphdl does 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 the following examples 2 and 3 for further details. The -member option does not work with the -perfcount option.

db2

Specifies that all trace operations are performed on the Db2 instance. This is the default.

das

Specifies that all trace operations are performed on the Db2 Administration Server instance.

cf

Specifies that all trace operations are performed on the CF server. The -change, -clear and -stop options does not work with this option.

cfcli

Specifies that all trace operations are performed on the members.

Note: To enable CF client trace, the environment variable CA_TRACE_KEY_FILE must be set to an existing file. This file is used by the CF client library to create a SHARED MEMORY set. This set is used collect the trace data. Additionally the permission and owner information for this file is used to set the permission and ownership of the created SHARED MEMORY. Any process that starts with this environment variable set to the same value will use the same SHARED MEMORY segment. The default name used by Db2 is ${INSTHOME}/sqllib/tmp/.CF_DB2TRACE.key. This environment variable is set automatically in the db2profile and db2cshrc files. However, if these are not being sourced then you must manually set this value.

Note: The CF client trace buffer cannot exceed 10 MB.

on

Use this parameter to start the trace facility. See the following Shared trace-options section 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 MB buffer:

db2trc on -l 4m

The default and maximum trace buffer sizes vary by operating system. 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, you must specify a fully-qualified file name, and if -l or -i is used with -f option, their buffersize values limit the size of the file on disk. -l preserves the last trace records and is allowed to wrap within the file. -i preserves the initial trace records and stops 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.

-clitracelevel loglevel

This parameter controls the logging level of the information dumped to CLI trace for CLI/ODBC driver and CLI applications. This option is ignored if -cli is not supplied while trace is being turned on. Possible values loglevel can take are [0-3] where each value is defined as follows:

• 0 - Log all the information captured (current default behavior).
• 1 - Do not log data bind-in data that is supplied by the application. Bind in data is the data that is supplied by application to client through API calls like SQLBindParameter(), SQLBindCol(), SQLExtendedBind(), and so forth, via the rgbValue application data pointers.
• 2 - Do not log bind-out data that is returned to application. Bind-out data is the data that is returned by the client to application through the application data pointers set by using API calls SQLBindParameter(), SQLBindCol(), SQLExtendedBind(), and so forth, via the rgbValue application data pointers.
• 3 - Do not log bind-in data that is supplied by and bind-out data that is returned to application.

The follow example shows the usage of the -clitracelevel parameter:

db2trc on -cli -clitracelevel 1 -f dumpFile
db2trc off
db2trc fmt -cli dumpFile clitrcfile.txt

This option is not applicable if -cli is not specified while turning on the trace.

-pattern

This parameter allow users to log information into the CLI trace based on a search pattern. The option can take multiple search patterns separated by ;(semicolon) character. The multiple search patterns should be enclosed within double quotes. Up to 10 search patterns with not more than a combined 255 characters can be specified with the patterns separated by semicolon character. If there are more than 10 search patterns, only the first 10 search patterns will be considered and others will be ignored. If there are more than 255 characters, an appropriate error will be returned and no action will be taken. This option can be specified only during the collection of the dump file & not during the formatting of the CLI trace from the generated dump file. The search pattern can also have a format as in SELECT%table1. This means, search for the SELECT keyword followed by any character and then followed by table1 keyword. The following examples show the usage of the -pattern parameter:

• Example 1

  The following command searches for all statements that have the "tab1" pattern. All the information on the statement handle that matched the "tab1" pattern is logged into the dump file.

  db2trc on -cli -pattern tab1 -f file1.dmp

• Example 2

  The following command searches for all statements that have the "tabl" or "tab2" pattern. All the information on the statement handle that matched the "tab1" or "tab2" pattern is logged into the dump file. The search will be made for every SQL being prepared or executed, and the first matched pattern will be logged.

  db2trc on -cli -pattern "tab1;tab2" -f file1.dmp

• Example 3
  The following command searches for all statements that have the SELECT followed by the "tab1" pattern. All the information on the statement handle that matched the “SELECT” keyword followed by any character, and then followed by the "tab1" keyword will be logged into the dump file.

  db2trc on -cli -pattern SELECT%tab1 -f file1.dmp

This option is ignored if -cli option is not specified while turning on the trace.

-dumpstmt

Obtain the details of all statement handles that were allocated by the CLI driver prior to enabling the Db2 trace. The details include all prepared SQL statements for the allocated statement handles, which were allocated prior to enabling the Db2 trace. You can specify the -dumpstmt option with or without the -cli option. When the -dumpstmt option is specified with the -m option, the masks must include the component 191 (*.*.191.*.*) for the -dumpstmt option to take effect.

CLI and ODBC applications must be thread safe and your operating system must support multithreading for the Db2 trace to be enabled with the -dumpstmt option. The -dumpstmt option traces applications that use the CLI driver.

-errors

Trace only errors and nonzero return codes on function exit. You cannot specify this option with the -debug or -perfcount options.

-lc

List all of the valid CF trace components and trace levels for either the CF server or CF client.

-id [cf-id]

The CF server ID as specified in the db2nodes.cfg file. By default the operation is performed against all CF servers. However, if this option has been specified, only the specified CF server will be affected.

-trccom

Display network requests in the CLI/ODBC trace.

-stoptrc [-sqlcodes]

Stops the trace when one of the SQLCODEs specified by -sqlcodes is hit. -sqlcodes is a comma separated list of up to 20 SQLCODEs that must be specified as signed integers.

change

Use the change option to change the trace options that are in effect. See the Shared trace-options section for a list of parameters. Cross reference to the element id 'mask_option'

Shared trace-options

Common trace options shared between on and change.

-m mask

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

Values for the mask are 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. You can use an asterisk (*) to match anything. You can specify field values by their names or corresponding numbers. You can use short forms of mask that consist of the products, components, or functions part names. Example: -m "entry,exit.*.SQLO,SQLE.*.*"

Setting the mask to "*.*.*.*.*" is equivalent to not specifying a mask.

For Db2 pureScale® environments, the mask has been overloaded so that tracing of the CF server, CF client, or both can be enabled. The mask is used somewhat differently when dealing with the CF.

You can find the values for the mask by using the -lc option. For the CF server and CF client the trace mask pieces have the following meaning:

• types is always *
• products is either CF or CFCLI (case does not matter)
• components is any combination of one or more CF server or CF client components as can be listed by using the -lc flag
• functions is always * because the CF does not support this feature
• categories is any combination of one or more CF server or CF client trace level as can be listed by using the -lc flag

Example: -m "*.CF.svr_list.*.CF_TRACE_ERROR,CF_TRACE_ALL"

-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 IDs for a process ID. A period separates multiple tids for a pid. A comma separates each pid tid pair. A maximum number 64 tids 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.

-perfcount

This mode records the total number of times each function is called.

-t

This option records the total amount of time spent in each function.

-m mask

This option reduces the amount of data that is collected or formatted. For more information, see the description for the -m option under Shared trace-options.

-edu

This option distinguish between EDUs when recording function calls.

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 puts 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 specified.

-q

Quiet mode.

ccfmt destFile

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

flow dumpFile destFile

After the trace is dumped to a binary file, format it 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]

Only show record numbers firstRecord to lastRecord.

-data

Include any trace record data in the flow.

-t

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

-wc

Include wall-clock timestamps, if available. To use 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

Generate a separate destination file for each distinct flow.

-rds

Include RDS operators information, if available.

format dumpFile destFile

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

-x firstRecord [-lastRecord]

Only show record numbers firstRecord to lastRecord.

-r

Output in reverse order.

-xml

Output data in xml parsable format.

-c

Format communications buffers.

-cli

Format the trace binary files to show the CLI driver functions and the CLI application buffer in a readable text file. The destFile variable can be one of the following objects:

• Name of the formatted trace file that is to be created.

  db2trc fmt –cli dumpFile clitrcfile.txt

• Name of the formatted trace file with the path name.

  db2trc fmt –cli dumpFile /TMP/clitrcfile.txt
  

• An existing directory where user has write permission.

  db2trc fmt –cli dumpFile /TMP
  

If the destFile directory exists and user has write permission to the directory, the trace is formatted into separate text files that are based on the process ID and thread ID. The trace output of the Db2 trace that is enabled with the -cli option and formatted with the -cli option is identical to the traditional CLI trace. If you specified -t when you started db2trc, then the trace will include a time stamp of the following format:

[processor_ticks.milliseconds - mm/dd/yyyy hour:min:sec.milliseconds]

The names of the trace files in the destFile directory consists of p<pid>t<tid>.cli. If there is a directory with same name as the destFile value and the user does not have write permission to the directory, an error is returned.

CLI trace files generated using db2trc command contains db2 trace record numbers. The record numbers for ODBC APIs in CLI trace are same as the record number of these APIs present in generated flow and format files.

-ncf

Do not use component custom formatting.

-errors

Trace only errors and nonzero return codes on function exit.

-global

Specifies that db2trc is also 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 is 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 the db2trc command uses 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. Use this option to reduce the amount of collected information by clearing the buffers of accumulated useless information before a connection to the wanted 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

-id [cf-id]

The CF server ID as specified in the db2nodes.cfg file. By default the operation is performed against all CF servers. However, if this option is specified, only the specified CF server is affected.

-u

Provides additional information about most of the command line options. The general form of the command line entry is shown in the following Usage notes section. 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:

   pid = 5414 tid = 182967198368 node = 0
   
   1    0.000000000  clp_bp_con data [probe 21] 2009-06-16-11.02.32.38407400
   2    0.000038000  | sqlossig entry 2009-06-16-11.02.32.38411200
   3    0.000050000  | sqlossig exit 2009-06-16-11.02.32.38412400
   4    0.000057000  | sqlorque2 entry 2009-06-16-11.02.32.38413100
   5    0.000062000  | | sqlogmblkEx entry 2009-06-16-11.02.32.38413600
   6    0.000070000  | | | sqloGetPrivatePoolHandle entry 2009-06-16-11.02.32.38414400
   7    0.000077000  | | | sqloGetPrivatePoolHandle exit 2009-06-16-11.02.32.38415100
   8    0.000088000  | | sqlogmblkEx mbt [Marker:PD_OSS_ALLOCATED_MEMORY] 2009-06-16-11.02.32.38416200
   9    0.000092000  | | sqlogmblkEx exit 2009-06-16-11.02.32.38416600
   10   0.000094000  | | sqlorqueInternal entry 2009-06-16-11.02.32.38416800
   11   0.000096000  | | | sqloSetAlarmApp entry 2009-06-16-11.02.32.38417000
   12   0.000099000  | | | | sqloSigMask entry 2009-06-16-11.02.32.38417300
   13   0.000101000  | | | | sqloSigMask exit 2009-06-16-11.02.32.38417500
   14   0.000103000  | | | | sqlohsig entry 2009-06-16-11.02.32.38417700
   15   0.000105000  | | | | sqlohsig exit 2009-06-16-11.02.32.38417900
   16   0.000108000  | | | sqloSetAlarmApp exit 2009-06-16-11.02.32.38418200
   2825 5.000561000  | | | sqloClearAlarmApp entry 2009-06-16-11.02.37.38463500
   2826 5.000576000  | | | | sqloSigMask entry 2009-06-16-11.02.37.38465000
   2827 5.000579000  | | | | sqloSigMask exit 2009-06-16-11.02.37.38465300
   2828 5.000582000  | | | | sqlohsig entry 2009-06-16-11.02.37.38465600
   2829 5.000585000  | | | | sqlohsig exit 2009-06-16-11.02.37.38465900
   2830 5.000587000  | | | sqloClearAlarmApp exit 2009-06-16-11.02.37.38466100
   2831 5.000589000  | | sqlorqueInternal exit [rc = 0x870F00B9 = -2029059911 = SQLO_SEM_TIMEOUT] 2009-06-16...
   2832 5.000592000  | | sqlofmblkEx entry 2009-06-16-11.02.37.38466600
   2833 5.000597000  | | sqlofmblkEx mbt [Marker:PD_OSS_FREED_MEMORY] 2009-06-16-11.02.37.38467100
   2834 5.000599000  | | sqlofmblkEx exit 2009-06-16-11.02.37.38467300
   2835 5.000601000  | sqlorque2 exit [rc = 0x870F00B9 = -2029059911 = SQLO_SEM_TIMEOUT] 2009-06-16-11.02.37.38467500
   2836 5.000614000  clp_bp_con data [probe 21] 2009-06-16-11.02.37.38468800
   2837 5.000617000  | sqlossig entry 2009-06-16-11.02.37.38469100
   2838 5.000620000  | sqlossig exit 2009-06-16-11.02.37.38469400
   2839 5.000623000  | sqlorque2 entry 2009-06-16-11.02.37.38469700
   2840 5.000626000  | | sqlogmblkEx entry 2009-06-16-11.02.37.38470000
   2841 5.000628000  | | | sqloGetPrivatePoolHandle entry 2009-06-16-11.02.37.38470200
   2842 5.000631000  | | | sqloGetPrivatePoolHandle exit 2009-06-16-11.02.37.38470500
   2843 5.000636000  | | sqlogmblkEx mbt [Marker:PD_OSS_ALLOCATED_MEMORY] 2009-06-16-11.02.37.38471000
   2844 5.000638000  | | sqlogmblkEx exit 2009-06-16-11.02.37.38471200
   2845 5.000640000  | | sqlorqueInternal entry 2009-06-16-11.02.37.38471400
   2846 5.000643000  | | | sqloSetAlarmApp entry 2009-06-16-11.02.37.38471700
   2847 5.000646000  | | | | sqloSigMask entry 2009-06-16-11.02.37.38472000
   2848 5.000647000  | | | | sqloSigMask exit 2009-06-16-11.02.37.38472100
   2849 5.000649000  | | | | sqlohsig entry 2009-06-16-11.02.37.38472300
   2850 5.000651000  | | | | sqlohsig exit 2009-06-16-11.02.37.38472500
   2851 5.000654000  | | | sqloSetAlarmApp exit 2009-06-16-11.02.37.38472800

Example 2

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

• db2trc on -member n1[,n2,n3,n64]
  Use this command to specify 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]

  Use this command to change the member mask to the specified database members or partitions.

• db2trc stop -member n1[,n2,n3,n64]
  Use this command to remove members from the member mask. When the last member is removed from the member mask, the trace is fully stopped, which has the same effect as running the db2trc stop command.

  • The member number issued by this command must exist in the current member mask. Running db2trc info displays 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 has the same effect as running db2trc stop . In this case when running db2trc stop without the -member option, the trace is fully stopped for all members on that host.
• db2trc off -member n1[,n2,n3,n64]
  Use this command to remove members from the member mask. When the last member is removed from the member mask, the trace is turned off, which 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 that are defined by the db2nodes.cfg file in the current host are specified in this command, it has the same effect as db2trc off. When running db2trc off without the -member option, the trace is turned off on that host

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

  Use this command 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 data:

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 is 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 runs on host1 and db2trc -member 3 runs on host2.

• db2trc on -host host1,host2

  db2trc on runs on both host1 and host2

• db2trc on -member all

  db2trc on runs on host1, host2, and host3

Example 4

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

• db2trc on -appid appid1,appid2

  This command turns on the trace for specific application IDs. The db2trc command with the -appid parameter supports up to 12 application IDs.

• db2trc chg -appid appid1,appid2

  This command changes or resets the specific application ID. To reset, run thedb2trc chg -appid none command. This command removes all application IDs in the mask.

• db2trc on -apphdl apphdl1,apphdl2,apphdl3

  This command turns on a trace for the specific application handle. This command supports up to 16 application handles.

• db2trc chg -apphdl apphdl1,apphdl2,apphdl3

  This command changes or resets the specific application handle. To reset, run the db2trc chg -apphdl none command. This command removes all application handles in the mask

Example 5

The following example 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 that is 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.

CF and CFCLI usage examples

Example 6

To turn a trace ON for the xport_common (CF_TRACE_WARNING), srv_init (CF_TRACE_PATH|CF_TRACE_ERROR), and srv_common (CF_TRACE_ALL) components, run the following command:

db2trc cf on -m "*.CF.xport_common,srv_init,srv_common.*.CF_TRACE_WARNING,0x09,CF_TRACE_ALL"

After running the command, trace is OFF for all other components.

Example 7

To turn trace ON for all CF client components at the CF_TRACE_ALL level, run the following command:

db2trc cfcli on

Example 8

To turn trace ON for the srv_list (CF_TRACE_ERROR) component and all the other components at the CF_TRACE_ALL level, run the following command:

db2trc cf on -m "*.CF.srv_list.*.CF_TRACE_ERROR,CF_TRACE_ALL"

Example 9

To turn trace ON for all CF components at the CF_TRACE_WARNING or CF_TRACE_DEBUG level, run the following command:

db2trc cf on -m "*.CF.*.*.CF_TRACE_WARNING|CF_TRACE_DEBUG"

Example 10

To turn trace ON for all CF servers listed in the db2nodes.cfg file and all the CF components at the CF_TRACE_ALL level, run the following command:

db2trc cf on

Example 11

To turn trace ON for only CF server 129 listed in the db2nodes.cfg file and all the CF components at the CF_TRACE_ALL level, run the following command:

db2trc cf on -id 129

Example 12

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 13

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 RecNum 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

1. Turn tracing on, which immediately begins the collection of the specified data and storage of it in the buffer after the Db2 instance is started
2. Clear the buffer before connecting to the database
3. Dump the binary format data into a dump file
4. Turn tracing off
5. Format the dump file into an easily readable text destination file

1. Stop the Db2 instance
2. Turn tracing on with the -l parameter and set the maximum trace buffer size
3. Start the Db2 instance
4. Clear the trace buffer

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
                 info    - Information
                 perffmt - Format a performance trace

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 before 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 the specific database partitions involved in a problem are known, only trace that database partition. 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>.