Ordinary Oracle Joe

Just an ordinary DBA's thoughts

Archive for April, 2010

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.


Posted in Migration | Tagged: , , | 3 Comments »