Ordinary Oracle Joe

Just an ordinary DBA's thoughts

Sybase to Oracle

Posted by oakesgr on April 29, 2010

Around a year ago Sybase lost it’s status as a Strategic RDBMS Platform within my organisation. This left Oracle and MSSQL as the alternatives going forward. As with many financial organisations in the banking sector there is an abundance of Sybase within the company.

I’m working on a project at the moment to convert a Sybase ASE 12.5 database running on Solaris 8 to Oracle on RHEL5.

The basic approach I’ve followed so far is…

  1. Work with the dev and support teams to clean up any unwanted tables
  2. Use the Migration Workbench toolset within SQLDeveloper to
  • capture the data model
  • set the datatype mapping
  • convert to an oracle model
  • create the BCP scripts for exporting from Sybase
  • create the DDL for Oracle
  • create the SQLLDR scripts for importing into Oracle

I then take this set of base scripts and edit them to meet my requirements more closely. For example, I can’t find an option within the SQLDeveloper settings to allow the SQLLDR ctl commands to be created with DIRECT=TRUE.

I also edit the scripts to move certain objects into different tablespaces.

A peculiarity of this particular Sybase db is that all of the character type columns are defined as CHAR not VARCHAR so they are all rpadded. Therefore, as part of the migration I want to remove this padding. My first attempt at doing this relied on a SED script. Whilst being reasonably quick I found that it caused some data integrity issues, to the point where I wasn’t comfortable that I could guarantee no data loss during the migration – obviously a pretty major issue.

With the help of a Sybase dba (cheers Nick) we decided to create views on each table, with RTRIMs around any column that required it. We then edited our BCP scripts to access the views instead of the tables.

This didn’t seem to have any major performance impact on the BCP export, and it had the added advantage of being ‘safe’. I’m willing to trust Sybase’s own RTRIM function a lot more than my SED command.

As far as datatype mapping goes here’s what I decided to go with.

 Nothing too exciting there.

Overall I’ve found this part of SQLDeveloper to be intuitive, well presented and no major flaws. A few extra options on the creation of the BCP, CTL and DDL files would be handy e.g. the ability to specify different tablespaces for tables and indexes, or the ability to specify certain SQLLDR parameters in the CTL files, but overall I’m quite happy with the tool. It does 90% of the work for you, and if you weren’t worried about performance you could probably get away with it doing 95%.

I had to play around a little to fix some characterset issues (thanks to Rob for the help on those) and also the formatting of timestamp fields but other than that there were no noteworthy issues.

UPDATE : This project has now been put on hold. Unfortunately it appears that the data migration was the simplest part of the job. The developers have run into some major issues around the Hibernate implementation. It’s all way above my head, but due to the issues they’re facing and the time that it would take to fix them other projects have been given priority.

Oh well. The hard work won’t have been wasted because I still have all the scripts, plus I learnt something new – which is always good.


3 Responses to “Sybase to Oracle”

  1. Pauline said

    Hello ,

    I am trying to the same process with a very large system .which 48 databases and 1000 of tables . Do you have a script that generate the sqlldr controlfiles ? Anyway I could get a copy ?

    Thanks for what you posted already it has help me with my migration . We are full steam ahead with our project .

    Thanks ,

    Pauline sybase and oracle dba for way too many years !

    • oakesgr said

      Hi Pauline,

      thanks for reading. It’s possible to use SQLDeveloper to generate the base sqlldr controlfiles.

      Once you’ve captured the schema from the sybase database you can right hand click on the captured schema and generate the Oracle schema.

      The easiest way to capture the sybase schema is to connect directly to the sybase database from SQLDeveloper. This can be done by installing an additional JDBC driver and then creating a new connection in the normal way.

      Once you have that converted oracle schema you can right hand click again and pick an option something like ‘Generate Migration Scripts’.

      This will create not only the sqlldr CTL files but also the sybase BCP scripts. Again there are a number of things to play with in the options first… the one that seemed to need a bit of tweeking in my experience was the date or timestamp format to ensure that what was BCP’d out of sybase matched up to the format you had in your CTL files.

      Apologies if this is a little vague. I’m away from my desk today so I’m going off of memory. Once I’m back in the office I’ll put a post together with all the details.


  2. […] Disclaimer The opinions in my posts are mine alone. They do not represent those of my employers (past or present), family members, friends or enemies. I think that's pretty clear. « Sybase to Oracle […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: