(Re) Introducing the SQL Dashboard in OMEGAMON XE for DB2 Performance Expert
Steve Fafard 1200005BEJ Visits (9701)
Many IBM® Tivoli® OMEGAMON® XE for DB2® Performance Expert on z/OS® clients are not aware of the built-in SQL Dashboard capability available with the product. This article highlights this industry differentiating capability.
OMEGAMON XE for DB2 Performance Expert is often referred to as a DB2 system monitor which focuses on threads (from the application perspective) and the DB2 address space from the system resource usage perspective, e.g. buffer pools, system-wide parameters (called DSNZPARMs), memory usage, etc. One of the most critical components of a DB2 application with the most potential to impact application and system performance is the SQL statement. A SQL statement is the “language” used to talk to DB2 and takes the form of verbs like SELECT, UPDATE, DELETE, etc. This is the key tuning element of a DB2 application and is where DBAs spend a majority of their time working with application programmers to tune their applications. There are tools provided by IBM® – namely IBM DB2 Query Monitor on z/OS and SQL Performance Analyzer (also both capable of being integrated with OMEGAMON XE for DB2 Performance Expert) - that are focused almost purely on SQL, but OMEGAMON provides an alternative to gain insight into SQL statements executing in the DB2 address space (or datasharing group) using a slightly different approach.
OMEGAMON XE for DB2 Performance Expert (shortened to OMEGAMON XE for DB2 PE) provides several application performance diagnostic tools and features like the Application Trace Facility, the Collect Report Data Facility, Batch Reporting, and Object Analysis. OMEGAMON XE for DB2 PE already provides more granular information at the SQL statement level using DB2 performance traces and aggregation perspectives of the SQL statement cache for both dynamic and static SQL. Version 510 however, introduced the “Extended Insight” feature aimed at providing insight outside of DB2 (e.g. application server metrics and network metrics) for distributed applications which can show the distributed SQL statement and associated metrics in a web console. The portion of the Extended Insight Analysis Dashboard that contains the SQL statement was later augmented and isolated as the SQL Statement Dashboard which became a key component of the Stored Procedure Analysis feature in OMEGAMON XE for DB2 PE. With this SQL dashboard, OMEGAMON XE for DB2 Performance Expert can display not only distributed SQL statements, but local/static SQL. Among other capabilities, this SQL statement rendered in the SQL Dashboard (whether via Extended Insight, stored procedures, or host applications) can be passed to Optim™ Query Workload Tuner (Data Studio) for access path analysis and tuning or even workload analysis and tuning.
The techniques OMEGAMON XE for DB2 PE uses to capture the SQL metrics for the SQL dashboard is a very low-overhead option for capturing information about recently run SQL statements in your DB2 for z/OS environment. OMEGAMON XE for DB2 Performance Expert uses the dynamic and static statement caches to ‘snapshot’ SQL statements. This snap-shot mechanism needs IFCID 318 and 400 (for the dynamic and static caches respectively) to be activated to populate the caches and for OMEGAMON to be able to read them. OMEGAMON XE for DB2 Performance Expert then employs a ‘plug-in’ of sorts to read the caches and populate the statements and related metrics in an LUW server process called the repository server and repository database. This is the process where statement matching, aggregation, detecting and resolving evicted SQL (statements evicted from the cache) for analysis occurs. Because OMEGAMON does this processing in the (distributed platform) repository server process and renders this analysis from a DB2 for LUW database, this CPU intensive work is avoided in the OMEGAMON address space.
One thing to bear in mind regarding the SQL dashboard is that the statements are captured using a ‘once a minute’ snapshot method meaning the SQL statements are always historical in nature. The time-period for display and analysis can be reduced to show statements as recent as the last 60 seconds or any 60 second (or longer) period from the past. The other key point is that the statements and metrics always represent an average over the period of time chosen by the user – even if that time period is one minute. These concepts are illustrated below.
Here we show a sample screenshot of the SQL statement summary shown in the SQL Dashboard:
Out of the box, this table view shows you the top n SQL statements for the observation period selected – in the example above – ranked by Total Execution Elapsed Time. Below illustrates selecting a different ranking criterion, for example by Average CPU Time.
Note the duration on the controls on the SQL Dashboard is set to 15 minutes and the time period being displayed is from July 10, 2012 at 11:48 through July 10, 2012 at 12:03. Understanding the time period and duration is a key concept because, with the exception of counts, the values shown in the prior two screenshots represent averages for the time period selected.
Lastly, as one might expect, the SQL Dashboard integrates with IBM explain tools like the IBM InfoSphere® Optim Query Workload Tuner or Data Studio by selecting the statement or statements you wish to tune and simply clicking a “Tune” or “Tune All” on the Action button.
To summarize, the SQL Dashboard in OMEGAMON XE for DB2 Performance Expert is clearly a useful method to gain alternative insights into SQL running in your application environment. This can be used in conjunction with other IBM tools for seamless integration with day to day DBA tasks. The highlights of OMEGAMON XE for DB2 Performance Expert with the SQL Dashboard include:
· Web Browser
· Shows static and dynamic SQL captured from DB2 for z/OS SQL cache
o Retrieves aggregated SQL execution delta in 1 minute intervals (1 minute granularity)
o Shows all SQL cache performance metrics as delta values
· Very low overhead via filtered IFI API READS request
· Keeps SQL history outside of DB2 for z/OS on DB2 LUW database (x/z Linux, UNIX, Windows)
· Is the basis for the Stored Procedure monitoring with nested activities analysis and statement correlation
· Optimal use case: Continuous SQL level monitoring
· Able to identify workload spikes along with the associated SQL statements
Note: All components are included in the OMEGAMON XE for DB2 Performance Expert on z/OS package downloadable from Shopz.
IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. A current list of IBM trademarks is available on the Web at http
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
Other product and service names might be trademarks of IBM or other companies.
 Note: The SQL dashboard and stored procedure monitoring are distinct from Extended Insight and do not require the application server client (known as the Data Tools Runtime Client or sometimes called the Extended Insight Client) to be installed – only the LUW repository server (including z/Linux.) This greatly simplifies deployment.