• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Understanding Database Performance Using The Performance Data Investigator, Part 2

    June 28, 2021 Dawn May

    This is the second in a two-part series on how you can use the Performance Data Investigator (PDI) to investigate performance of Db2 for i. While the SQL Performance Center in Access Client Solutions is more commonly known than PDI, using both tools is a good way to analyze database performance.

    In Part 1, I reviewed the Database content package, focusing on the charts for I/O reads and writes, SQL CPU utilization, and database locks. In this tech tip, I continue by reviewing the charts related to SQL Cursor and Native DB Opens as well as the SQL Performance Data charts, outlined in the screen capture below.

    SQL Cursor and Native DB Opens

    In this section of the navigation tree, there are charts that display the counts of native database (i.e., record level access) full opens and SQL full opens. These are important metrics to review and understand as a full open is expensive in terms of performance. The charts in PDI help you understand the rate of full opens, as well as what jobs or user profiles are doing the opens. However, if you need more information, such as what programs or procedures are doing the full opens, you need Performance Explorer data.

    In the screen capture below, I can see opens per second for SQL full opens, SQL pseudo opens, native full opens, as well as the number of plans built per second. I like that that SQL pseudo opens are a significant contributor, but the native full opens looks concerning.

    The IBM i Performance FAQ states: “A general guideline for partition wide full open rates is less than 1000 per second on small to medium sized partitions, and low single digit thousands per second on large partitions.”

    In the screen capture below, the SQL metrics are removed to provide a more detailed look at the number of native full opens; clearly there is a significant opportunity to improve performance during the early morning timeframe.

    SQL Performance Data

    When you open the SQL Performance Center in Access Client Solutions, you are presented with the properties of the plan cache at that point in time.

    Collection Services collects SQL plan cache statistics at each interval. The charts in the Collection Services folder visualize many of these metrics over time and you can see how these statistics change throughout the day.

    One of my favorite charts is the Plans Detailed chart. This chart shows the activity in the SQL plan cache in terms of plans built, plans removed, and plans pruned; it also shows the size of your plan cache as well as the plan cache size threshold. When the plan cache is automatically sized, this chart displays the plan cache size over time, which is insightful for issues regarding temporary storage utilization resulting in the plan cache size being pruned to a smaller size, potentially capped at 512Mb.

    I won’t review all of these charts; but if you are tasked with understanding your database performance, these graphs can provide insight into the SQL plan cache.

    SQL Plan Cache Snapshots and Event Monitors, SQL Performance Monitor

    This last set of charts is based upon plan cache snapshots, event monitors, and SQL performance monitor data, so you must obtain this type of data first. While using the SQL Performance Center to study this data can be useful, PDI offers a visual summarization that may provide more insight. The SQL Overview and SQL Attribute Mix perspectives each consist of many charts, which are too numerous to review here.

    The SQL Overview perspective summarizes the following:

    • Query time
    • Open summary (number of reusable vs nonreusable ODPs)
    • Open type summary (full opens vs pseudo opens)
    • Statement usage summary
    • Index used summary (no index, MTI used, index used, both)
    • Index create summary (full indexes created, sparse indexes created, index from index)
    • Index advised (index creates advised vs no index creates advised)
    • Statistics advised (advised statistics vs no advised statistics)
    • MQT use (MQTs used vs MQTs not used)
    • Access plan use (access plan used, access plan rebuilds, new access plan)
    • Parallel degree usage (the parallel degree used by number of statements)

    The SQL Attribute Mix perspective summarizes the following:

    • Statement summary (select, update, insert, delete, other, data definition, call)
    • Statement type summary (static SQL, dynamic, extended dynamic, system-wide cache dynamic)
    • Isolation level summary (no commit, uncommitted read, cursor stability, repeatable read, read stability, cursor stability keep locks, not applicable)
    • Allow copy data summary (allow copy no, allow copy optimize, allow copy yes)
    • Sort sequence summary (sort sequence vs no sort sequence)
    • Close cursor summary (endmod/endpgm vs endact/endjob)
    • Naming summary (system vs SQL naming)
    • Optimization goal (first I/O vs all I/O)
    • Blocking summary (blocking enabled all read, blocking enabled read, no blocking enabled)

    If you are reviewing your SQL plan cache snapshots or SQL plan cache event monitors via the SQL Performance Center, or using the Navigator for i web console, you can take the Investigate Performance Data option from these collections, which will open the SQL Overview perspective.

    The performance of the database is critial on IBM i. If you haven’t been using the PDI Database charts, now is the time to start.

    Dawn May worked on the AS/400 / iSeries / IBM i development team in Rochester, Minnesota, for more than 30 years. Dawn was the technical lead for a variety of projects at IBM, including development of the Performance Data Investigator. Dawn now runs her own consulting practice, specializing in troubleshooting and education. To learn more about PDI and how to use it, or for assistance with performance issues, visit dawnmayi.com.

    RELATED STORIES

    Understanding Database Performance using The IBM i Performance Data Investigator, Part 1

    Getting Started With The IBM i Performance Data Investigator

    IBM i Performance FAQ

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Access Client Solutions, ACS, DB2 for i, FHG, Four Hundred Guru, I/O, IBM i, Native DB, PDI, performance data investigator, SQL

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    New Converged Archive System, Power Gear Withdrawals Final RPG & DB2 Summit Scheduled

    Leave a Reply Cancel reply

TFH Volume: 31 Issue: 43

This Issue Sponsored By

  • Fresche Solutions
  • UCG Technologies
  • Comarch
  • Eradani
  • Precisely

Table of Contents

  • In Search Of Next Gen IBM i Apps
  • Final RPG & DB2 Summit Scheduled
  • Guru: Understanding Database Performance Using The Performance Data Investigator, Part 2
  • New Converged Archive System, Power Gear Withdrawals
  • Adding Depth To Your IBM i Security Bench

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle