call us toll-free +1 855 855 3600
 
Home > Blog > Using AWS Schema Conversion Tool Extension Pack for OLTP databases

Using AWS Schema Conversion Tool Extension Pack for OLTP databases

Posted by | On | In Amazon Web Services, AWS Database Migration Service, AWS RDS Platform, Database Migration, OLTP Databases, Operational Data Management | Tags:
 

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

<table>
<tbody>
<tr>
<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>
</tr>
<tr>
<td>Mail sending</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>
</tr>
<tr>
<td>Job running</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>
</tr>
<tr>
<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>
</tr>
<tr>
<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>
</tr>
<tr>
<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>
</tr>
<tr>
<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>
</tr>
</tbody>
</table>

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.

aws-sct-migration-structure-2

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

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

aws sct 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

aws sct 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

AWS SCT 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.

Related AWS SCT Posts

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image