SQLWays Product Page
Free Evaluation
This article describes SQLWaysDbCmp tool that is used for databases comparison.
The databases supported are: Sybase Adaptive Server Enterprise, MySQL, DB2, Oracle and Microsoft SQL Server.
Before using SQLWaysDbCmp 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 SQLWaysDbCmp.exe.
Usage: SQLWaysDbCmp.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_PK | Comparing two databases by primary key names and data per table | /CMP_PK |
| 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
SQLWaysDbCmp /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
SQLWaysDbCmp /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
SQLWaysDbCmp /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
SQLWaysDbCmp /SOURCE=Odbc /D=MSSQL_ODBC /U=sa /P=pwd /TARGET=Odbc /TD=ORAXE_ODBC /TU=ora /TP=ora /DIR=./compare /T=* /TRACE=3 /CMP_PK
Example 5: The command line below compares lists of all tables from both databases from the specified schemas
SQLWaysDbCmp /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
| File Name | Contents |
|---|---|
| ComparisonReport.txt | Contains the information about the whole comparison in general |
| 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 column in these tables |
| TableListColumnCount_tgt.txt | Contains the list of target tables that has equivalents in the source database with the number of column in these tables |
| 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 |
Discussion