Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Appendix A: Datatype Reference
  22. Appendix B: Reporting Views
  23. Appendix C: Metadata Import Syntax
  24. Appendix D: Jasper Reports
  25. Appendix E: Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Generating Table Pairs and Tests

Generating Table Pairs and Tests

Run a table comparison to generate table pairs between tables in two specified folders. Data Validation Option also generates count and value tests for each table pair.
  1. In Data Validation Option, click
    Action
    Compare Tables
    .
    The
    Compare Tables
    dialog box appears.
    The dialog box shows table properties for both tables in the table pair, comparison properties, and test properties. The dialog box also contains a section where you can specify a file that maps each source table to a target table.
  2. Select the repositories that contain the tables that you want to compare.
  3. Select the folders that contain the tables that you want to compare.
  4. Select
    Sources
    or
    Targets
    for each subfolder.
  5. Select the default connection for each folder.
  6. If the connection is for IBM DB2, enter the default database owner name in the
    Owner
    field.
  7. If connection is for flat files, in the
    Source Dir
    field, enter the default directory that contains the files.
  8. If the connection is for SAP, click
    Select SAP Table Parameters
    and configure the default SAP source parameters.
  9. Select
    Sort in DB
    to push the sorting logic for joins in table pairs to the database.
  10. You can select whether to save all the bad records.
  11. If you choose to save all the bad records, select whether to save the bad records in a flat file or in the Data Validation Option schema.
  12. To generate table pairs for tables that have different names, select
    Table names differ between source and target
    , and then click
    Browse
    to find the spreadsheet file that contains the table pair definitions.
  13. To generate table pairs for tables that have the same name, select whether to compare columns by name or position.
    If you select compare columns by position, if applicable, specify the number of columns to skip in Table A or Table B.
  14. In the
    Folder to Place Tests
    field, select the folder to store the table pairs and tests.
  15. In the
    Tests to Include
    field, specify whether to generate count tests or count and value tests.
  16. In the
    Trim Trailing Spaces from All Generated Tests
    field, select whether to trim the trailing spaces in the values of table columns.
  17. To generate value and outer value tests for tables that do not have primary keys, click
    Browse
    to select the text file that lists the primary keys for the flat files or tables.
    If you select a primary key for the table and specify a primary key file, Data Validation Option ignores the primary key file.
  18. If a primary key does not exist for a table or file, in the
    If no PK
    field, select whether you want to generate count tests only or skip the test generation.
    If you skip the test generation and no primary key is defined, Data Validation Option does not generate the table pair.
  19. Click
    Next
    .
    The
    Compare Tables
    dialog box previews table pairs that will be generated.
    The following figure shows a preview of the table pairs that will be generated and the errors:
    The dialog box shows table pairs that will be created. It also shows the following errors: no joins are defined and the table pair description is already used by an existing table pair.
    1. Selected table pair
    2. Errors for the selected table pair
  20. To not generate a table pair, select the table pair and click
    Remove
    .
  21. If there is a problem with a table pair, select the table pair to view the error.
  22. If an error says that no join is defined, select the table pair, and then select the join fields in the
    Join Conditions
    area.
  23. Click
    Mismatched Tables
    to view a list of all tables that are not matched in each folder.
    Data Validation Option does not generate a table pair or tests for tables that are not matched.
  24. Click
    Finish
    .
    Data Validation Option generates the table pairs and tests.
If a default setting does not apply to a particular table in a table pair, you can edit the table pair to change the setting after Data Validation Option generates the tables pairs and tests. For example, if a table requires a different connection than the default connection that you specified for all tables in the table comparison, edit the table pair and change the connection.