Wednesday, February 17, 2021

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 DBb2 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.

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.  If you still think that the Db2 for i SMP license feature may be a fit after reading this, our Lab Services team can provide a trial version of the feature for evaluation to help with the purchasing decision.  Also, our team is available to help you with Db2 performance tuning or teaching you how to tune whether it involves Db2 SMP or not – just contact me.

Wednesday, January 20, 2021

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.

Friday, December 11, 2020

Old Habits Die Hard, even QTEMP ones

As the old adage says, old habits die hard.  I have experienced this firsthand since returning to the IBM i world a couple of months ago.  While I was off working on Watson, IBM reached into its marketing bag of tricks and “changed” the name of its relational database product from DB2 to Db2.  If I had a nickel for every time that I’ve typed DB2 instead of Db2 since my return, I could use that money to take my wife out for a really nice meal; that assumes restaurants being open here in Minnesota, but I digress…

Another old habit that I’ve seen still being used in the IBM i world is copying data into QTEMP and then running SQL against this temporary copy.  There are certain aspects of this QTEMP approach that are beneficial, but there’s a real dark side, to use a Star Wars analogy, from a performance perspective that should be not ignored.   I’ve written this article to highlight the reasons why IBM i developers should consider ending their QTEMP habit.

The good news is that SQL has several different features that allow you to get the benefits of the QTEMP approach without the performance overhead.   If you or your team need any help with a New Year’s resolution in 2021 to break the QTEMP habit with advanced SQL, then let me know.

Have a Merry Christmas and wonderful holiday season, I look forward to talking with all of you in 2021! 

Friday, November 13, 2020

Db2 for i Comings & Goings

 As you might have guessed from the quietness of this blog, Mike Cain, my good friend and long-time Db2 partner in crime retired from IBM last year and is happily enjoying retirement.  

And if you haven’t heard yet, I returned to the IBM i world this Fall after a 5-year stint working on healthcare solutions powered by IBM Watson technologies. I’m excited to be back working work with the Db2 team in the IBM Lab Services Power Systems Delivery Practice where I get to help clients get the most out of Db2 and SQL on IBM i.  With my return, I’ll be using this blog to raise awareness about all things related to Db2 for i. 

Speaking of new things in Db2 land, today there are new IBM i technology refreshes available for the 7.3 and 7.4 releases that as always include numerous Db2 enhancements.  My favorite is the new IF EXISTS clause for DROP statements which can be used to greatly simplify your SQL database creation scripts – you can learn about how to use this feature in my article.  A complete list of all the Db2 and IBM i enhancements in the latest technology refresh can be found here.   

It's good to be back!

Friday, April 13, 2018

Data: The New Currency

In this month's issue of the IBM Systems Magazine - POWER edition, author Neil Tardy shares some observations and insights on the importance of realizing maximum value from data through collaboration.

If you have not already read the piece, you can check it out here.

I highly recommend you share it with your business leaders and executives, taking advantage of the opportunity to discuss and reevaluate your collection and use of data.

Please reach out to me if you need assistance with the conversation, and as you persevere to do more, and do better.

Monday, December 4, 2017

Making the Case for a Database Engineer - Again

Recently, I collaborated with IBM Systems Magazine author Neil Tardy to reiterate the importance, and the value of standing up an IBM i database engineer. The end result of this collaboration is a simple but profound article that once again, makes the case for the Db2 for i DBE.

Take a few minutes to read Neil's article, and then share it.  Use it at as a good excuse to call on your managers and executives.  Deliver a message.

If you need help in communicating the message, or convincing them of the urgency, please reach out, I am more than happy to help illuminate this important topic.

Mr. Tardy's article can be found here, in the December 2017 issue of IBM Systems Magazine.

Tuesday, October 3, 2017

Interesting Db2 items in the latest TR!

Today, IBM announces the latest IBM i 7.2 and IBM i 7.3 Technology Refresh (TR). 

The availability of the respective TRs via group PTF is scheduled for October 27, 2017.

More information on the IBM i TRs can be found here.

More detailed information on the Db2 for i TRs can be found here.

Looking through the database enhancements, we find some very interesting items to consider...

Db2 Web Query extends the community of users to the true data analyst and/or data scientist. New powerful data discovery capabilities make it even easier to navigate through data in pursuit of  understanding and actionable information.  More information can be found here.

Db2 for i JSON support extends database capability via new publishing functions:


Joining XML, JSON is fast becoming the go to mechanism for storing and sharing unstructured data. Composing a JSON document from the structured, relational (row and column) data via the integrated JSON functions represents expanded "data centric" development capabilities in Db2 for i.

SQL DML LIMIT and OFFSET enhancements (Hi Jon Paris!) including the ability to use them in both DELETE and UPDATE statements.

IBM i Access Client Solutions enhancements to database support. ACS is fast becoming the main tool for the IBM i database engineer.  Make it part of your toolkit today!

Alerts when approaching database limits.  IBM i will produce alerts for a subset of System Limits tracking. Once per day, the operating systems will look for new occurrences where consumption of some limits exceeded 90% of the maximum allowable size. For these instances of extremely high consumption, messages will be sent to the QSYSOPR system operator message queue. Limit alerting is available for:

    Maximum number of all rows in a table (partition)
    Maximum index size
    Maximum encoded vector index size
    Maximum number of variable-length segments

Trust me, when one hits a database limit unexpectedly, the fun stops. Allowing IBM i to provide an alert to the database engineer is the first step in avoiding a painful issue.  By the way, all of the Db2 for i limits can be found in the SQL Reference, appendix A.


If you need assistance with getting more value out of your data residing in IBM i, or determining how best to make use of these, or any Db2 features and functions, please reach out - we are here to help.

Note: if you are currently running IBM i 7.1, these new features and functions are not available to you.  Please upgrade your operating system as soon as possible to take advantage of all that Db2 for i can offer!