(SYBASE) Compare two tables with identical structure on TWO DIFFERNET SERVERS

Hi Joe,

Thanks for invaluable input. CIS looks to be promising. Can you please elaborate more upon this as I have never used this utility before. I dont know where to start. I can compre the data as and when the tables are available but as of now clueless.

Thanks

Hi,

You don't say which version of Sybase you're on, so I'll point you at resources for the latest and greatest (12.5.x). There haven't been any relevant changes to CIS since 11.9.2 so it should work in most versions.

The ASE 12.5 CIS User's Guide is at
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/omni_ug

(It used to be a separate product called "OMNIConnect", hence the "omni" in the URL there.)

Chapter 2 is probably the best place to start. Specific commands refered to there are well documented in the ASE Reference Manual, available at
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__BookView

I've pretty much covered the main steps in my earlier entry :

- update the interfaces file (or SQL.INI on Windows) of both servers using 'dsedit'
- run 'sp_addserver [ASE1]' in ASE2 and vice versa
- run 'sp_addexternlogin [ASE1], sa, sa' in ASE2 and vice versa (this maps the 'sa' logins to each other, and assumes they have the same password - read up on sp_addexternlogin for more details if those assumptions aren't valid)
- run 'create proxy_table [remote_table] at "[ASE2].[database].[owner].[remote_table]" ' in ASE1

You can now query remote_table as though it were local to the database you ran "create proxy_table" in.

Between the CIS User Guide and the Reference Manual, that should be enough to get you going. Let us know if it gives you any problems, and good luck!

Hi,

What is supposed to be updated in the interface file. i can run dsedit and in the window available i can see some configurations for the servers. please let me know what is supposed to be updated there.

Thanks

Further I have one server with 11.03 version and other 11.92. I have tried to run 'sp_addserver SERVERENAME' on both the servers without touching the interfaces files. It says you should have the appropriate user role. (Error 567 on line 58) whereas i am using the administrator login. And it seems i can run sp_addserver on both the servers. but can not use sp_configure "enable cis" on the server with 11.03. Can you please help me to

1. How to update the interfaces file. (The system that houses Sybase is SunOS and i use terminal server to connect to server)

2. How do i cope with the different versions of the two servers involved.

Thanks

Answering your questions in reverse order:

In terms of coping with the mixed versions, CIS is not built-in to 11.0.3. You'll have to do everything in 11.9.2. (This is why sp_configure "enable cis" fails in 11.0.3 - it doesn't exist in that version.)

Updating the interfaces file - dsedit is the standard GUI tool (it will require xterm capability). Basically it's like /etc/hosts for Sybase - it lists each Sybase server by name and gives the network address for how to talk to it. Depending on which version of Sybase and Sun Solaris you're running, the exact contents will be different. It's probably easier just to point you to the ASE Utility Guide for Sun Solaris which documents dsedit (which needs an xterm) and dscp (which doesn't).

You'll need to use sybinit to update the interfaces file for 11.0.3 - dsedit didn't exist back then!

http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/util

(That's the ASE 12.5 version - dsedit will work the same in 11.9.2, but not in 11.0.3, you'll have to read up on sybinit there.)

Updating the interfaces file is exactly what you'd do when you wanted to have the Sybase client installed on this box be able to talk to a new Sybase server somewhere else. (In fact, when using CIS, the local ASE acts as a client to the remote ASE.) "Updating the interfaces file" means add an entry for the server, with its address. Don't edit the file directly as there are very strict formatting requirements which are easily broken.

If sp_addserver is giving you permissions errors, you don't have all the permissions. Sybase distinguishes between "sa_role" and "sso_role" (for System Security Officer). sp_addserver needs "sso_role". Are you using the built-in "sa" login?

With 11.0.3 in the mix, your steps become:

1) Update the 11.9.2 interfaces file with a new entry for the 11.0.3 server using dsedit or dscp
2) Update the 11.0.3 interfaces file with a new entry for the 11.9.2 server using sybinit
3) (in 11.9.2) sp_configure "enable cis", 1
4) (in both) sp_addserver [other server name] (after ensuring you have sso_role)
5) (in 11.9.2) sp_addexternlogin [11.0.3 server], [local login], [remote login] (, [remote password only if different to the local one)
6) (in 11.9.2) create proxy_table remote_table at "[11.0.3 server].[database].[owner].[remote table]"

(This is why I said it might be simpler to just bcp the files and use diff or sdiff 8->)

Good luck!

Hi,

in my application, i have two tables not similar in structure on two servers say ps_job and
ps_job1 i need to compare three common filed values say company_code, satatus, confirmation_date field value in the both tables and write to another table if any changes were found along with the employee code. Can you please help?

thanks

Have you got CIS access to the remote table working?

If you're asking what SQL would achieve this, assuming you have got CIS working so the tables appear to be two local tables, that should probably be a separate question.

How will you be comparing the tables? Do they have the same primary key with values you trust? i.e. does the same row have the same primary key in both tables? This is a notorious problem in comparing data between different systems...

Are you expecting the same number of rows in both tables? i.e. what happens if one has a row that doesn't appear in the other?

If you can guarantee the same number of rows with the same primary keys, then this is fairly simple... something like:

select A.pk, A.a, A.b, A.c, B.pk, B.a, B.b, B.c
from psjob A, psjob1 B
where A.pk = B.pk
and (A.a <> B.a or A.b <> B.b or A.c <> B.c)

But I suspect you can't assume the PK values are identical.

We'd probably need to see the table structures, including keys, to advise you better. And if you ask this as a new question, more people are likely to look at it. 8->

Hi,

Thanks again for responding so quickly. I got CIS working. However there is still something I am wondering about. I am using cursor to fetch data. this is working fine, but the tables contain millions of records and it takes a pretty long time to get it going. Is there anyway we could make it work without using cursors. If that is possible that would make the things little quick.

The tables are not identical but contain few common fields and the match is required to be done on these columns only. The discrepancies are to be logged in a temp table. there is a possibily that the number of rows might be different. The job is to reconcile the database tables, one of which is updated by a third party application and another is being maintained inhouse.

I have developed a solution using cursors and then comparing individual data items. this takes around 3-4 hours. can it be done some other way.

The structure for reference can be taken as below. I am giving here the common fields:

table1:

emplid
empl_rcd# t
effdt
empl_seq
empl_status
paygroup
company
........
......

table2:

emplid
empl_rcd# t
effdt
empl_seq
empl_status
paygroup
company
........
......

Primary key: emplid + empl_rcd# + effdt + empl_seq
Fileds to be reconciled: company and paygroup for each employee id

If we trust the primary keys, then using the names you've given ("empl_rcd#" can't be a column name):

SELECT   T1.emplid, T1.empl_rcd#, T1.effdt, T1.empl_seq, T1.company, T1.paygroup, T2.company, T2.paygroup
INTO       #some_table
FROM      table1 T1, table2 T2
WHERE    T1.emplid = T2.emplid
AND         T1.empl_rcd# = T2.empl_rcd#
AND         T1.effdt = T2.effdt
AND         T1.empl_seq = T2.empl_seq
AND         (
                T1.company <> T2.company
                OR
                T1.paygroup <> T2.paygroup
                )

This will still be fairly slow, especially as at least one of the tables are going through CIS, but this should still beat a cursor.

While I haven't tested this, it's the same logic as the code fragment I posted before, and I *did* test that. 8->

Good luck!