Hiding at the end of the stand-alone section in the DB2 Utilities Guide is a tool nominally documented for IBM Support guided use. It can be used to trigger a dump when traced conditions occur to help with targeted diagnostics.
It can also start additional traces and capture specific IFCID data instead, and this can be quite handy when trying to understand how IFCID data is put together. It can also simply be used to capture specific IFCID trace data.
The following are a set of examples, building up the complexity of what we are doing with DSN1SDMP:
Capture Trace Data
In this example, we are capturing the IFCID196 for the first occurrence of a timeout:
The timeout is reported by IFCID 196 (cf SDSNIVPD(DSNWMSGS)) which we are explicitly tracing. Note all trace start and stop requests are reported with IFCID 4 and 5 respectively to the same output. This is done to ensure that the trace qualification is recorded with the output.
All DB2 IFCID trace records take the following form:
Writer Header
Self-Defining Section
Data Section 1
:
Data Section n
Product Section
Usually when we’re looking at IFICID data, we will start with the Writer Header (SMF writer, IFI writer, etc). DSN1SDMP performs selection processing with the offset pointer starting at the beginning of the Self-Defining Section.
The given SELECT statements are:
SELECT
P4,00
DR,04,X’00C4’
These mean:
- “P4,00” : Position the pointer using the offset in the 4-byte field in offset 0. This is the offset to the product section QWT02PSO (cf SDSNMACS(DSNDQWT0))
- “DR,04,X’00C4’” : Now in the Product Section Standard Header – QWHSTYP = 1 (cf SDSNMACS(DSNDQWT0)) – check that the value at offset 4 (i.e. QWHSIID, the IFCID number) is x’00C4’ (196). This prevents us triggering from the –START TRACE command IFCID 4.
These are used to ensure that we are only trapping and triggering on the specific trace events that we are after – i.e. just IFCID 196, and not IFCIDs 4 and 5.
Limiting Captured Trace Data
With the SELECT statement, we can augment the filtering available in the START TRACE command by providing additional tests to be performed on the IFCID data. In the previous example, we might want to limit the IFCID 196 records captured to just those that apply to a particular database.
Looking at IFCID 196 (cf SDSNMACS(DSNDQW02)) we can see that field QW0196KD (at offset 12) is the DBID for the locked resource. From SDSNIVPD(DSNWMSGS) we can see that this is in data section 1, so…
SELECT
P4,00
DR,00,X’00C4’
P4,08
DR,12,X’0153’
The second selection stanza positions the pointer using the value at absolute offset 8, i.e. the offset of the first data section. We then check that the DBID = 339 (x’153’).
Capturing More Events
By default, DSN1SDMP will end at the first qualified captured event. You might be interested in more than one of these, though. In our example, we want to see all of the timeouts that occur for SQL against our database, so that we can review the information at the end of the day.
To do this, we add a FOR statement, e.g.
Note that 32767 is the maximum supported number of events.
To end the job before the event limit is reached, you must stop the trace that DSN1SDMP is using.
Simple Gotchas
These apply to any monitoring solution that makes use of tracing to OPx:
- No spare OPx – there are only 8, so do a “–DIS TRACE(*)” before you start to verify that one is free. If you’re using the ACTION(STTRACE) to start another trace to capture additional data, you will need 2 free trace OPx
- Default buffer size (for OPx) is too small for your output rate / volume. Check DSN6SYSP macro ZPARM MONSIZE and optionally increase the buffer size in the “-START TRACE” command, e.g.-START TRACE(PERFM) DEST(OPX) AUTHID(FRED) IFCID(63) BUFSIZE(10240)starts the trace with a 10MB buffer.