×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

写个简单的script

本文发表在 rolia.net 枫下论坛我有2个server, 11个table 都是要保证一样的。就是两两一定要一致。如果映射的话太麻烦
用一个script, 读出2个table, 用MD5 hash 一下,比较下异同然后打印出哪行在哪个database中有出入
很粗糟不过可以参考下。

#!/usr/bin/perl
use FindBin;
use lib "$FindBin::Bin/lib";
use strict;
use File::Basename;
use Time::Local;
use Getopt::Std;
use Carp;
use English;

use vars qw($DBCONSTR1 $DBCONSTR2);

use Digest::MD5 qw(md5 md5_hex md5_base64);

$| = 1 ;

# The hash we use to compare 2 dbs
my %hData;

my $program = $0;

# -------------------------------------------------------------------------
my $BaseName = basename ($0);
my $dirName = dirname ($0);

sub Usage {
print <<EEOF;
Usage: $BaseName -1 'first db connect string'
-2 'second db connect string'
-t 'table name'
-c 'config file name'
-v print out verbose info
-p pageout if out of sync or errors

example1:
$BaseName -1 'VXML4RBC:dtuser:passwd' \\
-2 'VXML3RBX:dtuser:password'\\
-t 'dtuser.CR_TARGET' \\
-v -p
example2:
$BaseName -c 'compareDB.cfg'\\
-t 'dtuser.CR_TARGET' \\
-v -p
EEOF
exit (1);
}

# -------------------------------------------------------------------------

my %opts;
getopts('1:2:c:t:vp', \%opts) || Usage();

if (defined($opts{c})){
# Got the config file then check if it's valid
require "$dirName/$opts{c}";
if (!defined ($DBCONSTR1) || !defined ($DBCONSTR2)){
print "The config file needs to define DBCONSTRs\n";
Usage();
}
}elsif (!defined($opts{'1'}) || !defined($opts{'2'})
|| !defined($opts{t}) ){
print "Error: parameter(s) missing. Pls refer to usage.\n";
Usage();
}else {
$DBCONSTR1 = $opts{'1'};
$DBCONSTR2 = $opts{'2'};
}

my $bVerbose;
$bVerbose = (defined $opts{v});
my $PAGEOUT = (defined $opts{p});

my ($db1,$usr1,$pswd1) = split (/:/,$DBCONSTR1);
my ($db2,$usr2,$pswd2) = split (/:/,$DBCONSTR2);
if (!defined($db1) || !defined($usr1) || !defined($pswd1)){
print $opts{'1'} . " is not valid, must be database:username:password format\n";
Usage();
}
if (!defined($db2) || !defined($usr2) || !defined($pswd2)){
print $opts{'2'} . " is not valid, must be database:username:password format\n";
Usage();
}

my ($tableName) = $opts{t};

sub Main() {

my $sReturn; #return string when cmd succeed
my ($nMax,$nMin,$a,$cnt,$i,$cnt1,$cnt2);
Execute("db2 connect to $db1 user $usr1 using $pswd1");
$sReturn = DB2("select * from $tableName");
$cnt = 0;
for $i (split(/\n/,$sReturn)){
$cnt++;
#print "$cnt: $i\n";
$a = md5_hex($i);
#print "$cnt: $a\n";
$hData{$a} = "$db1:$i";
}
$cnt1=$cnt;
Execute("db2 connect reset");
Execute("db2 terminate");

Execute("db2 connect to $db2 user $usr2 using $pswd2");
$sReturn = DB2("select * from $tableName");
$cnt = 0;
for $i (split(/\n/,$sReturn)){
$cnt++;

$a = md5_hex($i);
print "$cnt: $a" if ($bVerbose);
if (!defined ($hData{$a})){
print "<=== Not there" if ($bVerbose);
$hData{$a} = "$db2:$i";
}else {
#if that key is in both side, remove it from the hash
delete ($hData{$a});
}
print "\n" if ($bVerbose);
}
$cnt2=$cnt;
Execute("db2 connect reset");
Execute("db2 terminate");

#Check if hData has any keys, if it has that means outofsync
my $cntHash = scalar keys(%hData); #cntHash should be 0 if array is empty
if ($cntHash > 0) {
print "For $db1 and $db2, $tableName has $cntHash rows out of sync. Check $program\n";
foreach $i (keys(%hData)){
print $hData{$i}."\n";
}
if ($PAGEOUT){
PageOut("$db1 and $db2,$tableName has $cntHash rows out of sync. Check $program");
}
}else {
if ($bVerbose) {
print "1st DB: $db1 $tableName ($cnt1 rows), 2nd DB: $db2 $tableName ($cnt2 rows)";
print " are in sync.\n";
}
}
exit(0);
}

Main();

exit(0);
#THE END



# -------------------------------------------------------------------------
# String Execute(String $cmd);
# @para String $cmd
# throw exception when failed
# otherwise return the command output
# -------------------------------------------------------------------------
sub Execute{
my ($cmd) = @_;
my $sReturn;
eval {
$sReturn = RunCmd("$cmd");
};
if ($EVAL_ERROR) {
onError($EVAL_ERROR);
exit(1);
}

return $sReturn;
}

# -------------------------------------------------------------------------
# String DB2(String $cmd);
# @para String $cmd
# throw exception when failed
# otherwise return the command output
# -------------------------------------------------------------------------
sub DB2{
my ($cmd) = @_;
my $sReturn;
eval {

$sReturn = `db2 -x \"$cmd\" 2>&1`;
# $? == 1 or 256 normally means select statement with no return rows
# print "return: $? $sReturn\n";
if ($? > 1 && $? != 256) {
#something wrong
croak ($sReturn);
}
return $sReturn;
};
if ($EVAL_ERROR) {
onError($EVAL_ERROR);
exit(1);
}

return $sReturn;
}

# -------------------------------------------------------------------------
# void onError($error)
# Param: $error is the error message,
# -------------------------------------------------------------------------
sub onError{
my ($error) = @_;
print("ERROR: $error\n");
if ($PAGEOUT){
PageOut($error);
}
}

# -------------------------------------------------------------------------
# void PageOut($erro)
# Param: $error is the error message,
# -------------------------------------------------------------------------
sub PageOut{
my ($error) = @_;
`echo "CRITICAL NCC $$ NA NA $error"| logger -t NCC -p crit`;
}

# -------------------------------------------------------------------------
# String RunCmd(String $cmd);
# @para String $cmd
# throw exception when failed
# otherwise return the command output
# -------------------------------------------------------------------------
sub RunCmd {
my ($cmd) = @_;
if (!defined ($cmd)) {
croak ("RunCmd: not defined cmd to run.");
}
my $output = `$cmd 2>&1`;
if ($?) {
#something wrong
croak ($output);
}
return $output;
}更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 如果想比较位于不同servers上的DB2数据库里的两个表中的数据异同, 用什么方法比较好? 谢谢!
    • dump them two 2 txt file. sort them. compare use command "diff".
      • 谢谢。 可是数据是保密的, 不经允许,我是不可以download到本地机的。所以只有想别的方法了。~~
        • ask your DBA to create a nickname for the table in the remote database. Then do the outer join between local table and the nickname.
          • 谢谢!除此之外, 还有别的办法吗,如果不想麻烦DBA?
            • join the two table. with where clause like: tb1.pk=tb2.pk and ((tb1.col1 <> tb2.col1) or (tb1.col1 <> tb2.col1) .....)
              • 谢谢你。 我是想说不想麻烦DBA产生nickname(涉及到一些管理流程), 而我又没有权限。查看网上有些工具可以用来比较两个位于不同服务器上的表。 所以想上来问问有没有DX用过这样的工具?哪家的比较好?
        • to /tmp
    • how old are you?
      怎么老是你?
      • 如果我以前没有碰到这种情况,真的不能问吗?:-)
        • 以前好像有人问过这个问题,回答还比较热闹,你查历史帖吧。
          • 谢谢;)
    • 比较两个集合的最有效简洁的办法就是用MINUS; 为了在一个SQL中访问两张表你必需把远程表映射到本地, DBA应该有义务协助你完成
    • 西双版纳?
    • 写个简单的script
      本文发表在 rolia.net 枫下论坛我有2个server, 11个table 都是要保证一样的。就是两两一定要一致。如果映射的话太麻烦
      用一个script, 读出2个table, 用MD5 hash 一下,比较下异同然后打印出哪行在哪个database中有出入
      很粗糟不过可以参考下。

      #!/usr/bin/perl
      use FindBin;
      use lib "$FindBin::Bin/lib";
      use strict;
      use File::Basename;
      use Time::Local;
      use Getopt::Std;
      use Carp;
      use English;

      use vars qw($DBCONSTR1 $DBCONSTR2);

      use Digest::MD5 qw(md5 md5_hex md5_base64);

      $| = 1 ;

      # The hash we use to compare 2 dbs
      my %hData;

      my $program = $0;

      # -------------------------------------------------------------------------
      my $BaseName = basename ($0);
      my $dirName = dirname ($0);

      sub Usage {
      print <<EEOF;
      Usage: $BaseName -1 'first db connect string'
      -2 'second db connect string'
      -t 'table name'
      -c 'config file name'
      -v print out verbose info
      -p pageout if out of sync or errors

      example1:
      $BaseName -1 'VXML4RBC:dtuser:passwd' \\
      -2 'VXML3RBX:dtuser:password'\\
      -t 'dtuser.CR_TARGET' \\
      -v -p
      example2:
      $BaseName -c 'compareDB.cfg'\\
      -t 'dtuser.CR_TARGET' \\
      -v -p
      EEOF
      exit (1);
      }

      # -------------------------------------------------------------------------

      my %opts;
      getopts('1:2:c:t:vp', \%opts) || Usage();

      if (defined($opts{c})){
      # Got the config file then check if it's valid
      require "$dirName/$opts{c}";
      if (!defined ($DBCONSTR1) || !defined ($DBCONSTR2)){
      print "The config file needs to define DBCONSTRs\n";
      Usage();
      }
      }elsif (!defined($opts{'1'}) || !defined($opts{'2'})
      || !defined($opts{t}) ){
      print "Error: parameter(s) missing. Pls refer to usage.\n";
      Usage();
      }else {
      $DBCONSTR1 = $opts{'1'};
      $DBCONSTR2 = $opts{'2'};
      }

      my $bVerbose;
      $bVerbose = (defined $opts{v});
      my $PAGEOUT = (defined $opts{p});

      my ($db1,$usr1,$pswd1) = split (/:/,$DBCONSTR1);
      my ($db2,$usr2,$pswd2) = split (/:/,$DBCONSTR2);
      if (!defined($db1) || !defined($usr1) || !defined($pswd1)){
      print $opts{'1'} . " is not valid, must be database:username:password format\n";
      Usage();
      }
      if (!defined($db2) || !defined($usr2) || !defined($pswd2)){
      print $opts{'2'} . " is not valid, must be database:username:password format\n";
      Usage();
      }

      my ($tableName) = $opts{t};

      sub Main() {

      my $sReturn; #return string when cmd succeed
      my ($nMax,$nMin,$a,$cnt,$i,$cnt1,$cnt2);
      Execute("db2 connect to $db1 user $usr1 using $pswd1");
      $sReturn = DB2("select * from $tableName");
      $cnt = 0;
      for $i (split(/\n/,$sReturn)){
      $cnt++;
      #print "$cnt: $i\n";
      $a = md5_hex($i);
      #print "$cnt: $a\n";
      $hData{$a} = "$db1:$i";
      }
      $cnt1=$cnt;
      Execute("db2 connect reset");
      Execute("db2 terminate");

      Execute("db2 connect to $db2 user $usr2 using $pswd2");
      $sReturn = DB2("select * from $tableName");
      $cnt = 0;
      for $i (split(/\n/,$sReturn)){
      $cnt++;

      $a = md5_hex($i);
      print "$cnt: $a" if ($bVerbose);
      if (!defined ($hData{$a})){
      print "<=== Not there" if ($bVerbose);
      $hData{$a} = "$db2:$i";
      }else {
      #if that key is in both side, remove it from the hash
      delete ($hData{$a});
      }
      print "\n" if ($bVerbose);
      }
      $cnt2=$cnt;
      Execute("db2 connect reset");
      Execute("db2 terminate");

      #Check if hData has any keys, if it has that means outofsync
      my $cntHash = scalar keys(%hData); #cntHash should be 0 if array is empty
      if ($cntHash > 0) {
      print "For $db1 and $db2, $tableName has $cntHash rows out of sync. Check $program\n";
      foreach $i (keys(%hData)){
      print $hData{$i}."\n";
      }
      if ($PAGEOUT){
      PageOut("$db1 and $db2,$tableName has $cntHash rows out of sync. Check $program");
      }
      }else {
      if ($bVerbose) {
      print "1st DB: $db1 $tableName ($cnt1 rows), 2nd DB: $db2 $tableName ($cnt2 rows)";
      print " are in sync.\n";
      }
      }
      exit(0);
      }

      Main();

      exit(0);
      #THE END



      # -------------------------------------------------------------------------
      # String Execute(String $cmd);
      # @para String $cmd
      # throw exception when failed
      # otherwise return the command output
      # -------------------------------------------------------------------------
      sub Execute{
      my ($cmd) = @_;
      my $sReturn;
      eval {
      $sReturn = RunCmd("$cmd");
      };
      if ($EVAL_ERROR) {
      onError($EVAL_ERROR);
      exit(1);
      }

      return $sReturn;
      }

      # -------------------------------------------------------------------------
      # String DB2(String $cmd);
      # @para String $cmd
      # throw exception when failed
      # otherwise return the command output
      # -------------------------------------------------------------------------
      sub DB2{
      my ($cmd) = @_;
      my $sReturn;
      eval {

      $sReturn = `db2 -x \"$cmd\" 2>&1`;
      # $? == 1 or 256 normally means select statement with no return rows
      # print "return: $? $sReturn\n";
      if ($? > 1 && $? != 256) {
      #something wrong
      croak ($sReturn);
      }
      return $sReturn;
      };
      if ($EVAL_ERROR) {
      onError($EVAL_ERROR);
      exit(1);
      }

      return $sReturn;
      }

      # -------------------------------------------------------------------------
      # void onError($error)
      # Param: $error is the error message,
      # -------------------------------------------------------------------------
      sub onError{
      my ($error) = @_;
      print("ERROR: $error\n");
      if ($PAGEOUT){
      PageOut($error);
      }
      }

      # -------------------------------------------------------------------------
      # void PageOut($erro)
      # Param: $error is the error message,
      # -------------------------------------------------------------------------
      sub PageOut{
      my ($error) = @_;
      `echo "CRITICAL NCC $$ NA NA $error"| logger -t NCC -p crit`;
      }

      # -------------------------------------------------------------------------
      # String RunCmd(String $cmd);
      # @para String $cmd
      # throw exception when failed
      # otherwise return the command output
      # -------------------------------------------------------------------------
      sub RunCmd {
      my ($cmd) = @_;
      if (!defined ($cmd)) {
      croak ("RunCmd: not defined cmd to run.");
      }
      my $output = `$cmd 2>&1`;
      if ($?) {
      #something wrong
      croak ($output);
      }
      return $output;
      }更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • 不会吧?一个SQL能做的事要写这么大一个SCRIPT? 你考虑过MD5的开销吗?这是按顺序比较吧?如果顺序不同或跳过一行呢?
        • 确实不是按顺序比较,是把表1的HASH全部放入内存,这方法只对小表适用。
          • 是效率不高,只对简单的小表,不过如果可以用PK之类的做hash的key,就不用用整个row内容作键值了。LZ说不能用create nickname的,你建议的映射她不能采用
            • 别的方法都不可避免地要把数据读取到客户端,按楼主的说法同样是违规的。
    • ETL一下
    • 有趣啊,一个developer又没下载数据的许可,又没有DBA的支持要比较两个表的差异--又没锅又没灶怎么做饭啊!