OpenVMS x86 Database Modernization with Mimer SQL and Amazon EC2

In this blog post, I will show how to migrate an Oracle Rdb database to Mimer SQL running on OpenVMS x86 in Amazon Web Services (AWS). At the time of this post, Oracle Rdb is only available on VAX, Alpha, and IA-64 platforms. Customers with Oracle Rdb workloads wishing to move to OpenVMS x86 need a solution for the migration. Mimer SQL provides that solution.

Mimer provides a set of scripts to extract the database from Oracle Rdb and prepare it for Mimer SQL. Once the database is running in OpenVMS x86, customers have the option to keep the database and applications running on OpenVMS x86 or to modernize and future-proof their platform. Modernizing involves choosing to run Mimer SQL on a modern platform such as Linux or Windows. In our scenario, you will see how to modernize the platform to Mimer SQL running on Amazon Linux 2023 in a highly available architecture.

Introduction

Mimer began as a research project at Uppsala University in Sweden in 1977 and later turned into a commercial product offering. Enterprises across the globe use Mimer SQL for their critical database workloads. The current database server runs on different platforms, including Microsoft Windows, Linux, macOS, and OpenVMS (x86 and Itanium).

Overview of solution

In this solution (Figure 1), we refactored database files that now run in OpenVMS x86 on Amazon Elastic Compute Cloud (EC2). In a previous post, Deploying OpenVMS x86 on Amazon EC2, I showed how to run OpenVMS x86 on Amazon EC2. Refactoring the database gives customers the following options:

1. With availability of source code, modify applications with embedded SQL to add compatibility with Mimer SQL by recompiling on OpenVMS x86. Customers can continue running the newly compiled application under x86 OpenVMS (Figure 1 step 1).
2. The same KVM hypervisor hosting the OpenVMS x86 guest also hosts AWS Storage Gateway – Amazon S3 File Gateway, to provide storage services to store Mimer SQL database backups (Figure 1 step 2).
3. Move the application outside of OpenVMS x86 and continue accessing the Mimer SQL database on OpenVMS x86 over TCP/1360 (Figure 1 step 3).
4. The AWS Transfer Family as an SFTP destination for the OpenVMS x86 guest provides a pathway to copy the Mimer SQL database files to an Amazon Elastic File System (Amazon EFS) where an Amazon Linux 2023 host will access them (Figure 1 step 4).

Walkthrough

In this section, we will walk through:

• The configuration of Mimer SQL on OpenVMS x86.
• The transformation of source Oracle Rdb files to Mimer SQL.
• Backing up the Mimer SQL database to Amazon Simple Storage Service (Amazon S3).
• Copy the Mimer SQL database files to Amazon EFS using the AWS Transfer Family.

After we copy the database files to Amazon EFS, we will walk through the configuration of a highly available architecture for Mimer SQL on Amazon Linux 2023.

Prerequisites

To complete the steps described in this post, you will need the following:

• An AWS Account.
• Identity and Access Management (IAM) permissions to launch AWS resources (Amazon EC2, AWS Storage Gateway, etc.).
• Mimer SQL from Mimer.
• Knowledge of OpenVMS operating system operations.
• Knowledge of Mimer SQL operations.

Step 1 – Configuring Mimer SQL on OpenVMS x86

Download Mimer SQL for OpenVMS from the Mimer website. This downloaded file is a self-extracting ZIP file. After extracting the files to a folder, run product install mimer1108e (or latest version downloaded) to load the software onto OpenVMS x86. Follow the installation steps outlined in the Mimer SQL OpenVMS Guide to complete the installation.

Step 2 – Migrating an Oracle Rdb database

Mimer provides a set of scripts to extract the data from Oracle Rdb and prepare it for use with Mimer SQL (Figure 2). These scripts are available from the Mimer article, Migration from Rdb on OpenVMS. In our example, we will use the World database from MySQL Example Databases.

1. First run the script, @unload_rdb <path to database> <schema>. The last argument is the schema name in Mimer SQL and the prefix for the generated files in the unload process. There will be one SQL schema file that creates all database objects and one text file for each Oracle Rdb table containing data.
   • The script creates these directories:
     [.unload_data] – Contain the unloaded data from Oracle Rdb.
     [.gen_sql] – Stores the generated SQL files for Mimer SQL.
     [.log] – Hold log files for the process.
   • The script runs the command RMU/EXTRACT to extract the database schema.
   • With the list of tables, the script runs RMU/UNLOAD to extract the data for each table.
2. Next run the script, @load_mimer <SYSADM password> <schema> [<Mimer SQL user> <Mimer SQL password>] [operation]. Table 1 lists these parameters and describes their function. The script will also look for files in the directory [.extra_sql]. For example, to create triggers that will not be automatically converted to Mimer SQL.The script will perform the following steps:
   • Runs sqltranslator (provided by Mimer) on the Oracle Rdb SQL schema to make it compatible with Mimer SQL.
   • Creates the Mimer SQL user if it doesn’t exist.
   • Creates a databank to store the database objects.
   • Creates the Mimer SQL schema for the migrated Oracle Rdb database.
   • Runs the translated SQL schema file using Mimer SQL.
   • Runs dbanalyzer (provided by Mimer) and applies the suggested changes on the created schema to optimize the database structure.
   • Loads each table that contains data.
   • If the file [.extra_sql]<schema>.sql exist, runs it for custom SQL, such as manually converted triggers.
   • Updates database statistics for the Mimer SQL database to ensure efficient query execution.

Table 1 – load_mimer script parameters Parameter Description

<SYSADM password>	Can be an empty string, but will prompt for password if not provided.
<schema>	Should be the same as ‘<name of database>’ in the unload step.
<Mimer SQL user>	A database user that will be created if it does not exist. If omitted, a default user named ‘<mimeruser>’ is used.
[operation]	If specified, only do part of the migration. Valid values are: ALL, CREATE, LOAD, and CONTINUE_LOAD

Step 3 – Recompile applications for Mimer SQL on OpenVMS x86

If the applications were written and compiled on OpenVMS versions other than x86, they will need to be recompiled on the OpenVMS x86 platform. In the following example (Figure 1.1) a C program was recompiled with embedded SQL to run the following query against the World database.

exec sql DECLARE MYCURSOR CURSOR FOR
SELECT Name, Population
FROM country
ORDER BY Population DESC
FETCH First 3 ROWS ONLY;

If the goal is to move to a modern platform such as Linux, you can install Mimer SQL on Amazon Linux. Download Mimer SQL for Linux from the Mimer website and follow the instructions in the article Installing Mimer SQL on Linux. You can use the same code from OpenVMS to recompile on Amazon Linux. At this point, you have the option is to keep the database running in OpenVMS x86 and point to it from the Amazon Linux host by editing the Mimer SQLHOSTS file (/etc/sqlhosts) and adding a REMOTE entry. For more information about the SQLHOSTS file see the article, The SQLHOSTS File on VMS and Linux.

From the Amazon Linux host we then run the newly compiled application which yields these results.

$ ./example **** (password)
Name                 Population
==================== ==========
China                1277558000
India                1013662000
United States         278357000

Testers then verify these produce the same results as when run from the OpenVMS x86 host.

Step 4 – Backing up a Mimer SQL database to Amazon S3

Follow the steps outlined in the previous post, Deploying OpenVMS x86 on Amazon EC2 to create an Amazon S3 File Gateway. In our example, the Amazon S3 bucket and NFS file share have the same name, sgw-openvms (Figure 1 step 2). Mount the file gateway in OpenVMS using the following command:
$ tcpip mount dnfs0: /path=”/sgw-openvms” /host=<Amazon S3 File Gateway host>
(where dnfs0: selects the next available device).

We can now create an online backup of the Mimer SQL database using these commands. Refer to the following documentation from Mimer SQL, CREATE BACKUP.
SQL>START BACKUP;
SQL>CREATE BACKUP IN 'world.bak' for DATABANK world;
SQL>CREATE BACKUP in 'world-log.bak' for DATABANK logdb;
SQL>CREATE BACKUP in 'world-sys.bak' for DATABANK sysdb;
SQL>CREATE BACKUP in 'world-tra.bak' for DATABANK transdb;
SQL>CREATE BACKUP in 'world-sql.bak' for DATABANK sqldb;
SQL>COMMIT BACKUP;
SQL>EXIT;

Copy the backup files to the Amazon S3 File Gateway through the NFS share. Once the files are in Amazon S3 (Figure 3), you can manage the lifecycle of the objects for long-term archival.

Step 5 – Transferring a Mimer SQL database to Amazon EFS

We will use the AWS Transfer Family (Figure 1 step 4) to copy the Mimer SQL database files directly to an Amazon EFS network share. We will first stop the database on the OpenVMS host with the command mimcontrol /stop world. Once stopped, we will use sftp on the OpenVMS host to copy the files.

STA150>sftp [email protected] [email protected]'s password:
Connected to ftp.example.com
sftp> put *

We can now see the Mimer SQL database files in the Amazon EFS share mounted in the Amazon EC2 instance.

$ ls
logdb.dbf mimer.log multidefs.dat sqldb.dbf sysdb110.dbf transdb.dbf world.dbf

Add the new database directory into /etc/sqlhosts. In this case, the database files are located on a mounted Amazon EFS file share.
LOCAL:
-- Database           Path
-- ------------------ -----------------------
world              /mnt/efs/mimer/world

Next start the copied database with the mimcontrol command.

$ sudo mimcontrol -s world
2025-02-26 21:43:46.12 <Information>
======================================
Mimer SQL 11.0.8E Nov 13 2024 Rev 46583
Mimer SQL Experience server for database WORLD STARTED at /mnt/efs/mimer/world

We now have our database running on the Amazon Linux host.

Step 6 – Modernization with Amazon Linux 2023 and Mimer SQL

To provide high availability for our Mimer SQL database, we will use a combination of AWS services (Figure 5). Although we have shared storage between the two Amazon Linux instances, only one Mimer SQL instance can be active at a time accessing the same database. Therefore, Mimer SQL works in an active/passive mode.

1. The Mimer SQL database files are on Amazon EFS which allows you to scale workloads on-demand to petabytes of storage and gigabytes per second of throughput out of the box. You can reliably access your files with a fully managed file system designed for 11 9s of durability and up to 4 9s of availability.
2. Two Amazon EC2 instances running Amazon Linux 2023 in separate availability zones have the Amazon EFS file system is mounted.
3. A Network Load Balancer (NLB) serves TCP/1360 (Mimer SQL port) traffic to the Amazon EC2 instances.
4. Amazon CloudWatch monitors the metric UnHealthyHostCount from the NLB. Since we are in an active/passive mode, a CloudWatch Alarm is triggered if the UnHealthyHostCount is greater than 1.
5. This alarm sends an email to an email account using Amazon Simple Notification Service (SNS).
6. An Amazon EventBridge rule matches the pattern of the CloudWatch Alarm (Figure 4). The rule has a target of an AWS Systems Manager.

   

   Figure 4 – Amazon EventBridge pattern

7. AWS Systems Manager executes a Run Command to start the Mimer SQL database on the passive node. The instance to run the command against is determined by the Amazon EC2 tag with the key: MimerSQLState and the value: Passive.

At this point, the failure on the previously active instance is investigated. A manual failover back to the primary node can be accomplished or reversing the active and passive instances by switching the active/passive Amazon EC2 tag.

Cleaning up

If you have deployed any AWS resources by following the solution outlined in this blog, they will incur costs. To avoid any ongoing future charges, delete any resources like Amazon EC2 instances, EFS Filesystems, NLBs, or Amazon S3 buckets that you no longer need.

Conclusion

In this post, we showed how legacy databases running on Oracle Rdb in OpenVMS can be modernized using Mimer SQL. Once running in Mimer SQL, you can continue running on OpenVMS x86 or move to a modern platform like Amazon Linux. We saw several integration points between Mimer SQL and AWS services. Once migrated to the AWS Cloud, there are many architectural and integration possibilities. For more information about Mimer SQL reach out to [email protected].