Optimize DB2 10.5 for Linux, UNIX, and Windows performance using InfoSphere Optim Query Workload Tuner with the DB2 BLU accelerator feature

Getting recommendations for column organization conversion

The new BLU Acceleration feature in IBM® DB2® 10.5 for Linux®, UNIX®, and Windows® can help improve the performance of your workload by converting row-organized tables to column-organized tables. However, the challenge is to know what tables could be converted and how much performance would improve. In this step-by-step article, you will learn how to use Optim Query Workload Tuner to perform column organization conversion, analyze what-ifs, and improve the performance of your workload.

Share:

Thuan Bui (tqbui@us.ibm.com), Technical Enablement Specialist, IBM

Author photoThuan Bui has worked more than 25 years for IBM in development, quality assurance, customer support, performance, and management positions in database and data management technology. He currently works on the IBM Optim Solutions Technical Enablement team at Silicon Valley Lab, California.



Soid Quintero (squinter@us.ibm.com), Software Engineer, IBM

Photo of Soid QuinteroSoid Quintero is a software engineer at IBM's Silicon Valley Lab in San Jose, CA. She focuses on customer technical enablement for Optim Database Tools. She has a Master's degree in Human Factors and Ergonomics from San José State University.



27 June 2013

Overview

Optimizing the performance of your workload is important to the business. It can reduce cost, improve customer satisfaction, and help your DBAs be more efficient. The new BLU Acceleration feature in IBM DB2 10.5 for Linux, UNIX, and Windows can help improve the performance of your current workload by converting row-organized tables to column-organized tables where the data pages contain compressed column data instead of row data. Column-organized data is memory-optimized, CPU-optimized, and I/O-optimized, which results in optimal performance.

To take advantage of this new feature, you need to know what tables could be converted and how much performance improvement would be made before you decide on the conversion. To help with these challenges, InfoSphere Optim Query Workload Tuner (OQWT) 4.1 is enhanced with the new Workload Table Organization Advisor that examines the tables referenced in a query workload and recommends some tables for conversion from row to column organization with estimated performance improvement.

Column-organized table feature is offered in the DB2 Advanced Workgroup Server Edition (AWSE), DB2 Advanced Enterprise Server Edition (AESE), and DB2 Developer Edition. The first two are also referred to as the Advanced Editions which are bundled with an assortment of database tools and features that help you during every stage of the data life-cycle and to exploit the newest features in DB2 10.5.

What's in the Advanced Editions

Tabel 1 provides a summary of the features and Optim Tool products that are included in the Advanced Editions.

Table 1. Optim tools that are included in the DB2 10.5 Advanced Editions
Product nameProduct features
InfoSphere Optim Performance Manager Extended Edition

Helps DBAs and IT staff manage performance pro-actively and prevent problems before they impact the business.

The new V5.3 includes new statistics to support monitoring of column-organized tables, additional alerts, and a simpler query tuning workflow.

InfoSphere Optim Query Workload Manager

Provides expert recommendations to help improve the performance of query workloads.

The new V4.1 includes usability enhancements and an advisor function to identify which row-organized tables could benefit from being converted to column-organized tables.

InfoSphere Optim Configuration Manager

Offers centralized management of database and client configurations.

The new V3.1 includes support for monitoring rolling updates and application isolation for DB2's pureScale clustering technology.

InfoSphere Data Architect

Provides a collaborative data design solution to discover, model, visualize, relate and standardize diverse and distributed data assets.

The new V9.1 includes support for column-organized tables and various diagramming and reporting enhancements.

InfoSphere Optim pureQuery Runtime

Provides a data access platform that enables improved performance, security, and manageability of database client applications.

The new V3.3 includes enhancements to SQL management and improved error reporting.

Objectives

This article will focus on InfoSphere Optim Query Workload Tuner, and more specifically, on its new Workload Table Organization Advisor. This new Workload Table Organization Advisor can analyze your workload and give you recommendations on which tables to convert from row to column organization, along with the estimated improvement.

You will learn how to use the new Workload Table Organization Advisor to generate recommendations, analyze what-ifs, and perform column organization conversion for your workload.


Getting started

The following highlights the prerequisites and setup needed to complete this article successfully.

Prerequisites

This article is written for users at an intermediate skill level. It is recommended that users have a general understanding of how to use Optim Query Workload Tuner and database administration principles. You do not need to have DB2-specific skills.

The following software should be installed:

  • IBM Data Studio 4.1
  • IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows version 4.1
  • IBM DB2 10.5 for Linux, UNIX, and Windows that supports column-organized feature such as AWSE, AESE, or Developer Edition.

Download the sample scripts for this article located in the Downloads section.

Creating the sample database

This article uses the sample database GSDB. This database contains data from a fictional outdoor equipment selling company called the Great Outdoors company. Note that these scripts have been modified for the use of this article. The following steps will help you create the sample database GSDB.

  1. Extract the contents of the archive file GSDB database scripts.
  2. Ensure that the user ID you are using has the appropriate authority to create the database.
  3. If you are going to create the database in a Windows environment, navigate to the win directory. If you are creating the database in a Linux or UNIX environment, navigate to the unix directory, make sure you have the correct permissions to execute the setup script, and then type chmod u+x setupGSDB.sh.
  4. Run the setup script to create the sample database GSDB. For Windows, type setupGSDB.bat and follow the prompts on the command line. For Linux or UNIX, type ./setupGSDB.sh and follow the prompts on the terminal.

When the database is finished being created, we will need to configure it for BLU.

  1. Open a DB2 command prompt and run the sample script modify.GSDB.BLU.sql. This script will modify the GSDB database to allow you to work with column organized tables.
  2. Type db2 -tvf modify.GSDB.BLU.sql to run the script. When the script is done running, DB2 will need to be restarted.

Capturing the Workload

The following section will walk you through the process of capturing the SQL workload using InfoSphere Optim Query Workload Tuner.

  1. Open Data Studio to a new workspace location.
  2. From the Data Source Explorer, create a database connection to GSDB. For more help on how to connect to a database in Data Studio, refer to the Resources sections.
  3. Right-click on the database connection for GSDB and select Analyze and Tune > Start Tuning, as shown in Figure 1.
    Figure 1. File Start Tuning database GSDB
    This figure shows how to select Analyze and Tune to start tuning the database.
  4. The Query Tuner Workflow Assistance will open. Select File from the workflow assistance menu, click Browse, and choose the sample workload file you downloaded in the Getting Started section, as shown in Figure 2.
    Figure 2. Browse for and capture workload file
    This figure shows the Browse button used to navigate to a file path to Capture Workload File
  5. Click Capture to start capturing your workload, and wait for it to finish being captured.
  6. Click Save All to Workload to save your workload, as shown in Figure 3.
    Figure 3. Save captured workload
    This figure shows the Save All to Workload button that is clicked to save the Captured Workload.

The Manage view will open, which allows you to see what workloads you have set up for the data source connection, in this case GSDB. You can do a few things under this manage view for a workload, which shows the workload statements and tasks, or you can go into the Invoke phase to launch advisors for a given workload.


Running the Statistics Advisor and following the recommendations

You are now ready to invoke the Workload Advisor and Tools on the captured workload. The first advisor that you will run will be the Statistics Advisor. It is necessary that the Statistic Advisor be run first to correct any missing statistics information before running other advisors. Bad statistics can lead to recommendations that are not the most optimal. After you run the statistics advisor, you will accept and run all the recommendations from the advisor.

Running the Statistics Advisor

  1. To Invoke the Workload Advisors and Tools, first select the workload you have captured from the table.
  2. Click the Invoke Workload Advisors and Tools icon or right-click the workload and choose Invoke Workload Advisors and Tools from the context menu, as shown in Figure 4.
    Figure 4. Invoke Workload Advisors and Tools
    This figure shows how to Invoke workload advisor and tools.
  3. In the Run Workload Advisors view, select the Re-collect EXPLAIN information before running workload advisors option and then click on the Select What to Run... button, as shown in Figure 5. You will be able to select which of the advisors to run on the workload.
    Figure 5. Run workload Advisor, for Statistics advisor only
    This figure shows how to select which advisors to run on the workload.
  4. Ensure that only the Statistics option is selected, then click OK to invoke the Statistics Advisor.
  5. You do not need to enter any information in the collect EXPLAIN Information window. Click Start EXPLAIN.

Running the recommendations from the Statistics Advisor

While the advisor is running, the status of what is being executed will be shown. When the execution of the advisor has finished, the Open Workload Recommendations view will open. Since this article will focus mainly on the new advisor of OQWT, you will need to accept whatever statistic recommendations are given and will not go into the details.

  1. Double-click the Statistics tab to view the recommendations, as shown in Figure 6.
    Figure 6. View the Statistics recommendations
    This figure shows the Statistics tab to view the statistics recommendations
  2. Click the View RUNSTATS button to view detailed information about the recommended RUNSTATS.
  3. In the Review Recommended RUNSTATS Commands window, click Next.
  4. In the Run or Save window, select RUN AND SAVE TO PROFILE and then click Finish to run the RUNSTATS commands, as shown in Figure 7.
    Figure 7. Review recommended RUNSTATS commands
    This figure shows the Run or save statistics using the RUNSTATS command.

When RUNSTATS completes, you will get a success message.


Running the Table Organization Advisor and following the recommendations

Running the Workload Table Organization Advisor

  1. Go back to the Invoke tab in the Query Tuner Workflow Assistance window, as shown in Figure 8.
    Figure 8. Invoke tab in Workflow Assistance
    This figure shows the Invoke Tab in Workflow Assistance view
  2. Select the Re-collect EXPLAIN information before running the workload advisors option. Since the statistics have been changed, this may cause the optimizer to pick different plans based on the updated statistics, so it's best to re-collect EXPLAIN information again.
  3. Click the Select What to Run... button.
  4. Select the Statistics and Table organization check boxes, then click OK, as shown in Figure 9.
    Figure 9. Select Advisors
    This figure shows the Select Activities dialog, which includes Statistics and Table organization options.
  5. Click Start EXPLAIN in the Collect EXPLAIN Information dialog.
  6. Before the Workload Table Organization Advisor starts to run, you will see a warning message, but go ahead and click Continue. This warning message is just a reminder to run the Statistics Advisor before running the Workload Table Organization Advisor.
  7. When the advisors are done running, the Summary results will indicate that no new statistic recommendations were generated. However, you will see recommendations for the Table Organization Advisor, as shown in Figure 10.
    Figure 10. Review new Workload Advisor Recommendations
    This figure shows the Review Workload Advisor Recommendations

Reviewing the recommendations from the Workload Table Organization Advisor

The following section will review the recommendations that were generated by the Workload Table Organization Advisor.

  1. Select the Table Organization tab, as shown in Figure 11, to view the recommendations.
    Figure 11. Workload Table Organization advisor estimates
    This figure shows the Table organization tab with shows the Estimated performance improvement.
    The table organization tab shows the estimated performance improvements if the suggested tables are converted from row-based to column-based organization, along with the warning about related changes such as removal of associated indexes. The list of SQL statements that would be affected if these tables are converted are also shown.
  2. You can virtually convert row-organized tables to column organization to determine whether the conversion would improve the performance of a query workload. Click on the Test Candidate Table Organization button. This will take you to the Workload Test Candidate Table Organization tab, which shows the list of tables pre-selected by the advisor for the conversion, as well as the list of tables not selected by the advisor.
  3. You can explore different combinations by selecting or clearing the check boxes of these tables and comparing the results of the testing. For example, see what estimated improvements happen when you exclude the table CUST_ORDER_HEADER. Clear the check box next to the table CUST_ORDER_HEADER and click the Test Candidate Table Organization button, as shown in Figure 12.
    Figure 12. Test Candidate Table Organization button
    This figure shows the Test Candidate Table Organization button, as well as clearing or selecting the check boxes for the tables to compare results of testing.
  4. When the task completes, you will see a new Candidate Table Organization item. Click the Candidate Table Organization tab to view the workload recommendation details.
  5. Notice the new estimated performance improvements for the two selected tables. It is less than the performance improvement for conversion of three tables, as shown in Figure 13.
    Figure 13. Review the Test Candidate Table Organization results
    This figure shows the test review and performance improvement for conversion of three tables.

You can continue to test what-if analysis scenarios for different combination of tables by repeating this process.

Running the recommendations from the Workload Table Organization Advisor

  1. Go back to the Table Organization tab and click the Show DDL Script button, as shown in Figure 14.
    Figure 14. Show DDL script button
    This figure shows the Show DDL Script button.
  2. The DDL to convert the columns for the tables GOSALES.PRODUCT_NAME_LOOKUP, GOSALESCT.CUST_ORDER_HEADER, and GOSALESCT.CUST_ORDER_DETAILS is displayed. Review the DDL. Notice that you can save the DDL or copy the contents to your clipboard.
  3. Click the Copy to Clipboard button, as shown in Figure 15, to copy the script, and then click OK to exit the DDL for Selected Tables window.
    Figure 15. Copy DDL to clipboard
    THis figure shows the Copy To Clipboard button used to copy the script.

    Note: Since the row-organized tables will be deleted along with the associated indexes before the column-organized tables are created, you may want to back up your database in case you need to revert to the row-organized tables.

    If you want to compare the performance of the workload before and after the conversion, you can run the sample workload before the tables are converted using your favorite tool. For example, you can copy the sample workload and paste it into the New SQL Editor Script from Data Source Explorer, and run it.

  4. To run the DDL that was generated by the Workload Organization Advisor, from the Data Source Explorer, right-click the GSDB database connection and select New SQL Script.
  5. In the new SQL Script editor, paste the DDL you copied to the clipboard, as shown in Figure 16.
    Figure 16. Paste DDL to SQL editor
    This figure shows the Paste DDL to Editor used to paste the DDL.
    .
  6. Click OK in the Statement Terminator pop-up.
  7. Click the Run button to execute the DDL, as shown in Figure 17.
    Figure 17. Run DDL
    This figure shows the Run DDL button used to execute the DDL.
    The status results are displayed in the SQL results view.

    Note: If you ran the sample workload before conversion, you can run it again after the tables are converted. You should see the workload runs faster, depending on the system environment.


Conclusion

In this article, you learned how to use the Workload Statistics Advisor to generate and run the recommended RUNSTATS. You then used the Workload Table Organization Advisor to generate recommendations for conversion from row-organized to column-organized tables along with estimated performance improvement and the script for the conversion. You ran the test candidates to analyze what-ifs, and finally ran the DDL to convert the tables.


Downloads

DescriptionNameSize
Sample workload to capturesampleWorkload.zip1KB
GSDB database scriptsGSDB_databases.zip47KB
GSDB database scriptsmodify.GSDB.BLU.zip4KB

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial for OQWT or DB2 today.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=935317
ArticleTitle=Optimize DB2 10.5 for Linux, UNIX, and Windows performance using InfoSphere Optim Query Workload Tuner with the DB2 BLU accelerator feature
publish-date=06272013