Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-25
10
Medium Priority
?
5,193 Views
Last Modified: 2012-06-27
Hi,
I need to compare two tables with identical structure on TWO DIFFERNET SERVERS (and different databases as well) and display the rows with different values. database is Sybase

Thanks
0
Comment
Question by:kuldeep_bhayana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 750 total points
ID: 12408560
You have two main options here.

(1) Do it in the operating system, or
(2) Do it in Sybase.

The first might be easier to do. This is where you'd use the standard Sybase tool "bcp" to copy both the tables to a file, then copy/ftp one of the files to the other box, then use operating system tools (like "diff" or "sdiff" on UNIX) to do the compare for you.

The second will be more powerful and flexible, but be a lot more complicated to setup. It has two stages - first you have to set things up so you can view tables in one server from the other, then you have to write SQL to do the compare.

Sybase (from 11.5 onwards) ships with the ability to view tables in other Sybase servers via "CIS" (Component Integration Services) - this is also called "proxy tables". Your best bet here is to look at the "Component Integration Services User's Guide", but very briefly:

- add entries for each server to the other server's interfaces file using "dsedit" (run from the operating system)
- add entries for each server to the other server using "sp_addserver" within ASE
- associate a login in one server with a login in the other using "sp_addexternlogin" within ASE
- setup a "proxy table" in one server that points to the table in the other using "create proxy_table" (if on ASE 11.9.2+, if still using 11.5 you'll need to do this in two steps with "sp_addobjectdef" and "create existing table")

That will give you what looks like a local copy of the remote table.

You can then write SQL to compare the tables. This would depend on what comparison you want - are you only looking for the same primary key values, or do you want to check for exact duplicate rows?

If you've never used CIS before then it will be simpler to just bcp the tables, but again that depends on what type of comparison you want to do.

Good luck!
0
 
LVL 1

Author Comment

by:kuldeep_bhayana
ID: 12408733
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
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12410008
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!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:kuldeep_bhayana
ID: 12418541
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
0
 
LVL 1

Author Comment

by:kuldeep_bhayana
ID: 12418667
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
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12418742
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!
0
 
LVL 1

Author Comment

by:kuldeep_bhayana
ID: 12529458
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

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12530151
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->
0
 
LVL 1

Author Comment

by:kuldeep_bhayana
ID: 12531019
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



0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12532097
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here in this article, you will get a step by step guidance on how to restore an Exchange database to a recovery database. Get a brief on Recovery Database and how it can be used to restore Exchange database in this section!
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question