call us toll-free +1 855 855 3600
 
Home > Blog > Converting Objects during Database Migration using AWS Schema Conversion Tool

Converting Objects during Database Migration using AWS Schema Conversion Tool

Posted by | On | In Amazon Web Services, Database Migration | Tags:
 

This continues our video blog series on the AWS Schema Conversion Tool (SCT). We will demonstrate how the AWS SCT helps users convert data and code objects from Oracle database to AWS RDS for MySQL, Amazon Aurora or PostgreSQL database.

Background

Converting Objects is one of the most important aspects in the AWS Schema Conversion Tool application.

The AWS Schema Conversion Tool allows users to convert all objects in the database, or simply select the required objects and convert them (as you already know, the Assessment Report is automatically generated for each pair of converted objects).

As far as not all objects being automatically converted, the AWS Schema Conversion Tool provides valuable comments and suggestions for users to fix issues arising during conversion. Depending on the project settings, users can adjust the level of details of commentaries in the target code, leaving just the error messages or the full set of warnings.

In the following video we’ll show you how to convert Oracle objects using AWS Schema Conversion Tool, and discuss the useful features of this application.

 

Need help getting started? Check out our Jumpstart for SCT offer!

Observations

Here are several observations we’ve made based on our experiences using SCT for our database migration projects that we mentioned in the video:

  • ROWID generation. The AWS Schema Conversion Tool can automatically generate the code to emulate Oracle’s ROWID pseudocolumn by adding an additional column to each table along with additional code.
    • There is a side effect during conversion of “SELECT *” queries. The number of columns in the table changes, which could result in not being able to use the result for a target variable. In this case the Action Item with a warning message will be automatically created, regardless of whether the RowID was actually used.
    • The other side effect is that the additional column is created in all tables, regardless of whether the RowID was actually used.
    • As a result, we recommend turning on the ROWID generation only for conversion of the objects which really use the pseudocolumn in Oracle. This is why SCT has the generation of ROWID turned off by default. During the conversion of code objects SCT doesn’t knows if the emulating column was actually generated, so its behavior depends on the settings.
  • Emulating Oracle’s Check Constraints. Since MySQL doesn’t support check constraints, SCT emulates the functionality using BEFORE INSERT and BEFORE UPDATE triggers.
    • You can add additional behaviors to the emulated trigger code, but keep in mind that they need additional system resources for executing.
    • MySQL is unable to use these triggers for query plan optimization.
    • MySQL also has limited capabilities for possible DEFAULT values for columns and doesn’t support the virtual columns. They are also emulated by triggers.
  • Regarding the NLS_Lower example. We do not recommend using the “Lower” Oracle’s function instead of “NLS_Lower” as shown in the video. It was just an example of how changes in the source code can improve conversion to the target.

Stay tuned to our blog for more tips and insights around migrating databases and applications to AWS. To learn how the experts at DB Best can help you jump-start your migration effort, contact us.

Related posts:

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image