AWS Schema Conversion Tool
User Guide (Version 1.0)

Release Notes for the AWS Schema Conversion Tool

This section contains release notes for AWS Schema Conversion Tool, starting with version 1.0.611.

Release Notes for the AWS Schema Conversion Tool Build 632

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.632.

Source Target What's new, enhanced, or fixed
ALL ALL SCT UI - Added new tab to show errors that happen when applying scripts.
SAP-ASE ALL You can now save the source tree as SQL.
Oracle PostgreSQL or Aurora PostgreSQL
  • Now converts empty string literals to NULL everywhere: object metadata, parsable statements, scripts, SQL statements in application source code etc.

  • REPLACE now has same behavior in Oracle and PostgreSQL.

  • Now accepts more then 100 arguments, that were not allowed for functions and procedures by default.

  • Functions aws_oracle_ext.instr no longer returns wrong result.

  • Functions LEAST and GREATEST now work the same.

  • Extpack function immutable_concat_ws should no longer be used instead of standard concat_ws in the views.

  • Implemented immutable concat extpack function.

  • Public synonym now longer expanded in views.

  • Added support for dbms_application_info.set_module.

  • Now supporting previously incompatible number type variables for Error Codes.

  • Now supporting FETCH BULK COLLECT INTO.

  • Now supporting SAVEPOINT, ROLLBACK TO SAVEPOINT inside of functions.

  • Now supporting cursor with SELECT...INTO....

  • Now supporting package's collection variable as a DEFAULT value of input parameter of routine.

  • A parameter or variable's type declaration is based on a system's view column %TYPE.

  • Now supporting global nested tables of %ROWTYPE.

  • Support for packages with subtypes of %TYPE.

  • Fix: Insert into collection element field caused transformer error.

SAP ASE PostgreSQL or Aurora PostgreSQL
  • Now supporting correct conversion of multi-table UPDATE statement with unresolved temporary table.

  • Correct object resolves, when the schema name is not specified or only the database name is used.

  • Now supporting CONVERT function (Extension pack).

Netezza Redshift
  • Stored code. Transaction control improved support (COMMIT/ROLLBACK).

  • Change severity "critical" to "high" for unresolved AI 15028. Added operator /=/ processing in queries.

  • Decrease "Estimated Time to Resolve Manually" for AIs 15001, 15002, 15003.

  • Conversion of /=/ operator in sql statements.

  • Datatype mapping for quoted datatypes (e.g. "VARCHAR", "TIME", and so on) improvements.

Amazon RDS for Oracle Redshift Fix: For copy command error that occurred when loading to Redshift from RDS Oracle using SCT Data extraction agent on any Japanese environment OS. Added command "ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH'" for Oracle before extracting data.

Resolved:

  • FIX: Typo in Current project settings menu

  • FIX: Unable to convert to upper case with the mapping rule (when PostgreSQL was the target)

Release Notes for the AWS Schema Conversion Tool Build 631

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.631.

Source Target What's new, enhanced, or fixed
Oracle and OracleDW

All

Added support for count mechanism and SQL code lines count
Oracle MariaDB (10.2/10.3/10.3 SQL MODE=ORACLE)

Hierarchical queries using Oracle CONNECT BY syntax are transferred to Common Table Expressions (WITH RECURSIVE)

Oracle

MariaDB

MySQL

MySQL (Aurora)

Fixed complex join with the table appearing on both sides of the join
SAP ASE MySQL Support for CONVERT function
SAP ASE

MySQL

MySQL (Aurora)

FIX: Conversion of INSERT with multi-table SELECT-implementation and conversion of multi-table UPDATE statement with a temporary table-implementation
Microsoft SQL Server

MySQL

MySQL (Aurora)

FIX: incorrect schema and object definition when you call an object and don't specify a schema

System objects without schemas now convert as is.

SAP ASE MySQL

MySQL (Aurora)

Fixed: incorrect conversion of INSERT with multi-table SELECT

Microsoft SQL Server

PostgreSQL

Comment in try/catch block is now transferred to target

Improved support for stored procedure parameters with defaults

FIX: Removing entire code from proc when ALTER TABLE and ENABLE/DISABLE TRIGGER ALL are used

SAP ASE PostgreSQL Support CONVERT function

Oracle

PostgreSQL

Support for ProC to ECPG code conversion

Improved support for RAWTOHEX/HEXTORAW/UTL_RAW conversion

Improved support for global cursors, to allow OPEN and FETCH commands in separate stored routines. With this improvement, the cursor can be opened in one procedure, fetched in another procedure, and closed in a third procedure.

Option to switch between procedure and function for PostgreSQl 11

DBMS_XMLGEN and DBMS_XMLQUERY

Create public synonyms for stored procedures

Microsoft SQL Server

PostgreSQL

PostgreSQL (Aurora)

Improved accuracy in schema and object definition when a procedure call specifies the database name only

System objects without schemas now convert as is

Oracle

PostgreSQL

PostgreSQL (Aurora)

Improved support for global cursors, to allow OPEN and FETCH commands in separate stored routines. With this improvement, the cursor can be opened in one procedure, fetched in another procedure, and closed in a third procedure.

Column aliases from a subquery are now converted correctly.

Support for the REVERSE function

Support for parameters in an anonymous block

Added option to convert ROWID as varchar(36). Using this new option, you can create a DOMAIN for MyRowid as a varchar(36) NOT NULL, then convert the ROWID datatype and column AS MyRowid, and finally create a SEQUENCE for each column and default it to NEXTVAL().

System view emulation

Support for FORALL MERGE

Support global cursor with parametrs of %TYPE types Improvements when converting from Oracle:

Support global cursor with parametrs of %TYPE typesSupport global cursor with parametrs of %TYPE types

Support for creation of initializing function for a package without variables

AI raised on the routine with 100+ arguments

Correct conversion DECODE to CASE with NULL

Transform for recursive SQL if a column other than connecting column is needed.

Usage of %FOUND and %NOTFOUND attributes of local explicit cursors

Calls to functions with the result of a collection type using named notation

Functions with the result of a collection type and default argument values

Greenplum

Redshift

FUNCTION declaration (DROP/CREATE PROCEDURE)

Variables and constants declaration

Variable assignments and expressions

Control-of-Flow Language.FOR

Cursors

Dynamic SQL v1. (rejection with AI)

Datatype mapping Greenplum PL/pgSql- Redshift PL/pgSQL

PL/pgSQL structure (BEGIN...END, subblocks, labels)

Comments

Control-of-Flow Language. RETURN / RETURN NEXT

Control-of-Flow Language. IF-THEN-ELSE

Control-of-Flow Language. EXIT / CONTINUE in loops

Control-of-Flow Language. Simple LOOP

Control-of-Flow Language. WHILE

Common Statements. Call another Procedure/UDF

RETURNS SETOF results in Redshift functions (plpythonu)

Returns VOID value to Redshift (plythonu)

Microsoft SQL Server

Redshift

Support for CLR stored procedures

Improved support in stored code for transaction control (COMMIT, ROLLBACK)

Netezza

Redshift

Netezza procedures no longer convert to python functions

Support for assignments, SELECT into variable, procedure comments, and other additional cases.

Oracle

Redshift

Improved support for PL/SQL packages

Improved support in stored code for transaction control (COMMIT, ROLLBACK)

Teradata

Redshift

Support for transaction control (COMMIT, ROLLBACK) in stored code.

Execute stored procedures and functions

Return of result set cursors

Diagnostic statements

DDL statement support for CREATE TABLE and CREATE VOLATILE TABLE

Resolved:

  • FIX: Unable to translate SQL Server SYSDATETIMEOFFSET function to PG

  • FIX: Service Profile testconnection failing for Amazon ETL Service (SCT 626)

  • FIX: Issues converting Teradata table to Redshift

  • FIX: Global Service Profile testconnection failing for Amazon ETL Service

  • FIX: SCT Assessment Report Can't Save Multi-Byte Characters as PDF

  • FIX: DMS standalone agent now supports S3 as target for DMS local task

  • FIX: You can now see SAP ASE system tables in the object tree.

  • FIX: In conversions from SQL Server to PostgreSQL, SCT now supports comments in try/catch blocks.

Release Notes for the AWS Schema Conversion Tool Build 630

The AWS Schema Conversion Tool 1.0.630 release was merged into the AWS Schema Conversion Tool 1.0.631 release. For changes that were merged together, see Release Notes for the AWS Schema Conversion Tool Build 631.

Release Notes for the AWS Schema Conversion Tool Build 629

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.629.

New Feature or Enhancement Description

Redshift

For conversions from Netezza, stored code support for the following:

  • Stored procedures

For conversions from Microsoft SQL Server:

  • Added ability to print source code CLR stored procedure

  • OBJECT_ID

For conversions from Oracle, stored code support for the following:

  • PL/SQL packages

For conversions from Teradata:

  • Exception handling

  • Condition handling

  • DCL statements

  • DDL statements: CREATE other objects

  • DML statements: improved support

SAP ASE

Support for SAP ASE 12.5 as a source

DynamoDB

For conversions from Cassandra:

  • Migration improvements and bugfixes

  • Collection type extraction for tuple and frozen

MySQL 8

For conversions from Oracle, added support for hierarchical queries using Oracle CONNECT BY, now converting syntax to WITH Queries (CTE) including RECURSIVE modifier.

MySQL and Aurora MySQL For conversions from SAP ASE:
  • Support for user-defined messages added to the system table sysusermessages for use by any application. Use sp_addmessage to add messages to sysusermessages; use sp_getmessage to retrieve messages for use by print and raiserror.

  • Corrected wrong conversion of RAISERROR.

PostgreSQL

For conversions from Oracle, support for the following:
  • Options to ignore disabled triggers and constraints

  • Assign operation of array or global nested table of nested record

  • EXTRACT, EXTRACTVALUE functions

  • Local and global application contexts with Extension Pack, including additional object category in the object tree and rules.

  • XMLSEQUENCE, XMLELEMENT, and XMLTYPE methods

  • Improved global cursor support to convert OPEN and FETCH commands in separate stored routines. Now, the cursor can be opened in one procedure, fetched in another one, and closed in the third one.

  • Fixed: Columns aliases in subselect are no longer incorrectly converted

  • Fixed SCT Transformer Error complex join no longer shows table on both sides of the join.

For conversions from Microsoft SQL Server, support for the dynamic SQL

PostgreSQL and Aurora PostgreSQL

For conversions from Oracle, dbms_application_info.set_action can be emulated by Extension Pack. Also, where SQL%bulk_exceptions reference caused a transformer error, these exceptions should now be rejected with AI.

Resolved:

  • Triggers and Procedures can now convert from SQL Server to MySQL

  • SCT now able to converting a schemas of 6000 procedures in conversions from SQL Server to Aurora PostgreSQL

  • Fixed inability to use AWS Glue in N. Virginia region

  • SQL Server source procedures no longer get stuck during schema conversion

  • General bugfixing and improvements.

Release Notes for the AWS Schema Conversion Tool Build 628

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.628.

New Feature or Enhancement Description
Service Substitutions

For conversions from DB2 to Aurora MySQL:

  • Email sending emulation

  • File support emulation

  • Job emulation

For conversions from DB2 to Aurora PostgreSQL:

  • Email sending emulation

  • File support emulation

  • UTL_File package emulation

For conversions from Microsoft SQL Server to MySQL, PostgreSQL, Aurora MySQL, or Aurora PostgreSQL:

  • SQL Server Scheduler emulation

For conversions from Oracle to MySQL or Aurora MySQL:

  • File support emulation

  • OWA_ packages emulation

  • Oracle queuing emulation

  • HTP/HTF/OWA_* packages emulation

For conversions from Oracle to PostgreSQL or Aurora PostgreSQL:

  • File support emulation

  • OWA_ packages emulation

  • Oracle queuing emulation

  • UTL_FILE package emulation

Redshift

For conversions from Microsoft SQL Server, stored code support for the following:

  • Stored procedures and dynamic SQL

For conversions from Oracle, stored code support for the following:

  • Cursors in procedures

  • MERGE

  • EXECUTE IMMEDIATE is supported in procedures and has improved support in dynamic SQL

  • Nested subprograms in procedures

For conversions from Teradata, stored code support for the following:

  • Cursors in procedures

  • MERGE

  • QUALIFY

  • DECLARE and SET variables

SAP ASE 15.0

Support for SAP ASE 15.0 as a source

DynamoDB

For conversions from Cassandra, support for the following:
  • Collection types (SET, LIST, MAP) extraction

  • General improvements and fixes

MySQL 8

For conversions from DB2 or SAP ASE, support for case-sensitive instance

PostgreSQL 11

For conversions from DB2, support for the following:
  • Support for embedded transactions and CALL for SQL stored procedures. In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

For conversions from Microsoft SQL Server, support for the following:
  • Support for dynamic T-SQL. Support for calling stored procedures by using CALL in dynamic queries.

For conversions from Oracle, support for the following:
  • Support for SQL*Plus conversions, including procedures and dynamic SQL. Stored Procedures CALL in dynamic queries.

  • Added option to change default data type mapping for columns with PK and FK referring to a modified PK. For example, you can convert a number with scale and precision to a bigint, for performance improvement. Previously, when the number data type was migrated from Oracle to PostgreSQL, SCT defaulted the conversion to numeric in PostgreSQL. If the number in Oracle was being used with scale and precision, this conversion was okay. But, if it was an integer with a PK index on the column, converting it to numeric in PostgreSQL could cause performance issues.

  • Support for converting %TYPE to built-in datatype. SCT replaces %TYPE with new datatype, according to type mapping rules.

  • Replaced default mapping of function and procedure arguments so that NUMBER converts to NUMERIC, rather than to DOUBLE PRECISION

For conversions from SAP ASE, support for the following:

  • CREATE PROCEDURE with RETURN

  • Support for calling stored procedures by using CALL in dynamic queries

  • Support for FOREIGN KEYon partitioned tables. PostgreSQL 11 only supports foreign keys from a partitioned table to a (non-partitioned) table.

  • Support for indexes on partitioned tables. After you index the master table, PostgreSQL automatically creates an identically configured index on existing child partitions and on future partitioned tables.

  • Support for PRIMARY KEY on partitioned tables. In version 11, you can add a primary key to the master table. Doing this creates the PRIMARY KEY on all existing child tables and future partition tables. If PARTITION is added, there's no need to create a primary key manually.

  • Support for triggers on partitioned tables. After you create a trigger on the master table, PostgreSQL automatically creates the trigger on all child tables (this behavior is similar to the one for indexes).

PostgreSQL and Aurora PostgreSQL

For conversions from Sybase, UDT is converted to DOMAIN

Resolved:

  • Added support for creating an SCT report along with logical components in WQF.

  • Fix: Issue where WQF report included extraction failure action items

  • Fix: Issue when SCT not automatically picking up DMS endpoint for Aurora PostgreSQL

  • Fix: Issue when Apply to database is grayed out for multiple schemas

  • General bugfixing and improvements

Release Notes for the AWS Schema Conversion Tool Build 627

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.627.

New Feature or Enhancement Description

Support for conversions to stored procedures in Amazon Redshift

Improving support for the following features when converting stored procedures from Microsoft SQL Server Server to Amazon Redshift:

  • Improvements to DDL support

  • Temporary tables

  • PROCEDURE declaration

  • Procedure error handling

  • SET statements

  • Control-of-flow language

  • Operators

  • CURSORS

  • Arithmetic expressions with mixed types of operands

PostgreSQL 11

Improvements for converting to PostgreSQL include the following.
  • For DB2 sources:

    • DEFAULT partition feature stores tuples that don't map to any other partition. Prior to PostgreSQL 11, these rows would error out. A row that is not mapped to any partition table would be inserted in the default partition.

    • Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX

    • CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. To be able to define a procedure, the user must have the USAGE privilege on the language.

    • Once the trigger is created on the master table, it will automatically create the trigger on all child tables (this behavior is similar to the one seen for index).

    • Once the index is created on the master table, it will automatically create the index with the same configuration on all existing child partition and take care of any future partition tables as well.

    • Postgres 11 only supports foreign keys from a partitioned table to a (non-partitioned) table

    • In version 11 PRIMARY KEY can be added to the master table which will create the PRIMARY KEY on all existing child tables and future partition tables. IF PARTITION is added, no need to create primary keys manually

  • For Microsoft SQL Server sources:

    • Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX

    • In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

    • CALL syntax for SQL stored procedures

    • CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. To be able to define a procedure, the user must have the USAGE privilege on the language.

    • Once the trigger is created on the master table, it will automatically create the trigger on all child tables (this behavior is similar to the one seen for index).

    • Once the index is created on the master table, it will automatically create the index with the same configuration on all existing child partition and take care of any future partition tables as well.

    • Postgres 11 only supports foreign keys from a partitioned table to a (non-partitioned) table

    • In version 11 PRIMARY KEY can be added to the master table which will create the PRIMARY KEY on all existing child tables and future partition tables. IF PARTITION is added, no need to create primary keys manually

  • For Oracle sources:

    • In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

    • CALL syntax for SQL stored procedures

    • CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. To be able to define a procedure, the user must have the USAGE privilege on the language.

  • For SAP ASE sources:

    • CALL Syntax for SQL Stored Procedures

    • In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

    • CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. To be able to define a procedure, the user must have the USAGE privilege on the language.

    • UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions

    • The PostgreSQL 11 DEFAULT partition feature stores tuples that don't map to any other partition. Prior to PostgreSQL 11, these rows would error out. A row that is not mapped to any partition table would be inserted in the default partition.

    • Once the index is created on the master table, it will automatically create the index with the same configuration on all existing child partition and take care of any future partition tables as well.

    • Hash Partitioning – The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder

MySQL 8

SCT now supports case sensitive instances for conversions from Oracle, Microsoft SQL Server, Azure, PostgreSQL, and MySQL. SCT now offers the option to create and use database, table, and trigger names in lower case.

Improvements for converting to MySQL include the following.

  • For DB2 sources:

    • MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others

    • MySQL now supports creation of functional index key parts that index expression values rather than column values. Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values

    • MySQL supports now CTE and recursive CTE WITH (Common Table Expressions)

    • A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause. Lateral derived tables make possible certain SQL operations that cannot be done with nonlateral derived tables or that require less-efficient workarounds

    • Several existing aggregate functions now can be used as window functions.

      • AVG()

      • BIT_AND()

      • BIT_OR()

      • BIT_XOR()

      • COUNT()

      • JSON_ARRAYAGG()

      • JSON_OBJECTAGG()

      • MAX()

      • MIN()

      • STDDEV_POP()

      • STDDEV()

      • STD()

      • STDDEV_SAMP()

      • SUM()

      • VAR_POP()

      • VARIANCE()

      • VAR_SAMP()

    • MySQL supports window functions that, for each row from a query, perform a calculation using rows related to that row.

      • CUME_DIST()

      • DENSE_RANK()

      • FIRST_VALUE()

      • LAG()

      • LAST_VALUE()

      • LEAD()

      • NTH_VALUE()

      • NTILE()

      • PERCENT_RANK()

      • RANK()

      • ROW_NUMBER()

  • For Microsoft SQL Server and Azure SQL DB sources:

    • Several existing aggregate functions now can be used as window functions.

      • AVG()

      • BIT_AND()

      • BIT_OR()

      • BIT_XOR()

      • COUNT()

      • JSON_ARRAYAGG()

      • JSON_OBJECTAGG()

      • MAX()

      • MIN()

      • STDDEV_POP()

      • STDDEV()

      • STD()

      • STDDEV_SAMP()

      • SUM()

      • VAR_POP()

      • VARIANCE()

      • VAR_SAMP()

    • WITH (Common Table Expressions) – MySQL supports CTE and Recursive CTE

    • MySQL supports window functions that, for each row from a query, perform a calculation using rows related to that row.

      • CUME_DIST()

      • FIRST_VALUE()

      • LAG()

      • LAST_VALUE()

      • LEAD()

    • Lateral derived tables

    • MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others

    • MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all.

  • For Oracle sources:

    • A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause. Lateral derived tables make possible certain SQL operations that cannot be done with nonlateral derived tables or that require less-efficient workarounds

    • InnoDB supports NOWAIT and SKIP LOCKED options with SELECT ... FOR SHARE and SELECT ... FOR UPDATE locking read statements. NOWAIT causes the statement to return immediately if a requested row is locked by another transaction. SKIP LOCKED removes locked rows from the result set.

    • WITH (Common Table Expressions) – MySQL supports CTE and recursive CTE

    • Several existing aggregate functions now can be used as window functions.

      • AVG()

      • BIT_AND()

      • BIT_OR()

      • BIT_XOR()

      • COUNT()

      • JSON_ARRAYAGG()

      • JSON_OBJECTAGG()

      • MAX()

      • MIN()

      • STDDEV_POP()

      • STDDEV()

      • STD()

      • STDDEV_SAMP()

      • SUM()

      • VAR_POP()

      • VARIANCE()

      • VAR_SAMP()

    • MySQL implements regular expression support as follows.

      • NOT REGEXP

      • REGEXP

      • REGEXP_INSTR()

      • REGEXP_LIKE()

      • REGEXP_REPLACE()

      • REGEXP_SUBSTR()

      • RLIKE

    • MySQL supports window functions that, for each row from a query, perform a calculation using rows related to that row.

      • CUME_DIST()

      • DENSE_RANK()

      • FIRST_VALUE()

      • LAG()

      • LAST_VALUE()

      • LEAD()

      • NTH_VALUE()

      • NTILE()

      • PERCENT_RANK()

      • RANK()

      • ROW_NUMBER()

  • For SAP ASE sources:

    • MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others

    • MySQL now supports creation of functional index key parts that index expression values rather than column values. Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values

    • MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all.

AWS Glue

Improvements for converting to AWS Glue include the following.

  • Improved conversion of procedures parameters.

  • Ability to add quotes for special characters to the AWS Glue scripts.

  • Fixes, improvements and polishing for conversion from Teradata BTEQ to AWS Glue.

  • Improvements and fixes for conversion from Oracle to AWS Glue.

DynamoDB

Migration improvements and fixes for migrations from Cassandra to DynamoDB.

Oracle

SCT now supports INTERVAL partitioning in Oracle sources.

Resolved:

  • Added support for MySQL 8 as a destination in AWS SCT.

  • Added support for Windows Authentication in WQF.

  • Username/Password fields are no longer required for AWS DMS proxy.

  • Fix: SCT now able to read all the database objects when entire schema is selected (Oracle to Amazon Aurora PostgreSQL).

  • Fix: Physical path to AWS SCT statistics file now supported in WQF, when you are loading physical/logical components from CSV/JSON files.

  • Fix: Composite key (b,a) no longer changes to (a,b) while converting from SQL Server to Aurora MySQL.

  • Fix: View conversion error (SQL Server to PostgreSQL).

  • Fix: For conversions from SAP ASE, added ability to specify the character set on the server connection.

Release Notes for the AWS Schema Conversion Tool Build 626

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.626.

New Feature or Enhancement Description
PostgreSQL 11

PostgresSQL 11 is now supported as a target. Key features include the following:

  • Creating a "default" partition for rows that don't automatically fit into an existing partition.

  • Creating foreign keys from a partitioned table to a non-partitioned table.

  • Creating a unique index on the master table. This index manages indexes with the same configuration on existing child tables and future partitioned tables.

  • When you create a trigger on the master table, it automatically creates triggers on all child tables.

  • When you UPDATE a partition key column so that it now fits on a different partition, that row is moved to the appropriate partition.

  • Using HASH partitions by specifying a modulus to apply to the partition key. The remainder of the modulus is used to identify the partition for that row.

MySQL 8.0

MySQL 8.0 is now supported as a target. Key features include the following:

  • Using DESC in an index definition now stores key values in descending order. Previously, scanning these indexes in reverse order caused a performance penalty. Now, a descending index can be scanned efficiently (moving forward, rather than in reverse). Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

  • Using expressions as default values in data type specifications. This includes BLOB, TEXT, GEOMETRY, and JSON data types.

  • Creating functional index key parts that index expression values rather than column values. Functional key parts enable indexing of values that can't be indexed otherwise, such as JSON values.

SAP ASE 15.5

SAP ASE 15.5 is now supported as a source.

AzureSQL/MSSQL to MySQL/AuroraMySQL

You can now use identity columns in temporary tables and table-valued functions.

You can now use INSERT INTO for tables with an identity column.

Cassandra to DynamoDB

Migration improvements and bugfixes.

Adding compaction process in the wizard.

Replaced utility telnet with netcat in the wizard.

DB2 to MariaDB 10.3

(SQL MODE=ORACLE) Module routines and variables references now supported.

DB2 to MySQL/AuroraMySQL

Service substitution - support for files.

Netezza to Redshift

Table columns that contains DEFAULT 'now(0)' are now set to DEFAULT SYSDATE.

Oracle to MariaDB 10.3

(SQL MODE=ORACLE) Module routines, variable references, and using sequences now supported.

Oracle to MySQL/AuroraMySQL

Service substitution - support for files.

Oracle to AWS Glue

The built-in function for FORMAT MODELS set is now supported.

SAP ASE to MySQL /Aurora MySQL

Global variables are now supported.

SAP ASE to PostgreSQL /Aurora PostgreSQL

Global variables are now supported.

Teradata BTEQ to AWS Glue

Now supporting built-in functions for BITBYTE and PERIOD sets.

Issues Resolved:

  • Fixed internal converter errors for migrating SQL Server to MySQL.

  • Fixed issues in the Application Conversion Assessment report.

  • Fixed issues for Oracle interval partitioning.

  • Fixed and polished conversions to AWS Glue.

  • General bugfixing and improvements.

Release Notes for the AWS Schema Conversion Tool Build 625

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.625.

New Feature or Enhancement Description
Teradata BTEQ conversion You can now convert Teradata BTEQ scripts to AWS Glue.
DB2 to MariaDB 10.3 You can now use cursors with parameters.
DB2 to MariaDB 10.3 (SQL MODE=ORACLE)

For conversions from DB2 to MariaDB 10.3 using SQL MODE=ORACLE, the following are now supported:

  • %TYPE and %ROWTYPE variables

  • ROW structure variables in a local cursors

  • Declaring scalar variables

  • Declaring a default variable value

  • Converting modules to packages.

  • CREATE PROCEDURE p1 (param OUT INT)

  • CREATE PROCEDURE p1 (a IN OUT INT)

  • Using AS before function body

  • Using IS before function body.

Oracle to MariaDB 10.3

For conversions from Oracle to MariaDB 10.3, you can use %TYPE and %ROWTYPE variables.

Oracle to MariaDB 10.3 (SQL MODE=ORACLE)

For conversions from Oracle to MariaDB 10.3 using SQL MODE=ORACLE, the following are now supported:

  • %TYPE and %ROWTYPE variables

  • Package routines (procedures and functions)

  • SELECT UNIQUE

  • GLOBAL CURSORS

  • Package Initialization block BEGIN..END

  • Package without the body

  • Local variables and constants

  • Global variables and constants

MS SQL Server to MySQL/AuroraMySQL

For conversions from MS SQL Server to MySQL/AuroraMySQL, the following are now supported:

  • sp_prepexec() with dynamic SQL

  • sp_execute() with dynamic SQL

  • sp_unprepare() with dynamic SQL

SAP ASE 15.7 SAP ASE 15.7 is now supported.

Issues Resolved:

  • Added notifications for when you close a project and might lose data agent connections.

  • Resolved conversion issues for Oracle to PostgreSQL.

  • Resolved issues with SCT Oracle to PostgreSQL - to_date issue.

  • General bug fixes and improvements.

Release Notes for the AWS Schema Conversion Tool Build 624

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.624.

New Feature or Enhancement Description
Convert Oracle ETL to AWS Glue You can now convert Oracle ETL jobs to ETL based on AWS Glue to work in conjunction with Amazon Redshift.
Microsoft SQL Server, Oracle, and IBM Db2 LUW to Amazon RDS for MariaDB conversions Added support for RDS for MariaDB 10.2 and 10.3 as a target.
SAP ASE to RDS for MySQL and Amazon Aurora with MySQL compatibility conversions Added support for SAP ASE to MySQL database object conversions.

Support for Orafce extension during Oracle conversion to PostgreSQL

You can now use the Orafce extension as a destination when converting to PostgreSQL in Oracle compatibility mode.

Issues Resolved:

  • Fixed an issue while using AWS profiles in AWS SCT in the Beijing and Ningxia regions.

  • Fixed an issue in SQL Server to RDS for PostgreSQL and Aurora PostgreSQL conversions where SCT doesn't return when converting a stored procedure.

  • Fixed a virtual partition error in data extractor agents from build 623.

  • General bug fixes and improvements.

Release Notes for the AWS Schema Conversion Tool Build 623

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.623.

Note

In the following table, interpret "PostgreSQL" to mean both PostgreSQL and Amazon Aurora PostgreSQL.

New Feature or Enhancement Description

SAP ASE conversion to PostgreSQL

Added SAP ASE as a source database.

Oracle function NLSSORT emulation for conversion to PostgreSQL

The NLSSORT function is used to replace a character string with the equivalent sort string used by the linguistic sort mechanism. In the case of a binary sort; the sort string is the same as the input string. In linguistic sort; each character in a string is replaced by some other binary values.

Oracle function DBTIMEZONE emulation for conversion to PostgreSQL

The Oracle/PLSQL DBTIMEZONE function returns the database time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name.

Oracle function LNNVL emulation for conversion to PostgreSQL

The Oracle/PLSQL LNNVL function is used in the WHERE clause of a SQL statement to evaluate a condition when one of the operands may contain a NULL value.

Oracle function TO_SINGLE_BYTE emulation for conversion to PostgreSQL

The Oracle/PLSQL TO_SINGLE_BYTE function returns a character value with all of the multibyte characters converted to single-byte characters. To use this function; your database character set contains both single-byte and multibyte characters.

Oracle function TO_MULTI_BYTE emulation for conversion to PostgreSQL

The Oracle/PLSQL TO_MULTI_BYTE function returns a character value with all of the single-byte characters converted to multibyte characters. To use this function; your database character set contains both single-byte and multibyte characters

Oracle function ROUND(date [; fmt ]) emulation for conversion to PostgreSQL

The Oracle/PLSQL ROUND function returns a date rounded to a specific unit of measure.

Oracle function MEDIAN emulation for conversion to PostgreSQL

The Oracle/PLSQL MEDIAN function returns the median of an expression.

Oracle function LISTAGG emulation for conversion to PostgreSQL

The Oracle/PLSQL LISTAGG function concatenates values of the measure_column for each GROUP based on the order_by_clause.

Oracle function NANVL emulation for conversion to PostgreSQL

The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE when a NaN ("Not a Number") value is encountered. This is most commonly used to convert NaN values into either NULL or 0.

Oracle function INSTRB emulation for conversion to PostgreSQL

The Oracle/PLSQL INSTRB function returns the location of a substring in a string, using bytes instead of characters.

Oracle function SUBSTRB emulation for conversion to PostgreSQL

The Oracle/PLSQL SUBSTRB function return a portion of string; beginning at a specified position in the string. The function SUBSTRB calculates lengths using bytes to return.

Oracle function LENGTHB emulation for conversion to PostgreSQL

The Oracle/PLSQL LENGTHB function returns the length of the specified string, using bytes instead of characters.

MSSQL: Convert scripts to PostgreSQL Convert T-SQL scripts, DML, and DDL to equivalent code or components.

MSSQL: OBJECT_NAME(@@PROCID) emulation for conversion to PostgreSQL

The MS SQL Server OBJECT_NAME(@@PROCID) function returns the object identifier (ID) of the current T-SQL module.

MSSQL: OBJECT_SCHEMA_NAME (@@PROCID) emulation for conversion to PostgreSQL

The Microsoft SQL Server OBJECT_SCHEMA_NAME(@@PROCID) function returns the database schema name for schema-scoped objects.

MSSQL: Sysobject view emulation for conversion to MySQL, Aurora MySQL, or MariaDB

The sysobject view can be emulated by Ext.Pack during a conversion from MS SQL Server to MySQL, Aurora MySQL, or MariaDB.

Modified SCT "Compare mode" for DDL Replication Module for PostgreSQL

When you're comparing two PostgreSQL schemas with SCT, you no longer need to deploy the service schema.

Improved the "small tables" rule (DISTSTYLE=ALL) for Redshift

The small tables rule takes into account the number of rows in the table, rather than its size.

Improved ZSTD compression control

Depending on the settings, the SCT sets the default ZSTD compression in all columns that are not included in the SortKey (DistKey - optionally).

Issues Resolved:

  • Fixed an issue with double UTF-8 encoding in the data extractors for migrations from Netezza to Redshift.

  • Fixed an issue with unsafe YAML deserialization in Cassandra configuration files.

  • Improvements to converting from Microsoft SQL Server to PostgreSQL.

  • Fixed an issue where the connection to Cassandra cluster fails if the version is 2.1.20. SCT now supports migrating from Cassandra 2.1.20.

Release Notes for the AWS Schema Conversion Tool Build 622

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.622.

New Feature or Enhancement Description
AWS DMS agent for AWS SCT If you're using the local AWS DMS agent to migrate data to an AWS Snowball Edge device or to Amazon S3, note the following:
  • If you have existing tasks running with local DMS agent version 2.4.1, you should migrate these tasks to the latest version of the DMS agent.

  • Don't upgrade the AWS Schema Conversion Tool until after the DMS agent is upgraded.

  • The following components must use the same replication engine version number:

    • The local DMS agent.

    • The DMS instance for the remote task.

Microsoft SQL Server to PostgreSQL: Script conversion

When you use SQL Server as a source, a new item appears in the source pane: "SQL Scripts'. If you choose Actions | Load Scripts from the AWS SCT menu, you can choose one or more .sql files to convert. During conversion, AWS SCT rewrites the scripts for use with PostgreSQL. The results are shown in the target pane: "SQL Scripts".

The AWS SCT assessment report highlights any issues encountered when it converts the scripts.

Data extractors allow NULL value replacement The COPY command can replace NULLs with a different value:
  • COPY … FROM … NULL AS 'MyReplacementValue' …

  • CREATE EXTERNAL TABLE … USING (REMOTESOURCE … NULLVALUE 'MyReplacementValue' …)

Support for ZSTD compression in Redshift

Ability to choose ZSTD compression for table columns. New project settings for choosing conversion options:

  • Do not use ZSTD compression.

  • Use ZSTD compression for all columns.

  • Use ZSTD compression for all columns except Distkey and Sortkey.

Oracle data warehouse: Support for multiple files during script generation

New project settings for saving SQL scripts:

  • Single file - all SQL will be generated to a single file.

  • Multiple files - each file contains SQL statements, including ID and stage name. The scripts should be run one at a time, in ID order.

Workload Qualification Framework

The Workload Qualification Framework (WQF) is part of AWS Database Migration Service (AWS DMS). It helps you analyze and plan your migrations to AWS database services. For more information, see Creating Migration Reports with the Workload Qualification Framework.

Issues Resolved

  • Microsoft SQL Server: Unable to create assessment reports for a named instance.

  • Oracle to PostgreSQL: Error message incorrectly states that materialized views aren't supported.

  • Microsoft SQL Server to PostgreSQL: Dynamic SQL being converted incorrectly.

  • Assessment reports: PDF files containing multibyte characters can't be saved.

  • Microsoft SQL Server to PostgreSQL: Conversions fail with NullPointerException.

  • OLAP data extractors: Bug fixes for virtual partitioning. (For more information, see https://aws.amazon.com/blogs/database/use-virtual-partitioning-in-the-aws-schema-conversion-tool.)

  • Using AWS SCT to create a task in AWS DMS: Bug fixes. We recommend upgrading to AWS SCT build 622 if you need to run an AWS DMS task.

Release Notes for the AWS Schema Conversion Tool Build 621

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.621.

New Feature or Enhancement Description

Microsoft SQL Server to MySQL: Enhancements

The following date and time functions are supported by the AWS SCT Extension Pack: DATETIMEFROMPARTS, DATETIME2FROMPARTS and TIMEFROMPARTS.

The CHECKSUM function is now supported.

Microsoft SQL Server to PostgreSQL: Enhancements

The OBJECT_ID function is supported by the AWS SCT Extension Pack.

In SQL Server, you can specify ELEMENTS XSINIL in a query to generate XML elements, even for NULL values in the result set. AWS SCT recognizes the XSINIL directive when converting from SQL Server to PostgreSQL, and converts it according to PostgreSQL semantics.

AWS SCT supports the use of the NEWID function in saved SQL scripts. To enable this behavior:

  • From the AWS SCT menu bar, choose Settings | Project settings.

  • Choose Save as SQL and Apply.

  • For Source database uses "newid()" function, choose Yes.

  • Choose OK.

Saving SQL for source tree creation

You can save the SQL statements that AWS SCT generates to create the source tree. In this release, the following database engines are now supported: MySQL, PostgreSQL, and Azure.

Support for Amazon Aurora PostgreSQL 10 as a target Amazon Aurora with PostgreSQL Compatibility now supports PostgreSQL major version 10.4. AWS SCT fully supports using this new release as a target.
Netezza as a source: Enhancements

AWS SCT recognizes EXTERNAL tables in Netezza. This allows AWS SCT to access external data, as if it were stored natively in Netezza.

For faster performance, AWS SCT uses parallel compression when reading data from Netezza.

The AWS SCT extraction agent uses the Redshift COPY command to copy data from Netezza to Redshift. You can control COPY command behavior using the following parameters:

  • extractor.redshift.copying.thread.pool.size—the number of COPY commands that the agent can run simultaneously. The default is 1, meaning that the agent will run all COPY commands sequentially.

  • extractor.redshift.copy.command.count.threshold—the maximum number of COPY commands that can run at the same time. The default value is 5.

When the extraction agent reads data from a single large table, it writes the data to multiple, smaller tables in Amazon Redshift. At the end of the migration, these staging tables are consolidated into a single large table in Redshift. To limit the number of staging tables allowed in Redshift, you can modify the extractor.redshift.staging.table.count.threshold parameter. The default value is 100.

(For more information on how to set these parameters, see Configuring Extraction Agents.)

Issues Resolved:

Release Notes for the AWS Schema Conversion Tool Build 620

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.620.

New Feature or Enhancement Description

Save CREATE statements in SQL scripts

AWS SCT allows you to save SQL statements that are used for creating database objects (CREATE TABLE, CREATE VIEW, CREATE FUNCTION, and so on). You can save all of the statements in one SQL script, or save each statement in a separate SQL script. single file, or save each statement in its own SQL script.

To modify this behavior, choose Settings | Project settings from the AWS SCT menu bar, and choose Save as SQL and Apply.

Microsoft SQL Server to MySQL: Support for CHECKSUM, ISNUMERIC and NEWSEQUENTIALID functions These functions are supported by the AWS SCT Extension Pack.
Oracle to MySQL: MERGE Support

The Oracle MERGE statement is used to insert, update or delete rows, depending on whether those same rows currently exist in the table. (This operation is sometimes referred to as an "upsert".) MySQL doesn't support the MERGE statement; however, AWS SCT can migrate MERGE statements to MySQL-compatible INSERT, UPDATE and DELETE statements.

Microsoft SQL Server to MySQL: TIMESTAMP data type AWS SCT can migrate TIMESTAMP data type from SQL Server to MySQL.
Microsoft SQL Server to PostgreSQL: OPENXML functions AWS SCT can migrate OPENXML functions from SQL Server to MySQL.
Oracle to MySQL: Global cursors

Oracle supports global cursors, where a cursor defined in one program unit (stored procedure, function or trigger) can be accessed outside of the program unit where it is defined. MySQL doesn't support global cursors; however, AWS SCT provides equivalent functionality by modifying the program unit code on the MySQL target.

Oracle to MySQL: Spatial data AWS SCT can migrate spatial data, objects and functions from Oracle to MySQL.
Oracle to MySQL: TIMESTAMP WITH TIME ZONE This data type is supported by the AWS SCT Extension Pack.
User-defined LOB size with AWS Snowball You can now define the maximum size for large objects (LOBs) when using the AWS SCT DMS Agent and AWS Snowball.

Issues Resolved

  • Oracle to PostgreSQL: Minor conversion issues fixed.

  • partitions no longer drop nulls.

Release Notes for the AWS Schema Conversion Tool Build 619

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.619.

New Feature or Enhancement Description

Migration support from Apache Cassandra to Amazon DynamoDB

You can use AWS SCT with an AWS data extraction agent to migrate from Apache Cassandra to Amazon DynamoDB. For more information, see Migrating Data From Apache Cassandra to Amazon DynamoDB.

Support for Vertica 9 as a migration source

You can now use Vertica version 9 as a migration source. For more information, see Using Vertica as a Source for AWS Schema Conversion Tool.

Microsoft SQL Server to MySQL: Stored procedure RETURN value support

MySQL doesn't support the RETURN keyword in stored procedures; however, OUT parameters are supported.

Support for Azure SQL Database as a source

You can now migrate from Azure SQL Database to MySQL, PostgreSQL, or Amazon Aurora (with MySQL or PostgreSQL compatibility).

Issues Resolved

  • Microsoft SQL Server to PostgreSQL: AWS SCT supports arithmetic operations with mixed types of operands.

  • Oracle Advanced Queuing (AQ) is supported when migrating to Amazon RDS for Oracle.

Release Notes for the AWS Schema Conversion Tool Build 618

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.618.

New Feature or Enhancement Description
Dynamic SQL in string literals You can execute a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR stored procedure, scalar-valued user-defined function, or extended stored procedure. The EXECUTE statement can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set. Metadata for the result set can be defined by using the WITH RESULT SETS options.
MS SQL Server to PostgreSQL: Spatial data AWS SCT supports conversion of spatial data from MS SQL Server to PostgreSQL.
MS SQL Server to MySQL: Spatial data AWS SCT supports conversion of spatial data from MS SQL Server to MySQL.
MS SQL Server to PostgreSQL: @@ROWCOUNT AWS SCT supports the Microsoft SQL Server @@ROWCOUNT function from MS SQL Server to PostgreSQL.
Oracle to MySQL: Formatting strings for conversion function TO_CHAR The Oracle TO_CHAR function accepts a wide range of formatting characters. AWS SCT supports these formatting strings from Oracle to MySQL.
Oracle to MySQL: WITH clause support for INSERT and UPDATE statements AWS SCT supports INSERT and UPDATE statements that use the WITH clause, from Oracle to MySQL.
Oracle to MySQL: Formatting strings for conversion function TO_NUMBER The Oracle TO_NUMBER function accepts a wide range of formatting characters. AWS SCT supports these formatting strings from Oracle to MySQL.
MS SQL Server to MySQL: sp_sequence_get_range The Microsoft SQL Server sp_sequence_get_range function returns a range of sequence values from a sequence object. AWS SCT supports this function from MS SQL Server to MySQL.
MS SQL Server to PostgreSQL: PARSE AWS SCT supports the Microsoft SQL Server PARSE function, from MS SQL Server to PostgreSQL.
MS SQL Server - Saving SQL for source tree creation

You can save the SQL statements that AWS SCT generates to create the source tree.

Issues Resolved

  • SCT data extractors now emit COPY commands for Redshift.

  • Redshift: count(distinct) window function need to re-write to Redshift DENSE_RANK.

  • Conversion of queries using the Oracle FROM_TZ and EXTRACT functions are now correctly translated to Redshift-based equivalents, using extract and convert_timezone.

  • Assessment Report inconsistently reporting number of code objects on the source DB.

  • Re-write of Oracle INSTR function to Redshift built in function for more cases.

  • Added PostgreSQL 9.x - PostgreSQL 10 schema compare support.

Release Notes for the AWS Schema Conversion Tool Build 617

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.617.

New Feature or Enhancement Description

Oracle to Amazon RDS for Oracle - Oracle server level objects

AWS SCT adds support for user profiles, user roles, event schedules, and more. For more information, see Converting Oracle to Amazon RDS for Oracle.

SQL Server to Amazon RDS for SQL Server - Assessment report includes additional metrics

The AWS SCT assessment report includes information on the Amazon RDS DB instance as well as SQL Server services in use by the source database. For more information, see Converting SQL Server to Amazon RDS for SQL Server.

Oracle to Amazon RDS for Oracle - Assessment report includes additional metrics The AWS SCT assessment report includes information on the Amazon RDS DB instance as well as Oracle services in use by the source database. For more information, see Converting Oracle to Amazon RDS for Oracle.

Oracle to PostgreSQL 10 - timestamp without time zone columns

AWS SCT supports timestamp without time zone columns.

Oracle to PostgreSQL 10 - Row ID columns

You can convert ROWID pseudocolumns to data columns. For more information, see Converting Oracle ROWID to PostgreSQL.

SQL Server to PostgreSQL - Merge statement emulation

AWS SCT converts the MERGE statement when migrating to PostgreSQL. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Oracle to MySQL - SELECT and WITH clause

AWS SCT converts a WITH clause when migrating to MySQL. For more information, see Converting Oracle to Amazon RDS for MySQL or Amazon Aurora (MySQL)

SQL Server to MySQL - Extension pack

The AWS SCT extension pack emulates several functions including ISDATE, FORMAT, PATINDEX, and CONVERT.

SQL Server to PostgreSQL - user-defined table types

You can use user-defined table types to specify table structures. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Oracle - Saving SQL for source tree creation

You can save the SQL statements that AWS SCT generates to create the source tree.

Issues Resolved

  • Improvements to the assessment report for server-level objects.

  • Fixed incorrect processing of objects ending with END block.

  • Added assessment report generation date in the PDF file.

  • Added logic to script generation for multiple files on Save as SQL option.

Release Notes for the AWS Schema Conversion Tool Build 616

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.616.

New Feature or Enhancement Description

SQL Server to PostgreSQL - SUSER_SNAME support

AWS SCT adds support for converting SUSER_SNAME. For more information, see Converting SQL Server to PostgreSQL.

SQL Server to PostgreSQL - Table-valued functions support

AWS SCT adds support for converting table-valued functions. For more information, see Converting SQL Server to PostgreSQL.

Oracle to Amazon RDS for Oracle - Oracle jobs AWS SCT adds support for many types of Oracle jobs in Amazon RDS for Oracle.

Oracle to Amazon RDS for Oracle - Oracle RAC

Amazon RDS for Oracle doesn't support Oracle RAC. Consider using a Multi-AZ deployment on an Amazon RDS instance for high availability.

Oracle to Amazon RDS for Oracle - Oracle Data Guard and Active Data Guard

Amazon RDS for Oracle doesn't support Oracle Data Guard and Active Data Guard. Consider using a Multi-AZ deployment on an Amazon RDS instance for high availability.

Oracle to Amazon RDS for Oracle - ongoing replication

Amazon RDS for Oracle doesn't support ongoing replication. You can use AWS Database Migration Service if you need to have ongoing replication to a target on Amazon RDS.

Oracle to Amazon RDS for Oracle - auditing

Amazon RDS for Oracle doesn’t support Oracle Unified Auditing. Amazon RDS for Oracle supports traditional auditing and fine-grained auditing (DBMS_FGA package).

Oracle to Amazon RDS for Oracle - schedule objects

AWS SCT supports converting Oracle DBMS_SCHEDULER objects when migrating to Amazon RDS for Oracle.

SQL Server to MySQL - table-valued functions

MySQL doesn't support multi-statement table-valued functions. AWS SCT simulates table-valued functions during a conversion by creating temporary tables. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

AWS SCT assessment report updates

The AWS SCT assessment report updates include the following:

  • Shows the size of databases on an Amazon RDS DB instance

  • Shows the amount of storage used on an Amazon RDS DB instance

  • Shows the number of databases on an Amazon RDS DB instance

  • Shows if databases included in a migration are already being used for replication

SQL Server to Amazon RDS for SQL Server - Service Broker and endpoints

Amazon RDS currently does not support Service Broker or additional T-SQL endpoints that use the CREATE ENDPOINT command. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Db2 LUW to PostgreSQL 10 - partitioned tables

AWS SCT can convert Db2 LUW tables to partitioned tables in PostgreSQL 10.

Issues Resolved

  • Db2 LUW to Amazon RDS for MySQL or Amazon Aurora (MySQL). Added ability to show target SQL for several transformed related objects.

  • Db2 LUW to Amazon RDS for PostgreSQL or Amazon Aurora (PostgreSQL). Added ability to show target SQL for several transformed related objects.

  • MySQL to Amazon RDS for PostgreSQL or Amazon Aurora (PostgreSQL). Added ability to show target SQL for several transformed related objects.

  • OLAP. Added ability to show target SQL for several transformed related objects.

  • AWS SCT issue with MySQL to MySQL conversion fixed.

Release Notes for the AWS Schema Conversion Tool Build 615

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.615.

New Feature or Enhancement Description

SQL Server to MySQL - Support for MERGE statement

MySQL does not support the MERGE statement, but AWS SCT can emulate the statement by using the INSERT ON DUPLICATE KEY clause and the UPDATE FROM and DELETE FROM statements. For more information, see Converting SQL Server to MySQL.

SQL Server to PostgreSQL - Support for creating unique index names

AWS SCT gives you the option of generating unique index names if your index names are not unique. To do this, choose the option Generate unique index names in the project properties. For more information, see Converting SQL Server to PostgreSQL.

Oracle to PostgreSQL - Prevent overlapping database sequence values

For Oracle to PostgreSQL migration projects, choose the option Populate converted sequences with the last values generated on the source side in the Conversion settings tab of Project Settings. For more information, see Converting Oracle to Amazon RDS for PostgreSQL or Amazon Aurora (PostgreSQL).

Oracle to PostgreSQL - Support for PostgreSQL 10 partitioning

AWS SCT can emulate partitions and subpartitions when converting a schema from an Oracle database to a PostgreSQL database. For more information, see Converting Oracle to Amazon RDS for PostgreSQL or Amazon Aurora (PostgreSQL).

SQL Server to PostgreSQL - Support for PostgreSQL 10 partitioning

AWS SCT can emulate partitions and subpartitions when converting a schema from an SQL Server database to a PostgreSQL database. For more information, see Converting SQL Server to PostgreSQL.

SQL Server to MySQL - Support for GOTO statement

MySQL does not use a GOTO statement. When AWS SCT converts code that contains the GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement.

SQL Server to PostgreSQL - Support for GOTO statement

PostgreSQL does not use a GOTO statement. When AWS SCT converts code that contains the GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement.

Oracle to PostgreSQL - Support for GOTO statement

PostgreSQL does not use a GOTO statement. When AWS SCT converts code that contains the GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement.

DB2 LUW to PostgreSQL - Support for triggers from DB2 to PostgreSQL

AWS SCT can convert the various TRIGGER statements used with DB2 LUW. For more information, see Converting DB2 LUW to Amazon RDS for PostgreSQL or Amazon Aurora (PostgreSQL)

SQL Server to Amazon RDS for SQL Server - Support for database level triggers

AWS SCT can add database triggers to the object tree when Amazon RDS for SQL Server is the target.

SQL Server to Amazon RDS for SQL Server - Support for server-level triggers, linked servers, and SQL Server Agents

AWS SCT now supports server-level triggers, linked servers, and SQL Server Agents. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Oracle to Amazon RDS for Oracle - Support for directory objects, tablespaces, and user roles and privileges

AWS SCT can add directory objects to the object tree. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Db2 support version 10.1

AWS SCT now supports IBM Db2 LUW version 10.1.

Added support of isolated government regions

AWS SCT now supports isolated government regions.

Issues Resolved

  • AWS Profile. Working with federated credentials.

  • DB2 to PostgreSQL. Conversion of DB2 triggers.

  • OLAP Migration. Current Project Settings - Skewed Threshold Value. Changed percent support.

  • Assessment Report. Flag LOB tables without Primary Key.

  • Some UI bugs were fixed.

Release Notes for the AWS Schema Conversion Tool Build 614

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.614.

New Feature or Enhancement Description

Oracle to PostgreSQL: Assessment Report for SQL*Plus Conversion

AWS SCT can convert SQL*Plus files into PSQL. The assessment report shows how AWS SCT converted the SQL*Plus files into PSQL. For more information, see Using Oracle as a Source for AWS Schema Conversion Tool.

Aurora MySQL Compatible 5.7 version support

Added support for converting Aurora MySQL 5.7 schemas.

Db2 LUW 9.1

Added support for Db2 LUW version 9.1.

OLTP Data Migration. Data compression.

Data compression during migration is now optional when you set up a task using a Replication agent.

Oracle to Oracle RDS: DB Links support

Oracle to Oracle RDS migrations now support DB Links. For more information, see Using Oracle as a Source for AWS Schema Conversion Tool.

Oracle to PostgreSQL: SELECT INTO BULK COLLECT (VARRAY) conversion

SQL statements using BULK COLLECT (VARRAY) can now be converted when migrating between Oracle and PostgreSQL.

Oracle comments conversion

Added support for converting Oracle comments into the format used by the target database engine. For more information, see Using Oracle as a Source for AWS Schema Conversion Tool.

Emulation of Oracle system objects

Added support for converting Oracle system objects into PostgreSQL. For more information, see Using Oracle as a Source for AWS Schema Conversion Tool.

Oracle to PostgreSQL: ROWNUM converted to LIMIT

Added support for converting ROWNUM.

Microsoft SQL Server to Microsoft SQL Server RDS: BULK INSERT and OPENROWSET()

Added support converting BULK INSERT and OPENROWSET()

Microsoft SQL Server to Microsoft SQL Server RDS: Links inside storage objects

AWS SCT now supports links inside stored objects during a migration to Amazon RDS. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Microsoft SQL Server to PostgreSQL: PATINDEX

AWS SCT now supports converting PATINDEX during a migration to PostgreSQL. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Microsoft SQL Server to PostgreSQL: System objects access

SQL Server system object are now converted to objects in PostgreSQL. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Microsoft SQL Server to PostgreSQL: Inline function creation

Added support for inline functions. For more information, see Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool.

Issues Resolved

  • Type Mapping. Bug fixing and improvements.

  • OLAP Conversion. Optimization strategies. Ability to reset strategies fixed.

  • Oracle to PostgreSQL. Dynamic SQL Conversion bug fixing and improvements

  • SQL*Plus script conversion. Bug fixing and improvements

  • Oracle to PostgreSQL Conversion. Bind variables recognition fix.

  • Added ability to update server info by clicking "Update server info" on server level.

  • Netezza. Schema conversion for objects in lower-case fixed.

  • Handling some specific characters in the AWS SCT navigation tree nodes' names derived from file names

  • Some UI bugs were fixed.

Release Notes for the AWS Schema Conversion Tool Build 613

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.613.

New Feature or Enhancement Description

DB2 LUW support

Added support for migrating a DB2 database as a source. For more information, see Converting Database Schemas Using the AWS Schema Conversion Tool.

Oracle to PostgreSQL Conversion. Added SQL*Plus files conversion

You can now convert SQL*Plus files.

Snowball Tab added for OLTP Data Migration

A new Snowball tab was added for OLTP Data Migration that shows you the current status of a Snowball device for particular project. For more information, see Using Data Extraction Agents.

Oracle To PostgreSQL Conversion. Convert Oracle spatial code to PostGIS

open

Oracle To PostgreSQL Conversion. Oracle spatial code converts to PostGIS.

Schema Compare Added Oracle 10 Support

You can now run Schema Compare with Oracle 10. For more information, see Comparing Database Schemas

Oracle to PostgreSQL Conversion. Implicit typecasting of Oracle db handled in PostgreSQL

While converting from Oracle to PostgreSQL, SCT add data type casting.

SQL Server, Windows Authentication

SQL Server. Windows Authentication connection method support added.

SQL Server To PostgreSQL Conversion

Added CITEXT type support. Now you can choose it with data type mappint.

Oracle to PostgreSQL Conversion

Additional improvements of Dynamic SQL Conversion for EXECUTE IMMEDIATE and DBMS_SQL and Cursors.

Oracle to PostgreSQL Conversion

Added support of SELECT INTO BULK COLLECT Conversion for Oracle to PostgreSQL.

Oracle to PostgreSQL Conversion

Now it is possible to convert an Associative array from Oracle to PostgreSQL.

Type Mapping - Custom type mapping improvements

Added ability to select source data types based on length and precision.

AWS Profile Settings

Added ability to select default profile in AWS Profile Settings.

Greenplum to Redshift Conversion

BuiltIn SQL Functions to Redshift Scalar SQL UDF conversion added.

Issues Resolved

Date reported Description
post version 612
  • Oracle to Redshift Conversion. CAST to CHAR working, CAST to CHARACTER does not work.

  • Comments on tables aren't created in the DDL of the source Oracle DB.

  • Type mismatch isn't flagged in assessment report.

  • Oracle to PostgreSQL Conversion. Wrongly converts full-width uppercase alphabet table name to full-width lowercase alphabet table name.

  • Global Settings Window cannot be closed by clicking the OK button.

  • Oracle to Redshift. to_date and subtraction re-write gives different result.

  • Some UI bugs were fixed.

Release Notes for the AWS Schema Conversion Tool Build 612

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.612.

New Feature or Enhancement Description

Custom Data Type Mapping

You can now set up mapping rules to change the data type for storage objects. You define what data type should be changed in Mapping Rules tab.

What's New option added

A new "What's New" option under the Help menu shows you all major features that were added for this release.

Schema Compare Added Oracle 10 Support

You can now run Schema Compare with Oracle 10.

Oracle to PostgreSQL Conversion.

The migration from Oracle to PostgreSQL now supports

  • global variables

  • associative arrays

  • formatting strings for TO_NUMBER function

  • converting Dynamic SQL with DBMS_SQL package

  • converting multiple nested subprograms with global variables

Added support for loading some PostgreSQL function attributes and domain constraints

Added support for loading PostgreSQL IMMUTABLE, STABLE, and VOLATILE function attributes.

Added support for loading PostgreSQL domain constraints.

Issues Resolved

Date reported Description
post version 611
  • APPLY is recognized as a keyword for Oracle.

  • Fixed error when running Schema Compare for an Oracle-to-Oracle project.

  • General improvements based on feedback and bug fixes for Microsoft SQL Server to Microsoft SQL Server migrations.

  • DROP FOREIGN KEY CONSTRAINTS missing when using Save as SQL for Oracle to PostgreSQL migrations.

  • TO_DATE & TRUNC Function conversion bug fixes for Oracle to Redshift migrations.

  • General improvements based on feedback and bug fixes for PostgreSQL to PostgreSQL migrations.

  • General improvements based on feedback and bug fixes for MySQL to MySQL migrations.

  • General improvements based on feedback and bug fixes for Oracle to Oracle migrations.

  • Some UI bugs were fixed.

  • Rewrite NUMTOINTERVL() to Redshift interval literal for Oracle to Redshift migrations.

  • Performance optimization.

Release Notes for the AWS Schema Conversion Tool Build 611

The following table shows the features and bug fixes for the AWS Schema Conversion Tool version 1.0.611.

New Feature or Enhancement Description

Schema Compare for MySQL to MySQL

Added the ability to compare MySQL to MySQL databases. For more information, see Comparing Database Schemas.

Oracle to PostgreSQL dynamic statements conversion

Added first version of DBMS_SQL package conversion support. For more information, see Converting Dynamic SQL for Oracle to PostgreSQL Migrations.

Oracle to PostgreSQL GOTO statement conversion

PostgreSQL doesn't support the GOTO operator in functionality such as Oracle, but it can be converted using BEGIN/END or LOOP/END loop statements.

Open log file from error message

When you encounter an error, you can click on it and have it take you to the associated log file rather than having to search around on the source system for it.

Added Estimated Complexity field to PDF export of Assessment Report

The Estimated Complexity field is exported in the .pdf version of the Assessment report, but it is not included in the .csv version. For more information, see Creating and Using the Assessment Report in the AWS Schema Conversion Tool.

OLAP Data Migration. Added option to not delete files on S3 after Redshift copy

After a migration to Amazon Redshift, the agent can either keep or delete the uploaded files. For more information, see Optimizing Amazon Redshift by Using the AWS Schema Conversion Tool .

OLAP Data Migration. Added LOB migration support for Greenplum, Vertica, Netezza, and Microsoft SQL Server.

Added ability to migrate LOB columns. For more information, see Migrating LOBs to Amazon Redshift.

Added ability to see related objects for conversions such as Oracle to MySQL or Aurora for MySQL and Microsoft SQL to MySQL or Aurora for MySQL.

When AWS SCT transforms source object into multiple target objects, you can now see a full list of related objects that were created. For more information, see Finding Related Transformed Objects .

OLAP Data Extractors. Added possibility to recover agent after reinstall

During installation or configuration, you can recover the agent if port or location changed. For more information, see Hiding and Recovering Information for an AWS SCT Agent .

Ability to hide schemas in tree view

You can decide what objects and information in your schemas you want to view in tree view. For more information, see Hiding Schemas in the AWS SCT Tree View .

Supports virtual partitioning

You can now manage large non-partitioned tables by creating subtasks that create virtual partitions of the table data using filtering rules. For more information, see Using Virtual Partitioning with AWS Schema Conversion Tool.

Issues Resolved

Date reported Description
post version 608
  • OLTP Data Migration. Added the ability to refresh Agent/Task Logs.

  • Oracle to Oracle schema compare. Fixed the comparison of public synonyms.

  • Fixed the ability to hide system schema from Tree View.

  • Added a Tooltip for the tree filter. Allows user to upload .csv files with objects that needs to be filtered.

  • Fixed conversion of Oracle NUMTOINTERVAL to Redshift interval literal where applicable.

  • Oracle to Redshift Conversion. Fixed the migration of a Redshift SUBSTR with a second parameter constant (start of substr) to be a simple expression rather than a CASE statement.

  • OLAP Data Migration. Added description for virtual partitioning.