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.
Of all the challenges to overcome when migrating SQL code from, say, Oracle, to, say, SAP ASE, you would probably not think that it's important whether the converted SQL statements are in uppercase or in lowercase. But as you'll see below, in fact it is important, and the SAP Exodus DBMS migration tool takes care of this.
SQL statements want to look pretty too!
But first, one of the noticable features of the Exodus-generated SQL code is that it is quite nicely formatted. Indeed, Exodus reformats and re-indents the SQL code to make it look better and more standardized. In most cases, the output is well-formatted even if the SQL code used as input was looking like a mess.
Here's an example of some Oracle PL/SQL:
p_total := 0; p_Error_Code := -1; p_error_text = 'n/a';
if p_flag = 'Y' then
select c_name, C_ID into v_NAME, v_id from CUST_TB where c_id is not null order by C_date;
end if;
And this is what Exodus generates after conversion to ASE T-SQL. Also note how all identifiers are formatted uniformly in lowercase (more on that below).
SET @p_total = 0
SET @p_error_code = -1
SET @p_error_text = 'n/a'
IF @p_flag = 'Y'
BEGIN
SELECT
@v_name = c_name ,
@v_id = c_id
FROM
cust_tb
WHERE
c_id IS NOT NULL
ORDER BY
c_date
END
Why uppercase/lowercase matters
While consistently formatted and indented SQL is certainly nice to have, it has no impact on the functionality or correctness of the generated SQL code.
There is however another seemingly innocent cosmetic aspect that is in fact quite important for correctness. This concerns the uppercase/lowercase spelling of identifiers.
The issue of uppercase/lowercase matters because some databases, like Oracle or DB2, are case-insensitive for identifiers. This means that
mytable,
MyTable and
MYTABLE all refer to one and the same object.
However, other databases such as SAP ASE and SAP IQ are case-sensitive for identifiers by default (and their case-sensitivity for identifiers is coupled to case-sensitivity for data comparison, so you can't simply configure ASE or IQ as case-insensitive). This means that
mytable,
MyTable and
MYTABLE refer to three different objects as far as ASE is concerned.
Consequently, when converting SQL code from Oracle, syntax errors will result (
'object mytable not found') if the original developers were not sufficiently disciplined in following a consistent spelling in their Oracle coding (having seen lots of Oracle PL/SQL code from a wide range of customer applications I can tell you that usually, those developers were usually not so disciplined).
The Exodus migration tool aims to compensate for this, and ensure that all identifiers are uniformly spelled. For this, Exodus can format identifiers according to user-configurable preferences, thus eliminating syntax errors due to uppercase/lowercase mismatches in the converted SQL code (keep reading for details).
Exodus will also perform a spelling consistency analysis and report how consistently identifiers have been spelled, and whether there's the overall tendency is towards uppercase or lowercase. The Exodus user can use this information to pick an identifier case mapping option that is closest to the original SQL code.
Why uppercase/lowercase matters even when it isn't necessary
It is worth noting that identifier case mapping is still relevant even when converting to a target DBMS which is case-insensitive for identifiers (like SQL Anywhere). This is because identifiers are stored in the Oracle database catalogs in uppercase, independently of how they were originally specified (for brevity, I'm ignoring discussing delimited identifiers for now).
In such a situation, the target DBMS will not raise any syntax errors due to uppercase/lowercase mismatches, so you could conclude there is no need for Exodus to perform any case mapping on the identifiers.
However, the Exodus-generated DDL statements to recreate the schema in the target database are based on information extracted from the source DBMS's catalogs. Since these identifiers are stored in uppercase in the Oracle catalogs, the converted table and column names will therefore be in uppercase too:
User's original Oracle DDL:
CREATE TABLE MyTable (MyKey NUMBER, MyAttributes VARCHAR2(100))
- Identifiers are stored in Oracle catalogs as MYTABLE, MYKEY, MYATTRIBUTES
Exodus-generated DDL statement:
CREATE TABLE MYTABLE (MYKEY INT, MYATTRIBUTES VARCHAR(100))
Compared with the DDL specified originally by the user in Oracle, some information about the identifier spelling has now been lost. Other applications that expect the original identifier spelling, like a client app which performs
SELECT * from MyTable, will have a problem now. Also, it is quite possible that certain corporate coding standards were followed in the original Oracle system, which are no longer adhered to in this converted code.
To address this challenge, Exodus allows the user to specify a list of (partial) words which will be used to format the identifiers.
In the example above, by specifying a list of the words
My,
Table,
Key and
Attributes, Exodus will generate the original identifiers again:
Exodus-generated DDL statement with 'dictionary' case mapping:
CREATE TABLE MyTable (MyKey INT, MyAttributes VARCHAR(100))
The only downside of using this formatting option is that the user needs to specify this list of words, which may or may not be easy to do. Also, it will cause the overall conversion to be somewhat slower.
Exodus case mapping options
In summary, Exodus lets you specify how to perform case mapping for 3 types of identifiers, namely:
- user names
- table/column names (and names of indexes, sequences, cursors)
- names of stored procedures/functions.
For each of these categories, one of the following case mapping options can be specified by the Exodus user:
- no mapping at all (the default)
- uppercase
- lowercase
- 'sentence case' (=lowercase with initial capital, i.e. Mytable)
- 'word case' (=lowercase with initial capital after underscore, i.e. My_Big_Table)
- 'dictionary case' (=using a user-specified list of words, see the example above).
Note that names of SQL variables and parameters will not be mapped: the spelling of the first declaration of a variable/parameter will be applied to all subsequent occurrences.
In conclusion...
Migrating SQL code for custom applications between different DBMSs is not an easy task, and there are many hurdles to overcome. The SAP Exodus DBMS migration tool for custom application goes a few extra miles to make the migration job easier.
For more information about SAP Exodus, see the
main Exodus blog post here
