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 name||Product 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.
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.
The following highlights the prerequisites and setup needed to complete this article successfully.
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.
- Extract the contents of the archive file GSDB database scripts.
- Ensure that the user ID you are using has the appropriate authority to create the database.
- 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.
- Run the setup script to create the sample database GSDB. For Windows,
setupGSDB.batand follow the prompts on the command line. For Linux or UNIX, type
./setupGSDB.shand follow the prompts on the terminal.
When the database is finished being created, we will need to configure it for BLU.
- 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.
db2 -tvf modify.GSDB.BLU.sqlto 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.
- Open Data Studio to a new workspace location.
- 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.
- 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
- 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
- Click Capture to start capturing your workload, and wait for it to finish being captured.
- Click Save All to Workload to save your workload, as
shown in Figure 3.
Figure 3. Save 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
- To Invoke the Workload Advisors and Tools, first select the workload you have captured from the table.
- 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
- 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
- Ensure that only the Statistics option is selected, then click OK to invoke the Statistics Advisor.
- 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.
- Double-click the Statistics tab to view the
recommendations, as shown in Figure 6.
Figure 6. View the Statistics recommendations
- Click the View RUNSTATS button to view detailed information about the recommended RUNSTATS.
- In the Review Recommended RUNSTATS Commands window, click Next.
- 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
When RUNSTATS completes, you will get a success message.
Running the Table Organization Advisor and following the recommendations
Running the Workload Table Organization Advisor
- 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
- 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.
- Click the Select What to Run... button.
- Select the Statistics and Table
organization check boxes, then click OK,
as shown in Figure 9.
Figure 9. Select Advisors
- Click Start EXPLAIN in the Collect EXPLAIN Information dialog.
- 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.
- 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
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.
- Select the Table Organization tab, as shown in Figure
11, to view the recommendations.
Figure 11. Workload Table Organization advisor estimates
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.
- 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.
- 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
CUST_ORDER_HEADER. Clear the check box next to the table
CUST_ORDER_HEADERand click the Test Candidate Table Organization button, as shown in Figure 12.
Figure 12. Test Candidate Table Organization button
- 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.
- 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
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
- 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
- The DDL to convert the columns for the tables
GOSALESCT.CUST_ORDER_DETAILSis displayed. Review the DDL. Notice that you can save the DDL or copy the contents to your clipboard.
- 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
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.
- 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.
- 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
- Click OK in the Statement Terminator pop-up.
- Click the Run button to execute the DDL, as shown in
Figure 17. Run 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.
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.
|Sample workload to capture||sampleWorkload.zip||1KB|
|GSDB database scripts||GSDB_databases.zip||47KB|
|GSDB database scripts||modify.GSDB.BLU.zip||4KB|
- Take a look at the DB2 with BLU Acceleration microsite.
- Get more information and help on how to install the GSDB sample database.
- Find helpful links on how to install and set up InfoSphere Optim Query Workload Tuner.
- Learn how to connect to a database in IBM Data Studio.
- Read the "What's new in DB2 10.5 for Linux, UNIX, and Windows" article (developerWorks, April 2013) for in-depth information on the key features of DB2 10.5 for Linux, UNIX and Windows.
- Learn how to use Optim Query Workload Tuner.
- Learn more about tuning query workloads.
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
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.
- Participate in the discussion forum.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.