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.
