Informing the IBM Community

A screen a story –  Db2 Symmetric Multiprocessing

5
(1)

Part of the announcement of IBM i 7.5 was a change in what is entitled as part of the base operating system with IBM i. If you want a complete list of the Price Change(s): Price Decrease on Selected IBM i Sofware Products please have a look at the spreadsheet which is available for download on that page. For some IBM i Licensed Programs the new price is zero, do we not all like things which are for free?

This image will save you the time having to go there:

In this article I would like to focus on the above mentioned “IBM i option 26 – Db2 Symmetric Multiprocessing (SMP). Briefly, I am going to discuss how to order the software, where to download it and how to test the functionality of SMP. Before doing so I would like you to have a look at the excellent presentation I found on IBM’s website written by Kent Milligan titled “Db2 Symmetric Multiprocessing”. The last page of the PDF document is history due to this IBM i entitlement change because now it’s free. So, if you are interested in what SMP can do for you, after all the “Proof of the pudding is in the eating”. So now you have the option to give it a test drive yourself.

The ordering

The quickest way to order this software, is to place a software update order. If you are running IBM i 7.4 and do not intend to upgrade to 7.5 soon, you can still order this upgrade. All it does is make the software available for download and generate the necessary license keys. Another way to do this is to order the software through your IBM Business Partner.

The download

As this software is not available on the Multiple language version media – B_GROUPx_nn iso images, you need to go to the IBM Entitled Systems Support and go to the licensed link shown on the page after signing in with your IBM ID.

After selecting the correct IBM i version, this page is shown:

After selecting the “5770-SS1” pressing “Continue” twice, you are able to select SMP as shown below.

After the mandatory “I agree” you can select the option for download. Although the “Download Director” option is still offered, but I cannot remember when I for last used it, so “HTTPS” is the option I use. After uploading the zipped iso image using IBM Access Client Solutions (ACS) Integrated File System you can also run the unzip command offered there as shown below:

Once you have Option 26 of 5770SS1 installed it is time to get started.

The Testing

In order to discover what SMP adds in performance, the approach below is a simple process and it does not release SMP for all your SQL statements. This is something which can be done after you are happy with the results. The potential of SMP is high, I remember a customer who instead of having to purchase another IBM i processor license did not need to after implementing SMP.

Again, we are using ACS, but this time the SQL Performance Center. By default this will show the “Plan Cache” and there we select “Show Statements”. Here we select “Show”. Now it is up to you to select a SQL Statement with high processing time. After selecting this statement, use

 the option “Work with SQL Statement and Variables” as shown below:

When ACS Run SQL Script pops up, run the statement with “Visual Explain”. In the document titled “Database Performance and Query Optimization” all the icons available in Visual Explain are discussed. For every icon we can see if the Data access method “SMP parallel enabled” is available “yes” or “no”. Below is an example:

When testing make sure SMP is enabled somewhere for the SQL statement you are testing otherwise using SMP does not give you any advantage. Another way to discover if SMP is being used when executing SQL is by changing the settings as shown below:

Before running the SQL statement using SMP, Visual Explain will look similar to the screen shown below:

All arrows are single arrows, so SMP is not being used resulting in a Total Estimated Run Time of 338.163 ms. In order to enable SMP, we run the following SQL statement: “set current degree = ‘2’ “. With this statement 2 threads can be used to execute the SQL statement. Once we do that we get a different result:

Compared with the previous Visual Explain result we can clearly see the SMP on the arrows after the Table Scan icon and the Total Estimated run Time. The Optimization Time is 4 ms more but as the Total Estimated Run Time drops to 165.793 ms, we can clearly see the beneficial effect of SMP. In order to learn if raising the level once more improves the performance again the SQL “set current degree = ‘3’ “ needs to be run. This results in:

With a Total Estimated Run Time of 143.279 and an Optimization Time of 5 ms, we do not see the same improvement compared to moving from “1” to  “2”. All my testing was done on a very small LPAR with less than one core so your results may vary.  You have to keep in mind that using SMP will take away time from other tasks. If your environment can afford this loss is something only you can tell.

If you are going to unleash the power of SMP is up to you, but when you are running a lot of SQL is definitely worth giving it a try.

Once you have decided to implement SMP on IBM i, in the topic Objects processed in parallel it describes the different options on how to implement SMP. The SQL script below is an example within which can be found sample SQL for each of the options:

In the toolbox of the modern IBM i system administrator SQL is here to stay so you had better get used to this idea and learn SQL. Why not get started using SMP today?

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *