Setting up Database Comparisons

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Comparing Database Data >

Setting up Database Comparisons

A database data comparison involves comparing a "left" data source (table, view, or recordset retrieved by a SQL query) with a "right" one.

 

To configure a data comparison, open the client configuration file and add a new [datadiff:<name>] section, where <name> is the unique name by which you want to identify this database comparison. In the database comparison job, you essentially specify what should be "left" data source and what should be the "right" one. It is assumed that you have already created two or more data sources as described in Setting up Data Sources.

 

The parameters that may be present in a [datadiff] section are listed below.

 

enable-driver-logging

Optional parameter. Turn it on if you need the database driver to return more detailed information in case of errors. Example:

 

enable-driver-logging=true

 

Note that enabling this option may slow down execution.

 

server-left, server-right

Conditional parameter. This option lets you specify that the left or right data source of the comparison is defined in the server configuration file (not the client configuration file). The option must point to a named data source as it is defined in the server configuration file on the machine where DiffDog Server runs. Example:

 

server-left=products

server-right=resourcesdb

 

To point to a data source on the machine where the DiffDog Command Line Client runs, use the left and right options instead (see below).

 

left, right

Conditional parameter. This option sets the left or right side of the comparison. The option must refer to a named data source [datasource:<some_name>] as it was defined in the client configuration file on the machine where the comparison runs. Example:

 

left=hr

right=testdb

 

If you don't set these options, then the server-left or server-right options must be set instead. Combined comparison between client data sources and server data sources are also possible, for example:

 

left=hr

server-right=testdb

 

In the example above, the data source "hr" is on the machine where DiffDog Command Line client runs (without a server). The data source "testdb" is defined in the server configuration file on the machine where DiffDog Server runs.

 

map

Optional parameter. This option specifies which columns from the left side of the comparison should be compared to those from the right side of the comparison. For example, you might want to compare columns col1, col2, col3 from the "left" table with columns colA, colB, colC from the "right" recordset. To indicate a column, you can specify either the ordinal number of the column, starting with 1, or the column name. If you use the column name, the name must be enclosed within quotes. Example:

 

map = 1 => "col 2", 2 => "col1", "h" => 6, "i" => "x"

 

For each pair of columns shown above, the part before "=>" means "on the left side" and the part after "=>" means "on the right side".

 

If you don't define column mappings, the application will attempt to map columns automatically based on the name and data type. However, this is not guaranteed to work correctly if the columns on the left and right have different names and types.

 

xml-aware

Optional parameter. When enabled, this option performs XML (not text) comparison for database fields of XML type. This applies only to fields whose data type is recognized by the driver as XML. Database fields which store XML as varchar or clob are not affected by this option. If you need to treat such fields as XML type, use the sql option instead of table and perform type conversion to XML by means of SQL queries.

 

If you set this option, any XML comparison options present in the configuration file or specified at the command line will apply as well. On Windows, if DiffDog is installed on the same machine as the DiffDog Command Line Client, then XML options present in the registry will apply as well. DiffDog Server does not read the Windows registry options. You can always view the current configuration options of the client by running the showcfg command. To view the configuration options of the server, run the showcfg command of the server executable.

 

For reference to all XML comparison options, see the diff command.

 

whitespace-mode

Optional parameter. Applicable only for database fields of text type such as varchar, clob, and so on. The option defines how whitespace characters should be treated for comparison. Whitespace characters are any of the following: space, tab, carriage return, and line feed. Not specifying this option means that whitespace characters are always relevant for comparison. Otherwise, set one of the following values:

 

normalize

Text A is equal to Text B if, after normalization, characters in Text A correspond to those in Text B. “Normalization” means that multiple consecutive occurrences of whitespace characters are replaced by a single space character. In addition, the leading and trailing whitespace characters are trimmed for each line of text.

strip

Text A is considered equal to Text B if, after stripping whitespace characters, characters in Text A correspond to those in Text B. In other words, any whitespace characters are stripped from the text and considered not relevant for comparison.

 

ignore-case

Optional parameter. Applicable only for database fields of text type such as varchar, clob, and so on. Valid values:

 

true

Perform a case-insensitive comparison (for example, "A" and "a" are treated as equal).

false

(Default) Perform a case-sensitive comparison (for example, "A" and "a" are not treated as equal).

 

server-output-path

Optional parameter. Specifies the path to which the comparison results will be saved on the server machine, for example:

 

server-output-path=/path/on/server.xml

 

If DiffDog Command Line Client and DiffDog Server run both on the same machine, you can use server-output-path and client-output-path interchangeably.

 

If this parameter not set, the comparison output will be displayed at the command line (assuming that you did not set the option -quiet=true).

 

client-output-path

Optional parameter. Specifies the path to which the comparison results will be saved on the client machine, for example:

 

client-output-path=/path/on/client.xml

 

If this parameter not set, the comparison output will be displayed at the command line (assuming that you did not set the option -quiet=true).

 

output-mode

Optional parameter. Specifies the output format of the comparison result. Valid values are text and xml, for example:

 

output-mode=xml

 

When this option is not set, the default value is text.

 

treat-null-as-empty

Optional parameter. Enable this option only if you need to treat NULL database values as empty strings.

 

treat-null-as-empty=false

 

For example, by default, a comparison between the two recordsets illustrated below will result in a difference, because the second row on the left is empty and not equal to the second row on the right. However, if you set the treat-null-as-empty option to true, both recordsets will be considered equal.

dds_null_recordsets


© 2019 Altova GmbH