db2trc controls the trace facility of a DB2® instance or the DB2 Administration Server (DAS).
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.
None
.-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-----'
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.
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.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.*.*"
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
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
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.db2trc flow db2trc.dmp db2trc.flw
db2trc off
db2trc das dump -u
db2trc on -t -f db2trc.dmp
db2trc off
db2trc flow -t -wc db2trc.dmp db2trc.flw
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)
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:
This command allows you to change the member mask to the specified database members or partitions.
Running this command will allow you to specify which members to include in the formatted trace
0 host1 0
1 host1 1
2 host1 2
3 host2 0
4 host3 0
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 will run on both host1 and host2
db2trc on will run on host1, host2, and host3
This command will turn on trace for specific application IDs. This supports up to 12 application IDs.
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.
This command will turn on trace for specific application handle. This supports up to 16 application handles.
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
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
db2trc print -stack 5 test.flw
ordb2trc print -s 5 test.flw
pid = 1608 tid = 47604608002368 node = 0
3 sqkfFastCommManager::ResourceSelfTuning entry [eduid 1 eduname db2sysc]
4 | sqlogmt entry [eduid 1 eduname db2sysc]
5 | sqlogmt exit
$ db2trc print -stack 18 test.flw
ERROR: Unable to find the Record ID 18 . Exiting.
db2trc on -t
db2start
db2trc dump db2trc.dmp
db2trc perfrep -g db2trc.dmp db2trc.perfrep
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
[....]
db2trc on -t
db2start
db2trc dump db2trc.dmp
db2trc fflw db2trc.dmp db2trc.fflw
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]
[...]
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.
In
Stage #2 - Parsing section above, the command ccfmt dumps
and formats a "code coverage trace". The code coverage trace is an
extension of db2trc that keeps a count of function
entries, exits, probe points, and code paths. It can be used to gather
statistics on what functions are being heavily used, or which functions
are not being touched during tests. 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).