AWS Schema Conversion Tool. User Guide Version 1.0

102  Download (0)

Full text

(1)

AWS Schema Conversion Tool

User Guide

(2)

AWS Schema Conversion Tool: User Guide

Copyright

©

2016 Amazon Web Services, Inc. and/or its affiliates. All rights reserved.

Amazon's trademarks and trade dress may not be used in connection with any product or service that is not Amazon's, in any manner that is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon. All other trademarks not owned by Amazon are the property of their respective owners, who may or may not be affiliated with, connected to, or sponsored by Amazon.

(3)

Table of Contents

What Is the AWS Schema Conversion Tool? ... 1

Converting Your Schema ... 2

The AWS Schema Conversion Tool Project Window ... 3

Related Topics ... 4

Installing and Updating ... 5

Installing the AWS Schema Conversion Tool on Windows ... 5

Installing the AWS Schema Conversion Tool on Mac OS X ... 6

Installing the AWS Schema Conversion Tool on Ubuntu Linux ... 6

Installing the AWS Schema Conversion Tool on Fedora Linux ... 6

Updating the AWS Schema Conversion Tool ... 7

Required Database Drivers ... 7

Installing JDBC Drivers on Linux ... 7

Update Global Settings ... 8

Related Topics ... 9

Getting Started ... 10

Before You Begin ... 10

Create an AWS Schema Conversion Tool Project ... 10

View the Schema from Your Source Database ... 15

Create the Converted Schema ... 17

Clear the Converted Schema ... 19

Update Your Source Schema ... 20

Overwrite the Converted Schema ... 21

Create the Database Migration Assessment Report ... 22

Apply the Converted Schema to the DB Instance ... 26

Related Topics ... 27

Converting Schema ... 28

Create a Project ... 29

The Assessment Report ... 31

To Create the Database Migration Assessment Report ... 32

Converting Your Schema ... 35

Clearing a Converted Schema ... 39

Updating and Refreshing Your Converted Schema ... 40

Saving and Applying Your Schema ... 41

Saving Your Converted Schema to a File ... 41

Applying Your Converted Schema ... 42

Related Topics ... 43

Best Practices ... 44

Memory Management and Performance ... 44

Related Topics ... 44

Reference ... 45

Related Topics ... 45

(4)

Statements ... 75 Procedures ... 78 Flow Control ... 79 Functions ... 79 Operators ... 87 Transactions ... 90 Data Types ... 91

Data Definition Language (DDL) ... 93

Cursors ... 94

Related Topics ... 95

Keyboard Shortcuts ... 95

Related Topics ... 95

Troubleshooting ... 96

Cannot load objects from Oracle source database ... 96

Related Topics ... 96

Document History ... 98

(5)

What Is the AWS Schema

Conversion Tool?

The AWS Schema Conversion Tool helps you convert your database schema from an Oracle or Microsoft SQL Server database, to an Amazon Relational Database Service (Amazon RDS) MySQL DB instance, an Amazon Aurora DB cluster, or a PostgreSQL DB instance.

The AWS Schema Conversion Tool can be run on a Microsoft Windows, Mac, or Linux desktop and provides a project-based user interface to automatically convert the database schema of your source database into a format compatible with your target Amazon RDS instance. In the case where schema from your source database cannot be automatically converted, the AWS Schema Conversion Tool provides guidance on how you can create equivalent schema in your target Amazon RDS database.

(6)

Converting Your Schema

The process of converting your database schema to Amazon RDS follows these steps:

1. Create your target DB instance – Create the Amazon RDS for MySQL DB instance, Amazon Aurora DB cluster, or PostgreSQL DB instance to host your converted schema and data. For a MySQL or PostgreSQL DB instance, be sure to allocate enough storage for your data once converted. 2. Create a new AWS Schema Conversion Tool project – Create a project to convert your database

schema.

3. Connect the AWS Schema Conversion Tool to your source and target – Connect your AWS Schema Conversion Tool project to your source database and your Amazon RDS DB instance. 4. Create a database migration assessment report – The AWS Schema Conversion Tool creates a

database migration assessment report that details the schema elements that cannot be converted automatically. You can use this report to identify where you need to create schema in your Amazon RDS DB instance that is compatible with those of your source database.

In some cases when the schema from your source database cannot be automatically converted, you can modify those schema in your source database into a format that can be automatically converted

AWS Schema Conversion Tool User Guide Converting Your Schema

(7)

to the database engine for your target DB instance. After you have modified the schema from your source database and verified that it is compatible with your application, refresh the metadata in your AWS Schema Conversion Tool project with the updated schema information, and then convert the schema again and create a new database migration assessment report.

5. Convert your schema – The AWS Schema Conversion Tool creates a local version of the converted schema for you to review, but it doesn't apply that version to your target DB instance until you are ready.

6. Apply the converted schema to Amazon RDS – When you are ready, have the AWS Schema Conversion Tool apply the converted schema in your local project to your Amazon RDS DB instance. 7. Create any schema elements that cannot be automatically converted – If you have schema

elements that couldn't be automatically converted to your target database engine, create equivalent schema elements in your target Amazon RDS DB instance.

Caution

Once you manually add schema elements for items that couldn't be automatically converted to your target DB instance, take care if you plan to rewrite the converted schema from the AWS Schema Conversion Tool to your target DB instance. Rewriting the converted schema overwrites the schema in your target DB instance, and any schema elements that you have manually added are lost. You might want to save your manual work before rewriting the converted schema, or you might want to avoid rewriting the converted schema and update the schema in your target DB instance using a different method.

For details, see Converting a Database Schema to Amazon RDS Using the AWS Schema Conversion Tool (p. 28).

The AWS Schema Conversion Tool Project

Window

The following is what you see in the AWS Schema Conversion Tool when you create a schema migration project.

AWS Schema Conversion Tool User Guide The AWS Schema Conversion Tool Project Window

(8)

1. In the left panel, the schema from your source database is presented in a tree view. Your database schema is "lazy loaded." That is, at the point when you select an item from the tree view, the AWS Schema Conversion Tool gets and displays the current schema from your source database. 2. In the top middle panel, action items appear for schema elements from the source database engine

that couldn't be automatically converted to the target database engine.

3. In the right panel, the schema from your target DB instance is presented in a tree view. Your database schema is "lazy loaded." That is, at the point when you select an item from the tree view, the AWS Schema Conversion Tool gets and displays the current schema from your target database.

4. In the lower left panel, when you choose a schema element, properties describing the source schema element and the SQL command to create that element in the source database are displayed. 5. In the lower right panel, when you choose a schema element, properties describing the target schema

element and the SQL command to create that element in the target database are displayed here.

Related Topics

• Installing and Updating the AWS Schema Conversion Tool (p. 5)

• Getting Started with the AWS Schema Conversion Tool (p. 10)

AWS Schema Conversion Tool User Guide Related Topics

(9)

Installing and Updating the AWS

Schema Conversion Tool

The AWS Schema Conversion Tool is available for the following operating systems: • Microsoft Windows (download)

• Mac OS X (download) • Ubuntu Linux (download) • Fedora Linux (download)

The AWS Schema Conversion Tool also installs the Java Runtime Environment (JRE) version 8u45.

Important

After you install the AWS Schema Conversion Tool, you must also install the required JDBC drivers to connect to your source database and target Amazon Relational Database Service (Amazon RDS) DB instance. For details, see Required Database Drivers (p. 7).

Topics

• Installing the AWS Schema Conversion Tool on Windows (p. 5)

• Installing the AWS Schema Conversion Tool on Mac OS X (p. 6)

• Installing the AWS Schema Conversion Tool on Ubuntu Linux (p. 6)

• Installing the AWS Schema Conversion Tool on Fedora Linux (p. 6)

• Updating the AWS Schema Conversion Tool (p. 7)

AWS Schema Conversion Tool User Guide Installing the AWS Schema Conversion Tool on Windows

(10)

2. Run the AWS Schema Conversion Tool-1.0.msi installer.

By default, the AWS Schema Conversion Tool is installed in the c:\Program Files\AWS Schema Conversion Tool folder.

3. When the installation is complete, you can run the AWS Schema Conversion Tool application. Install the required JDBC drivers for your source database engine and target DB instance database engine, if needed, and set the paths to the JDBC drivers in the global settings of the AWS Schema Conversion Tool. For details, see Required Database Drivers (p. 7).

Installing the AWS Schema Conversion Tool on

Mac OS X

To install the AWS Schema Conversion Tool on Mac OS X

1. Download aws-schema-conversion-tool-1.0.latest.zip for Mac OS X, and then extract AWS Schema Conversion Tool-1.0.dmg.

2. In Finder, open AWS Schema Conversion Tool-1.0.dmg.

3. Drag AWS Schema Conversion Tool-1.0.dmg to the Applications folder.

4. When the installation is complete, you can run the AWS Schema Conversion Tool application. Install the required JDBC drivers for your source database engine and target DB instance database engine, if needed, and set the paths to the JDBC drivers in the global settings of the AWS Schema Conversion Tool. For details, see Required Database Drivers (p. 7).

Installing the AWS Schema Conversion Tool on

Ubuntu Linux

Important

The AWS Schema conversion tool is supported on Ubuntu Linux version 15.04 (64-bit).

To install the AWS Schema Conversion Tool on Ubuntu Linux

1. Download aws-schema-conversion-tool-1.0.latest.zip for Ubuntu, and then extract aws-schema-conversion-tool-1.0.deb.

2. Run the following command where file-path is the folder that you downloaded the file to: sudo dpkg -i file-path/aws-schema-conversion-tool-1.0.deb

3. When the installation is complete, you can run the AWS Schema Conversion Tool application. Install the required JDBC drivers for your source database engine and target DB instance database engine, if needed, and set the paths to the JDBC drivers in the global settings of the AWS Schema Conversion Tool. For details, see Required Database Drivers (p. 7).

Installing the AWS Schema Conversion Tool on

Fedora Linux

Important

The AWS Schema conversion tool is supported on Fedora Linux version 22 (64-bit).

AWS Schema Conversion Tool User Guide Installing the AWS Schema Conversion Tool on Mac OS

(11)

To install the AWS Schema Conversion Tool on Fedora Linux

1. Download aws-schema-conversion-tool-1.0.latest.zip for Fedora, and then extract aws-schema-conversion-tool-1.0-1.x86_64.rpm.

2. Run the following command, where file-path is the folder that you downloaded the file to: sudo yum install file-path/aws-schema-conversion-tool-1.0-1.x86_64.rpm 3. When the installation is complete, you can run the AWS Schema Conversion Tool application. Install

the required JDBC drivers for your source database engine and target DB instance database engine, if needed, and set the paths to the JDBC drivers in the global settings of the AWS Schema Conversion Tool. For details, see Required Database Drivers (p. 7).

Updating the AWS Schema Conversion Tool

To check whether new version of the AWS Schema Conversion Tool is available, choose Help -> Check

for Updates. If there is a newer version of the AWS Schema Conversion Tool than the version that you

have installed, you will be prompted to download and install the updated version.

Required Database Drivers

You must have the required JDBC drivers installed for the AWS Schema Conversion Tool to connect to your source database or target RDS DB instance. You can download the latest database drivers from the following locations.

Download Database Engine

https://www.mysql.com/products/connector/

MySQL or Amazon Au-rora https://jdbc.postgresql.org/download.html PostgreSQL http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html Oracle https://www.microsoft.com/en-us/download/details.aspx?display-lang=en&id=11774 Microsoft SQL Server

Installing JDBC Drivers on Linux

AWS Schema Conversion Tool User Guide Updating the AWS Schema Conversion Tool

(12)

Installation Commands Database Engine

PROMPT> cd /usr/local/jdbc-drivers

PROMPT> sudo tar xzvf /tmp/mysql-connector-java-X.X.X.tar.gz

MySQL or Amazon Au-rora

PROMPT> cd /usr/local/jdbc-drivers

PROMPT> sudo cp -a /tmp/postgresql-X.X.X.jre7.tar . PostgreSQL

PROMPT> cd /usr/local/jdbc-drivers PROMPT> sudo mkdir oracle-jdbc PROMPT> cd oracle-jdbc

PROMPT> sudo cp -a /tmp/ojdbc7.jar . Oracle

PROMPT> cd /usr/local/jdbc-drivers

PROMPT> sudo tar xzvf /tmp/sqljdbc_X.X.X_enu.tar.gz Microsoft SQL Server

Update Global Settings

After you have downloaded and installed the required JDBC drivers, you must update the settings in the AWS Schema Conversion Tool with the driver file locations.

To update the driver file locations

1. In the AWS Schema Conversion Tool, choose Settings, and then choose Global settings.

2. For Global settings, choose Drivers. Add the file path to the JDBC driver for your source database engine and your target RDS DB instance database engine.

AWS Schema Conversion Tool User Guide Update Global Settings

(13)

3. Choose OK to exit the Global Settings window.

Related Topics

• What Is the AWS Schema Conversion Tool? (p. 1)

• Getting Started with the AWS Schema Conversion Tool (p. 10)

AWS Schema Conversion Tool User Guide Related Topics

(14)

Getting Started with the AWS

Schema Conversion Tool

This topic walks you through how to create an AWS Schema Conversion Tool project and convert the schema from your source database to your target Amazon Relational Database Service (Amazon RDS) DB instance.

Before You Begin

Before you perform the tasks described in this topic, you must first have completed the following: • Install the AWS Schema Conversion Tool. For more information, see Installing and Updating the AWS

Schema Conversion Tool (p. 5).

• Identify the connection information for your source database.

• Create your target Amazon RDS DB instance (if needed) and identify the connection information for your target DB instance. For more information, see Connecting to a DB Instance Running the MySQL Database Engine, Connecting to an Amazon Aurora DB Cluster, or Connecting to a DB Instance Running the PostgreSQL Database Engine.

Create an AWS Schema Conversion Tool Project

The first time that open the AWS Schema Conversion Tool you automatically begin creating a database migration project using the Create New Database Migration Project wizard. You can create additional projects using the Create New Database Migration Project wizard by choosing the File menu, and then choosing the New Project Wizard option.

Optionally, you can create a project without the Create New Database Migration Project wizard by choosing the File menu, and then choosing the New Project option. For more information, see Creating an AWS Schema Conversion Tool Project (p. 29).

Follow these steps to create your first data migration project using the Create New Database Migration

Project wizard.

AWS Schema Conversion Tool User Guide Before You Begin

(15)

Select your source database

1. Add your local project settings and the information to connect to your source database in the first step of the wizard:

Note

You cannot connect to an Oracle database using Transparent Network Substrate (TNS) names.

• Project Name: A name for your project, which is stored locally on your computer. • Location: The file location for your local project file.

• Source DB Engine: The database engine for your source database. This value can be Oracle for an Oracle database or SQL Server for a Microsoft SQL Server database.

• Server Name: The DNS name or IP address of your source database server. • Server Port: The port used to connect to your source database server. • Instance name/Oracle SID:

• For Microsoft SQL Server, this value is the instance name. You can obtain the instance name by submitting the following query to your SQL Server database:

SELECT @@servername;

• For Oracle databases, this value is the Oracle System ID (SID). You can obtain the Oracle SID by submitting the following query to your Oracle database:

SELECT sys_context('userenv','instance_name') AS SID FROM dual; • Username: The user name used to connect to your source database server.

• Password: The password for your user name.

The AWS Schema Conversion Tool uses this password to connect to your source database only when you choose the Connect to source option. To guard against exposing the password for your source database, the AWS Schema Conversion Tool does not store that password. If you close your AWS Schema Conversion Tool project and reopen it, you are prompted for the password to connect to your source database as needed.

You can choose the Test Connection button to verify that your database connection information successfully connects you to your source database.

After you have finished adding the project and connection information, choose Next to continue.

AWS Schema Conversion Tool User Guide Create an AWS Schema Conversion Tool Project

(16)

2. Select the schema from your source database

Select the schema from your source database that you want to convert to your target RDS DB instance.

After you have selected the schema that you want to convert, choose Next to continue.

AWS Schema Conversion Tool User Guide Create an AWS Schema Conversion Tool Project

(17)

3. Run the database migration assessment report

The database migration assessment report provides important information regarding the conversion of the schema from your source database to your target RDS DB instance. The report summarizes all of the schema conversion tasks and details the action items for schema that cannot be converted to the DB engine of your target DB instance. The report also includes estimates of the amount of effort that it will take to write the equivalent code in your target DB instance that could not be automatically converted. For more information on the database migration assessment report, see

Creating and Using the Assessment Report (p. 31).

You can save a local copy of the database migration assessment report as either a Portable Document Format (PDF) file, or a Comma-separated Values (CSV) file. This step is optional. You can view the

AWS Schema Conversion Tool User Guide Create an AWS Schema Conversion Tool Project

(18)

4. Select your target RDS DB instance

Add the information to connect to your target RDS DB instance:

• Target DB Engine: The database engine for your target DB instance. This value can be MySQL for a MySQL DB instance, Amazon Aurora for an Amazon Aurora DB cluster, or PostgreSQL for a PostgreSQL DB instance.

• Server Name: The DNS name of your target DB instance. • Server Port: The port used to connect to your target DB instance. • Username: The user name used to connect to your target DB instance. • Password: The password for your user name.

The AWS Schema Conversion Tool uses this password to connect to your target DB instance only when you choose the Connect to target option. To guard against exposing the password for your source database, the AWS Schema Conversion Tool does not store that password. If you close your AWS Schema Conversion Tool project and reopen it, you are prompted for the password to connect to your target DB instance as needed.

You can choose the Test Connection button to verify that your database connection information successfully connects you to your target RDS DB instance.

After you have finished adding your connection information, choose Finish to create your project.

AWS Schema Conversion Tool User Guide Create an AWS Schema Conversion Tool Project

(19)

View the Schema from Your Source Database

Your AWS Schema Conversion Tool project displays the schema from your source database in the left panel. The schema is presented in a tree-view format and each node of the tree is "lazy loaded." That is, when you choose a node in the tree view, the AWS Schema Conversion Tool will request the schema information from your source database at that time.

AWS Schema Conversion Tool User Guide View the Schema from Your Source Database

(20)

AWS Schema Conversion Tool User Guide View the Schema from Your Source Database

(21)

Create the Converted Schema

Use the following procedure to create the converted schema.

AWS Schema Conversion Tool User Guide Create the Converted Schema

(22)

2. In the left panel that displays the schema from your source database, choose a schema object to convert. Open the context (right-click) menu for the object, and choose Convert Schema.

When the AWS Schema Conversion Tool finishes converting the schema, you can view the proposed schema in the panel on the right of your project. At this point, no schema is applied to your target RDS DB instance. The planned schema is only part of your project. If you select a converted schema item, you can see the planned schema command in the panel at lower-center for your target RDS DB instance.

AWS Schema Conversion Tool User Guide Create the Converted Schema

(23)

Clear the Converted Schema

Before the AWS Schema Conversion Tool applies the schema to your target RDS DB instance, you can clear the planned schema from your project by opening the context (right-click) menu for the tree-view node for your target DB instance, and then choosing Refresh from Database. Because no schema has been written to your target DB instance, refreshing from the database removes the planned schema elements in your schema conversion tool project to match what exists in your target DB instance.

AWS Schema Conversion Tool User Guide Clear the Converted Schema

(24)

Update Your Source Schema

You can update your AWS Schema Conversion Tool project with the most recent schema from your source database by opening the context (right-click) menu for the schema item from your source database that you want to update, and then choosing Refresh from Database. Doing this enables you to update your schema conversion tool project if changes have been made to the schema of your source database.

AWS Schema Conversion Tool User Guide Update Your Source Schema

(25)

Overwrite the Converted Schema

After you have refreshed the schema from your source database in your project, you can overwrite the converted schema in your project with updated schema.

To overwrite a converted schema in your project with an updated schema

1. In the left panel that displays the schema from your source database, choose the schema object to update the converted schema for.

2. Open the context (right-click) menu for the object and choose Convert Schema, as shown following.

AWS Schema Conversion Tool User Guide Overwrite the Converted Schema

(26)

3. A prompt asks you to confirm the action before overwriting the local converted schema, as shown following.

Create the Database Migration Assessment

Report

The New Project Wizard includes a step to create the database migration assessment report, which summarizes all of the action items for schema that cannot be converted to the engine of your target RDS DB instance. The report also includes estimates of the amount of effort that it will take to write the equivalent code in your target DB instance that could not be automatically converted.

AWS Schema Conversion Tool User Guide Create the Database Migration Assessment Report

(27)

You can create an updated database migration assessment report at any time in your project by following these steps.

To create the database migration assessment report

1. In the left panel that displays the schema from your source database, choose a schema object to create an assessment report for. Open the context (right-click) menu for the object, and choose

Create Report.

The assessment report view then opens. To explore the report, choose the Action Items tab. The

Action Items tab displays a list of items that describe schema that could not automatically be

converted to the database engine of your target RDS DB instance.

Select one of the action items from the list. Notice that the AWS Schema Conversion Tool highlights the item from your schema that the action item applies to, as shown following.

AWS Schema Conversion Tool User Guide Create the Database Migration Assessment Report

(28)

2. To view the assessment report summary

Select the Summary tab. The Summary tab displays the summary information from the database migration assessment report. It shows the number of items that were automatically converted and the number of items that could not be automatically converted. The summary also includes an estimate of the time that it would take to create schema that is equivalent to your source database in your target DB instance. An example is shown following.

AWS Schema Conversion Tool User Guide Create the Database Migration Assessment Report

(29)

3. To save the assessment report

Step 3 of the Create New Database Migration Project wizard gives you the option to create a local copy of the database migration assessment report. You can choose to save a local copy of the database migration assessment report as either a Portable Document Format (PDF) file, or a Comma-separated Values (CSV) file after you have created your database migration project. Choose the Summary tab, and then choose Save to PDF to create the database migration assessment report as a PDF file and store it locally on your computer. The PDF file contains both the summary and action item information. The CSV file contains only action item information.

AWS Schema Conversion Tool User Guide Create the Database Migration Assessment Report

(30)

Apply the Converted Schema to the DB Instance

When you are ready, you can apply the converted database schema to your target RDS DB instance. Once the schema has been applied to your target DB instance, you can update it based on the action items identified in the database migration assessment report.

To apply the converted database schema to your target RDS DB instance

Caution

If you have an existing schema in your DB instance that matches the schema being applied, then the AWS Schema Conversion Tool overwrites the existing schema. This functionality is useful to update the schema in your target DB instance. However, be careful not to overwrite schema unintentionally—especially a schema that you have modified in your target DB instance based on a database migration assessment report.

AWS Schema Conversion Tool User Guide Apply the Converted Schema to the DB Instance

(31)

1. To apply the schema to your target DB instance, choose the schema element in the right panel of your project that displays the planned schema for your target DB instance.

2. Open the context (right-click) menu for the schema element, and choose Apply to database.

Related Topics

• What Is the AWS Schema Conversion Tool? (p. 1)

• Installing and Updating the AWS Schema Conversion Tool (p. 5)

AWS Schema Conversion Tool User Guide Related Topics

(32)

Converting a Database Schema to

Amazon RDS Using the AWS

Schema Conversion Tool

The AWS Schema Conversion Tool automates much of the process of converting your database schema from an Oracle or Microsoft SQL Server database to an Amazon Relational Database Service (Amazon RDS) MySQL DB instance, an Amazon Aurora DB cluster, or a PostgreSQL DB instance. Because the Oracle, SQL Server and MySQL database engines (Amazon Aurora is MySQL-compatible) contain many different features and capabilities, the AWS Schema Conversion Tool attempts to create an equivalent schema in your RDS DB instance wherever possible. In cases where there is no direct conversion possible, the AWS Schema Conversion Tool provides a list of action items for you and a database migration assessment report that includes information on the schema elements that couldn't be converted, along with guidance on how you can manually create equivalent schema from your source database in your target DB instance.

Topics

• Creating an AWS Schema Conversion Tool Project (p. 29)

• Creating and Using the Assessment Report (p. 31)

• Converting Your Schema (p. 35)

• Updating and Refreshing Your Converted Schema (p. 40)

• Saving and Applying Your Converted Schema (p. 41)

• Related Topics (p. 43)

The process of converting your database schema to RDS can be described with the following steps: 1. Create your target DB instance – Create the Amazon RDS for MySQL DB instance, Amazon Aurora

DB cluster, or PostgreSQL DB instance to host your converted schema and data. For a MySQL or PostgreSQL DB instance, be sure to allocate enough storage for your data once converted. 2. Create a new AWS Schema Conversion Tool project – Create a project to convert your database

schema.

3. Connect the AWS Schema Conversion Tool to your source and target – Connect your AWS Schema Conversion Tool project to your source database and your RDS DB instance.

4. Create a database migration assessment report – The AWS Schema Conversion Tool creates a database migration assessment report that details the schema elements that cannot be converted

(33)

automatically. You can use this report to identify where you need to create a schema in your RDS DB instance that is compatible with your source database.

In some cases when the schema from your source database cannot be automatically converted, you can modify those schema in your source database into a format that can be automatically converted to the database engine for your target DB instance. After you have modified the schema from your source database and verified that it is compatible with your application, refresh the metadata in your AWS Schema Conversion Tool project with the updated schema information, and then convert the schema again and create a new database migration assessment report.

5. Convert your schema – The AWS Schema Conversion Tool creates a local version of the converted schema for you to review, but it doesn't apply it to your target DB instance until you are ready. 6. Apply the converted schema to Amazon RDS – When you are ready, have the AWS Schema

Conversion Tool apply the converted schema in your local project to your RDS DB instance. 7. Create any schema elements that cannot be automatically converted – If you have schema

elements that couldn't be automatically converted to your target database engine, create equivalent schema elements in your target RDS DB instance.

Caution

Once you manually add schema elements that could not be automatically converted to your target DB instance, take care if you plan to rewrite the converted schema from the AWS Schema Conversion Tool to your target DB instance. Rewriting the converted schema overwrites the schema in your target DB instance, and any schema elements that you have manually added are lost. You might want to save your manual work before rewriting the converted schema, or you might want to avoid rewriting the converted schema and update the schema in your target DB instance using a different method.

Creating an AWS Schema Conversion Tool

Project

You can use a AWS Schema Conversion Tool project to do the following: • Connect to the source database that you will be converting a schema from. • Display the schema from your source database in tree-view format.

• Connect to the target RDS DB instance that you will be converting schema to. • Display the schema from your target DB instance in tree-view format.

• Generate a database migration assessment report that lists the schema items from your source database that couldn't be automatically converted to the equivalent schema for your target DB instance. • Convert the schema from your source database to a format compatible with the DB engine of your

target DB instance. This converted schema is stored locally until you are ready to apply it to your target

AWS Schema Conversion Tool User Guide Create a Project

(34)

To create your project and connect to your source and target databases

1. Start the AWS Schema Conversion Tool, and choose New Project on the File menu. Add the following information:

• Project Name: A name for your project, which is stored locally on your computer. • Location: The file location for your local project file.

• Source: The database engine for your source database. This value can be Oracle for an Oracle database or SQL Server for a Microsoft SQL Server database.

• Target: The database engine for your target DB instance. This value can be MySQL for a MySQL DB instance, Amazon Aurora for an Amazon Aurora DB cluster, or PostgreSQL for a PostgreSQL DB instance.

Choose OK to create your AWS Schema Conversion Tool project.

2. Choose the Connect to source button where source indicates the database engine for your source database.

Add the following information:

Important

You cannot connect to an Oracle database using Transparent Network Substrate (TNS) names.

• Server Name: The DNS name of your source database server. • Server Port: The port used to connect to your source database server. • Instance name/Oracle SID:

• For Microsoft SQL Server, this value is the instance name. You can obtain the instance name by submitting the following query to your SQL Server database:

SELECT @@servername;

• For Oracle databases, this value is the Oracle System ID (SID). You can obtain the Oracle SID by submitting the following query to your Oracle database:

SELECT sys_context('userenv','instance_name') AS SID FROM dual; • Username: The user name used to connect to your source database server.

• Password: The password for your user name.

The AWS Schema Conversion Tool uses this password to connect to your source database only when you choose the Connect to source option. To guard against exposing the password for your source database, the AWS Schema Conversion Tool does not store that password. If you close your AWS Schema Conversion Tool project and reopen it, you are prompted for the password to connect to your source database as needed.

Choose OK to connect to your source database.

3. Choose Connect to target, where target indicates the database engine for your target DB instance.

AWS Schema Conversion Tool User Guide Create a Project

(35)

Add the following information:

• Server Name: The DNS name of your target DB instance. • Server Port: The port used to connect to your target DB instance. • Username: The user name used to connect to your target DB instance. • Password: The password for your user name.

The AWS Schema Conversion Tool uses this password to connect to your target DB instance only when you choose the Connect to target option. To guard against exposing the password for your source database, the AWS Schema Conversion Tool does not store that password. If you close your AWS Schema Conversion Tool project and reopen it, you are prompted for the password to connect to your target DB instance as needed.

Choose OK to connect to your target DB instance.

Creating and Using the Assessment Report

The database migration assessment report provides important information regarding the conversion of the schema from your source database to your target RDS DB instance. The report summarizes all of the schema conversion tasks and details the action items for schema that cannot be converted to the DB engine of your target DB instance. The report also includes estimates of the amount of effort that it will take to write the equivalent code in your target DB instance that could not be automatically converted. You can respond to the action items in the assessment report in different ways:

• Add equivalent schema manually – You can write all of the schema that can be automatically converted to your target DB instance by choosing Apply to database in the right panel of your project. The schema that are written to your target DB instance won't contain the items that couldn't be automatically converted. Those items are listed in your database migration assessment report.

After applying the schema to your target DB instance, you can then manually create schema in your target DB instance that are equivalent to the source database. In some cases, you cannot create equivalent schema in your target DB instance.You might need to rearchitect a portion of your application

AWS Schema Conversion Tool User Guide The Assessment Report

(36)

that the updates are compatible with your application, choose Refresh from Database in the left panel of your project to update the schema from your source database. You can then convert your updated schema and generate the database migration assessment report again, and the action item for your updated schema will no longer appear.

The advantage to this process is that your updated schema will always be available when you refresh from your source database.

To Create the Database Migration Assessment

Report

1. In the left panel that displays the schema from your source database, choose a schema object to create an assessment report for. Open the context (right-click) menu for the object, and choose Create Report.

After you create the assessment report, the assessment report view opens, showing the Summary tab. The Summary tab displays the summary information from the database migration assessment report. It shows items that were automatically converted and items that could not be automatically converted. For schema items that could not be automatically convered to the target database engine, the summary includes an estimate of the effort that it would take to create schema that is equivalent to your source database in your target DB instance. The report categorizes the estimated time to convert these schema items as follows:

• Simple – actions that can be completed in less than 1 hour.

• Medium – actions that are more complex and can be completed in 1 to 4 hours.

AWS Schema Conversion Tool User Guide To Create the Database Migration Assessment Report

(37)

• Significant – actions that are very complex and will take more than 4 hours to complete.

You can save a local copy of the database migration assessment report as either a Portable Document Format (PDF) file, or a Comma-separated Values (CSV) file. The CSV file contains only action item information. The PDF file contains both the summary and action item information, as shown in the following example.

AWS Schema Conversion Tool User Guide To Create the Database Migration Assessment Report

(38)

The assessment report view also includes an Action Items tab. The AWS Schema Conversion Tool displays a list of items that describe schema that could not automatically be converted to the database engine of your target RDS DB instance. If you select an action item from the list, the AWS Schema Conversion Tool will highlight the item from your schema that the action item applies to.

AWS Schema Conversion Tool User Guide To Create the Database Migration Assessment Report

(39)

Converting Your Schema

After you have connected your project to both your source database and your target RDS DB instance, your AWS Schema Conversion Tool project displays the schema from your source database in the left panel. The schema is presented in a tree-view format, as shown following, and each node of the tree is "lazy loaded." That is, when you choose a node in the tree view, the AWS Schema Conversion Tool requests the schema information from your source database at that time.

AWS Schema Conversion Tool User Guide Converting Your Schema

(40)

AWS Schema Conversion Tool User Guide Converting Your Schema

(41)

You can choose schema items from your source database and then convert the schema to equivalent schema for the DB engine of your target DB instance.You can choose any schema item from your source database to convert. If the schema item that you chose is dependent on a "parent" item, then the AWS Schema Conversion Tool also generates the schema for the parent item. For example, if you choose a column from a table to convert, then the AWS Schema Conversion Tool generates the schema for the

AWS Schema Conversion Tool User Guide Converting Your Schema

(42)

You can create a database migration assessment report that contains a summary of all of the action items displayed in the Action Items tab and save it as a PDF. The assessment report is useful for identifying and resolving schema items that could not be automatically converted. For more information, see Creating and Using the Assessment Report (p. 31).

After you have converted the schema from your source database, you can choose schema items from the left panel of your project and view the converted schema in the center panels of your project. The lower-center panel displays the properties of and the SQL command to create the converted schema, as shown following.

AWS Schema Conversion Tool User Guide Converting Your Schema

(43)

After you have converted your schema for the first time or created a database migration assessment report, you can save your project and the schema information from your source database will be saved with your project. This means that you can work offline without being connected to your source database. The AWS Schema Conversion Tool will still connect to your source database to update the schema in your project if you choose the Refresh from Database git checoption for your source database. For more information, see Updating and Refreshing Your Converted Schema (p. 40).

Clearing a Converted Schema

Until you apply the schema to your target DB instance, the AWS Schema Conversion Tool only stores

AWS Schema Conversion Tool User Guide Clearing a Converted Schema

(44)

Updating and Refreshing Your Converted

Schema

You can update the schema in your AWS Schema Conversion Tool project by choosing Refresh from

Database, as shown following.

AWS Schema Conversion Tool User Guide Updating and Refreshing Your Converted Schema

(45)

If you choose the Refresh from Database option for your source database, then the AWS Schema Conversion Tool replaces the schema in your project with the latest schema from your source database. This functionality enables you to update your project if changes have been made to the schema of your source database.

If you choose the Refresh from Database option for your target RDS DB instance, then the AWS Schema Conversion Tool replaces the schema in your project with the latest schema from your target DB instance. If you have not applied any schema to your target DB instance, then the AWS Schema Conversion Tool clears the converted schema from your project. You can then convert the schema from your source database for a "clean" target DB instance.

Saving and Applying Your Converted Schema

When the AWS Schema Conversion Tool generates converted schema (as shown in Converting Your Schema (p. 35)), it does not immediately apply converted schema to the target DB instance. Instead,

AWS Schema Conversion Tool User Guide Saving and Applying Your Schema

(46)

automatically convert. You can then run your updated scripts on your target DB instance to apply your converted schema to your target database.

To save your converted schema as SQL scripts, open the context (right-click) menu for the schema element, and choose Save as SQL, as shown following.

Applying Your Converted Schema

When you are ready to apply your converted schema to your target RDS DB instance, choose the schema element from the right panel of your project. Open the context (right-click) menu for the schema element, and choose Apply to database, as shown following.

AWS Schema Conversion Tool User Guide Applying Your Converted Schema

(47)

Important

The first time that you apply your converted schema to your target DB instance, the AWS Schema Conversion Tool adds a schema to your target DB instance. This schema implements system functions of the source database that are required when writing your converted schema to your target DB instance. The schema is named AWS_ORACLE_EXT if your source database is Oracle and AWS_SQLSERVER_EXT if your source database is Microsoft SQL Server. Do not modify this schema, or you might encounter unexpected results in the converted schema that is written to your target DB instance. When your schema is fully migrated to your target DB instance, and you no longer need the AWS Schema Conversion Tool, you can delete the AWS_ORACLE_EXT or AWS_SQLSERVER_EXT schema.

Related Topics

• What Is the AWS Schema Conversion Tool? (p. 1)

AWS Schema Conversion Tool User Guide Related Topics

(48)

BestPractices with the AWS

Schema Conversion Tool

This topic includes information on best practices and options for using the AWS Schema Conversion Tool.

Topics

• Memory Management and Performance (p. 44)

• Related Topics (p. 44)

Memory Management and Performance

You can configure the AWS Schema Conversion Tool with different memory performance settings. Increasing memory will speed up the performance of your conversion, but will use more memory resources on your desktop.

To set your memory management option, go to Settings -> Global Settings and choose the Performance

and Memory tab. Choose one of the following options:

• Fast conversion, but large memory consumption – This option optimizes for speed of the conversion, but might require more memory for the object reference cache.

• Low memory consumption, but slower conversion – This option minimizes the amount of memory used, but will result in a slower conversion. Use this option if your desktop has a limited amount of memory.

• Balance speed with memory consumption – This option optimizes provides a balance between memory use and conversion speed.

Related Topics

• What Is the AWS Schema Conversion Tool? (p. 1)

• Getting Started with the AWS Schema Conversion Tool (p. 10)

• Installing and Updating the AWS Schema Conversion Tool (p. 5)

AWS Schema Conversion Tool User Guide Memory Management and Performance

(49)

AWS Schema Conversion Tool

Reference

The following sections provide reference material for the AWS Schema Conversion Tool: • Oracle to MySQL Supported Schema Conversion (p. 45)

• SQL Server to MySQL Supported Schema Conversion (p. 74)

• Keyboard Shortcuts (p. 95)

Related Topics

• What Is the AWS Schema Conversion Tool? (p. 1)

• Getting Started with the AWS Schema Conversion Tool (p. 10)

• Installing and Updating the AWS Schema Conversion Tool (p. 5)

Oracle to MySQL Supported Schema Conversion

The following sections list the schema elements from an Oracle database and whether they are supported for automatic conversion to MySQL using the AWS Schema Conversion Tool.

AWS Schema Conversion Tool User Guide Related Topics

(50)

• Exceptions (p. 73) • Related Topics (p. 74)

Statements

Topics • SELECT (p. 46) • INSERT (p. 47) • UPDATE (p. 48) • DELETE (p. 49) • MERGE (p. 50) • TRUNCATE (p. 50) • LOCK TABLE (p. 50)

SELECT

Details Automatic-ally Conver-ted Clause

Use a stored procedure to prepare data, or rewrite your query to avoid the WITH clause.

No WITH No AS Yes SELECT Yes DISTINCT | UNIQUE | ALL

Yes select_list

You can try to include all of the fields from your table in an INTO clause.

No BULK COLLECT INTO

Yes INTO No record_name Yes FROM No @dblink No materialized view No TABLE (collection_expres-sion)

MySQL doesn't support the MODEL statement. No

MODEL

MySQL doesn't support hierarchical queries. Use a stored procedure to prepare data.

No START WITH

MySQL doesn't support hierarchical queries. Use a stored procedure to prepare data.

No CONNECT BY

No PIVOT

AWS Schema Conversion Tool User Guide Statements

(51)

Details Automatic-ally Conver-ted Clause No XML No UNPIVOT Yes WHERE Yes GROUP BY

Use a stored procedure to prepare data. No

CUBE

Use a stored procedure to prepare data. No GROUPING SETS Yes HAVING Yes ORDER BY No SIBLINGS

MySQL doesn't support NULLS FIRST and NULLS LAST. In MySQL NULL sorting, NULL values go first for an ascend-ing order and last for a descendascend-ing order. Try rewritascend-ing the ORDER BY clause with CASE.

No NULLS FIRST | NULLS LAST

Yes FOR UPDATE

Try using FOR UPDATE instead of FOR UPDATE OF. No

OF

MySQL doesn't support WAIT and NOWAIT clauses. Try using FOR UPDATE without NOWAIT.

No NOWAIT | WAIT

Try using FOR UPDATE without SKIP LOCKED. No SKIP LOCKED Yes UNION Yes INTERSECT | MINUS]

INSERT

Details Automatic-ally Conver-ted Clause

AWS Schema Conversion Tool User Guide Statements

(52)

Details

Automatic-ally Conver-ted

Clause

Perform this operation on the underlying tables instead. No subquery No WITH table_collection_ex-pression Yes column … Yes VALUES Yes subquery

To perform this operation, divide the INSERT statement with the RETURNING clause into an INSERT statement with following SELECT statements and use the same key conditions in each SELECT.You can also use the last value that was generated by AUTO_INCREMENT column in the key condition.

No RETURNING … INTO

You can add error records by inserting them into the log in the exception block. Iterate through the errors in the excep-tion block, add them to the log, and use the EXIT command when finished. No LOG ERRORS

UPDATE

Details Automatic-ally Conver-ted Clause Yes UPDATE [hint] Yes table Yes PARTITION No PARTITION FOR

Either insert data into the overlying partition, or perform a manual transformation using the UPDATE statement. No

SUBPARTITION

Perform an update on the underlying tables instead. No

VIEW

No MATERIALIZED VIEW

Perform this operation on the underlying tables instead. No subquery No WITH Yes table_collection_expression Yes SET VALUE Yes WHERE condition

AWS Schema Conversion Tool User Guide Statements

(53)

Details

Automatic-ally Conver-ted

Clause

To perform this operation, divide the UPDATE statement with the RETURNING clause into an UPDATE statement with following INSERT statements that have the specified key conditions in the SELECT part.

No RETURNING ... INTO

You can add error records by inserting them into the log in the exception block. Iterate through the errors in the excep-tion block, add them to the log, and use the EXIT command when finished.

No LOG ERRORS

Note

MySQL doesn't support FILESTREAM DATA. Perform a manual conversion to update the data in the file system file.

DELETE

Details Automatic-ally Conver-ted Clause Yes DELETE Yes FROM Yes PARTITION

Either insert data into the overlying partition, or perform a manual transformation using the DELETE statement. No

PARTITION FOR

No SUBPARTITION

Perform a manual conversion. No

VIEW

Perform a manual conversion. No

MATERIALIZED VIEW

Perform this operation on the underlying tables instead. No subquery No WITH Yes table_collection_expression

AWS Schema Conversion Tool User Guide Statements

(54)

MERGE

Details Automatic-ally Conver-ted Statement

To achieve the effect of a MERGE statement, use separate INSERT, DELETE, and UPDATE statements.

No MERGE

TRUNCATE

Details Automatic-ally Conver-ted Clause Yes TRUNCATE TABLE No PRESERVE MATERIAL-IZED VIEW LOG

No PURGE MATERIALIZED VIEW LOG No DROP STORAGE No REUSE STORAGE

LOCK TABLE

Details Automatic-ally Conver-ted Clause No PARTITION No SUBPARTITION No NOWAIT

Procedures

Details Automatic-ally Conver-ted Item

MySQL doesn't support the LOCK TABLE statement inside a stored procedure.

No LOCK TABLE

Try using INSERT in the log table. To do this, you must add code into AWS_ORACLE_EXT.PUT_LINE.

No dbms_output.put_line

Try using INSERT in the log table. To do this, you must add code into AWS_ORACLE_EXT.PUT.

No dbms_output.put

AWS Schema Conversion Tool User Guide Procedures

(55)

Flow Control

Details Automatic-ally Conver-ted Clause No GOTO

Try using a WHILE DO statement. No FORALL No EXECUTE IMMEDIATE No BULK COLLECT No RETURNING BULK COL-LECT INTO

Try rewriting variables without using labels. No LABEL

Packages

Details Automatic-ally Conver-ted Item

MySQL doesn't support EXCEPTION BLOCK in initialization blocks in packages. Try using CONTINUE HANDLER. Partial

Initialization block, BEGIN … END No User type No Global cursor No Global user exception

Functions

Topics

• Aggregate Functions (p. 52)

• Date and Time Functions (p. 53)

• Mathematical Functions (p. 55)

AWS Schema Conversion Tool User Guide Flow Control

(56)

• Analytic Functions (p. 61)

This sections lists the Microsoft SQL Server built-in functions and indicates whether AWS Schema Conversion Tool performs an automatic conversion. Where MySQL doesn't support a function, consider creating a user-defined function.

Aggregate Functions

Details Automatic-ally Conver-ted Function Yes AVG No COLLECT No CORR No CORR_* Yes COUNT No COVAR_POP No COVAR_SAMP No CUME_DIST No DENSE_RANK No FIRST No GROUP_ID No GROUPING No GROUPING_ID No LAST Yes MAX No MEDIAN Yes MIN No PERCENTILE_CONT No PERCENTILE_DISC No PERCENT_RANK No RANK No REGR_ (Linear Regres-sion) Functions No STATS_BINOMIAL_TEST No STATS_CROSSTAB No STATS_F_TEST

AWS Schema Conversion Tool User Guide Functions

(57)

Details Automatic-ally Conver-ted Function No STATS_KS_TEST No STATS_MODE No STATS_MW_TEST No STATS_ONE_WAY_AN-OVA No STATS_T_TEST_* No STATS_WSR_TEST Yes STDDEV Yes STDDEV_POP Yes STDDEV_SAMP Yes SUM

Converts to: TRUNCATE() Yes TRUNC Yes VAR_POP Yes VAR_SAMP Yes VARIANCE

Date and Time Functions

Details

Automatic-ally Conver-ted

Function

Converts to: TIMESTAMPADD(MONTH, num, date) Partial

ADD_MONTHS(date, num)

Converts to: NOW() Partial

CURRENT_DATE

Converts to: NOW() Partial

CURRENT_TIMESTAMP No DBTIMEZONE

AWS Schema Conversion Tool User Guide Functions

(58)

Details

Automatic-ally Conver-ted

Function

Converts to: SECOND(time) Partial EXTRACT(SECOND FROM time) No FROM_TZ Yes LAST_DAY(date) Yes LOCALTIMESTAMP

The maximum precision for the MySQL LOCALTIMESTAMP function is 6. If you need greater precision, create a user-defined function. Partial LOCALTIMESTAMP(prec) No MONTHS_BETWEEN(date1, date2) No NEW_TIME No NEXT_DAY No NUMTODSINTERVAL No NUMTOYMINTERVAL No ROUND (date) No SESSIONTIMEZONE No SYS_EXTRACT_UTC

Converts to: SYSDATE() Partial

SYSDATE

Converts to: CURRENT_TIMESTAMP Partial

SYSTIMESTAMP

Converts to: DATE_FORMAT()

Note that the TO_CHAR and DATE_FORMAT format strings are different. Partial TO_CHAR (datetime, format) No TO_TIMESTAMP(exp) No TO_TIMESTAMP_TZ No TO_DSINTERVAL No TO_YMINTERVAL

Converts to: DATE(datetime) Partial

TRUNC (datetime)

No TZ_OFFSET

AWS Schema Conversion Tool User Guide Functions

(59)

Mathematical Functions

Details Automatic-ally Conver-ted Function Yes ABS(num) Yes ACOS(num) Yes ASIN(num) Yes ATAN(num) Yes ATAN2(x,y)

Converts to: (exp1 & exp2) Partial BITAND(exp1, exp2) Yes CEIL(num) Yes COS(num)

Converts to: (EXP(num) + EXP(-num)) / 2 Partial COSH(num) Yes EXP(n) Yes FLOOR(num) Yes LN(num) Yes LOG(num1, num2) Yes MOD(dividend, divisor) No NANVL(n2, n1) Yes POWER(value, n)

Converts to: (n1 - n2*ROUND(n1/n2)) Partial

REMAINDER(n1, n2)

Yes ROUND (num, integer)

Yes SIGN(exp) Yes SIN(num) Yes SINH(num)

AWS Schema Conversion Tool User Guide Functions

(60)

Character (String) Functions

Details Automatic-ally Conver-ted Function Yes ASCII(str)

Converts to: CHAR(num USING ASCII) Partial CHR(num) Yes CONCAT(char1, char2) No INITCAP(string) Yes INSTR(str, substr)

Converts to: LOCATE (substr, str, pos)

This is the same as the two-argument form of INSTR(), except that the order of the arguments is reversed. Partial

INSTR(str, substr, pos)

No INSTR(str, substr, pos,

num)

Converts to: LENGTH (string) Partial

LENGTH(string)

Yes LOWER(string)

Converts to: LPAD(string, len, ' ') Partial

LPAD(string, len)

Yes LPAD(string, len, pad)

Yes LTRIM(string)

Converts to: TRIM(LEADING set FROM string) Partial LTRIM(string, set) No NLS_INITCAP No NLS_LOWER No NLS_UPPER No NLSSORT No REGEXP_INSTR No REGEXP_REPLACE No REGEXP_SUBSTR

Converts to: REPLACE(str, search, '') Partial

REPLACE(str, search)

Yes REPLACE(str, search, re-place)

Converts to: RPAD(string, len, ' ') Partial

RPAD(string, len)

Yes RPAD(string, len, pad)

Yes RTRIM(string)

Converts to: TRIM(TRAILING set FROM string) Partial

RTRIM(string, set)

AWS Schema Conversion Tool User Guide Functions

(61)

Details Automatic-ally Conver-ted Function Yes SOUNDEX(string) Yes SUBSTR(string, pos, len)

No TRANSLATE(string, from, to)

TREAT

Yes TRIM([type trim FROM]

string) Yes UPPER(string)

Conversion Functions

Details Automatic-ally Conver-ted Function No ASCIISTR(string) No BIN_TO_NUM(bit1, bit2, …) Yes CAST No CHARTOROWID No COMPOSE

Converts to: CONVERT(string USING charset) Partial CONVERT(string, charset) No DECOMPOSE No HEXTORAW No NUMTODSINTERVAL No NUMTOYMINTERVAL No RAWTOHEX

AWS Schema Conversion Tool User Guide Functions

(62)

Details

Automatic-ally Conver-ted

Function

Converts to: DATE_FORMAT(datetime, format)

Note that the TO_CHAR and DATE_FORMAT format strings are different.

Partial TO_CHAR (datetime,

format)

Converts to: FORMAT(number, decimal_digits)

In MySQL, you can use FORMAT function as well as other string functions and expressions.

Partial TO_CHAR (number,

format)

Partial TO_CLOB

Converts to: STR_TO_DATE(string, format)

Note that the TO_DATE and STR_TO_DATE format strings are different. Partial TO_DATE No TO_DSINTERVAL No TO_LOB No TO_MULTI_BYTE No TO_NCHAR (character) No TO_NCHAR (datetime) No TO_NCHAR (number) No TO_NCLOB No TO_NUMBER No TO_DSINTERVAL No TO_SINGLE_BYTE No TO_TIMESTAMP No TO_TIMESTAMP_TZ No TO_YMINTERVAL No TRANSLATE ... USING

Converts to: CHAR(string USING UCS2) Partial

UNISTR

General Comparison Functions

Details Automatic-ally Conver-ted Function Yes GREATEST(exp, exp2, …) Yes LEAST(exp, exp2, …)

AWS Schema Conversion Tool User Guide Functions

(63)

Encoding and Decoding Functions

Details Automatic-ally Conver-ted Function

Converts to: CASE expression Partial

DECODE(exp, when, then, ...) No DUMP No ORA_HASH No VSIZE

Email

MySQL doesn’t support sending e-mail. To send email, use Amazon Simple Email Service (Amazon SES).

HTTP

MySQL doesn't support sending messages to HTTP endpoints. To send messages to HTTP and HTTPS endpoints, you can use the Amazon Simple Notification Service (Amazon SNS).

SMS

MySQL doesn't support sending notifications by using SMS. To send and receive SMS notifications, you can use the Amazon Simple Notification Service (Amazon SNS).

NULL Functions

Details Automatic-ally Conver-ted Function Yes COALESCE(exp1, exp2, …) No LNNVL

Converts to: NULLIF(exp1, exp2) Partial

NULLIF(exp1, exp2)

AWS Schema Conversion Tool User Guide Functions

(64)

Details

Automatic-ally Conver-ted

Statement

Try changing the function to a stored procedure and use a table to store the results.

No RETURN resultset

Try changing the function to a stored procedure and change record-type items to separate parameters.

No RETURN record-type

Try changing the function to a stored procedure and use a table to store the results.

No TYPE .. IS TABLE OF ..

INDEX BY

Try changing the function to a stored procedure and use a table to store the results.

No TYPE .. IS TABLE OF

Try changing the function to a stored procedure and use a table to store the results.

No TYPE .. IS VARRAY(..) OF

Try changing the function to a stored procedure and use a table to store the results.

No RETURN .. PIPELINED

Try changing the function to a stored procedure and use a table to store the results.

No TYPE ... IS REF CURSOR

No object-type

Collections

Details Automatic-ally Conver-ted Item

MySQL doesn't support table type variables. Try using a table.

No TYPE .. IS TABLE OF…

MySQL doesn't support table type collection methods. Try using a table. No collection_name.First, col-lection_name.Last, tion_name.Count, collec-tion_name.Next

MySQL doesn't support the constructor for the collection type. Try using a table.

No := collection_type(...)

Arguments

Details Automatic-ally Conver-ted Item No UDT record-type No UDT collection-type No UDT object-type

AWS Schema Conversion Tool User Guide Functions

(65)

Large object Functions

Details Automatic-ally Conver-ted Function No BFILENAME

Converts to an empty string. Partial

EMPTY_BLOB

Converts to an empty string. Partial EMPTY_CLOB

Hierarchical Functions

Details Automatic-ally Conver-ted Function

Try creating a user-defined function. No

SYS_CON-NECT_BY_PATH

Analytic Functions

MySQL doesn't support analytic functions. Try creating a user-defined function.

Operators

Topics • Arithmetic Operators (p. 61) • Assignment Operator (p. 62) • Comparison Operators (p. 62) • Logical Operators (p. 62)

• String Concatenation Operator (p. 63)

• Date and time format specifiers (p. 63)

Arithmetic Operators

AWS Schema Conversion Tool User Guide Operators

(66)

Assignment Operator

Details Automatic-ally Conver-ted Clause Yes =

Comparison Operators

Details Automatic-ally Conver-ted Clause Yes > Yes < Yes >= Yes <= Yes <> Yes != Yes !< Yes !>

Logical Operators

Details Automatic-ally Conver-ted Clause Yes IN Yes NOT IN Yes ANY Yes SOME Yes ALL Yes BETWEEN x Yes EXISTS Yes LIKE Yes IS NULL Yes NOT Yes AND

AWS Schema Conversion Tool User Guide Operators

(67)

Details Automatic-ally Conver-ted Clause Yes OR

String Concatenation Operator

Details Automatic-ally Conver-ted Clause Yes ||

Date and time format specifiers

Default Conversion Automatic-ally Conver-ted Clause No YEAR %Y Partial YYYY No YYY %y Partial YY No Y No IYY %y Partial IY No I %Y Partial IYYY No RRRR No Q %m Partial MM

AWS Schema Conversion Tool User Guide Operators

(68)

Default Conversion

Automatic-ally Conver-ted

Clause

Day of the week (0=Sunday, 6=Saturday) Partial

Day of the week (1=Sunday, 7=Saturday) %W Partial DAY %e Partial DD %j Partial DDD %a Partial DY No J %h Partial HH %h Partial HH12 %H Partial HH24 %i Partial MI %S Partial SS No SSSSS No FF %p Partial AM, PM No A.M., P.M. No AD or A.D No BC or B.C. No TZD No TZH No TZM No TZR

AWS Schema Conversion Tool User Guide Operators

(69)

Data Types

Details Default Conversion Automatic-ally Conver-ted Data type

Contains the file path to the BFILE. MySQL does not support the BFILE. Because BFILE data is made up of the path to a file, you can either store a filename and create a routine that gets the file from the file system, or store the file contents as a LONGBLOB. VARCHAR(255) Partial BFILE FLOAT Yes BINARY_FLOAT DOUBLE Yes BINARY_DOUBLE LONGBLOB Yes BLOB TEXT Yes CHAR CHAR(n), CHARACTER(n) Yes CHAR(n), CHARACTER(n) VARCHAR(n) Yes CHAR(n), CHARACTER(n) LONGTEXT Yes CLOB DATETIME Yes DATE DOUBLE Yes DECIMAL, DEC DECIMAL(p,s), DEC(p,s) Yes DECIMAL(p,s), DEC(p,s) DOUBLE PRECISION Yes DOUBLE PRECISION DOUBLE Yes FLOAT DOUBLE Yes FLOAT(p) DECIMAL(38) Yes INTEGER, INT DOUBLE Yes INTERVAL YEAR TO

AWS Schema Conversion Tool User Guide Data Types

Figure

Updating...

References

Related subjects :