Performing data validation v7
The current working directory from which you invoke the Data Validator script runValidation.sh (runValidation.bat for Windows) must be the bin subdirectory containing the script (that is, XDB_HOME/bin).
For example, if the Replication Server is installed in its default directory location, then issue the following command before invoking the Data Validator:
cd /usr/edb/xdb/bin
Similarly for Windows hosts, issue the following:
cd C:\Program Files\edb\EnterpriseDB-xDBReplicationServer\bin
The general command format for invoking the Data Validator is the following:
./runValidation.sh { –ss | --source-schema } schema_name [ option ] ...
schema_name is the name of the schema in the source database containing the tables to validate.
For Windows hosts, the command format is the following:
runValidation { –ss | --source-schema } schema_name [ option ] ...
The following option displays the Data Validator version:
./runValidation.sh { –v | --version }
On Linux the version is displayed as follows:
$ ./runValidation.sh --version EnterpriseDB DataValidator Build 3
On Windows the version is displayed as follows:
C:\Program Files\edb\EnterpriseDB-xDBReplicationServer\bin>runValidation -v EnterpriseDB DataValidator Build 3
The following option displays the help information.
./runValidation.sh { –h | --help }
For example,
$ ./runValidation.sh --help Usage: runValidation.sh (-v | --version) | (-h | --help) runValidation.sh (-ss | --source-schema) SOURCE_SCHEMA [OPTIONS] CONNECTION_INFO_FILE OPTIONS: (-ts | --target-schema) target-schema-name (-it | --include-tables) comma-seperated-tables-name (-et | --exclude-tables) comma-seperated-tables-name (-ld | --logging-dir) logging-dir-path (-ds | --display-summary) (true|false) (-srs | --skip-rowsonlyin-source) (true|false) (-srt | --skip-rowsonlyin-target) (true|false) (-srb | --skip-rowsin-both) (true|false) (-fs | --fetch-size) row count (-bs | --batch-size) row count (-sdbms | --source-dbms) source database type (-sh | --source-host) source database server name/IP (-sp | --source-port) source database server port (-sdb | --source-database) source database name (-su | --source-user) source database user id (-spw | --source-password) source database user password (-tdbms | --target-dbms) target database type (-th | --target-host) target database server name/IP (-tp | --target-port) target database server port (-tdb | --target-database) target database name (-tu | --target-user) target database user id (-tpw | --target-password) target database user password (-uoc | --use-ora-case) use oracle (upper) case for table name
New enhancement
The --use-ora-case option is available in Replication Server 7.1 and later.
Note
Data Validator does not support column mapping. With custom column mapping, table columns in the source and target databases can have different data types. As a result, the Data Validator is not able to validate the data when column mapping is applied.
The general syntax for all options except for --version and --help is shown by the following:
./runValidation.sh –ss schema [ -ts schema ] [ -it table_1 [,table_2 ] ... ] [ -et table_1 [,table_2 ] ... ] [ -srs { true | false } ] [ -srt { true | false } ] [ -srb { true | false } ] [ -ld log_directory_path ] [ -ds { true | false } ] [ -sdbms database_type ] [ -sh host ] [ -sp port ] [ -sdb dbname ] [ -su user ] [ -spw password ] [ -tdbms database_type ] [ -th host ] [ -tp port ] [ -tdb dbname ] [ -tu user ] [ -tpw password ] [ -bs row_count ] [ -fs row_count ] [ -uoc ]
For clarity, the syntax diagram shows only the single-character form of the option. The description of each option lists both the single-character and multi-character forms.
Specifying any database connection option (-sdbms through -tpw listed in the syntax diagram) overrides the corresponding parameter in the datavalidator.properties file. See Installing and configuring the data validator for information on the datavalidator.properties file.
Options
-ss, --source-schema schema
The schema of the source database containing the tables to compare against the target database.
-ts, --target-schema schema
The schema of the target database containing the tables to compare against the source database. If omitted, the schema of the target database is the same schema as specified for the source database with the -ss option.
-it, --include-tables table_1 [,table_2 ] ...
The tables in the source schema to include for comparison. If omitted, all tables in the source schema are compared against tables in the target schema. The exception is the tables excluded from comparison using the -et option. Don't use white space between the comma and table names.
-et, --exclude-tables table_1 [,table_2 ] ...
The tables in the source schema to exclude from comparison. If omitted, only those tables specified with the -it option are included for comparison. If both the -it and -et options are omitted, all source schema tables are included for comparison. Don't use white space between the comma and table names.
-srs, --skip-rowsonlyin-source { true | false }
When you specify true, the logging of differences for rows that exist only in the source database table are skipped. The default is false.
-srt, --skip-rowsonlyin-target { true | false }
When you specify true, the logging of differences for rows that exist only in the target database table are skipped. The default is false.
-srb, --skip-rowsin-both { true | false }
When you specify true, the logging of differences for rows:
- That exist both in the source and target database tables
- Have the same primary key
- Have different non-primary key values
are skipped. The default is false.
-ld, --logging-dir log_directory_path
Directory path to create and store the Data Validator log and diff files. If log_directory_path doesn't exist, Data Validator attempts to create it. If a full directory path isn't specified log_directory_path is created or assumed to be located relative to the XDB_HOME/bin subdirectory where the runValidation.sh script is invoked. (That is, the logs directory is XDB_HOME/bin/log_directory_path.) Be sure the operating system account used to invoke the runValidation.sh script has the privileges to create the directory if needed or to create files in the specified directory. The default is the XDB_HOME/bin/logs directory.
-ds, --display-summary { true | false }
Specify true to display only the Data Validator summary. This value omits the source and target database connection information as well as the detailed breakdown of the results by source database table. Specify false to display all of the Data Validator results. The type and amount of information that is displayed at the command line console when the Data Validator is invoked is the same information that is also stored in the log file for that run. The default is false.
-sdbms, --source-dbms database_type
The type of the source database server. Supported types are oracle, enterprisedb, sqlserver, sybase, and mysql.
-sh, --source-host host
The IP address or server name of the host where the source database server is running.
-sp, --source-port port
The port number on which the source database server is listening for connections.
-sdb, --source-database dbname
The name of the source database.
-su, --source-user user
The database user name for connecting to the source database.
-spw, --source-password password
The password of the source database user in unencrypted form.
-tdbms, --target-dbms database_type
The type of the target database server. Supported types are enterprisedb and oracle.
-th, --target-host host
The IP address or server name of the host whereh the target database server is running.
-tp, --target-port port
The port number on which the target database server is listening for connections.
-tdb, --target-database dbname
The name of the target database.
-tu, --target-user user
The database user name for connecting to the target database.
-tpw, --target-password password
The password of the target database user in unencrypted form.
-bs, --batch-size row_count
The -bs option specifies the number of rows to group in a batch to use for comparison across the source and target database tables. For example, if a table contains 1000 rows, then a -bs setting of 100 requires 10 batch iterations to complete the comparison across the source and target databases. The Data Validator reads 100 rows, both from the source and target tables, and adds them in source and target buffers. The validation thread then reads the 100 rows from the source and target buffers and performs the comparison. It then moves to read and prepare the next 100 rows for comparison, and so on. The actual database round trips required to bring in 100 rows from the database depends on the -fs option for the fetch size. For example, an -fs setting of 100 needs just one round trip and an -fs setting of 10 requires 10 database round trips.
-fs, --fetch-size row_count
Performing data validation for tables that are quite large can cause the Data Validator to stop with an out-of-heap-space error when using the default fetch size of 5000 rows. Use the -fs option to specify a smaller fetch size to help avoid the out-of-heap-space issue. The result set iteration brings in as many rows as represented by the row_count value in a single database round trip.
Examples
The following examples use an Oracle source database and an EDB Postgres Advanced Server target database to compare the tables in schema EDB on Oracle against the tables in schema public in EDB Postgres Advanced Server.
The following lists the tables in schema EDB along with the content of tables DEPT and EMP in the Oracle source database:
SQL> SELECT table_name FROM user_tables;
TABLE_NAME ------------------------------ ORATAB DEPT EMP JOBHIST
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 FINANCE CHICAGOSQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9001 SMITH ANALYST 7566 8500 20
9002 ROGERS SALESMAN 7698 8000 4000 30
16 rows selected.The following lists the tables in schema public along with the content of tables dept and emp in the EDB Postgres Advanced Server edb database:
edb=# \dt List of relations
Schema | Name | Type | Owner --------+---------+-------+-------------- public | dept | table | enterprisedb public | emp | table | enterprisedb public | jobhist | table | enterprisedb (3 rows)
edb=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)edb=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+--------------------+---------+---------+-------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 9001 | SMITH | SALESMAN | 7698 | | 8000.00 | 4000.00 | 30 9002 | ROGERS | SALESMAN | 7698 | | 9500.00 | 4000.00 | 30 (16 rows)
Note the following differences:
- The Oracle
EDBschema contains one moew table namedORATABthat doesn't exist in the EDB Postgres Advanced Server public schema. - The Oracle DEPT table contains one extra row with
DEPTNO 50that doesn't exist in the EDB Postgres Advanced Server dept table. - The rows in the EMP table with
EMPNOvalues9001and9002have column values that differ between the Oracle and EDB Postgres Advanced Server tables. - In this example, the JOBHIST table contains identical rows for both the Oracle and Advanced Server tables.
The content of the datavalidator.properties file is set as follows:
###############################################################
Source database connection
###############################################################
#source_dbms=(enterprisedb | oracle | sqlserver | sybase | mysql)
source_dbms=oracle
source_host=192.168.2.23
source_port=1521
source_database=xe
source_user=edb
source_password=password
###############################################################
Target database connection
###############################################################
#target_dbms=(enterprisedb | oracle)
target_dbms=enterprisedb
target_host=localhost
target_port=5444
target_database=edb
target_user=enterprisedb
target_password=passwordThe following example compares all tables in the Oracle EDB schema against the EDB Postgres Advanced Server public schema.
The Data Validator log files are created in directory /home/user/datavalidator_logs as specified with the -ld option. The operating system account used to invoke the runValidation.sh script has write access to the /home/user directory so the Data Validator can create the datavalidator_logs subdirectory.
$ cd /usr/edb/xdb/bin $ pwd /usr/edb/xdb/bin $ ./runValidation.sh -ss edb -ts public -ld /home/user/datavalidator_logs EnterpriseDB DataValidator Build 3 ---------------------------------------------------------------------------- Source and target databases connection information ---------------------------------------------------------------------------- Source database: DBMS: ORACLE Host: 192.168.2.23 Port: 1521 Database: xe User: edb Target database: DBMS: ENTERPRISEDB Host: localhost Port: 5444 Database: edb User: enterprisedb ---------------------------------------------------------------------------- Databases data validation process started... ---------------------------------------------------------------------------- Validating Table DEPT Rows validated: 5 Finished validating table DEPT with 1 errors. Logging errors details in the diff file... Validating Table EMP Rows validated: 16 Finished validating table EMP with 2 errors. Logging errors details in the diff file... Validating Table JOBHIST Rows validated: 17 Finished validating table JOBHIST with 0 errors. Validating Table ORATAB Table not validated as it does not exist on the target database. DataValidator found 3 errors across source and target databases. For detailed error report see datavalidator_20150713-144417.diff file. ---------------------------------------------------------------------------- Data validation process has completed. ---------------------------------------------------------------------------- *************************************************************** DataValidator Summary *************************************************************** All tables count: 4 Validated tables count: 3 Rows count: 38 Errors count: 3 Missing tables on the target database count: 1 Tables list: - EDB.ORATAB Tables having only unsupported datatypes count: 0 Tables having primary key limitation count: 0 Total time(s): 0.678 Rows per second: 56 ***************************************************************
The Data Validator output indicates the following:
- There is one error in the DEPT table (the missing row).
- There are two errors in the EMP table (the two rows with mismatching column values).
- The JOBHIST table contains no errors.
- The ORATAB table doesn't exist on the target database.
The following shows the files created in the Data Validator logs directory:
$ pwd /home/user/datavalidator_logs $ ls -l total 24 -rw-rw-r-- 1 user user 18999 Aug 13 15:44 datavalidator_20150713-144417.diff -rw-rw-r-- 1 user user 2133 Aug 13 15:44 datavalidator_20150713-144417.log
The log file contains the same content as displayed when the Data Validator is invoked. The diff file compares the differences where errors were detected.
The following example includes only tables dept and emp with the -it option when comparing the Oracle EDB schema against the Advanced Server public schema.
$ cd /usr/edb/xdb/bin $ pwd /usr/edb/xdb/bin $ ./runValidation.sh -ss edb -ts public -ld /home/user/datavalidator_logs -it dept,emp EnterpriseDB DataValidator Build 3 ---------------------------------------------------------------------------- Source and target databases connection information ---------------------------------------------------------------------------- Source database: DBMS: ORACLE Host: 192.168.2.23 Port: 1521 Database: xe User: edb Target database: DBMS: ENTERPRISEDB Host: localhost Port: 5444 Database: edb User: enterprisedb ---------------------------------------------------------------------------- Databases data validation process started... ---------------------------------------------------------------------------- Validating Table DEPT Rows validated: 5 Finished validating table DEPT with 1 errors. Logging errors details in the diff file... Validating Table EMP Rows validated: 16 Finished validating table EMP with 2 errors. Logging errors details in the diff file... DataValidator found 3 errors across source and target databases. For detailed error report see ``datavalidator_20150714-123353.diff`` file. ---------------------------------------------------------------------------- Data validation process has completed. ---------------------------------------------------------------------------- *************************************************************** DataValidator Summary *************************************************************** All tables count: 2 Validated tables count: 2 Rows count: 21 Errors count: 3 Missing tables on the target database count: 0 Tables having only unsupported datatypes count: 0 Tables having primary key limitation count: 0 Total time(s): 0.539 Rows per second: 39 ***************************************************************
The following example excludes tables ORATAB and jobhist with the -et option when comparing the Oracle EDB schema against the EDB Postgres Advanced Server public schema. The -ds true option results in the display of only the Data Validator summary.
$ ./runValidation.sh -ss edb -ts public -ld /home/user/datavalidator_logs -et ORATAB,jobhist -ds true Databases data validation process started... *************************************************************** DataValidator Summary *************************************************************** All tables count: 2 Validated tables count: 2 Rows count: 21 Errors count: 3 Missing tables on the target database count: 0 Tables having only unsupported datatypes count: 0 Tables having primary key limitation count: 0 Total time(s): 0.535 Rows per second: 39 ***************************************************************
For this run, the corresponding log file contains only the Data Validator summary, omitting the source and target database connection information along with the error breakdown by table.