Sybase to Oracle #1

Following on from my initial post I thought it might be worth fleshing out some of the steps I went through in bitesize chunks. This is in no way attempting to replace the already superb documents out there (I’ll provide links to them at the end of the post). It’s more of a list of the steps I went through, and the challenges I faced. I’m sure everyone does it slightly differently.   

I’ll summarise the whole process I went through before describing the first 6 steps in a bit more detail.   

1. Download and install Oracle’s SQL Developer.
2. Install 3rd Party JDBC Driver to enable direct connection to Sybase database
3. Create a migration repository in an oracle instance
4. Connect to the Sybase database
5. Capture the Sybase database
6. Set the data mapping
7. Set migration preferences
8. Convert the captured Sybase model to Oracle
9. Generate the Oracle DDL
10. Generate the Data Migration scripts
11. Modify the Oracle DDL as required
12. Modify the Data Migration scripts as required
13. Run the Oracle DDL into the destination database
14. Run the BCP export scripts against Sybase
15. Move the exported data over to the Oracle host
16. Run the SQLLDR scripts to import the data into Oracle

NOTE : Steps 11 – 16 went through a number of iterations before I was happy with the results.   

1. Download and install Oracle’s SQL Developer   

The first step was installing Oracle’s SQLDeveloper. It’s really just a case of downloading and running the .exe. The download can be found here.   

As discussed before, this tool comes with the migration options you need to go from Sybase to Oracle. However, it doesn’t enable you to connect directly to the Sybase database without installing an additional JDBC driver. This means that you would have to run a bunch of 3rd party offline scripts against the Sybase db to capture the ddl. We did try this but frankly, it’s a little painful.   

2. Install 3rd Party JDBC Driver to enable direct connection to Sybase database   

The easier option is to install the JDBC driver that allows you to connect directly to sybase. The JDBC driver can be found here. Once this has been successfully downloaded go into Tools -> Preferences -> Database -> Third Party JDBC Drivers and Add a new entry pointing to the downloaded jtds-1.2.4jar file. (see below).   

Adding the 3rd Party JDBC Driver

This will then enable you to create new connection directly to a Sybase database (also SQLServer).   

3. Create a migration repository in an oracle instance   

However, before jumping in with both feet you first need to create a repository to hold the meta data of the database you’ll be migrating. This needs to be created in an oracle database. To do this connect to the oracle db that you’d like to create the repository in, then on the main menu click on Migration -> Repository Management -> Create Repository.   

Once you’ve done this you’re free to start clicking.   

4. Connect to the Sybase database   

To do this click on the New Connection button on the Connections tab. You should now see tabs for Access, SQLServer and Sybase as well as Oracle. Click on the Sybase tab and you’ll see the usual boxes for Connection Name, Username and Password. In addition you’ll see Hostname and Port boxes.   

If you enter the appropriate details for these 5 parameters you can then click on the Retrieve Database button. This will populate a drop down list of all the available Sybase databases on this host and port number. Select the one you wish to connect to and then save the connection and connect to the db.   

5. Capture the Sybase database   

Now that you’ve created your Sybase connection you can right hand click on it and select ‘Capture Sybase’. That’s it. You should get a pop-up box displaying the progress of the capture. Once it completes You should see an additional window below the normal Connections box called Captured Models. In the Captured Models box you should have a single entry named something like 

<username>\<sybase_db_name>(Sybase<version>)<datetime> 

By right hand clicking on the captured model you can choose to   

• Convert to Oracle Model
• Delete Captured Model
• Rename the Model
• Set Data Mapping

NOTE: To capture a Sybase database you must have an existing open connection to your Oracle database containing your repository.   

6. Set Data Mapping  

I touched on this in my original post, so I won’t repeat myself (too much!) here. Basically you get to choose which Sybase data types map to their equivalent Oracle ones.

The biggest thing to note (in my opinion) is that the Sybase DateTime type seems to hold time down to microseconds, where as the Oracle Date type (the default mapping) doesn’t. If your application requires time precision to less than a second you’ll need to change this to Oracle type TIMESTAMP[6] in the mapping window. There are some other changes to the defaults that you may wish to look at, particularly around the default mapping of Sybase types VARCHAR[MAX], VARBINARY[MAX] and VARBINARY.  

I think this ‘bitesize’ chunk is in danger of becoming more than a mouthful so I’ll stop there.

References

As promised here is the link to Oracle’s own document on using this tool set. 

http://www.oracle.com/technology/tech/migration/workbench/files/omwb_getstarted.html#conf