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
<td style="text-align: center;"><strong>Feature</strong></td>
<td style="text-align: center;"><strong>Description</strong></td>
<td style="text-align: center;"><strong>Extension pack desсription</strong></td>
<td>Oracle mail sending using UTL_SMTP package.</td>
<td>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.</td>
<td>Oracle job running with DBMS_JOB package.</td>
<td>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.</td>
<td>Package variables and constants</td>
<td>Working with Oracle package variables.</td>
<td>Extension Pack Wizard for MySQL, Aurora MySQL and PostgreSQL creates some functions that provide necessary interface for SCT conversion process.</td>
<td>Methods for collection datatypes</td>
<td>Methods for variables of collection datatype.</td>
<td>Extension Pack Wizard for PostgreSQL creates some functions that provide necessary interface for SCT conversion process.</td>
<td rowspan="2">Other system functions</td>
<td>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</td>
<td>Extension Pack for MySQL and Aurora MySQL emulates these functions.</td>
<td>ADD_MONTHS, ASCIISTR, CHARTOROWID, FROM_TZ, INSTR, LAST_DAY, months_between, NEXT_DAY, sys_guid, sysdate, TO_CHAR, TO_DATE, TO_NUMBER, trunc, UNISTR</td>
<td>Extension Pack for PostgreSQL emulates these functions.</td>
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.|
|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:
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.
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.
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.
Press ‘Create Extension Pack’ button in the final step to create the corresponding schema in your target database environment.
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.
Related AWS SCT Posts
- Migrating an Oracle to MySQL Database using the AWS Schema Conversion Tool
- AWS Schema Conversion Tool: Offline mode
- AWS Schema Conversion Tool: Connecting to Oracle Source Database
- Selecting target database type for migration using Assessment Report in AWS Schema Conversion Tool
- Troubleshooting database migration issues using AWS Schema Conversion Tool Assessment Report
- Converting Objects during Database Migration using AWS Schema Conversion Tool
- Applying Converted Code to Target Database
- General Approach to Migration of Data Warehouses to Amazon Redshift
- AWS Schema Conversion Tool: Specific Features of Migrating Data Warehouses to Redshift
- First look at AWS Schema Conversion Tool with tighter DMS integration
- Using AWS Schema Conversion Tool for Application Conversion
- Optimizing your Redshift Storage with AWS Schema Conversion Tool
- Extracting Data from Warehouses with Data Migration Agents in AWS Schema Conversion Tool
- Using AWS Schema Conversion Tool Extension Pack for Data Warehouses