In this tutorial I’m going to show you how to migrate your Sybase Adaptive Server Enterprise databases to MySQL.

As usual, we’ll start with a couple assumptions:

• You have MySQL Workbench 6.0 installed.
• You have a running Sybase Adaptive Server Enterprise database somewhere in your network. I’ll be using the pubs3 sample database that Sybase distributes with Adaptive Server Enterprise 15.
• You have installed the Sybase Adaptive Server Enterprise ODBC drivers in the same PC where MySQL Workbench is running. The ODBC drivers are distributed with theSybase Adaptive Server Enterprise SDK and included in the Adaptive Server Enterprise Developer Edition.
• A running MySQL Server instance with proper user access is available and you are able to connect to it from MySQL Workbench. The Migration Wizard supports MySQL versions from 5.1 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.6.12 CE installed in the same PC where MySQL Workbench is running.

Open MySQL Workbench and start the Migration Wizard

You will find the Migration Wizard icon in the sidebar in Workbench’s main screen, under the name “Database Migration”. Click on it to start the Migration Wizard:

A new tab showing the Overview page of the Migration Wizard should appear.

In this tutorial we’ll be using an ODBC DSN connection to connect to our source Sybase Adaptive Server Enterprise database. Click on the “Open ODBC Administrator” and create a new connection under the User DSN tab in the window that appears. Select the Sybase Adaptive Server Enterprise driver and set the parameters for your source connection. Here’s how it looks in my PC:

Set up the parameters to connect to your source database

Back in the Migration Wizard, move to the next page by clicking on the “Start Migration”button. Now select “Sybase ASE” as the Database System and ODBC Data Source as theConnection Method. Then select the ODBC connection that you defined in the previous step from the DSN combo box. Put your user name, password (optional, will be asked later if you don’t set it) and database name (optional, you will be given the option to select it later from a list). You can take a look at this picture to get an idea of what you should have:

When you have entered all the relevant parameters, click on the “Test Connection” button to verify that you can connect to your source database. Once you have successfully tested your connection, move to the next page by clicking on the “Next” button.

Set up the parameters to connect to your target database

Now you have to set the parameters to connect to your target MySQL server. If you have already created a MySQL Workbench connection to it, you can select it from the Stored Connection selector. Remember to test your connection parameters and make sure that you can connect before moving to the next page.

Click on the “Next” button to move to the next page. The Migration Wizard will communicate to your Adaptive Server Enterprise instance to fetch a list of the catalogs and schemata. If you left blank the Database field in the Source Selection page it will retrieve all of the catalogs in the server. Otherwise it will just fetch the schemata corresponding to the catalog you explicitly typed.

Verify that all tasks finished successfully and click on the “Next” button to move forward. You will be given a list of catalogs and their corresponding schemata to select the ones to migrate. Keep in mind that you can only migrate schemata from one catalog at a time.

Beware that the concept of schema in Adaptive Server Enterprise is more close to the definition of schemata in Microsoft SQL Server. A schema is more of a group of users that owns certain database objects than a logical grouping or classification of objects.

The Schema Selection page will look like this:

As you can see, the pubs3 sample database has only one schema named “dbo”. Now take a look at the “Schema Name Mapping Method” group box. MySQL only supports one schema in each database (to be more precise, a MySQL database is a schema) so we have to tell the Migration Wizard how to handle the migration of schemas in our source database. We can either keep all of the schemas as they are (the Migration Wizard will create one database per schema), or merge them into a single MySQL database. The two last options are for specifying how the merge should be done: either remove the schema names (the Migration Wizard will handle the possible name colisions thay may appear along the way) or either adding the schema name to the database object names as a prefix. Let’s select the second option (it’s the default option) since we only have one schema and we are not particularly interested in keeping its meaningless name.

Select the objects to migrate

Move to the next page using the “Next” button. You should see the reverse engineering of the selected schema in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below:

In the next page (the Source Objects page) you will have a list with the objects that were retrieved and are available for migration. If you click on the “Show Selection” button you will be able to select exactly which of the retrieved table objects you want to migrate, as shown here:

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the “Objects to Migrate” list and that the“Migrate Table Objects” checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click “Next”.

Review the proposed migration

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You may have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

As you can see in the image above, there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the “pubs3? row and rename it.

An interesting option in the View combo box is the “Column Mappings” one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.

Run the resulting MySQL code to create the database objects

Check any warning or error you get there (you should get none) and move to the Target Creation Options page. It will look like this:

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the next page: the Create Schemata page.

Once the creation of the pubs3 database finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

You can still edit the migration code using the code box to the right and save your changes by clicking on the “Apply” button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the “Recreate Objects” button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the “Next” button.

Transfer the data to the MySQL database

The next steps in the Migration Wizard are related to the transference of data from the source Adaptive Server Enterprise database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It may take a little while to copy the data. At this point the corresponding progress page will look familiar:

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

A little verification step

Now that the “pubs3? database was successfully migrated, let’s see the results. Open your MySQL Server instance and query the newly created “pubs3? database. You can try something like “SELECT * FROM pubs3.authors;”. You should get something like this:

Conclusions

By now you should have a pretty good idea of the capabilities of the Migration Wizard and should be ready to use it for your own migrations. The official documentation is also there for you and you can always ask any question in the comments of this post or in the migration official forum.

Sergio de la Cruz