Solved

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

Posted on 2008-10-13
5
1,773 Views
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
0
Comment
Question by:gudii9
5 Comments
 
LVL 19

Accepted Solution

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

Regards,
Bill
0
 
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
go

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.
0
 
LVL 6

Assisted Solution

by:IncisiveOne
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.

0
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 300 total points
ID: 22711659
Joe,

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.

Regards,
Bill
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31505696
thank you all for your help
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to query constraints in Sybase 3 1,456
SyBase SQL Syntax 5 619
SQL select to return the latest row for each vacancyID 17 105
SQL Syntax 10 54
A Short Story about the Best File Recovery Software – Acronis True Image 2017
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

22 Experts available now in Live!

Get 1:1 Help Now