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

© 1999-2024, Ispirer Systems, LLC. All Rights Reserved.