rec

!/bin/bash

Usage: ./verify_table_checksum.sh mytable.ctl

Compares Sybase vs Oracle data fidelity (excluding FILLER/IDENTITY columns, normalizing nulls/spaces)

if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi

CTL_FILE="$1"

--- Extract table name (uppercase, strip extension)

TABLE_NAME=$(basename "$CTL_FILE" .ctl | tr '[:lower:]' '[:upper:]')

--- Extract filler/ignored columns (any line containing 'FILLER' or 'IDENTITY')

FILLER_COLS=$(grep -iE 'FILLER|IDENTITY' "$CTL_FILE" | awk '{print toupper($1)}' | tr '\n' ' ')

--- Extract all column names (skip comments/blank lines)

ALL_COLS=$(grep -v -iE 'FILLER|OPTIONS|INTO|FIELDS|INFILE|LOAD|REPLACE|TRAILING|^ *$' "$CTL_FILE"
| grep -v '^[#;/]'
| sed 's/[(),]//g'
| awk '{print toupper($1)}')

--- Build normalized include list (exclude filler/identity)

COLUMNS_TO_INCLUDE=()
for col in $ALL_COLS; do
skip=false
for filler in $FILLER_COLS; do
[[ "$col" == "$filler" ]] && skip=true && break
done
$skip || COLUMNS_TO_INCLUDE+=("$col")
done

--- Connection settings

SYBASE_USER="sa"
SYBASE_PASS="yourpass"
SYBASE_DB="MYDB"

ORACLE_USER="oracle_user"
ORACLE_PASS="oracle_pass"
ORACLE_SID="ORCL"

--- Temp SQL files

TMP_SYB=$(mktemp)
TMP_ORA=$(mktemp)

--- Sybase SQL with normalization (NULL→'', trim spaces)

{
echo "SELECT CHECKSUM_AGG(BINARY_CHECKSUM("
for i in "${!COLUMNS_TO_INCLUDE[@]}"; do
[[ $i -gt 0 ]] && echo ","
echo "ISNULL(LTRIM(RTRIM(${COLUMNS_TO_INCLUDE[$i]})),'')"
done
echo ")) AS table_checksum FROM $TABLE_NAME"
} > "$TMP_SYB"

--- Oracle SQL with same normalization

{
echo "SET HEADING OFF"
echo "SET FEEDBACK OFF"
echo "SET PAGESIZE 0"
echo "SET TRIMSPOOL ON"
echo "SELECT STANDARD_HASH(LISTAGG("
for i in "${!COLUMNS_TO_INCLUDE[@]}"; do
[[ $i -gt 0 ]] && echo " || '~' ||"
echo "NVL(NULLIF(TRIM(${COLUMNS_TO_INCLUDE[$i]}),''),'')"
done
echo ", '~') WITHIN GROUP (ORDER BY 1), 'MD5') AS table_checksum FROM $TABLE_NAME;"
echo "EXIT;"
} > "$TMP_ORA"

--- Compute checksums

syb_sum=$(isql -U "$SYBASE_USER" -P "$SYBASE_PASS" -S "$SYBASE_DB" -b -Q "$(cat "$TMP_SYB")" | tail -n1 | tr -d '[:space:]')
ora_sum=$(sqlplus -s "$ORACLE_USER/$ORACLE_PASS@$ORACLE_SID" @"$TMP_ORA" | tail -n1 | tr -d '[:space:]')

--- Compare

timestamp=$(date '+%Y-%m-%d %H:%M:%S')
if [ -n "$syb_sum" ] && [ "$syb_sum" == "$ora_sum" ]; then
echo "✅ [$timestamp] MATCH for $TABLE_NAME"
else
echo "❌ [$timestamp] MISMATCH for $TABLE_NAME"
echo "Sybase: $syb_sum"
echo "Oracle: $ora_sum"
echo "$timestamp $TABLE_NAME SYBASE=$syb_sum ORACLE=$ora_sum" >> checksum_mismatches.log
fi

--- Cleanup

rm -f "$TMP_SYB" "$TMP_ORA"