Bookmark and Share
RSS

Recent Posts

Monitoring Index Cleanup Threads

December 13, 2016

I was recently asked about activating a trace that sends information about DB2 11 index cleanup threads to the IBM System Management Facility (SMF).

I've written in detail about index cleanup threads, but the short answer is that DB2 provides a trace record (IFCID 377) that monitors pseudo delete daemon cleanup.

To learn more about this trace and see a sample Omegamon report, go here. I looked at the report myself, and this sentence jumped out:

This record is not associated to a trace class.

If you haven't setup traces before -- or even if you haven't done it in awhile -- that sentence is a little confusing. So as a first step, you may want to review the different types of DB2 traces.

The purpose of starting a performance trace is to conduct basic performance analysis and tuning. Starting a “trace class” launches a group of IFCIDs containing the information you're looking for. DB2 traces are started with the –START TRACE command.

The IBM Knowledge Center lists the performance trace classes (1 through 32) and provides a short description of the information each trace class collects. For instance, if I want to collect information related to data set open and close activity, I'd start performance trace class 19, which would start IFCIDs 0370 and 0371.

I gave a broader description of all the different trace classes and IFCIDs. But because I wrote about this back at DB2 9, you should also check out the DB2 12-specific info at the Knowledge Center.

But let's get back to the original question: How do you start trace 377 and have the information written out to SMF? Because it's not associated to a trace class, you won't find anything about IFCID 377 in the aforementioned list of trace classes. Keep in mind though, that of the 32 trace classes, the last three -- 30, 31 and 32 -- are considered local use. So when starting a trace with IFCID 377, you can simply associate it to one of the open classes.

The example that follows will start a performance trace to collect IFCID 377 and write the information to SMF. Because the system is sharing data, SCOPE(GROUP) will be included to ensure that all members have the trace running:

    -START TRACE(PERFM) DEST(SMF) CLASS(30) IFCID(377) SCOPE(GROUP)

If you've monitored index cleanup threads, please share your experiences in comments.

Posted December 13, 2016 | Permalink

comments powered by Disqus