July 2005

User exits upgrade 3000 data migrations

New HP 3000 tool gives programmers exit-level porting into SQL

A new HP 3000 utility can steer database migrations with a deeper level of program code flexibility. The TurboIMAGE Porting Software (TIPS) made its debut last month from Comp Three, a company which developed the utility as part of its 11-year-old HP 3000 consultancy.

TIPS is bidding for a slot in a migration programmer’s toolbox because it adds a new level of function to the task of 3000 database migration: user-level exits. The feature gives programmers a way to control the content of a migrated database at the same time as the data is targeted and reformatted. Comp Three president John Hohn said the company’s work migrating 3000 shops sparked his firm to build TIPS — because data migrations off the 3000, with its loosely-enforced data types, usually include data cleanup during the migration.

“I had to do some very difficult conversion tasks,” said Hohn, “such as splitting one IMAGE record into multiple SQL records, and ‘re-converging’ two IMAGE databases which had migrated to different machines some years ago. The two databases had been developed along separate lines since their migration, using different datasets and items.”

“We had to do some very business logic-specific conversions while we transferred the data,” he added. “Now we have a SQL converter that calls a user exit as you extract the data, and allows you to insert your own code.” The new code could add new fields during the conversion, for example, then populate the fields with existing data.

Because TIPS allows a programmer to drop into his own HP 3000 code during schema creation and record extraction,

“there’s no conversion task you cannot do, as long as you can write the code,” Hohn said, such as going after values in another database to populate fields.

TIPS enters the 3000 database migration tool market against some established competition. Speedware has sold its DBmotion since 2003. MB Foster offers a database migration module as part of its UDA-Central data migration solution. And Taurus Software, in conjunction with Quest Software, sells Bridgeware, which uses the Taurus Warehouse engine to move data from HP 3000s to other data systems under SQL.

User exit flexibility

In such a robust field, TIPS needs more than the price advantage which Comp Three has supplied it with at introduction. (The product sells for $2,000.) TIPS wants to earn a trial with HP 3000 sites by adding features. It can define conversion rules for items and sets, using wildcards and a general-to-specific scheme. “For example,” Hohn said, “you can specify the number of implied decimals you’d like to add for the TurboIMAGE P, I, J and K items with commands.”

He said a single command in TIPS can set all P,I,J, and K items to two implied decimals during a migration and conversion, with a single line needed for each exception.

“That feature is especially useful for defining DATE types,” Hohn explained. “In the databases I’ve converted, it’s usually any item with the word DATE, except for one or two exceptions.”

TIPS converts HP 3000 TurboIMAGE databases to Oracle, SQL Server or MySQL formats. An initial user exit lets a programmer open other databases. A second user exit offers a chance to remove add, or change fields in each table, using a runtime library. A final user exit can be called during each and every record extract, “so you can populate the fields that were added or change the fields that were already there.”

TIPS’ infrastructure is a conversion tool “like everyone else’s conversion tool,” Hohn explained. “Its extension is the user exit capability.”

TIPS user exits are also useful for changing the “general buffer space” or “reserved for future use” fields often found in IMAGE datasets into their true application-defined structures.

For example, Hohn said many shops have employed the practice of placing large unused buffer space into IMAGE datasets, then using pieces of this buffer over time as the application evolved to have more fields. This was done so that the database structure didn’t have to change every time a new field was added.

In IMAGE, Hohn reported, “you may have a field like ‘FILLER80 X(80)’ and the application code may be treating this field as, for example:

05 FILLER80 X(80).

05 FILLER80-1 REDEFINES FILLER80.

10 SALES-DISC-FLAG PIC X(02).

10 SALES-DISC-PCT PIC S9(07)V99 COMP.

10 FILLER80-1-1 PIC X(74).

Using TIPS user exits, Hohn said “a migration developer could easily change the target SQL structure during the conversion process, so that the new fields (SALES-DISC- FLAG and SALES-DISC-PCT), rather than the old field (FILLER80) would wind up in the new database.”

Experience for sale

HP 3000 migration trends often offer such tools on a service engagement basis at first, then roll out the software for sale after some fine-tuning. Hohn said he wanted to make TIPS available while HP 3000 sites were in the full flush of migrating data.

The product isn’t the first software Comp Three has offered for sale. The services-based company has released a Windows/SQL Server-based construction company document tracking application, True Track Construction.

Hohn said the TIPS rollout was built upon Comp Three’s lessons from that Windows application, “especially how generic you need to make a product, and its configurability. You have to write something which everyone can use who wants to use it.”

TIPS runs entirely on an HP 3000, unlike some other database migration tools which use a Windows or Java client communicating with the 3000 server. “In our engagement, the HP 3000 was where all the business logic was,” Hohn said. “I had to add the account type, like GL, to the target database, and they had 200 lines of COBOL to determine that account type.”

Once Comp Three completed the work of making its tool generic enough to fit any user’s need, TIPS could split one record into two while building extract files, for example, using load scripts for Oracle, SQL Server or Linux-based MySQL. “Everything is built on the 3000 and then it’s pushed over to the target system,” he said.

PowerHouse applications are also part of the target for TIPS conversions. “We needed a way to define our flat files and KSAM files,” Hohn said. “IMAGE stuff you can get right out of the root file. We made TIPS read a PowerHouse Dictionary, from which it determines the actual names of the KSAM files and the layout. That lets it derive the SQL definition.”

TIPS is available for a free trial use. A demo version available at www.compthree.com/Tips.htm lets users process 100 records at a time; the free version has all the functionality of the paid version, but makes users “turn primary keys off, or you’ll get master-detail violations,” Hohn said. A TIPS product manual is also available for download at Comp Three’s Web site.

Making changes to migrated data is an essential part of the migrations Comp Three has done for HP 3000 customers.

“I know there’s other products out there for database porting, but this is the one that has served our needs,” he added. “There’s never a time when you want to pick up the data and it’s just fine, and then stuff it into SQL. You always want to mess with it.”

The need for a focused but flexible product like TIPS also underscores an overall migration philosophy from Comp Three. “The idea that you can buy a couple of products, throw a switch, and have a new system — that’s not realistic,” Hohn said. “We see this product as a one-time or couple of time use solution.”