Robelle Sponsor Message

September

2002

Get the most out of your Robelle Tools

Boost Your HP 3000 With Underused Software

By Bob Green

The HP 3000 has many third-party tools that will extend the life and power of the system. These tools often lie installed, but unnoticed and unused. For the most part, we continue to use the few tools and techniques that we learned when we first met MPE and IMAGE.

For example, once upon a time there was an HP 3000 site that had many user reports like this:

“Find all students from New York with 3.0 grade point, majoring in English, whose parents are alumni, and who have less than $5,000 in student loans.”

Although the site’s database was small, it was complex, with many links from the student master to the detail datasets about the student. Some reports like the one described above took two hours or more to run and interfered with interactive response. Why were these reports taking so long and what could we do to help?

These were straightforward COBOL programs, such as you or I might write. They went serially through the student master, and for each student, they did a keyed DBFIND/DBGET on the each of the related details, looking for the information on that student (i.e., “state” is in the Address dataset, etc.).

Then I had a brain wave. There must be a faster way to do this. Using Suprtool I knew that I could scan his or her entire database in just a few minutes, looking at every single bit of information about every single student! Maybe using the indexed DBFINDs was not always the fastest way to retrieve complex related data.

Quickly I put together a Suprtool job that serially scanned each of the five datasets, extracting and sorting the relevant data by “studentid.”

Then I spec’ed out a COBOL program to read the five files simultaneously, mashing together the records with a matching “studentid,” dropping those students who did not have an entry in all five files. The result was a report containing only the students who matched all the criteria. And the execution time for the entire job was 20 minutes.

How was such an improvement possible?

How could the “indexed” approach be so slow and the brute-force “serial-sort” approach be so much faster?

Here is the explanation that I came up with. Visualize a hard disk containing the five datasets of this database, spread out across the disk space.

When you do indexed DBFINDs and DBGETs by “studentid” you are causing the read head on the disk to jump all over the drive, physically moving the mechanism for each call. This means that you are using the disk drive in the slowest way possible: random access.

When you do serial scans of a dataset, each entry retrieved is adjacent to the preceding entry, thus reducing the number of disk accesses needed, sometimes dramatically. For each new entry that you retrieve, you also retrieve the adjacent entries in the same disk page.

And with Suprtool, the contrast is even more vivid, since Suprtool reads about 50,000 bytes of adjacent entries with each disk access. Assume that each entry occupies 250 bytes, then each disk read would retrieve 200 entries. Suprtool is using the disk hardware in the fastest way possible.

In addition, Suprtool saves the CPU overhead of the database calls, replacing them by a few file system calls and some highly optimized sort operations.

Suprlink is born

This experiment reduced run times by up to six and led to one of those under-used third-party tools: Suprlink, designed to speedily link related data.

For years, people had been telling us “We love Suprtool’s speed, but couldn’t we have multiple dataset extracts too?” What they were really asking for was fast multi-dataset extracts, because they were accustomed to Suprtool’s high speed. We resisted adding regular indexed lookups to Suprtool because we knew the result would often be slow performance.

With Suprlink we gave them multi-dataset extracts and a big performance improvement at the same time. We made Suprlink an integral part of our Suprtool product, and every site that has Suprtool also has Suprlink.

Suprlink is a unique program — it allows you to tradeoff temporary disk space (cheap) for clock time, (always in short supply). With Suprlink, you use Suprtool to serially scan the tables for your selected students, creating a sorted Self-Describing file from each table. Then Suprlink reads all the extract files simultaneously and merges the related fields together into a new, wider record with all the fields. It is amazingly fast.

Many of you already have Suprlink on your system, since you are already Suprtool users. But you may never have used it. Quite often, revising two of your slowest reports to use Suprlink can make it seem like you have upgraded the server — especially if the reports must run during the workday.

Taking better advantage of the tools available to you can extend the life of your HP 3000 system.

By the way, Suprlink makes just as much sense on HP-UX as it does on MPE. So when you do eventually migrate one of your applications, you do not have to change the logic and coding of the extracts. For more information on the theory and practice of Suprlink, visit www.robelle.com/tips/suprlink.html

Copyright The 3000 NewsWire. All rights reserved.