Table of Contents
Ispirer Website
Ispirer Toolkit Overview
Free Trial
Database Comparison in Batch Mode
This article describes SQLWaysComparator command-line tool that is used for databases comparison.
The databases supported are: Sybase Adaptive Server Enterprise, MySQL, DB2, DB2 AS400, Oracle, Microsoft SQL Server, Progress, Informix and PostgreSQL.
Before using SQLWaysComparator in the command line, please open a command line window (cmd.exe on Windows NT/2000/XP or command.exe on Windows 9.x).
Then set options and run SQLWaysComparator.exe.
Options Overview
Usage: SQLWaysComparator.exe [/option_name=option…]
| Option name | Description | Syntax |
|---|---|---|
| Source Database Connection Attributes Specification | ||
| /SOURCE | Source database type | /SOURCE=Odbc |
| /D | Source DSN or source ODBC name | /D=DataSourceName |
| /U | Source user name | /U=UserName |
| /P | Source user password | /P=Password |
| /H | Source host or server name | /H=HostName |
| /PORT | Source database port | /PORT=PortNumber |
| /S | Source schema name | /S=SchemaName |
| Target Database Connection Attributes Specification | ||
| /TARGET | Target database type | /TARGET=Odbc |
| /TD | Target DSN or target ODBC name | /TD=DataTargetName |
| /TU | Target user name | /TU=UserName |
| /TP | Target user password | /TP=Password |
| /TH | Target host or server name | /TH=HostName |
| /TPORT | Target database port | /TPORT=PortNumber |
| /TS | Target schema name | /TS=SchemaName |
| Comparison Options | ||
| /CMP_TABLIST | Comparing two databases by table names. This is the default option, that works for each other option | /CMP_TABLIST |
| /CMP_ROWCNT | Comparing two databases by number of rows per table | /CMP_ROWCNT |
| /CMP_COLCNT | Comparing two databases by number of columns per table | /CMP_COLCNT |
| /CMP_COLNAME | Comparing two databases by column names per table | /CMP_COLNAME |
| /CMP_COLTYPE | Comparing two databases by column types per table | /CMP_COLTYPE |
| /CMP_PKVAL | Comparing two databases by primary key names and data per table | /CMP_PKVAL |
| /CMP_PKDATA | Comparing two databases by data per table. The comparison is made based on PK values | /CMP_PKDATA |
| Other Options | ||
| /DIR | Export directory for the files containing comparison information | /DIR=ExportDir |
| /MAP | Normalize name mapping XML file. Usually it is created by SQLWays tool during the migration and called NormalizeNameReport.xml as the default | /MAP=PathToDir\FileName |
| /T | Table name or template for comparison | /T=TableName |
| /TF | List of tables for comparison in file | /TF=PathToDir\FileName |
| /TRACE | Trace mode (Level: 0 or nothing - for saving log 1 - for saving errors 2 - for saving warnings 3 - for saving detailed info 4 - for saving debug info) | /TRACE=LevelTrace |
Notice: Options can be specified in any order.
Example 1: The command line below connects using ODBC DSNs to SQL Server as the source database and Oracle as the target database and compares lists of all tables from both databases. /CMP_TABLIST option is used as the default
SQLWaysComparator /SOURCE=Odbc /D=MSSQL_ODBC /U=sa /P=pwd /TARGET=Odbc /TD=ORAXE_ODBC /TU=ora /TP=ora /DIR=./compare /T=* /TRACE=3
Example 2: The command line below compares the number of rows in all the tables from both databases. The list of tables is also compared
SQLWaysComparator /SOURCE=Odbc /D=MSSQL_ODBC /U=sa /P=pwd /TARGET=Odbc /TD=ORAXE_ODBC /TU=ora /TP=ora /DIR=./compare /T=* /TRACE=3 /CMP_ROWCNT
Example 3: The command line below compares the number of columns and rows in all the tables from both databases. The list of tables is also compared
SQLWaysComparator /SOURCE=Odbc /D=MSSQL_ODBC /U=sa /P=pwd /TARGET=Odbc /TD=ORAXE_ODBC /TU=ora /TP=ora /DIR=./compare /T=* /TRACE=3 /CMP_ROWCNT /CMP_COLCNT
Example 4: The command line below compares the primary key names and the data that primary keys contain in all the tables from both databases. The list of tables is also compared
SQLWaysComparator /SOURCE=Odbc /D=MSSQL_ODBC /U=sa /P=pwd /TARGET=Odbc /TD=ORAXE_ODBC /TU=ora /TP=ora /DIR=./compare /T=* /TRACE=3 /CMP_PKVAL
Example 5: The command line below compares lists of all tables from both databases from the specified schemas
SQLWaysComparator /SOURCE=Odbc /D=MSSQL_ODBC /U=sa /P=pwd /TARGET=Odbc /TD=ORAXE_ODBC /TU=ora /TP=ora /DIR=./compare /S=schema1 /TS=schema2 /T=* /TRACE=3
Output Overview
| File Name | Contents |
|---|---|
| ComparisonReport.txt | Contains the information about the whole comparison in general |
| Report.xml | Contains the information in table form about the whole comparison in general for each option per each table |
| TableList_src.txt | Contains the list of tables that are not in the target |
| TableList_tgt.txt | Contains the list of tables that are not in the source |
| TableListRowCount_src.txt | Contains the list of source tables that has equivalents in the target database with the number of rows in these tables |
| TableListRowCount_tgt.txt | Contains the list of target tables that has equivalents in the source database with the number of rows in these tables |
| TableListColumnCount_src.txt | Contains the list of source tables that has equivalents in the target database with the number of columns in these tables |
| TableListColumnCount_tgt.txt | Contains the list of target tables that has equivalents in the source database with the number of columns in these tables |
| TableListColumnName_src.txt | Contains the list of source tables and their columns that has no equivalents in the target database in the tables with same names |
| TableListColumnName_tgt.txt | Contains the list of target tables and their columns that has no equivalents in the source database in the tables with same names |
| TableListColumnType_src.txt | Contains the list of source tables and their columns with types that has no equivalents in the target database in the tables with same names |
| TableListColumnType_tgt.txt | Contains the list of target tables and their columns with types that has no equivalents in the source database in the tables with same names |
| TableListPkColName_src.txt | Contains list of columns from each table used in primary key in the source database |
| TableListPkColName_tgt.txt | Contains list of columns from each table used in primary key in the target database |
| <Tablename>_src.txt | There are as many of such txt files as there are corresponding tables in both databases, with the <Tablename> replaced with the name of the appropriate table. Each file contains data rows that are in the source and not in the target table |
| <Tablename>_tgt.txt | There are as many of such txt files as there are corresponding tables in both databases, with the <Tablename> replaced with the name of the appropriate table. Each file contains data rows that are in the target and not in the source table |