Blog: Automating data management operations with CLI scripts in Database Compare Suite

We continue our blog post series covering key Database Compare Suite (DBCS) features. In our previous posts, we talked about comparing data and discovering data differences by leveraging the user interface of Database Compare Suite. The command-line interface of our product provides users with all the features of the main application. In this blog post we will talk about using Database Compare Suite for data management tasks in the CLI mode.

Why use Database Compare Suite in the CLI mode?

This approach allows for increased productivity. Simply put, you prepare the script once and then use it every day.

Also, finding the objects you need to compare in huge databases takes a while. With a strictly defined script, you don’t need to select these objects manually as you do in the UI mode.

Another important advantage is automation. You can use Database Compare Suite CLI scripts for test automation or even integrate them into a DevOps pipeline. All you need to do is check the output in the file and take relevant actions.

Creating the CLI scripts

A Database Compare Suite CLI script uses an XML file format for defining connection information and the operations to perform. You can use one of the following examples as a reference. The easiest way to craft your own script is to customize an existing example.

It all starts with the root element, which is DatabaseCompareSuiteScenario. This element is mandatory, and you should place it at the top of your XML file. This element may include the version number attribute and a boolean waitForExit parameter. This indicator specifies whether the command-line shell should stay open after executing this script. We use the command-line interface of Database Compare Suite quite often and we suggest setting the true value for this parameter. In this case, you will always see the output of the script you executed.

Two more elements are required in the XML script file: connection and operation. The first one specifies the connection credentials while the second determines the list of operations to execute.

The other elements include preferences, matching rules, type mapping, ExecuteSql, and OpenProject. You can specify preferences for all operations in the script and set up the rules for matching and mapping of different data types. You can also execute SQL code against the databases or even open projects saved in the UI mode of the application.

When formatting your scripts make sure that you put the closing bracket in the same line with the text rather than moving it to a new line. This is required by the XML parser that Database Compare Suite uses.

Finally, be sure to check the principal structure of the script in the application user manual. As usual, you can get the help information in the command-line interface using “/help”, “help” or “-help” in the end of the string and pressing Enter.

Running the command-line interface scenario

Open the command line in your Windows OS or use another command-line shell. Enter the path to the DBBest.DataSuite.Console.exe file.

Then, specify the full path to the command-line interface scenario file. Be sure to use quotes before and after the path to these files. The result should look as follows:

"C:\Program Files\DB Best\Database Compare Suite\DBBest.DataSuite.Console.exe" "C:\Users\Public\Data Comparison script.xml"

When you run the command, the Database Compare Suite Console window opens up and displays the progress of the compare operations.

XML scripts examples

In our previous blog posts, we talked about comparing data in two databases. We used 3 different operations to do so which we covered in these posts:

  1. Basic data comparison operation
  2. Examine data differences in detail
  3. Quickly verify migration of extremely huge tables

Here are the equivalent script examples to recreate the same workflow.

Script 1: Initial data comparison

<?xml version="1.0" encoding="utf-8"?>
<!-- ############################################################
Code example 1 - XML CLI that performs an initial data comparison
for a Oracle tables in the sourceScope schema against the
corresponding set of PostgreSQL tables in the targetScope schema.
############################################################# -->
<DatabaseCompareSuiteScenario
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   waitForExit="true" >
    <Connection
       side="left"
       databaseDialect="Oracle"
       server="oracle11"
       port="1521"
       database="orcl"
       userID="user"
       password="pass" />
    <Connection
       side="right"
       databaseDialect="PostgreSQL"
       server="postgre"
       port="default"
       userID="user"
       password="pass" />
    <Operation
       name="Data Comparison"
       sourceScope="Tables DBCS_DATA_SOURCE.dbo"
       targetScope="Tables DBCS_D_ORACLE_11_2.dbo" />
        <Export />
</DatabaseCompareSuiteScenario>

You can see that this script connects to Oracle and PostgreSQL databases and then compares data in all tables. There are two optional items we recommend using when you are getting started that are highlighted above.

  • waitForExit="true" attribute leaves the Database Compare Suite console window open after completing the operation so you can scroll through the results.
  • <Export/> element tells DBCS to create a CSV file with the results as shown below. When you specify this option without the optional path="full_path_file_name.csv" attribute, DBCS creates a CSV file for you. This file is located in the C:\ProgramData\DBBest\DatabaseCompareSuite\Export\ directory with a unique name.

Here is an example of the output file after loading it into Excel.

data comparison results

You can see that Database Compare Suite excluded several tables from comparison because they don’t have a primary key defined for the table and no alternative sort columns were defined in the XML. We highlighted these tables in yellow. The rows highlighted in green include the tables with equal data. The rest of the tables have data differences and we highlighted them in red.

Script 2: Detailed data comparison

<?xml version="1.0" encoding="utf-8"?>
<!-- ############################################################
Code example 2 - XML CLI that performs a detailed data
comparison using the same Oracle and PostgreSQL set of tables.
Output of the results to a CSV file defined in Export path.
############################################################# -->
<DatabaseCompareSuiteScenario
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    waitForExit="true" >
    <Connection
        side="left"
        databaseDialect="Oracle"
        server="oracle11"
        port="1521"
        database="orcl"
        userID="user"
        password="pass" />
    <Connection
        side="right"
        databaseDialect="PostgreSQL"
        server="postgre"
        port="default"
        userID="user"
        password="pass" />
    <Operation
        name="Detailed Data Comparison"
        sourceScope="Table DBCS_DATA_SOURCE.dbo.Table1"
        targetScope="Table DBCS_D_ORACLE_11_2.dbo.Table1" >
        <Export path="C:\Temp\Example2.csv" />
    </Operation>
</DatabaseCompareSuiteScenario>

Our next script connects to databases and then executes the detailed data comparison operation. You can run this operation only against a pair of tables, so you can’t select all tables as in the first example. Another difference here is that we redirect the output into the specified file. Now we can easily find it.

Script 3: Fast data comparison

<?xml version="1.0" encoding="utf-8"?>
<!-- ############################################################
Code example 3 - XML CLI that performs a fast data
comparison using the same Oracle and PostgreSQL set of tables.
Output of the results to a CSV file defined in Export path.
############################################################# -->
<DatabaseCompareSuiteScenario
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    waitForExit="true" >
    <Connection
        side="left"
        databaseDialect="Oracle"
        server="oracle11"
        port="1521"
        database="orcl"
        userID="user"
        password="pass" />
    <Connection
        side="right"
        databaseDialect="PostgreSQL"
        server="postgre"
        port="default"
        userID="user"
        password="pass" />
    <Operation
        name="Fast Data Comparison"
        sourceScope="Tables DBCS_DATA_SOURCE.dbo"
        targetScope="Tables DBCS_D_ORACLE_11_2.dbo" >
        <Preferences>
            <Add
                side="left"
                parent="DBCS_DATA_SOURCE.dbo.binarystrings"
                name="columns_to_sort_by"
                value="Id" />
        </Preferences>
        <Export path="C:\Temp\Example3.csv"/>
    </Operation>
</DatabaseCompareSuiteScenario>

This script executes the fast data comparison operation. In this case, our product calculates the hash values for huge tables and then compares these values instead of working directly with data. This script uses the <Preferences>  element to specify the parent data object parameters. Here’s what the output looks like:

fast data comparison operation output

Benefits of using CLI for data management

This completes the overview of the command-line interface mode of the Database Compare Suite application. You can see that creating scripts is quite simple and they are easily customizable. You can export the execution results to the file and then analyze this output in your DevOps pipeline. BTW, we plan to come up with another post that describes the integration of Database Compare Suite CLI scripts into PowerShell scripts that are part of an established DevOps pipeline.

To better understand the full capabilities of the Database Compare Suite XML schema, please check out our online help article CLI scenario schema.

We all know that those who rule data will rule the entire world. And if you really want to rule your data, you should avoid possible data loss and ensure perfect testing of your data changes. You can rely on the CLI mode of Database Compare Suite to automate different data management tasks that are regularly executed in your daily routine.

In our next blog post in the series, we will show how you can address data differences in Database Compare Suite. We will also provide you with examples of CLI scripts that address the data migration or synchronization tasks.

Be sure to download a free version of this ultimate tool for database developers and administrators.

Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin