At the end of Part Four, we were ready to
create the Oracle model of the Northwind database "imported" from
a SQL Server 2000 instance. In this part of the series, we will
continue the SQL Server migration and look at some issues related
to the SQL Server to Oracle migration process.
Migrating a SQL Server database
At the end of Part Four, we saw the Capture
Wizard welcome window. Before getting into the models, let's
re-visit a topic mentioned in a previous article. With the SQL
Server plug-in successfully installed, one of the items I said we
would look at was the Help menu. When the previous setup for MySQL
was replaced, Migration Workbench also replaced the help menu
contents, so if you need to reference documentation for another
source database system (MySQL, DB2, etc.) not currently "loaded"
in the utility, you can look in the docs directory under OMWB home
and proceed from there. The SQL Server specific help menu appears
as shown below.
When I select a guide, the browser that appears
is Internet Explorer, even though in the last article I had set my
preference to Netscape. Apparently, the file is overwritten.
Again, this setting is found in the state.properties file (edit
the BROWSER_PATH variable).
The Source model versus the Oracle model
Notice the comparison in structure between the
source model and the Oracle model.
|
SQL Server Source Model |
|
Oracle Model |
|
|
|
|
Some objects are mapped one-to-one, that is,
there is the same exact number of items in both models under a
specific category. The check constraints are an example of this.
The eight constraints in the Northwind database were directly
mapped into the Oracle model. In the screen shot below, you can
see the eight constraints in Northwind.
The Oracle model representation is shown below.
Do you have to do anything with the check
constraints (in this case) because they match up so well? It
depends, because SQL Server has its own version or flavor of SQL.
Upon inspection of the CK_BIRTHDATE constraint (you can use either
model, click on the constraint name to make the code appear in the
right pane), you'll see a "getdate()" function in the Constraint
Details section.
For Oracle, this "getdate()" function is a
no-go, and this leads into what may be a complex at a minimum,
tedious to be sure task: converting SQL Server code into Oracle
compliant code, and this topic is covered in detail in the
Microsoft SQLServer 2000 Reference Guide (accessible via the Help
menu). In fact, there is a lot of detail and it appears many SQL
Server code constructs need to be converted before the code will
successfully compile in Oracle.
More than likely, any database
migration/conversion project is going to be using a schema (or
more than one) that was developed with only a single RDBMS product
in mind. For example, the code to retrieve the top ten items in a
SQL Server function/procedure/trigger could be written in generic
ANSI SQL that Oracle can use (and vice versa). Another potentially
edit-intensive conversion process concerns the use of quoted
identifiers. Many of the views in the SQL Server databases (Northwind
and pubs) use "Some Name" as an object name instead of a single
unquoted string like somename. If you do not like using quoted
identifiers (which Oracle supports), this is where a coding
standards guide or policy pays off handsomely.
So, what has to be converted from SQL Server to
make the code syntax Oracle compliant? After perusing the
reference guide, you may conclude that this question should have
been phrased as "So, what doesn't have to be converted?" Oracle
always has a FROM clause in SELECT statements, whereas SQL Server
does not. Any "getdate()" reference in SQL Server then becomes
"SELECT sysdate FROM dual." Which method is better first begs the
question of defining what "better" means, but the idea of a one
word named function that even looks like a function with the "()"
part is very appealing if you are a developer constantly having to
select sysdate from the dual table. On the other hand, having the
dual table is useful for performing calculations.
Going back to SQL Server's Query Analyzer tool
for a moment, let's look at what SQL Server provides in comparison
to Oracle's SQL*Plus utility. What does it take to create, alter
or drop, say, a trigger? The Query Analyzer presents the code in a
ready to edit format, all of it right there in front of you. The
woefully inadequate SQL*Plus lets you perform a select statement
to see the code, and then you have to type it back in along with
the changes.
Here is an example from SQL Server, using the
CustOrdersDetail stored procedure. With a simple right-click/click
maneuver (right-click on the item, then select Create, Alter or
Drop from the menu), you can go from what is shown below -
to a ready for editing window.
This is another of those "Gee, I wish Oracle
did that" examples I mentioned in Part Four.
Picking up where we left off in Part Four
After starting OMWB, select Action>Capture Source
Database and the Capture Wizard starts. The following series of
screen shots is similar to what we already saw with the MySQL
example, so they are shown without comment.
After clicking "Yes" at this last window, let's
generate a migration report and view its contents. You generate the
reports via the Report menu and OMWB informs you as to where they
are located.
Clicking OK results in the report being presented
to you in your browser. Let's look at the details for Northwind's
stored procedures. The first line item has to do with a "set
ROWCOUNT" statement in the Ten_Most_Expensive_Products procedure.
Oracle does not support "set ROWCOUNT" which is what SQL Server uses
to return (in this case) the top ten, umm, most expensive products.
The main body of the original code is:
ALTER procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
To perform the manual conversion, you have at
least two options.
Option 1) Modify the original code in SQL Server
(or Oracle) to something you can immediately use in Oracle. You can
simply run the SELECT part of the code and see that any product with
a price greater than 44 is going to be in the top ten. Of course,
you will be performing regular and frequent checks to ensure "44" is
the correct value to distinguish the top ten most expensive products
from the rest.
The kludged code is shown below.
ALTER procedure "Ten Most Expensive Products" AS
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
WHERE UnitPrice > 44
ORDER BY Products.UnitPrice DESC
Option 2) Modify the original code in SQL Server
(or Oracle) to use ANSI SQL standards or syntax. This may or may not
be simple, depending on your knowledge of what ANSI SQL looks like
when compared to SQL Server or Oracle SQL. If you do not know how to
reformat "set ROWCOUNT" to ANSI SQL (or a comparable construct in
Oracle), but need to get this migration project done like yesterday,
do what you do know and go back and fix it later.
What else is in the report?
Looking at the Sales by Category view, the parser
does not like the comma at the end of line two (shown highlighted
after Products.ProductName).
Would a simple "make it one line and save it" fix
remove this error? Let's see. Here's the altered code saved in SQL
Server. My original "out of the box" create an Oracle model had 22
errors.
Running the Migration Workbench capture process
again results in some errors going away. Now there are only 20
errors (I made a few other changes, including the "set ROWCOUNT"
conversion).
The "set ROWCOUNT" problem in the ten most
expensive products stored procedure went away, but Migration
Workbench still does not like the comma in the view. And just as an
experiment, adding a space before the comma to see if the editing
makes any difference, only results in the same error, but now it is
at position 77 instead of 76.
What is wrong with this line (and others like it
in the other error-flagged views)? The nondescript parse error does
little to help you fix what Migration Workbench does not like. There
are two approaches to this problem: ignore it and continue on with
the migration, or figure out what, exactly, the parser does not
like. Moving on with "Migrate to Oracle" via the menu path shown
below, the end result shows a smaller number of errors, but they are
spread out across more areas (stored procedures, indexes and views).
Migration Workbench will present an editable
field where you can fix errors on the spot. The "getdate()" function
is easy to fix in this context because all it requires is replacing
it with sysdate. The other errors, one of which is shown, comes back
to the views that Migration Workbench did not like.
An easily correctable error:
An error which involves fixing code in SQL Server
or in the model.
The end result showing a breakdown of failed
objects by object type is shown below.
Failed indexes are easy to fix - or not, because
you can always work without them. The failed stored procedures are
of concern, so if those were encountered in a real or production
database, you would probably want to fix them before going live with
the migrated data.
A query from SQL*Plus confirms the two failed
stored procedures, but there is also a view that appears invalid.
Logging in as "sa" and compiling the view results
in the following:
The same approach (trying to fix one of the
procedures by compiling) fails, and the error reflects what was
shown in the migration report.
In general, the report table should be read with
care. The ten views that failed in the "Create Oracle Model" phase
means only seven of the 17 original views actually made it into
Oracle. Of the seven views present in Oracle, one of them had to be
compiled to change its status from INVALID to VALID.
What's in the reference guide?
Quite a bit, actually. The troubleshooting
section is fairly extensive, but it fails to address the parsing
errors which were present in the Oracle model (before the data was
actually migrated into Oracle itself). There is also an extensive
side-by-side comparison of SQL Server and Oracle, plus plenty of
code examples. Be forewarned, however, that some examples are
incorrect. Using the pubs database as one of the code examples:
Although presented in a text format, you will
still be able to notice (even more) neat things SQL Server does or
supports. For example, the phone number constraint of
"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9])"
in SQL Server would have to be dealt with by
using a combination of SUBSTR, TRANSLATE and LIKE, or use of a
trigger and PL/SQL in Oracle.
The title of the reference guide includes Sybase
in it, but for the most part, the guide is all about SQL Server
versus Oracle. What happened to Sybase? It is not as if Sybase was
omitted in the documentation, but rather that Sybase used to be the
"SQL Server" product and the development/code base is not that
different today. In the dark ages before high speed Internet
service, Sybase partnered with Microsoft because Sybase needed help
marketing its database product, and Microsoft needed help with its
Office product (and its lack of a database component). The Microsoft
version of SQL Server overtook the Sybase version, and the companies
parted ways, with Sybase renaming its product. Anyway, because
Sybase and SQL Server share a common history, Oracle did not need to
create a separate reference guide for Sybase.
In Closing
If you need to get a migration project up and
running in short order, Oracle Migration Workbench, even when using
a more sophisticated RDBMS product such as SQL Server as the source,
can get you pretty close to where you need to be without too much
difficulty. Unfortunately, using a relatively simple database such
as Northwind causes heartburn for Migration Workbench. Are there
third party products that can do a better job? In the next part of
this series, we will look at one such product - SwisSQL - and
examine its ability to do two things: migrate from SQL Server to
Oracle and perform a migration in the other direction. Although
Migration Workbench is free, it is a one-way ticket.
and include the URL for the page.
