Previously I wrote about Job Level SQL Metrics in Collection Services. In this week’s blog, I will review the support added to the Performance Data Investigator (PDI) to allow you to view and analyze these job-level SQL metrics.
First, you must have the latest Navigator for i PTFs installed as reviewed in the blog Navigator for i Enhancements. You will also want to verify that you have the PTFs installed and activated that were noted in the Job Level SQL Metrics in Collection Services blog.
The GUI for the new SQL metrics is contained in a content package named Database. This content package will only show up under the list of perspectives in the Performance Data Investigator if you have the Performance Tools licensed program product (5770-PT1) installed; you just need the manager option. Put another way, the enablement for the database graphical interface in the Performance Data Investigator is shipped with PT1.
Once you have the latest Navigator PTFs installed (along with the performance tools and database group PTFs that I reviewed previously), you will discover you now have some new perspectives available in the Performance Data Investigator. In particular, you will notice a new Database folder that includes Database I/O perspectives and SQL Performance Data perspectives. The following screen capture shows this:
You need to drill down into the Database I/O and subsequent folders to find the graphs and charts of data you can now view on Physical Database I/O metrics – as you can see from the following screen capture, you have several views of these database metrics:
If you have not followed the activation instructions required for the Collection Services PTF I previously wrote about, you will get an error if you try to view one of these physical database I/O charts. This is because the underlying data is not collected. Refer to SI47594 PTF Special Instructions for details.
An example of the Physical Database I/O Overview chart is below. You can see the SQL-related physical I/O metrics are summarized by time for the selected collection.
There are several different ways you can view the physical database I/O – by job, thread, user profile, current user profile, subsystem, or server type – to give you various ways to view and analyze the metrics.
You will note that the Performance Data Investigator now also supports viewing data from the SQL plan cache and the SQL performance monitor. I don’t have time in this blog to go into details on using PDI to look at this data, but for those of you who are interested in database performance, it’s worth getting current on your PTFs and checking out these new features.
The Performance Data Investigator development team has created documentation on developerWorks that summarizes the latest enhancements that have been made. There is an article on the new Database content package that you can look at for more information.
Finally, there are some recent changes that were not discussed in the prior blog regarding the SQL metrics. My previous blog stated that this was only supported on the 7.1 release, but the database metrics are now also available on the 6.1 release! Please read the Spring 2013 documentation (which is a subset of the Performance on the web documentation) on the IBM i developerWorks site to get the details regarding the support added to 6.1 and the required PTFs. If you read that Web page, you’ll discover that I have even more to blog about in coming weeks!