Technical Blog Post
How to set a large trace buffer in DB2 LUW
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
To set a 512 MB of trace buffer,
To set a 1 GB of trace buffer,
Then the db2 needs to be restarted to make that effective,
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-126.96.36.1994927-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.