Technical Blog Post
Abstract
How to set a large trace buffer in DB2 LUW
Body
This might be commonly used now a days. But, questions on this come up over and over still. So, trying to put few lines on that.
Generally, the db2trc has a maximum buffer size of 128m and it can differ based on platforms/levels too.
In certain platform and levels it cannot set to even 128m unless it's set before a db2start.
Also, if a trace buffer is set to a smaller value example 64m for the first time after a db2start it cannot be increased or changed to a different value unless the db2 is recycled.
There are good blogs and technotes where the reason is explained in details.
So, here we want to know how a trace buffer could be changed to a higher value than 128m
To do that, first a db2 registry parameter has to be set which is called, DB2TRC_DEF_BUFFSIZE
Example,
To set a 512 MB of trace buffer,
db2set DB2TRC_DEF_BUFFSIZE=512m
To set a 1 GB of trace buffer,
db2set DB2TRC_DEF_BUFFSIZE=1g
Then the db2 needs to be restarted to make that effective,
db2stop/db2start
After that the trace could be set using (example 1 GB),
$ db2trc on -l 1G
Trace is turned on
$ db2trc info
Marker : @TRACE@
Trace version : 7.0
Platform : AIX 64BIT
Build level : s141128
maxBufferSize : 1073741824 bytes (1024 MB)
auxBufferSize : 0 bytes (0 MB)
allocationCount : 2
DB2TRCD pid : 0
Trace destination : <shared memory buffer>
numSuspended : 0
Trace starting time : 2017-02-06-12.59.59.394927-300
Buffer size : 1073741824 bytes (1024 MB)
Allow buffer to wrap : yes
Mask : *.*.*.*.*
Timestamps : disabled
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 handle mask : none
Application ID mask : none
Point to be noted, even after setting this to 1GB using the registry if for the very first time after a db2start the buffer size is set to a smaller value it cannot be changed back unless the db2 is recycled again.
Sometimes question arise about why not use -f option just to put unlimited trace in a file. The answer to that usually will be, with the file option there could be additional overheads for the output file I/O which is not suggested to be used while a performance issue is being diagnosed. If it's not related to performance troubleshooting then the file option is o.k. to be used. But, -f option is still to be used with caution as that can create huge output file resulting disk full or sometimes that much large file don't become useful for analysis.
UID
ibm11140622