Optim Query Workload Tuner and BLU Acceleration
This chapter demonstrates how IBM InfoSphere Optim Query Workload Tuner is used with DB2 with BLU Acceleration. If you have an existing DB2 9.7 or 10.1 data warehouse and are debating if a near-term upgrade to DB2 10.5 with BLU Acceleration is worthwhile, this tool can help you make decisions by estimating the performance increase for your analytic workloads when running with BLU Acceleration. If you have mixed workloads for your data environment and considering making column-organized tables as an alternative to speed your analytic workloads, this tool can also be used to help select column-organized table candidates for defining shadow table copies or for conversion to optimize analytic performance.
The following topics are covered:
4.1 Planning and testing BLU Acceleration with IBM InfoSphere Optim Query Workload
DB2 with BLU Acceleration delivers a set of breakthrough technologies and can improve performance of complex analytic queries dramatically. Testing with BLU Acceleration can be simple. With just a few commands, you can create an analytics-workload database and start loading your data to explore the benefits of BLU Acceleration in your environment.
In a data warehouse or data mart environment dedicated for analytic workloads, one can easily test and witness the performance improvements from loading all user data (or all tables that are referenced in complex analytic workloads) into BLU Acceleration column-organized tables. It is not necessary to use the Workload Table Organization Advisor (WTOA) in Optim Query Workload Tuner (OQWT) for column-organized table candidate recommendations. Although a mixture of row-organized tables and column-organized tables can coexist in the same database, BLU Acceleration provides best performance by joining column-organized tables of the same type.
Optim Query Workload Tuner is licensed with DB2 Advanced Editions. In the new version of Optim Query Workload Tuner 4.1.1, the GUI capability is enhanced to support BLU Acceleration. It comes with a Workload Table Organization Advisor that helps make decisions on table organization based on provided workloads.
In this chapter, we review two common use cases where you might find Optim Query Workload Tuner useful.
4.1.1 Use case 1: Databases with mixed workloads
One of the many unique advantages of DB2 with BLU Acceleration is that traditional row-organized tables and BLU Accelerated column-organized tables can coexist within one database. Businesses might need to run up-to-the-minute analytics directly from OLTP databases. With BLU Acceleration supported in mixed workloads environments, you can choose to convert or load only the analytic-focused tables into column-organized tables to speed analytic insights.
In cases where the same tables are accessed in both OLTP and OLAP workloads, users can benefit from the shadow table capability included in DB2 Cancun Release 10.5.0.4. Users can create column-organized shadow table copies with data replicated asynchronously from row-organized tables. As a result, OLTP workloads have the same regular transactional performance from accessing the original row-organized tables, while OLAP workloads referencing the same tables can be rerouted to column-organized shadow table copies for enhanced analytic performance.
If you have mixed workloads, you might choose to use Optim Query Workload Tuner 4.1.1 to help make test decisions and plan for a BLU Acceleration adoption. Workload Table Organization Advisor helps analyze ongoing workloads, and recommends table candidates that might benefit from storing in column-organized format. Users can then decide whether they should convert all recommended tables into column-organized tables or define those as shadow table copies in the same environment.
4.1.2 Use case 2: DB2 10.5 with BLU Acceleration upgrade
Mixed workloads environment is not the only use cases for Optim Query Workload Tuner. For existing DB2 9.7 or 10.1 data warehousing users, you can use Optim Query Workload Tuner to help decide whether a near-term upgrade to DB2 10.5 with BLU Acceleration is worthwhile for you. Workload Table Organization Advisor in Optim Query Workload Tuner uses cost-based approach to determine if existing row-organized tables would benefit from conversion to column-organized tables for a provided workload. It helps evaluate and understand the possible estimated performance gain when using BLU Acceleration. This helps make upgrade decisions easier.
4.2 How the Workload Table Organization Advisor works
When the advisor is run, the tool first determines the cost estimation for the workload with existing row-organized tables. For tables that are referenced in the provided workload, it virtually converts qualifying row-based tables into column-organized format, then compares the cost differences of the queries before and after the virtual column-organized conversion. It then provides column-organized table candidate recommendations if they have performance improvements higher than a user-defined minimum performance improvement threshold.
For those tables that BLU Acceleration does not support (for example, those that contain LOB and XML columns), the tool might recommend as shadow table candidates if DB2 engine finds improved performance for the supported columns to be stored as shadow tables.
Because the tool performs virtual conversion in the analysis, it does require the database being analyzed to have BLU Acceleration enabled. With that said, it is not necessary to have all data reside in the BLU-enabled DB2 10.5 database. A common scenario is to use db2look to replicate the current database catalog and statistics into a new, empty DB2 10.5 database with BLU Acceleration enabled. With the tool, you can capture a representative workload from existing database for the analysis. The advisor can then perform the workload table organization analysis using the replicated database catalog and statistics info.
Figure 4-1 shows a high-level workflow to capture workloads from current DB2 database and analyze the performance gains moving to DB2 10.5 with BLU Acceleration.
Figure 4-1 Workflow for using Workload Table Organization Advisor in Optim Query Workload Tuner
Of course, for those who already have a test database loaded in DB2 10.5, you can always run the Workload Table Organization Advisor directly from the test database without using db2look. Remember to enable BLU Acceleration parameters in the test database for Workload Table Organization Advisor to work properly.
4.2.1 Scenario used in this chapter
In this chapter, we demonstrate Optim Query Workload Tuner with a mixed workload use case. We use a Sample Outdoors database from Cognos, GS_DB, our scenario. Throughout the example, GS_DB101 represents a DB2 10.1 OLTP database that is currently being used; GS_DB105 represents a new, “empty” DB2 10.5 database that has the OLTP database catalog and statistics replicated.
The following major steps are involved in analyzing workloads with Optim Query Workload Tuner:
To avoid changes and impact in the currently running DB2 10.1 OLTP database, we first run steps 1 and 2 against the current database to capture and save a representative workload. Consequently, we run steps 2 to 4 to generate analysis using the replicated catalog and statistics on the DB2 10.5 database.
 
Note: Although this scenario uses Cognos BI workloads as an example, DB2 with BLU Acceleration is compatible with other business intelligence solutions.
4.3 Prerequisites
To use Workload Table Organization Advisor offered in Optim Query Workload Tuner, you must install the software. The following software is used for this demonstration:
•IBM InfoSphere Optim Query Workload Tuner 4.1.1 (entitled with any DB2 Advanced Editions)
This includes the following two components from the Optim Query Workload Tuner installer:
 – IBM Data Studio client 4.1
 – A license activation kit for InfoSphere Optim Query Workload Tuner 4.1
For more information about installing Optim Query Workload Tuner, go to the following location:
•IBM DB2 10.5 for Linux, UNIX, and Windows
For a full list of DB2 editions that support BLU Acceleration, see Chapter 2, “ Planning and deployment of BLU Acceleration” on page 17.
•IBM Cognos Business Intelligence Server 10.2 or 10.2.1
If you have another business intelligence (BI) software solution in your current infrastructure, you can optionally replace the Cognos BI Server component with your own BI solution. Of course, you are welcome to try the Cognos BI solution as an alternative for your BI environment. All DB2 10.5 with BLU Acceleration supported editions include license entitlements to five authorized entitlement of Cognos BI.
For more information, see the DB2 10.5 with BLU Acceleration announcement letter:
4.4 Preparing an empty DB2 10.5 database with current objects and statistics using db2look
This section describes the preparation steps for users who are looking to use Workload Table Organization Advisor in Optim Query Workload Tuner without making any changes to the current non-BLU environment. For users who are planning to run the analysis on the same database, you can skip this preparation and go directly to 4.5, “Step 1: Capturing existing workloads for analysis” on page 144.
The Workload Table Organization Advisor in Optim Query Workload Tuner is the advisor to estimate performance improvements of the workload in row-organized or column-organized tables. The tool requires database catalog and statistics to complete the analysis. Therefore, it is not necessary to migrate all the data. The db2look command allows us to quickly mimic the current database with similar structures and statistics in an “empty” DB2 10.5 with BLU Acceleration database for analysis.
Complete the following steps:
1. Before extracting database objects and statistics on the current database in an existing DB2 database, ensure that statistics are updated. Otherwise, use RUNSTATS to update statistics of database objects in the system catalog before proceeding to the next steps.
2. On the current database, run the db2look command to extract the database object definitions and statistics into a file. Example 4-1 illustrates a sample db2look command that extracts all user-defined database objects and statistics along with registry variables into an output file.
Example 4-1 Sample db2look command
db2look -d GS_DB101 -a -e -m -l -x -f -o db2look.sql
The command uses the following parameters:
-d GS_DB101 Specifies the name of the database (in our example, GS_DB101).
-a -e Generates the DDL statements for all objects in the database.
-m Runs in mimic mode; generates UPDATE statements to replicate statistics on tables, views, columns, and indexes.
-l Generates DDL statements for user-defined objects such as table spaces, storage groups and buffer pools.
-x Generates DDL statements for authorizations (optional for this use case).
-f Generates statements to reproduce configuration parameters and registry variables that affect the query optimizer.
-o db2look.sql Writes all generated statements to an output file (in our example, db2look.sql).
3. In a DB2 10.5 test environment, create a new database instance and set DB2_WORKLOAD=ANALYTICS. Then, create a new database with the default analytics settings, as demonstrated in Example 4-2. For further details about how to create a new DB2 10.5 with BLU Acceleration database, see 2.5.2, “New database deployments” on page 26.
Example 4-2 Create a new, empty DB2 with BLU Acceleration database
db2set DB2_WORKLOAD=ANALYTICS
db2stop
db2start
db2 create db GS_DB105
4. Review the db2look.sql output file generated from step 2 on page 142. The script has CONNECT TO statements that connect to a database and re-create the extracted objects and statistics. Modify the script to connect to the newly created DB2 10.5 database from step 3. Review the same file and comment out all DBM CFG and DB CFG parameters that might disable BLU Acceleration. For parameters that are suggested in BLU Acceleration, see to 2.6, “Configuration preferred practices for BLU Acceleration deployment” on page 31.
5. On the newly created DB2 10.5 with BLU Acceleration database, run the db2look.sql script modified from step 4. Example 4-3 demonstrates a command to execute the updated db2look.sql script and reproduce the database objects and statistics.
Example 4-3 Execute previously modified db2look script to reproduce database objects and statistics on new database
db2 -tvf db2look.sql
You now have a DB2 10.5 with BLU Acceleration database that mimics your current database objects and statistics from an existing DB2 database. You are now ready to run Workload Table Organization Advisor and estimate performance increase of your current workload with DB2 10.5 with BLU Acceleration.
4.5 Step 1: Capturing existing workloads for analysis
Before Optim Query Workload Tuner can provide table organization analysis and recommendations on the current workload, you must first provide the tool a good representation of your existing workload for analysis.
Optim Query Workload Tuner can capture existing workloads from various sources. For example from package cache, event monitor tables, a SQL procedure, triggers that use to compile SQL statements, user-defined functions with compiled SQL statements, views, a SQL file and so on. In our example, we use the DB2 package cache to capture our current BI workloads. Any incoming dynamic and static SQL coming through DB2 database manager is regularly recorded in the DB2 package cache. This helps benefit different applications agents that access the same queries. In case of dynamic SQL query statements, it helps reduce the internal compilation costs in DB2. Because package cache has limited size, only recent statements are found here.
In our scenario, our Cognos BI users have been running their usual analytic workloads (that is, Cognos Dynamic Cube sample reports in our case) from Cognos. Analytic queries that require access to the underlying database are collected in the DB2 package cache. By capturing workloads in DB2 package cache, we capture the usual workloads executed by our BI users.
 
Tip: You can also use other products in the IBM Optim tools suite with BLU Acceleration. For instance, you can use Optim Workload Replay to capture existing workloads as you always used to. Optim Query Workload Tuner can take those workloads, captured by Optim Workload Replay, to run table organization analysis. For more information, see this web page:
You can also use the Optim Performance Manager to monitor your workload performance.
To capture existing workloads for table organization analysis, follow these demonstration steps:
1. Open Data Studio with a new workload location.
2. By default, Data Studio opens the Database Administration perspective. Click the Open Perspective icon () at the top-right corner. Select IBM Query Tuning in the Open Perspective window, as shown in Figure 4-2.
Figure 4-2 Open IBM Query Tuning perspective
3. From the Data Source Explorer panel, connect to a database by right-clicking the database name, clicking Connect, and providing database connection information.
4. In Data Source Explorer, right-click the database connection from the previous step, and select Analyze and Tune → Start Tuning, as shown in Figure 4-3.
Figure 4-3 Start Tuning option for database
If the Analyze and Tune option is not available, ensure that Optim Query Workload Tuner License is enabled on the target database. By default, the Optim Query Workload Tuner License Activation Kit is included with the installation package. After the activation kit is installed, you can configure your DB2 databases for query workload tuning using IBM Data Studio client, as follows:
a. Run the License.sh file (on Linux) or License.bat file (on Windows) from the IBM Data Studio 4.1 installation directory. By default, Optim Query Workload Tuner Activation Kit is in the following directory, where /opt/IBM/DS4.1.0/ is the default Data Studio installation directory (DS_installdir):
 • On Linux:
<DS_installdir>/QueryTunerServerConfig/all_features/LUW/License
 • On Windows:
<DS_installdir>QueryTunerServerConfigall_featuresLUWLicense
b. In Data Studio, right-click the database and select Analyze and Tune → Configure for Tuning → Guided Configuration.
For more information about configuring databases for query workload tuning and the necessary authorities and privileges, go to this web page:
5. The Query Tuner Workflow Assistant panel opens, with the 2. Capture view displayed, and is ready to capture workloads from various sources. Under DB2 for Linux, UNIX, and Windows Sources, select Package Cache. In this example, we capture workloads from the DB2 Package Cache for analysis (Figure 4-4). You can also select other workload source types available in the list.
For details of each source types available for workload capturing, see the following web page:
Figure 4-4 Capture workloads from the DB2 Package Cache
6. On the panel at the right side, the Capture SQL from Package Cache page opens. As shown in Figure 4-5, click New (under Step 1) to define a filter for collecting SQL statements from the DB2 package cache.
Figure 4-5 Define new or existing filter for capturing workloads
7. You can select a variety of filters. For example, dynamic or static statements, statements that run over certain milliseconds, statements that have been executed over a specific number of times, and so on.
If you have an analytics environment and are looking to analyze possible performance improvements for only long, complex analytic queries, you can define a filter on statement execution time. Figure 4-6 illustrates filter definition with STMT_EXEC_TIME > 60000 ms as a demonstration, that is, any statements that have spent over 1 minute to execute. You can specify a different filter according to your environment and the workload type you want to capture.
Figure 4-6 Defining a filter on STMT_EXEC_TIME
8. Go through the wizard to define the filters you want for capturing workloads. Then, click Capture Now (under Step 2) to start capturing workloads from DB2 package cache, as demonstrated in Figure 4-7.
Figure 4-7 Starting capturing workloads
9. SQL statements that match the defined filter are displayed, as demonstrated in Figure 4-8.
Figure 4-8 Captured workloads
10. Users can save all captured statements as a workload and proceed with the advisor tools. Click Save All to Workload to save the workload as a unique name, as shown in Figure 4-9.
Figure 4-9 Save captured statements to workload
4.6 Step 2: Managing a list of captured workloads
After capturing representative workloads from a currently running database, you can export the captured workloads from the Manage tab. Subsequently, start a new Optim Query Workload Tuner project that connects to the DB2 10.5 database created in 4.4, “Preparing an empty DB2 10.5 database with current objects and statistics using db2look” on page 142, then import the captured workloads in the new project for further analysis.
For cases where the query workload analysis should be run from the same database (for example, the mixed workload use case), you can skip to 4.6.2, “Invoking Workload Table Organization Advisor” on page 153 for procedures to start the Workload Table Organization Advisor.
4.6.1 Exporting and importing captured workloads
In the 3. Manage section of the workflow assistant, you can manage the list of previously captured workloads and invoke the desired advisors from here. For users who want to analyze the performance gains moving to DB2 10.5 with BLU Acceleration, complete the following steps to import captured workloads in a DB2 10.5 Optim Query Workload Tuner project:
1. To export captured workloads, in the 3. Manage section, click Export Workload as illustrated in Figure 4-10. A pop-up window prompts for the location where the captured workloads should be exported. Save the workload in a .zip file.
Figure 4-10 Export captured workloads
2. Follow step 4 on page 146 in 4.5, “Step 1: Capturing existing workloads for analysis” on page 144 to connect to a DB2 10.5 database and start a new Optim Query Workload Tuner project. To import captured workloads when a new Optim Query Workload Tuner project is connected to a DB2 10.5 database, click Import Workload in the 3. Manage section, as illustrated in Figure 4-11. A pop-up window prompts for the workload to import. Open the previously exported workload from step 1 to import.
Figure 4-11 Import previously captured workloads
After the captured workload is imported into an Optim Query Workload Tuner project connected to a DB2 10.5 database, you are ready to run the Workload Table Organization Advisor to start the analysis.
4.6.2 Invoking Workload Table Organization Advisor
To start the Workload Table Organization Advisor, in the Manage view, select the captured workload and click Invoke Advisors to start the advisor tools, as shown in Figure 4-12. Alternatively, you can also select the workload and right-click, select Invoke Workload Advisor and Tools from the pop-up menu.
Figure 4-12 Invoke Advisors for the selected workload
4.7 Step 3: Running the Workload Table Organization Advisor
The Invoke view opens after you selected the Invoke Advisors option for your captured workload. In this view, you can select the options of which advisors to run for your workload. This is where you start the Workload Table Organization Advisor.
 
Note: By default, Workload Table Organization Advisor provides recommendations for column-organized table candidates when estimated performance improvement of the workload is greater than a minimum threshold of 20%. To customize this minimum threshold for your analysis, go to 4. Invoke → Workload → Set Advisor Options to change the Minimum threshold for estimated performance improvement setting before running Workload Table Organization Advisor.
Use the following steps to run Workload Table Organization Advisor:
1. The 4. Invoke section opens after you start Workload Advisor as demonstrated in 4.6, “Step 2: Managing a list of captured workloads” on page 151. Select Re-collect EXPLAIN information before running workload advisors in the Run Workload Advisors page. This ensures that the advisors have access to latest EXPLAIN information for SQL statements in the workload. Click Select What to Run, as shown in Figure 4-13. A window opens where you can select which of the advisors to run for this workload.
Figure 4-13 Select what (advisor) to run for the captured workload
2. In the Select Activities panel, select Table Organization, as demonstrated in Figure 4-14. The Workload Table Organization Advisor opens. It runs an analysis against current workload and generates recommendations for candidate tables that can benefit the workload when storing in column-organized tables rather than in row-organized.
Figure 4-14 Selecting the Table organization advisor
 
Note: When Table organization is selected, some advisors are disabled. These advisors are not applicable to column-organized tables, therefore they cannot be run with Workload Table Organization Advisor at the same time. For example, if tables are recommended as column-organized by Workload Table Organization Advisor, indexes are not required and its relevant option disabled. BLU Acceleration automatically creates and maintains metadata objects within DB2 to achieve fast performance and great storage savings.
Important: If you are running Workload Table Organization Advisor on the same database you captured workload on, before using the advisor to collect table organization recommendations, run statistics advisor twice to ensure that the Workload Table Organization Advisor has the latest RUNSTATS statistics for the analysis. This ensures more accurate estimates for the workload analysis. Note that this is not required when advisor is running against an empty database with statistics replicated from another existing database.
To run statistics advisor, select Statistics in the Select Activities panel (Figure 4-14 on page 155). The advisor provides recommended RUNSTATS commands if statistics are required. Follow the wizard to run RUNSTATS using the Statistics Advisor tool. Repeat a second time until the advisor gives no new recommendations for statistics. Subsequently invoke Workload Table Organization Advisor for the actual column-organized table recommendation analysis.
3. Optionally, specify the filters in the Collect EXPLAIN Information panel and click Start EXPLAIN to continue. If in doubt, leave it blank and click Start EXPLAIN.
4. You might see a warning recommending that the Workload Statistics Advisor be run twice before Workload Table Organization Advisor. If you ensured that the catalog statistics are updated at this point, read the warning and click Continue. This is a reminder to ensure that statistics information is most updated before running Workload Table Organization Advisor.
4.8 Step 4: Reviewing the table organization summary
When Workload Table Organization Advisor is running, it virtually converts tables from row-organized to column-organized and generates cost estimates before and after the conversion. When the cost improvement is better than the user-defined minimum performance improvement threshold, the advisor provides recommendations on ideal candidates to store in column-organized format for the provided workload. For those tables that cannot be converted to column organized (such as tables that contain LOB/XML columns), the advisor analyzes whether the supported columns are ideal candidates as shadow tables. When the advisor finishes the analysis, you are ready to review the advisor summary.
To review the table organization summary, continue with these steps:
1. The 5. Review tab opens after the advisor completes running. On the Review Workload Advisor Recommendations page, summary of the advisor results are listed under the Summary tab. In this example (Figure 4-15), new recommendations are generated for table organization. You can double-click Table Organization under the Summary tab or click the Table organization tab to view the detailed recommendations.
Figure 4-15 Workload Advisor Recommendations summary view
2. The Table organization tab shows a list of tables. These tables are identified as candidate tables that can benefit from column-organization. In this scenario (Figure 4-16 on page 158), all ten tables found in the workload are recommended to be converted as column-organized tables. An estimated performance improvement is displayed at the top of the page. In this example, advisor estimated that storing the listed candidates in column-organized tables will likely gain estimated performance improvement of 74%.
Figure 4-16 Table Organization Advisor Results
If you are evaluating only the estimated performance gain to evaluate if a near-term upgrade to DB2 10.5 with BLU Acceleration is worthwhile, you can stop here and use this possible improvement estimation of your workload as part of your decision making. Because this estimated number is generated from estimated costs from DB2 engine when it mimics column-organized tables in the catalog, the actual performance when data is loaded into column-organized tables can be different.
Proceed with a regular test cycle to check the performance improvement with data loaded. If the database you are considering to upgrade is purely for analytic workloads, attempt to test load all your data being used in your queries into column-organized tables, even though the advisor might only suggest a subset as candidate tables. Queries generally perform optimally with tables of the same type.
Continue with the next steps if you are using the tool to select a list of candidate column-organized tables in your mixed workloads environment.
3. The top grid of the Table organization tab (Figure 4-17 on page 159) shows a list of recommended column-organized tables, along with the Conversion warning about the suggested row- to column-organization conversion. These changes are related to the characteristics of BLU Acceleration. For example, user-defined indexes are not required in BLU Acceleration, therefore, any existing secondary indexes are removed when a row- to column-organization conversion takes place. Enforced referential integrity (RI) constraints are changed to NOT ENFORCED. These not-enforced key constraints require less storage and time to create, and are beneficial to the BLU Acceleration query optimizer.
By default, this list is filtered to only display the list of tables recommended for column-organization tables. To view all the tables referenced in the workload, or tables that are recommended to keep their original table organization, you can optionally select the desired options from the “Filter by” drop-down list.
4. If you scroll the list of advisor-recommended tables to the right, as demonstrated in Figure 4-17, you see these columns:
 – The Findings column indicates the reasons why the list of tables are suggested to be converted (or not). A common finding is improved performance for column-organized table recommendation.
 – The IUDM Statements column specifies the number of INSERT, UPDATE, DELETE, and MERGE statements that reference the table in the workload.
 – The Cumulative Total Cost column indicates the total CPU cost spent on the tables in the captured workload.
 – The Reference to Table column states the number of times the table is referenced in the captured workload.
Figure 4-17 Recommended column-organized table candidates and advisor findings
You might see some recommendations are listed as row- to column-organized conversion candidates, and some are listed as shadow table candidates. For any tables that can benefit from column-organized store, Workload Table Organization Advisor first recommends converting tables from row-organized into column-organized. Subsequently it considers the rest as possible shadow table candidates if applicable. Usually when the table is not compatible for a conversion (for example, when table has LOB, XML columns), or when the advisor finds that there are enough OLTP workloads referencing the table. In the advisor, no tables are recommended as both column-organized table conversion and shadow table candidates. But it is possible to define shadow tables for those that are recommended for a conversion.
 
Tip: For users who have mixed workloads and converting tables into column-organized is not an option in an OLTP environment, consider defining shadow tables for all the recommended column-organized candidates instead of an actual row to column-organized conversion.
This will leave the original row-organized tables unaffected in OLTP performance, while allowing DB2 optimizer to reroute complex analytic workloads to column-organized shadow table copies to speed analytic reporting.
Note: Defining shadow tables requires an InfoSphere Change Data Capture configuration. For more details, see Chapter 3, “Planning and deployment of BLU Acceleration shadow tables for mixed workload environments” on page 53.
5. In the lower tab labeled SQL Statements Affected (Figure 4-18), all SQL statements in the workload that are affected if these tables are converted to column-organized tables are shown. In the grid, you can see the statement texts and the number of times the SQL statement is run in the captured workload, the weight cost of the SQL statement in the entire captured workload, estimated percentage of performance gain, and the estimated costs of statement execution before and after conversion. This order of the list is shown by descending weight cost, that is, the computing cost of the SQL statement out of the entire captured workload.
Figure 4-18 SQL Statements Affected tab
4.9 Running the conversion recommendations from the advisor
The tool not only provides table organization recommendations, but it can also generate a DDL script to execute the recommended changes.
After reviewing the detailed Workload Table Organization Advisor results, you can generate a DDL script of the recommended changes for further BLU Acceleration testing. From here, you can save the script, customize, and run it on your BLU Acceleration test environment when ready. For users who are already connected to their BLU Acceleration test database using the tool, you can modify and execute the table conversion script within IBM Data Studio. During a row- to column-organized table conversion, the original tables remain accessible online. It is important to note that data server configuration, IBM InfoSphere Change Data Capture configuration, subscription, and table mappings are required to use Shadow Tables.
Alternatively, after you have made decisions based on the advisor recommendations, you can manually create shadow tables or to convert existing row-organized tables to column-organized format on your own. For instructions on creating shadow tables, see Chapter 3, “Planning and deployment of BLU Acceleration shadow tables for mixed workload environments” on page 53. For manual conversion, you can use the db2convert command, as described in 2.6.5, “Converting tables to column-organized tables” on page 39. This command calls the same ADMIN_MOVE_TABLE stored procedure as in the script generated by Workload Table Organization Advisor.
If you prefer to continue with manual analysis, you can optionally select your own list of tables for further analysis by the tool, as described in 4.10, “Optional: Selecting your own candidate tables for conversion analysis” on page 165.
 
Important note for table organization conversion: Row-organized to column-organized table conversion is a one-direction conversion. That is, after a row-organized table is converted to column-organized, no DB2 utilities or tools support the backward conversion to the original row-organized format. To undo the conversion, unload the data from the converted column-organized tables, then reload into new row-organized tables.
A good practice is to perform a full backup of the database or the affected table spaces before a row- to column-organized conversion. An alternative to a conversion is create-and-load. That is, create a new set of selected tables in a new column-organized table format, and load data into the new column-organized tables, as described in 2.6.5, “Converting tables to column-organized tables” on page 39.
Important note for shadow tables creation: InfoSphere Change Data Capture configuration is required to properly map and replicate shadow tables from original row-organized tables. For more details, see Chapter 3, “Planning and deployment of BLU Acceleration shadow tables for mixed workload environments” on page 53.
When you are ready to convert the recommended tables to column-organized tables, complete the following steps:
1. Use Show DDL Script to generate a script that can be used to convert the list of recommended tables from row-organized to column-organized, as shown in Figure 4-19.
Figure 4-19 Generate DDL script for recommended column-organized table conversion
2. The Show DDL Script panel opens as illustrated in Figure 4-20. In this panel, review the column-organized table conversion command options. Here, you can choose the table conversion type, target table spaces for the column-organized tables, and constraint option. Note the Constraint Option section. All enforced referential integrity and check constraints should be converted to NOT ENFORCED for any new column-organized tables.
Figure 4-20 Show DDL Script panel - Column-organized table conversion options
Click Preview command to preview the generated DDL script.
3. The command section opens at the bottom, as illustrated in Figure 4-21.
Figure 4-21 Preview command in Show DDL Script panel
DDL statements for the recommended conversion are displayed.
For suggested row-organized to column-organized table conversions, the generated statements call the ADMIN_MOVE_TABLE stored procedure to convert row-organized tables to column-organized tables. It is the same procedure that is used behind the scenes in the db2convert command.
For recommended shadow tables, the script generates the db2 create table commands to create the replicated shadow tables, set integrity statements to bring them out of check pending state, If there are any primary key or unique constraints, they will be added. The script also generates insert statements and runstats commands to update the shadow tables. IBM InfoSphere Change Data Capture configuration is required to set up proper mapping and replication of the shadow tables. For detailed procedures about how BLU Acceleration shadow tables are set up, see Chapter 3, “Planning and deployment of BLU Acceleration shadow tables for mixed workload environments” on page 53.
Click Save... to save the DDL and customize for your BLU Acceleration tests.
Optionally, users who are already connected to test databases can click Run to execute the column-organized table conversions in place. It is important to note that row-organized to column-organized table conversions (ADMIN_MOVE_TABLE) are permanent. You cannot convert column-organized tables back to row-organized table.
4.10 Optional: Selecting your own candidate tables for conversion analysis
Workload Table Organization Advisor also comes with a test candidate feature. This feature allows users to virtually convert table candidates to column-organized tables, or to virtually define shadow tables. It then generates an estimated performance improvement for review.
To mark your own tables for further analysis, follow these steps:
1. Click Test Candidate Table Organization to start manually selecting candidates for the analysis, as shown in Figure 4-22.
Figure 4-22 Test Candidate Table Organization button
2. The Workload Test Candidate Table Organization page opens (Figure 4-23). All tables referenced by the captured workload are listed. To manually change the table candidate type, click a table, then select Change Candidate Table Type.
Figure 4-23 Change candidate table type
 
Tip: When selecting your own tables for column-organized table conversion, consider that tables with the following properties generally benefit from column-organized tables:
•Large number of rows: You have a large table with a high number of rows.
•References to table: Many SQL statements from the analyzed workload access this table.
•High cumulative total cost: Significant computing cost is spent on this table.
•Low IDUM value: The table has few or none of INSERT, UPDATE, DELETE, and MERGE operations.
•Low column access: A small number of columns are accessed by queries in analyzed workload.
Also, keep in mind that generally queries perform faster joining tables of the same type.
3. A pop-up window allows users to select the table type to virtually test for the selected table. In Figure 4-24, we choose to define all listed tables as shadow tables as an example.
Figure 4-24 Specify table type to test virtually
4. After you select the tables you want, click Test Candidate Table Organization to start the analysis (Figure 4-25).
Figure 4-25 Test Candidate Table Organization
5. When the test candidate table organization analysis is completed, the Review Workload Advisor Recommendations page opens. Under the Summary tab, a list of previously run analysis results are shown. In the following example (Figure 4-26), new results are generated for Candidate Table Organization. Double-click Candidate Table Organization in the list, or click Candidate Table Organization on top to review the detailed analysis results.
Figure 4-26 Review Candidate Table Organization analysis results
6. In the Candidate Table Organization tab, an estimated performance improvement is shown with similar details as described in 4.8, “Step 4: Reviewing the table organization summary” on page 157. In this scenario, (Figure 4-27), the manual analysis achieves 71% performance improvement for the given workload.
Figure 4-27 Candidate Table Organization analysis results
7. You can optionally continue to test your own selection of candidate table organization analysis until a decision is made by repeating steps from 4.10, “Optional: Selecting your own candidate tables for conversion analysis” on page 165 until you are ready to proceed to carry out a BLU Acceleration test.
 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.170.223