DB2 Version 9.7 for Linux, UNIX, and Windows

Performance reports for DB2 traces

You can create a performance report for a DB2® trace by using the db2trc command with the perfrep parameter.

You can issue the db2trc command with the perfrep parameter for dump files that you created by issuing the db2trc command with the -t option. The new feature eliminates the excessive processor usage that was previously caused by executing a performance trace in addition to the regular trace for diagnostic purposes.

The perfrep report displays the time that was spent in each caller function, in addition to the elapsed time that was spent to execute the trace. You can sort the output by the number of invocations, the time that was spent in each function, or the elapsed time that was spent, in ascending or descending order. By default, the output is sorted by the time that was spent in each function, in descending order. You can also group the output by using the combination of member (node) number, process ID (PID), and thread ID (TID).

Example

In the following example, the db2trc command is run with the perfrep parameter:

db2trc perfrep trc.dmp perfrep.out

Sample output in the perfrep.out file is as follows:

nCalls TotalElapsed   AvgElapsed   TotalSpent    AvgSpent     FunctionName
30     128.900145590  4.296671520  128.900122035 4.296670735  sqlorqueInternal
22     35.070790711   1.594126850  35.070442569  1.594111026  sqlo_waitlist::timeoutWait
5      33.333113807   6.666622761  33.333113807  6.666622761  OSSHIPCWaitpost::wait
6      20.412543399   3.402090567  20.412527985  3.402087997  sqlorest
1      18.300280961   18.300280961 18.300280961  18.300280961 sqloAlarmThreadEntry
3      18.248856592   6.082952197  18.248856592  6.082952197  sqloReadNamedPipe
1      14.907893602   14.907893602 14.907893602  14.907893602 OSSHIPCSemaphore::wait
1      14.888266323   14.888266323 14.888030081  14.888030081 sqloSSemP
7      8.131142984    1.161591855  8.130613854   1.161516265  sqlowchd
4      5.736910141    1.434227535  5.736471457   1.434117864  sqloWaitIPCWaitPost
1      21.967713234   21.967713234 3.600555023   3.600555023  sqleSysCtlr
115    1.111718480    0.009667117  1.105887133   0.009616410  sqlnlsgetcpcc
34     0.701106437    0.020620778  0.701106437   0.020620778  OSSHLibrary::load
1      0.157144687    0.157144687  0.143064433   0.143064433  cryptContextRealInit
4      0.098807890    0.024701972  0.093563598   0.023390899  sqloLoadModule
25     0.131057287    0.005242291  0.079349292   0.003173972  NetlsRequestLicense
3245   0.093867246    0.000028927  0.067123446   0.000020685  hregReadBlock
97     0.062272831    0.000641988  0.061129273   0.000630199  sqlnlscmsg
3      0.068937667    0.022979222  0.058964884   0.019654961  sqloexec
3      0.057550155    0.019183385  0.052918398   0.017639466  sqkfDynamicResourceMgr::InitResourceManager