Tale of the Tape - SYSDUMMY1 vs Tableless SQL Performance

 I thought about titling this entry “Tableless SQL for Dummies” to play off the old Dummies book series and the focus of this entry: the Db2 dummy table, SYSDUMMY1.  However, I thought that might be a little too abrasive for some readers.  Plus, that catchy title doesn’t reflect that SQL performance can be improved by simply replacing SYSDUMMY1 queries with a tableless SQL request. This Tale of the Tape comparison will help you better understand this performance difference.  

You may or may not know that Db2 provides a dummy table, SYSDUMMY1, in the SYSIBM schema.  The SYSDUMMY1 table contains a single row and column which as you can see from the following example makes it really easy for a developer to invoke an SQL function or retrieve the value of an SQL special register. 

SELECT UPPER(:hv), CURRENT TIMESTAMP
  INTO :hv1, :hv2
FROM sysibm.sysdummy1

Back when I was working with software vendors, the SYSDUMMY1 table was also quite handy when porting Oracle applications to the IBM i platform because some of those applications relied on using the Oracle dummy table - DUAL.  Database vendors often included a dummy table because all SQL DML (Data Manipulation Language) statements required a table reference. However, that’s no longer case because the SQL standard has been enhanced to support tableless queries.

The VALUES statement was the new addition to the SQL standard which enables you to run tableless queries.  Taking the previous example, you can see how it is implemented with the tableless support:

VALUES (UPPER(:hv), CURRENT TIMESTAMP)
  INTO :hv1, :hv2

These two examples are almost identical except for the FROM clause. Not only does the VALUES statement save you from having to type the FROM clause, it also will return the results faster because there is no FROM clause! Let’s dig into why the FROM clause causes a performance difference. 

 

Any time there is a table referenced on the FROM clause on an SQL request, the Db2 for i engine has to performance serialization actions to support concurrent access of the specified table and its rows. Even though the SYSDUMMY1 table is small and contains a single row, the required serialization steps makes it slower to process than the equivalent tableless query. 

 

To measure this performance difference, I created separate SQL procedures with each one of them running these two equivalent SQL statements 50,000 times. 

SELECT CURRENT TIMESTAMP INTO :hv1 FROM sysibm.sysdummy1

VALUES CURRENT TIMESTAMP INTO :hv1

The average run time for the SELECT statement procedure call was 1,430 milliseconds while the average run time for the VALUES statement call was 340 milliseconds – a 4x times difference in performance!  While you might be thinking that the average execution time of each method is very small when you consider each statement was executed fifty thousand times, remember the old proverb that “Little by little, a little becomes a lot”.

You might find that this type of SYSDUMMY1 request is being run over and over again on your system.  In fact, I recently reviewed a customer’s SQL Plan Cache Snapshot data and they had a SYSDUMMY1 query that has been run over 360 million times on their system.  So even if the tableless query approach was just 100 microseconds faster, the multiplicative impact of that small improvement will add up to make a difference on their system. 

The performance advantages that tableless SQL requests hold over SYSDUMMY1 queries should make it clear that tableless queries are the clear winner in this comparison.

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 Expert Labs 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.

Simplify your SQL development by staying current

Since I returned to the IBM i world last Fall, I’ve spent time reviewing all of the Db2 for i and SQL enhancements that have been delivered during the 5+ years that I was off working on Watson.  The Rochester development team has definitely been busy cranking out some great functionality while I was away. It’s been nice to see improvements to the SQL standards that deliver real efficiency for developers.

One of those SQL standard additions that caught my eye is the LISTAGG aggregate function.  The documentation states that this function aggregates a set of string elements into one string by concatenating the strings.  I think a simpler real-world benefit description is that it makes it easy to combine values from multiple rows into a single row. For example, you’re asked to produce a report of the shipping companies that your business uses by region and the report format needs to be the following:

NORTH	FEDEX, GARZA SHIPPING, SPEEDEE DELIVERY
SOUTH	FEDEX, TYLER TRUCKING, UPS, USPS

 

This is easily done with the LISTAGG function with this simple, but powerful SQL statement:

SELECT region, 

       LISTAGG(DISTINCT carrier, ', ') WITHIN GROUP(ORDER BY carrier)
   FROM shipping_method GROUP BY region

I highlight the simplicity of this solution because you could do this in SQL before with recursive SQL syntax, but those types of SQL statements are longer and difficult to understand. In this article, I provide more details on LISTAGG and contrast it with the recursive SQL solutions.

I believe a feature like this is also a good reminder of the benefits of staying current on your IBM i release level and Database Group PTFs.  The more current your systems are, the more Db2 & SQL features there are available to simplify life for your developers.

Latest IBM i TR adds Query Supervisor to Db2 & Your Team!

The latest IBM i Technology Refresh was announced this week which means there are new Db2 for i features and functions available to you. This latest announcement includes Db2 Mirror for i enhancements, SQL advancements, and new & updated IBM i services – however, the Query Supervisor is by far the most interesting new bell and whistle.

I like to remind IBM i clients that when they start using SQL they’re actually extending the size of their development team. Your team grows in size because the Db2 for i SQL engine features a Query Optimizer whose job it is to determine the fastest way to access and retrieve the data. Your development team is able to focus on the coding of business logic and leave the performance logic to the Query Optimizer. This latest announcement adds another member to your team with the introduction of the Query Supervisor. 

The new Query Supervisor allows you to take query governance and monitoring to another level. If you’ve been around the platform for a while, you might be thinking that Db2 for i already supports query governance and you would be right.  The Db2 for i support for a Query Governor goes way back to V3R1.

The Query Governor is known as a predictive query governor because it allows action to be taken before a query starts running. The governor allows you to prevent a query from wasting system resources when the query’s runtime implementation is predicted to exceed the time and/or temporary storage thresholds that you’ve defined for your server. The predictive aspect means that the Db2 for i Query Optimizer had to estimate the amount of time and resources that it takes to run the requested query.

As you might imagine, generating an accurate estimate is a tall task. One of the biggest hurdles is that the optimizer has no idea how busy the system will be when the query starts running. A CPU intensive workload could start running milliseconds after the current query starts and there’s no easy to way to predict this. Thus, the optimizer estimates are not going to be one hundred percent accurate. The optimizer’s estimates are relatively accurate meaning that long-running queries will have larger estimates than small-running queries. 

This “relative” accuracy of the optimizer’s estimates made it difficult to use the predictive query governor function system-wide. If you set a query time limit of 5 minutes on the system, a query that runs closer to two minutes might be prevented from running because the optimizer’s estimate was 6 minutes. A query that was estimated to run in 3 minutes might actually run for 7 minutes due to the system being busier than predicted. As a result of these challenges, clients have asked IBM for additional query workload controls and IBM has delivered on that request with the new Query Supervisor feature.

The Query Supervisor provides a solution for taking action on an active query whose actual resource usage exceeds a user-defined threshold. When the Db2 engine detects that your threshold has been exceeded, it will call a user-defined exit program to take action. That action might be cancelling the query or just logging it and notifying an administrator about the offending query; the possibilities are endless. If the exit program chooses to cancel the query, then one could argue that system resources were wasted by a query that didn’t complete. However, the upside is that you were able to limit the amount of time that the query caused overhead on your system.  Like the Query Governor, the Query Supervisor supports SQL and non-SQL queries (OPNQRYF, etc.).

The new Query Supervisor support allows you to set the following threshold types with a simple call to an SQL procedure - QSYS2.ADD_QUERY_THRESHOLD.

• ELAPSED TIME
• TEMPORARY STORAGE
• CPU TIME
• TOTAL IO COUNT

The threshold value is dependent on the type of threshold. The threshold value for Elapsed Time and CPU Time would be specified in seconds while the value specified for a Temporary Storage threshold would be given in megabytes. The Total IO Count threshold value is just a count because it specifies a limit on the number I/O operations that a query can perform.

With the Query Supervisor, you can also specify filters to narrow the focus of the supervisor’s threshold monitoring. The threshold filters that can be applied are subsystem names, job names, and user profile names to include or exclude. The values for each of these thresholds can be up to 100 names and can include generic names (eg, ‘RPTUSER*’). 

Here’s an example of a threshold definition to prevent QZDASOINIT jobs from having queries that run longer than 5 minutes, unless the queries were submitted by a manager. Notice in this example that you’re able to specify multiple threshold filters on the definition.

 CALL QSYS2.ADD_QUERY_THRESHOLD (
  THRESHOLD_NAME => 'QZDASOINIT Time Limit',
  THRESHOLD_TYPE => 'ELAPSED TIME',
  THRESHOLD_VALUE=> 300,
  JOB_NAMES      => 'QZDASOINIT',
  EXCLUDE_USERS  => 'MGRID*',
  SUBSYSTEMS     => '*ALL'  )

Thresholds can be easily removed by specifying the THRESHOLD_NAME on the QSYS2.REMOVE_QUERY_THRESHOLD procedure

The user-defined exit program needs to be registered to the new QIBM_QQQ_QRY_SUPER exit point. As mentioned earlier, the exit program allows you take a wide variety of actions. That flexibility is enhanced even further with the input values that are passed to the exit program. Here’s some of the more interesting input values to consider utilizing in your program logic:

–        Threshold name, type & value

–        User, Subsystem, and Job Info

–        SQL Statement text, Plan Identifier & Host Variable values

–        Client Register Values

To try out your new Query Supervisor team member, you just need to load the Database Group PTFs onto your IBM i 7.3 or 7.4 systems when they are released next month. 

IBM i Services are Great, but they're NOT Magic

 The development team in Rochester has been quite busy over the last several years making it easy to use SQL to access data and services related to the IBM i operating system. These IBM i Services can be used with SQL to perform a variety of requests; these requests range from retrieving data on active jobs & database connections to placing entries on data queues to checking object authorities to writing data to IFS files. I’m using the "IBM i Services" term here generically to refer to services that you call and to queries against system catalog views because many of the newer views utilize service calls to return data.   

The good news is that we’re seeing a lot of IBM i developers leverage these services to solve a number of problems. The bad news is we’re finding many developers using these services without any thought given to performance. Little consideration is given to how much data is being requested and how often the request is being made - it’s almost like they think there’s something magical about the performance of these services just because they are from IBM. While the IBM development team creating these services is really good, performance analysis and testing should be part of the rollout of any new functions using the IBM i Services. The same performance rigor that you would apply to the deployment of new business processes on your production systems needs to be applied to the deployment of code using the IBM i Services.

Our Db2 team in IBM Expert Labs team analyzes the Top 25 Most Time Consuming SQL statements as part of the SQL Performance Assessment service that we perform for clients.  In the last two years, we’re often finding SQL statements using IBM i Services in the list of most time consuming SQL. These time consuming SQL statements often have a negative impact on overall system performance because of their resource usage. A recent conversation with colleagues in IBM Support revealed they are seeing similar trends with clients reporting system performance problems, only to find a suboptimal services call as the root cause.

Here are some performance considerations when using IBM i Services with SQL.

First, not all services are designed to be called repeatedly on the system and/or to be called during daytime hours when system usage is heavy. Some services consume more system resources than others – that’s why performance testing is key to get a feel for how long the service takes to run and how many resources will be consumed by the service. The IBM Support team shared one story of a client complaining about system performance only to determine the client was calling the DISPLAY_JOURNAL service with very few input filters every two minutes on their production system. Repeated calls to other services like DATA_QUEUE_ENTRIES may be perfectly fine and that can be easily determined with performance testing and analysis. Performance analysis may also lead you to determine that the service needs to be called and run overnight when system usage is low.

Second, while the creation of indexes is often a solution to make SQL queries run faster & more efficiently  – this technique does not help with SQL statements using IBM i Services.  Indexes are not applicable because most IBM i Services rely on functions to dynamically generate the result data, so there’s no data to index. The function  logic is accessing & processing IBM i objects with system API calls. The operating system data being processed is not stored in tables and rows which are normally the target of an SQL request.

The lack of help from indexing leads to the last and probably most important performance consideration – ensure your IBM i Service calls are tightly focused.  A tight focus means specifying as many search parameters as possible on your calls to minimize the amount of data that has to be touched in order to return the result. For example, one could use the following query against the SYSPROGRAMSTAT catalog view to retrieve release details about an SQL program (MYSQLPGM)

SELECT target_release, earliest_possible_release     
  FROM qsys2.sysprogramstat
  WHERE program_name='MYSQLPGM'

While this query works, the scope is not focused – the request directs Db2 to search through every library containing an SQL program instead of scoping the search to the library containing the program.  The focus has been tightened on this new version of the SELECT statement resulting in the same result, but with better performance since the scope was narrowed to a single library:

SELECT target_release, earliest_possible_release     
  FROM qsys2.sysprogramstat
  WHERE program_name='MYSQLPGM' AND program_schema='MYPGMLIB'

Including search predicates on a WHERE clause is one way to narrow the focus of an IBM i Service.  The other method for narrowing focus is passing input parameters on the service call itself. Let’s use the OBJECT_STATISTICS service as an example. An audit requires you to provide a list of all the program & service program objects on your system. The following SELECT statement generates the required listing, but the scope of the service request is too broad requesting all libraries on the system be processed by the table function call.

SELECT objname, objlib, objtype
  FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL', 'PGM SRVPGM'))
  WHERE objlib NOT LIKE 'Q%'

While the IBM libraries are filtered out with the WHERE clause – that filtering is performed after the data is returned by the table function.  The amount of data processed by this call would be greatly reduced by changing the first input parameter from *ALL to *ALLUSR so that only user libraries are processed by the service.  Thus, eliminating the need for the WHERE clause.

SELECT objname, objlib, objtype
  FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR', 'PGM SRVPGM'))
  WHERE objlib NOT LIKE 'Q%'

Some IBM i services have options that reduce the amount of detailed data is collected and returned in order to speed up the performance.  The OBJECT_STATISTICS service has such an option, so this request can be made even more efficient by adding a third parameter with a value of *ALLSIMPLE. 

SELECT objname, objlib, objtype
  FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR', 'PGM SRVPGM', '*ALLSIMPLE'))
  WHERE objlib NOT LIKE 'Q%'

With these guidelines in tow, you should be able to use IBM i Services in production without negatively impacting system performance.  You can also find a ton of good examples of using IBM i Services on  Scott Forstie’s SQL examples site:  ibm.biz/Db2foriSQLTutor  Let me know if your company needs assistance tuning your usage of IBM i Services.

The ABCs of Effective Db2 SMP Usage

 Creation of the Db2 for i SMP (Symmetric Multiprocessing) licensed feature was one of the coolest projects that I was able to work on during my time on the Db2 development team. Figuring out how to add parallel processing into the Db2 engine was both interesting and challenging work. I did some searching through the InfoWorld archives (remember when hard copy IT periodicals were a thing…) and figured out that the SMP feature recently turned 25 years old in November 2020.  As the old saying goes, time flies when you’re having fun.

As part of that development project, I also had the opportunity to help one of the first customers use Db2 SMP in their shop. While the SMP feature and underlying hardware have changed over the years, the items to consider for a successful Db2 SMP implementation have not.

I’ve tried to break the success factors into the following ABC acrostic –might be a bit of a stretch, but it makes for a catchy title😉

·        Available system resources

·        Balanced expectations

·        Controlled usage of SMP

Available system resources

With Db2 for i SMP, the basic approach is dividing the work for a query across multiple threads and running those threads in parallel across multiple processor/cores to shorten the amount of time it takes to run the query (check out Mike’s nice graphic). The Db2 engine is using more system resources in order to reduce the overall amount of time it takes to run your query. 

Resource usage is being traded for time. That’s why reviewing the availability of system resources is a critical step to perform before buying and implementing Db2 SMP. If you don’t have the system resources to trade, then you’re not going to realize the performance benefits of Db2 SMP.  For example, if CPU utilization is currently running at 80-85%, adding Db2 SMP to use more system resources is not going to have a positive impact on system performance. 

The system resources also need to be balanced.  CPU resources are not the only system resource consumed by Db2 SMP.  Each thread used by Db2 SMP needs a chunk of memory to perform its segment of the query and that work can involve performing I/O on your database objects. As a result, your system needs sufficient memory and a properly sized I/O subsystem to support the increased CPU usage.  If the query optimizer finds that this combination of resources is not available, then the optimizer will not use parallel methods – even if you’ve installed and activated the Db2 for i SMP licensed feature.

Balanced expectations

Assuming you’ve determined that your system has a balanced set of resources available to support Db2 SMP parallel processing. The next step is setting the proper expectations on the type of database requests and workloads that may run faster with Db2 SMP. Some people tend to believe that Db2 SMP’s parallel processing will be the silver bullet for all of their performance problems. 

Running a query with parallel processing adds overhead because there is work involved in dividing a query into multiple parts and distributing the work among threads.  This startup overhead means that Db2 SMP will not be a great benefit to short-running queries that are common in transactional workloads. Think about your own household - it’s not uncommon for younger kids to want to help parents with household chores, but often parents chose to do the chores themselves to avoid the overhead of involving and training their kids.  Your time would be better spent trying to tune short-running queries than hoping that parallel processing will magically improve performance.

Longer running queries are the best performance targets for Db2 SMP because they have a longer runtime which can quietly hide the startup overhead associated with parallel processing.  If Db2 SMP can reduce a long running query from 10 minutes to 5 minutes, no one is really going to notice that a hundred milliseconds was spent setting up threads for parallel processing. 

You might have noticed that I keep using queries as the parallel processing example. That is because Db2 SMP does not enable all database requests to use parallel processing.  Queries from SQL and non-SQL interfaces can use Db2 SMP, but native record-level access requests do not. Db2 SMP also does not support parallel inserts, updates, and deletes.  The only type of database change operation that can use Db2 SMP is Index Maintenance – however, this parallel processing is only done when the index updates are done as a result of a blocked Insert or write request. Db2 SMP can also utilize parallel processing to improve the performance of index creations and reorganize operations.

When setting expectations for the performance benefits, you need to make sure that everyone understands that it’s longer running queries that will be the primary benefactor from Db2 SMP and that not all database requests can use parallel processing.

Controlled usage of SMP

Once Db2 SMP has been installed on a system, it must be activated before the Db2 engine will consider using parallel processing on a request.  There are several different interfaces for enabling parallel processing which include: CHGQRYA CL command, QAQQINI PARALLEL_DEGREE option, SET CURRENT DEGREE statement or the QQQRYDEGREE system value.

Based on the discussion in the previous section, you should try to limit parallel processing enablement to only those jobs or requests that will benefit from Db2 SMP.  Enabling Db2 SMP for all requests just adds overhead to query optimization and can result in your system resources being overwhelmed if parallel processing is used. On a transaction-oriented system, you probably should scope parallel enablement to a limited set of requests and workloads from Db2 SMP. In contrast, you could cast a pretty wide parallel enablement on a data warehousing system which features longer-running queries.

Activating Db2 SMP system-wide should only be done with the IBM i 7.5 release level. The reason that this recommendation is scoped to the IBM i 7.5 release is Db2 was enhanced with two new configuration options to help prevent parallel processing activity from swamping your system. The new query options are PARALLEL_MAX_SYSTEM_CPU and PARALLEL_MIN_TIME.  

In addition to figuring out which jobs and requests to enable parallel processing on, you should consider when to activate parallel processing. It could be that your server has high utilization of resources during the day, but resources to spare during off hours.  The enablement interfaces make it easy to turn Db2 SMP on or off.

In terms of which parallel degree value to use, I recommend starting with the *OPTIMIZE value.  With the *OPTIMIZE value, the Db2 optimizer tries to choose a degree of parallel processing that results in an implementation that is a good neighbor in terms of sharing system resources with other jobs.  A more cautious approach would be setting the QAQQINI PARALLEL_DEGREE option with a value of *OPTIMIZE 50.  This setting tells Db2 to use the good neighbor approach, but dial the parallel processing back by 50%. 

Hopefully, you now have a better understanding of when and how to use SMP. As of June 1, 2022, the Db2 for i SMP feature is a no charge feature (on all 7.* releases) - meaning you just need to install the feature to try it out on your queries. If you need help optimizing your usage of Db2 SMP or with SQL performance in general our Technology Expert Labs team is available to help – just contact me.

A New "Routine" Habit for the New Year?

 Welcome to 2021! I hope that everyone’s new year is off to a good start.  A new year often brings discussion of regeneration and rebooting to start the year with a clean slate when it comes to developing good habits or dropping bad habits.

In the spirit of developing new habits for the new year, you may want to look into starting the habit of regular regeneration of your SQL routines.  When an SQL function, procedure, or trigger objects gets created, you may or may know that behind the scenes Db2 generates a C program with embedded SQL to implement the specified SQL procedural logic.  The efficiency of the program generation can have a direct impact on the runtime performance of your SQL routines.  As part of the code generation process, Db2 tries to implement some assignments and comparisons statements with pure C code to get the best performance.

Over time, the smart developers in Rochester have expanded Db2 for i’s ability to generate pure C code in more situations to speed up the performance of your SQL functions, procedures, and triggers.  However, your SQL routines can only benefit from the C code generation enhancements when they are recreated with the newer version of Db2 for i.  This article shows you can how you can query the system catalogs to identify those SQL routines that have not been recreated in a while.  Or you could just choose to recreate all of your SQL routines since that’s a relatively easy operation to try to see if it improves performance?

Hopefully, I’ve convinced you to add regular SQL routine recreation after Db2 for i updates to list of new habits to develop in the new year since it’s such a simple operation that may deliver faster performance.