Compare feature

From The Real BLU AGE® Wiki
Jump to: navigation, search
Updating Wiki - Mise à jour du Wiki

The Wiki is currently being updated to the Blu Age 5.4 version. - Le Wiki est en cours de migration vers la version 5.4 de Blu Age.

— The Blu Age Team

Last updated : 25 juin 2014
Apply to versions : Blu Age II 5.0 - all technologies

Requirements

BluAge Compare generates HTML reports with localStorage based Javascript functions.
This HTML 5 freature is supported by browsers listed below:

  • Firefox: 3.5 +
  • Chrome: 4+
  • Internet Explorer: 8+
  • Safari: 4+
  • Opera: 10.5+

Introduction

Each batch you created needs to be tested on one or more test cases.

A test case of a batch consists in

  • Run the batch on LEGACY environment
  • Capture data before/after the LEGACY run
  • Transform LEGACY data in MOD data
  • Run the batch on MOD environment on before data
  • Compare output MOD data with expected data

MOD: Modernized

Compare output MOD data with expected data consists in

  • Compare EBCDIC files output EBCDIC files from MOD with EBCDIC output files from LEGACY
  • Compare flat files output flat files from MOD with output flat files from LEGACY
  • Compare databases output database from MOD with output database from LEGACY

To automate testing process, compare tool has been developed and integrated to Blu Age software.

For one test case, you need

  • If your batch creates output files
    • Output and expected files
    • A repository of copybooks
    • A file with mapping of files / copybooks
      • A run job file
      • A substitution property file
  • If your batch modifies the database
    • Output and expected databases
    • A repository of SQL views

All information is stored in properties files used by Bluage Compare.

A property file contains

  • Either information for at least one comparison type
  • Comparison to compute are listed in "comparison.list" property
    • Choices are :
      • Database
      • Ebcdic
      • FlatFile
Property file for EBCDIC files Property file for flat files Property file for databases
# General information
compare.repo.dir=${project.workspace}/Compare-repository
result.dir=${project.workspace}/Batch-project/test/result
 
comparison.list=Ebcdic
 
# Compare files information
copybook.dir=${project.workspace}/Batch-project/copybooks
expected.dir=${project.workspace}/Batch-project/data/expected
output.dir=${project.workspace}/Batch-project/data/outputs
runjob.file=${project.workspace}/Batch-project/specific/runJob.xml
# General information
compare.repo.dir=${project.workspace}/Compare-repository
result.dir=${project.workspace}/Batch-project/test/result
 
comparison.list=FlatFile
 
# Compare files information
expected.dir=${project.workspace}/Batch-project/data/expected
output.dir=${project.workspace}/Batch-project/data/outputs
runjob.file=${project.workspace}/Batch-project/specific/runJob.xml
# General information
compare.repo.dir=${project.workspace}/Compare-repository
result.dir=${project.workspace}/Batch-project/test/result
 
comparison.list=Database
 
# Compare databases information
db.type=DB2
db.source.host=SRV-111110
db.source.name=DBOUT
db.target.host=SRV-111110
db.target.name=DBEXP
db.tables=TABLE_1,TABLE_2

Depending on information in the property file, to launch Bluage Compare, you need to

  • Select one property file
  • Right click on the file
  • Select “Bluage compare” menu
    • Select "Compare all" submenu

When you select a submenu, compare tool execution is launch and information is displayed

  • On progress bar: execution timeline
  • On console: execution log
    • In the same folder than the property file, log and error files are created
    • When execution finished, log and error file content are displayed to the console
    • To follow step-by-step the execution, open and refresh the log and error files

How does it works ?

Comparison process

To compare EBCDIC files

  1. Transform expected and outputs files to CSV files using copybooks
    Specify copybook folder
    Transform all files in output and expected folders
    Get mapping between files/copybboks from either runjob or substitution property file
    Get copybook from copybook folder
  2. Generate Javascript files from copybooks for HTML report
  3. Generic CSV comparison

To compare flat files

  1. Format expected and outputs flat files
  2. Generate Javascript files from flat file headers (for HTML report)
  3. Generic CSV comparison

To compare databases

  1. Extract output and expected database data to CSV files using SQL scripts (views)
    Specify object names to compare
    Get SQL scripts from compare repository
  2. Generate Javascript files from system tables using SQL scripts (for HTML report)
    Run SQL scripts and view on a reference database
  3. Generic CSV comparison

Generic CSV comparison

  1. Compare two folders with CSV files base on file name
  2. For each couple of CSV file which differ
    1. Compute differences between CSV files using DIFF command
    2. Transform DIFF report (textual report) in HTML report (browsable report) using specific ANT scripts

Comparison result directory

Result directory is a folder structure where files and databases comparison artefacts and report are generated.

In result directory, there are directories matching with IDs list into comparison.list properties

In each comparison result folder, there are

  • A folder diff : diff text report (CSV comparison text result by diffutils)
  • Folders expected and outputs : expected and outputs CSV files
    • For databases : extracted using SQL sscripts
    • For files : extracted using Java transformation
  • A folder html : HTML report files
    • Start comparison result page is index.html

Comparison states

Matching between files is based on file name.

Comparison state can be

  • Equals: there are left and right files with the same name and there are equal
  • Differ: there are left and right files with the same name but they differ
  • No output file: there is an expected file but no output file with the same name
  • No expected file: there is an output file but no expected file with the same name

Comparison repository

BluAge Compare repository is a folder structure where you put your copybooks and views to use in comparison.

The BluAge Compare repository must contains

  • A folder db
    • A subfolder views : all SQL views
    • A subfolder js : all javascript files generated from views
  • A folder files
    • A subfolder copybooks : all copybooks
      • A subfolder common : copybooks used by one or more batches
      • A subfolder for each batch (name equal to batch project name) : copybooks used only by the corresponding batch
    • A subfolder js : all javascript files generated from copybooks

Report legend

When files differ, there are 3 types of differences : added lines, deleted lines and modified lines.
A color is set for each type of difference.

Legend colors are background color in table rows (to display differences) or titles (to display summary).

Differences summary are displayed in

  • Differences between two files in HTML index report
  • If CSV files are split, differences between two split files in HTML index report
  • If HTML report is split, differences for each part in HTML part index report
  • Differences in HTML detail report
  • If CSV files is polymorphic, differences for each record type

A modified line can have cells with

  • Important differences: background color: gray / text color: red
  • Unimportant differences (value equal when remove spaces): background color: gray / text color: orange
  • No differences: background color: blue / text color: black

General properties

In property file, you can use properties below (automatically computed).

Property Description
project.name Project name (name of Eclipse project)
project.workspace Absolute path to workspace

All properties files must have at least properties below.

All properties files must have at these properties below.
Property Description
result.dir Absolute path to comparison result folder
compare.repo.dir Absolute path to compare repository folder
Property Description
comparison.list List of comparison to compute : Database, Ebcdic or/and FlatFiles separated by ','

Compare options

Split CSV file

This feature consists in splitting a CSV file in many parts (cut by number of lines) if the file size is more than the maximum file size.
The file is split from a number of lines (each part will contain the same number of lines except the last part).
The part file name is: [file name].split[part number].csv.

[part number] starts with 0 and is on 5 digits (00000).

Be careful when you use this feature because comparison process will apply for each part of file and not on the whole file.

Two properties must be added to your property file.

Property Description Default value Possible values
compare.split.size Maximum file size in MB before split a CSV file 100 1 to N
compare.split.lines Number of lines in each part when a CSV file is split 100000 1 to N

If you split a file but you didn’t have the same number of parts between left and right file, you will have comparison state below

  • No split left file: The right file has been split in more parts than left file.
  • No split right file: The left file has been split in more parts than right file.

Example

CSV file D22TREX1.G0009V00
  • More than 500 MB
  • 104 240 lines
File before split
Compare tool options compare.split.size=100
compare.split.lines=10000
Result File size = 500 MB > 100 MB = Maximum file size

So the file is split in parts of 10 000 lines
98 parts are produced where

  • Part 0 to 96 contain 10 000 lines each
  • Part 97 contains 7 240 lines

Total lines: 97 x (10 000) + 7 240 = 104 240 lines
File after split - first parts
...
File after split - last parts

Comparison index examples

Compare flat files

Requirements

Output and expected files must have the same name.
Spaces are not allowed in file names.

Special characters

The rules below is automatically applied on each field value of each line of a file during CSV formating.

  • Each character ; (CSV separator character) is replaced if needed by a space character

Required properties

To compare files, you must set properties below.

Property Description Default value Possible values
expected.dir

Absolute path to folder which contains expected files.
No subfolders allowed.

N/A Any valid absolute path
output.dir

Absolute path to folder which contains output files.
No subfolders allowed.

N/A Any valid absolute path

You also must set one of the two properties below.

Property Description Default value Possible values
substitution.props.file

To set if your project use a substitution property file to run.
Absolute path to substitution property file.

N/A Any valid absolute path to a file.
runjob.file

To set if your project use a runjob file to run.
Absolute path to runjob file.

N/A Any valid absolute path to a file.

Comparison states

For files, there are 1 more comparison states:

  • Format CSV error: an error has occured during CSV formatting

Quick example

In this example, we have:

  • A Blu Age batch project named Batch-project
    • A file produced by the batch File-1.csv
    • The corresponding expected file of File-1.csv
    • A runjob and substitution property file with refer to File-1.csv
    • A property file Test.properies with comparison information
  • A Compare repository
    • Javascript files for all flatfiles
Project Batch-project
Sample project structure
Runjob file Substitution property file
<project name="runJob" >
 <property name="jobDef" value="Job.xml" />	
 <property name="substitutions" value="
   DUMMY.FILENAME_OUT=${basedir}/../data/outputs/File-1.csv" />
</project>
<substitution-props>
	<prop name="DUMMY.FILENAME_OUT" value="__DATA__/../data/outputs/File-1.csv" />
</substitution-props>
Test property file
# General information
compare.repo.dir=${project.workspace}/Compare-repository
result.dir=${project.workspace}/Batch-project/test/result
 
comparison.list=FlatFile
 
# Compare files information
expected.dir=${project.workspace}/Batch-project/data/expected
output.dir=${project.workspace}/Batch-project/data/outputs
runjob.file=${project.workspace}/Batch-project/specific/runJob.xml
substitution.props.file=${project.workspace}/Batch-project/specific/SubstitutionsProperties.xml

Compare EBCDIC files

Requirements

BluAge Compare uses attribute cobolRecordName of RecordFormat node of copybook files (.fileformat files).
Acrross all copybooks of the project, cobolRecordName must be unique!

Output and expected files must have the same name.
Spaces are not allowed in file names.

Special characters

The rules below is automatically applied on each field value of each line of a file during transformation from EBCDIC to CSV.

  • Each character x01 to x1F + x7F is replaced by a space character
  • Each character ; (CSV separator character) is replaced by a space character
  • Each line break is replaced by a space character
  • Each tabultation is replaced by a space character

Required properties

To compare files, you must set properties below.

Property Description Default value Possible values
copybook.dir

Absolute path to folder which contains copybooks.
One subfolder allowed : common.

  • A copybook right under this folder is a specific copybook for the project
  • A copybook under folder common is a common copybook used by many projects
N/A Any valid absolute path
expected.dir

Absolute path to folder which contains expected files.
No subfolders allowed.

N/A Any valid absolute path
output.dir

Absolute path to folder which contains output files.
No subfolders allowed.

N/A Any valid absolute path

You also must set one of the two properties below.

Property Description Default value Possible values
substitution.props.file

To set if your project use a substitution property file to run.
Absolute path to substitution property file.

N/A Any valid absolute path to a file.
runjob.file

To set if your project use a runjob file to run.
Absolute path to runjob file.

N/A Any valid absolute path to a file.

Comparison states

For files, there are 3 more comparison states:

  • No copybook: there is no copybook referenced for the file (expected or output)
  • Transformation to CSV error: an error has occured during transformation from EBCDIC to CSV
  • Empty file: there is an output and expected file linked to a copybook named emptyFile.fileformat

Quick example

In this example, we have:

  • A Blu Age batch project named Batch-project
    • A common copybook: Copybook-1.fileformat
    • A specific copybook: Copybook-2.fileformat
    • A file produced by the batch File-1
    • The corresponding expected file of File-1
    • A runjob and substitution property file with mapping betwwen File-1 and Copybook-1.fileformat
    • A property file Test.properies with comparison information
  • A Compare repository
    • Common and specific copybooks
    • Javascript files for all copybooks
Project Batch-project Compare repository
Sample project structure Sample project structure
Runjob file Substitution property file
<project name="runJob" >
 <property name="jobDef" value="Job.xml" />	
 <property name="substitutions" value="
   DUMMY.copybook_out=${basedir}/../copybooks/common/Copybook-1.fileformat
   DUMMY.FILENAME_OUT=${basedir}/../data/outputs/File-1" />
</project>
<substitution-props>
	<prop name="DUMMY.FILENAME_OUT" value="__DATA__/../data/outputs/File-1" />
	<prop name="DUMMY.copybook_out" value="__DATA__/../copybooks/common/Copybook-1.fileformat" />
</substitution-props>
Test property file
# General information
compare.repo.dir=${project.workspace}/Compare-repository
result.dir=${project.workspace}/Batch-project/test/result
 
comparison.list=Ebcdic
 
# Compare files information
copybook.dir=${project.workspace}/Batch-project/copybooks
expected.dir=${project.workspace}/Batch-project/data/expected
output.dir=${project.workspace}/Batch-project/data/outputs
runjob.file=${project.workspace}/Batch-project/specific/runJob.xml
substitution.props.file=${project.workspace}/Batch-project/specific/SubstitutionsProperties.xml

Feature: Handle character LOW-VALUE

The special character x00 is equal to LOW-VALUE (also equal to NUL).
This feature allow you to specify how to handle LOW-VALUE characters in comparison:

  • Either consider them as spaces
    • In this case, difference between LOW-VALUE and SPACE characters are skipped
  • Or replace group of LOW-VALUE characters by a static string (LOW-VALUES)
    • Default mode.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.write.lowvalues

Indicate how to handle LOW-VALUE characters.

true true / false

Feature: Handle Variable block file

A variable block file is an ebcdic file containning lines with a variable length. Length is the first field of each line and the last have a variable length. Into copybook, this string type field is mark with a length equals to "VB". BA compare tools handle that.

In addition, if a variable block field have a value format like a csv line, you can compare each csv field instead of the whole csv line. To activate this feature add "generate.csv.sub.column.separator" property values with csv separator character into compare tools configuration file.

Feature: Handle trimmed records into an EBCDIC File

In some case EBCDIC file records can be separate with a line separator and the last field trimmed. To handle that compare feature must know which separator is used.

Property Description Default value Possible values
record.separator

String record separator value

record.separator.newline

Put this property at “true” if record separator is a new line. (Value of “line.separator" java property)

true / false

If “record.separator.newline” is set “record.separator” value is not take in account.

Feature: Delete temporary folder

During comparison, compare tool creates work folders where CSV are generated, diff text report are made, etc…
This feature allows you to automatically delete these temporary folders after comparison.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.delete.temp.dir

Indicates if temporary folders will be deleted at the end of comparison.

false true / false

Feature: Reject fields

Sometimes, you want to compare just a set of columns between two files because other columns are not relevant (like audit time, audit date, …).
From a list of FieldFormat names to reject, this feature automatically add ToReject="true" attribute for each FieldFormat in all copybooks.
During conversion in CSV, if a FieldFormat has attribute ToReject="true", compare tool writes (REJECT) instead of the real value in the CSV file.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.fields.reject

Indicate all FieldFormat to reject in comparison.

Empty List of FieldFormat names (each name is separated by a comma)

Note: if you want to reject a FieldFormat for only one RecordFormat in one specific copybook, you must manually add attribute ToReject="true" in this FieldFormat and not add the name of this FieldFormat in files.fields.reject property.

Feature: Remove fields

Sometimes, you want to compare just a set of columns between two files because other columns are not relevant (like audit time, audit date, …).
From a list of FieldFormat names to reject, this feature automatically add ToRemove="true" attribute for each FieldFormat in all copybooks.
During conversion in CSV, if a FieldFormat has attribute ToRemove="true", compare tool will not write the value of the field format in the CSV file.
You must generate the Javascript files for each files from which there is at least one field format of one copybooks listed as to remove.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.fields.remove

Indicate all FieldFormat to remove in comparison.

Empty List of FieldFormat names (each name is separated by a comma)

Note: if you want to remove a FieldFormat for only one RecordFormat in one specific copybook, you must manually add attribute ToRemove="true" in this FieldFormat and not add the name of this FieldFormat in files.fields.remove property.

Feature: Trim fields

This feature allows you to trim all data fields before comparing them.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.fields.trim

Indicate if data is trimmed before comparison.

false true / false

Feature: Split CSV by record type

Sometimes, you want to split polymorphic files by record type to analyse comparison result by type per file "business view" and not by file "technical view".
Each file coming from copybooks in this list will be split.

For each copybook is this list, compare tool automatically add ToSplit="true" attribute for FileFormat node. During conversion in CSV, if a FileFormat of a copybook has attribute ToSplit="true", compare tool writes one CSV file per record format.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.split.copybooks

List of copybooks from which files will be split.

Empty List of copybook names.

Example

  • File_1 is conform to Copybook-1.fileformat
  • Copybook-1.fileformat has two record format RECORD_TYPE_1 and RECORD_TYPE_2
  • File_1 is a polymorphic file and contains record of each record type

Feature: Sort CSV

Sometimes, you want to sort a file before comparison process.

From a copybook name list, each file conforms to one of this copybook will be sorted using sort options.
Before sort a file, the file is automatically split.

You must specify a sort option for each record format of this copybook you want to sort.
Sort options can be

  • A simple sort mode: sort file from left to right
  • A specific sort mode: order columns and then sort file from left to right
  • No sort

You may also want to keep original and sorted files iin comparison to see if sort option has benefit or not.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
files.sort.copybooks

List of copybooks from which files will be sorted.

Empty List of copybook names.
files.sort.opt.[copybook].[record format]

Indicate sort mode for a record format of a copybook

Empty (no sort)
  • simple: simple sort mode
  • [1 to N]: specific sort mode: list of column indexes, like 10,2,4
files.sort.keep.files

Indicate if keep original and sorted file in comparison

false true / false

Specific sort mode consists in reorder columns and then sort the file.

Feature: Expected copybook

Feature: Business key

To use this feature, you must set sort / split properties.

Feature: Generate Javascript files

Comparison result are displayed in HTML table : headers came from Javascript files generated from copybooks.
This feature allows you to generate Javascript files before launch comparison.

For each copybook, you need to generate the corresponding Javascript file

  • The first time you launch compare tool (if the Javascript file is not already generated in compare repository)
  • Each time you modify the copybook

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
generate.js.files

Indicate if generate Javascript files for copybooks before compare.

false true / false
generate.js.files.skip.common

Indicate if generate Javascript files for common copybooks.

true true / false
generate.js.files.filter

File filter to select from which copybooks generate Javascript files.

**/*.fileformat file filter

Compare databases

Supported databases by BA compare tools are :

  • DB2
  • Oracle 10g/11g
  • Postgresql 8/9
  • SQL Server 2008

Requirements

Each object is linked to a SQL file in located in [Compare repository]/db/views.
Each SQL file is named VW_[object name] and contains 2 SQL statements

  1. CREATE VIEW
    1. The first column is the business key of the object
    2. Don’t include IDENTIFIER and VERSION columns
    3. Don’t include foreign keys columns
    4. Join with parent tables to get the business key
  2. SELECT FROM VIEW
    1. The first column is the view name
    2. The second to the last columns are all other columns
    3. The statement is sorted by business key and then all other columns

A best pratice is to create one view per table.

Example
In database, we want to compare a table named TABLE_1 with 7 columns and business is composed by columns 3,4,2.

Required properties

To compare databases, you must set properties below.

Property Description Default value Possible values
db.type Database type DB2 DB2, Postgresql 8, Postgresql 9, SQL Server and Oracle
db.source.host Database source host Empty N/A
db.source.port Database source port 50000 1 ... N
db.source.name Database source name Empty N/A
db.source.schema Database source schema Empty N/A
db.source.user Database user Empty N/A
db.source.pwd Database password Empty N/A
Property Description Default value Possible values
db.target.host Database target host Empty N/A
db.target.port Database target port 50000 1 ... N
db.target.name Database target name Empty N/A
db.target.schema Database target schema Empty N/A
db.target.user Database user Empty N/A
db.target.pwd Database password Empty N/A
Property Description Default value Possible values
db.tables

Indicate which objects to compare.
If the property contains .txt, it's a reference to a text file with one object name by line.

Empty (take all object names) list of object names

Special characters

The rules below is automatically applied on each column value of each line of a file during transformation from EBCDIC to CSV.

  • Each group of one or more x00 characters is replaced by (LOW-VALUES)
  • Each x01 to x1F + x7F characters is replaced by a space character
  • Each line break character is replaced by a space character

If a column has potentially the character ; (CSV separator character), it must be replaced by a space character in the CREATE VIEW statement using DB2 REPLACE function.
REPLACE(COL_1,';',' ') AS COL_1

Feature: Delete temporary folder

During comparison, compare tool creates work folders where CSV are generated, diff text report are made, etc…
This feature allows you to automatically delete these temporary folders after comparison.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
db.delete.temp.dir

Indicates if temporary folders will be deleted at the end of comparison.

false true / false

Feature: Reject fields

Sometimes, you want to compare just a set of columns between two objects because other columns are not relevant (like audit time, audit date, …).
To reject columns for database, you must modify SELECT VIEW statement by replacing the rejected column by constant string (REJECT)

Do not remove the column from the SELECT VIEW!
Table headers are computed from columns view, you will miss one or more values if you remove column(s)!

Feature: Trim fields

Sometimes, you want to trim column value before comparing them.

To trim columns for database, you have to modify SELECT VIEW statement by calling DB2 trim function LTRIM (left trim) and RTRIM (right trim) on column.

Feature: Generate Javascript files

Comparison result are displayed in HTML table : headers came from Javascript files generated from views using system tables.
If generate.js.db is set to true, compare tool will generate Javascript files before compare databases.

For each view, you need to generate the corresponding Javascript file

  • The first time you launch compare tool (if the Javascript file is not already generated in compare repository)
  • Each time you modify the view (sql file)

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
generate.js.db

Indicate if generate Javascript files for databases before compare.

false true / false
generate.js.db.tables

List of object names from which generate Javascript files.

Empty (take all objects) list of object names

A reference database is used to generate Javascript files.

Property Description Default value Possible values
db.ref.host Database reference host Empty N/A
db.ref.port Database reference port 50000 1 ... N
db.ref.name Database reference name Empty N/A
db.ref.schema Database reference schema Empty N/A
db.ref.user Database user Empty N/A
db.ref.pwd Database password Empty N/A

Feature: Generate SQL view scripts

BluAge Compare include a wizard to generate SQL view script required by database comparison.

Requirement :

  • A property file with correct database connection informations

Follow these steps to generate SQL view scripts :

Note :

  • SQL scripts are created into [Compare repository]/db/views folder.

Report options

Feature: Side-by-side

This feature allows you to generate a new HTML report called « side-by-side » report to broswe differences by showing left file and right file side-by-side (like Winmerge tool).

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
report.side.by.side Indicates if a side-by-side report must be generated. false true / false

Feature: Keep history

This feature allows you to copy previous comparison result folders (files and db) and timestamp them before launch a new comparison process.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
report.keep.history Indicates if previous comparison result folders must be kept. false true / false

Feature: Max differences

This feature allows you to split HTML detail report in many parts to be openeable and browable easier by a browser.

To use this feature, you must set the property below in your property file.

Property Description Default value Possible values
report.max.differences Number of differences in each HTML report part. 1000 1 .. N

Annexes

Console view

Console view for compare tool extends standard Eclipse text console.

Features have been added to console:

  • Close current compare tool console
  • Close all compare tool consoles