(Application Developer) Test application performance
Option | Use |
|---|---|
PARM=RBTEST3 | Identify this test run with user initials and sequence number |
TYPE=DETAIL | Relatively low overhead (panel default includes SQL) |
START= | Leave blank to start immediately |
STOP=30 | Leave default trace duration of 30 minutes |
WRAP=YES | Keep the latest occurrences |
DB2AUTH=ABG01 | Select own Db2 work by AUTHID |
STORAGE=1000K | Trace display buffer space is in BBI-SS PAS extended private area |
LOGTRAC=Y | Log to a single dynamically allocated data set |
TITLE=‘TEST 3’ | User-specified title to identify log data set |
A detail trace answers a great many questions with relatively low system overhead. It selectively traces the key events in the life of a query or thread, including all SQL. For example, sorts invoked, elapsed and CPU times, and the row statistics showing activity per SQL statement can identify problem areas quickly. It also includes EXPLAIN data for dynamic SQL and binds of static plans. In addition, multiple index access path processing can be analyzed both when it is successful and when it fails.
Application developers now have an easy way of testing different versions of SQL and comparing the results. All the necessary information for every test iteration can be gathered in one trace showing SQL text, EXPLAIN data, and actual execution performance. The data can be reviewed online during the testing, recalled online from the log at a later time, or printed for more thorough analysis. A series of tests can be made over several days (for example, identified as TEST1, TEST2, and so on) and recalled online or printed for performance comparisons.
More data can be collected with the higher levels of detail trace (TYPE=D,SQL | SCAN | IO | LOCK | DDF | DDFVTAM):
- SCAN is needed occasionally to pick up the scans and show specific table and index usage.
- IO may be requested less frequently to trace the I/O patterns. However, this area is the most subject to change between test and production because the data access and buffering varies greatly with table size and the amount of concurrent activity.
- LOCK is needed only to check out locking patterns.
- DDF is needed to trace distributed SQL, either as requestor or as server, in this location.
- DDFVTAM is needed to analyze the VTAM-related activity caused by distributed SQL.
A summary trace can be run after the SQL statements are checked. This trace provides elapsed and CPU times that are not artificially increased by the Db2 performance trace overhead.
When the occurrences that need to be evaluated are collected, the trace (and its overhead) can be stopped with a Z line command. The data from stopped traces can be browsed until the request is purged, or WRAP can be changed to NO with a modify trace request. This request prevents previously collected data from being overwritten until it can be analyzed. Tracing can then be restarted with a modify trace request to change WRAP to YES. However, tracing overhead continues during this whole period.
When multiple programs are part of one plan, activity by program can be analyzed with the STRAC display. It presents an SQL summary by program for the total plan, with percent of total calculations of elapsed and CPU times per SQL statement and program. This immediately points out where the most time is being spent. Also, in the event traces (shown in the DTRAC displays), an identifier, which is appended to each SQL statement number if it is in a different program than the previous SQL. This shows the logical flow between programs. By expanding from one statement in the SQL summary to DTRAC, you can view just the detail events from that one program (DBRM).
Because this trace was logged, any of the online displays can be printed for offline analysis. You can submit a print job for this log directly from the HT view or by tailoring and submitting a batch job.