Point and Click to Migrate Databases

Speedware’s DBMotion points toward graphical interface to move data simply

Review by John Burke

DBMotion is a migration tool designed to help HP e3000 users migrate their TurboIMAGE, KSAM (both native and CM) and flat-file databases to Oracle or Microsoft’s SQL Server on Windows or HP-UX. DBMotion also supports Speedware’s catalogs, of course, and a future release will support rival Cognos’ PowerHouse PDL dictionaries. Speedware Corp. has over 25 years’ experience providing tools for the HP 3000 and TurboIMAGE, with Speedware its flagship product, but far from its only offering. DBMotion made its debut in late December, 2002 as its latest product.

With DBMotion, Speedware attempts to automate most of the database conversion process, saving the user both time and effort. The goal for DBMotion is to “easily migrate your databases in minutes” with a simple three-step process:

• Point to the source database

• Optionally, make naming and data type adjustments

• Press migrate

DBMotion simplifies or eliminates many of the tedious manual tasks involved in migrating HP 3000 databases because it understands HP 3000 native database concepts and provides default target database mapping suggestions. Yet it also allows you to retain full control over the process at all times. Basically, manual masters and details are mapped to tables. When migrating a TurboIMAGE database to Oracle or Microsoft SQL Server, DBMotion:

• ignores automatic masters – they are never migrated;

• automatically replicates all master-detail relationships using foreign keys.

Foreign keys ensure that values in a dependent (or child) table must match a unique or primary key value in a referenced (or parent) table.

DBMotion gives you complete control over the migration process, while still providing the benefits of an automated tool. You can manage file and item mapping right down to details such as data type and byte size. But DBMotion will warn you of errors that may occur if you make your own changes to data type or precision.

DBMotion allows you to develop multiple migration scenarios for testing purposes, each with its own properties and mapping attributes — something you would not likely be able to do when doing a migration manually.

Features

There are two principal components to a DBMotion migration. First is the migration of your TurboIMAGE database (or KSAM file or flat file) “structure” to an Oracle or SQL Server structure. Second is the migration of the data from your TurboIMAGE database (or KSAM file or flat file) to your new Oracle or SQL Server database. The first step can be done independently of having Oracle or SQL Server installed.

DBMotion can handle arrays, dates and nulls, concepts that are handled differently in TurboIMAGE than in relational database management systems. DBMotion currently offers two, and will soon offer three, methods for converting TurboIMAGE arrays. It automatically converts date items to a data type of your choice and lets you specify which columns should be null-allowed. A future release will include a powerful rules-based data conversion engine. For example, suppose you used all-9’s to indicate a null date. You can tell DBMotion to change that item to a null in the target database.

Since KSAM and flat files are not self-describing, DBMotion gives you the capability to describe their record layout so that it may be replicated in your target database. DBMotion also allows you to merge several TurboIMAGE databases, KSAM files and flat files into one target database. DBMotion’s find-and-replace feature allows you to make mass updates to file and item names, including case, prefix, suffix and special characters. Even in the mass update dialogue, you are given control over which items are changed. You can deselect any subset of items by just un-checking them.

During the migration, DBMotion again gives you complete control over the process. Its time estimation engine allows you to decide how many records and files to copy and calculates the time required to do so. You can control the disk or partition location of your large database tables or indexes. Currently, DBMotion does not do any compression during data transfer, but it does try to optimize data transfer by performing bulk updates. Finally, DBMotion allows you to start any number of database migrations, detach yourself from the copy process running on the server and simply reconnect to the process when you are ready.

System Requirements and Supported Environment

DBMotion is supported on Windows 98, NT, 2000 and XP. The source MPE system can be running any supported version of MPE/iX and TurboIMAGE. If you are planning to migrate many gigabytes of data, I recommend your MPE system have 100base-T capability, since the data transfer does not use any compression and takes place across your LAN.

The supported target systems are Oracle (8.04, 8i, 9i) on either HP-UX or Windows (NT, 2000) and SQL Server (6.5, 7, 2000) on Windows (NT, 2000). Also supported are Omnidex (3.04, 3.08) and Omni-Access (3.07).

DBMotion supports Oracle on Linux, or any other platform for that matter, provided you also have the Oracle Client for Windows installed along with DBMotion. Currently, Speedware only has a server daemon for HP-UX, so the data transfer is not direct from MPE to Linux, for example, but passes through the Oracle Client for Windows.

Installation

The DBMotion install is your typical Windows install and only takes a minute or two. DBMotion itself has a relatively small footprint and ran just fine on a Pentium II 450 MHz PC with 128Mb RAM and Windows 2000 Professional.

You can actually test all the features of DBMotion up to but not including the actual migration without having a target database available. All you need to do is tell DBMotion that the target is either Oracle or SQL Server. The MPE server install is straight-forward, with the actual transfer of files done to the HP 3000 by DBMotion itself. Installing the server daemon on HP-UX, if necessary, is equally easy.

Documentation

There is a laudable trend in the industry to do away with printed manuals and the deforestations they cause, though what most vendors still do is provide the manual in either PDF or HTML (sometimes both) format so that the user can search for keywords and print out sections as needed. Speedware has taken this trend a step further by eliminating the formal manual altogether for DBMotion.

All the information you need to use DBMotion is contained in its sophisticated online help system, which is indexed and searchable with numerous embedded links. Of course, context-sensitive pop-up help is also available for every dialog screen.

I would personally prefer something more like a traditional manual, especially when the feature list starts exploding; Speedware says they will consider this. But then maybe I’m just getting old. For the product as it exists today, the online help is comprehensive and should be adequate for most people.

I believe it’s important to note that Speedware does not pretend to give any kind of tutorial on TurboIMAGE, Oracle or SQL Server. It is assumed that you already have this knowledge. My test proved that you need only a limited knowledge of Oracle, and maybe a little help from Speedware support, to do a simple database migration.

Let’s take it out for a spin

I tested DBMotion on a TurboIMAGE database with one automatic master, 12 manual masters and four detail datasets. The database had 109 items, including 19 arrays, both I2 and Xn. My target was Oracle 9i Standard Edition running on Windows 2000.

I had originally planned to run Oracle on Linux because I thought that was a combination that would interest readers; however, my Linux server did not have adequate hard drive space for the notoriously piggish Oracle. I switched to Oracle running on Windows for this review, at least still preserving a mixed database/OS vendor environment for the target system.

Figure 1 above shows the Source Database Information screen. The “Load from a Catalog On:” check box is for your Speedware catalog, if you have one. Once you’ve defined your source database, DBMotion reads the root file on the source system and loads its description. The left half of Figure 3 shows the results of loading the source database definition.

Figure 2 (below) shows the Target Database Information screen plus a portion of the DBMotion Online Help system that describes the fields on the screen. Mostly obscured is the Split Array check box, checked for this example. RDBMSs have no concept of an array item.

Version 1.0 of DBMotion gives you two options for handling TurboIMAGE array items, do nothing or split the array into separate columns. The “do nothing” option is actually more important than it might seem at first. A TurboIMAGE array item of mXn, for example, is just a buffer of m*n bytes, which may be exactly how your application treats the data. If you want to make the minimal changes to your applications as part of a migration (recommended by most), then the “do nothing” option creates one column in your target database of, in this case, m*n bytes.

If you check the split option, then an array of mXn, for example, gets re-structured into m columns of data type char(n). Figure 4 shows how the 10I array DED-BASIS has been re-structured into ten columns of type NUMBER(5), DED-BASIS, DED-BASIS2, DED-BASIS3 … DEDBASIS10. The next version of DBMotion will support a third option, creating a new table for each array item.

Once you’ve finished defining the target database and any special instructions, DBMotion creates and displays the schema for the target database (see the right half of Figure 3, below). Note that the first dataset in the source database is an automatic master that is eliminated in the target Oracle database.

Since Oracle does not like dashes in column names, I used the Replace in Database Definition Wizard to globally change all dashes to underscores (see Figure 5). I wanted to change all names in the target database to lower case, but as of version 1.0, DBMotion cannot do that and retain the underscores. Speedware reports that this capability will be added in the next release.

All that remained was to click the migrate icon, and then answer a few questions: whether to build a new database or to use any exiting base; whether to copy the whole database or just a portion; and whether to verify data integrity after n number of records. In my test, the target database was then created on the target host (deleting any existing version) and the data copied over, making any necessary changes on the fly. Figure 6 shows part of the actual migration process, where you can further choose to copy only certain datasets.

Conclusion

I always find it a challenge to review version 1.0 of a product, because often the promise greatly outweighs the substance — and then there is the tendency to focus more on what the product does not do, rather than on what it does do. Keeping this in mind, DBMotion falls into my “It’s a Very Good Beginning” category. It meets its stated goal of “easily migrate your databases in minutes” for simple, straight-forward databases such as the one in my test and simple, straightforward migrations.

Once I understood how to use the product, I was able to migrate my test database quite literally in just a few minutes. However, I believe DBMotion needs to become more versatile to handle some of the challenges that exist in many HP 3000 production databases and potential migrations. In fairness, Speedware probably did not even consider the development of DBMotion until after HP announced the end of its support for the HP 3000, so the company has made considerable progress, even with version 1.0.

Speedware also has numerous enhancements in the pipeline to increase the flexibility and versatility of DBMotion. I have not mentioned some of the more exciting possibilities because Speedware has not formally committed to them yet. But the DBMotion of a year from now is likely to have radically expanded capabilities.

One advantage of a relatively new product is that customers and prospects have much more influence over the development of features. If you need some feature not discussed here, contact Speedware. They are likely to be very receptive. For example, I’m told DBMotion 1.0 does not support Linux natively only because there has been no demand for this feature.

Speedware has a reputation for good support, which is in this case a very useful thing: the environment is complex, and significant increased capabilities are on the horizon, so do not skimp on support. In case you feel lucky and think you can do your migration in just a month or two, DBMotion may also be leased for $3,000 per month with a support cost of $750 per month.

In short, if you are planning a HP 3000 database migration project, you should take a good look at DBMotion. It could save you considerable time and effort.

John Burke is editor of the NewsWire’s net.digest and Hidden Value columns and has managed HP 3000 systems for more than 20 years.