Using AWS Schema Conversion Tool Extension Pack

23 Mar 2017 Bill Ramos Amazon Web Services, AWS Database Migration Service, AWS RDS Platform, Database Migration, OLTP Databases, Operational Data Management
Tags: AWS Schema Conversion Tool

Running your database migration projects, you may have already noticed that AWS Schema Conversion Tool (SCT) automatically generates an additional schema called extension pack. This schema emulates the system functions and specific features of the source database in your target DB instance.

In the following video, we demonstrate how to apply this extension pack to the OLTP database. And in our future blog posts we will talk about working with extension pack for data warehouses.

Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer.

Continue reading to learn more about the extension pack features.

Background on the Extension Pack

SCT cannot successfully convert all system functions of the source database. So, in order to convert the original code, you need to emulate the behavior of the functions that were not originally converted. SCT creates the extension pack to solve this issue.

Let’s discover the unsupported functions that SCT places into the extension pack.

Oracle Extension Pack

Feature	Description	Extension pack desсription
Mail sending	Oracle mail sending using UTL_SMTP package.	Extension Pack Wizard for MySQL, Aurora MySQL and PostgreSQL creates mail-sending function under Amazon Lambda service and provides necessary interface for SCT conversion process.
Job running	Oracle job running with DBMS_JOB package.	Extension Pack Wizard for MySQL, Aurora MySQL and PostgreSQL creates job-running function under Amazon Lambda service and provides necessary interface for SCT conversion process.
Package variables and constants	Working with Oracle package variables.	Extension Pack Wizard for MySQL, Aurora MySQL and PostgreSQL creates some functions that provide necessary interface for SCT conversion process.
Methods for collection datatypes	Methods for variables of collection datatype.	Extension Pack Wizard for PostgreSQL creates some functions that provide necessary interface for SCT conversion process.
Other system functions	GETROMANNUMBER, INITCAP_CHAR, INITCAP_LONGTEXT, INITCAP_TEXT, INITCAP_VARCHAR, MONTHS_BETWEEN, NEXT_DAY, DBMS_OUTPUT.PUT, DBMS_OUTPUT.PUT_LINE, RAISE_APPLICATION_ERROR, ROUND, TO_CHAR_DATE, TO_CHAR_NUMBER, TO_CLOB, TO_DATE, TO_LOB, TO_NCHAR, TO_NUMBER, TRANSLATE_CHAR, TRANSLATE_LONGTEXT, TRANSLATE_TEXT, TRANSLATE_VARCHAR, UNISTR, WIDTH_BUCKET	Extension Pack for MySQL and Aurora MySQL emulates these functions.
Other system functions	ADD_MONTHS, ASCIISTR, CHARTOROWID, FROM_TZ, INSTR, LAST_DAY, months_between, NEXT_DAY, sys_guid, sysdate, TO_CHAR, TO_DATE, TO_NUMBER, trunc, UNISTR	Extension Pack for PostgreSQL emulates these functions.

SQL Server Extension Pack

Feature	Description	Extension pack desсription
Mail sending	SQL Server mail sending using sp_send_dbmail procedure	Extension Pack Wizard for MySQL, Aurora MySQL and PostgreSQL creates mail-sending function under Amazon Lambda service and provides necessary interface for SCT conversion process.
Job running	SQL Server job management using sp_add_job and other procedures	Extension Pack Wizard for MySQL, Aurora MySQL and PostgreSQL creates job-running function under Amazon Lambda service and provides necessary interface for SCT conversion process.
Statement level triggers	Emulation of tables INSERTED/DELETED inside statement-level triggers	Extension Pack Wizard for PostgreSQL creates some functions that provide necessary interface for SCT conversion process.
Other system functions	PRINT, STR1, STR2, STR3, STUFF1, SUBSTRING1	Extension Pack for MySQL and Aurora MySQL emulates these functions.
Other system functions	isdate, isnumeric, istime, newid, RAND, ROUND3, STRPOS3	Extension Pack for PostgreSQL emulates these functions.

PostgreSQL Extension Pack

Feature	Description	Extension pack desсription
System functions	width_bucket, overlay, btrim, initcap, left, ltrim, right, split_part, substr, translate, date_trunc, to_char, to_number, xmlcomment	Extension Pack for MySQL and Aurora MySQL emulates these functions.

MySQL Extension Pack

Feature	Description	Extension pack desсription
System functions	crc32, addtime, benchmark, bin, bit_count, char, date_add, date_format, date_sub, elt, export_set, extract, field, find_in_set, from_days, get_format, hex, hexb, inet_aton, inet_atonb, inet_ntoa, inet6_aton, einsert, last_day, ln, locate, log(arg numeric), log(arg1 numeric,arg2 numeric), log10, log2, make_set, makedate, maketime, mod, oct, period_add, period_diff, rand, sqrt, str_to_date, strcmp, substr, substr, substring_index, subtime, time_format, time_to_sec, timestampdiff, trim, unhex, unhexb	Extension Pack for PostgreSQL emulates these functions.

Installing the Extension Pack

AWS Schema Conversion Tool generates the extension pack schema automatically and includes the full set of functions, not just the functions that you used in the source code. This set of functions depends on the type of source and target database. You can find the extension pack schema by its name, according to your source database type:

Microsoft SQL Server: aws_sqlserver_ext;
MySQL: aws_mysql_ext;
Oracle: aws_oracle_ext;
PostgreSQL: aws_postgresql_ext.

When you apply your converted schema to the target database, SCT automatically creates the extension pack schema in your target DB instance. However, you may wish to install the extension pack schema manually (for example, if you have accidentally deleted the schema or its functions from the database or you simply want to use some features implemented in this extension pack schema). Select the extension pack schema and press the ‘Apply to database’ button to install it manually to the target environment. You can edit the SQL code of the extension pack functions, it is as easy as editing the converted code.

Please note that SCT creates a special hidden schema with the similar name to the extension pack schema with _data suffix. This schema contains metadata of the main extension pack schema. SCT protects the metadata in this hidden schema, so you can’t unintentionally edit or accidentally delete it. SCT doesn’t display it in the metadata tree, but you can access this schema using other utilities. You should consider these special metadata schemas when deleting the extension pack from your target database instance.

Extension Pack Wizard

Consider migration of an on-premise database and its services to the Amazon cloud. In this case, you get the combination of Amazon RDS and Lambda functions to operate with the database data. Also, you may use the Data Migration Service to transfer the data itself. SCT helps to manage all these tasks in one place, but you have to configure them correctly.

For example, you may use AWS Lambda functions to emulate Oracle’s send email calls either Microsoft SQL Server jobs that use a job scheduler or some other features. Thus, you may need to specify the mandatory settings to run AWS Lambda functions from the extension pack in the target database instance.

In this case, you will need to go through the Extension Pack Master. Select the corresponding extension pack schema and press the ‘Apply Extension Pack’ button from the context menu. This option launches the extension pack wizard that includes 4 key steps:

1
AWS Services Settings

Provide credentials to connect to your AWS account to configure it for the run-time environment. You can use the AWS Data Migration Service profile here. You may need to go to the SCT Global Settings to create the AWS Service profile or use the project settings to switch to a different profile.

2
Email Sending Service

On the next step, you have to specify the AWS Lambda function for email service. You can either create a new Lambda function or select the existing one. If your source Oracle code includes the send email calls that use UTL_SMTP, you need to set up the Lambda function to send emails.

3
Job Emulation Service

Now you should create a new AWS Lambda function for the scheduling service. To do so, you have to specify database login and password as well as a path to the folder containing Python driver. If you already have a Lambda function, you have to configure it from the AWS Console.

4
Functions Emulation

Press ‘Create Extension Pack’ button in the final step to create the corresponding schema in your target database environment.

Observations

Extension packs in SCT offer quite a wide range of emulated functions and procedures. We regularly use extension packs provided by SCT to simplify code conversion during database migration projects. Although we have noticed several issues you should pay attention to while using the extension packs.

AWS regularly releases new SCT versions and updates existing extension packs. When this happens, SCT automatically overwrites the existing extension packs into your target database once you apply new schemas to the target instance. Hence, you get the latest set of functions but all the changes you have previously made to the extension pack functions (in case you have altered them) will be dismissed.
You may want to remove the unused functions or procedures from the extension pack. You can do this the same way you edit the SQL code of those functions. Keep in mind the previous item: once AWS releases an update for extension pack, all your changes will be buried.
We have found out that adding user functions to the existing extension pack might be very handy. You cannot do this in the current version of the AWS SCT.
In addition, you may optimize your converted code by using the SQL code of the extension pack functions instead of calling them. This approach will increase performance and productivity of your target database instance, but the code will become less readable.

Please check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your migration to Amazon Redshift.

Blog: Using AWS Schema Conversion Tool Extension Pack for OLTP databases