How to merge two Sybase databases into one in a case where a business has acquired another

Well, since there is not Sybase ASE 12.2, I believe you are actually talking about 12.5.2   You can check this by logging into the database with something like ISQL and doing a SELECT @@version.

As far as magic tools to do all this for you, I'm afraid you are out of luck.  The task you have in front of you will range from not to bad to a huge pain in the butt.  It depends primarily on the data model and how the application assigns keys.  The good news is that there are probably no show stoppers but it could get pretty complicated.

The first thing to do is evaluate the data model.  I would get a hold of the Sybase Windows Client installation CD for 12.5.2.  On it you will find a free copy of PowerDesigner Physical Architect.  Install it and then reverse engineer the database into a model.  There you can beging to get a feel for how the beast works.  Things like how much declarative referential integrity is in place and whether the keys are assigned by the application or if it uses identity columns in the database.

From the data model you can also get an appreciation for how the data is organized.  For instance, you want the data from two organizations loaded into the same database.  Do you need them to stay seperate organizations or was this a merger and now everything is going to be in one organization?

This is a fairly significant project you are undertaking.  It is not possible to give you step by step instructions because there are just too many variables.  I can tell you that you are going to have to develop those DBA skills in a big hurry.  My company does this kind of thing all the time.  If you want to talk more in general terms, you can find my contact information in my profile.

BTW, step one is to build a development environment.  You are going to need a machine with enough disk to hold both databases twice plus at least two dumps of each.

Regards,
Bill

Hello Bill, and thanks for your reply.

You are right, its Sybase12.5.2. In Reference to your question, I would want them in the same organization. Right now its two separate servers in two different locations and both instances have different organizations.  

Perhaps, I can start small and simply start with Objects, forward and reverse.

Are you able to provide me a sql script to start comparing?

Also, would DBArtisan help me in any way?

Thanks

Any decent query tool will help.  My guess is that before it is all over with, you are going to be writing a bunch of stored procedures to do the data conversions.

DBArtisan is O.K. though I prefer an older version of RapidSQL Pro.  7.3.4 was the last good version before they decided to rewrite everything and really buggered it up, at least for Sybase folks.

There are also some other choices.  Aquafold Aqua Data Studio is pretty good and just keeps getting better.  It is only $395 instead of $995 for RapidSQL.  You can also get a copy of Sybase Workspace.  I think it is still free until the end of December or you can join the ISUG (International Sybase Users Group) for $99 and get full licensed copies for free.

I have no idea what you mean by Objects, forward and reverse.

I can't give you a magic SQL script or query unless/until I know what the tables and keys look like.  If, by some highly unlikely miracle, all of the primary keys contain the ORGANIZATION field, you will not have any collisions.  Otherwise, you can be pretty well assured that there will be problems.  Most well designed databases use IDs on each record; integer or numeric fields that uniquely identify each row in the table.  As such, I cannot imagine you won't have duplicates.  What is more, if they are identity columns (auto-number), you have to pull some tricks in order to assign new ones and get them into the target tables.

Also, you can't do any SQL on the data until you get it all in one place.  Again, you have to start with a development environment, create two databases, one for org-A and one for org-B, dump the production databases and restore them onto the development DBs.  Then you can write queries to see where you key collisions are since you can refer to tables from different databases (in the same server of course) in a single query.

For instance....(snippet 1)  This will give you a list of the rows in TableX that have the same primary key.  Now that may be way more information than you can swallow; you may just want the count.  For that, see snippet 2...

Of course, all this gets more complicated when you have composite primary keys.  You may also have both IDs and Unique Business Keys on the same tables.  Either one or neither of them may be defined as the primary key.  In fact, any unique index in the database schema needs to be evaluated.

Of course, none of this can be done or makes any sense without first looking at the data model.  You really need to get a copy of PowerDesigner 10 and reverse engineer it before you even know what to start looking at.

Regards,
Bill

SELECT < Identifying information from both tables >
  FROM ORGA.dbo.TableX AX
  JOIN ORGB.dbl.TableX BX
    ON AX.PrimaryKeyField = BX.PrimaryKeyField
 
SELECT AX.PrimaryKeyField PKF
  FROM ORGA.dbo.TableX AX
UNION ALL
SELECT BX.PrimaryKeyField
  FROM ORGB.dbl.TableX BX
GROUP BY PKF
HAVING COUNT(*) > 1

Select allOpen in new window

Hello, Excellent info Sir. You referenced Snippet 2? I dont seem to see it. Just Snippet 1 ( unless its combined and I did not see it :)

If I get you the Schema, could you see if this is even possible?

Perhaps rather than trying to merge the whole database.. I can start with a step by step and start with IP addresses that contain Host names and reverse records, reverse records are IP to host and fwd is name to IP.

I also need to query if there are any resource records that reference the above and if they any way conflict with either database.

I know these terms maybe more than you need to know, but perhaps it may shed some light.

I looked you up and sent you an email, perhaps I can send you the schema there?

I really appreciate your help ( or anyone's help for that matter) as this is high level and I really need guidance.

Thanks

Hello,

great, I will email it to you rather than post it.

Lets touch base later in the evening Monday

Regards,