Table of Contents

Search

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

Data Validation Option User Guide

Data Validation Option User Guide

results_summary_view

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.