Solved

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

Posted on 2004-10-25
4,295 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
Question by:kuldeep_bhayana
    10 Comments
     
    LVL 24

    Accepted Solution

    by:
    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
    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
    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
     
    LVL 1

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    Finding a job can be stressful - searches, resume tweaks, and networking events can be super boring. Luckily we're here to help you land your dream job!
    Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now