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

Posted on 2008-10-13
Last Modified: 2012-05-05
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
Question by:gudii9
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
LVL 19

Accepted Solution

grant300 earned 300 total points
ID: 22707231
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.

LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 100 total points
ID: 22707730
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.

Assisted Solution

IncisiveOne earned 100 total points
ID: 22708323
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.

LVL 19

Assisted Solution

grant300 earned 300 total points
ID: 22711659

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.


Author Closing Comment

ID: 31505696
thank you all for your help

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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