Last week I wrote about collecting performance data for database. This week I will review how you can visualize that database performance data with the Performance Data Investigator (PDI).
The Performance Data Investigator includes a Database content package. This set of perspectives is only available if you have the IBM i Performance Tools (5770-PT1) licensed program product installed.
There are a variety of charts available in the Database content package. The major categories are shown in the figure below:
The first three charts are generally considered “Starting Points” – charts that provide a high-level system overview of performance, which can be useful if you are not exactly sure where you should start for analysis. These three charts are based upon Collection Services data.
- I/O Reads and Writes gives you a system-wide summary of logical and physical database I/O (reads and writes) per second.
- SQL CPU Utilization Overview can help you answer the question “Is the CPU used on my partition due to SQL work or other work?” I have written about this previously in SQL CPU Utilization.
- Database Locks Overview can quickly help you determine if you have database record locking contention on your partition. Collection Services data can show if there is database record lock contention, but for detailed analysis, you will need to use another tool, such as Job Watcher.
The Database I/O charts display the job-level SQL metrics. I wrote about this previously in Viewing Job Level SQL Metrics with the Performance Data Investigator. This set of graphs allows you to drill into much more detail for the physical and logical I/O.
The SQL Cursor and Native DB Opens charts allow you to review the file open processing on your partition that is collected by Collection Services. The Overview chart will show you SQL Full Opens per second, SQL Pseudo Opens per second, and Native DB Full Opens per second. This is a great starting point to determine if you possibly have too many full opens occurring. The drill-down charts allow you to review this information by job or job current user profile.
In the SQL Performance Data section, you will find charts organized within three categories.
- Collection Services
These charts graph the data that is collected by the *SQL category in Collection Services. - SQL Plan Cache Snapshots and Event Monitors
These charts allow you to use PDI to graphically review information from the SQL Plan Cache snapshots and SQL Plan Cache Event Monitors. This is data that you collect using the database performance tasks. - SQL Performance Monitor
These charts allow you to use PDI to graphically review information from the SQL Performance Monitor. This is data that you collect using the database performance tasks.
Navigation from Database Tasks
You can launch PDI from the Database tasks. For example, the screen capture below shows an SQL Plan Cache Snapshot to show how you can go to Investigate Performance Data directly from the SQL Plan Cache Snapshot.
Hopefully you can take advantage of the ability to graphically view database performance information to better understand and analyze the performance of Db2 for i.
This blog post was edited to fix broken links on April 15, 2020.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.