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.
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:
- Migrating an Oracle to MySQL Database using the AWS Schema Conversion Tool
- AWS Schema Conversion Tool: Offline mode
- AWS Schema Conversion Tool: Connecting to Oracle Source Database
- Selecting target database type for migration using Assessment Report in AWS Schema Conversion Tool
- Troubleshooting database migration issues using AWS Schema Conversion Tool Assessment Report
- AWS Schema Conversion Tool: Applying Converted Code to Target Database
- General Approach to Migration of Data Warehouses to Amazon Redshift
- Specific Features of Migrating Data Warehouses to Redshift
- First look at AWS Schema Conversion Tool with tighter DMS integration
- Using AWS Schema Conversion Tool for Application Conversion
- Optimizing your Redshift Storage with AWS Schema Conversion Tool
- Extracting Data from Warehouses with Data Migration Agents in AWS Schema Conversion Tool
- Using AWS Schema Conversion Tool Extension Pack for OLTP databases
- Using AWS Schema Conversion Tool Extension Pack for Data Warehouses
