Preface
Introduction to Data Validation Option
- Data Validation Option Overview
  - Data Validation Guidelines
- Data Validation Option Architecture
- Data Validation Test Components
- Data Validation Process
- Data Validation Views
- Data Validation Table Objects
- Data Validation Tests
- Data Validation Reports
- Data Validation Example
- Data Validation Client
Repositories
- Repositories Overview
- Adding a Repository
- Editing Repositories
- Deleting Repositories
- Refreshing Repositories
  - Troubleshooting a Repository Refresh
- Folders
- Exporting Repository Metadata
- Metadata Export and Import
  - Exporting Metadata
  - Importing Metadata
- Metadata Manager Integration
XML Data Source
- XML Data Source Overview
- XML Groups
- XML Data Conversion to a Relational Structure
  - Join View Properties for an XML File
  - Automatically Generating a Join View for an XML File
- Rules and Guidelines for XML Files
Tests for XML Data Sources
- Test for XML Data Sources Overview
- Importing XML Definitions and Viewing them in Data Validation Option
- Using XML Sources Directly in Table Pair or Single Table Tests
- Flattening XML using a Join View
- Editing Table and Column Names in the Join View
- Overriding XML Source File Information
- Troubleshooting
  - Maximum Row Size Limit
Connections
- Connections Overview
- File Connections
- Relational Connection Properties
  - Owner Name and Table Name Override
- SAP Connection Properties
Expressions
- Expressions Overview
- Expression Types
- Expression Editor
  - Data Type, Precision, and Scale of Field Expressions
  - Creating an Expression
- Expression Syntax
- Functions
- Operators
- Parameters
- Test Logic Processing in the Data Source
- Expression Examples
- Reusable Expressions
  - Rules and Guidelines for User-Defined Functions
  - Using PowerCenter User-Defined Functions in Data Validation Option
Table Pairs
- Table Pairs Overview
- Basic Properties for a Table Pair
- Advanced Properties for a Table Pair
- Rules and Guidelines for WHERE Clauses in a Table Pair
- Table Joins
  - Rules and Guidelines for Table Joins
  - Table Join Optimization
- Bad Records Configuration for a Table Pair
  - Bad Records in Flat File
  - Bad Records in Database Schema Mode
- Data Sampling for a Table Pair
  - Rules and Guidelines for Data Sampling
- PowerCenter Cache for a Table Pair
- Adding a Table Pair
- Table Pair Generation from Table Comparisons
- Editing Table Pairs
- Deleting Table Pairs
- Viewing Overall Test Results for a Table Pair
Tests for Table Pairs
- Tests for Table Pairs Overview
- Table-Pair Tests
- Test Properties for a Table Pair
- Adding a Table-Pair Test
- Automatic Test Generation for Table Pairs
  - Column Comparison by Position
- Generating Tests for a Table Pair
- Test Generation for a Table Pair from a Spreadsheet
  - Spreadsheet Requirements for Table-Pair Tests
  - Generating Tests for a Table Pair from a Spreadsheet
- Editing a Table-Pair Test
- Deleting a Table-Pair Test
- Running Table-Pair Tests
- Bad Records for a Table-Pair Test
- Viewing the Details of a Bad Record
- Table Pair Test Example
- Troubleshooting Table-Pair Tests
Single-Table Constraints
- Single-Table Constraints Overview
- Basic Properties for a Single-Table Constraint
- Advanced Properties for a Single-Table Constraint
- Bad Records Configuration for a Single-Table Constraint
  - Bad Records in Database Schema Mode
  - Bad Records in Flat File
- Data Sampling for a Single-Table Constraint
  - Rules and Guidelines for Data Sampling
- PowerCenter Cache for a Single-Table Constraint
- Adding a Single Table
- Editing Single Tables
- Deleting Single Tables
- Viewing Overall Test Results for a Single-Table Constraint
Tests for Single-Table Constraints
- Tests for Single-Table Constraints Overview
- Single-Table Constraint Tests
- Test Properties for a Single-Table Constraint
- Adding a Single-Table Constraint Test
- Test Generation for a Single-Table Constraint from a Spreadsheet
  - Spreadsheet Requirements for Single-Table Tests
  - Generating Tests for a Single-Table Constraint from a Spreadsheet
- Editing a Single-Table Constraint Test
- Deleting a Single-Table Constraint Test
- Running Single-Table Constraint Tests
- Bad Records for Single-Table Constraint Tests
- Viewing the Details of a Bad Record
Examples of Tests from Spreadsheets
- Examples of Tests from Spreadsheets Overview
- Spreadsheet Import and Export of Table Pair Tests
- Spreadsheet Import for Compare Tables
  - Scenario 1 - Add tables to the Compare Tables Import Spreadsheet
    - Adding Tables to the Compare Tables Import Spreadsheet
  - Scenario 2 - Importing the Compare Tables Spreadsheet into DVO
SQL Views
- SQL Views Overview
- SQL View Properties
- Rules and Guidelines for SQL Views
- Adding an SQL View
- Editing SQL Views
- Deleting SQL Views
- SQL View Example
Lookup Views
- Lookup Views Overview
- Lookup View Properties
- Adding Lookup Views
- Editing Lookup Views
- Deleting Lookup Views
- Lookup Views Example
Join Views
- Join Views Overview
- Join View Data Sources
- Join View Properties
- Output Field Properties
  - Column Aliases
- Adding a Join View
- Join View Example
Aggregate Views
- Aggregate Views Overview
- Aggregate View Editor
- Aggregate View Properties
  - Group By Columns
  - Using Sorted Input
- Rules and Guidelines for Aggregate Views
- Adding an Aggregate View
- Editing Aggregate Views
- Deleting Aggregate Views
- Aggregate View Example
Business Intelligence and Reporting Tools Reports
- Business Intelligence and Reporting Tools Reports Overview
- BIRT Report Examples
Dashboards
- Dashboards Overview
- Dashboard Types
DVOCmd Command Line Program
- DVOCmd Command Line Program Overview
- Rules and Guidelines for Running DVOCmd Commands
- CopyFolder
- CreateUserConfig
- DisableInformaticaAuthentication
- EncryptPassword
- ExportMetadata
- ImportMetadata
- InstallTests
  - Cache Settings
- LinkDVOUsersToInformatica
- PurgeRuns
- RefreshRepository
- RunTests
  - Running Multiple Table Pair Objects in Parallel
  - Cache Settings
- UpdateInformaticaAuthenticationConfiguration
- UpgradeRepository
- Rules and Guidelines for Special Characters
Troubleshooting
- Troubleshooting Overview
- Troubleshooting Initial Errors
- Troubleshooting Ongoing Errors
- Troubleshooting Command Line Errors
Appendix A: Datatype Reference
- Test, Operator, and Datatypes Matrix for Table Pair Tests
- Test, Operator, and Datatypes Matrix for Single-Table Constraints
- Selecting a Valid PowerCenter Transformation Data Type for an Expression
- Properties of PowerCenter Transformation Data Types
Appendix B: Reporting Views
- Reporting Views Overview
- Using the Reporting Views
  - Sample Queries for Custom Reports
- results_summary_view
- rs_bad_records_view
- results_id_view
- meta_sv_view
- meta_lv_view
- meta_jv_view
- meta_ds_view
- meta_tp_view
- meta_av_view
- rs_jv_id_view
- rs_lv_id_view
- rs_sv_id_view
- rs_av_id_view
Appendix C: Metadata Import Syntax
- Metadata Import Syntax Overview
- Table Pair with One Test
- Table Pair with an SQL View as a Source
- Table Pair with Two Flat Files
- Table Pair with XML File and Relational Table
- Single-Table Constraint
- SQL View
- Lookup View
Appendix D: Jasper Reports
- Jasper Reports Overview
  - Status in Jasper Reports
  - Generating a Report
- Jasper Report Types
- Jasper Report Examples
Appendix E: Glossary
- aggregate tests
- bad records
- constraint value
- count test
- data validation
- Data Validation Option repository
- Data Validation Option user
- Data Validation Option folder
- DVOCmd
- format test
- inner join
- join view
- lookup view
- outer join
- single table
- SQL view
- table comparison
- table pair
- threshold
- unique test
- value test

Data Validation Option User Guide

10.5.1
- 10.5.2
- 10.5
- 10.4.0

Back Next

results_summary_view

The results_summary_view view contains metadata for single-table constraints, table pairs, tests, and connections. The view also contains results of each test in each version of a single-table constraint or table pair.

For each single-table constraint, the *_a columns in the view contain information about the table on which the single-table constraint is based. The *_b columns are null for single-table constraints.

The following table describes the prefixes in the names of the view columns:

Column Prefix	Description
tp_	Single-table constraint or table pair information.
tc_	Test condition information.
tc_rs_	Test results information.
tr_	Run-time information for single-table or table-pair tests.
ti_	Test installation information. Test installation refers to the creation and installation of a PowerCenter mapping based on a single-table constraint or table pair.
conn_	Connection information for the table or file in a single-table constraint or table pair.

The following table describes the view columns that provide run-time information about each test in single-table constraints and table pairs:

View Column	Description
tr_id	Test run ID for all tests associated with the single-table constraint or table pair. Increments for each test run.
tr_state	Result state. Contains one of the following values: 2. Install error. 4. Run success. 5. Run error.
tr_start_time	Start time of the single-table or table-pair test. Time is represented as the number of milliseconds since 1970 UTC.
tr_finish_time	End time of the single-table or table-pair test. Time is represented as the number of milliseconds since 1970 UTC.
tr_start_timestamp	Start time of the single-table or table-pair test. The time corresponds to the millisecond value in the tr_start_time column and appears in standard date time form.
tr_finish_timestamp	End time of the single-table or table-pair test. The time corresponds to the millisecond value in the tr_finish_time column and appears in standard date time form.
tr_is_latest	Indicates whether this is the latest run of a given single-table or table-pair test. Contains one of the following values: 0. Not latest. 1. Latest.
tr_error_msg	Error message if the test run has an error.
ti_id	ID for test installation. Do not use in reports.
ti_folder_name	Name of the PowerCenter folder that contains the PowerCenter mapping associated with the test.
ti_mapping_name	Name of the PowerCenter mapping associated with the test.
ti_session_name	Name of the PowerCenter session that contains the PowerCenter mapping associated with the test.
ti_workflow_name	Name of the PowerCenter workflow that contains the PowerCenter mapping associated with the test.

The following table describes the view columns that provide information about each single-table constraint and table pair:

View Column	Description
tp_user_id	User ID of the person who ran the test.
tp_username	User name of the person who ran the test.
tp_obj_id	ID of the single-table constraint or table pair.
tp_version	Version of the single-table constraint or table pair.
tp_name	Description of the single-table constraint or table pair. Same as tp_description.
tp_folder_id	ID of the folder that contains the single-table constraint or table pair.
tp_folder_name	Name of the folder that contains the single-table constraint or table pair.
tp_time_stamp	Time that the single-table constraint or table pair definition was last modified. Time is represented as the number of milliseconds since 1970 UTC.
tp_edit_timestamp	Time at which the single-table constraint or table pair definition was last modified. The time corresponds to the millisecond value in the tp_time_stamp column and appears in standard date time form.
tp_comments	Comments about the single-table constraint or table pair.
tp_description	Description of the single-table constraint or table pair. Same as tp_name.
tp_table_a, tp_table_b	Name of the file, table, or view included in the single-table constraint or table pair. File and table names also include the PowerCenter repository directory.
tp_table_version_a tp_table_version_b	Version of the join view, lookup view, or SQL view included as a table in the single-table constraint or table pair.
tp_type_a, tp_type_b	Type of table included in the single-table constraint or table pair. Contains one of the following values: 1. Relational. 2. Flat file or XML file. 3. SQL view. 4. Lookup view. 6. Join view.
tp_conn_name_a tp_conn_name_b	Name of the PowerCenter connection for the table or file in the single-table constraint or table pair.
tp_owner_name_a, tp_owner_name_b	Override of the owner or name of the relational table.
tp_src_dir_a, tp_src_dir_b,	Directory that contains the file when the connection is based on a flat file or XML file. The path is relative to the machine that runs the PowerCenter Integration Service.
tp_src_file_a, tp_src_file_b	Name of the file when the connection is based on a flat file or XML file. The file name includes the file extension.
tp_in_db_a, tp_in_db_b	Indicates whether the input data is already sorted or the database sorts the input data and performs aggregation. Contains one of the following values: 0. Input data already sorted. 1. Database sorts data and performs aggregation.
tp_where_clause_a, tp_where_clause_b	WHERE clause for the table included in the single-table constraint or table pair.
tp_is_where_clause_dsq_a, tp_is_where_clause_dsq_b	Indicates whether the database processes the WHERE clause. If not, the PowerCenter Integration Service performs the sampling. Contains one of the following values: 0. False. 1. True.
tp_is_large_a, tp_is_large_b	For a table pair, indicates whether the table is larger than the other table in the table pair. Contains one of the following values: 0. False. Value is also '0' for single-table constraints. 1. True.
tp_join_list_str	For a joined table pair, comma-separated list of join conditions defined in the table pair. For a single-table constraint, comma-separated list of columns used to define the primary key for the table in the single-table constraint.
tp_external_id	External ID of the single-table constraint or table pair.
tp_enable_sampling	Indicates whether data sampling is enabled. Contains one of the following values: 0. False. 1. True.
tp_sample_percentage	Percentage of rows that are to be included in the data sample.
tp_sample_seed	Starting value used to generate a random number for data sampling.
tp_apply_sample_to	Table on which you want to perform data sampling.
tp_enable_native_sampilng	Indicates whether the database performs the data sampling. If not, the PowerCenter Integration Service performs the sampling. Contains one of the following values: 0. False. 1. True.

The following table describes the view columns that provide information about each test:

View Column	Description
tc_index	ID of the test in a single-table constraint or table pair.
tc_description	Description of the test.
tc_comment	Comments about the test.
tc_type	Type of test. Contains one of the following values: AGG SET_AinB SET_BinA SET_AeqB SET_ANotInB VALUE OUTER_VALUE FORMAT UNIQUE NOT_NULL NOT_BLANK Displays 'AGG' for the aggregate tests COUNT, COUNT_DISTINCT, COUNT_ROWS, MIN, MAX, AVG, and SUM.
tc_agg_func	Type of aggregate test. Contains one of the following values: COUNT COUNT_DISTINCT COUNT_ROWS MIN MAX AVG SUM
tc_column_a, tc_column_b	Field in the test condition .
tc_operator	Operator in the test condition.
tc_condition_a, tc_condition_b	Condition used to filter records for the test.
tc_tables_type	Type of table. Contains one of the following values: 0. Table pair. 1. Single-table constraint.
tc_threshold	Allowable margin of error for an aggregate or value test that uses the approximate operator. The value is an absolute value or a percentage value.
tc_max_bad_records	Maximum number of records that can fail the comparison for a test to pass. The value is an absolute value or a percentage value.
tc_is_case_insensitive	Ignores case when you run a test that compares string data. Contains one of the following values: 0. False. 1. True.
tc_is_treat_nulls_equal	Allows null values in two tables to be considered equal. Contains one of the following values: 0. False. 1. True.
tc_is_trim_right_ws	Ignores trailing spaces when you run a test that compares string data. Data Validation Option does not remove the leading spaces in string data. Contains one of the following values: 0. False. 1. True.
tc_expression_a, tc_expression_b	An expression that represents a field value.

The following table describes the view columns that provide information about the results of each test:

View Column	Description
tc_rs_result	Result of a test. Contains one of the following values: -2. Error. -1. No results. 0. Fail. 1. Pass.
tc_rs_failure_count	Number of bad records.
tc_rs_processed_count	Number of records processed.
tc_rs_count_rows_a, tc_rs_count_rows_b	Number of records in the table or file.
tc_rs_agg_value_a, tc_rs_agg_value_b	Value of the aggregate test. The value is null for other types of tests.

The following table describes the view columns that provide information about the each connection used in a single-table constraint and table pair:

View Column	Description
conn_connection_prop_a, conn_connection_prop_b	Connection string for relational connections. Null for all other connection types.
conn_connection_type_a, conn_connection_type_b	Type of connection. Contains one of the following values: application DB2 Microsoft SQL Server Netezza Oracle PWX DB2zOS PWX NRDB Lookup relational SAP R3 Salesforce Connection SAS Connection Sybase Teradata The "application" value represents Adabas, DB2 for i5/OS, IMS, sequential files, and VSAM data sources. The "relational" value represents database connections that use an ODBC connection.
conn_user_name_a, conn_user_name_b	User name for the connection.

Rename Saved Search

Table of Contents

Data Validation Option User Guide

Data Validation Option User Guide

results_summary_view

results_summary_view