Last update:22-Dec-2017
NB: earlier,
SAP Advanced SQL Migration was called ‘Exodus’. it was renamed in Q4 2017.
Converting SQL code from one SQL dialect to another, like Oracle's PL/SQL to SAP ASE's Transact-SQL probably sounds like a boring, nerdy whaddever to most people. But since you, dear reader, are reading this blog, you are likely not "most people" but a member of the tech crowd. Admittedly though, even in those circles SQL code conversion may not seem terribly exciting to many folks, since databases, and especially geeky topics like SQL syntax and semantics, are kinda specialized, developer-oriented stuff. So why bother? (keep reading for the answer)
Personally, I find SQL conversion to be one of the more exciting things that life has to offer (we can discuss the other ones over a drink at the bar). That I've been working with SQL since 1989 may have something to do with that. And yes, I should get out more.
Even for SQL-infected readers, converting a PL/SQL statement to its equivalent in Transact-SQL or Watcom-SQL may not sound like a terribly complex, or even interesting, problem. After all, all those SQLs are pretty similar, right? And there is even an ANSI SQL standard all SQL dialects pledge adherence to.
Right. What could possibly go wrong?
Back down here in reality, converting between SQL dialects actually appears to be surprisingly hard -- as anyone who has tried this will know.
So what about that supposedly universal ANSI SQL standard?
Indeed, most SQL dialects claim to be ANSI SQL-compliant. But when you look closer, those claims often boil down to something more narrow like "ANSI SQL-92 entry-level" compliance.
To understand what that means, consider that the ANSI SQL-92 standard dates back to -could you guess?- 1992. In those days the world of SQL was much simpler than it is today. For example, stored procedures were not even defined by ANSI until the SQL:1999 standard appeared (to the ANSI standard fanatics who disagree: yes, you're formally correct, but SQL-92/PSM wasn't there until years later and is generally considered to be part of SQL:1999; and it's not part of SQL-92 entry level anyway).
Despite all that ANSI compliance, in practice the SQL implementations by most database vendors are chock-full with vendor-specific "extensions" to the ANSI standard - which is a polite way of stating that aspects of a SQL feature are not ANSI SQL-compliant at all. And thus, also likely incompatible with other SQL dialects.
Not fully complying with the ANSI SQL standard may sound like a Bad Thing. But let's keep things in perspective: standards will always lag the natural progression of a technology.
It starts when some vendors pioneer a concept, like SAP Sybase ASE did with stored procedures and triggers in the 1980's. Other vendors then also adopt those concepts but in the absence of a standard, everyone implements their own variant. Years later, a standards body like ANSI then tries to define a "standard" even though the existing products have already done their own thing. So it is pretty much unavoidable there will always be discrepancies between the standard and the actual products. That's life.
Bottom line: while there is indeed a lot of similarity across SQL dialects, the number if aspects that are not ANSI-compliant typically far exceeds the parts that do conform to the ANSI SQL standard.
It's pretty safe to that no SQL dialect is fully ANSI SQL-compliant or fully implements the ANSI SQL standard (the one exception perhaps being "Ocelot SQL" who claimed full implementation of a particular ANSI SQL standard at some point; but then, Ocelot didn't quite win the RDBMS race so you shouldn't feel bad not knowing about them).
And BTW,
which ANSI SQL standard are we talking about anyway? We haven't even discussed the more recent incarnations like ANSI SQL:2003, SQL:2008 or SQL:2011 (I know you've heard it before but indeed: the good thing about standards is that there are so many of them).
If you're still reading this article at this point, it must mean that you don't find this a boring topic after all (if you were attracted by the blog title and you're still hoping for some E.L.James-style raunchy prose, well, just keep reading).
Why should we bother discussing cross-dialect SQL conversion in the first place?
As I pointed out in
earlier blog posts, SAP wants to enable customers to migrate their custom applications from non-SAP databases to a SAP DBMS. One of the biggest challenges in such migrations is converting the SQL code, especially the server-side SQL in stored procedures/functions etc.: such code can contain many complexities that may not always be easy to find. Consequently, converting server-side SQL code is an area where migration projects often overrun or fail.
As it happens, converting stored procedures is one of the main functions of SAP's Exodus DBMS migration tool. Not only will Exodus quickly analyze all server-side SQL code and report precisely which features are being used; it will also highlight those features which do not convert easily to the target SAP database of choice. This allows for running a
quick complexity assessment before starting the migration project.
As for all those vendor-specific extensions to the ANSI SQL standard, Exodus takes these into account as much as possible. When the difference between the source and target SQL dialect is merely different syntax, then Exodus can often compensate by generating the syntax as required by the target SQL dialect.
It gets more difficult when there is a difference in semantics (i.e. functionality of a SQL feature). In such cases, Exodus may also be able to compensate, but human intervention may also be required. In case Exodus spots any constructs which it cannot convert automatically, it will alert the user to the construct in question, and often suggests a possible solution direction.
In my next blog post we will look at some actual examples and how Exodus handles these.
Incidentally, database vendors usually don't see their non-ANSI-compliance as a problem. On the contrary: if it makes it hard for customers to migrate away to a competitor's database, then that is good for the vendor's future business prospects. Customers often see this differently however, and words like "lock-in", "stranglehold" and "help!" often appear in related conversations.
With the Exodus DBMS migration tool, customers no longer need to feel handcuffed to a particular database vendor just because migrating to a SAP database seems too hard to even consider. So if the relationship with your DBMS vendor has turned into a painful affair, contact
ExodusHelp@sap.com to discuss how SAP can provide a fresh perspective.
So, you may wonder, what would an Exodus engagement be like? Well, it may go something like this...
She had been waiting for more than an hour. Outside, it was already getting dark.
The chair had become uncomfortable by now, but the instructions had been clear. She had to wait.
Suddenly, the door opened.
A middle-aged woman stepped into the waiting room.
"She must be his secretary", it flashed through her mind.
The secretary looked around, but there was nobody else in the room.
She could only be coming for her.
"Miss Outer Join?"
When she heard her name, a shiver ran down her spine.
She opened her mouth to answer, but her breath faltered with excitement.
For a brief moment she closed her eyes.
This was what she had been waiting for, she had prepared herself for.
She took a breath and opened her eyes.
"People call me O.J."
The secretary looked at her slightly longer than would have been necessary.
Her tone was more determined.
"As you wish.
O.J., please come in.
Mr. Exodus will see you now."
