First steps: Generate resource-related performance reports from SMF records, particularly Top-n reports of CPU consumed, synchronous I/O waits, and elapsed times. Choose what window (e.g., Top-20, Top-50, etc.), what environment (e.g., development, test, etc.) and what intervals (e.g., daily, weekly) to report. Concentrate on studying the worst offenders. Remember that some long-running or resource-intensive processes—such as those that process large amounts of data or do extensive data analysis—won’t benefit from tuning. After you choose some resource “offenders,” direct your analysis toward possible resource trading as previously described.
Application tuning isn’t complex, though it encompasses many things. This type of tuning requires knowledge of multiple user applications and their characteristics, such as table access, batch windows, restart requirements, SLAs, and relevant DB2 configuration parameters.
What you analyze: The short list includes online transaction processing, data warehouse activity, vendor software package performance, deadlocks and timeouts, bind parameters, information in the DB2 subsystem Job Entry Subsystem (JES) log, units of work, commit frequency, referential integrity, and batch cycles. Each category provides a window into certain performance characteristics of an application or suite of applications.
Using what tools: Your most important tool will be the performance reports generated from SMF records. The Accounting reports are useful for analyzing specific plans and packages while the Statistics reports show system-level information. In addition, you should have available documentation related to standards for application unit-of-work and commit frequency and an enterprise data model (or, at a minimum, data models for the objects accessed by the applications you’re reviewing). You should create these if you lack them.
Where to look for the largest productivity gains: The best places for productivity gains are in addressing issues with SLAs. Often, this is related to either elongated elapsed times (especially for batch processes) or resource-intensive queries in an online environment. For long elapsed times use the performance reports to determine where the time is being spent. If the time is spent waiting for resources, consider ways to reduce the wait time. For resource-intensive queries consider ways to “trade” other available resources for the constrained resource, as previously discussed.
First steps: Develop Top-n reports for high-resource usage among your batch jobs, online transactions, and stored procedures. These can then be dealt with individually or in groups. You may discover that several of your top long-running processes execute similar SQL queries against the same tables. You must then investigate more deeply to determine if this is due to poorly written SQL, highly volatile resources (where deadlocks and timeouts elongate elapsed times), unavailable resources, or other causes.
Object tuning will involve one or more of the following: the enterprise data model, DB2 object creation parameter settings, standards for executable code, and options for enforcement of business and data integrity rules. Here, the focus is on object definitions and data availability. This requires an indepth knowledge of the various parameters and settings that control how and where objects (e.g., table space partitions, indexes, stored procedures, etc.) are created.
What you analyze: A typical list would include index management, table space partitioning, object configuration parameters (e.g., DASD allocations, embedded free space, etc.), data set placement, data modeling, and referential integrity.