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.
It's been longer than I wanted before publishing this blog post, but it's been busy getting the new version of Exodus ready (that's Exodus v.2.5, will cover that in a subsequent blog).
In the
previous post in this series about the
Exodus migration tool, we looked at some simple examples of how Exodus converts SQL code from a non-SAP SQL dialect to the SQL flavour of one of the SAP databases. In many cases, syntax differences can be compensated for by the Exodus migration tool.
It gets more interesting when we consider semantic differences between the SQL dialects. Let's look at two examples that are highly likely to occur in practice.
Numeric division
Consider the following PL/SQL statement, and see if you can predict what the correct result will be:
SELECT 1/10 INTO my_var FROM DUAL;
In Oracle, the result of this division will be 0.1. But in SAP ASE, SAP IQ and SAP SQL Anywhere, the result will be 0. This is because these databases use different ways of determining the datatype of the result of an expression.
Clearly, this is a problem when migrating from Oracle since the same SQL expression can easily produce a different result. For this reason, Exodus identifies such divisions and ensures the result in the SAP databases is 0.1, like in Oracle. It does this by adding terms to the expression that force the datatype to be a number with decimals rather than an integer:
/*
ASE T-SQL syntax */
SELECT @MY_var = (1*1.0)/(1.0*10)
Each occurrence of such a division is also flagged separately by Exodus, and it is recommended to verify the correctness of the resulting expressions, as well as the datatypes of variables such an expression is assigned to.
The empty string
Another example of something that looks innocent, but can be nasty, is the infamous 'empty string'.
In most databases,
'' (=two single quotes) denotes a zero-length empty string. However, ASE is special in that the empty string is actually not empty, but evaluates to a single space. Exactly why ASE behaves this way will probably remain a mystery -- it's just always been this way.
Regardless, this is a fact that needs to be taken into consideration when migrating: also here, converted SQL statements can easily produce different results due to this semantic difference between source and target database.
When migrating to ASE, Exodus handles this migration issue by replacing all empty strings by
NULL in the converted ASE SQL code. This will almost always produce the same result as in the original PL/SQL code that specified an empty string.
For example, consider these PL/SQL statements:
/* PL/SQL syntax */
my_var2 := '';
my_var3 := my_var2 || 'abcde';
my_var4 := substr(my_var3,4,1);
Please observe that the resulting value in variable
my_var4 is a one-character string
'd'.
Now, Exodus converts this code as follows to ASE T-SQL syntax:
/* ASE T-SQL syntax */
SET @MY_var2 = NULL /* ORIGSQL: '' */
SET @MY_var3 = @MY_var2 || 'abcde'
SET @MY_var4 = SUBSTRING(@my_var3,4,1)
These ASE T-SQL statements produce the same result as the original PL/SQL code. If the empty string had not been replaced by NULL, then the result would have been
'c' instead of
'd' (this is left as an exercise to the reader to verify).
(BTW: IQ and SQL Anywhere have the common semantics for an empty string, i.e. equivalent to NULL, so this adjustment does not apply when migrating to those databases).
In summary, semantic differences between SQL dialect need to be taken into account when migrating existing SQL code.
The Exodus DBMS migration tool tries to help reduce the complexity of the migration by minimizing the risk of ending up with SQL code that produces different results than in the original application.
