Last update:22-Dec-2017
NB: earlier,
SAP Advanced SQL Migration was called ‘Exodus’. it was renamed in Q4 2017. This blog post will be updated soon.
When you're trying to migrate SQL stored procedures from Oracle or Microsoft SQL Server to SAP ASE, IQ or SQL Anywhere, inevitably you'll be facing the problem that the source DBMS has particular features or syntax constructs that the target database don't have.
That may sound like a hopeless situation. However, the
SAP Exodus DBMS migration tool manages to convert SQL code in a number of cases that might seem to be too difficult to migrated successfully. Let's look at some selected examples below.
Sequences
Oracle PL/SQL applications often use sequences to generate unique numbers like primary keys, for example:
/* PL/SQL syntax */
INSERT INTO MyTable (keycol, attrib1)
VALUES (MyKeySequence.nextval, 'Hello!');
SAP ASE does not support sequences, but Exodus migrates this to identical functionality anyway:
/* ASE T-SQL syntax */
INSERT INTO MyTable (keycol, attrib1)
VALUES (dbo.sp_f_dbmtk_sequence_nextval('MIGRATED_DB','dbo.MyKeySequence'), 'Hello!')
What happens here is that the Exodus run-time component
sp_f_dbmtk_sequence_nextval() delivers the same functionality as the Oracle
nextval function. This component is an ASE SQL function that uses the
reserve_identity() built-in function, as well as a few other tricks to effectively get a full sequence implementation in ASE (the arguments
'MIGRATED_DB','dbo.MyKeySequence' are the name of the target ASE database and the name of the ASE table posing as a sequence, respectively).
When migrating to SQL Anywhere, there is no migration problem since SQL Anywhere supports sequences natively.
When migrating to IQ, you'd think that those same SQL Anywhere sequences could be used - but unfortunately sequences are not supported when inserting into an IQ table. Fortunately, Exodus provides a run-time component similar to the ASE example above (but slightly different) thus allowing sequences to be used directly with IQ tables.
Datatypes
Oracle PL/SQL has a number of datatypes that do not exist in the some of the SAP target databases.
For example, PL/SQL has an
INTERVAL datatype (2 flavours) and a date/time-with-timezone datatype. ASE, IQ do not support such datatypes natively, and SQL Anywhere supports only date/time-with-timezone.
A more challenging example is PL/SQL's
ANYDATA datatype, and MS SQL Server's
SQLVARIANT datatype. Both of these are 'type-less' and can represent any datatype.
To allow these datatypes to be migrated anyway, Exodus provides its own predefined user-defined datatypes, plus a number of stored procedures and functions that operate on these datatypes. This allows most of the functionality around these datatypes to be retained after conversion. Some additional manual editing may be needed to complete fully equivalent SQL code after conversion.
Try-catch Exception Handling
Oracle PL/SQL uses a try-catch method for handling exceptions where control transfers to a declared exception handler when an exception occurs. IQ and SQL Anywhere provide a similar exception handling method, but ASE does not. In ASE, error status should be checked explicitly after every DML statement, and action should be taken depending on the error status found. Oracle exception handlers will be jumped to automatically; moreover if a statement block or a stored procedure does not handle an exception, it is passed on the next outermost block or to the caller.
As a result, there are big differences between Oracle and ASE when it comes to exception handling. The resulting control flow, as well as the transactional aspects of implicitly rolling back in case of unhandled PL/SQL exceptions, and very different.
At first (and second) glance, it would seem impossible to convert Oracle's exception handling to ASE since the error handling approaches are so fundamentally different. Yet, that is exactly what the just-released latest version 2.5 of Exodus achieves. By generating additional SQL statements and inserting these at strategic locations in the ASE T-SQL code, we have managed to achieve nearly-identical run-time behaviour when it comes to handling exceptions.
(Please appreciate that this stuff is too complex to show as an example here)
PL/SQL Packages
A "package" is a very commonly used construct in PL/SQL.This is a named collection of stored procedures and functions, plus a number of session-specific variables that are global to the package. Even though the SAP databases do not provide a package mechanism, Exodus makes creative use of available SQL features and manages to convert PL/SQL packages to a functionally identical series of SQL objects, including the semantics around the package-global variables. Nearly all semantics are fully retained, except for some of the more rarely used features (like explicitly resetting a package).
Buffered PL/SQL output
For Sybase users, one of the first things that meets the eye in Oracle, is how output is generated. In PL/SQL this is typically done by calling
DBMS_OUTPUT.PUT_LINE() which generates a single line of output text. It may be tempting to convert this to a
PRINT statement in ASE (or
MESSAGE...TO CLIENT in IQ/SA). However, this would, strictly speaking, not be correct, since
DBMS_OUTPUT.PUT_LINE() does not actually send anything to the client. It does indeed generate a line of output, but this is stored in an internal buffer which can be read later by the client application, and displayed to the user, once control passes back to the client from the server. If the client chooses not to retrieve and display this output, it is discarded.
By default, Exodus converts such calls to an identical approach where the generated output is buffered for the session until it is read by the client. Optionally, Exodus can be configured to convert calls to
DBMS_OUTPUT.PUT_LINE() to a direct
PRINT statement.
(I'm leaving the Oracle commands
DBMS_OUTPUT.ENABLE() and
SET SERVEROUTPUT ON aside for now, but full details are included in the Exodus documentation).
%TYPE, %ROWTYPE, RECORD
It is very common in PL/SQL, and indeed highly convenient, to declare variables with the datatype of a particular table column. This is done with the
%TYPE qualifier:
MyVar MyTable.MyColumn%TYPE;
Exodus will look up the datatype of
MyTable.MyColumn, and substitute this in the variable declaration (so that you don't have to).
A similar mechanism allows defining a record whose fields resemble a table's columns. This uses the
%ROWTYPE qualifier:
MyVar2 MyTable%ROWTYPE;
However, none of the SAP databases supports the concept of a record data structure.Exodus will expand such record variables to individual variables with the correct name and datatype. For example, if the above table has 3 columns with different datatypes, the declaration above looks like this after converting to ASE T-SQL.
DECLARE @MyVar2@MyColumn BIGINT
DECLARE @MyVar2@MyColumn2 DATE
DECLARE @MyVar2@MyColumn3 VARCHAR(30)
This expansion is performed in all places where the original record variable occurs.
This same approach is used for variables that are declared as RECORD datatypes.
In summary...
We do not claim that Exodus fully automates the migration of all possible SQL constructs that may be found in a real-life Oracle or MS SQL Server-based application. But the Exodus DBMS Migration tool has certainly tackled a number of particularly challenging aspects that would otherwise have required large amounts of effort and manual work.
Bottom line: with Exodus, migrating custom applications to SAP databases has become very significantly less risky and complex.
Please contact
ExodusHelp@sap.com to discuss migration opportunities in your organization.
Also contact this same address if you are an SAP Partner and you want to
get your own copy of the Exodus migration tool.
