Table Data Comparison and Auto-Insertion Script

Table Data Comparison and Auto-Insertion Script

Overview

The runTableDataComparisonReport.sh/bat script (located in AppManager17/working/bin) is used to compare table data for a given query between the Central server and the Probe server databases. It automatically detects mismatches and inserts missing records from the Central server to the Probe server and vice versa, based on the provided options. This script is available and works on both the Central and Probe servers.

The mismatch report will be generated with the following name format and stored in the APM_MisMatchReports folder in the user's directory: mismatch_report_from_AAM_YYYY-MM-DD_HH_MM_SS

Command

./runTableDataComparisonReport.sh/bat <masId> <apiKey> <queryFilePath> <direction> <asInsertQuery> <isMissingEntriesNeedToInsert> <batchSize> <timeoutInSeconds>

Arguments

Parameter

Description

<masId>

The Probe server ID

<apiKey>

The API key of the server where the script is being executed, used for authentication.

<queryFilePath>

Path to the query file containing the table comparison SQL.

<direction>

Comparison direction:

- BD → Compare both directions (default).

- MAS-NOT-IN-ADMIN → Find missing entries in Central server that exist in Probe server.

- ADMIN-NOT-IN-MAS → Find missing entries in Probe server that exist in Central server.

<asInsertQuery>

true → Generates INSERT queries instead of just reporting. (Default: false)

<isMissingEntriesNeedToInsert>

true → Automatically inserts missing entries to the Central server and Probe server. (Default: false)

<batchSize>

Number of records to process in a batch. (Default: 100)

<timeoutInSeconds>

Default value for <timeoutInSeconds> is 30 seconds if not provided.

Examples

1. Compare Both Directions (Default Behaviour) 
Compares both directions (Central server → Probe server and Probe server → Central server) without inserting missing entries.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt       

2. Find Entries Missing in Probe server (Central server → Probe server)

Finds entries that are present in the Central server but missing in the Probe server.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt ADMIN-NOT-IN-MAS    

3. Find Entries Missing in Central server (Probe server → Central server)

Finds entries that are present in the Probe server but missing in the Central server.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt MAS-NOT-IN-ADMIN    

4. Automatically Insert Missing Entries (Both Directions)

Compares in both directions and automatically inserts the missing records in both the Central and Probe server.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt BD false true    

5. Generate Insert Queries

Generates SQL INSERT statements without executing them.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt BD true false    

6. Run with Custom Batch Size

Compare and insert missing entries with a batch size of 500.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt BD false true 500    

7. Run with timeout

Compare and insert missing entries with a timeout.

./runTableDataComparisonReport.sh 1 884ff42xxxxxxxxxxxxxxxxxxx3d6d5 /path/to/query.txt BD false true 500 60   

Expected Output

  • A mismatch report displaying the missing entries.

  • If isMissingEntriesNeedToInsert=true, it automatically inserts missing entries.

  • If asInsertQuery=true, it generates INSERT statements instead of executing them.

Note:
  1. This script can be run on both Central and Probe servers.
  2. The script supports only columns that contain textual or numeric values. Columns of other types, such as BLOB or encrypted VARCHAR values, are not supported.
  3. The Central and Probe Server must be up and running for the script to execute successfully.
  4. Input queries should include the range column to define the Probe server.
  5. If the query file contains a DELETE query, cleanup will begin on both Central and Probe servers, irrespective of the specified direction. Always place DELETE queries first in the file, followed by SELECT queries.

Troubleshooting

  • Ensure the API key is correct.

  • Check if the query file path is valid and accessible.

  • If insertions fail, check for unique constraints or foreign key dependencies in stdout logs .