how to import sybase data dump in to oracle or msaccess to see the data structure and data

can you pl tell me how to import sybase data dump in to oracle or msaccess to see the data structure and data.I just want to do it to get idea about the table structure and the data before starting actual migration work from sybase 12.0 oracle latest version.Is there are any free automated tools available to migrate between sybase12.0 to oracle latest version.Thanks in advance
Who is Participating?
grant300Connect With a Mentor Commented:
The short answer is, you can't.  The Sybase database dump alone is worthless; you have to have a running Sybase ASE server in order to do anything with the dump.  Same rules as you have with Oracle.  After all, you can't take an Oracle dump and do anything with it without running Oracle.

12.0 is also a rather old version of Sybase so your client/employer will have to supply you with a copy of it so you can install ASE, restore the dump, and then start looking at the migration effort.

BTW, in order to restore the dump, particularly with version 12.0, you really need to know the device and segment layout that the dump was taken from.  There are tools in later version to help you do this so you have have a hope of restoring a dump when the original installation is lost or forgotten.

I don't know if the dump analysis tool in later versions, e.g. 15.0.2 will work against 12.0 dumps or not.  It might be worthwhile to download the ASE 15.0.2 developers edition and give it a try.  You can then configure a server and devices and restore the dump into 15.0.2  Just a thought.

Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Supporting Bill's answer:

You don't *have* to know the details of the original database that was the source of the dump. You *do* need an ASE installed and configured, although it need not be an exact match for the original version. ASE 12.5.4 would do.

You could install ASE 12.5.x, then run

load database model
from "[path and file]"
with headeronly

Note the "model" database is a tiny built-in database in every ASE. We don't care that it's too small as "with headeronly" means "just give me the basic dump header details". That will include the database size. We don't care that you aren't using ASE 12.0 as you can load a 12.0 dump into 12.5.x. (I am pretty sure you can even load it into ASE 15.0.)

Once you know the size, you can create a simple database of that size, a single fragment of mixed data and log. (ie. "create database [...] on [device]=[size]"). When you load the dump into that, the dump will impose it's own segment mapping on the single fragment, ie. it will now be split into multiple fragments of data and/or log. From this you'll be able to figure out what the original database looked like, drop your current one, and then recreate it in the proper way.

But yes, bottom line, a Sybase dump can only be used to load into a Sybase install.
IncisiveOneConnect With a Mentor Commented:
If you create a Sybase server to load your 12.0 dump into, you need to ensure it is 12.5.3 or greater, since that is when Cross Platform Dumps (loading older dbs, and loading db dumps on a platform other than which it was dumped, was supported.  A small upgrade step will be automatically performed, and there are certain checking routines which should be run, which you can probably set aside due to your circumstance.  For ASE 15.0, the upgrade step is large.

What you need to move an entire database, either from the current Sybase server, or from one you have build in order to load the dump, to anything else, is:

1  Migrate Definition (DDL):
ddlgen (one of the client-side binaries, it can be run on any client that Sybase client was installed on; not just the server)).  This will extract the DDL used to create all the objects in the db.  Change T-SQL to PL/SQL and run that (or portions thereof) against your oracle server/schema.

1.1  If you do not like ddlgen (command line) , you can use any no of GUI Data Modelling tools, PowerDesigner, ERStudio, ERwin.  It is a lot easier, and you may save time resolving the differences once you get the db migrated.  Most of these have a "migrate" facility that's let's you migrate an entire db definition from (eg) Sybase to Oracle; they take care of the version diffs.  They do not migrate the data ...

2 Migrating the actual data:
bcp-out (another client-side binary) in character mode (the default) all the tables you need.  Import them into oracle.

grant300Connect With a Mentor Commented:

Clever procedure but what a pain in the butt.  It is good to know it is possible though.

I have had good success using PowerDesigner to migrate data models between database platforms.  It won't fix everything but it gives it a pretty good shot.

The difficulty rating of what you are trying to do depends greatly on what features of Sybase have been used.  For instance, Identity columns are not supported in Oracle; you have to use Sequences.  Native data types are also not supported so previously efficient data types like INT get turned into the Oracle internal number format; an ugly thing native to no platform.

Stored procedures and triggers can take anywhere from syntax re-write to a complete re-engineering of the application code and modifications to the data model.  Oracle does not, for instance, support a trigger that modifies the table that fired the trigger.  They call this a "mutating trigger".  This kind of thing is done routinely in Sybase and other databases for that matter.

These are just a taste of the things you are likely to run into doing a migration.  Bottom line, if your application has any complexity at all, this is a much bigger job than just moving the table definitions and data.

Oracle has some tools to help with the migration but I don't know if they are free or not.  You may have to buy services in order to get them.  Check with the Oracle website and see what you can find there.

gudii9Author Commented:
thank you all for your help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.