In the past, IBM i database performance management and analysis was done using tools specifically created for database, while system performance management and analysis was done using the system performance tools. There was little integration between the two toolsets.
This changed with the IBM i 7.2 release (and PTFs to the 7.1 release). Database performance metrics are now included in Collection Services and you can use the Performance Data Investigator to visualize those metrics. In addition, you can visualize database performance information from the SQL Performance Monitor, the SQL Plan Cache and the SQL Plan Cache Event Monitor using the Performance Data Investigator (PDI).
This blog outlines collecting the database performance data that you can analyze with PDI. Next week, I will write about the Performance Data Investigator and how you can display this data graphically.
Performance Data Collection for Database
There are two major categories of performance data for database that you can analyze using PDI:
- SQL performance data: This is additional data that you can optionally collect using the database performance tasks.
If you want to know more about SQL performance monitors or the SQL plan cache, please refer to the following information in the IBM i Knowledge Center:
- SQL performance data that is collected by Collection Services. This data is automatically collected in the 7.2 release and later.
- Job-level SQL metrics: I previously wrote about collecting these metrics in the blog Job Level SQL Metrics in Collection Services. These metrics are collected automatically as part of job-level performance data.
- The SQL category of data collection in Collection Services (7.2 and later). The 7.2 release introduced a new SQL category in Collection Services and this SQL category includes SQL Plan Cache data. This category is part of the standard collection category and this data is collected by default. The data is stored in the QAPMSQLPC file.
Next week I will review how you can graphically display this data using the PDI.
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.