Considerations when Migrating from Oracle, MSSQL etc to IRIS

Migrating from Oracle, MSSQL, or other purely relational database systems to a multimodel InterSystems IRIS is a strategic decision that requires careful planning and execution. While this transition offers significant benefits, including enhanced performance, scalability, and support for modern architectures, it also comes with challenges. In this article I will highlight some of the considerations connected to coding to ensure a successful migration. I will leave everything connected to an actual migration of structures and data outside the scope of this article.

First, when you're considering migrating to a different database system, you need to understand your business logic, whether it's on the side of the application (application server) or the database server. Basically, where do you have your SQL statements that you will need to potentially rewrite? 

When your application logic heavily relies on SQL executed directly within the application code (rather than within stored procedures or triggers in the database), migrating from relational DB to InterSystems IRIS requires a careful examination of your SQL statements. Let's look at some of the most important factors you need to think about.

1. SQL dialect differences. IRIS SQL supports SQL-92 standard. It doesn't mean that some more modern features are not implemented. It just means that you need to check beforehand. For example, the window functions appeared in SQL:2003, but you still can write them in IRIS:

--window function
select id, rating
  from (select a.id, 
               r.rating, 
               avg(r.rating) over () as avg_rating 
          from SQLUSER.Actor a join SQLUser.Review r on a.id = r.Reviews) as sub 
 where rating > avg_rating

At the same time, new complex datatypes, like XML, JSON, Arrays, and Geographic datatypes, are not supported. So the following query

SELECT a.id, 
       a.firstname, 
       ARRAY_AGG(r.rating) AS ratings 
  FROM SQLUSER.Actor a LEFT JOIN SQLUser.Review r ON a.id = r.Reviews 
GROUP BY  a.firstname

will return an error: ERROR #5540: SQLCODE: -359 Message: User defined SQL function 'SQLUSER.ARRAY_AGG' does not exist

But it's not the end of the world. There are a lot of built-in functions that will allow you to rewrite the queries so that you get the expected result.

2. Built-in functions. Different DBMSs have different built-in functions. Thus, you need to understand how they correspond to the ones available in IRIS. Here are several examples of what I'm talking about, functions used in Oracle and their equivalents in IRIS:

When your primary SQL logic resides within a database (e.g., stored procedures, triggers, views), migrating to InterSystems IRIS requires a different approach. Here are some of the considerations:

1. Database Object Migration
   1. All the Stored Procedures have to be rewritten using ObjectScript. This may also be a good time to change to the object model as you will get a table anyway when you create a class. However, working with classes will allow you to write methods (that can be called as stored procedures) and use the full power of the object-oriented paradigm. 
   2. Triggers, Indexes, and Views are all supported by IRIS. You can even leave your Views as they are if the table columns remain the same if they don't use any of the unsupported functions/syntax (see the previous point). 
2. Migration of Definitions is also significant and may pose some challenges. First, you must carefully match the data types from your previous DB to IRIS, especially if you're using new complex types. Also, having more flexibility with indexes, you may want to redefine them differently.

Here are some things you need to consider when deciding to migrate to InterSystems IRIS from a different relational database. It is a strategic decision that can unlock significant benefits, including improved scalability, performance, and efficiency. However, careful planning is crucial to ensure a seamless transition and to address compatibility, data transformation, and application refactoring needs.