Performance Toolbox for IBM i Services

 In March, I wrote about the importance of thinking about performance as part of using IBM i Services with SQL.  One reader suggested a follow-up article highlighting which tools would be helpful for analyzing the performance of an IBM i Service, so that’s what I aim to do here. 

Since I’m a Db2 guy, we’ll start with the database performance tools first which means the IBM i Access Client Solutions (ACS) toolset. Visual Explain is THEE tool for understanding the implementation details of an SQL request. Visual Explain can be used from many different perspectives – interactively from Run SQL Scripts as you run your SQL or post-SQL execution from the Plan Cache or Plan Cache Snapshots.

 Visual Explain graphically lays out the blueprint of the query optimizer’s implementation for a query.  This graphical representation shows all of the Db2 objects used in the query implementation including tables, indexes (some might still refer to these as access paths), and user-defined functions.  The number of objects represented in a query plan can give you an indication in how much work you’re asking the system to do.  For example, joining together ten tables is usually going to require consume more system resources than joining together two tables.

Below I’ve included a portion of Visual Explain output from a customer’s report that was joining to the Group_Profile_Entries catalog view/service.   At face value, this view seems relatively simple in that it returns a row for each user profile that belongs to the group profile.  As you can see from the Visual Explain representation, the processing required to return data from this view is anything but simple. This view ends up joining together the result data from 5 different IBM i Service calls in order to return the user profile entries that belong to a specific group.  Instead of simply reading data from underlying table(s) like many of the Db2 for i catalog view, the Group_Profile_Entries view has to dynamically generate the data using user-defined table function calls (ie, external program calls).  As you might guess, program calls are more expensive than reading a row from a table. 

Based on this Visual Explain output, you should be getting a sense that SQL referencing the Group_Profile_Entries may consume a fair amount of system resources.  This perspective may influence how often you run the SQL request and when you run the SQL request.  In addition, you’ll want to look at the query to see if more filtering can be specified with the Group_Profile_Entries reference to limit the amount of data that must be dynamically generated. 

System Performance tools can also be used to analyze the performance signature of the IBM i Services that you’re trying to use. These tools are great at providing insights into the system resource utilization that are used by a service.  Performance tools that you might want to use to analyze the system impact of IBM i services queries are Collection Services and IBM iDoctor.

Here’s a relatively simple query using the Output_Queue_Entries service to count the number of output queue entries for each job on the system. 

SELECT job_name AS job, COUNT(*) AS sum_splf
  FROM qsys2.output_queue_entries
  GROUP BY job_name ORDER BY COUNT(*) DESC

 

While this query is simple, the following IBM iDoctor output shows that this service resulted in the job waiting on a large number of page faults while it was off processing each output queue entry on the system. This page fault wait time signature is probably an indicator that you should consider tightening the focus of your request by limiting the number of jobs that have output queue entries checked. 

If you’re looking for help becoming more proficient with these tools, IBM Lab Services can help.  The Database Engineer (DBE) Enablement offering features hands-on training with Visual Explain and the full suite of ACS SQL performance tools.  For assistance getting started with the system performance tools, check out the IPAWs training or the IBM iDoctor workshop.