When we speak of “bells and whistles,” we don’t mean far-fetched extensions of SQL but simple operators such as the string concatenation. If you want to combine the two strings “Hello” and “World” together in Oracle, you use a totally different operator than you do with Sybase. For Oracle, the operation would be

'Hello ' || 'World'

Here you see the concatenation operator || used. Notice also the use of single quotation marks to denote a string in Oracle. On the other hand, using Sybase or SQL Server the string command would be

"Hello " + "World"

This is the reason why many migration efforts just take a large amount of man hours to perform. Now, maybe you could go through each file and replace every + with a || symbol using an editor or other tool, but what about the following statement:

select 1 + interest_rate from econ_table;

Using this method, you would be changing all arithmetic expressions of + to || and invalidating your mathematical code! Even in this simple case, you are forced to ponder a more complex operation. And once you’re successful, you still have covered only one operator of the SQL language and are faced with strings that need to be changed from double quotation marks to single quotation marks.

The point of this discussion is that even a migration from one ANSI SQL database to another will be complex unless the original system was written in standard SQL. Even in this case, time-consuming changes will need to take place.

In the best-case scenario, even when you are migrating from one database to another and not changing the application code, the most you can hope for is a modular set of database calls within libraries that the application uses (see Figure 7.3). With this isolation, you can change the database libraries to fit another RDBMS without going through all of the application code.

Figure 7.3.  If the database calls of an application are isolated, the migration to another database will be more efficient.

Another problem with vendor SQL is that it is so enticing to use vendor “extensions” to the SQL language. As a developer, there is no reason to refrain from using these tools unless you are informed that the code needs to be as compatible to standard SQL as possible.

This may seem heartless that I would code an Oracle SQL application using big, mean SQL extensions such as the DECODE statement. I might have a module or “method” that requires that I sum on salary for all employees but those in the sales department. In this case, I would use a DECODE statement like the following:

Select

dept_code,

sum( DECODE(dept_code, 'SALES', comm + salary, salary) )

from Employee

By doing this, I would save time by eliminating the need for if/else logic in my application code. Therefore, by designing applications to be very open and portable, you will sacrifice development time and cost if you look to maximize portability. The lesson learned is: Portable software is something you need to plan.

What you can do if you want to build applications that are more portable is isolate database calls to certain modules of your application. (For the object-oriented fans in the audience, you would just have a class like DATABASE_LIBRARY which contained all the database methods.) Ultimately, though, you probably will face differences in vendor SQL and will need to code those changes.

The Great Operating System Barrier

When you need to migrate across operating system platforms, you are always in for drastic changes in an application. There are some exceptions to this. For instance, if your application is written in a vendor tool that is portable across the different OS environments, a migration is easier.

An example of this would be a SQL*Forms 3.0 application residing on DEC VMS and being ported to UNIX (see Figure 7.4). In this case, you would only need to regenerate the forms and possibly change a few scripts. In these instances, a vendor layer shelters you from the specifics of each OS environment.

Figure 7.4.  A software vendor’s code should shelter the user from the specifics of each OS environment so as to make migrations easier.

Contrast this with a COBOL/CICS/DB2 application being moved to a Windows NT/ Oracle environment. From the front-end to the database, every module would need to change. More importantly, the functionality of the application would be designed differently in a GUI environment than in a character-mode environment. The farther away you move in the philosophies of the two different environments, the more extensive your migration efforts will be.

Oracle Alliance—Design and Migration Services to the Rescue

The Oracle corporation, whose goal it is to make any migration from any other platform to an Oracle RDBMS easier, offers the Design and Migration Services (DMS). It is a department that works closely with the Oracle Alliance.

DMS provides very inexpensive documentation and conversion kit software for most of the major migration combinations. Along with this comes detailed documentation describing the differences between your old environment and the new Oracle environment.

The Conversion Kit software can be run against different portions of your code, and will automatically convert different parts of your old code into new Oracle-compliant code. Of course, as stated earlier, these kits or services cannot replace the human expertise needed to make the port and cannot provide the organizational communication that will be needed.

Along with this, Oracle provides consulting through DMS and works with a variety of vendors to provide more specialized services that require human resources.