About this tutorial
Organizations continuously go through hardware/platform/software version upgrades. These requirements come from many different sources and affect stakeholders such as Line of business executives, application owners, IT managers, and testing teams. Often these kinds of upgrades are not managed well in a sense that many problems due to system in-correctness and/or performance issues are found post roll-out, and are expensive and disruptive to fix.
InfoSphere Optim Query Capture and Replay 1.1 for Linux, UNIX, and Windows lets you capture a traces of a workload that is running on a production database, or on any other target database. This is achieved in a very unobtrusive fashion with ultra-low overhead. You can then replay the workload to mimic the characteristic of the original workload on a different database that has the same or similar schemas and data volume. Before replaying the workload, you have the option to change the values for schema names and user names as these might differ between the capture database and the replay database for security reasons.
Once the workload has been replayed, you can run reports to validate and compare the results between the captured workload and the replayed workload. Reports provide the following kinds of information.
- The first part of a report shows you a measure of the accuracy between two executions, for example how many SQL statements are identical in terms of return code and number of rows affected. It shows missing SQL statements, and statements that are new in the replayed workload. Similar information is shown for transactions where all SQL signatures and sequences are considered for comparison.
- In the second part of a report, the relative performance of the captured and replayed SQL statements and transactions are compared in terms of total elapsed time. Only the successful (matching) SQL statements and transactions are considered for comparison. A user can view the improvement and regression of SQL statements and transactions by a predefined percentage.
You can remove failed SQL statements from the report and then replay the modified workload again and compare the results. The report feature lets you remove some or all SQL statements by error code, and create a new replay-ready workload to compare the workloads, as shown in Figure 1.
Figure 1. InfoSphere Optim Query Capture and Replay workflow
Part 1 of this tutorial provides an overview of the InfoSphere Optim Query Capture and Replay 1.1 for Linux, UNIX, and Windows, along with a sample step-by-step approach on how to configure, capture, transform, replay, and generate a report for the same workload in two different environments.
There are many use cases for InfoSphere Optim Query Capture and Replay 1.1 for Linux, UNIX, and Windows. A few of the major ones are listed as follows.
- Migration: Upgrade the database operating system or apply a fix pack. Understand potential performance impacts, identify bottlenecks, or rebind impacts.
- More data: Test the impact of increased data volume to maintain stable performance as the data increases.
- More users: Speed up replay to simulate increase in concurrent users. Continue to meet Service Level Agreements as the number of users increases.
- Tuning: Physical database design changes, for example new indexes. Identify how a new index will impact existing access plans and overall performance.
InfoSphere Optim Query Capture and Replay 1.1 for Linux, UNIX, and Windows consists of an InfoSphere Guardium appliance that hosts the InfoSphere Capture Replay server. In addition, Guardium provides the S-TAP component which sits on the monitored database and collects inbound and outbound SQL-related data to and from the database server.
The diagram in Figure 2 shows a high-level overview of the InfoSphere Optim Query Capture and Replay 1.1 components.
Figure 2. InfoSphere Optim Query Capture and Replay architecture
Note: In Figure 2, the steps for Data Privacy, Test Data Subset, and Catalog and Configuration replication from production to test environment are manual processes. They are not part of the current product features, and are included here as part of the overall solution.
InfoSphere Optim Query Capture and Replay for Linux, UNIX, and Windows can be installed and configured by performing the following tasks, which are not covered in this tutorial.
- Installation of IBM InfoSphere Guardium Version 8.2: The first step is the installation of the InfoSphere Guardium appliance which provides the base for capturing and replaying workloads. The detailed steps can be found in the Installing IBM InfoSphere Guardium Version 8.2 topic.
- Installation of InfoSphere Optim Query Capture and Replay license: To enable capture and replay, the InfoSphere Optim Query Capture and Replay license needs to be activated. The detailed steps can be found at: Installing the IBM InfoSphere Optim Query Capture and Replay license topic.
- Installation of InfoSphere Capture and Replay: The next step is installation of the InfoSphere Capture Replay patch and other required software patches on the InfoSphere Guardium appliance by using the InfoSphere Guardium command-line interface (CLI). The detailed steps can be found at: Installing InfoSphere Capture Replay topic.
- Installation of a capture and replay policy: To capture and replay DB2-to-DB2 workloads by using the InfoSphere Capture Replay web console, the Capture and Replay - DB2-to-DB2 policy must be installed and activated. The detailed steps can be found at: Installing a capture and replay policy for your environment topic.
- Installation of IBM InfoSphere Guardium S-Tap: In order to use a database server for capturing or replaying workloads, InfoSphere Guardium S-TAP must be installed on it. InfoSphere Guardium S-TAP is a lightweight software agent that monitors database traffic and sends data access information to an InfoSphere Guardium appliance. The detailed steps can be found at: Installing and configuring IBM InfoSphere Guardium S-TAP on your database servers topic.
- Configuring your environment to capture and replay DB2 for
Linux, UNIX, and Windows SQL workloads: After
installing InfoSphere Optim Query Capture and Replay (Listed
previously), the following steps must be completed to configure
the environment for capture and replay:
- Add inspection engines for databases. Inspection engines make the SQL statements and transactions that are captured by InfoSphere Guardium S-TAP available for the capture-replay engine. The InfoSphere Guardium web console can be used to add an inspection engine for each database instance that will be used to capture or replay workloads.
- Add database connections. To use the InfoSphere Capture Replay web console to capture and replay workloads, you must add database connections to the DB2 databases that have been configured for capture and replay.
- Configure InfoSphere Capture Replay for web console authentication. To support multiple users, InfoSphere Capture Replay must be configured for web console authentication by adding a repository database and configuring authentication for the users and groups of that database. The detailed steps can be found at: Configuring IBM InfoSphere Optim Query Capture and Replay topic.
Once these tasks have been completed, you can use the InfoSphere Optim Query Capture and Replay for Linux, UNIX, and Windows web console to capture workloads from a database, replay them on other databases, and compare the results of the execution.
You can open InfoSphere Optim Query Capture and Replay web console using one of the following browsers.
- Firefox (v 3.5 and above)
- Internet Explorer (v 8.0 and above)
More information about resource requirements and supported platform is given in the Resources section at the end of the article.