Today, enterprises around the world rely on their mission-critical applications to drive revenues. It is imperative that these businesses deliver and maintain reliable, thoroughly tested applications to minimize downtime caused by application failures, which could damage customer relationships and the business' integrity.
This is especially true when changes are made to enterprise applications or infrastructure due to data or user growth, database upgrades or migration, or hardware reconfiguration in order to meet and exceed competitive offerings. Satisfying current customer expectations, while simultaneously exploring new strategies can prove to be costly and difficult if not executed correctly, consequently leading to lost business, missed opportunities, and declining revenues.
InfoSphere Optim Query Capture and Replay aims to minimize the negative effects that can arise when businesses make changes to applications or infrastructures by effectively reducing the amount of time it takes to thoroughly test new environments, as well as maximizing the quality of the new application before it is made available.
Capture Replay reduces costs and mitigates risks with realistic testing, enabling database professionals to capture SQL workloads on a production database and replay those real-life workloads on a test database in order to simulate production activities. Specifically, it is capable of capturing SQL commands, original application timing, order of execution, and performance metrics, then replaying captured traffic at various speeds for capacity and scalability testing. This eliminates the need for testing scripts that are time-consuming to create. In addition, reports are generated for accurately analyzing the kind of effect caused by the changes made to the environment, providing further insight into potentially problematic areas.
Optim Query Capture and Replay architecture
Figure 1 illustrates the basic architecture implemented by the Capture Replay solution.
Figure 1. Capture Replay Architecture Capture Replay Server and S-TAP Agent
The Capture Replay solution resides on the database server to capture incoming and outgoing traffic, so it does not require changes to applications, databases, or infrastructure to be implemented.
The first main component of the solution is the Query Capture Replay Server, which will have IBM InfoSphere Guardium software installed. You can choose to install the Guardium image yourself on your own server (that meets Guardium's hardware and software requirements) or purchase an appliance that already has Guardium installed and configured. The Capture Replay Server needs to be patched with a Guardium service patch for the latest Guardium fixes, and a Query Capture Replay patch to update it with capture/replay functionality.
The second important component is the lightweight S-TAP agent software, which will be installed on both database servers to capture SQL traffic from, as well as the database servers you would like to replay the captured traffic on.
The S-TAP agent's main responsibility is to simply capture workloads on a database server for offloading to the Capture Replay Server from both source and target databases, which will be used later for analysis (for example, comparison reports). It has a minimal impact since it does not rely on native database logging or tracing, but can still capture 100 percent of SQL workloads.
The Capture Replay Server receives raw database activity data from your capture database server and is responsible for parsing, evaluating, and logging the data, which will be used for replaying on your replay database, as well as generating reports for analytical purposes. Users interact with the Capture Replay Server through its two web console GUIs, where you can specify capture and replay options, as well as view high-level and in-depth workload comparison reports:
- Guardium Web Console— Used for creating and configuring capture replays between heterogeneous databases (for example, Oracle to DB2®, which is discussed here)
- Capture and Replay Web Console— Used for creating and configuring capture replays between DB2 databases (for more information, visit the Information Center link in Resources)
Capture and replay scenarios
In order to meet SLAs, satisfy customer expectations, and exceed the offerings of competitors, enterprises must be able to adapt to the transactional requirements of users and applications. Capture Replay allows organizations to safely handle enterprise changes.
Figure 2. Common scenarios — database upgrades/migrations and hardware reconfigurations
As the amount of data or application users increase, businesses may find themselves having to upgrade or reconfigure hardware and databases to maintain acceptable performance levels for their applications. With Capture Replay, database administrators can capture workloads on a current production database server and repeatedly replay it during the database upgrade or hardware reconfiguration process. You can then examine comparison reports that include metrics to help evaluate and compare database failures and response times to ensure that problematic issues are addressed and service-level agreements are maintained.
This article focuses on database migration use cases. Moving data from one database platform to another can lead to several problems, such as failing SQL statements due to non-conformity to SQL standards. Migrations are usually more complex than database upgrades since you are moving data between two database vendors that may differ in many technical aspects. Database administrators need to ensure that their application functionality and performance does not fall below acceptable levels.
Using Capture Replay for migration projects, DBAs can:
- Identify "stale" and "hot" SQL statements and database objects for prioritizing porting efforts
- Effectively estimate initial hardware and storage requirements for migrated data
- Quickly diagnose SQL failures through detailed exception reports
- Determine sets of data absent in migrated environment
- Observe real response times to determine performance issues
To demonstrate the business value of using Capture Replay to test enterprise changes, you will go through a simple database migration scenario.
When performing database migrations, Capture Replay can be used to help validate migration operations and estimate further migration effort necessary to finalize the migration processes. Once your target database environment has been created, you can quickly pinpoint migration issues by replaying the production workload on the target database platform. Although target database objects need to be created first, Capture Replay minimizes migration efforts by allowing you to test the database layer without having to repurpose the entire application layer to communicate with your new testing database.
Capture Replay comparison capabilities allow you to identify failed SQL statements, returned data inconsistencies, and performance issues due to your new testing environment. Moreover, replay/replay capabilities can also be used to further your migration process until all issues have been resolved and the target database system reaches a desired working state with minimal errors or inconsistencies reported.
The following scenario shows the migration of an inventory management system, a web application that uses Oracle as its back end. You will use this application to generate traffic in the Oracle source database to be replayed later by Optim Query Capture and Replay on the DB2 for Linux®, UNIX®, and Windows® target database.
Capture Replay database migration workflow
To complete the migration scenario:
- Capture Oracle production workload for the relevant database traffic.
- Prepare target DB2 migration environment. DB2 database should be populated with the database objects and data required to run the workload (for example, through a migration tool).
- Replay the captured workload on the target DB2 migration environment.
- Analyze reports to identify migration issues and estimate further potential migration efforts.
- Perform database changes to resolve identified migration issues and repeat another workload replay to validate the changes.
Figure 3. Capture Replay database migration workflow
Before you can begin the scenario, you must make certain that the S-TAP agent is installed and configured so it correctly captures traffic to send to the Capture Replay Server for replay on a target database. This can all be done through the Guardium Web Console.
First, ensure that the source and target database servers have the Guardium S-TAP agent installed. Observe that the S-TAP status is installed and active.
Figure 4. S-TAP Status Monitor
- Verify that an inspection engine is created and correctly configured for
both the Oracle and DB2 databases.
Figure 5. Inspection engines configured for Oracle and DB2 databases
The Guardium S-TAP agent allows you to monitor all (local and remote) traffic related to (supported) databases existing on the server where it is installed. The S-TAPs on both our source and target databases captures all SQL traffic by creating a policy that does not filter by any kind of IP, database, SQL command, etc. You need to specify a
LOG FULL DETAILSaction, which must be used for the capture/replay feature to work. This action will not only log SQL statements but it will also log the values. Note that when you run a replay on a target database, you are implicitly performing a capture so that its data can be used for future comparison reports.
Figure 6. Log Full Details policy
- In order to capture response times between statement executions and log the
number of records affected by an SQL statement, you need to ensure that the
Compute Avg Response Time and Log Records Affected options
are chosen, respectively, in the general Inspection Engine Configuration
(Administration Console > Configuration > Inspection
Engines). Moreover, make sure the Default
Mark Auto Commit option is selected if you plan to use the
Auto Commitoption during replay.
Figure 7. Validating that inspection engine is properly configured
- At this stage, the S-TAP agent will be able to capture all Oracle database traffic, but later, you can choose which of the captured traffic to replay on the target database.
In this article, you will be taken through a simple scenario showcasing how Capture Replay can be used to analyze a target database system, where an Oracle database environment has been recreated in a DB2 environment. This scenario will primarily concern itself with resolving migration problems relating to the migrated data itself, the performance of running traffic on the target database environment, as well as any unsupported SQL statements replayed on DB2. Capture Replay's capture/replay and replay/replay capabilities provide reports for analysis, allowing you to identify specific porting issues, as well as help estimate the migration effort needed to resolve those issues.
- Recreate relevant databases on target system
- Capture Oracle traffic data
- Stage Oracle traffic data
- Replay data on DB2
- Compare and analyze capture/replay reports
- Identify several migration issues: poor performance and incompatible SQL
- Tune DB2 database and modify staged data to correct incompatible SQL
- Rerun replay on DB2
- Verify that the migration issues have been resolved
First, you have to recreate the relevant databases in the production environment in your testing environment and migrate the data. There are many migration utilities you can use to aid in this process, such as:
- MEET DB2— The Migration Enablement Evaluation Tool for DB2 (i.e., MEET DB2) parses DDL and PL/SQL code extracted from an Oracle database and reports on supported features, non-supported features, and suitable solutions for migration efforts.
- IBM Data Movement Tool— IBM Data Movement Tool is a simple and powerful tool that enables applications from Oracle (or various other DBMSes) to be run on IBM DB2 9.7 for Linux, UNIX, and Windows by connecting to an Oracle database and extracting database objects DDL to files that can be later executed, as well as generating load scripts to import the data itself into DB2.
- Optim TDM— The Optim Test Data Management Solution (or simply Optim TDM) speeds iterative testing, controls costs, and ensures that test environments are secure by creating referentially intact right-sized test databases, easily refreshing production-like test environments and automating test comparisons, and masking cloned production data.
- You have to generate traffic on your source database by using the web application. This traffic will then be automatically captured by the S-TAP agent and sent to the Capture Replay Server where it will be parsed and stored for replay on another database.
- As mentioned, you already had created your target DB2 database with relevant objects similar to those found in the Oracle database. The creation process can be done manually or can involve migration utilities to aid the process. You should use Capture Replay to perform a migration assessment, which allows you to estimate work that needs to done to have your DB2 database function without errors. By replaying Oracle traffic on the target DB2 database, you can identify problematic objects that require further attention as a result of the migration process.
- Next, you need to configure the replay. This will identify which parts of
the captured Oracle SQL stream to replay on the target DB2
database. Remember that the S-TAP agent has been capturing all of the
traffic, including traffic from other databases. You need to focus on
the relevant captured data. This includes specifying the
timeframe of captured SQL traffic that would be used for replay, as
well as the type of database you captured traffic from.
Configure a new replay by using the Replay Builder, located under the Capture/Replay > Configuration tab. The first four parameters are mandatory while the rest are optional.
Figure 8. Creating a Replay Configuration via Replay Builder
In order to use Capture Replay effectively for migration purposes, it is critical that your Period start parameter is set correctly. When using Capture Replay to replay production workloads, the source and testing database must be in similar states. In addition, the S-TAP agent must be installed on your source and test database servers prior to migration so traffic can be monitored and logged for later use.
Whether the source database was migrated using a full backup or migrated through another method, you must know the time of the "snapshot" state of the source database that was migrated to the testing database. That time is your Period start parameter setting, and any captured traffic from your source database from that time onward can be safely replayed on the testing database since the test database is in a verified state, similar to the snapshot state of your source database.
- Before any selected traffic can be replayed onto a target database, a
staging phase is needed on the Capture Replay Server. This will
associate your replay configuration definition with the actual traffic data. The
staging process can be initiated by selecting a replay configuration, clicking
on the Stage button, followed by Start.
Figure 9. Staging the data
- Staging will add the replay configuration to the process queue where it can be
monitored under the Capture/Replay > Job
Queue tab. You need to until the staging process has a
COMPLETED status. Please take note of the
process ID of the staging job.
Figure 10. Monitoring job queue for staging process
- To view the staged data ready for the replay once the
staging job is complete, you can find it under the
Capture/Replay > Data Staging tab. At first, you may notice that "No Staging Data" will be
found. To view the data, you have to customize the report.
Figure 11. Data staging report
Click on the Customize button in the top-right corner of the page to customize the report.
Figure 12. Customize Data Staging report
- You need to specify that you only want to view the staged data associated
with the specific replay configuration ("migration_replay") by
configIdparameter and changing it to the Process ID you previously took note of in the Job Queue. You can update the Data Staging report by clicking on the Update button at the bottom.
Figure 13. Changing configId parameter of Data Stating report
- The report will be updated with the staged data associated with the replay
configuration. You will be able to view details of each SQL statement executed and
captured, such as timestamp, the type of database and server IP
where it was executed, etc., as well as the total number of SQL statements.
Figure 14. Data Staging report with captured traffic
- Now that you have verified that the replay configuration has associated traffic, you
can replay this on your test DB2 database. You can accomplish this by
returning to the Replay Builder, selecting Replay Configuration,
and clicking Replay.
Figure 15. Replay via Replay Builder
- You need to configure the Replay Schedule Setup and click
Apply. Here, you can specify parameters relating to the data
source on which you would like to replay, as well as other options such as commit
methods and number of times you would like to repeat the replay. Once you are
finished, you can click Run Once Now to start replaying
the staged Oracle traffic onto the target DB2 database.
Figure 16. Replay Schedule Setup
Tip Here are some details to consider when configuring your replay schedule: Speed rate This parameter determines the speed at which you would like to replay the captured traffic on your target database (for example, 0 = no delay (SQL will be executed as fast as possible), 1 = same speed, 10 = ten times faster than original speed, etc.). Commit methods This determines how to commit changes to the database during replay. The Don't force option looks at your Inspection Configuration settings (i.e., the Default Mark Auto Commit setting) to determine whether to use auto-commit during replay, the Force auto commit option will commit after every SQL statement, and the Force no auto commit option will not commit unless it explicitly sees one in the captured traffic. Note that you may find yourself with errors caused by referential integrity constraint failures using option "0" (no delay) for Speed rate and/or Don't force for Commit Methods, especially if your traffic contains insert and update statements using different sessions as transactions may not be replayed in the original order. It is recommended that you avoid option "0" for Speed rate and to use Force auto commit or Force no auto commit for a commit method.
Go to the Capture/Replay > Job Queue tab again to view the status of the replay. Once it is complete, you can click on the Capture/Replay > Capture/Replay List tab, where you will be able to generate different reports about captured and replayed traffic.
On this capture/replay list, one record is listed. As indicated by the Name-From and Name-To columns, you can verify that this record pertains to your previous capture and replay jobs (i.e., "migration_replay" as your captured traffic and "replay_on_DB2" as your replayed traffic). To generate different types of comparison reports between captured and replayed traffic, double-click on the record and click Invoke.
Figure 17. Creating capture/replay reports to compare traffic
- You will see a list of GuardAPI functions that, when executed, generate detailed
reports comparing statistics about the captured and replayed traffic. Each
function will generate a different kind of report. Let us first look at the
queue_replay_object_agg_match_by_idfunction by clicking on it.
Figure 18. The
Calling this function will generate the Workload Aggregate Match report. This aggregates similar SQL statements (based on SQL verb, object, and depth) from captured and replayed traffic, compares them side by side, and provides statistics for each. You should start with this high-level report to begin analyzing your capture/replay scenario. Call the function by leaving the default parameters and clicking Invoke now.
Figure 19. Invoke
It will take time for the comparison job to run. In the meantime, you can initiate another report to review later.
- Close all the pop-pup windows and return to the main Capture Replay
(Guardium) interface. You can initiate the next comparison job by double-clicking
Capture/Replay again, clicking Invoke, then
Figure 20. The
queue_replay _match_by_idGuardAPI function
Calling this function will generate the Workload Match report, which is much more granular and detailed than the previous Workload Aggregate Match report. Instead of creating a high-level report summarizing the comparison between similar SQL statements, this report compares every individual SQL statement. You can leave the default parameters, except change the
includeGroupparameter to Replay - Include in Compare and the
excludeGroupparameter to Replay - Exclude from Compare. Then click Invoke now.
Figure 21. Invoke
- Now that you have triggered both the creation of the Workload Aggregate
Match and Workload Match reports, let's take a
look at their status. You need to close all the pop-up windows, return to the
main interface, and go to the Job Queue. You
will see that the last two jobs that ran were your comparison jobs initiated by
calling the GuardAPI functions
queue_replay_match_by_id. The Guardium Job Description column will tell you how many SQL statements were matched and unmatched during comparison between your captured and replayed traffic.
Figure 22. Monitoring Job Queue for GuardAPI function jobs
- Since both comparison jobs are completed, you can view the
reports generated. To view them, go back to the
Capture/Replay > Capture/Replay List tab, double-click on the
Capture/Replay record, and click View
Figure 23. Capture/Replay List - View Workload Comparison
- A window will pop up, stating that data compilation has completed. Click
Take me there to go straight to the Workload
Comparison for this specific capture/replay. Here, you will
find the other reports generated by the GuardAPI functions.
Figure 24. Data compilation process for Workload Comparison
You will be taken to the Summary Comparison report. Here, you can analyze high-level details and statistics about captured and replayed traffic. You can take a look at some of the comparisons as examples. Let's look at the comparison of average execution time.
Here, you can see that the target database performed approximately 250ms slower on average during replay. If the performance of your target database does not meet your SLAs or is unsatisfactory, database tuning and reconfiguration will need to be performed. By using this high-level bar graph, you can expect that further efforts are needed to improve performance on the migrated database. You have to perform a simple database-tuning task to improve performance in a later step.
Figure 25. Compare Avg Execution Time (ms)
Now, let's take a look at the comparison of total rows retrieved/affected by the captured and replayed traffic. Here, you can see that approximately 3,000-4,000 fewer rows have been retrieved during the replay on the target database. This could possibly mean that not all data was migrated from your production database to your testing database, or several
SELECTSQL statements failed when running on the testing environment.
Figure 26. Compare rows retrieved
- If you previously triggered a report generated from a GuardAPI function, you
will be able to see the report on the left menu panel. You can now look at
another high-level summary report by clicking the Workload Aggregate
Figure 27. Finding the Workload Aggregate Match report
- Here, you will find a comparison among groups of similar SQL statements. For
example, the record below represents the group of all SQL
statements that have the SQL verb as
SELECT, the object as
invent.locations,as well as the depth of "0" (depth pertains to embedded SQL - since the SELECT statement is not embedded within another SQL statement, its depth is 0).
Figure 28. A record from the Workload Aggregate Match report
Observe that there are many performance and consistency metrics in this report. The record below says that there were 18 SQL statements, selecting from invent.services table, that executed on both your Oracle and DB2 databases. However, all 18 of them failed when running on DB2 (indicated by the Compare-To Failures column). This could imply that there may be an issue with the invent.services table in your test database or that the syntax/format of the SQL statement itself may not be compatible with DB2. The same can be said about another SQL statement that updated records in the invent.services table.
Figure 29. SQL statements failing to run on DB2
If you look at the average runtime of a few SQL comparison records, you can clearly see that DB2 is a few milliseconds slower during replay (indicated by the Compare-To Avg Runtime column), which confirms what you saw in the Summary Comparison report. Once again, this may be due to improper database tuning.
Figure 30. SQL statements with slower runtimes
As shown by the lower counts of records affected, you might be missing data in the invent.locations and invent.owners tables within your DB2 testing database (indicated by the Compare-To Avg Records Affected column). This confirms the suspicion of the incomplete migration, discussed earlier while analyzing the Summary Comparison report.
Figure 31. SQL statements with lower records retrieved
- You can take a look at the granular version of the Workload Aggregate
Match report. On the left side, click on the Workload
Match report to bring you there.
Figure 32. Finding the Workload Match report
- Here, you will find a comparison of performance and accuracy statistics between
each of the individual SQL statements themselves (as opposed to comparing them
in groups). You will notice that it looks very similar to the Workload
Aggregate Match report, with a few differences in what the columns
represent. You can sort the report by SQL (as opposed to timestamp) to observe
metrics pertaining to a specific SQL statement (i.e., selecting from
the invent.services table). Let's click on the column name
Compare-From FULL SQL and look for the records.
Figure 33. SQL statements selecting from services table
As mentioned, you have found the 18 SQL statements, selected from the invent.services table, that failed to run on DB2. According to the report, you can verify this information by looking at the Compare-To Success and Compare-To Records Affected column. You will know how to fix this issue later.
Figure 34. SQL statements failing to run on DB2
- Examining the rest of the report, you will find that there is another SQL
statement (updating the invent.services table by changing existing
values of a specific item) that seems to be failing when being replayed on DB2. Once
again, you can verify this by looking at the
Compare-To Success and Compare-To Records Affected column.
You will see how this issue is fixed later.
Figure 35. Failing SQL statement
Figure 36. Another set of SQL statements failing to run on DB2
TIP Similar to the Workload Aggregate Match report, the Workload Match report displays how many times each individual SQL statement was executed, from captured and replayed traffic. This is a powerful feature that helps identify which SQL is most and least frequently run, allowing you to locate "stale" code and "hot" points for consideration during conversion efforts.
- At this point in the scenario, there are three issues that need to be resolved:
- Poor performance of DB2 replaying Oracle traffic
- Failing statement SQL A when replayed on DB2:
SELECT count(*) FROM invent.services ORDER BY id DESC
- Failing SQL statement SQL B when replayed on DB2:
UPDATE invent.services SET severity=<severity>, description=<description>, serviceOwner=<service_owner>, targetCloseDate=(TO_DATE(targetCloseDate) + <num_of_days>) WHERE id=<id>
- To improve DB2 performance when replaying captured Oracle traffic, one task you
can perform is to execute the
DB2 RUNSTATScommand on all tables. This updates the statistics about characteristics of a table and any associated indices to allow the optimizer to determine better access paths to your data. Open the DB2 command-line interface and execute
RUNSTATSon your tables. This can also be done through a GUI via IBM Data Studio or the DB2 Control Center.
Figure 37. RUNSTATS DB2 command
- To resolve this issue pertaining to the two SQL statements, let's go
to the Workload Exceptions report. You can find it as well as
the other reports on the left side of the Workload Comparisons tab.
Figure 38. Finding the Workload Exceptions report
Here, you will see that no errors were found when capturing running traffic on our source Oracle database, but you will find errors when replaying the same traffic on your testing DB2 database.
Figure 39. Exceptions occurred during capture of Oracle
Figure 40. Exceptions occurred during replay on DB2
Click on the column name SQL string that caused the Exception to sort by SQL in the report. After finding statement SQL A, you can see that the error has to do with the SQL statement's incompatibility with DB2, rather than with the data or database objects.
Figure 41. SQL A exception explanation
To resolve this issue, you simply need to replay these SQL statements without an
ORDER BYclause. Furthermore, after finding statement SQL B in the exceptions report, you can also see that there is an SQL compatibility issue with DB2.
Figure 42. SQL B exception explanation
The fix for this error is to provide a format string argument for the
TO_DATE()function when run on DB2 (with Oracle, this function does not need a format string explicitly defined).
- Now that you have identified the cause of the errors, let's move to the
Data Staging report, which you can find on the left side of
the Workload Comparisons tab.
Figure 43. Finding the Data Staging report
- In this report, you can view each individual SQL statement captured on
your source Oracle database and replayed on your target DB2 database. Note that
this is the same Data Staging report you saw earlier; it has
just been accessed through a different part of the interface. Here, you need to
modify the captured incompatible SQL so the specific errors identified
above will not arise when you replay them again. Click on the column
Full SQL to sort by SQL and search for SQL A.
Figure 44. Finding SQL A statements in Data Staging report
- Click Invoke at the bottom of the report and choose
Figure 45. Invoking the modify_staging_data GuardAPI function
Here, you can modify multiple SQL statements at the same time. You need to select only the checkboxes on the left side for each record pertaining to SQL A.
Figure 46. Selecting checkboxes for SQL A statements
Then you have to fill out the toSQL column with the new SQL statement:
SELECT count(*) FROM invent.services.
Click Invoke now and close any pop-up windows that appeared to return to the main interface.
Figure 47. Modifying SQL statements
After refreshing the Data Staging report, you can see that the incompatible SQL A statements have been correctly modified.
Figure 48. SQL A statements in Data Staging report after modification
- You need to modify the staged data one more time to resolve the incompatibility
issue for SQL B. The new SQL statement to replace the originals will be:
UPDATE invent.services SET severity=<severity>,description=<description>, serviceOwner=<service_owner>, targetCloseDate=(TO_DATE(targetCloseDate + <num_of_days>,'YYYY-MM-DD HH24:MI:SS')) WHERE id=<id>.
Figure 49. SQL B statements in Data Staging report after modification
- Now that you have modified the staged data and tuned the DB2 test database,
replay the captured traffic one more time. Return to the Replay
Builder, highlight Replay Configuration > migration_replay, then click Replay.
Figure 50. Rerunning Replay via Replay Builder
Select Replay Schedule Setup > replay_on_DB2, click Run Once Now, then click OK on the confirmation message box.
Figure 51. Rerunning Replay Schedule Setup
Return to the Job Queue and wait until the replay is complete. Once it is finished, click the Capture/Replay > Replay/Replay List tab. Here, you will be able to generate details about the comparison between one replay and another. In this case, it will be a comparison between the first and second replays (after having modified a few SQL statements and tuning the DB2 database) on your test database. Double-click on the Replay/Replay record and click View Workload Comparison.
Notice that this is the record you want since the Name-From column represents your first replay schedule setup while Name-To represents your second replay schedule setup.
Figure 52. View workload comparison in Replay/Replay list tab
- After data compilation is complete, click Take me there.
Figure 53. Data compilation process for Workload Comparison
You will immediately be taken to the Summary Comparison report. You will see that during the second replay, the performance of our DB2 database greatly improved after basic database tuning.
Figure 54. Compare Avg Execution Time (improved DB2 performance)
The number of failures during your second replay has greatly reduced due to the modification of incompatible SQL statements from captured Oracle traffic.
Figure 55. Compare SQL Failures (reduced amount of failures)
- To verify that the previous incompatible SQL statements are not causing any more
errors, go to the Workload Exceptions report.
Figure 56. Finding the Workload Exceptions report
Here, you will find that there exists only one error for the second replay on your testing DB2 database. This error pertains to a procedure that was captured in Oracle traffic, which is initially called when you start the Oracle SQL*Plus interface for debugging purposes. Since DB2 does not have this procedure, this SQL can be removed from the next replay to reduce the number of errors as well.
Otherwise, all other previous errors have been resolved. You now know that these SQL statements need to be modified on the application layer to prevent the same kind of errors from occurring in production.
Figure 57. Workload exceptions report after Staged Data modification
Now that you have identified performance and SQL compatibility issues and resolved them, this finishes the scenario. Other migration issues may be discovered and fixed in similar fashion by leveraging Capture Replay Capture/Replay and Replay/Replay comparison capabilities and reports such as Summary Comparison, Workload Aggregate Match, Workload Match, and Workload Exceptions.
Capture Replay allows you to handle enterprise changes properly. By taking advantage of its simple capture and replay functionalities, companies can bypass the painstaking process of creating scripts for simulating production workloads. By leveraging its powerful comparison capabilities, businesses can rapidly identify and resolve issues that result from these changes. In addition, straightforward workload capture capabilities allow experts to have a much more solid base to estimate the initial hardware and storage requirements for migration projects. Capture Replay also works hand in hand with your existing testing tools to provide a more comprehensive analysis of your production and testing data.
Having the ability to easily capture production workloads for customized replay on a testing environment without having to disrupt the application layer itself can be particularly valuable, especially when testing database upgrades, migrations, and hardware configurations. Being able to mirror production environments quickly and effectively allows for rapidity during testing phases. Accelerating testing cycles can lead to faster product delivery, which in turn will translate to increased revenue your business.
- Participate in the discussion forum.
- Explore InfoSphere Optim Query Capture and Replay.
- Delve into InfoSphere Capture Replay by checking out the InfoSphere Capture Replay Information Center.
- Learn more about Information Management Best Practices, authored by leading experts designed to give you practical advice for improving the value of your IBM data integration products.
- Compliment Capture Replay by streamlining your testing environment using the Optim Test Data Management Solution.
- Be sure to check out our other Optim Solutions.
- Check out the official "Oracle to DB2 Conversion Guide: Compatibility Made Easy" Redbook.
- Learn more about the IBM Data Movement Tool through this developerWorks article.
- Experiment with the MEET DB2.