Assessing database migration with InfoSphere Optim Query Capture and Replay

Capture production workloads, replay on testing environments, and analyze traffic comparison reports

Migration projects enterprises undertake can be complex, costly, and disruptive. If migration teams are not equipped with effective testing tools, they may spend months reviewing code to prioritize conversion efforts and preparing scripts to simulate production workloads, draining businesses of valuable resources. With InfoSphere® Optim™ Query Capture and Replay, organizations can handle enterprise changes safely. Using its ability to easily capture production workloads, replay them on target databases, and generate comparison reports, data teams can systematically manage problematic issues that arise as a result of database upgrades.

Martin Dizon (mdizon@ca.ibm.com), Optim/Guardium Specialist, IBM

Martin Dizon is a technical enablement specialist in the Information Management Technology Ecosystem team at the IBM Toronto Software Lab, providing enablement for the IBM Optim and Guardium products. He conducts demonstrations, bootcamps, and skill transfers for worldwide business partners to help accelerate and ensure their success in their own Optim/Guardium projects.



Denis Kirichenko (Denis.Kirichenko@ru.ibm.com), Optim/Guardium Specialist, IBM

Denis Kirichenko is a software engineer in the Optim and Guardium Technology Ecosystem team at the IBM Science and Technology Center Russia. He provides end-to-end InfoSphere Optim and Guardium enablement from skills transfers and demos/PoTs/PoCs to product best practices, helping accelerate partner success and progress on the InfoSphere Optim and InfoSphere Guardium products.



02 August 2012

Also available in Chinese

Introduction

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.

Note

In this article, InfoSphere Query Capture and Replay is referred to as Capture Replay.

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
Image shows Capture Replay Architecture: Capture Replay Server and the 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.

Guardium

For those who are familiar with InfoSphere Guardium, the Capture Replay Server is based on the Guardium platform.

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
Image shows 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.


Database migrations

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:

  1. Capture Oracle production workload for the relevant database traffic.
  2. 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).
  3. Replay the captured workload on the target DB2 migration environment.
  4. Analyze reports to identify migration issues and estimate further potential migration efforts.
  5. Perform database changes to resolve identified migration issues and repeat another workload replay to validate the changes.
Figure 3. Capture Replay database migration workflow
Capture Replay database migration workflow

Validate prerequisites

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.

  1. Note

    S-TAP agents are OS-specific, not database-specific. You are only required to install one S-TAP per database server (regardless of how many kinds of databases exist on it). In our scenario, our source and target databases are installed on the same server, so only one S-TAP needs to be installed.

    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
    S-TAP Status Monitor
  2. 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
    Inspection engines configured for Oracle and DB2 databases
  3. 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 DETAILS action, 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
    Log Full Details Policy
  4. 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 Commit option during replay.
    Figure 7. Validating that inspection engine is properly configured
    Image shows validating that inspection engine is properly configured
  5. 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.

Migration assessment

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.

Scenario objectives

  1. Recreate relevant databases on target system
  2. Capture Oracle traffic data
  3. Stage Oracle traffic data
  4. Replay data on DB2
  5. Compare and analyze capture/replay reports
  6. Identify several migration issues: poor performance and incompatible SQL
  7. Tune DB2 database and modify staged data to correct incompatible SQL
  8. Rerun replay on DB2
  9. Verify that the migration issues have been resolved

Scenario walk-through

  1. Note

    You will need to know the time of the snapshot state of the source database that was migrated to the testing database because it will be important when configuring a replay in a future step.

    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.
  2. 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.
  3. 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.
  4. 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
    Image shows creating a Replay Configuration via Replay Builder
    Tip

    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.

  5. 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
    Image shows staging the data
  6. 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
    Image shows monitoring job queue for staging process
  7. 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
    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
    Image shows customizing Data Staging Report
  8. You need to specify that you only want to view the staged data associated with the specific replay configuration ("migration_replay") by modifying the configId parameter 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
    Image shows changing configId parameter of Data Stating report
  9. 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
    Data Staging report with captured traffic
  10. 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
    Replay via Replay Builder
  11. 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
    Replay Schedule Setup
    Tip
    Here are some details to consider when configuring your replay schedule:
    Speed rateThis 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 methodsThis 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.
  12. 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
    Image shows creating capture/replay reports to compare traffic
  13. 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_id function by clicking on it.
    Figure 18. The queue_replay_object_agg_match_by_id GuardAPI function
    Image shows the queue_replay_object_agg_match_by_id GuardAPI function
  14. Note

    The queue_replay_object_agg_match_by_id function is used because you can compare workloads between two databases. Expect some white noise since this method makes SQL comparisons based on statements and object names. If you are comparing workloads of the same database type, use the queue_replay_agg_match_by_id function, which is more accurate.

    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 queue_replay_object_agg_match_by_id function
    Image shows invoking queue_replay_object_agg_match_by_id function

    It will take time for the comparison job to run. In the meantime, you can initiate another report to review later.

  15. 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 choosing the queue_replay_match_by_id function.
    Figure 20. The queue_replay _match_by_id GuardAPI function
    Image shows queue_replay_match_by_id GuardAPI function
  16. Note

    You can assign specific SQL statements to the groups Replay - Include in Compare and Replay - Exclude from Compare in order to include them and exclude them, respectively, during comparison for a much more focused analysis if need be.

    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 includeGroup parameter to Replay - Include in Compare and the excludeGroup parameter to Replay - Exclude from Compare. Then click Invoke now.

    Figure 21. Invoke queue_replay_match_by_id function
    Image shows invoking queue_replay_match_by_id function
  17. 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_object_agg_match_by_id and 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
    Image shows monitoring Job Queue for GuardAPI Function Jobs
  18. 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 Workload Comparison.
    Figure 23. Capture/Replay List - View Workload Comparison
    Image shows Capture/Replay List - View Workload Comparison
  19. 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
    Image shows data compilation process for Workload Comparison
  20. 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.

    Note

    The Count From and Count To bars represent the average execution time of captured and replayed traffic, respectively (you will see this naming convention often in other comparison reports as well). The Period (x-axis) represents the period of time the capture/replay took place in hours. In this example, the capture/replay took place in the "0"-th hour (within the first hour).

    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)
    Image shows comparing Avg Execution Time (ms)
  21. Note

    For any of the bar graphs, you can double-click on them to view the exact numbers each bar represents in the graph.

    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 SELECT SQL statements failed when running on the testing environment.

    Figure 26. Compare rows retrieved
    Image shows comparing rows retrieved
  22. 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 Match report.
    Figure 27. Finding the Workload Aggregate Match report
    Image shows finding the Workload Aggregate Match report
  23. 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
    Image shows a record From the Workload Aggregate Match report
  24. Note

    You can quickly view performance/consistency issues by looking at the background color of the row of each SQL statement in the report. View the manual for the issues each color represents.

    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
    Image shows 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
    Image shows 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
    SQL statements with lower records retrieved
  25. 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
    Finding the Workload Match report
  26. 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
    Image shows SQL statements selecting from services table
  27. Note

    There are several values in a report that signify special issues, and they are usually represented as negative values (except for when they appear in a column displaying differences, such as Records Affected Difference). The special value "-1" represents a database error when executing the SQL statement, and a value of "-2" represents a counter overload, such as executing an SQL statement that retrieves more rows than Capture Replay can count.

    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
    Image shows SQL statements failing to run on DB2
  28. 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
    Image shows failing SQL statement
    Figure 36. Another set of SQL statements failing to run on DB2
    Image shows 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.
  29. At this point in the scenario, there are three issues that need to be resolved:
    1. Poor performance of DB2 replaying Oracle traffic
    2. Failing statement SQL A when replayed on DB2:

      SELECT count(*) FROM invent.services ORDER BY id DESC
    3. 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>
  30. To improve DB2 performance when replaying captured Oracle traffic, one task you can perform is to execute the DB2 RUNSTATS command 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 RUNSTATS on your tables. This can also be done through a GUI via IBM Data Studio or the DB2 Control Center.
    Figure 37. RUNSTATS DB2 command
    Image shows RUNSTATS DB2 command
  31. 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
    Image shows finding the Workload Exceptions report
  32. 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
    Image shows exceptions occurred during capture of Oracle
    Figure 40. Exceptions occurred during replay on DB2
    Image shows 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
    SQL A exception explanation

    To resolve this issue, you simply need to replay these SQL statements without an ORDER BY clause. 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
    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).

  33. 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
    Image shows finding the Data Staging Report
  34. 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
    Image shows finding SQL A statements in Data Staging report
  35. Click Invoke at the bottom of the report and choose modify_staging_data.
    Figure 45. Invoking the modify_staging_data GuardAPI function
    Image shows invoking the modify_staging_data GuardAPI function
  36. 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
    Image shows 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
    Image shows 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
    Image shows SQL A statements in Data Staging report after modification
  37. 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
    Image shows SQL B statements in Data Staging report after modification
  38. 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
    Image shows rerunning Replay via Replay Builder
  39. 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
    Image shows rerunning Replay Schedule Setup

    Note

    You need not use the GuardAPI functions to generate extra reports like Workload Aggregate Match and Workload Match this time around since you will not be using them.

    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
    Image shows workload comparison in Replay/Replay list tab
  40. After data compilation is complete, click Take me there.
    Figure 53. Data compilation process for Workload Comparison
    Image shows data compilation process for Workload Comparison
  41. 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)
    Image shows comparing 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)
    Image shows comparing SQL Failures (reduced amount of failures)
  42. 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
    Image shows finding the Workload Exceptions report
  43. Note

    After reviewing the reports, you can see that only 3.5 percent of all traffic comprised failures due to SQL incompatibility (37 errors vs. 1,047 SQL statements), aside from the fact that most of the failures were just repeated executions of the same incompatible SQL statement.

    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
    Image shows 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.


Conclusion

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.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

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

 


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

All information submitted is secure.

Choose your display name



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

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

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

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

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=828539
ArticleTitle=Assessing database migration with InfoSphere Optim Query Capture and Replay
publish-date=08022012