User Guide > Working with Test Data Warehouse > Reset a Data Set Version
  

Reset a Data Set Version

Reset is the process of moving data from a data set version in the test data mart to a target.
During the course of testing, the test data might change. You might want to reset the test data in a target with a version of the data set from the test data mart.
For example, you are responsible for testing certain features of an application. You create test data to use in the test cases that you run. Before you use the data in test cases, you save the data as a data set version in the test data mart. When you complete tests on some features, the data might change or get corrupted. You might require the original test data to run the rest of the test cases. You can replace the data in the test system with the data set version in the test data mart. Perform a reset operation from the data set version to a target. Use the test system as the target.
You can configure a reset operation to delete the target data and replace it with the data set version. In this case, the reset operation restores the target to the exact same state as the data set version. Perform this reset operation if testers do not share the tables. You do not need records that exist in the target and not in the data set version.
You might want to retain data in the target that is not in the data set version. Consider a case where multiple testers use the test data. You need the first 100 records in a Customer table. You create a data set version that contains the first 100 records of the Customer table. After running a few tests, you want to replace the test data with the data set version. You must configure the reset operation to ensure that the additional data in the target remains untouched when you reset the data in the data set version.
In reset operations that selectively delete and load data, TDM runs an SQL script to create a backup of the deleted data in the target.
You can select the required configuration when you configure the reset operation.

Classification of Data Set Version Tables

You can classify the tables in a data set version as master tables or transactional tables. The results of the reset operation depends on how you classify the tables.
A reset operation does not delete records in master tables. It performs an upsert on master tables. When testers share all records in a table, classify the table as a master table. Because the reset does not delete records in master tables, the reset operation does not delete records that might impact the work of other testers.
Classify tables that are descendants of master tables as transactional tables. Records in transactional tables are generally not shared records. When you delete a record, you do not impact the work of another tester. By default, during a reset operation TDM performs an insert on transactional tables. You can choose to change the default behavior when you configure the reset operation.
If you do not classify a table, by default a reset operation considers the table as a transactional table.
TDM performs the following tasks during an upsert:
During an insert, TDM inserts records from the data set version that do not exist in the target. If the record exists, the insert fails.
Based on how you classify tables as master and transactional, TDM identifies top-level transactional tables and begins the cascade delete at this level.
A top-level transactional table is a direct child of a master table. All parents of a top-level transactional table, if any, are master tables.
Verify the table classification before you perform a reset operation.

Classifying a Data Set Version Table

Classify tables in a data set version as master tables or transactional tables to control the results of a reset operation.
    1. Open the required data set version.
    2. Click the Plan Components.
    3. Click the Tables tab in the panel below the list of plan components to display a list of tables and the properties.
    4. Click Edit Table Type to open the Edit Table dialog box.
    5. Select the required table type from the list of table types.
    6. If you classify a table as a master table, you must choose one of the following options:
    A list of the tables in the data set version with the impact of the classification on the tables appears.
    The following image shows the Edit Table dialog box:
    The Edit Table dialog box shows the table classification options. The table is classified as a master table with the option to mark all descendant tables as transactional selected. The tables in the data set are listed with the current classification and change, if any.
    7. Click OK.

Reset With and Without Downtime

You can choose to disable constraints and indexes when you perform a reset operation.
Disabling constraints during the reset might increase the performance of the reset operation because the constraints do not exist. TDM performs delete and load in multiple tables in parallel and the reset operation might take less time to complete.
When you do not disable constraints between tables during a reset operation, TDM must check the order in which deletes and inserts happen. TDM cannot perform load and delete for multiple tables in parallel. TDM must consider constraints before deleting records. The reset operation might take longer to complete than when you disable constraints and indexes.
You apply constraints between tables. When you disable constraints, you disable constraints for the entire table. During the time the constraints are disabled, tests that you run using the tables might fail as the constraints do not exist. Consider a case where multiple testers use the test data. You create a data set version that contains the first 100 records of the Customer table. After running a few tests, you want to reset the test data with the data set version. If you disable constraints for the table during the reset operation, tests run by other testers that include the Customer table might fail. The reset operation causes a downtime for running tests that use the target connection.
While disabling constraints can increase performance, there might be a tradeoff with a downtime in testing if the target tables are shared among testers.
If multiple testers use the tables, a slower reset operation might be better than a downtime in testing for multiple testers. If the target tables are not used by other testers, and you are sure that no tests that include the tables are run during the reset, you can increase the performance of the reset by disabling constraints.
Analyze the requirement and consider this tradeoff when you configure a reset operation.

Resetting a Data Set Version

You can reset a specific version of a data set to a target. Reset a data set version to a target to reuse a specific version of test data or to reset test data to an earlier state. You can configure the reset operation based on how you want to replace the test data.
Ensure that you classify the tables in the data set version before you perform a reset operation. TDM considers unclassified tables as transactional tables during a reset operation.
    1. Open the required data set version.
    2. Click Actions > Reset.
    The Reset dialog box opens.
    3. Select the target connection from the list of connections.
    The source is the test data mart. You cannot edit the source. Select the database where you want to replace the data as the target connection.
    4. Configure the settings for the reset operation:
    5. Enter the reset recovery properties.
    You cannot enter the reset recovery properties if you choose to truncate tables. TDM does not create a backup when it truncates tables.
    6. Edit the Error and Recovery and Advanced Settings as required.
    7. Click Next.
    8. Optional. You can override the reset settings at the data source level or at the table level.
    9. Select the PowerCenter Integration Service to use for the reset operation. You must select the service that you configured in the Test Data Manager Service.
    10. Select to run the operation or schedule it to run later.
    11. Click OK.
You can view the job log messages in the Monitor tab.

Reset Process Flow

You can perform a reset operation that truncates tables or a reset operation that does not truncate tables.
Perform a reset with the truncate tables option when you own the target schema or when you do not share the data. By default, TDM disables the constraints and indexes during the reset. The downtime that this causes does not impact the work of other users because you do not share the tables.
Do not truncate tables if you share the table data and you need to reset a part of the data. You might delete data that other users need.
The following image indicates the steps that a reset operation goes through:
The reset process figure demonstrates the steps that the reset process goes through depending on the options that you select. It contains the tasks that the reset performs if you choose to perform a reset that truncates tables. It also contains the steps that the reset performs if you use the default settings.
Note: The image does not include tasks performed during backup.

Reset with Truncate Tables Process Flow

You can choose to truncate the tables when you perform a reset operation.
TDM performs the following tasks when you perform a reset operation and select the Truncate Tables option:
  1. 1. Disables the constraints and indexes.
  2. 2. Deletes all tables in the target.
  3. 3. Inserts all tables from the data set version to the target.
  4. 4. Enables the constraints and indexes.

Reset Without Truncate Tables Process Flow

You can perform a reset operation without truncating the tables.
TDM performs the following tasks when you perform a reset operation and do not select the truncate tables option:
  1. 1. Identifies records for deletion from transactional tables that have child tables.
  2. 2. If there are no records to delete, TDM skips this step. If records for deletion exist, TDM performs the following tasks.
    1. a. Creates staging tables for the identified records and stages the keys.
    2. b. Identifies top-most transactional tables in the data set version.
    3. c. For each record in a top-most transactional table in the target, if a record exists in the data set version, TDM marks the record in the staging table.
    4. d. Runs an SQL script to cascade mark records in the staged tables for deletion. If a parent record is marked for deletion, TDM marks the child record for deletion.
    5. e. Checks if recovery is enabled for the reset operation and that the user name and prefix information are available.
    6. f. If the recovery option is enabled and the prefix information provided, TDM performs the following tasks. If the recovery option is not enabled, TDM skips this step.
      1. a. Creates temporary tables in the backup schema and then moves records from the staging area to the backup schema.
      2. b. Stores a backup of all records marked for deletion in the backup tables in the backup schema. TDM runs an SQL script to back up the tables and uses the naming convention prefix_targetname.
  3. 3. Checks if the Disable Constraints and Indexes option is enabled. If the option is enabled, the reset operation performs the following steps:
    1. a. Disables the constraints and indexes.
    2. b. Deletes marked records from the target. TDM reads the marked records from the staging tables.
    3. c. Moves data from the data set version to the target. TDM processes multiple tables in parallel because the constraints and indexes are disabled.
    4. d. Enables the constraints and indexes.
      5. If the option is not enabled, the reset operation performs the following steps:
    1. a. Deletes marked records from the target. TDM reads the marked records from the staging tables.
    2. b. Moves data from the data set version to the target. TDM processes the tables according to constraints because the constraints exist.
  4. 4. Deletes the temporary tables, if created.
  5. 5. Deletes the staging tables, if created.
  6. 6. If the reset operation is successful, TDM deletes the backup tables. If the reset operation is not successful, TDM retains the backup tables and displays an error message.

Restoring Deleted Data After a Failed Reset

If you need to restore data that was deleted during a failed reset operation, you can restore the data from the backup created during the reset. Run a separate plan to restore the data from the backup. Ensure that you have the backup prefix used during the failed reset operation.
Perform the following high-level steps to restore data from the backup schema:
    1. Create a group that contains all the tables that were backed up during the reset operation.
    The backed up tables include all transactional tables.
    2. Open a project and create a plan.
    3. Add the group as a subset component to the plan.
    4. Add the connection that contains the backup schema as the source connection.
    5. Add the database where you want to restore the data as the target connection.
    6. In the target settings in the plan, disable the constraints and indexes.
    Select the following values for the Update Strategy properties:
    7. In the Advanced Settings, enter the backup prefix in the Reset Recovery Prefix field.
    8. Run the plan.