Currently Being Moderated

simple script to compare 2 tables

There are 3 common methods to compare 2 tables.

1. table row count

2. bcp out the table data and compare the file size

3. RS utility rs_subcmp

 

The simple script please download the attach file.

(The script is for unix or linux)

 

Here's how it works:

 

1)vi the diff2table.sh, edit the variables,

for example:

<method 1>

### input Environment Variables ###

PDS=ASE1570

RDS=BSE1570

RSUSER=sa

RSPAWD=sybase

DB=pubs2

### end of user input varibles ###

 

<method 1>

1)exec the script

./diff2table.sh

 

2)chooes 1 and enter table name

# data compare #################

  1. count(*)

  2. bcp out and check file size

  3. sybase rs_subcmp

##### press anykey to exit #####

1

Enter table name...

aaa

 

3)row count come out

ASE1570 =          10 rows

BSE1570 =          2 rows

 

<method 2>

1)exec the script

./diff2table.sh

 

2)chooes 2 and enter table name

# data compare #################

  1. count(*)

  2. bcp out and check file size

  3. sybase rs_subcmp

##### press anykey to exit #####

2
Enter table name...
aaa
use bcp out and diff MAY cause the working directory run of of space
consider 6x of table space as working dir
enter the working directory...(/tmp)


Starting copy...

10 rows copied.
Clock Time (ms.): total = 12  Avg = 1 (833.33 rows per sec.)

Starting copy...

2 rows copied.
Clock Time (ms.): total = 8  Avg = 4 (250.00 rows per sec.)
Sorting...
ls result...
-rw-r--r-- 1 sybase sybase 42 Feb 11 10:49 /tmp/P_D_S__
-rw-r--r-- 1 sybase sybase 11 Feb 11 10:49 /tmp/R_D_S__

 

<method 3 compare only>

1)exec the script

./diff2table.sh

 

2)chooes 2 and enter table name

# data compare #################

  1. count(*)

  2. bcp out and check file size

  3. sybase rs_subcmp

##### press anykey to exit #####

3
Enter table name...
aaa
use subcmp to compares a replicated table to the primary table
this program MAY cause a very log time to complete and ASE's tempdb run out of space

If found different, need to sync?(Y/N)
N
Please input the select command, the command MUST used ORDER to order rows by key
select * from aaa order by a1,a2
Please input primary key column name, key 1=a1
Please input primary key column name, key 2=
Please wait, this WILL take a long time.....

---rs_subcmp resoult---
MISSING ROWS:
a1  a2 
--------
2  2

a1  a2 
--------
3  3

a1  a2 
--------
4  4

a1  a2 
--------
5  5

a1  a2 
--------
6  6

a1  a2 
--------
7  7

a1  a2 
--------
8  8

a1  a2 
--------
9  9

a1  a2 
--------
10  10

ORPHANED ROWS:
a1  a2 
--------
99  99

INCONSISTENT ROWS:
_________Replicate row________
a1  a2 
--------
1  99

__________Primary row_________
a1  a2 
--------
1  1


any key to exit

 

<method 3 compare and sync table data>

1)exec the script

./diff2table.sh

 

2)chooes 2 and enter table name

# data compare #################

  1. count(*)

  2. bcp out and check file size

  3. sybase rs_subcmp

##### press anykey to exit #####

3
Enter table name...
aaa
use subcmp to compares a replicated table to the primary table
this program MAY cause a very log time to complete and ASE's tempdb run out of space

If found different, need to sync?(Y/N)
Y
Please input maintenance user name
maint
Please input maintenance user password
manit_ps

Please input the select command, the command MUST used ORDER to order rows by key
select * from aaa order by a1,a2
Please input primary key column name, key 1=a1
Please input primary key column name, key 2=
Please wait, this WILL take a long time........
---rs_subcmp resoult---
MISSING ROWS:

a1  a2 
--------
2  2

a1  a2 
--------
3  3

a1  a2 
--------
4  4

a1  a2 
--------
5  5

a1  a2 
--------
6  6

a1  a2 
--------
7  7

a1  a2 
--------
8  8

a1  a2 
--------
9  9

a1  a2 
--------
10  10

ORPHANED ROWS:
a1  a2 
--------
99  99

INCONSISTENT ROWS:
_________Replicate row________
a1  a2 
--------
1  99

__________Primary row_________
a1  a2 
--------
1  1


any key to exit

 

<test data>

ASE: ASE1570, BSE1570

database: pubs2

table: aaa [ create table aaa ( a1 int , a2 int ) ]

primary table data: 1,1 2,2 3,3 4,4 5,5 6,6 7,7 8,8 9,9 10,10

replicate table data: 1,99 99,99

Comments

Delete Document

Are you sure you want to delete this document?

Actions